Microsoft Excel’s pivot table options provide powerful tools for aggregating and analyzing accounting data, but so does alternate software such as ACL. So why use pivot tables?
- Convenience—The data to analyze may already reside in a spreadsheet, the required analytical tools are already there, and no additional software is required.
- Flexibility—Pivot tables allow for an almost unlimited number of cross-tabulations in one, two or three dimensions, and this may be sufficient for the auditing tasks at hand.
- Documentation—The fact that, unless deleted, any pivot tables created become a permanent part of the same Excel workbook as the data on which it is based.
- Many formatting options—Auditors are not limited to a single prescribed format, but instead can present their analyses in a wide variety of designs and graphics.
The following examples demonstrate three variations of a common accounting task that is easily performed using pivot tables—creating an aging analysis of accounts receivable. The steps discussed apply equally to Excel 2010 and Excel 2013.
Task 1: Creating a One-dimension Aging Analysis
The (simplified) spreadsheet in figure 1 contains the purchase records for 100 fictitious unpaid invoices, the first few of which are displayed. Each record includes information such as customer number, customer type, assigned salesperson, account balance (i.e., Bal.) and an invoice date (i.e., Inv. Date). What a manager or auditor might desire is an aging report that summarizes these data in order to assess the collectability of customer accounts.
At first, the data might not include computations for determining the age of the invoices (column F in figure 1), but the spreadsheet can easily compute them as the difference between today’s date (in cell C2) and the invoice date, formatted to a number format. For example, the formula for cell F5 is: = $C$2 - E5. This formula uses an absolute cell reference for cell C2, so that it can be copied to the other cells in column F.
To classify the customer records into standard categories such as “current,” “30-60 days” and so forth, the spreadsheet uses the data in cells I4:J7, which it treats as a VLookUp table.1 To illustrate, the formula for cell G5 (for the first customer) is: =VLOOKUP(F5,$I$4:$J$7,2). In this formula, F5 is the value to look up; $I$4:$J$7 is the range of cells containing the VLookUp table; and the last value of “2” is the column offset (i.e., the reference to the second column of the lookup table). To display conventional labels in the subsequent pivot table, the entries in the VLookUp table use the words “current,” “30-60 days” and so forth, as shown in figure 1.
Because the coordinates of the lookup table are absolute cell addresses, the VLookUp formula can be copied to the other cells in column G. Figure 1 shows the results. For example, because the age for first record is 54 days, its classification is “30-60 days,” as desired. Similar computations produce similar results for the other cells in column G.
A pivot table is a perfect tool with which to compute the desired aging analysis. To do so:
- Highlight the entire set of data (i.e., the range A4:G104 for the spreadsheet in figure 1).
- Include the column titles in row 4, which Excel will use to identify the various data for the pivot table.
- Select the menu choices Insert/Pivot Table/Pivot Table for Excel 2010 (or Insert/Pivot Table for Excel 2013) starting from Excel’s main menu. The resulting dialog box (not shown) enables the spreadsheet designer to verify the data range selected and also to decide whether or not to use a separate worksheet for the results.
Figure 2 illustrates the results of this work, mostly a blank worksheet that includes the Pivot Table Field List on the right side of the worksheet.
To create an aging analysis, the spreadsheet designer must first indicate which columns of data to use. He/she does this by checking the checkboxes for the ”Classification” data and for the “Bal.” (balance) data in the Pivot Table Field List. Alternatively, the designer can drag the name of the data field item, whether checked or not, into one of the four boxes in the lower portion of the Pivot Table Field List.2 Figure 3 displays the desired settings, with the classification in the Row Labels box and the sum of balance in the Values box. Field names can also be dragged from box to box if necessary. Finally, Excel assumes that the designer wants the sum of the balances, but this can be changed. Figure 3 displays the results of this work.3
To format the numbers in the pivot table of figure 3, click on the ▼ symbol next to the “Sum of Bal.” entry in the Values box in the lower-right corner of the Pivot Table Field List and select “Value Field Settings.” This produces the dialog box shown in figure 4. In this box, the designer can click on the Number Format button in the lower left portion, and then select any desired numerical format from the subsequent dialog box that Excel provides (not shown).
The auditor may also want values other than (or in addition to) the sum of the amounts owing for each category in an aging analysis. For example, suppose that the auditor wants to examine (1) the sum of the transaction amounts for each aging category, (2) a count of the number of transactions, (3) the average of the transaction balances for each aging category and (4) the maximum transaction amount for each category. This is easily accomplished by adding new items to the Values box in the lower right corner of the pivot table dialog box of figure 3.
To perform this task, click and drag the balance field from the Pivot Table Fields List (top right portion of figure 3) to the Values box, and then repeat this step twice more. The results will roughly resemble those in figure 5, except that Excel will again show the sum of balance entries instead of the desired counts, averages and maximums. To have Excel display these desired values, click on the ▼ symbol next to each of these fields in the Values box and then select “Count,” “Average” or “Max” from the Field Settings dialog box in figure 4.
The results, when formatted, will resemble those in figure 5. Here, the first column shows the aging categories, the second column displays the sum of receivables for each category, the third column displays the number of transactions for each category, the fourth column displays the average transaction amount for each category and the fifth column displays the largest transaction in each category. The designer can display additional columns, such as the minimum transaction amount for each category, by repeating these same steps for each value desired.
Task 2: Creating a Two-dimension Aging Analysis
When performing audits of accounts receivable data, it may also be useful to create a two-dimensional table that displays account balances classified by both transaction age and customer. Pivot tables perform such tasks easily. Figure 6 displays the desired analysis.
The following are the steps to create the two-dimensional pivot table shown in figure 6:
- Start with the original data from figure 1.
- Highlight the entire dataset.
- Select the menu choices Insert/Pivot Table/Pivot Table to create a new pivot table in a new worksheet.
- Next, select the “Customer,” “Bal.,” and “Classification” data fields and make sure that the “Classification” field is in the Column Labels box of the Pivot Table Field List, the “Customer” field is in the Row Labels box of the field list, and the “Bal.” field is in the Values box of the field list.
- If any data field is not in its correct box, simply drag it from whatever box in which it does appear and drop in into the appropriate area.
The results, when formatted, should resemble those in figure 6.
With the data for the pivot table properly specified and formatted, it is now easy to make some observations about this set of transactions. For example, in this illustration, it is clear that some customers are completely current (e.g., customers 7 and 9), while others have transactions that are seriously in arrears (e.g., customers 2, 6 and 8).
In practice, it is likely that a company would have hundreds of customers. To select only a few of them for viewing, the analyst can click on the drop-down button in cell A4 of the pivot table to select specific customers for viewing. Similarly, to limit the pivot table to show only one or two specific aging categories (e.g., only the “60–90 days” and “over 90 days” columns in the pivot table), the analyst can click on the drop-down button in cell B3.
Task 3: Creating a Three-dimension Aging Analysis
If an auditor wishes to classify receivables in some way other than by Customer and Aging Category, he/she need only create a new pivot table using alternate data fields in the Pivot Table Field List. To illustrate, perhaps the auditor wishes to tabulate the amounts by both age classification and customer type.4 In the initial dataset of figure 1, for example, there are three types of customers with codes X, Y and Z. Do the aged accounts receivables differ by customer type?
To find out, an auditor can create the pivot table in the left portion of figure 7. Here, the aging categories form one dimension of the table and the customer codes form the second dimension. This table was created using the tools described previously for task 2. The only difference is that the column variable is now “Cust.Type.” The lower portion of the Pivot Table Field List on the right side of figure 7 shows the specific settings required.
In Excel, it is also possible to add a third dimension, called a “filter,” for a pivot table. Figure 7 illustrates an example—an aging analysis that shows the sum of the transaction amounts owing by (1) age, (2) customer type and (3) salesperson. To create such a table, first create the two-dimensional table using the tools described earlier for task 2. Then, add the final variable (“Salesperson”) and, if necessary, drag this variable to the Report Filter box in the Pivot Table Field List box, as shown in figure 7. Excel will then add the filter to the pivot table, as shown in cells A1 and B1 of figure 7. Now by clicking on the arrow in cell B1, an auditor can ask Excel to display the aging analysis for a specific salesperson. This allows the auditor to examine how average account balances vary by age category (the variable on the left), by customer type (the variable across the top row) and by salesperson (the selection in the filter).
Selecting Alternate Display Options
After creating a pivot table, the auditor can reformat it as desired. For example, as suggested by figure 7, Excel provides a number of different design options for pivot tables. To select an alternate, perhaps more colorful, style for the pivot table, the auditor can click on the Design tab for a pivot table and then click on the More button to see other design options (see figure 7). Excel classifies its design options as light, medium and heavy, but spreadsheet designers can also create their own custom designs, if desired.
As illustrated in figure 7, designers can also create pivot charts from pivot tables. In fact, if the user selects “Insert/Pivot Chart” instead of “Insert/Pivot Table,” Excel can generate both the chart and the table at the same time. However, a pivot chart can always be added by selecting “Pivot Chart” from the Analyze tab (in Excel 2013) or the Options tab (in Excel 2010) under Pivot Table Tools. The designer can format each item separately once they have been created.
A final programming note is that Excel does not automatically update a pivot table if a spreadsheet designer alters any of the underlying data. But, this inconvenience is easily overcome by manually refreshing the table. To do so, the auditor can use the Pivot Table tabs in the top menu portion of figure 7 and select Options/Refresh for this task.
Conclusion
Microsoft Excel’s pivot table options provide powerful tools for aggregating and analyzing accounting data. While the example here focused on analyzing accounts receivable, similar analyses are possible for accounts payable, cash sales, inventory or payroll applications, as well. The data do not even have to be numeric—pivot tables can also count occurrences of alphabetic data if frequency distributions are required. Further, although the example presented here used a small set of data, much larger data sets are easily handled in the same manner. For these reasons, the ability to use pivot tables should be a natural part of the skill set of external and internal auditors wishing to perform cross-tabulation analyses of accounting data.
Endnotes
1 Technically, the categories are “current,” “30-59 days,” “60-89 days,” and “90 days or more.” The VLookup table used here performs exactly this classification.
2 All pivot table tools as well as the Pivot Table Fields List are visible only when the spreadsheet cursor is located in one of the cells of the pivot table itself.
3 The default row label order is alphabetical; however, custom sorting orders are available by clicking the ▼ symbol next to “Row Labels.”
4 For example, the term “customer type” could designate geographic sales region, credit rating or type of account.
Joshua J. Filzen, Ph.D., CPA, is an assistant professor of accounting in the College of Business at the University of Nevada (USA). He can be reached at jfilzen@unr.edu.
Mark G. Simkin, Ph.D., is a professor of information systems in the College of Business at the University of Nevada (USA). He can be reached at markgsimkin@yahoo.com.