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.

• « previous
• 1
• 2
• 3
• next »