Tutorial A-2. Designing the Income Statement Template

This tutorial walks you through the initial steps of creating an income statement. This includes selecting the database tables and writing the formulas. It is used as the foundation for the next two income statement tutorials.

The first step in designing an income statement is to define the sections and categories on the income statement. Ideally, you were given report specifications of what the income statement should look like and you can use this to define the sections and categories. In the sample income statement in Figure A-9, the sections are shown as the rows in bold (Gross Margin, Operating Income, Net Income Before Taxes, and Net Income). The categories are Net Revenue, Cost Of Goods Sold, Selling Expenses, etc. If you weren’t given any specs for what the income statement should look like, you should interview the person who will be reading it.

Since income statements have so much variation between them, it’s unlikely that the accounting database you are using will have a table defining the sections of the income statement. You will need to create a formula that defines each section and return the appropriate one based upon the account number.

For the categories, it’s possible that there is a table that lists the account categories that you can use in the income statement. If not, you’ll also need to create a formula that defines each category and returns the correct one based upon the account number. Fortunately, the Xtreme.mdb database that we are using in this tutorial has a table that can be used to define the categories. The Account Class table breaks down each account into specific categories that are perfect for displaying on the income statement.

Before creating the report, you need to fully understand the database you are reporting from. Identify the tables which hold account information, account classifications and the journal entries. This may even require talking to the database administrator or someone in the accounting department.

Earlier in the chapter, we looked at three accounting databases and discussed their pros and cons. For this tutorial, we are going to use the updated version of the Xtreme.mdb database installed with Crystal Reports XI R2. From the analysis we did previously, we can identify the following tables and fields which will be used on the balance sheet. They are listed in Table A-6.

Table A-6. Necessary tables and fields for the income statement.

The Account Class table is used for grouping the records. The Journal Entry table has the detailed information for creating the main content of the report. The Date field is used for filtering the records. The Amount field will be summed for the account balance.