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.