How to make spreadsheets error-proof.

Making those handy number-crunches more productive.

Commercial spreadsheet application programs probably are accountants' most widely used computer tools. But how accurate are the numbers they generate? While the underlying programs that run spreadsheet software are flawless, are the myriad custom formulas that users build into a typical spreadsheet equally sound? Research by a major accounting firm found that over 90% of spreadsheets larger than 150 rows contained at least one significant formula mistake. And it takes just one small error--a single misplaced code--to produce wildly erroneous results. Such errors can be devastating because the data often are the foundation on which many organizations base their key decisions.

It's possible to make spreadsheets not only virtually error-proof, but easier to use and more understandable. This article tells how it's done.

Unlike other information system programs, which typically are developed through extensive design and testing, spreadsheet files are mostly cobbled together on an as-needed basis by ordinary users. As a result, most spreadsheet files are relatively inefficient, difficult to use and hard to maintain. Also, since they generally lack the inherent controls and audit trails typically found in commercial information systems, there is no way to ensure the integrity, of their data. For example, what controls normally prevent the entry of invalid data or the accidental overwriting of a formula cell with a number? Usually none.

PLAYING THE ODDS

Using such a spreadsheet is much like playing Russian roulette. While it may work fine most of the time, at some stage, when that minor, undetected error emerges, it's going to produce a wrong answer--and it's unlikely that anyone is going to catch it.

But it doesn't have to be that way. There are techniques that even ordinary spreadsheet users--those without special programming training--can apply to improve the quality of spreadsheets and reduce--if not eliminate--the risk of errors. All the examples shown in this article are generated in Microsoft's Excel, but other spreadsheet applications have similar functions. Let's look at some of those techniques.

Basic design. When laying out a spreadsheet, divide it into three areas--input, calculation and output. Each of these areas is discussed in more detail later. They should be laid out diagonally so they share no common rows or columns, as shown in exhibit 1, below.

Why use this structure rather than a conventional vertical or horizontal design, where all data and formulas are arranged either side-by-side or one under the other? Because in a side-by-side or stacked arrangement, adding or deleting rows or columns invites problems. For example, if you are not careful when you delete or add a row' or column, it could affect all the data in those rows or columns.

An alternative technique is to exploit the three-dimensional capabilities of today's popular spreadsheets--using multiple worksheets in the same file. While this technique requires more complex formulas (because they must refer to cells in other worksheets), the payoff in security often is worth the effort.

Here's how to set up the input area: Begin by dividing it into two discrete parts. One should contain data that change infrequently or never; the other should contain data that a user will change regularly. The input area should not contain formulas or calculations. This helps overcome one of the major problems with spreadsheets-- version control.

This design also addresses an inherent shortcoming of spreadsheets: They were not designed to keep an audit trail of changes to data. But if you maintain a separate data-only input area, you can extract and save this section; in effect you produce your own audit trail. To enable easy identification of different spreadsheet versions, headers or footers on printouts should show the date and time of printing. To be effective over time, the input area structure shouldn't change very much, so plan the layout very carefully.

In addition, when setting up a presentation, select colors, fonts (typefaces) and graphic lines that enhance the input area's understandability. For example, use colors in cells to indicate that only data belong in them. For added security, add cell protection in cells that contain formulas or data that should not change.

The range names of input data cells should be descriptive words rather than letters and numbers; it makes formulas easier to read and the spreadsheet easier to audit. For example, in exhibit 2, below, renaming cell C4 with the formula =Net_income*Tax_rate rather than =C1*C2 is a real improvement.

Thus, to calculate the tax, the user simply types into the calculation cell =Net_income*Tax_rate instead of =C1*C2. While this query requires more typing, it does make the function clearer.

When printing, be sure to include the input area also so that underlying assumptions are clear to the viewer. To do this, simply print the input area or design an output area that reformats the input in a form that is better suited to paper. For example, the input area should contain only formatting (such as lines and shading) and references to cells in the calculation area and the input area but no calculations.

Rounding. All calculations other than pure additions or subtractions should be rounded to the same number of decimal places used for display purposes. This is important because the number displayed on the screen is not necessarily the same as the number actually in the cell. It is surprising how many printouts from spreadsheets contain rounding errors. Although they may seem immaterial, they can undermine confidence in a spreadsheet. Most spreadsheets programs have a ROUND function that rounds formulas to a specified number of decimal places. For example, 100 divided by 56 is held in the spreadsheet cell as 1.78571428571429. It will be truncated to 1.785714 on the screen (and on printouts). The command ROUND (100/56,2), which rounds to 2 decimal places, gives 1.79 both in the cell and on the screen.

Avoid complex formulas. They are hard to understand and are susceptible to errors. A good rule of thumb: If the formula's purpose isn't obvious, it's too complex. Solution: Break formulas down to their simplest components.

RIGHTING WRONGS

So that errors can be found and corrected before they are inserted into a spreadsheet, add controls that alert you to a problem. Several types are easy to construct and are effective in blocking errors.

Consistency controls. These provide an independent check on the results of a calculation. The most common example is to check totals--when columns and rows are summed to assure congruity. Exhibit 3, below, provides an example.

The total in cell E6 is the sum of cells E2 through E5 or the sum of B6 through D6. To ensure that this is true and that one of the totals in columns B though E does not omit a figure or has not been overwritten with a number instead of a formula, cell F6 contains the control formula =SUM(B6:D6)=SUM(E2:E5). That formula, if true, will automatically put the word TRUE in the appropriate cell. If the formula is wrong, indicating an error, it will read FALSE.

Limit controls. These prevent entry of figures outside tolerable ranges.

As shown in exhibit 4, above, depreciation must be between 0% and 75%. Cells B2 and C2 are inputs, Cell B5 is the depreciation rate entered. Cell D5 contains the formula =IF(OR(BS<B2,BS>C2),"outside limit",BS). In English, this cell says that if the depreciation rate is less than 0% or greater than 75%, display "outside limit," otherwise give the value in cell BS.

Reasonableness. Are numeric data being entered where text is expected or vice versa?

In exhibit 5, at right, data have been transposed in cells B2 and B3. Cell D2 contains the formula =IF(ISTEXT(B2),B2,"name must be text"). This formula says that if cell B2 contains text, show the text, otherwise give an error message. Cell D3, which has the formula =IF(ISNUMBER(B3),B3,"age must be numeric", in this case tests for a number.

Implementation of these and other types of controls varies depending on a user's specific requirements. The spreadsheet should not be able to produce output unless all the controls indicate there are no errors. For this reason it's a good idea to group all the controls in one area of the spreadsheet so they can be checked easily.

ROAD TESTING

Having built a spreadsheet, it's important to test it to make sure it works. The procedure depends on its complexity. A simple spreadsheet can be tested by manually checking its output. A complex spreadsheet that may be frequently used needs a different, more detailed approach that involves checking all the formulas and using test data. Unfortunately, checking formulas is a complex process requiring a combination of care and patience. If your organization uses the output from one or several spreadsheets for management information or decision making, it should address these questions:

* What controls prevent errors appearing in output?

* Does the spreadsheet rely on the knowledge and skills of one individual? What happens if that person is unavailable?

* Is there an audit trail to show changes since the last version?

* Is there documentation to explain the design of the spreadsheet and instructions on how to use it?

* Was the spreadsheet tested? Has the test data been retained to ensure that the data are still processed properly when the spreadsheet is altered?

* What ensures that data entered into the spreadsheet do not contain errors or inconsistencies when compared with their sources?

* How do you know the data in the spreadsheet are current?

* Does the spreadsheet allow an efficient work process?

If you had trouble answering any of these questions, your spreadsheet could be a ticking time bomb. So redesign the file following the suggestions in this article.

[TABULAR DATA OMITTED]

EXECUTIVE SUMMARY

* HOW ACCURATE ARE THE NUMBERS generated by spreadsheet programs? While the underlying programming that runs today's spreadsheet software is flawless, the many custom formulas that users build into a typical spreadsheet may contain errors.

* RESEARCH BY A MAJOR ACCOUNTING firm has shown over 90% of spreadsheets larger than 150 rows contain at least one significant formula mistake. And it takes just one small error--a single misplaced code-to produce wildly erroneous results. To avoid errors, use these techniques:

1. Divide a spreadsheet into three areas--input, calculation and output.

2. Organize the input area by data that change infrequently or never and data that a user will change regularly.

3. Don't insert any formulas or calculations into areas where data is inputted.

4. Give input data cells range names with descriptive words rather than letters and numbers.

5. When printing the output data be sure to include the input area also so underlying assumptions are clear.

6. To enable easy identification of different spreadsheet versions, printouts should include in either headers or footers the date and time of printing.

7. All calculations other than pure additions or subtractions should be rounded to the same number of decimal places used for display purposes.

DAVID FREEMAN, FCA, is managing consultant with RDA Consultants, Timonium, Maryland. He is a fellow of the Institute of Chartered Accountants in England and Wales and until recently was a board member of that institute's information technology faculty.