Use PivotTable

to create and analyze Financial Statements In the previous chapters you created financial statements using formulas that returned data from the database worksheet containing accumulated balances of the General Ledger Trial Balances. In this chapter, you will use PivotTables to analyze financial data and return summarized data to financial statements. The PivotTables technique, which analyze data in Excel, is one of the quickest and best techniques used for data summing, receive immediate answers to complicated queries and for decision making. With PivotTables you can summarize data, compare data between months, quarters and years, summarize data by using various functions and style, create quick graphs and return data to reports created in regular Excel worksheets. This chapter contains examples and step by step instructions on how to create PivotTables and analyze and summarise the Trial Balances figures. The techniques to save the accumulated Trial Balances In this chapter you will create PivotTables from data ( Trial Balances ) saved in Excel worksheet using two different techniques: 1. A worksheet in a workbook containing monthly accumulated Trial Balances in adjacent columns.

2. A worksheet in a workbook containing vertical sequence of monthly Accumulated Trial Balances in adjacent rows. The technique used to save accumulated Trial Balances in a worksheet has been described in chapter 4.

What is the essential difference between the two techniques Field names in the PivotTable (see Field buttons on the right side of the figure below ) are determined by the text entered in the top cells of each column in the data table which is the data source for the PivotTable. The PivotTable Fields are divided into two categories: 1. Data Fields
2. Query, filtering and sorting Fields.

Figure 1 - Before Field sorting Figure 2 - After Field sorting

The number of Data Fields in a PivotTable, when using the first technique ( Accumulated Trial Balances are in adjacent columns in the worksheet ) equals to the number of Trial Balances in columns in the worksheet. When using the second techniqe ( Accumulated Trail Balances are in adjacent rows in the worksheet), a PivotTable has only one single Data Field created from a single column of the Trial Balances. There are advantages and disadvantages to each method. This chapter contains examples of PivotTables created from both techniques. Creating a PivotTable
The technique used to create a PivotTable is similar to the two methods used to save Trial Balance data. Additions and modifications resulting from the data saving method will be described afterwards.

1 - Define Name to the range of source data

Select the TB_data worksheet, select cell A1 and press Ctrl+* to select the data table.
Press Ctrl+F3.
In the Names in wordbooks box: Type TB_data.
In the Refers To box: enter the following formula: =OFFSET(TB_data!$A$1,0,0,COUNTA(TB_data!$A:$A),COUNTA(TB_data!$1:$1))
Click OK.
2 - Create a PivotTable
Add new worksheet by pressing Shift+F11, and change the sheet name to PivotTable.
Select the PivotTable worksheet, select cell A1.
From Data, select PivotTable and PivotChart Report.
In step 1 of 3, make sure that the Microsoft Excel list or database box is checked.
Click Next.
In step 2 of 3, check the Range box: Press F3, select the name TB_data and click OK.
Click Next.
In step 3 of 3, select cell A1 and click Finish. 0. Adding Fields to the PivotTable
From the PivotTable Field list, drag the query Fields to Page (upper left side). Query Fields are all of the Fields which are not defined as Data Fields.
Drag the Data Field or Data Fileds to Drop Data items Here.

Part A
Creating a PivotTable and analyzing data using the first technique: Accumulated Trial Balances are in adjacent columns in a worksheet The PivotTable technique returns filtered and summed data used for two purposes: * Returning summary data to prepared reports such as Financial Statements. * Data analysis for business decision making and immidiate answers to queries.

2. 2. Creating a PivotTable that returns summary data to prepared reports such as financial statements

Create and save a PivotTable that retains a fixed structure of Fields, which is not to be used for data analysis, but rather for data summaries only and eliminating the requirement to enter many formulas, and to return the summary figures to the reports created in regular Excel worksheets. All unused Fields have been removed from the PivotTable so that the table structure and Field position in the worksheet will not be changed when the PivotTable is refreshed, particularly as a result of adding new Fields to the original source data. Returning data from the PivotTable to Financial Statements Save the PivotTable, in the structure described above, and you might want to hide the PivotTable worksheet for security purposes. Before hiding the worksheet containing the PivotTable, define Names to be used. The Names defined:
1. Select the PT_data worksheet ( where the PivotTable located ), press Ctrl+A, press Ctrl+F3 and define the Name PT_data. 2. Define Names for the first three columns: o Select column A, press Ctrl+A, press Ctrl+F3 and define the Name PT_Level1. o Select column B, press Ctrl+A, press Ctrl+F3 and define the Name PT_Level2. o Select column C, press Ctrl+A, press Ctrl+F3 and define the Name PT_Level3. 3. Select row 2 ( with the titles above the columns ), press Ctrl+F3 and define the Name PT_Level3. Select the Balance Sheet worksheet.
This worksheet contains a reporting month selection Combo Box. Column A contains cells with a list of the Financial Statement sections and clauses, and in the cells of columns C and D are formulas that return the summarized figures from the PivotTable according to the selection of the reporting month and text recognized in column A.

5. 5. 5. 5. 5. 0. 0. 0. 0. 0. 0. 0.

0. 0. * * 3. 3. 3.

The formula in column C cells:
=INDEX(PT_data,MATCH(A12,PT_Level3,0),MonthNumber+3+12) The formula in column D cells:
=INDEX(PT_data,MATCH(A12,PT_Level3,0),MonthNumber+3)

The second argument of the Index formula contains a nested Match formula that return the cell number where the text Cash is located in column C ( level3 ) in the PivotTable worksheet (see PivotTable figure in previous page), the cell number is 4.
The third argument of the Index formula calculates the column number of the reporting month in the PivotTable. This calculation is a result of the MonthNumber you select from the months list in the Combo Box. MonthNumber is the Name of the cell linked to the Combo Box. 3 are the three first columns in the PivotTable worksheet, while 12 is the number of columns for the year 2002. Automatically refreshing the PivotTable Data in PivotTables is not automatically linked to cells in the source data. When updating the source data you must refresh the PivotTable to transfer the data from the source data to the computer memory (Cache), the PivotTable is automatically updated from the Cache memory. Add an Event to the TB_data worksheet to automatically refresh the PivotTable when a new Trial Balance column is added or data is updated or corrected in the TB_data worksheet. The refresh will be performed after leaving TB_Data worksheet when selecting a different worksheet in the workbook. 1. Press Alt+F11 to open the VBE. 2. In the Project- VBAProject, Double click the TB_data worksheet( see the figure below). 3. From the left scroll box (above the module sheet (General)) select Worksheet. 4. From the scroll box on the right (above the module sheet) select the Deactivate Event. Enter the following code to the Event: Sheets("PT_Data").PivotTables("PivotTable1").PivotCache.Refresh
Analysis and summary of monthly, quarterly and yearly data You can easily summarize by months, quarters and years the figures from the accumulated Trial Balances data using the PivotTable. You can use some of the columns to create few different PivotTables. For example, you can use the December columns of each year to create a PivotTable used to summarize and analyze the yearly data. Use each third column to create reports for quarter data analysis based on calender or fiscal reporting year. In the PivotTable shown in the figure, see data analysis and comparison between the years 2002 and 2003.
Comparison and analysis Profit and Loss statements between years
The PivotTable ( see the figure above), contains 8 columns in the data area, where each year has 3 data columns and two additional columns used to calculate the appreciation (decrease) between years.
Creating the PivotTable step by step Step A Note: See the begining of this chapter for instructions how to create a simple PivotTable , structure and Field names.
Create a PivotTable containing two data Fields:
December 2002 .
December 2003.

Format and modify Field name.
To format and modify the Field, select

        any cell in the Field column,
        For example, select cell B8.
1. Right click and select Field Setting,
         From PivotTable toolbar.
2. In the Names: box Type 2002
         (and 2003 in the second Fileld).

3. Click the Number... button and select the format. 4. Click OK.

Step B
Add two more December 2002 and December 2003 Fields to the data area in the PivotTable. To add the Fields, open the PivotTable Field List window ( first icon to the right on the PivotTable toolbar). From the PivotTable Field List window, drag each Field two more times to the data area of the PivotTable.

See figure for result.

Organize the Data Fields in two groups. Three Fields for 2002 and three Fields for 2003. The technique used to move the Field position in the PivotTable. Select the title cell Field, for example cell D7 ( in the figure above) and drag, using the mouse, the Field to a new location in the data area.

Step C
Change the calculation method of the Fields you added. Field 2 - calucation of running balance in the years 2002 and 2003 1. Select a cell in one of the new dragged data Fields, the December 2003 Field. 2. Click the icon Field Settings in the PivotTable toolbar. 3. In the PivotTable Field of the Name: box, type: Running Balance 2003. 4. Click Options and select Running Total from Show data as: 5. in Base Fields: Select the Field positioned in the PivotTable as a row, BS, P&L Level3. 6. Repeat steps 1 to 5 and modify the second December 2002 Field.

Field 3 - % of December 2002 and December 2003. Calculating % of the Profit and Loss clauses according to sales revenue. 1. Select a cell in one of the new dragged data Fields, the December 2003 Field. 2. Click the icon Field Settings in the PivotTable toolbar. 3. In the Name: box, type the text % Of Sales 2003. 4. In the PivotTable Field, select Of % from Show data as: 5. Select the BS, P&L Level3 Field, from Base Filed. 6. Select Sales in Base Items:.
7. Modify the design of the percent value: Click Number..., in the Format window, select Custom from the Type: box Type the design 0.00% ;[RED](0.00) % ( positive percent colored black, negative percent colored red ) and click OK. 8. Click OK.
9. Repeat steps 1 to 6 and design the last Field of the year 2002.

Step D
Add two columns for analysis and comparison between years 1- Add a calculated Field containing a formula that calculate the difference between the two years 1. Select a cell in one of the PivotTable data area. 2. From the PivotTable toolbar select from the PivotTable icon, Formulas, select Calculated Field. 3. In the Insert Calculated Fields window in the Name: box Type a name for the formula: 2002 VS 2003.

4. In the Fields: windows select the Field December 2003 and click Insert Field. 5. Enter a minus (-) sign in the Formula box. 6. Select the Field named December 2002 and click Insert Field. 7. Click OK.

2 - Calculating the difference in percents between years by adding a calculated Field Repeat the technique explained
In section 1 and add a calculated
Field containing a formula that
calculates the changes
( increase / decrease ) in percentage
between the years (see the figure).
The final result:

Step E
Group Profit and Loss clauses,
common size income statement

The PivotTable containg summary of level 3 in the Profit and Loss clause does not provide important subtotal calculations such as Gross Income and a calculation of the Gross Profit and additional essential calculations required to analyze firm business results. Add summary levels - group the clauses by subjects and add a new Field to the PivotTable. 1. Copy the worksheet containing the PivotTable created in step D (previous) to a new worksheet, click the worksheet tab, press CTRL, drag the worksheet tab and release the CTRL and the mouse at the same time. 1. Select the two following 2. clauses: Sales, Cost of goods sold 2. Right click and select Group and Show Detail, 3. Choose Group.

4. Select additional cluases and perform Group to the Operating Expenses sections and to an additional group of sections. 5. Drag the Field BS, P&L Level3 to the Page area (upper corner above the PivotTable). The final result:

Creating a graph to compare data between years Select a cell in the data area of the PivotTable and press F11.

The graph is created in a new worksheet in the workbook. Add the option Show Data Table to the graph. * From the Chart menu, select Chart Options. From the Chart Options window, select the Data Table tab, select show Data Table Option and click OK. Common Size Balance Sheet
Analyze the Balance Sheet, add Fields to the Data area and new calculated Fields. In the PivotTable (in the figure), see total summaries and subtotals according to three levels:

The PivotTable does not contain data Fields with ratio calculations. Check the figure (above the last column), The totals in percentage at the lines Assetes Total and Equity & Liabilities Total do not end with the total of 100%. To add a Calculated Field containing a calculation of the subtotals and balance items in percents related to the total Assets and Equity & Liabilities, copy the PivotTable worksheet to two new additional worksheets. 1. Select the worksheet tab in the worksheet containing the PivotTable, press Ctrl and drag the worksheet to a different location in the workbook. 2. Drag the three summary level Fields from Page area to Row area, as shown in the figure. 3. From the PivotTable Field List windows ( if the window is not visible, click the last icon at the PivotTable toolbar ), Drag the Field December 2002 and the Field December 2003 to the data area In the PivotTable. 4. Select a cell in in the Field December 2003, Click Field Settings icon ( in the PivotTable toolbar) and format the Field, as shown in the figure.

5. Repeat steps 1-4 to design the Field December 2002.

To view the result, see the PivotTable in the figure containing Assets data and a second figure containing Equity & Liabilities data.

Compare quarterly figures
To create a PivotTable to compare the quarter's figures, you will have to add to the PivotTable created (or will be created) the quarterly month's columns: March 2003, June 2003, September 2003 and so on. Modify column titles to Q1 2003, Q2 2003, Q3 2003 and so on. See the results in the figure:

To analyze the compareble figures between the quarters, add calculated Fields and format according to the techniques used to compare years compareable figures. Part B
Creating a PivotTable and analyzing Trial Balance figures organized in a worksheet in adjacent rows

Technique used to save Trial Balance figures in vertical sequence 1. Complete the adjusted Trial Balance, using the techniques explained in chapter XXX. 2. Copy the Trial Balance and paste it to a new temporary worksheet. 3. Add a column containing the month date of the Trial Talance by inserting a column after Column E (see column F in the figure above). 3. Copy the 4. rows from row 2 and unde, select row 2, Press Ctrl+Shift+Down Arrow and then press Ctrl+C. 5. Select the worksheet containing the saved Trial Balances. 6. Select the first empty cell in column A under the previous month's Trial Balances. 7. Press Enter and paste the new Trial Balance. 8. Copy the formulas in columns C:E to cells in rows of the Trial Balance added.

Analyzing and summarizing the figures of the Trial Balances in month, quarter and year levels

The end date of the year is the 31st of December of each year ( calander year ) Drag the Field Month from Page area to Row area, on the left side of the PivotTable. 1- Select one of the cells in the Field Month, for example A10. 2- Right click and select Group from Group and Show Details. 3- In the Grouping window, select Months, Quarters, Years and click OK.

Drag the Field Years from Row area to Column area within the PivotTable, See result in the figure below.

Adding a Calculated Fileld in percents to the PivotTable : 1- Drag the Field Sum from PivotTable Field list to the data area.

Note:
If the window PivotTable Field list is not visible, click the first icon on the right of the PivotTable toolbar. 2- In the PivotTable Data area, select the cell in the new added SUM Field. 3- Click the Icon Field Settings (second from the right) in the PivotTable toolbar. 4- Format the Field, according to the figure.

5- From the Field BS, P&L Level 1, filter the data by selecting Profit & Loss. The result:
In the PivotTable you add new Fields to compare and analyze the balances of Trial Balance figures by months, years and quarters.

You can create numerous comparative reports by moving the Fields from Row area to Column area and adding items details ( by double clicking any item within the Row area, left side of the PivotTable) according to the summary of Profit & Loss clauses by subjects. Here is an example: Note: This example does not contain row summary. To lower row summaries:
1. Click the Icon PivotTable in the PivotTable toolbar. 2. Select Table Option and uncheck the boxes Grand total for Columns or Grand total for Rows.

Handling Fiscal year reports
The grouping technique used with the PivotTable to group dates by Months, Quarters, and Years are based on calander year system only. If you need to group the dates using fiscal year basis you will need to add a new columns with a formula that calulates the quarter number on fiscal year basis in TB_data worksheet, the source data. see column H in the figure below.

Step A:
Add a formula that will calculate the quarter number for a fiscal year in column I. In the example, the fiscal year ends in September 30 of each year. The formula used to calculate quarter number in a fiscal year: =MOD(CEILING(22+MONTH(F2)-9-1,3)/3,4)+1) ( the number 9 appearing in the formula is the month number where the fiscal year ends ). Add the letter Q to the beginning of the formula and the year number to the end. The final result: ="Q"&(MOD(CEILING(22+MONTH(F2)-9-1,3)/3,4)+1)&" / "&YEAR(G2) The formula in the example returns the value: Q2 / 2002

Step B:
Create a PivotTable, see figure.
Step C: Sort the quarters in ascending order. For example - select cell A13 and drag it to cell A12 and so on.

Step D: Grouping by Years
1. Select the four quarters of the year 2002, right click and select Group from Group and Show Details . 2. Select the four quarters of the year 2003, right click and select Group from Group and Show Details . 3. Type 2002 in cell A11, and type 2003 in cell A15. 4. Type the text Years in the grey button of the Field name.

See the result in the figure:

Use the PivotTables you created to summarize and analyze the Trial Balance figures as desired: See figure for an example of a comparative Trial Balance figures between years.

See the figure for an example of a comparative Trial Balance figures by quarters: