"Rafael, should the debit side always be equal to the credit side?" This question was made to me years ago, by a young programmer, when I was still a Controller working for a large computer manufacturer in Ottawa. We had been at a meeting discussing the particulars of a journal data entry subroutine for the Company's departmentalized accounting system, for which I was ultimately responsible. I thought at first that she was joking, but soon I realized that she was legitimately concerned, because, even though her training in programming had been very thorough and complete, she had never had any exposure to accounting.
And I also thought that, if she didn't really know that in all cases, the sum total of all debits in a transaction should in fact equal the sum total of all credits in the same transaction, then my balance sheet would look a bit awkward, to say the least. I pondered about this for a while and I began to understand why there was a need for Business Systems Analysts: these guys, at least in Canada in those days, were in charge of acting as a living dictionary between "us", the accounting geeks and "them", the computer geeks. The BSA's knew both bits and bytes and debits and credits and were in charge of interpreting what each group of professionals were saying to the other group.
What has all this got to do with VFP?
The answer to that question has to be: nothing in particular and a lot in general. Why? Simply because accounting and computer systems should not be tied to any one language in particular. You can design a computer system to be written in Fortran, Pascal, Cobol, VFP or Visual Basic.NET. Include accounting routines in it and you would still have a computerized accounting system in place.
However, the love of my life as far as computer languages are concerned is Visual FoxPro and that is why the rest of this article will show examples written in VFP.
How do we include accounting into our system?
First of all, what kind of system are we talking about? Is it a system designed to interpret digitized images from a communications satellite? Or a series of routines to calculate winnings in horse racing? Or a complete stock tracking invoicing system for a business corporation? The answer I believe is the latter. I fail to see how accounting would be of any use to the scientist who interprets the satellite images. So I understand that it is business systems that are more appropriate for the inclusion of accounting subroutines.
Having said the above, let us analyse the type of transactions that we can encounter in a business organization. They are many and they are complex. But basically, business transactions involve purchasing, selling, collecting, paying, budgeting and revenue calculation. Each one of these transactions has typically their expression in accounting.
And in the same way that a computer language has a syntax, constructs, rules, reserved words and the like, Accounting has its own rules, syntax, constructs and key words. The first of these constructs is the Chart of Accounts. Accounts define the transaction in just one or two words, ie Purchases, Sales, Accounts Receivable, etc. And they consist of three parts: the account code part (100), the name part (Purchases) and the amount part (the actual amount of money allocated).
But a transaction could describe the acquisition of goods, or machinery, the outlay of funds or express the amount of a profit or a loss. If we acquire some merchandise or equipment, we are said to add to our assets. If we borrow from a bank we are said to have incurred a liability (we will have to repay the loan in the future). So we are dealing here with transactions that affect our net worth. These are called patrimonial transactions. And the accounts that reflect them are called patrimonial accounts and they simply mean to express an asset or a liability.
A transaction can also show a profit or a loss or express a part of a business operation that normally involves a profit or a loss. A sale should generally bring up a profit (when we deduct the cost of the goods sold). Contrary wise, paying rent is a definite loss, as is the payment of fees.
Therefore, accounts also have a part that places them into any of these categories: an account is either a patrimonial account (assets or liabilities) or a profit and loss account (a result account).
And we can complicate accounts a bit more if we say that some of them are "funds" accounts while the rest is not and, in those countries where there is high inflation and the accounting principles call for inflation adjustments, adjustable or non adjustable accounts.
Since this is not by any means an accounting theory article, I will only say that what I mean for "funds" accounts are all those which represent bank accounts or cash. This distinction becomes useful when we design a payments form and only show the cash and bank accounts for the user to choose from.
So when we as programmers are told by the Company accountant that he needs a data entry screen for the Chart of accounts we should not fret. He just wants us to show a screen that will enable him or her to add, edit or delete records in a very simple table: the accounts table which, in VFP I usually design thus:
Accounting principles
As said above, Accounting has a series of rules that should be followed in order to register all business transactions. And these rules, which were first put forth by a Venetian monk (Luca Paccioli) in the middle ages are the ones that we as programmers have to follow strictly, if our system is to be of any use to the Company Accountants.
I am not going to write them all here, but will simply quote a few:
I realize that this is a bit confusing but can be clarified with examples such as:
If I receive money from the bank, I am both acquiring an asset (cash) and incurring a liability (I will eventually have to repay the loan).
Another example: if I purchase goods I can either pay for them in cash or promise the vendor to pay at a later time. So I am acquiring and asset (goods) and disbursing assets (cash) in the first example or incurring a liability (put my debt into a current account with my supplier)
The above transactions involve a series of positive (incoming assets) or negative (outgoing assets) movements in our patrimony, which the accounting rules force us to express thus:
Account debits credits ------- ------ ------- 100 Cash 10,000 200 Bank loans payable 10,000 when we receive the 10,000 monetary units loan from the Bank 300 Purchases 50,000 100 Cash 50,000 (if we buy for cash) or 300 Purchases 50,000 201 Accounts payable 50,000 (if we will pay at a later date)
As can easily be seen, every transaction has two parts: one involves the reception of something and the other one expresses the obligation to pay or the outlay of part of our assets, in exchange for what we received.
The above constructs are called accounting entries and the debit side of them always equals the credit side. Those are very simple entries, but transactions in real life are a bit more complicated: some of them involve additional expenses, such as taxes, freight charges, interest, penalties and so on. A quick example would be the value added tax charged in some countries in every sale transaction, which would be shown in the following entry:
110 Accounts receivable 12,100 210 Value Added Tax payable 2,100 500 Sales 10,000
In this last example, if you add both accounts in the credit side, the total equals the debit side.
The process of writing accounting entries and allocating a particular transaction to a set of accounts, as shown in the above examples, is called posting. And posting is done daily, for every transaction, into a special book, called General Journal, seen in fig 4 below.
Now, if you could look at a General Journal listing, you would probably notice that there are repetitive entries, posting transactions to the same accounts. For instance, a sales invoice originates an entry such as the one shown in the last example above. But a company makes many sales daily, issuing a corresponding number of invoices, one for each sale. And accounting principles call for the registration of each individual sale into the General Journal, or rather, the production of an accounting entry for each invoice, which is then registered into the General Journal.
A listing of all the transactions (invoices in our example) posted to the Sales account, called a General Ledger sheet for the Sales account, would look like this:
A similar G/L sheet would be generated for each account shown in all the accounting entries.
Lastly, if we produce a listing of all the balances of each account at any one point in time, we would be obtaining a Trial Balance (fig 7).
The Company Accountant then produces the final Balance Sheet of the company, after a number of reclassifications which are beyond the scope of this article. His source for this work is the Trial Balance.
Now if you look at all the examples shown above, you will see that "the debit side is equal to the credit side".
How is all of the above put into a computerized accounting system?
In Visual FoxPro we would need a few tables to put a very simple accounting system in place, namely:
Some people would think that this is oversimplifying things, that accounting is far more complex than that, but let me assure you that all you need is a few data entry screens and a lot of reporting routines, involving forms with grids, reports in different formats and the information recorded in the Journal and Details tables. You don't believe me? Well, let us see.
If you look at figure 2, the Accounts data entry screen, you will see that this is a very simple edit screen, involving two tables, the Accounts and the Groups tables. The latter being the one that classifies the accounts into different categories, as described above.
Once you have the accounts table complete with all the accounts to be used by the accounting system (the Company Accountant is your primary source of information as to what goes into this table), you are ready to start producing accounting entries.
You can produce manual accounting entries, using the screen shown in figure 4, or you can let your system produce an accounting entry automatically, every time a transaction is originated. For instance, every time you issue an invoice, an entry to show this transaction is automatically generated by the system.
The entries (manual or automatic) produced, are automatically recorded ("posted") into the Journal table. That is all there is to it. The Journal table has the structure shown in fig 8.
This table receives all the accounting entries, line by line, each record representing one line of the entry. The Type field can only receive one of the following values ("D" for debit or "C" for credit). In addition, if an amount is a debit, it will be recorded in the Amount field as a positive number and, if it is a credit, it will be recorded as a negative number. This might seem redundant and unnecessary, but it makes things very easy when reporting. Sometimes, redundancies help the programmer, even though normalization rules are not strictly adhered to, as in this case. We shall see this clearly when producing general ledger reports, later on)
OK, we want to show a listing of all entries? Very easy. Just make a report from the data in the JOURNAL table. Now we want to show the General Ledger sheets of all accounts. Also very easy. Just make the appropriate report, using the JOURNAL table. (fig 9 and fig 10)
Automatic postings
There are probably several ways to make automatic postings. These are all dependant on the type of transaction to record. In my case, I chose one that involves marrying totals in certain parts of a screen to accounts in the chart of accounts.
The Screens table (fig 11) has one field that identifies the data entry screen the postings come from, another field to identify the totals to be allocated to the specified account, a field for the account code and a field to determine whether the posting will be a debit or a credit.
Let us assume we are entering suppliers invoices. There are many formats for invoices a Company can use, but all of them involve details of what is being invoiced (item codes, quantities, unit prices), invoice subtotals and several other amounts for taxes, freight, duties, etc.
A very simple supplier invoice could look like the one shown on figure 12.
Now the question is: what should be posted to the accounts? In other words, should all the items be posted, or just the subtotals or what?
The invoice shown could originate an entry as this one:
Entry # 1234 Date Feb 27,2007 Code Account description Debit Credit -------------------------------------------------------------------------------------------- 11801/105 Purchases - building timber 2800 11801/106 Purchases - timber (sundries) 700 11000 Value Added Tax withheld 735 20002 Accounts payable - Foreign suppliers 4235 ------------------------------ Totals 4235 4235 =============== International Importers Inc Inv # 44334
If we examine the invoice, we see that the tax and the final amount payable to the supplier are directly shown in the invoice. They are one single concept each and can be easily assigned an account, namely Accounts payable to the invoice total and Taxes withheld to the tax amount. However, each detail line needs a different allocation, because the Company uses a different account for purchased goods, to be resold unchanged, and raw materials, to be incorporated as part of a manufacturing process.
The user recording this invoice would then have to enter each detail account manually and let the system post the tax and invoice total automatically.
This is done with the help of the SCREENS table which would look like this:
Screen Line Account Type ----------------------------------------- 1 1 99999 1 2 11000 D 1 3 20002 C
The screen number designates the data entry screen for suppliers invoices. You can have several data entry screens, like the one for invoices issued by the Company, the one used to enter collections from customers, the one for payments made, etc. Each one of these screens would have a sequential number. I chose, for the sake of this example, to assign number 1 to the supplier invoices data entry screen. Lines 2 and 3 designate both the Value Added Tax (a special tax in Argentina) and the invoice total, allocated respectively to accounts 11000 and 20002. Because it cannot be known in advance how many detail lines an invoice will have and which accounts every item will be posted to, I use a general purpose account number (99999) and leave the type field blank.
Recording the automatic postings
When the user presses the OK button after completing the data entry form, the save routine in the form will scan the grid (its underlying record source, that is) line by line and will use the account the user enters manually for that line, to post the amount into the JOURNAL table. As a convention, all amounts in the detail lines will be posted as debits. After the details lines have been posted, the save routine will then examine the header table (remember, this is an invoice, consisting of two cursors, the header cursor containing all totals, the invoice number, date, vendor account, etc., and the details cursor, containing all invoice items, prices, quantities, descriptions). As I was saying, the header table is then examined and the SCREENS table will we examined, to locate line 2, picking the account number for the tax and then line 3, picking the account number from that line, for the invoice total.
Some considerations
The above technique requires hard coding of the "lines" in the save routine of the form. In other words, the program will look for the account in line 2 and use it to post the amount obtained from the tax textbox. Likewise, it will look for line 3 to post into the selected account the amount from the Totals textbox. I realize that this is not a very good technique, because one should never hard code anything. One way to avoid hardcoding could be to tie the name of each textbox to the line field of the SCREENS table. In other words, instead of using numbers for those lines, you could use the textbox name. In any case, if you ever decide to change the textbox name on the form you would have to change its reference in the SCREENS table. But that seems to be a minor problem.
Conclusion
Accounting can be easily included in commercial software. All that is required is a bit of accounting knowledge and a deep understanding of the company's business rules. And to work in close connection with the Company Accountants.
In a future article I will show in detail how to enter accounting entries manually and how to produce the different reports, using grids on forms and regular reports with the report writer.
