Ferret Out Spreadsheet Errors

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.

Exhibit 1

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

Specialized Auditing Software There are a number of add-on software products available that can help CPAs check spreadsheets for errors. Three worth looking at: Spreadsheet Professional, offered by Excelerated Consulting, www.exceleratedconsulting.com.au/index1.html . Spreadsheet Detective, from Southern Cross Software Queensland Pty. Ltd., www.uq.net.au/detective/home.html . Operis Analysis Kit, distributed by Operis Group, www.operis.com/oak.htm . For more products, go to www.mathtools.net .

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.

Exhibit 2

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.

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.

Exhibit 3

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.

Exhibit 4

Exhibit 5

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 6

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.

Exhibit < 8

Exhibit 9

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.

Exhibit 10

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.

Exhibit 11

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 .