Chapter 15Analyzing Profit Centers |
| About This Chapter |
This chapter describes how to create the company's Profit Centers' Income Statement reports, and includes the following sections:
F Overview, page 286, introduces the ProfitCenter concept.
F Adding Profit Center Details to the Trial Balances Data Worksheet, page 287, describes how to add a ProfitCenter details in a new column to the Trial Balances Data.
F Analyzing Profit Centers Using PivotTable Reports, page 291, describes how to create PivotTable reports to analyze ProfitCenter by ProfitCenter while presenting summary balances for Account Name and Account Type.
F Creating an Income Statement to Present Each Profit Center's Results, page 294, describes how to create Profit Center Income Statement reports.
F Printing Profit Center Income Statement Reports, page 304, describes how to automate the printing of ProfitCenter Income Statement reports.
Adding ProfitCenter Details to the Trial Balances Data Worksheet
Ø To add Profit Centers' names in a new column:
F In worksheet 21 – Trial Balances Data, add a new column (column F below), and then enter the Profit Centers' names into the appropriate cells in the column.
If the account number in column A contains within its structure the applicable Profit Center number, the last digit in column A (the account numbers) indicates the Profit Center name, for example, 1 is for Construction, 2 for Hotels and so on.
As shown in Figure 15‑1, you can use a formula (nested within the INDEX formula) to return the ProfitCenter name to each cell in column F.
Figure 15‑1: Return Profit Center Name Formula
F The RIGHT formula (nested within the INDEX formula) returns the last digit of the account number in cell A2.
F In worksheet 13 – Parameters & Calculations, range A20:A25 includes the Profit Centers List (the Name defined for the range is ProfitCenterList, as shown below.
F The INDEX formula returns theProfit Center name from the defined range Name ProfitCentersList.
Defining a Name for the Trial Balances Data Table
Ø To define a dynamic range Name for the Trial Balances data table:
1. Select worksheet 21 – Trial Balances Data.
2. Select any cell in the data area and press <Ctrl+Shift+*>.
3. Press<Ctrl+F3> and enter TB_Data in the Names inWorkbook box.
4. Enter the following formula in the Refers To box:
=OFFSET('21 - Trial Balances Data'!$A$1,0,0,COUNTA('21 - Trial Balances Data'!$A:$A),COUNTA('21 - Trial Balances Data'!$1:$1))
5. ClickOK.
To learn more regarding defining dynamic range Names and the OFFSET formula, refer to Chapter 2, Adjusting the Trial Balance.
Saving Filtering Profit Centers Criteria Using Custom Views
Ø To save the Profit Center filter criteria:
1. From the Data menu, select Filter and then AutoFilter.
2. From the dropdown list at the top ofcolumn F, choose the first Profit Center name.
3. In the Custom View box, type the name of the Profit Center and click OK twice.
4. Repeat steps 2 and 3 for all of the filtering criteria and save each one in Custom View, and then save Financial Statements.xls workbook. Your results should look like this:
Analyzing Profit Centers Using PivotTable Reports
Figure 15‑2: PivotTable Report Presenting the Account Names Balances Summarized by Profit Centers
Figure 15‑3: PivotTable Report Presenting the Account Type Balances Summarized by Profit Centers
To learn how to create PivotTable reports, refer to Chapter 13, Analyzing Financial Statements Using PivotTable and PivotChart Reports.
The PivotTable data source for the two PivotTable reports presented in the two previous figures is TB_Data, which is the Name defined for the Trial Balance data table, as described in Defining a Name for the Trial Balances Data Table, page 289.
Ø To create two PivotTable reports that use the source of the range:
1. Create a single PivotTable report. Then copy the worksheet containing the PivotTable report by right-clicking the worksheet's tab and select Copy or Move from the shortcut menu.
2. Modify one PivotTable report to summarize the data from TB_Data based on Account Name (as in Figure 15‑2, page 291) and the second PivotTable report based on Account Type (as in Figure 15‑3, page 291).
Why Use Two PivotTable Reports?
Excel's powerful and innovative PivotTable report feature greatly facilitates data analysis. It quickly and easily summarizes the data from the data source, and the results are updated by refreshing.
However, you will need more than one PivotTable report to present summarized data in a different subtotals levels. To do this you will have to duplicate the PivotTable report as many times as required by copying the worksheet where the PivotTable was created and changing the Field structures to present a new report.
The next step in creating Income Statement reports for each Profit Center is to define Names in the two worksheets where the PivotTable reports created.
Defining Names
Ø To define Names in the PivotTable report that presents summarized balances for the Account Name as shown in Figure 15‑2:
1. Define the Name PT_Table_AccountName for the worksheet by pressing <Ctrl+A> to select all cells in the worksheet.
2. Press <Ctrl+F3> to enter the Name into the Names in Workbook box.
3. Define the Name PT_Titles_AccountName to row 2 of the worksheet (the title line).
4. Define the Name PT_ColA_AccountName to column A of the worksheet.
Ø To define Names in the PivotTable report that presents summarized balances for the Account Type, as shown in Figure 15‑3:
1. Define the Name PT_Table_Level3 for the worksheet by pressing <Ctrl+A> to select all cells in the worksheet.
2. Press <Ctrl+F3> to enter the Name into Names in Workbook box.
3. Define the Name PT_Titles_Level3 to row 2 in the worksheet (the title line).
4. Define the Name PT_ColA_Level3 to column A of the worksheet.
Creating an Income Statement to Present Each Profit Center's Results
F One Combo Box enables you to choose the Profit Center name.
F The second Combo Box enables you to choose the reporting month.
Figure 15‑4: Summary Income Statement for a Profit Center
Refer to Chapter 5, Income Statementfor information about how to create Income Statements. The worksheet 41 – Income Statement is used as the base for adding and modifying Profit Centers Income Statement reports.
Ø To add a second Combo Box and Profit Center name:
1. Copy worksheet 41 –- Income Statement by selecting its tab and dragging to a different location while pressing and holding the <Ctrl> key. Rename the copied worksheet to 42 – IS_PC(IS = Income Statements, PC = Profit Centers).
2. Delete row 10 onward.
3. Add two columns and insert formulas to return the percentage calculation, Sales=100% (see columns C and D in Figure 15‑4, page 294).
4. Select worksheet13 – Parameters & Calculations.
5. In cells20 to 25 in column A, type the names of all Profit Centers included in the Consolidation Report, select the list range and define the NameProfitCentersList (see below).
6. Select cellB16 and define the NamePC_Number (the cell link to the new Combo Box).
7. Enter the following formula into cell B17:
=INDEX(ProfitCentersList,PC_Number)
The formula will return the Profit Center name from the Profit Center list in cells A20 to A25.
8. Select the worksheet42 – IS_PC.
9. Copy theCombo Box containing the month's list and paste it aside.
10. Select the copied Combo Box, then right-click and select Format Control from shortcut menu.
11. Selectthe Control tab.
12. In the Input Range box, replace the MonthsList reference with ProfitCentersList.In the Cell Link box, replace the MonthSelectionNumber referencewith PC_Numbers, as shown.
13. Click OK.
14. Select cell A8 and insert the formula=PC_Name, as shown below.
Creating Summary and Detailed Profit Center Income Statement Reports
Ø To create two types of Profit Center Income Statement reports, a Summary report and a Detailed report:
1. Add an Accounts Types list (to the white cells in column A – cells A19, A23, A30:A31, A38) and Accounts Names list into cells in column A (to the cells colored gray in column A).
2. Use the Group and Outline symbols, as follows:
F Select rows 12-22 (as shown in the previous step).
F From the Data menu, choose Group and Outline and select Group.
F Repeat twice and group Marketing expense Accounts Names by selecting rows 27-29, and General expense Accounts Names by selecting rows 32-37.
3. Insert the formulas that return summary balances from the PivotTable report, as follows:
F Into cells in columns B and D, insert the formulas that return the summary balances from the PivotTables report, according to the Account Types and Account Names you entered to cells in column A.
v The first formula returns the summary balances to Accounts Types from PivotTable report Level3 in column A (see Figure 15‑3).
v The second formula returns the summary balances to Accounts Names from PivotTable Account Name in column A (see Figure 15‑2).
15‑5: IF Formula
Building the Formula Step-by-Step
15‑6: If Function with Two Nested INDEX Formulas
In the Profit Centers list (shown on page 288), five Profit Centers and one that will present the totals balances to the whole five, the Consolidated Report.
Choosing one of the first five items from the Combo Box list (which is the Profit Centers list), causes the formula in the Logical test to calculate and return the result from the third argument in the IF formula.
Figure 15‑7: PivotTable Report Presenting the Account Type Balances Summarized by Profit Centers
Second Argument in the IF Formula, the INDEX Formula
Enter the INDEX formula to the Value_if_true argument of the IF formula:
=INDEX(PT_Table_Level3,MATCH(A11,PT_ColA_Level3,0),10)
Figure 15‑8: INDEX Formula in the Second Argument of the IF Formula
F Array (first argument Figure 15‑7): Name defined for the worksheet that contains the PivotTable report, as shown in Figure 15‑3, page 291.
F Row_num (second argument):Returns the row number incolumn A for the Account Type in cell A11, in this example, for Sales.
F Column_Num (third argument): Enter the column number, which is column L in the PivotTable report worksheet, as shown in Figure 15‑3, page 291.
Third Argument in the IF Formula
Enter the INDEX formula to the Value_if_false argument of the IF formula:
=INDEX(PT_Table_Level3,MATCH(A11,PT_ColA_Level3,0),MATCH(PC_Name,PT_Titles_Level3,0))
15‑9) is the same form as that entered to cell B11. The differences are that the Names defined to the PivotTable report contain summaries of the Account Name:
F Name defined for the PivotTable worksheet: PT_Table_AccountName
F Namedefined for column A: PT_ColA_AccountName
F Name defined for the titles: PT_Titles_AccountName
Figure 15‑9: Formula That Returns Summarized Balances for Account Name
Printing Profit Center Income Statement Reports
Ø To add a Custom View for the Summary Income Statement report:
1. Hide the detailed rows by clicking 1 from the three Group and Outline level numbers.
2. Select and set the print area.
3. Add a Custom View, as shown in the figure below. (For more details, refer to Saving Filtering Profit Centers Criteria Using Custom Views, page 290.)
Ø To add a Custom View for the Detailed Income Statement report:
1. Unhide the detailed rows by clicking 3 from the three Group and Outline level numbers.
2. Select and set the print area.
3. Add a Custom View, as shown in the figure below. (For more details, refer to Saving Filtering Profit Centers Criteria Using Custom Views, page 290.)
Automating the Printing of Profit Center Reports
The printing of Profit Center reports can be automated by adding two macros to a regular module:
F The first macro (see below) prints Summary Profit Center reports, including the Consolidation report.
F The second macro (see below) prints Detailed Profit Center reports, including the Consolidation report.
Operate the macros from the Custom Menu you created, according to the instructions in Chapter 8, Customizing the Financial Statements.xls Workbook and Presenting Information.
Figure 15‑10: Finished Custom Menu