• Technology
  • CFO.com | US

Spreadsheet “Worst Practices”

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

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.

Discuss

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