Key to
Instructions
To help
readers follow the instructions in this
article, we use two different typefaces.
Boldface type is used to
identify the names of icons, agendas and
URLs.
Sans serif type indicates
commands and instructions that users
should type into the computer and the
names of files.
|
 ost spreadsheets are complicated
files—not only because they contain a multitude of
formulas and data, but because all the formulas
are intricately linked to data distributed in
various parts of the worksheet. As a result, even
one small error—a transposed digit or an incorrect
formula—can turn an entire spreadsheet into a
useless jumble of numbers.
BUILDING AUDIT MODULES
There are several ways to prevent
and uncover spreadsheet errors. One of the best is
to embed self-checking tool sets, or modules,
directly in worksheets—in effect, making them
self-auditing. This is especially important in
spreadsheet templates because any errors they
contain are reproduced in each subsequent copy of
the template. But because spreadsheet
designs vary so widely, there are no standard
auditing modules available. To illustrate how you
can create customized ones, consider the payroll
spreadsheet in exhibit 1. If you want to download
the worksheet so you can follow along with me, click
here. This spreadsheet
computes the regular and overtime earnings for the
employees of a small construction company. Be
aware that I’ve purposely created errors in the
spreadsheet to illustrate various auditing
techniques that I will explain later. I’ve set it
up so that Regular Pay = Regular Hours x Pay Rate
and Overtime Pay 5 1.5 x Regular Pay 3 Overtime
Hours. Most payroll spreadsheets would
include only the kinds of data and formulas
provided in rows 1 through 12 plus, perhaps, the
totals in row 14. However, I have added four kinds
of auditing tools in rows 16 through 25 that
illustrate ways to help verify a spreadsheet’s
accuracy: control totals, accounting identity
tests, limit tests and derived formulas.
 Control totals are sums or
counts that are computed for a specific set of
data. Two examples are in C17 and D17. I use
Excel’s CountIf function to count
the number of positive values in columns C and D.
You can compare the value in C17 with the total
number of employees working for the company or use
the value in D17 to evaluate who qualifies for
overtime.
Accounting identities give
you an alternative way to compute, and thus
affirm, a value. In the above example, note that
the regular plus overtime pay total of $3,643.15
in G14 is a column total—that is =SUM(G5:G12) .
But I also can compute it as the sum of the
regular pay (E14) and overtime pay (F14), which is
computed in G20 and, of course, should match the
value in cell G19. To automatically test whether
they match, I created the following formula in
G21: =IF(G19=G20, “ Yes ”, “ No ”). If the
two values are equal, G21 will display Yes ,
verifying the accounting identity. If they fail to
match, it will display No .
Caveat: A common error that causes a
false negative—that is, the cells fail to match
even when the calculations are correct—occurs when
someone uses inconsistent column ranges in
formulas. For example, he or she inserts a new row
at the top of a data range but fails to change the
cell references.
| Limit tests compare
the values in a row or column with
prescribed thresholds. For example, let’s
say the company’s upper limits for the
maximum pay rate (B23) is $14, the maximum
number of regular hours worked (C23) is 40
and the maximum number of overtime hours
worked (D23) is 10. We can use the
Max function to compute
maximum spreadsheet values and then
compare them with these threshold limits.
For example, the Max
formula for B24 (testing for the
maximum pay rate) is =MAX(B5:B12).
This function finds the largest
pay rate in the range B5:B12. The
associated If test for
this limit test in B25 is
=IF(B24<=B23, “Yes”, “No”).
| |
This function displays Yes if the maximum pay
rate found in cell B24 is less than or equal to
the value prescribed in cell B23, and No if it is
more. Although they are not included in this
example, you also can devise comparable
spreadsheet formulas that will test for minimum
values.
Derived formulas enable you
to recompute numbers using alternative ways to
verify results. In exhibit 1, for example, if the
maximum pay rate allowed is $14 and the maximum
number of regular hours allowed is 40, then the
maximum regular pay is $14 x 40, or $560. E23
displays this value. The spreadsheet can use this
value as a test limit and compare it with the
maximum of the actual regular payment amounts
(computed for E5:E12), or $502. The formula for
E25 is =IF(E24<=E23, “Yes”, “No”).
If the maximum found in the actual data is less
than or equal to the threshold limit, E25 will
display Yes ; otherwise it will display No . This
test helps guard against wrong data entries and
can detect certain types of erroneous formulas or
large constants in place of formulas.
EXISTING SPREADSHEETS
If, instead of creating a
spreadsheet from scratch, you want to
check an existing one, you need to use
other methods.
Compare results:
One way is to compare current
spreadsheet answers with known
results—for example, with either
manually calculated data or computations
performed elsewhere. In our example the
payroll will be performed weekly, so
earlier runs can provide clues about
data inaccuracies. If a spreadsheet is a
small but important file, a reviewer may
want to create a parallel copy for
comparison. | |
Plot a chart: Another way to
spot spreadsheet errors is to plot a copy of the
file in a chart, which, as you’ll see, can give
you a graph picture of any errors. Exhibit 2, at
rightt, illustrates this technique using a
modified version of the payroll data from exhibit
1; in it we chart regular hours worked (from
column C). Notice how the chart graphically shows
two common types of data-entry errors: an entry of
400 instead of 40 for the hours worked for an
employee Adams and an entry of a –40 instead of 40
for Hartford.
Perform sensitivity analysis:
Change either a parameter or an entry value
in an early portion of a spreadsheet and see how
it affects the bottom line. For example,
increasing the pay rate for Daniels in B8 by $1
(to $11.20) tests the formulas that calculate his
regular pay, his total pay, the total for regular
pay in E14 and the grand total in G14. Changing
the value of regular hours worked by just one unit
is an even better test because it is easy to
predict the result. Likewise, if we
increase the regular hours worked for Daniels by
one hour to 36 (in C8), for example, we would
expect each of the values in the following table
to increase by $10.20. If any value fails to
increase by that amount, that is evidence of an
error.
| Value (cell) |
Current value |
Predicted new value
| | Regular pay (E8)
| 408.00 | 418.20 |
| Total pay (G8) | 408.00
| 418.20 | | Total
regular hours (C14) | 305 |
306 | | Total regular
pay (E14) | 3,324.00 |
3,334.20 | | Total pay
for all employees (G14) | 3,643.15
| 3,653.35
|
Attest requirement: A final
safeguard is simply to install this statement,
signed by the spreadsheet reviewer, attesting to
its accuracy and validity:
 Nothing breeds accuracy
like accountability.
AUTOMATED AUDITING
It’s possible for a spreadsheet
to pass all the above tests and still be
incorrect. For this reason, you also may find it
desirable to examine the data and formulas in
spreadsheets using alternative methods.
| Examine formulas:
With Excel you can view formulas in
cells by clicking on Tools
, Options and
the View tab. Then, under
the Window options ,
place a check in the Formulas
box. A faster way is to press
Ctrl1~ (Ctrl+tilde). I warned you that
I purposely created errors in the
spreadsheet shown in exhibit 1. If
you’ve downloaded the file and followed
along with me to reveal the formulas,
you’ll see a screen similar to exhibit
3, which shows the formulas for the
cells in columns E, F and G. In column E
only the formula for E5 is correct; the
formulas for the other cells mul tiply
each pay rate by 40 even though the
value for regular hours is not always
40. This type of error commonly occurs
when a worksheet is prepared from a
template and the developer later enters
a formula correctly for the first row
but fails to copy that formula into
other appropriate cells. |
|
Column F contains a different kind of error.
Here, the constant 38.8 in F7 is in a column
where, as you can see, there should be only
formulas. Newer versions of Excel automatically
provide alerts for such inconsistencies, but older
versions do not.
Validate data: Excel enables
you to create automated validation tests, making
it possible to reject data errors before they
enter a spreadsheet. To illustrate let’s create
data validation tests for the following three
rules in our payroll worksheet: All pay rates must
fall between $6.75 and $14; all regular hours
worked must be at least 0 and no more than 40; and
all overtime hours worked must be at least 0 and
no more than 10. To automate the rule for
pay rates, highlight their values in column B
(B5:B12), click on Data and then
Validation, which evokes the
Data Validation dialog box
(exhibit 4). Tip: Highlighting the data
range before creating the rest of the validation
criteria applies the rule to all values in the
range.
Notice three tags in the dialog box. The first,
Settings , tells Excel which
data entries it will accept. For example, the
Allow box enables you to
specify a particular data type—decimal, whole
number, date, time, list or text length. The more
specifically you describe the data type, the more
likely Excel will correctly reject erroneous data.
Because we are creating a data validation rule for
the pay rates, which are in decimal format, we
will select Decimal from the
Allow choices. Once we
select Decimal , a new
Data Validation dialog box
similar to the one in exhibit 5 (above) appears,
with choices that relate to decimal numbers. Now
we select between in the
Data box, and then enter the
value of 6.75 in the Minimum box and 14.00 in the
Maximum box. If you select List
in the Allow box, the
validation dialog screen enables you to create a
set of allowable data-entry options—for example,
department codes that use letters A, B and C.
Exhibit 6, below, provides an example.
Finally, as an option, you also can create a
customized error message that will appear if a
user enters data that violate your validation
rule. To do this click on the Error Alert
tab and create an error message such as
the one illustrated in exhibit 7.
Exhibit 8, below, is the error message that
would pop up if someone attempted to enter data
that violated the pay rate rules.
| Auditing existing data:
Once you have created separate data
validation rules, there are still other
ways to spot mistakes. Click on
View , Toolbars
and Formula Auditing
, evoking the Formula
Auditing toolbar on the
spreadsheet (se e exhibit 9, below). As
you pass your cursor over each toolbar
icon, its description appears on screen.
If you click on the fourth icon from the
right— Circle Invalid Data
—Excel will circle in red the
data entries that violate the rules you
created. As you can see I’ve created extra
errors in the spreadsheet in exhibit 9 for
illustration. | |
| |
| The
Formula Auditing toolbar also
lets you display precedent relationships
to determine the source of a cell’s value.
For example, if you click on E5 and then on the
Trace Precedents icon (the
second icon from the left), Excel will create the
blue lines and dots in B5, C5, D5 and E5 (exhibit
10, below). The dots show that the formula in E5
uses the values in B5 (Adams’ pay rate) and C5
(regular hours worked) to compute the regular pay
amount in E5. If you now click
on E6 and again click on the Trace
Precedents icon, Excel will display
similar dots, indicating the cell values used to
create Baker’s regular pay. The absence of a dot
in C6 alerts you that regular hours worked were
not used to compute Baker’s pay—a formula error.
It also is possible to use Excel’s formula
auditing capabilities to show the precedent
relationships for an entire range of cells. For
example, if you first click on G14 (containing
$3,623.35) and then on Trace Precedents
, Excel will display a heavy, dark blue
line between G6 and G13, as shown in exhibit 11,
below, signifying that all the cells in this
column affect G14. If you again click on
Trace Precedents , the heavy
line will remain, but new lines and dots in the
data range E5:F12, as well as the arrows in column
G, will appear; these show that the values in E5
and F5 determined the value in G5, the values in
E6 and F6 determined the value in G6, and so
forth. Finally, if you
click on Trace Precedents a third
time, all the lines and dots shown will appear.
The presence of inconsistent dots for similar
computations alerts you to a potential error.
In addition to tracing precedent relationships,
the toolbar also can show dependent
relationships . For example, if you first
select E14 ($3,324.00) and then click on
Trace Dependents , Excel will
draw an arrow from E14 to G20. This arrow
indicates that G20 contains a formula that depends
upon E14. If the dependent cell is on another
worksheet, Excel will display an arrow pointing to
a small datasheet icon—not shown in exhibit
11—alerting you to this off-sheet dependency.
As you can see, there is no magic wand that can
guarantee a spreadsheet’s validity, but
fortunately, Excel has many tools to ferret out
and correct the errors. It requires knowledge and
patience. MARK G. SIMKIN, P hD, is a
professor of information systems at the University
of Nevada at Reno. His e-mail address is simkin@unr.edu
. |
Spreadsheet
Professional, offered by
Excelerated Consulting, 
Better decision-making with data analytics