Dial a Forecast

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..

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.

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 =D22/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 toolbar 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).

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 this point, your worksheet will resemble exhibit 2 , below.

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 ). Type 100 in the Current value box, 0 in the Minimum value box, 200 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.

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) 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 .

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 ).

In Condition 1 set Cell Value Is to less than 0 . 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 ).

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 ).

The worksheet should now resemble exhibit 8 .

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 weisel_ja@mercer.edu .