Ferret out Spreadsheet Errors: Use Excel's Tools to Uncover and Correct Formula Problems
Mark Simkin · Journal of accountancy online/Journal of accountancy · 2004
Most 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, page 63. If you want to download the worksheet so you can follow along with me, go to www.aicpa.org/download/pubs/jofa/2004_02_simkin-example.xls. [ILLUSTRATION OMITTED] 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 spreadsheets 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 = 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, 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 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. …