Create the Income Statement in the Financial Report Painter
The For field, GL_ACCOUNT, is the only column listed
in your report. You will be adding other columns to this report
in the Financial Report Painter.
Initially, the hierarchy is collapsed in the For field values
panel. You will expand various sections of the hierarchy as you
begin to populate the rows of the matrix.
In the rest of this exercise, you will match revenue and expenses
in the income statement, first showing all the revenue earned and
then deducting from the revenue all the expenses (the costs of doing
business) that you incurred in producing that revenue.
xAdd 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.
- 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.
- 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.
- Click Apply to
add the calculated value to the Fields list. Then click OK to
add Actual as a column on the matrix.
- 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).
- Click the New button.
The expression box is cleared.
Name the field Planned in
the Fields box. Accept the default field format, D12.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.
- Click Apply to
add the calculated value to the Fields list and OK to
add Planned as a column on the matrix.
- 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.
- Click the New button
to clear the input box.
- 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.
- Click the Fields button
to open the Fields list. Click the Computed Fields folder
to see the two calculated values, Actual and Planned.
- 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.
- Click Apply to
add the calculated value to the Fields list and OK to
add the Variance as a column in the matrix.
- To change the
title of the Variance column to Increase/(Decrease), right-click Variance and
choose Options. The Field Properties dialog
box opens.
- Click the General tab,
then click the Set Title button.
The Title
dialog box opens.
- Type the following
in the Title dialog box.
Increase/
(Decrease)
- Click OK twice,
then click the Save icon.
The matrix includes the three calculated columns and appears
as follows:
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.
xAdd 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.
- In row R1, right-click
in the Row Type column and select Change Row Type to,
and then Text.
The TEXT dialog box opens.
- Type the following:
Revenue:
- Click OK.
Revenue
appears in the Title column on the matrix.
- Make the text
row bold.
- Right-click Revenue and
select Options.
The Field Properties dialog
box opens at the Style tab.
- Make Row the
active object (upper-left).
- Click the Select
Font button under Graphical (upper-right).
The Font
dialog box opens.
- Choose Bold as
the Font Style and click OK.
- 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.
- 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.
- Right-click
account 2200 and select Row Properties.
The
TAG dialog box opens.
- 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.
- 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.
- Right-click
in the Row Type column in row R3 and choose Change
row type to, and then Bar.
The
BAR dialog box opens.
- Click OK to
accept the default underline character.
- Drag account 2200 into
row R4 on the matrix.
- Right-click 2200 and
select Row Properties to open the TAG dialog
box.
- 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.
- 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:
- 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.
- Right-click 2300 and
select Row Properties.
The TAG dialog box
opens.
- Under Children,
select Show only children from the drop-down
list.
- Leave Display
children's caption checked and click OK.
- To add an underline,
right-click in row R7 and choose Change row type to,
and then Bar.
- When the BAR
dialog box opens, click OK.
- Drag account 2300 into
row R8 to create the summary row.
- Right-click 2300 and
select Row Properties to open the TAG dialog
box.
- Under Children,
choose Show selected item consolidated and
click OK.
- For Internet
Sales:
- Drag account 2400 from
the Chart of Accounts into row R10, making row R9 a blank TEXT row.
- Right-click 2400 and
select Row Properties.
The TAG dialog box
opens.
- Under Children,
select Show only children.
- Leave Display
children's caption checked and click OK.
- To add an underline,
right-click in row R11 and choose Change row type to,
and then Bar.
- When the BAR
dialog box opens, click OK.
- Drag account 2400 into
row R12 to create the summary row.
- Right-click 2400 and
select Row Properties to open the TAG dialog
box.
- Under Children,
choose Show selected item consolidated and
click OK.
- Add a row that
rolls up all of the Sales Revenue data.
- Drag account 2100 (Sales
Revenue) into row R14, making R13 a blank text row.
- Right-click 2100 and
select Row Properties.
The TAG dialog box
opens.
- Under Children,
choose Show selected item consolidated.
- 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.
xAdd 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.
- Right-click Cost
of Goods Sold and select Options.
The
Field Properties dialog box opens at the Style tab.
- Make Row the
active object (upper-left).
- Click the Select
Font button under Graphical (upper-right).
The Font
dialog box opens.
- Choose Bold as
the Font Style and click OK.
- 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.
- Right-click 2500 and
select Row Properties.
The TAG dialog box
opens.
- 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.
- 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.
- 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.
- Right-click 2500 and
select Row Properties.
The TAG dialog box
opens.
- 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.
- 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.
- Right-click 2000 and
select Row Properties.
The TAG dialog box
opens.
- Under Children,
choose Show selected item consolidated.
- Leave Display
children's caption checked and click OK.
- 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.
xAdd 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.
- Right-click Expenses and
select Options.
The Field Properties dialog
box opens at the Style tab.
- Make Row the
active object (upper-left).
- Click the Select
Font button under Graphical (upper-right).
The Font
dialog box opens.
- Choose Bold as
the Font Style and click OK.
- Click OK on
the Style tab to return to the matrix.
- Drag account 3000 (Total
Operating Expenses) into row R24.
- Right-click 3000 and
select Row Properties.
The TAG dialog box
opens.
- 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.
- Right-click 5000 and
select Row Properties.
The TAG dialog box
opens.
- 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.
- Right-click 1000 and
select Row Properties.
The TAG dialog box
opens.
- 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.