Dial a forecast: a speedy way to examine multiple business scenarios.

Business forecasts typically are based on historical information that is modeled by current conditions and anticipated futures. But to make the forecasting exercise more useful for planning, accountants can tinker further with the numbers by calculating how even an unanticipated future will change various metrics. This article will describe a fast and easy way to plug multiple alternative scenarios into a "what-if" calculation so managers can adjust their strategic and tactical decisions.

Say we anticipate a 2% sales growth rate. This forecast will affect performance metrics such as return on sales and earnings per share. But what happens to those metrics if the growth rate is 5%, or -1%? In a typical spreadsheet we could simply plug in alternative assumptions. But that process is very cumbersome when you need to examine many different assumptions; keeping track of the variables can become quite a headache.

We'll show how to use three Excel tools--spinners, scroll bars and conditional formatting--to make forecast calculations a breeze. If you wish to follow along, download the illustration file of XYZ Company's profit and loss statement at www.aicpa.org/download/pubs/jofa/dec2006/weisel_xyz_forecast.xls.

THE RIGHT SPIN

XYZ'S forecast is based on these assumptions: base quarter sales, quarterly sales growth rate, sales returns and allowances rate, cost of sales rate, marketing and promotion, and general and administration (exhibit 1, below). If you want to evaluate the effect of a 5% growth rate, say, all you have to do is overwrite the original estimate in cell B18 with 0.05.

[ILLUSTRATION OMITTED]

Each time you want to change the assumption, you have to adjust the number in that cell.

A more efficient way is to add a spinner--an Excel tool that you can rotate like a dial to change a cell's value. By default, spinners can change values by increments of only 1 unit and within a range of 0 to 30,000. Since we want to use the spinner to adjust a percentage value, we must create an index key--a way of scaling what the spinner controls.

Start by typing 100 in cell D22 (any blank cell will do). Then insert the formula =D29/1000 in B18 (the cell that displays quarterly sales growth rate) and format the cell to display percentage with one decimal place (see screenshot below).

Now, to load the spinner right-click in any free area in your tool-bar to elicit this menu.

Click on Forms to produce this menu, below left. Now hover your cursor over the Forms menu until you find the spinner icon (see screenshot below left).

Click on it and your cursor icon changes from a pointer to a cross-hair. Move your cursor to C18 and click again to attach the spinner there (see screenshot at right).

At this point, your worksheet will resemble exhibit 2, left.

[ILLUSTRATION OMITTED]

Finally, to link the spinner to the index key and define its characteristics, right-click on the spinner to bring up the Format Control dialog box and select the Control tab (exhibit 3, above). Type 100 in the Current value box, 0 in the Minimum value box, 900 in the Maximum value box and 10 in the Incremental change box. If the completed Format Control box resembles exhibit 3, the setup is correct. Click in the Cell link box, on D22 and on OK. This configuration allows us to dial in sales growth rates between 0% and 20% in 1% increments.

[ILLUSTRATION OMITTED]

Variations: If you wish to hide the index key (in this case the formula in cell D22), format the text in the cell to white. You also can adjust the control options to produce different levels of sensitivity See "Spinner and Scroll Bar Format Controls," below, for several choices.

LET'S GO FOR A SCROLL

Spinners work well for metrics formatted as percentages and other relatively small values. However, if you wish to control values over a broad range (say from $80,000 to $120,000), the scroll bar is easier to use. The steps to create a scroll bar are virtually the same as for a spinner. As before, you must first create an index key and formula.

In cell D21 place the value 1000 and change B17 to this formula:

=100*D21

Now select the scroll bar (see screenshot, page 76) from the Forms toolbar and draw the scroll bar in the worksheet near Base Quarter Sales.

Right-click on the scroll bar and select the Control tab in Format Control and enter the following:

Current value 1000, Minimum value 800, Maximum value 1200, Incremental change 10. Finally, click in the Cell link box, on D21 and on OK. You have a scroll bar that can change Base Quarter Sales from $80,000 to $120,000 in $1,000 increments. You can change Sales either by clicking on the left or right arrows or by sliding the center bar to the left or right. The spreadsheet now should resemble exhibit 4, below.

[ILLUSTRATION OMITTED]

Key to Instructions

To help readers follow the instructions in this article, we use two different typefaces: Boldface type is used to identify the names of icons, agendas and URLs. Sans serif type shows the names of files and the names of commands and instructions that users should type into the computer.

HIGHLIGHT RESULTS

To make your what-if analyses more graphic, add Conditional Formatting, a tool that highlights cells when they achieve a predetermined value or range of values. In the case of XYZ, we want to draw attention to periods in which the Return on Sales (line 14) is negative, between 0% and 2%. and greater than 2%.

Begin by holding down the left mouse key and select cells B14:D14. Then click on Format, Conditional Formatting (exhibit 5, above).

[ILLUSTRATION OMITTED]

In Condition 1 set Cell Value Is to less than O. Click on Format and on the Font tab, select Font style bold, color white. On the Patterns tab, select red from the Cell shading color palette. Click on OK and the dialog box should resemble (exhibit 6, above right).

[ILLUSTRATION OMITTED]

Finally, add two more conditions so that Return on Sales values between 0 and 0.02 are highlighted in yellow and values greater than 0.02 are highlighted in green. When done, click on OK (exhibit 7, right).

[ILLUSTRATION OMITTED]

The worksheet should now resemble exhibit 8 at right.

[ILLUSTRATION OMITTED]

Now not only can you speedily spin and slide to examine various forecast scenarios, the color-coded numbers will instantly reveal critical metrics. With this arrangement you will be able to easily build a useful set of future scenarios so management can focus on preparing for these conditions.

James A. Weisel, CPA, DBA, CMA, is a professor at the Stetson School of Business & Economics, Mercer University, Atlanta. His e-mail address is [e-mail].

Spinner and Scroll Bar Format Controls

When preparing a forecast worksheet, you can adjust the sensitivity
of your spinner and scroll bar control tools to work with different
scenarios. This table provides several typical options.

                             Minimum    Maximum    Incremental
Formula target cell           value      value       change

Examples of Spinner
Format Control

=index key/1000                 0         200          10

=(index key/1000)-.05           0         200           1

=index key * 100              1000       2000          10

=(index key * 100)-100000       0        10000         100

Examples of Scroll Bar
Format Control

=index key * 100                0        30000         10

=index key * 10000             100        300           1

=(index key * 100)-10000        0         300           1

Formula target cell          Result

Examples of Spinner
Format Control

=index key/1000              Percentage ranging from 0% to 20% in
                             1% increments

=(index key/1000)-.05        Percentage ranging from -5% to 10% in
                             0.1% increments

=index key * 100             Value ranging from 100,000 to 200,000
                             in 1,000 increments

=(index key * 100)-100000    Value ranging from (100,000) to 100,000
                             in 10,000 increments

Examples of Scroll Bar
Format Control

=index key * 100             Value ranging from 0 to 300,000
                             in increments of 1,000

=index key * 10000           Value ranging from 1,000,000 to 3,000,000
                             in increments of 10,000

=(index key * 100)-10000     Value ranging from (10,000) to 30,000
                             in increments of 100