Create the Income Statement in the Financial Report Painter

Add Calculated Columns to the Income Statement

Your first task is to add three new columns to the report.

Actual and Planned. Rather than including these fields directly from the data source, you will calculate their values using a specialized function called FMLINFO, which will reverse the negative values for revenue in the report output.
Variance. You will reflect the difference between the calculated Actual and Planned amounts in the previous two columns. This information will help Century Corporation plan sales initiatives and spending reductions, as needed.

You will calculate all three columns from the Computes tab of the Report Options dialog box, which you can access directly from the Financial Report Painter.

Place the cursor in the column labeled 1 on the matrix, then select Computes from the Report menu.
The Report Options dialog box opens at the Computes tab.
1. In the Fields input box, name the field Actual, then click the Format button and choose B from the Edit Options list, to display parentheses around any negative values in the output. Click OK to close the Format dialog box.
  You will now create an expression that calculates Actual as a new field in which negative revenue values are converted to positive values.
2. Formulate the expression in the input box using a combination of keys on the Compute calculator, fields from the Field list, which you can access from the Fields button, and type characters:
```
IF FMLINFO('FORVALUE','A7') LT '2500' THEN NAT_AMOUNT * ( - 1 ) ELSE NAT_AMOUNT
```
  Tip: You must type the following part of the expression:
```
FMLINFO('FORVALUE','A7') 
```
  where:
  FORVALUE
  Is the General Ledger account, in this case, GL_ACCOUNT, that you are using as the For field in the report.
  A7
  Is the format of that field, as defined in the Master File.
  Note that the FMLINFO function is only available to calculate values from the Compute tab and is not included on the generic list of functions that you access from the Functions button.
3. Click Apply to add the calculated value to the Fields list. Then click OK to add Actual as a column on the matrix.
4. Click the Save icon.
  Before you repeat this process for Planned, reflect on what FMLINFO does. For accounts less than 2500, FMLINFO multiplies the Actual value by -1, thereby converting the negative value into a positive value for those accounts.
In the FOR field values panel, expand all the accounts with number less than 2500 in For field values panel.
You will see that accounts 2100 through 2450 comprise the Revenue accounts in the hierarchy. Accounts 1000 (Profit Before Tax) and 2000 (Gross Margin) are "net" calculations based on the revenue accounts and, therefore, subject to the same sign conventions. Without the adjustment provided by FMLINFO, all of these accounts would generate negative numbers in the Actual and Planned columns, as illustrated in Displaying Revenues and Expenses Without Adjusting Positive and Negative Values.

So, you need to understand the structure of the accounts hierarchy with which you are working to apply the FMLINFO calculation to the correct accounts.
Place the cursor in the column labeled 2 and choose Computes from the Report menu.
The Computes tab shows your previous expression. Since you will need to use a nearly identical expression to generate the Planned field, select the entire expression and press Ctrl + C (Copy).
1. Click the New button. The expression box is cleared.
  Name the field Planned in the Fields box. Accept the default field format, D12.2.
2. Click in the expression box and press Ctrl + V (Paste). The previous expression is pasted into the input box. Change NAT_AMOUNT to NAT_BUDGET in both places.
3. Click Apply to add the calculated value to the Fields list and OK to add Planned as a column on the matrix.
4. Click the Save icon.
  Create the third column, Variance, and rename it Increase/(Decrease).
Select the Computes icon from the Setup toolbar The Report Options dialog box opens at the Computes tab.
1. Click the New button to clear the input box.
2. Name the field Variance in the Fields input box, then click the Format button and choose B from the Edit Options list, to display parentheses around any negative values in the output. Click OK to close the Format dialog box.
3. Click the Fields button to open the Fields list. Click the Computed Fields folder to see the two calculated values, Actual and Planned.
4. Double-click Actual in the Fields list to enter it in the expression box, type a minus sign or click minus (-) on the Compute calculator, then double-click Planned in the Fields list. Click Close to close the Insert Field dialog box.
5. Click Apply to add the calculated value to the Fields list and OK to add the Variance as a column in the matrix.
6. To change the title of the Variance column to Increase/(Decrease), right-click Variance and choose Options. The Field Properties dialog box opens.
7. Click the General tab, then click the Set Title button.
  The Title dialog box opens.
8. Type the following in the Title dialog box.
```
Increase/
(Decrease)
```
9. Click OK twice, then click the Save icon.

The matrix includes the three calculated columns and appears as follows:

matrix

Tip: If you would like to add these formatting touches, you can right-click Increase/Decrease and select Options. The Field Properties dialog box will open at the Style tab. Then follow the instructions beginning with step 11 from Create the Base Report in Report Painter. When you are done, you will return to the Financial Report Painter.

You have finished structuring the columns in the report and will start adding rows to the matrix.

Add Revenue Rows to the Income Statement

Complete the Revenue section of the income statement.

Your report will display information about several revenue accounts so start by adding a Text row to the matrix, titled Revenue.
1. In row R1, right-click in the Row Type column and select Change Row Type to, and then Text.
  The TEXT dialog box opens.
2. Type the following:
```
Revenue:
```
3. Click OK.
  Revenue appears in the Title column on the matrix.
Make the text row bold.
1. Right-click Revenue and select Options.
  The Field Properties dialog box opens at the Style tab.
2. Make Row the active object (upper-left).
3. Click the Select Font button under Graphical (upper-right).
  The Font dialog box opens.
4. Choose Bold as the Font Style and click OK.
5. Click OK on the Style tab to return to the matrix.
  Row R1 is identified as a TEXT row type, and Revenue is bold.
  
  You are now going to begin to add data values from the Chart of Accounts. The first step is expanding a section of the hierarchy to expose the levels of values.
In the For field values panel, expand account 2000 (Gross Margin), then expand 2100 (Sales Revenue), then 2200 (Retail Sales).
To complete the Revenue section of the report, you will add detailed information for the children in each revenue category, followed by a consolidated row for each category, and finally a consolidated row for all sales revenue.
Tip: Some of these steps will seem very similar to those in the previous tutorial. Be on the alert for places where they are different. Remember that you will be explicitly consolidating data that was pre-consolidated in the data mart.
1. Start by dragging the account 2200 (Retail Sales) from the Chart of Accounts to row R2 in the GL_ACCOUNT column on the matrix.
  Notice that TAG is entered in the Row Type column.
2. Right-click account 2200 and select Row Properties.
  The TAG dialog box opens.
3. Under Children, select Show only children from the drop-down list.
  Notice that Display children's caption is checked by default.
  
  These selections indicate that you want to see values for the children of Retail Sales, (Retail - Television, Retail - Stereo, and so on), but not the value of Retail Sales itself.
  
  In addition, the captions (descriptive titles) for the children will appear in the output in place of their tag values.
4. Click OK to return to the matrix.
  Notice that a plus (+) sign precedes 2200, indicating that it contains subordinate values that you can expand if you wish.
To ensure that you can use the 2200 account and other values in more than one row of the matrix, click the Use Multiple Values check box in the FML Report Properties area above the matrix.
Place an underscore under these values.
1. Right-click in the Row Type column in row R3 and choose Change row type to, and then Bar.
  The BAR dialog box opens.
2. Click OK to accept the default underline character.
Drag account 2200 into row R4 on the matrix.
1. Right-click 2200 and select Row Properties to open the TAG dialog box.
2. Under Children, choose Show selected item consolidated.
  This will roll up the data for the children of Retail Sales and display the total in a single row.
3. Click OK.
  Tip: In the data mart tutorial, you selected Show selected item at this point, since the data you were using was pre-consolidated.
  
  You are going to repeat this process for Mail Order Sales and Internet Sales.
For Mail Order Sales:
1. Drag the account 2300 from the Chart of Accounts into row R6 in the GL_ACCOUNT column on the matrix, making row R5 a blank TEXT row.
2. Right-click 2300 and select Row Properties.
  The TAG dialog box opens.
3. Under Children, select Show only children from the drop-down list.
4. Leave Display children's caption checked and click OK.
5. To add an underline, right-click in row R7 and choose Change row type to, and then Bar.
6. When the BAR dialog box opens, click OK.
7. Drag account 2300 into row R8 to create the summary row.
8. Right-click 2300 and select Row Properties to open the TAG dialog box.
9. Under Children, choose Show selected item consolidated and click OK.
For Internet Sales:
1. Drag account 2400 from the Chart of Accounts into row R10, making row R9 a blank TEXT row.
2. Right-click 2400 and select Row Properties.
  The TAG dialog box opens.
3. Under Children, select Show only children.
4. Leave Display children's caption checked and click OK.
5. To add an underline, right-click in row R11 and choose Change row type to, and then Bar.
6. When the BAR dialog box opens, click OK.
7. Drag account 2400 into row R12 to create the summary row.
8. Right-click 2400 and select Row Properties to open the TAG dialog box.
9. Under Children, choose Show selected item consolidated and click OK.
Add a row that rolls up all of the Sales Revenue data.
1. Drag account 2100 (Sales Revenue) into row R14, making R13 a blank text row.
2. Right-click 2100 and select Row Properties.
  The TAG dialog box opens.
3. Under Children, choose Show selected item consolidated.
4. Leave Display children's caption checked and click OK.
  The Financial Report Painter appears as follows.
  
  The plus signs (+) indicate the presence of children, which you can expose if you wish.
  
  The sigma signs indicate consolidated data.
Save your work and run the report. The output is:

You have completed the Revenue section of the income statement.

Notice that revenues are represented as positive values in both the Actual and Planned columns.

Your next task is to add rows for Cost of Goods Sold and Gross Margin.

Add Cost of Goods Sold and Gross Margin Rows to the Income Statement

You are now going to add a row for Cost of Goods Sold and one for Gross Margin, which subtracts total Cost of Goods Sold from total Revenues.

Collapse the hierarchy for accounts 2000-2400 and expand the hierarchy for account 2500 (Cost of Goods Sold).
Make row R15 a blank TEXT row.
Make row R16 a TEXT row, but this time, instead of opening the TEXT dialog box, type the following text in the Title cell:
```
Cost of Goods Sold:
```
Make the text row bold.
1. Right-click Cost of Goods Sold and select Options.
  The Field Properties dialog box opens at the Style tab.
2. Make Row the active object (upper-left).
3. Click the Select Font button under Graphical (upper-right).
  The Font dialog box opens.
4. Choose Bold as the Font Style and click OK.
5. Click OK on the Style tab to return to the matrix.
Drag account 2500 (Cost of Goods Sold) into row R17 on the matrix.
Notice that Variable Material Costs, one of the children of Cost of Goods Sold, has children of its own.
1. Right-click 2500 and select Row Properties.
  The TAG dialog box opens.
2. Under Children, select Show only children to level and specify Level 1.
  This indicates that you want to display only one level of the hierarchy. The children of Variable Material Costs will not be shown.
3. Click the Consolidate check box.
  This ensures that although the children of Variable Material Costs will not be explicitly broken out in the report, the value on the Variable Material Costs line will reflect a roll up of the values of the children.
4. Leave Display children's caption checked and click OK.
Make row R18 a Bar row.
Drag account 2500 (Cost of Goods Sold) into the matrix in row R19.
1. Right-click 2500 and select Row Properties.
  The TAG dialog box opens.
2. Under Children, choose Show selected item consolidated.
  This will consolidate the Cost of Goods Sold data for all children (including those of Variable Material Costs) in a single row.
3. Leave Display children's caption checked and click OK.
Make row R20 a Bar row.
Drag account 2000 (Gross Margin) into the matrix in row R21.
1. Right-click 2000 and select Row Properties.
  The TAG dialog box opens.
2. Under Children, choose Show selected item consolidated.
3. Leave Display children's caption checked and click OK.
4. On the matrix, click the plus sign (+) to expose the direct children of Cost of Goods Sold.
  The matrix looks like the following image.
Save your work, run the report, and examine the output, which follows.

Notice that Century Corp has exceeded expectations in terms of keeping costs down and can report a healthy Gross Margin for the reporting period.

Add Expense and Profit Rows to the Income Statement

The last section of the income statement defines the expenses of Century Corp during this period and calculates profit before taxes (Profit= Gross Margin - Expenses).

Begin by making row R22 a blank TEXT row.
Make row R23 a TEXT row and type the following text in the Title cell:
```
Expenses:
```
Make the text bold.
1. Right-click Expenses and select Options.
  The Field Properties dialog box opens at the Style tab.
2. Make Row the active object (upper-left).
3. Click the Select Font button under Graphical (upper-right).
  The Font dialog box opens.
4. Choose Bold as the Font Style and click OK.
5. Click OK on the Style tab to return to the matrix.
Drag account 3000 (Total Operating Expenses) into row R24.
1. Right-click 3000 and select Row Properties.
  The TAG dialog box opens.
2. Under Children, choose Show selected item consolidated.
  Profit will be calculated based on total expenses so you need to display consolidated data for each expense category.
Drag account 5000 (Total R + D Costs) into row R26, making row R25 a blank TEXT row.
1. Right-click 5000 and select Row Properties.
  The TAG dialog box opens.
2. Once again, under children, choose Show selected item consolidated.
Separate Expenses from the Profit calculation coming next with a heavier underscore.
Create a Bar in row R27, but this time select ===== in the BAR dialog box.
Drag account 1000 (Profit Before Taxes) into row R28.
1. Right-click 1000 and select Row Properties.
  The TAG dialog box opens.
2. Under Children, choose Show selected item consolidated.
Finish the report with another double underline and save your work.
The lower section of the matrix looks like the following image.
Save your work and run the report.

The consolidated Century Corporation Income Statement shows an Actual Profit that significantly exceeds expectations.

output