<<

. 54
( 62 .)



>>

TLFeBOOK
CHAPTER 18


Bells and Whistles




In this chapter, we will consider some more of the additional
features”the ˜˜bells and whistles™™”to make our financial
model more functional.


A CAVEAT
When it comes to a model that you are sharing with others, you
should consider carefully before you add more features to a
core model that is already working well. Will the new features
make the model run better and/or make it easier to operate?
Sometimes, new features can mean new confusion for your users,
who are used to doing things in a certain way. (And some of
your users may prefer that they themselves add what they want
to a bare-bones core model.) Even if the feature does make the
model work better for the next user, adding more things also
means there are more things that can go wrong.
If the model is for your own use, these concerns do not
matter as much, since you will be your own judge of what
makes sense and what doesn™t.
In any case, as you make changes and add new features to a
model that already works well, make sure you save the model
under a different name after each successful modification. In this
way, if you should decide to unwind a change that you have
297



Copyright © 2004 by John S. Tjia. Click here for terms of use.
TLFeBOOK
Chapter 18
298




made, you can simply retrieve the last saved version from which
to experiment again.


SCENARIOS
As you use the model for analysis, you will reach a time when you
need to have different scenarios run through the model in order to
test the possible outcomes given changes in your forecast assump-
tions. Usually, there are three types of scenarios that you would
want to look at: the base case, the high case, and the low case.
It is not critical that we change every account in the model
for the three scenarios. We need only to look at the main
accounts that most affect the model™s outcomes. In our model,
there are only seven such accounts, called drivers:
Sales
u

COGS
u

SGA
u

Accounts receivable
u

Inventory
u

Capital expenditures
u

Accounts payable
u


These seven drivers set a company™s profitability and cash
needs. The simplest arrangement is for all seven to move in
tandem following one setting, called a toggle, and that is what
the following illustration will show. If you want to have more
possibilities, you can set seven toggles, one for each of the drivers,
so that you can get permutations of combinations (base case on
sales, but high case on COGS, low case on SGA, etc.). From an
analysis point of view, however, this may be overdoing things.
Remember, the numbers we are working with are assumptions,
and there is very little to gain from putting in details that go
beyond a broad-brush approach.
In the basic model, each one of the drivers has more than one
input row, so as we develop scenarios, we should have the same
number of rows. Here are the steps we must follow in order to
have the scenario feature. We will be making these changes in the
Input sheet, where each of the driver outputs will be calculated.




TLFeBOOK
Bells and Whistles 299




The resulting outputs will flow as before to the output sheets (IS,
BS, etc.), so we won™t need to make any changes on those sheets.
1. Set a cell that is the toggle.
2. Insert extra rows above each of the driver accounts.
3. Change the formula that reads the inputs so that it reads
three scenario inputs based on the toggle setting.

Set a Cell That Is the Toggle

A B C D E F G
1 First Corporation
2 Scenario 1
3 Proj Proj Proj
4 INCOME STATEMENT 2000 2001 2002 2003 2004 2005
5
6 Revenues 825.0 900.0 1,000.0
Percent growth % Na 9.1% 11.1% 10.0% 10.0% 10.0%
7
8 Revenues 825.0 900.0 1,000.0 1,100.0 1,210.0 1,331.0
9
10 COGS 450.0 490.0 550.0
As % revenues 54.5% 54.4% 55.0% 55.0% 55.0% 55.0%
11
12 COGS 450.0 490.0 550.0 605.0 665.5 732.1
13 Gross profit 375.0 410.0 450.0 495.0 544.5 599.0
14 Gross margin 45.5% 45.6% 45.0% 45.0% 45.0% 45.0%
15
Input

Here is the top section of the Input sheet. Let™s select the cell B2
as the input cell and type the label next to it. We can also put
a yellow shading to the cell (or whatever color you have been
using to indicate an input cell).

Insert Extra Rows Above Each of the
Driver Accounts
We will insert two more lines so that there are three growth
percentage input rows, one for each of the scenario cases. The
additional rows will be only for the forecast periods, since the
historical periods do not need any alternative set of numbers.
(Historical data are historical data.)
We could ponder here whether we need to ˜˜double up™™”to
have three couples of input rows, with each having a row for




TLFeBOOK
Chapter 18
300




hard-coded inputs and a row for the percentage growths. This
would be nice to have only if you were certain that you would
be using this model to replicate another model™s (precalculated)
scenario numbers. For our model, however, we will keep to a
simple design. Here is what the screen will look like, just for the
revenues section:
A B C D E F G
1 First Corporation
2 Scenario 1
3 Proj Proj Proj
4 INCOME STATEMENT 2000 2001 2002 2003 2004 2005
5
6 Revenues 825.0 900.0 1,000.0
Percent growth % na 9.1% 11.1% 10.0% 10.0% 10.0%
7
Percent growth % 20.0% 20.0% 20.0%
8
Percent growth % 5.0% 5.0% 5.0%
9
10 Revenues 825.0 900.0 1,000.0 1,100.0 1,210.0 1,331.0
11
Input


Change the Formula So That It Can Read
the Three Scenario Inputs Based on the
Toggle Setting
Now the magic begins for the scenario modeling. We need only
to change the formulas for column E onward.
A B C D E F G
1 First Corporation
2 Scenario 2
3 Proj Proj Proj
4 INCOME STATEMENT 2000 2001 2002 2003 2004 2005
5
6 Revenues 825.0 900.0 1,000.0
Percent growth % na 9.1% 11.1% 10.0% 10.0% 10.0%
7
Percent growth % 20.0% 20.0% 20.0%
8
Percent growth % 5.0% 5.0% 5.0%
9
10 Revenues 825.0 900.0 1,000.0 1,200.0 1,440.0 1,728.0
11
12 COGS 450.0 490.0 550.0
As % revenues 54.5% 54.4% 55.0% 55.0% 55.0% 55,0%
13
As % revenues 54.0% 54.0% 54.0%
14
As % revenues 57.0% 57.0% 57.0%
15
16 COGS 450.0 490.0 55.0% 648.0 777.6 933.1
17 Gross profit 375.0 410.0 450.0 552.0 662.4 794.9
45.5% 45.6% 45.0% 46.0% 46.0% 46.0%
18 Gross margin
Input




TLFeBOOK
Bells and Whistles 301




E10 ¼IF(E6,E6,D$10*(1þCHOOSE($B$2,E7,E8,E9)))
Copy this cell across to column G.
The formula looks to the hard-coded revenue
entry first, and if that is a 0, it then looks to any
one of the three growth rate inputs. We use a
CHOOSE function using the cell B2 as the
toggle. The growth formula also has a reference
to the prior period™s revenues, which will form
the basis for the growth rate. Note that the
reference is to the final (calculated) revenues
number (on row 10) and not the input line
(on row 6). The reason for this is apparent if we
look at the second forecast year: the formula
would have a base year of 0 if it looked to cell
E6, rather than cell E10.
E16 ¼IF(E12,E12,CHOOSE($B$2,E13,E14,E15)*E$10)
Copy this cell across to column G.
We are using the CHOOSE function again, but
this is not a growth formula, rather it is a
formula that applies a percentage to the
Revenues line. The reference to the Revenues
line, E$10, has an absolute reference to the row
only because it is likely that we will copy this as
a way to quickly build other sections. The
absolute reference will ensure that the formula
continues to read row 10 as it is copied further
down. Note that the column reference must not
have an absolute reference as we want the
reference to move across as we copy the formula
across the model.



Scenario Formulas for More Than One Input Row
The examples used above show scenarios for only one of the
input rows (for growth of revenues, for example). When we
want to do two input rows, the formulas get complicated pretty
quickly. Here is an example for the accounts receivable section,




TLFeBOOK
Chapter 18

<<

. 54
( 62 .)



>>