Consolidated Income Statement Case Study Task: Add the Percent of Revenue Columns

Note: You can access an existing layout that was created for the One View Financial Statements feature only by using the Manage Layout option on the Work with Statement Definition form. If you exited the Layout Editor after you completed the last set of steps and need to re-access an existing layout, see Consolidated Income Statement Case Study Task: Accessing an Existing Layout (Optional).

Note: Use the steps in this section when setting up the Revenue, Direct Costs, General and Admin Expenses, Other Income and Expenses, and Income Tax sections. Use the steps in Consolidated Income Statement Case Study Task: Add the Percent of Revenue Columns in Subtotal Sections when setting up the Gross Margin, Operating Income, Net Profit Before Tax, Other Income and Expense, and Net Profit or Loss layout sections.

When you planned your consolidated income statement, you decided that you want to see the percent of total revenue for the account balances retrieved. You also want to include a column that shows the percent of change from last year to date to current year to date. Because the JD Edwards EnterpriseOne tables do not store those values, you must set up columns in the BI Publisher Layout Editor to calculate the values.

To create the calculated columns for the percent of revenue, you need to divide the balances in the Current Period Actual, Last Year Actual, Year to Date Actual, and Last Year to Date Actual columns by the total revenue. You created a section in the statement definition for Total Revenue, so you have the total revenue account balances to work with in the layout editor, even though you do not have a separate section on the statement layout for total revenue.

Financial statements often show revenue balances with the sign reversed. You already reversed the sign for revenue accounts in both the Consolidated Revenue and Total Revenue sections when you selected the Reverse Sign check box for the row definitions.

To add the percent of revenue columns for the Revenue section, complete the following steps:

1. Locate the <column name> for the section that you are working with in the Revenue section of the Data Model.

   For example, on the Data Source tab, scroll to the Revenue section and select Current Period Actual from the Rowset subsection.

2. Drag-and-drop the column in the grid, placing it to the right of the existing <column name>.

   You placed the <column name> in the layout in another task. You will not change that column. Instead, you place another copy of the column in the layout so that you can perform a calculation on the new column to get the percent of total revenue.

3. Double-click in the column title area and change the title of the column to % of Rev.

4. Select the cells in the column, and then click the Define Custom Formula icon in the Formula section of the Column tab.

5. In the Function window, select Division.

6. Complete the following steps:

   1. In the Function: Division section, select the Divisor option.

   2. In the Parameter: Divisor section, select the Field option.

   3. In the Field list, scroll to and select the <column name> value from the Total Revenue section of the list.

   4. Click OK on the Function window.

   Your formula for the Current Period Actual column in the Revenue section should look like the following image:

   

7. Click the Arrow for the Data Formatting option on the Column tab, and then select Percent.

8. Change the column width to 50.

   You access the Appearance section on the Properties tab to change the width of the column.

9. Click the Save icon to save your layout.

10. Repeat Steps 1-9 to add the percent of revenue columns for the Last Year Actual, Year to Date Actual, and Last Year to Date columns.

    Substitute the Last Year Actual, Year to Date Actual, and Last Year to Date columns for the Current Period Actual column in Step 1, placing the column to the right of the column for which you are setting up the calculated column.

11. After you have added the percent of revenue column for the Current Period Actual, Last Year Actual, Year to Date Actual, and Last Year to Date columns, remove the grid lines.

    See Consolidated Income Statement Case Study Task: Remove Grid Lines.

Caution: If you need to exit the BI Publisher Layout Editor, do not click the Sign Out icon or you will log out of the server. Instead, click the Close icon (the X in the upper right corner of the window) to exit the window.

After you complete the steps in this task, your statement layout should look like the following image: