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 (columm 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
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.
Audit Accounting Data Using Excel Pivot Tables
An Aging of Accounts Receivable Example
Do you have
something
to say about
this article?
Visit the Journal
pages of the ISACA
web site ( www.isaca.
org/journal), find the
article, and choose
the Comments tab to
share your thoughts.
Go directly to the article:
Figure 1—Sample Accounts Receivable Data
Source: Filzen and Simkin. Reprinted with permission.
