- For each field that is displayed on the report, create a formula that returns its value. The formulas that you need to create are listed in Table A-7.
Table A-7. Initial balance sheet formulas.
| Formula Name | Formula |
|---|---|
| Account Class | {Account_Class.Account Class} |
| Account Class ID | {Account.Account Class ID} |
| Amount | {Journal_Entry.Amount} |
- These formulas are the easy ones because they are a direct copy of an original data field. But, we still need to define the sections of the income statement. There are three sections and each section has certain account classes associated with it. The Account Class ID field will define the sub-group within each section. Table A-8 lists each section and the account classes that belong to it.
Table A-8. Income statement sections and the related account classes.
| Section Name | Account Class ID | Account Class |
|---|---|---|
| Gross Margin | 17 | Operating Revenue |
| 19 | Cost Of Goods Sold | Operating Income |
| 20 | Payroll Expense | 21 |
| General & Administrative Expense | Net Income | 22 |
| Interest Expense | 23 | Amortization/Depreciation Expense |
| 26 | Bank Service Charges |
- When writing your own income statement report, I recommend creating a similar table. Make a list of all the sections to be shown on the income statement and identify which account classes belong to each section. If your accounting database doesn’t have a table that lists the accounting classes, you need to map out which account numbers belong to each section. Since there will be a lot of accounts that need this information, it can be helpful to write down the account ranges instead. This will make it easier to write the section formula.
- We’ll create a formula that uses a Select Case statement to return the appropriate Section Name and Section ID. This is determined by the current Account Class ID value listed in Table A-8. Let’s create that formula now.
- In the Field Explorer window, right click on the Formula Fields category and select New. Enter the formula name Section ID and click the OK button.
- . In the Formula Workshop dialog box, enter the following formula:
Select {Account_Class.Account Class ID}
Case 17, 19:
1
Case 20, 21:
2
Case 22 to 26:
3;
- Click the Save and Close button to save your changes.
- Create another formula for the section name. In the Field Explorer window, right click on the Formula Fields category and select New. Enter the formula name Section Name and click the OK button.
- In the Formula Workshop dialog box, enter the following formula:
Select {Account_Class.Account Class ID}
Case 17, 19:
"Gross Margin"
Case 20, 21:
"Operating Income"
Case 22 to 26:
"Net Income";
- Click the Save and Close button to save your changes.
- At this point, you’ve set the record selection formula and created the necessary formulas. Let’s add the report header information before finishing the template.
- Make the Report Header section larger so that it is about a half inch tall. Right-click on it and select Insert Text Object. Place the text object near the top and center it.
- Enter the text Xtreme, Inc. in the text object.
- Right-click on the report header and insert another text object below the previous one. Enter the text Income Statement.
- Drag and drop the {?Year to Print} parameter onto the report header and place it below the previous text object.
- Right-click on the parameter field and select Format Field.
- Click on the Number tab and choose the format -1123.
- Click on all three text objects and center them as well as make them bold.
- Save the report as Income Statement Template.rpt.
You are now finished creating the income statement template and it is ready to be used with the next two tutorials. Unfortunately, since we haven’t placed any of the formulas on the report yet, there isn’t anything to preview. But that’s okay because that is the next order of business.
