Chapter 13  Analyzing Financial Statements Using PivotTable and PivotChart Reports

 

About This Chapter

This chapter describes how to use PivotTable and PivotChart reports to analyze the company's Financial Statements.It contains examples and step-by-step instructions on how to create PivotTable and PivotChart reports, and then analyze them while summarizing the balances of the General Ledger Trial Balance accounts.

The following sections are included:

F       Overview, page 225, provides an overview of the use of PivotTable and PivotChart reports to analyze financial reports and return summarized data to Financial Statements.

F       Saving Trial Balance Accumulated Balances in Different Structures, page 226, describes how to create PivotTable reports from Trial Balances data stored in Excel worksheets, using two different storage structures.

F       Creating a PivotTable Report, page 229, describes the steps required to create a new PivotTable report.

F       Using a PivotTable Report to Summarize Balances Used in Financial Statements Reports, page 233, describes how to create and save a PivotTable report that retains a fixed structure of PivotTable Fields, which is useful in preparing Financial Statements.

F       Using a PivotTable Report to Summarize Data According to Period, page 239, describes how to use the PivotTable report to summarize the account balances of the accumulated Trial Balances by month, quarter and year.

F       Creating Monthly or Quarterly Income Statement Reports, page 252, describes how to create a PivotTable to compare monthly Income Statement balances.

F       Creating a PivotChart Report to Compare Data Between Years, page 252, describes how to create comparison reports quickly and easily using PivotChart reports.

F       Using PivotTable Reports to Create Common-size Balance Sheets, page 255, describes how to analyze the Balance Sheet using a PivotTable report.

F       Summarizing Accumulated Trial Balance Account Balances Stored in Vertical Sequences in Adjacent Rows, page 258, describes how to create PivotTable reports that use accumulated balances of the Trial Balance accounts stored in vertical sequences in adjacent rows.

F       Adding a Field to a PivotTable Report to Analyze the Balances by Fiscal Year-end Reporting Date, page 266, describes how to deal with Financial reports when the ending reporting date is a fiscal year end.

Overview

In the previous chapters, you learned how to create Financial Statements using formulas and objects that summarized the balances of the General Ledger Trial Balances, which are stored in Excel worksheets.

In this chapter, you will learn how to use PivotTable and PivotChart reports to analyze financial data and then return summarized data to Financial Statements.

The PivotTable and PivotChart report technique, which analyzes data in Excel, is one of the quickest, most effective and amazing techniques provided by the Microsoft Excel program.

PivotTable and PivotChart reports enable you to summarize data, compare the summarized balances between months, quarters and years, summarize the balances from the source data by operating various functions and styles and create quick charts.

Saving Trial Balance Accumulated Balances in Different Structures

In this section, you will learn how to analyze the accumulated balances of the General Ledger Trial Balance accounts, which are stored in Excel worksheets in two different structures, using the power of PivotTable and PivotChart reports.

The two different structures that store the accumulated General Ledger Trial Balance account balances are described as:

F       Saving the accumulated balances of the Trial Balance accounts in adjacent columns. (For details, refer to Chapter 1, Introducing Financial Statements.xls Worksheets.)

Figure 13‑1: Accumulated Balances of the Trial Balance Accounts in Adjacent Columns

F       Saving the accumulated balances of the Trial Balance accounts in vertical sequence in adjacent rows. (For details, refer to Summarizing Accumulated Trial Balance Account Balances Stored in Vertical Sequences in Adjacent Rows, page 258.)

Figure 13‑2: Accumulated Balances of the Trial Balance Accounts in Vertical Sequence in Adjacent Rows

To learn more about saving and updating accumulated balances for the General Ledger Trial Balance accounts in Excel worksheets, refer to Chapter 3, Updating the Trial Balance Data Worksheet.

The essential difference between the two techniques described above is as follows:

F       The Field names in the PivotTable report (see the Field buttons on the right side of Figure 13‑3 and Figure 13‑4) are determined by the text entered in the top cells of each column in the Trial Balance data worksheet (row 1 in Figure 13‑2), which is the source data to create the PivotTable report.

The PivotTable Fields are divided into two categories:

v        Data Fields

v        Query, filtering and sorting Fields

An example of the Fields buttons for the second storing technique is shown in the two figures below.

Figure 13‑3: Before Field Sorting	Figure 13‑4: After Field Sorting

When using the first technique to store the accumulated balances of the Trial Balance accounts in adjacent columns, the number of Data Fields in a PivotTable report equals the number of Trial Balance columns in the worksheet.

When using the second technique to store the accumulated balances of the Trial Balance accounts in vertical sequences in adjacent rows, the PivotTable report has only a single Data Field created from a single data column (the Sum column, as shown in Figure 13‑2).

Each technique has its advantages and disadvantages. This chapter contains examples of PivotTable reports created using both techniques.

Creating a PivotTableReport

The technique to create a PivotTable report is similar for the two storing structure techniques described previously. Differences resulting from the structure used to store the Trial Balance will be described later.

Step 1: Defining a Dynamic Range Name for the Source Data Range

Ø     To defining a Dynamic Range Name for the source data range:

1.       Select worksheet 21 – Trial Balances Data, select cell A1 and press <Ctrl+Shift+*> to select the Trial Balances data.

2.       Press <Ctrl+F3>.

3.       In the Names in wordbooks box, enter TB_data.

4.       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))

5.       Click OK.

To learn more about Defining a Dynamic Range Name, refer to Chapter 2, Adjusting the Trial Balance.

Step 2: Creating a PivotTable Report

Ø     To create a PivotTable report:

1.       Add a new worksheet by pressing <Shift+F11>, and change the worksheet name to PivotTable.

2.       Select the PivotTable worksheet and select cell A1.

3.       From the Datamenu, select PivotTable and PivotChart Report.

4.       In the Wizard Step 1 of 3, ensure that the Microsoft Excel list or database box is checked.

5.       Click Next.

6.       In the Wizard Step 2 of 3, check the Range box, press <F3>, select the Name TB_data and click OK.

7.       Click Next.

8.       In the Wizard Step 3 of 3, select cell A1 and click Finish.

Step 3: Adding Fields to the PivotTable Report

Ø     To add fields to the PivotTable report:

1.       From the PivotTable Field Listwindow, drag the query Fields to Page (upper-left side). Query Fields are any Fields that are not defined as Data Fields, as shown below.

2.       Drag the Data Field or Data Fields to the Drop Data Items Here area. The Data Field in the figure above is the Sum Field.

Using the PivotTable Report

The PivotTable report is a very powerful technique that has the ability to filter and summarize data quickly and easily. In this section, you will cover two subjects used by the PivotTable report:

F       Using the PivotTable report to summarize balances used in Financial Statement reports, page 233, describes how to summarize the balances of the Trial Balance accounts to be used in the Financial Statements reports.

F       Use the PivotTable report to summarize data according to period, page 239, describes how summary data can be used for data analysis, business decision making and immediate responses to queries.

In the next two sections, you will learn how to create PivotTable reports required for the two structures mentioned above. The PivotTable report uses the technique of saving the accumulated balances of the Trial Balance accounts in adjacent columns as source data, as described in Saving Trial Balance Accumulated Balances in Different Structures, page 226.

Using a PivotTable Report to Summarize Balances Used in Financial Statements Reports

The PivotTable report created in this section will form a fixed structure of Fields while summarizing the account balances of the Trial Balance by levels.

Thisstructure uses the PivotTable report between the Trial Balance data worksheet and the Financial Statements reports worksheets, and removes the need to add complicated formulas to Financial Statements worksheet cells.

Figure 13‑5: Summary by Account Level Using a PivotTable Report

After creating the PivotTable report as described in Creating a PivotTable Report, page 229, you should remove all unused Fields from the PivotTable report so that the table structure and Field position in the worksheet do not change when the PivotTable report is refreshed, particularly as a result of adding new Fields to the original source data.

Returning the Balances Summarized by the PivotTable Report to Financial Statements Report Worksheet Cells

Save the PivotTable worksheet, in the structure described previously (as shown in Figure 13‑5, page 233), and define the Namesto be pasted into formulas in the Financial Statement worksheet cells.

Ø     To define the Names:

1.       Define a Name for the PivotTable worksheet, select worksheet PivotTable, and then press <Ctrl+A>.

2.       Press <Ctrl+F3> and define the Nameas PT_data.

3.       Define the Names for the first three columns:

F       Select column A, press <Ctrl+A>, press <Ctrl+F3> and define the Name as PT_Level1.

F       Select column B, press <Ctrl+A>, press <Ctrl+F3> and define the Name as PT_Level2.

F       Select column C, press <Ctrl+A>, press <Ctrl+F3> and define the Name as PT_Level3.

4.       Select row 2 (the title row above the columns), press <Ctrl+F3> and define the Name as PT_Row2.

Adding Formulas That Return the Summarized Balances from the PivotTable Report to the Balance Sheet Worksheet

In Chapter 4, Balance Sheet, you created a Balance Sheet report in worksheet 31 – Balance Sheet. Copy this by dragging the worksheet tab while pressing the <Ctrl> key, and change the copied worksheet to 33 – Balance Sheet PT.

For details about the structure of worksheet 31 –- Balance Sheet, refer to Chapter 4, Balance Sheet.

Figure 13‑6: INDEX Formula Returns Summarized Balances to Balance Sheet Worksheet Cells

Figure 13‑7: Choose an Account Type List Using Validation

The formula in cell C12 (shown in Figure 13‑6) is as follows:

=INDEX(PT_data,MATCH(A12,PT_Level3,0),MonthNumber+3+12)

The formula in cell D12 is as follows:

=INDEX(PT_data,MATCH(A12,PT_Level3,0),MonthNumber+3)

Figure 13‑8: Index Function Arguments

F       Thesecond argument of the INDEX formula contains a nested MATCH formula, which returns the cell number where the text Cash is located in column C (Level 3, Account Types) in the PivotTable worksheet (see Figure 13‑7, page 236). The row number is 4.

F       Thethird argument of the INDEX formula calculates the column number of the reporting month in the PivotTable worksheet. MonthNumber is the Name of the linked cell to the Combo Box.

The first Trial Balance in DT_data is located in column D, which is column number 4 in the worksheet. The starting number of the MonthList in the Combo Box is number 1. To calculate the column number in PT_data, add 3 to the number inthe MonthNumber cell and add 12 for the previous year (2002).

Refreshing the PivotTable Automatically

Updating the data source used by the PivotTable report is not automatic. You must refresh the PivotTable reportto transfer any new or updated data from the source to the computer memory (cache). The PivotTable report is automatically updated from the cache memory.

However, you may add an VBA macro Event to the TB_data worksheet that automatically refreshes the PivotTable report when adding a new Trial Balance column to worksheet 21 –- Trial Balances Data, or when updating the TB_data worksheet. The refresh option will be performed automatically when leaving the TB_Data worksheet by selecting a different worksheet in the workbook.

Ø     To refresh the PivotTable:

1.       Press <Alt+F11> to open the VBE (Visual Basic Editor).

2.       In the Project- VBAProject, double-click the TB_data worksheet, as shown below.

3.       From the left scroll box above the General module sheet, select Worksheet.

4.       From the scroll box on the right above the module sheet, select Deactivate Event.

5.       Enter the following code to the Event:

Sheets("PT_Data").PivotTables("PivotTable1").PivotCache.Refresh

Using a PivotTable Report to Summarize Data According to Period

You can easily use the PivotTable report to summarize the accumulated balances of the Trial Balance accounts by months, quarters and years.

The PivotTable report allows the user to use a Field many times while changing the Field format and adding formulas to Calculated Field.

In Figure 13‑9, all three columns from B to D, 2003 in column D (row 7), Running Balance 2003 in column C, and % of Sales 2003 in column D were created from one data Field – December 2003. In columns F:G the data Field is December 2002.

Figure 13‑9: Analyzing a Comparison PivotTable Report

This is an exciting option, because it enables you to quickly and easily create a comparisonreport that compares two consecutive years, as shown in Figure 13‑9.

In this section you will learn how to create similar report.

This PivotTable report contains eight columns in the data area, with each year having three data columns. This provides a total of six columns, plus two additional columns that calculate the increase (or decrease) between years.

The following steps describe how to create the PivotTable report to compare periods.

Step 1: Creating a PivotTable Report Containing Two Data Fields

Ø     To create a PivotTable report containing the two data Fields:

F       Create a PivotTable report containing the two data Fields, December 2002 and December 2003, and then format and modify the Field name.

 

Ø     To format and modify the Field name:

1.       Select any cell in the Data area, for example, select cell B8.

2.       Right-click and select Field Setting from shortcut menu.

3.       In the Names box, enter 2003.

4.       Click the Number button and select the desired format.

5.       Click OK.

6.       Select cell C8 and repeat steps 2 through 5, entering 2002 in step 3.

Step 2: Adding Two More Fields to the Data Area in the PivotTable Report

The next step is to add two more Fields, December 2002 and December 2003, to the Data area in the PivotTable report.

Ø     To add the Data Fields again:

1.       Open the PivotTable Field List window (first icon to the right on the PivotTable toolbar).

2.       From the PivotTable Field List window, drag each Field two more times to the Data area of the PivotTable report, as shown.

The result is shown in the following figure.

3.       Organize the Fields in the Data area into two groups:

F       Three Fields for 2002

F       Three Fields for 2003

This is done by moving the Field position in the PivotTable report.

4.       Select the title cell Field, for example, cell D7 in the figure in step 2, and drag the Field using the mouse to its new position in the Data area.

Step 3: Changing the Calculation Method

In this step, you will change the calculation method of the added Fields.

Ø     To change Field 2 to calculate a running balance in years 2002 and 2003:

1.       Select a cell in one of the newly dragged data Fields, the December 2003 Field.

2.       Click Field Settings in the PivotTable toolbar.

3.       In the PivotTable Field,in the Name box, type Running Balance 2003.

4.       Click Options,and select Running Total in from Show data as.

5.       In Base field, select the Field positioned in the PivotTable as a row, BS, P&L Level3.

6.       Repeat steps 1 through 5 and modify the second December 2002 Field.

Ø     To change Field 3 to calculate the percentage of December 2002 and December 2003, (that is percentage of the Profit and Loss account types according to sales revenue):

1.       Select a cell in the third dragged data Field, the December 2003 Field.

2.       ClickField 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 Field.

6.       Select Sales in Base Items.

7.       Change the format the percent value:

8.       In the Format window, click Number, and then select Custom from the Type box.

9.       Type 0.00% ;[RED](0.00) % (positive percent colored black, negative percent colored red) and click OK.

10.   Click OK.

11.   Repeat steps 1 through 8 and format the last Field of the December 2002.

Step 4: Adding Two More Columns for Analysis and Comparison Between Years

In this step, you will learn how to add two more columns for analysis and comparison between years, as shown in columns H:I of Figure 13‑10

Figure 13‑10: PivotTable Report After Adding Two New Analyzing Fields

 This is done in two stages:

F       Adding a new Field that calculates the difference between the two years.

F       Adding a new Field that calculates the percentage difference between the two years.

Ø     To add a Field that calculates the difference between the two years:

1.       Select a cell in one of the PivotTable report Data area.

2.       From the PivotTable toolbar, select Formulas from the PivotTable shortcut menu (submenu within the icon) and select Calculated Field, as shown.

3.       In the Insert Calculated Field window, in the Name box, enter a name for the formula:2002 VS 2003.

4.       In the Fields area,select the Field December 2003 and click Insert Field.

5.       Enter a minus (-) sign in the Formula box.

6.       Select the Field name December 2002 and click Insert Field.

7.       Click OK.

Ø     To add a Field that calculates the percentage difference between the two years:

F       Repeat the procedure above. To add a Field containing a formula that calculates the changes (increase/decrease) in percentage between the years Fields, select the Field 2002 VS 2003 in the Fields windowand click Insert Field (as shown below).

The final result should look like this:

Step 5: Grouping Items to Create a New Field to Present the Subtotals

The PivotTable report contains the summary of the Accounts Types, Level 3.

There are moreimportant subtotal levels, such as Gross Income and Gross Profit, as well as additional essential calculations required to analyze the company's business results.

In this step you will learn how to group items from BS,P&L Level3 to create a new Field to present the subtotals of Profit & Loss Level 1, as shown in Figure 13‑11.

      Figure 13‑11: PivotTable Report After Adding New Level 1 Group Field

Ø     To add new group Field:

1.       Copy the PivotTable worksheet to a new worksheet by clicking the worksheet tab, pressing <Ctrl>, dragging it and releasing the <Ctrl> key and the mouse.

2.       Change the worksheet name to PivotTable PL Group.

3.       Ensure that the PivotTable report has only one Row Field, as shown below.

4.       Select Sales and Cost of goods sold from BS, P&L Level 3 Field.

5.       Right-click and select Group and Show Detail.

6.       ChooseGroup.

7.       Change the text Group1 in cell A7 to Gross Income by typing the new text into the cell.

8.       Select additional items from BS, P&L Level 3 Field and perform the Group action to the group items.

9.       Drag the Field BS, P&L Level3 to the Page area (upper corner above the PivotTable report).

The final result should look like this:

Creating Monthly or Quarterly Income Statement Reports

Add a comparison of quarterly Income Statement reports by adding the quarterly month columns March 2003, June 2003, September 2003, and so on, to the PivotTable.

Ø     To create a PivotTable to compare monthly Income Statement balances figures:

1.       Add the months you want to compare or add the quarterly months (March, June, and so on) from PivotTable Field List window to the Data area in the Pivot Table report.

If the PivotTable Field List window is not visible, click the right icon in the PivotTable toolbar.

2.       After adding the months you want to compare and analyze, add the calculated Fields and format according to the techniques explained in To format and modify the Field name, page 241.

Creating a PivotChart Report to Compare Data Between Years

Selecting a cell in the Data area of the PivotTable report and pressing <F11> creates a PivotChart report in a new worksheet of the workbook.

Figure 13‑12: PivotChart Report

The PivotChart report shown in Figure 13‑12 has a Data Table at the bottom. Pressing <F11> does not automatically create a Data Table; to add it, follow the procedure below.

Ø     To add the option Show Data Table to the PivotChart report:

1.       From the Chart menu, select Chart Options.

2.       From the Chart Options window, select the Data Table tab.

3.       Select Show Data Table Option and click OK.

Using PivotTable Reports to Create Common-size Balance Sheets

With the PivotTable report technique, you can create a common-size Balance Sheet.

Add two Fields to the Data area:

F       Diff 2002 VS 2003.

F       Diff in % 2002 VS 2003.

For details regarding how to add these two Fields, refer to Step 4: Adding Two More Columns for Analysis and Comparison Between Years, page 246.

Figure 13‑13: Common-size Balance Sheet

If you check the percentages in cells G16 and G30, you will notice that the percentages of the lines Assets Total and Equity & Liabilities Total do not equal 100%. The procedure below explains how to get around this problem.

Ø     To add a Field that calculates the subtotals and balance items as a percentage of the total Assets and Equity & Liabilities:

1.       Copy the PivotTable worksheet to two new additional worksheets.

2.       Drag the three summary level Fields from the Page area to the Row area, as shown in Figure 13‑13.

3.       From the PivotTable Field List window, drag the Field December 2002 and the Field December 2003 to the Data area in the PivotTable report. (If the PivotTable Field List window is not visible, click the last icon in the PivotTable toolbar.)

4.       Select a cell in the Field December 2003, click Field Settings (in the PivotTable toolbar) and format the Field, as shown below.

5.       Repeat steps 1 through 4 to format the Field December 2002.

The results of this procedure are shown in the following figures.

The PivotTable in the first figure contains Assets data. The second figure shows Equity & Liabilities data.

Figure 13‑14: PivotTable Report Showing the Assets Category

Figure 13‑15: PivotTable Report Showing the Equity & Liabilities Category

Summarizing Accumulated Trial Balance Account Balances Stored in Vertical Sequences in Adjacent Rows

In this section, you will learn to create a PivotTable report that uses accumulated balances of the Trial Balances accounts stored in vertical sequences in adjacent rows as its source data.

Figure 13‑16: Accumulated Balances of the Trial Balances Accounts in Vertical Sequence in Adjacent Rows

Ø     To store the Accumulated Balances of the Trial Balances accounts in vertical sequences in adjacent rows:

1.       Adjust the Trial Balance, as explained in Chapter 2, Adjusting the Trial Balance.

2.       Copy the final adjusted Trial Balance and paste it into a new temporary worksheet (to add a new worksheet, press <Shift+F11>), as shown below.

3.       Insert four columns before column C (the column with the title Sum).

4.       Select worksheet 21 – Trial Balance Data, and copy cells C1:E2.

 

 

5.       Select the new worksheet you added and paste the copied cells to cells C1:E2.

6.       Type into cell F1 the title Month.

7.       Type into cell F2 the month-ending date for the final adjusted Trial Balance.

8.       Select column F, press <Ctrl+1>, select the Number tab, and then select Custom.

9.       In the Type box, type mmmm yyyy and press OK.

10.    Copy cells C2:F2 and paste them downwards.

11.   Add more previous Trial Balances from worksheet 21 – Trial Balances Data. When you have finished, the worksheet should look like Figure 13‑16.

After you have stored the accumulated balances of the Trial Balances accounts in vertical sequence in adjacent rows, you are ready to create a PivotTable report.

Ø     To create a PivotTable report:

1.       Define a dynamic Name for the Trial Balances data, as explained in Chapter 2, Adjusting the Trial Balance.

2.       Enter the formula:

=OFFSET(TB_Verticaldata!$A$1,0,0,COUNTA(TB_Verticaldata!$A:$A),COUNTA(TB_Verticaldata!$1:$1))

into the Refers to box in theDefine Namewindow.(The worksheet name TB_ValidationData in the formula is the name of the new worksheet.)

3.       Create a PivotTable report in a new worksheet, as described in Creating a PivotTable Report, page 229.

Ø     To analyze the balances for the accounts in the Trial Balances by monthly, quarterly and annual terms:

 

 

1.       Select any cell in the PivotTable report and drag the Field Month from the Page area (left corner) to the Row area (left side of the Data area).

2.       Select one of the cells in the Field Month, for example, A10.

3.       Right-click and select Group from Group and Show Details.

4.       In the Grouping window, select Months, Quarters, Years, as shown, and click OK.

5.       Drag the Field Years from the Row area to the Column area (above the Data area) in the PivotTable report.

The results are shown in the figure below.

Ø     To add a calculated Field and present the Balances figures in percentages to the PivotTable report:

1.       Drag the Field Sum from the PivotTable FieldList to the Data area.

 

 

2.       In the PivotTable Data area, select a cell from the newly added SUM Field.

3.       Click Field Settings (second from the right) in the PivotTable toolbar.

4.       Select % of column from Show data as, as shown below.

5.       From the Field BS, P&L Level 1, filter the data by selecting Profit & Loss criteria.

The results are shown in the figure below.

You can create numerous other comparative PivotTable reports by moving the Fields from the Row area to the Column area and adding item details (by double‑clicking any item within the Row area, on the left side of the PivotTable) according to the summary of Profit & Loss Accounts Groups level or Accounts Types level, and by canceling the Grand Totals for Rows or Columns.

Ø     To cancel the Grand Totals for Columns or Grand Totals for Rows:

1.       Click the PivotTableiconin the PivotTable toolbar.

2.       Select Table Option and uncheck the boxes Grand total for Columns or Grand total for Rows.

The PivotTable report will not display Grand Total for Rows, as shown below.

Adding a Field to a PivotTable Report to Analyze the Balances by Fiscal Year-end Reporting Date

The grouping technique described in the previous section to group dates by months, quarters, and years is only useful when the reporting period is based on a calendar year-end reporting period.

To group dates based on a fiscal year basis, you need to add a new column with a formula that calculates the Quarter numbers on a fiscal year reporting period in the TB_VerticalData worksheet. An example of this can be seen in column H in the figure below.

Figure 13‑17:Formula to Calculate and Present Quarter Numbers for a Fiscal Year Reporting Period

Step 1:Adding a Formula to Calculate the Quarter Number

The first step is adding a formula that will calculate the Quarter number for a fiscal year reporting period in column H. In the example, the fiscal year-end reporting date is September 30 of each year.

Ø     To add the formula:

1.       Type the text Quarters by Fiscal Year into cell H1.

2.       Enter the following formula into cell H2:

="Q" & MOD(CEILING(22+MONTH(F2)-9-1,3)/3,4)+1 & " / " & YEAR(F2)

The number 9 in the nested MONTH formula is the month number for the fiscal year reporting date.

3.       Copy the formula from cell H2 to all the cells in column H, as required.

Step 2: Refreshing the PivotTable Report

The source data used by the PivotTable report you created on page 260 is the dynamic range defined as TB_ValidationData, which uses the OFFSET formula to adjust its size automatically.

In this step you will refresh the PivotTable report and add the Field Quarters by Fiscal Year.

Ø     To refresh the PivotTable report and add the Field Quarters by Fiscal Year:

1.       Select the worksheet where the PivotTable report was created.

2.       Select any cell within the PivotTable area and click Refresh Data in the PivotTable toolbar (the red exclamation point).

3.       From the PivotTable Field List window, drag the Field Quarters by Fiscal Year to the Row area.

The results are shown in the figure below.

Step 3: Sorting the Quarters in Ascending Order

The next step is to sort the quarters in ascending order. This can be done by dragging the cells one by one in their proper sequence, as shown in column A in the figure above.

Step 4: Grouping the Data by Years

The last step is to group the data by years.

Ø     To group the data 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 next quarters of the year 2003, right-click and select Group from Group and Show Details.

3.       Enter 2002 in cell A11, and type 2003 in cell A15.

4.       Enter the text Years in the gray button of the Field name.

The results are shown in the figure below.

5.       Use the PivotTable report you created to summarize and analyze the balances of the Trial Balance accounts, as desired.

Figure 13‑18 shows the Trial Balance report's comparison balances that compare the Profit & Loss results between two consecutive years.

Figure 13‑18: Comparison Quarterly PivotTable Report

Figure 13‑19 shows the Trial Balance report's comparison balances that compare the Profit & Loss results between consecutives months.

Figure 13‑19: Comparison Monthly PivotTable Report