cfo.com | 5/14/08 | Shahid Ansari and Richard Block
Here's how finance executives abuse the most-useful of computer programs — and how to do better.
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 discount 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.
It is not hard to imagine being asked at some point after the detailing of quarterly prices, discounts, and volumes: Why is product A's quarterly volume growth in Q2, Q3, and Q4 a staggering 100 percent, 33 percent, and 194 percent, and product B's a substantial 38 percent, -
27 percent, and 157 percent, respectively. And why is Q4 volume growth over Q1, for products A and B, a whopping 683 percent and 350 percent?
It would not have taken long to add an interim calculation showing quarter-over-quarter volume growth. This interim calculation, called a documentation of constraints, is effective in quickly highlighting problems in data, assumptions, and the algorithms applied during spreadsheet development. A complicated spreadsheet analysis is more efficient if problems are detected during its creation.
In this scenario, say a sales volume growth of greater than 20 percent has never been achieved, or a sales volume growth greater than 15 percent would severely tax manufacturing capacity.
Anticipating, and then including this testing of constraints, would have had immediate benefit. And a revised set of assumptions about volume growth would have been incorporated into the spreadsheet model before the previous results were sent to the requestor. Waiting to test or validate data, assumptions, or algorithms until the end of development is more difficult, because there is simply more to test and validate, and the inevitable spreadsheet redesign will delay the final analysis and make the appropriate desired conclusions harder to achieve.
4) Difficulties in Making Changes
At this point in the scenario there may be a request for your data or assumptions to be modified, or added to. And Figure 1 allows for no easy modification. To change a price, a price discount, or a volume we must enter and potentially alter each cell, increasing the risk that we accidentally alter the wrong variable or inadvertently change an algorithm. The data and algorithm in cell D1 suggests that the month of December needed to be separated, for example.
And if you want to convert quarterly data to monthly, the model has to be substantially modified. Substantial modifications would also be required if you needed or wanted to add a third product and its requisite set of data and assumptions to the model.
It should be easy to use a spreadsheet to quickly accumulate and calculate data to answer an initial inquiry. But in the Figure 1 case, when revised quarterly revenue forecasts are sought, it's clear that to allow for future modifications you would want a far different design. It would not have taken much more time to separate and label key data and time frames and document assumptions, or to place interim constraint calculations in place to highlight trends and problems. That would have made calculations that produce the requested analysis simpler and clearer.
Because spreadsheets are powerful and easy to use, they are often employed to address immediate or short-term needs, with too little thought given to their future use.
5) "Now It's Here; Now It's Not"
It is common after completing a spreadsheet to want to see how the results differ when a variable changes. Say that the developer of our Figure 1 spreadsheet wants to assess the impact of changing the Q2 and Q4 volumes of products A and B to 6,000 and 13,500, and to 16,000 and 25,000, respectively. The adjusted Q4 forecast immediately changes to $16,742,500.
Unless the original result was written down or saved elsewhere, it would be hard to quantify the change in revenue and the change in adjusted average net selling price/unit produced by those volume revisions. (The answer: a $1,145,000 reduction in the first case and a $143.13 increase in the second.)
The common approach to documenting that answer is changing the volumes back to their original assumptions. One would hope that the adjusted four-quarter revenue projection just calculated would have be written down, or saved, before changing the volumes back.
This poor practice in comparing results is called the "now-it's-here-now-it's-not" phenomenon. The powerful "what if?" capability of spreadsheets is one major reason that they are so popular.
But only rarely are the basic numbers produced by the spreadsheet the ultimatel goal that is sought from it. Rather, the numbers are a means to the final answer or the final decision. And this decision is often achieved only by comparing and reviewing the results from different combinations of variables.
The best ways to compare and review the results from different combinations of variables are (a) to copy the original data sets and calculations into a separate spreadsheet tab, and (b) to build a comparison spreadsheet tab, which presents and contrasts the original, and at least one alternative result, built with a different data set.
6) The Presentation Readiness Problem
The inability to have spreadsheet results ready for a presentation is perhaps the final common abuse committed in Figure 1. At some point in this scenario, it is fair to assume that presenting the revised quarterly revenue forecasts will be required. With this spreadsheet, though, a presentation would require reviewing each cell — a common thread from the poor practices previously listed — and reentering the data and results. A sound, but often unused best-practice is to anticipate presentation readiness. This means, in addition to segregating data from algorithms, placing meaningful column and row labels in each portion of the spreadsheet. Thus, each significant portion of the spreadsheet can be copied and placed in a presentation without
retyping and reviewing for errors.
"Better" Practices Review (Part I)
Figure 1 clearly falls far short of the ideal for developing a spreadsheet. But what would a
bestpractice spreadsheet look like?
Typical spreadsheet improvements are represented in Figure 2a and Figure 2b. This type of spreadsheet design resolves many of the issues and poor practices we encountered in Figure 1. Monthly data by product are separately listed; interim calculations and assumptions are added.
However, this all-encompassing approach still has two weaknesses. It is difficult to update if other products need to be added. And it includes too much detail; copying for presentation purposes is still difficult. One would have to hide the monthly columns first, to present a cleaner set of analyses.
"Better" Practices Review (Part II)
A far better approach is pictured in Figures 3a, 3b, 3c and 3d. Here, even for this "simple" request to forecast revised quarterly product revenues, we have developed a spreadsheet with four separate tabs. Tab 1, (Figure 3a) and Tab 2 (Figure 3b) would hold basic volume and price data, data source references, and assumptions. Tab 3 (Figure 3c), would hold key interim calculations and analyses. Tab 4, (Figure 3d) would summarize the key data and calculations in a presentation-ready format. This approach promotes a sound financial analysis and outcome by:
—Encouraging the separation of data, and documenting their reference sources. Keeping data separate makes it far easier to change or increment, as interim calculations produce incorrect or unexpected results. Using separate tabs for data and some interim calculations also makes efficient the timely identification of poor or inaccurate data or assumptions, and makes changing or adding data to subsequent analyses much easier. Often one is attempting to solve for a specific outcome and making it easier to modify the data makes finding the solution much quicker. Also, later use by yourself or others is not adversely affected by memory or even personal involvement.
—Making robust and useful the key interim calculations and analyses for which the data and your assumptions are being used. The spreadsheet developer would use this tab to observe and identify profound and or unusual trends, poor assumptions, etc. The developer would continue to iterate through observation, review, and analysis using tabs 1, 2, and 3 until a sound analysis has been prepared.
—Encouraging a summary design for communication and presentation. By developing a presentation-ready tab, the analyst has the discipline to synthesize the results of all key assumptions and analytical results, concurrent with the ongoing analysis, so that only key findings and results will be presented or communicated. All too often, a reply using a spreadsheet design similar to Figure 2b is offered. The audience then is forced to either "analyze" the detailed spreadsheet to determine the key findings and results, or to ask the analyst to summarize once more. Designing a summary tab at the outset encourages sound analytical skills.
"Better" Practices Summary
This scenario may seem simple, but the preparatory steps to provide the answer are clearly anything but simple. Treat the development of a spreadsheet — any spreadsheet — more like writing a term paper with footnotes and a bibliography. It is a difficult discipline to acquire and perfect. But the incredible functionality and ease-of-use of an electronic spreadsheet can lull users into a false sense of accomplishment that they will pay for down the road. Understanding that pitfall — along with the others — should provide incentives for trying to improve spreadsheet discipline, and getting better results.