Getting the Oops! out of spreadsheets.

EXECUTIVE SUMMARY

* SINCE SPREADSHEETS ARE tools that not only describe a company's financial history but also tell its future, even tiny errors in one cell can be disastrous. Yet, as CPAs know, spreadsheets usually do contain errors.

* WHILE THERE ARE WAYS to ferret out and correct most errors, CPAs should be aware that no foolproof solutions exist. At best, errors can be minimized, so the prudent user should stay alert to the danger and use all the available tools to find them.

* WAYS TO CHECK FOR and eliminate errors:

* Perform input range checks using the Validation tool.

* Add explanatory comments and names to cells.

* Avoid the option to round off numbers.

* Review all formulas--using the Formulas tool.

* Engage the Auditing toolbar to be able to track the source of errors.

* Examine imported data regularly.

How to use built--in audit tools to minimize mistakes.

Spreadsheets, the lingua franca of the world of business, are pressed into service not only to describe a company's financial history but also to tell its future--which is why an error in even one spreadsheet cell can be disastrous. Yet, as CPAs know only too painfully, most spreadsheets do contain errors. Although some may be small and initially appear insignificant, even the tiniest slip can grow into a totally erroneous financial picture as the spreadsheet program computes data and performs further calculations based on that one small error. Making matters worse, many spreadsheets are templates, or models, to which users continually add information. If the original contains an error, each new data input amplifies that original error.

So what's one to do?

While there are ways to ferret out and correct most errors--and this article illustrates some of the most effective ways to do that (using Microsoft's Excel 97, version 8.0 to illustrate the steps)--CPAs should be aware that no foolproof solutions exist. At best, errors can be minimized using the processes shown in this article, so the prudent user should stay alert to the danger and employ all available tools to find them.

CHECKING THE RANGE

Excel makes it possible to validate input data--that is, perform input range checks--even without writing sophisticated software code. To perform a data validation, place your cursor in the target cell and click on the Data button on your menu bar and then on Validation. A dialog window opens with three tabs: Settings, Input Message and Error Alert. By selecting Settings, you can choose the data type (whole number, date, time) you want to validate and the upper and lower ranges for the input value. In addition to fixed numbers, you can use Excel formulas, so if you want to enter one of the last seven years you can use the formula =YEAR(NOW())-7 in the Minimum text field and =YEAR(NOW())-1 in the Maximum field (see exhibit 1, below).

[EXHIBIT 1 ILLUSTRATION OMITTED]

Then click on the Input Message tab and enter the message you want displayed when the cell is activated; for example, exhibit 2, below, identifies the cell as "Balance Sheet Year," where the user inserted the following text: "Only the last seven years are valid!"

[EXHIBIT 2 ILLUSTRATION OMITTED]

If you want to mark all cells in the spreadsheet for which input validation rules are defined, click on Edit, Go To (shortcut key: F5). Then click on Special and, in the next dialog window, select Data validation. You can choose between All (which selects all cells containing any input validation) and Same (which selects only cells containing exactly the same input validation as the active cell).

It's also possible to copy only the input validation rule from one cell to another. To do this, select the cell containing the rule you want to reproduce, copy it (Control + C), go to the destination cell and click on Edit, Paste Special and Validation. In that way you can store some useful input validation rules for later use; they can even be used in other sheets or workbooks.

NAMES AND COMMENTS

Another way to minimize errors--and also improve readability--is to add explanatory comments to key cells (Shift + F2) and use cell names (Sales, for example) instead of G5. The shortcut key to cell names is Ctrl + F3. If your comment doesn't appear after you've created it, click on Tools, Options and View; then, in the Comments section, set the visibility of cell comments to either Comment indicator only (producing little red marks in the upper right corner of commented cells and comments that appear only when the cursor passes over those cells) or to Comment & indicator (both the little red marks and comments that appear all the time (see exhibit 3, page 81).

[EXHIBIT 3 ILLUSTRATION OMITTED]

ROUNDED NUMBERS

Another frequent source of problems is the rounding option. Although rounding off numbers doesn't alter the underlying numerical code (so subsequent calculations are not compromised), rounding affects how numbers are displayed. The confusion arises when some numbers are rounded off and others are not or, worse, when different cells display the rounded-off numbers in different ways.

Therefore, when building spreadsheet models, it's best to deselect the option to round off numbers. Follow this rule: Always use a consistent number of decimal places (say, two rather than one) and employ commas to separate thousands--"#,###.##." Failure to maintain decimal consistency can produce irritating--and sometimes very confusing--results.

EXAMINING A SPREADSHEET

Every spreadsheet--especially a template--should be audited for errors very carefully. Begin by reviewing the formulas. One easy way to do this is to use this menu command sequence: Edit and Go To. Then click on Special and select Formulas, deselecting the options Numbers, Text and Logicals while leaving selected only the option Errors (see exhibit 4, page 81).

[EXHIBIT 4 ILLUSTRATION OMITTED]

If, after clicking OK, a dialog window reports, "No cells were found" you can at least be assured that the spreadsheet contains no trivial syntactic formula errors--that is, formulas referencing text entries or something similar. But if you receive an error message, search out its source. Excel has audit tools to track down errors.

Warning. The Auditing function appears to be far more complicated than it really is. If the following description leaves you confused--and anxious about even trying it out--you should reconsider that decision. If you jump in and test the function, you'll probably find, to your surprise and delight, that much of it is quite intuitive. As with many intuitive processes, the steps are hard to explain, but the process becomes clear when you watch it being done. For example, try to follow verbal directions for tying a bow; it's so much easier to learn how to do it by seeing someone tie a bow. So, read the following and resolve to test it in a spreadsheet: It's likely both your questions and your anxiety will evaporate.

Tip. If you find yourself using the Excel audit function often, you can save time by opening the Auditing toolbar; clicking on Tools, Customize and Toolbars; and placing a check in Auditing. If you use it less often, go to Tools, Auditing, Show Auditing Toolbar; this puts the Auditing toolbar on your screen just for the current session.

The nine functions of the Auditing toolbar (see exhibit 5, at right) are (left to right) trace precedents (that is, locate the source of data in the selected cell), remove precedent arrows (see exhibit 6, page 83), trace dependents (data subsequently calculated by the data in the selected cell), remove dependent arrows, remove all arrows, trace error, insert new cell comment, circle invalid input data (in cells containing data that don't conform to their validation rules) and clear all validation circles in the active spreadsheet.

[EXHIBIT 5 ILLUSTRATION OMITTED]

When you find an error message (which usually begins at the first cell with the # sign, as shown in exhibit 6), activate that cell and press the exclamation-point symbol in the Auditing toolbar (the trace error symbol). That commands Excel to draw a line of arrows, which displays the complete dependency chain for the cell containing the error message (as shown in exhibit 6). Red arrows show the propagation of an error message and blue arrows show correct input values into cell formulas. The source of the error is located in the cell where a blue arrow points in and a red one leads out.

[EXHIBIT 6 ILLUSTRATION OMITTED]

Although this source-of-error cell is always the first in the dependency chain that contains the error message, it's not necessarily the first cell containing wrong data. In the example in exhibit 6, the wrong data are in the cell two rows above where the number zero was entered, resulting in a division-by-zero error message in all dependent cells. To find the logical (and not syntactical) source of error, you must look one step up from the cell that generates the first error message.

If the lines are too long--spanning great portions of the spreadsheet and making them difficult to follow--double click on the arrow heads or on their back ends (the colored dots). That takes you right to the other end of each arrow; the formula references to different sheets are represented by little table symbols dropped right onto the sheet to which the arrows point; double clicking those symbols activates the spreadsheet being referenced.

You can customize the input validation rules in the Data Validation dialog box (see exhibit 1, page 80) by clicking on the register tab Error Alert. The drop-down list box called Style gives three options for the consequences of input data that violate the validation rules: Stop means no data violating the validation rules are accepted; Warning and Information means that when the user enters input data that are out of range, only a message box with warning text is displayed but Excel nevertheless accepts the input data. Input data that violate the data validation rules can be located by clicking on the second symbol button from the right in the Auditing toolbar (exhibit 5, page 81).

IMPORTED DATA

Spreadsheet data that are imported should be examined for consistency and plausibility by comparing the imported data with data from previous business years or from a regression line model in the case of prospective data. When controlling balance sheet ratios, check the numbers by calculating them twice--the second time using the balance sheet equation of assets = liabilities.

Spreadsheet software is a powerful tool. Recognize its strengths, and it adds significant leverage to your accounting skills. But ignore its limitations in tracking down the errors you inadvertently introduce, and it can speed you to disaster.

For more on spreadsheet errors, check out the Web site of Raymond R. Panko, professor of decision sciences at the University of Hawaii, a leading researcher into spreadsheet errors. It's at http://panko.cba.hawaii.edu/ssr/.

An Invitation

If you have a special how-to technology topic you would like the JofA to consider for inclusion in this series, or an application shortcut you devised and would like to share with other professionals, contact Senior Editor Stanley Zarowin. His e-mail address is [e-mail].

Example: Checking the Quick Ratio Calculation for a Balance Sheet

Assets
Current assets
  Cash and cash equivalents               $    250
  Other securities                             150
    Accounts receivable--net                 2,500
    Inventories                              2,600
    Other current assets                       700

    Total current assets                  $  6,200

Investments                                    500
Property, plant and equipment--net           5,000
Other assets                                   900

    Total                                 $ 12,600

Liabilities and Stockholders' Equity
Current liabilities
    Accounts payable                      $    900
    Payroll                                    300
    Income taxes                               100
    Short-term debt                            900
    Other current liabilities                1,200

    Total current liabilities             $  3,400

Other liabilities                            2,100
Long-term debt                               1,000
    Stockholders' equity--net                6,100

    Total                                 $ 12,600

The quick ratio can be calculated as the following:
   Quick ratio = (cash + other securities +
   receivables)/current liabilities = (250 + 150 +
   2,500)/3,400 = 0.85

The second way to calculate that ratio uses the balance
sheet equation for the same formula:
  Quick ratio = (balance sheet sum - inventories
  - other current assets - investments - property,
  plant and equipment - other assets)/(balance
  sheet sum - other liabilities - long-term debt - stockholders'
  equity) = (12,600 - 2,600 - 700
  - 500 - 5,000 - 900)/(12,600 - 2,100 - 1,000 - 6,100) = 0.85

This process assures not only the correct calculation of the quick ratio value but also the consistency and completeness of the whole balance sheet.

FRANZ HORMANN is an associate professor in the Department of Accounting and Tax Planning at the University of Economics and Business Administration, Vienna, Austria. He also is a data processing consultant for the Austrian Board of Chartered Public Accountants. His e-mail address is [e-mail].3