Spreadsheet "Worst Practices"

There's little doubt that electronic spreadsheets are the most widely-used financial software application. But they are also the most-abused.

It takes some effort — often a lot of effort — to develop and maintain sound, proper, and effective spreadsheet practices. The spreadsheet's very ease of use encourages sloppy habits, and even seasoned finance professionals can find themselves falling into bad habits. At its worst, spreadsheet sloppiness, reflected in poor design, difficult manipulation, and lack of documentation, can lead an auditor to declare that a company has ineffective controls over some aspect of financial reporting. But even less serious spreadsheet follies can cause major headaches.

So what are some of the pitfalls of spreadsheet abuse, and what would a properly constructed spreadsheet look like without them? Many common pitfalls may be detected in the following simple example, Figure 1, "Request for a Revised Forecast."

Imagine someone in your finance department receiving a basic request to revise quarterly revenue forecasts for two products your company markets. A new spreadsheet is opened. Expected prices and volumes are typed into the first four cells on the top row of the spreadsheet. The total of the four quarters is computed in the fifth cell. As this is a simple request, a simple spreadsheet is created. It might look similar to Figure 1 below. Watch for proper spreadsheet practices that may have been compromised.

Above, the revised quarterly revenue forecasts have been typed into an E-mail replying to the request. Anticipating questions, your department saves this spreadsheet. A few weeks after sending the revised revenue projections, your department gets questions regarding pricing and price changes, volumes and volume changes, and quarterly revenue growth.

To answer these questions, the saved spreadsheet is opened. You find yourself reading the content of each cell with a blank stare.

Seem at all familiar? As simple as this example is, it does highlight a number of spreadsheet abuses:

1) Poor Segregation of Data
The simple spreadsheet did produce mathematically accurate results. But unfortunately, that's where its effectiveness ended.

An electronic spreadsheet is, of course, a powerful calculator. And because of that, it may seem sensible to achieve a quick result from a multistep calculation after putting data and assumptions into each cell. But when each cell contains both key data and the complicated assumption-laden algorithms to be applied, confirming that the results are appropriate or reasonable may be virtually impossible — even if calculated "correctly." It is a better practice to separate the data from the algorithms and assumptions being applied to the data.

Trying to remember, weeks later, the source or the values of the basic data also is difficult, and fraught with inefficiency. Figure 1 is a simple spreadsheet, and simple, too, is the request it attempts to satisfy. But to recall the prices and volumes used for each quarter, each cell has to be reread. If the timing of a subsequent inquiry on prices and volumes used is important, reading each cell of a spreadsheet will certainly increase the risk of responding slowly.

2) Poor Documentation of Assumptions
Trying to recall the assumptions being applied to the base data is even more difficult, and thus fraught with potential misinterpretation. Again, determining the assumptions requested in the initial reply requires each cell to be reread. But that may not be enough.

Would rereading each cell refresh a memory? The spreadsheet example lists price discounts starting in Q3 and volume growth starting in Q2, before discounts are offered. Would a rereading help you remember why you made those assumptions? If you replied to a further inquiry with price and volumes, as indicated in Figure 1, a second request would certainly follow — asking to further explain the apparent timing mismatch of discounts and volume growth.

Were you to determine quarterly revenues using a calculator, pad, and pencil, you would probably write down some of your assumptions, or at least document the products, prices, and volumes used to calculate quarterly revenues. The calculator would be used to calculate. But a spreadsheet makes number crunching so easy that users have a tendency to forget to write any words. Unfortunately, that limits the spreadsheet's usefulness to the amount of time that its developer can remember and explain the assumptions he or she used for prices and volumes.

Documenting the data and assumptions as they are being created may seem inefficient — or may even create a barrier — when the need for a response is immediate. However, most spreadsheet analyses will be used again, and even simple documentation will make subsequent uses more efficient and more accurate.

3) Poor Documentation of Constraints
Because a spreadsheet is an infinite calculator, it offers the opportunity for many calculations to be performed simultaneously. One such use involves placing interim formulas into cells — to provide early warning signals if our data or assumptions are yielding questionable results. Figure 1 offers no such warnings.

• 1
• 2
• 3
• next »