• Technology
  • CFO.com | US

Spreadsheet “Worst Practices”

Here's how finance executives abuse the most-useful of computer programs — and how to do better.

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.

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.

Discuss

Your email address will not be published. Required fields are marked *