The sun has gone down on the "would-be" accounting solutions written in FileMaker Pro. Too many people have blasted beyond the data and user threshold of these systems that plugged along unaware of their fate—that one day they would buckle under their own weight, while astonished users wondering what had become of their year-end financials.
Does this sound familiar? The ease of use factor can lull FileMaker programmers into thinking they can tackle accounting. Many don't realize the tried-and-true practices of contact managers, to-do lists, and other solutions just don't hold water when it comes to major-league number crunching.
If you've been reading FileMaker Pro Advisor for a while, you may recall another accounting article of mine (available at http://Advisor.com/Article/RYLEG01), wherein I describe pitfalls you should avoid and techniques required to build a world-class foundation for accounting. You would do well to review that article to understand these concepts before you embark on your fiduciary expedition with FileMaker Pro.
In this article, I'll discuss issues that go beyond basic database structure, and deal with workflow and how to handle the ultimate demands that will be placed on such a system. You'll learn why it's important to handle transactions that start life as "pending" before being "posted." And you'll learn a method for storing month-end account totals while, at the same time, back-dating entries and seamlessly update all affected accounts henceforward. And, finally, you'll delve into the practice of printing reports, including financial statements as well as checks and check stubs.
So, buckle your seat belts, my friends, and stock up on the No-Doze. Before you're through, you're going to learn more than you bargained for about general accounting practices and how to deal with them effectively within the realm of FileMaker Pro.
Transactions speak louder than words
Most FileMaker programmers don't have a background in accounting. They look at a database from a programmer's perspective, paying attention to the organizational detail but often missing the thought process of a typical accountant. It's all about reconciliation and balancing. To keep track of the movement of funds between accounts, you must be able to retrace your steps. When an error crops up, it's important to trace its history, or face being "out of balance."
Accountants record entries in their books as transactions. These transactions fall into one of three categories: payables, receivables, or general. In all cases, at least two accounts are affected by each transaction. When something is purchased, it's recorded in the payables journal and funds are moved from one account to another. When something is sold, it's recorded in the receivables journal. All other transactions take place in the general journal, such as the posting of payroll, corrections, or the general shifting of funds for any reason.
At all times, the accountant must record each transaction and ID it so he can trace it for auditing. But accountants usually spend time preparing a transaction and may not immediately post it. If you reviewed my last accounting article, you'll know I talked about writing account totals into number fields in your Accounts file, rather than as slow and unstored calculations based on relationships. For all these reasons, you want to construct a temporary holding place where the user can begin a pending transaction and keep it there until ready to post. I'll refer to this as a worksheet.
Your worksheet will be a new, blank record in a file specifically built to hold pending transactions. It should also provide a portal for creating multiple line items. The record-level fields represent values that apply to all line items, such as the date, an account number, charge code, or description. The portal fields should let the user create a list of items to be posted simultaneously in a single transaction. When the transaction is posted, the worksheet's data is moved into the files storing your posted transactions, and the worksheet is cleared. At the same time, the account numbers involved should be updated to reflect their new activity balance. This last step is important if you want your financial reports to appear quickly, because it will preclude any real-time calculation of non-indexed data.
Patent pending
Depending on which journal you're posting to, your worksheet will look slightly different. If you're building an accounting system for someone else's use, you should learn what he does and doesn't like about his current system, and try to emulate the good elements as much as possible to minimize (or eliminate) the need to train him on a new and foreign system.
For a first example, I'll look at a typical receivables journal. Several types of transactions are entered here: Whenever a sale is made, a credit or refund is issued, or a payment is received, a receivable transaction occurs. I'll start with the charge.
Typically, a charge is linked to some kind of record, such as an invoice. In a system that provides invoicing, you'd want to link it to a journal transaction on your accounting back end. The invoice is considered "pending" until it's executed, meaning printed and issued. However, for these examples, I'll stay strictly in the accounting realm. Therefore, a charge shall be posted by way of a pending journal transaction.
To demonstrate, I'll use a fictitious school's accounting system that posts various charges on a regular basis to various students (things such as lunchroom charges, AP exam fees, etc.). These charges eventually appear on a monthly statement that's sent to the students' parents, and shows any payments received and recorded since the last statement. But I'll get to that later. Figure 1 demonstrates what a pending transaction for the receivables journal may look like.
Figure 1: Receivables--Various charges are entered and linked to an account. All required information must be entered before recording is permitted.
Typically, all posted charges affect at least two accounts: Money is moved out of the accounts receivable account and into the account designated in the transaction. But say your fictitious school is part of a district consisting of four schools: two lower, a middle, and an upper or high school. In this case, you want to keep the receivable accounts for each school separate. Therefore, you have to group the students in the transaction from each school and add the amounts accordingly.
Stay at your post
After all the required information has been entered (account numbers, amounts, etc.), you're ready to post the transaction. When you do, three things must happen. First, your system adds the account transactions to the receivables journal. Then, it adds the necessary debit(s) and credit(s) to the Ledger file. And, finally, it updates the current balance stored in the Accounts file for each account that was involved. This last step involves a looped script that tests to see if an account record is currently locked before updating its new total. If you build your system so all editing occurs in a pending worksheet, the account records are only locked for short instances when those transactions are posted. So, it should never take too long to update the account successfully.
Figure 2 is an example of a register receipt for the posted receivables transaction, which should be automatically printed upon successful posting. In this example, you can clearly see the three students included in the transaction, along with the moving of funds between accounts. Because two students were from the high school, and one was from the middle school, there are two debits from their respective AR accounts and one credit to the AP Exams account.
Figure 2: Register receipt--Your system should print a receipt about posting a transaction to a journal, indicating which accounts were debited and credited.
When payments are received, you need a different worksheet. Figure 3 is an example of a layout for posting cash receipts. In this case, a parent may write one check to cover accounts for multiple siblings who attend the same school. The worksheet must validate that the total amount of the check is distributed completely before the transaction can be posted.
Figure 3: Show us the money--When posting cash receipts, you may have to distribute the amount of a single check across multiple accounts, and validate that the distributed amounts are in balance with the check amount before posting.
Similar to posting charges, your cash receipts will post to the receivables journal and money will move between accounts. However, unlike the charges posted earlier, cash receipts require that you debit funds from the cash account and credit them to one or more AR accounts. Similarly, the total amounts being credited and debited must balance, and you should print a journal receipt to prove this (figure 4).
Figure 4: Pleasure doing business with you--This transaction receipt shows the checks that were recorded to the receivables journal, as well as which accounts were debited and credited.
Taking the payable by the horns
Posting transactions to the payables journal is almost identical to the above examples involving the receivables journal, with one obvious difference: Instead of money coming in, it's going out. Instead of posting charges, you're posting invoices from vendors that have to be paid. Along with the amounts due, you want to record the date of each invoice and what payment terms you've been afforded by the vendor, from which you can automatically calculate each invoice's due date.
When it's time to pay bills, you'll want to group all unpaid invoices with a due date on or before the current date. From this list, your interface should let the user check off or flag the invoices he's prepared to pay immediately, and then process his payment. Figure 5 shows how a bill payment "pick list" can look. You may have a vendor that charges often, sometimes for small amounts. You may want to create a user-definable minimum amount that the sum of a particular vendor's unpaid invoices must exceed before they appear in this list. In figure 5, this amount is set to US$1.00 so all invoices appear.
Figure 5: Taking care of business--By marking the invoices you want to pay, you can choose which bills to pay now and which to pay later.
When you're finished marking the bills you want to pay, you'll want to run a script that: Creates the checks that pay groups of invoices per vendor, lets the user enter the check number to start printing from, prints the checks, then posts the cash disbursements receipt. Figure 6 shows an example of such a receipt, with the individual invoices listed, grouped by vendor, and the debited and credited accounts at the bottom.
Figure 6: Transaction reaction--A receipt such as this one should print after th user posts a payables transaction.
Just a minute, I'll check
When it comes time to design your check-printing layout, you'll have to make a few decisions. First, you'll have to decide on a format. There are basically three 8.5 x 11 check format types from which to chose: the check in the middle with a stub at the top and bottom, the check at the top followed by two stubs, or the check at the top with one big stub at the bottom.
Your choice of format may depend on the number of lines you want to print on your stub. You should print from the file where your checks reside, and list the line items (invoices being paid) via a portal. If you choose the stub/check/stub format, you can't otherwise print a list more than once on the same page, which you'd have to do if you were printing from the AP journal file where the invoices you're paying reside.
Therefore, first decide how many line items you'll have to print and decide on a format. However, you may not have that choice to make (e.g., the client decides for you), in which case, there's another option: If you first test in your check-printing script the number of related line items per check, you can run a subscript when the number exceeds the number of portal rows on the layout so a second page is printed listing the additional line items. Of course, you should engineer this script in a user-friendly way that warns the user that a supplemental form is about to be printed so it prints after the last check has printed and he has a chance to remove the unprinted checks from the printer.
Your printed checks will require a calculation that translates the check's dollar amount from numeric form to text (see http://Advisor.com/Article/ryleg04 for a sample file). Although there are several variations on this formula, I think you'll find the following works just fine. Assume the field storing the check's amount is named "CheckAmount." First, divide the check amount's digit places using the Int (integer) and Mod (Modulo) functions:
Next, convert each digit place to text, starting with millions. Make sure these fields are defined to produce a text result:
Millionstext=
Choose(Int(Millions/100) ,"", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine") & If(Int(Millions/100) > 0," Hundred ","") & Choose(Int(Mod(Millions,100)/10), "","", "Twenty ","Thirty ", "Forty ", "Fifty", "Sixty", "Seventy ", "Eighty ", "Ninety ") & Choose(If(Mod(Millions,100) >= 20,Mod(Millions,10), Mod(Millions,100)),"", "One " ,"Two " ,"Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ", "Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen " ,"Eighteen " ,"Nineteen ") & If(Millions > 0,"Million ","")
Now the thousands:
Thousandstext=
Choose(Int(Thousands/100),"", "One", "Two", "Three", "Four", "Five", "Six", "Seven","Eight","Nine") & If(Int(Thousands/100) > 0," Hundred ","") & Choose(Int(Mod(Thousands,100)/10),"","","Twenty ","Thirty ","Forty","Fifty ", "Sixty ","Seventy ","Eighty ","Ninety ") & Choose(If(Mod(Thousands,100) >= 20, Mod(Thousands,10), Mod(Thousands,100)), "", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ", "Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ") & If(Thousands > 0,"Thousand ","")
Now the hundreds:
Hundredstext=
Choose(Int(Hundreds/100),"","One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine") & If(Int(Hundreds/100) > 0," Hundred ","") & Choose(Int(Mod(Hundreds,100)/10),"","","Twenty ","Thirty ", "Forty", "Fifty ", "Sixty ", "Seventy ","Eighty ","Ninety ") & Choose(If(Mod(Hundreds,100) >= 20,Mod(Hundreds,10),Mod(Hundreds,100)),
"", "One ","Two ","Three ","Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ", "Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ") & If(Check Amount < 1,"No Dollars", If(Check Amount < 2, "Dollar", "Dollars"))
And finally, the cents:
Centstext=
Case(Mod(Cents,100) = 00, "NO", Mod(Cents,100) < 10, "0" & Mod(Cents,100), Mod(Cents,100))
Now you can put this together, like so:
CheckAmounttext=
Millionstext & Thousandstext & Hundredstext & " and " & Centstext & "/100"
The result is a $6,249.00 check spelled out "Six Thousand Two Hundred Forty-Nine Dollars & NO/100" (figure 7).
Figure 7: Check's almost in the mail--A sample check with the amount shown in both numerical and text formats, and the invoices being paid shown at the bottom.
Accountant general
When someone posts to the general journal, he has more freedom to move funds from any account to any account, whereas posting charges, payments, etc. is simply a matter of summing up the amounts and automatically distributing them among their respective accounts. However, you must carefully validate general journal entries to ensure those transactions balance before you post them. Figure 8 shows a pending general journal transaction, while figure 9 shows how a printed receipt of the transaction may look.
Figure 8: Finding balance--A pending general journal transaction should be validated to make sure all debits and credits balance out to $0.00 before posting.
Figure 9: Follow the money--After a general journal transaction has been posted, a receipt such as this one will serve as a record to show how the funds were moved between accounts.
Balancing act
A correctly built accounting system will always leave accounts in balance, meaning the same amount that was debited from one or more accounts exactly equals the amount credited to one or more accounts within the same transaction. In figure 8, you'll notice that the total debit equals the total credit. That's just the thing an auditor wants to see. And it's printed, to serve as a backup just in case the files get corrupted or damaged, or for some reason erased.
I stand corrected
When errors in journal transactions occur--and yes, they do occur (you think accountants are perfect?)--corrections must be entered as new transactions in the general journal. It's taboo to suggest going back and correcting an erroneous entry. Nothing arouses more suspicion in an auditor than a business with books devoid of correction entries. Errors happen and auditors expect to see correction entries as a normal part of doing business. Although it may be tempting to let users go back and make changes, you must resist if you want to build an airtight and auditor-friendly accounting system.
Close the book
At this point, I'll switch to New Millennium Communications Genesis (http://www.newmillennium.com) for my examples. I chose Genesis because it has an existing user base, reflects tried-and-true practices in programming and accounting, and does an exemplary job of functionality and support.
When you close the month, total the month's transaction amounts per account and lock them to prevent further transactions from being posted against them. In your Accounts file, create one field for each of 18 months of totals (one and a half fiscal years) or create a single field with 18 repetitions. Either way, you store the month-end totals of all transactions for each account. In another field, store the beginning balances for your balance sheet accounts. To derive the current balance for any account, have FileMaker add the beginning balance (if any) with the sum of the 18-month totals.
The 18-month distinction is important. Eventually, you'll have to close the year, in much the same manner as closing a given month. However, you may not close the year until you're well into the second or third month of the new fiscal year, or even later. Having an 18-month window gives you plenty of space within which to move without being forced to close your year. Figure 10 shows a detailed view for editing accounts with a repeating field to store monthly activity totals.
Figure 10: In the beginning--In Genesis, each account has a repeating field letting you store the totals for up to 18 months.
The closing of the month introduces another validation requirement to your journal transactions: to make sure you aren't posting to a previous month that has been closed. However, you may have to go back and post to the previous month for a variety of reasons, including corrections or posting payroll (if handled by an outside source and you must wait for their data). In these cases, you'll post directly to the general journal. If you let the user post to a closed month, you must remember to adjust the totals for that month for all accounts involved in the transaction. Also, if you let the user post back two or three months, you'll have to adjust the involved accounts' totals not only for that month, but also for the months that have been closed since then, because the totals will "roll through," having a sort of domino effect.
Report on the double
If you've been tracking your data correctly, running reports shouldn't take much time. You don't want to make the user wait while FileMaker adds up all the debits and credits for each account. At the most, you'll simply have it do a little math; take a beginning balance (if any) and add the closed months' totals along with the current month's balances. By putting the onus on each transaction to update account totals when they post, you save a lot of time when producing these reports.
The most common financial reports are the balance sheet, trial balance, income statement, and revenue and expense reports. A simple balance sheet (also known as a profit and loss statement), includes inflows and outflows, assets, and liabilities.
When you set up your chart of accounts, link each to a "total" account to allow for this report. Total accounts may calculate on the fly, because they aren't required to crunch too many numbers. However, if you have a large chart of accounts, say over 1,000, you may want to update the total account whenever you update one or more of its child accounts' totals.
An income statement is useful in identifying profitable months from those less so. In Genesis, you can see activity on a monthly basis for each account, giving you a spreadsheet layout that conveys a lot of information all at once (figure 11). In this income statement, you'll see what look like sub-summary breaks in the data. These are records in the file, and the report you see is being displayed while in Browse mode. These records are "title" accounts that don't store data, but serve as dividers categorizing the accounts that fall below each.
Figure 11: The big picture--Genesis gives you a "bird's eye" view of your year-to-date income data, broken down by month.
Another advantage to showing this report in Browse mode is that you can drill down on any month's total per account. If you want to see a report of the activity that made up February's total for Cost of Goods account, just click and there it is.
More options
Hopefully you now know more about general accounting practices and the time needed to build such a system. If it seems too intimidating, consider an established accounting solution. See Debbie Cohen-Sitt's article, "So You're Not an Accountant," on page 44 for tips on integrating FileMaker Pro with some third-party accounting packages.