12.10. Income Statements

This is the most complex of the client's requirements, and requires some understanding of accounting as well as Access and Excel. First, set up a report format, preferably in a table. Next, link the general ledger accounts to lines on your report. Have a query that calculates the net income, a query for a detailed income statement (by general ledger account), and a rolled-up income statement (by summary line). Finally, automate Excel to produce the income statements, and have an Access report show net income. Look at the example database to see how it was implemented. Again, this is one of the more complex requirements, but it is not impossible to follow.

The complexity of producing an income statement is that you need to take into account timing of the general ledger entries, build the tables to produce the income statement while ensuring that every general ledger account is used, and have checks in place to make sure that the net income ties out.

There was a basic income statement example in Chapter 8 that you can refer to while attempting to build this. For this income statement, I suggest putting in ratios to show margin percentage, variance from last month/year/etc., and a separate column to show each expense as a percentage of total expense and each revenue line as a percentage of total revenue. You could also show each as a percentage of net income. Put in number formats, colors, borders, etc. How you go about this affects how you program it.

Probably the easiest way would be to have a form with a begin month/year and an end month/year. Have an option to show the total of the months or a trend on the report and an option as to whether you want a detailed or rolled-up income statement. For both of these options, your best bet is to use an option group with radio buttons for each.