usiness 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.
To help readers follow the
instructions in this
article, we used two
type is used to identify
the names of icons, agendas
serif type shows the names
of files and the names of
commands and instructions
that users should type into
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).
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
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
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:
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
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 ).
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