<<

. 31
( 62 .)



>>


You may be wondering what the final printout will be.
Remember that this is the input sheet. So what we need to do
is to create several other sheets whose job is basically nothing
more than to read the results of the input sheet (not the yellow
input lines themselves). These sheets can be named, for example,
˜˜Income Statement,™™ ˜˜Balance Sheet,™™ and ˜˜Cash Flow.™™ You will
see how they look at the end of the chapter.
First, let™s look at the elements of the ˜˜Input™™ sheet, and I
will explain them as we go along.


HOW TO READ THE ILLUSTRATIONS
In order to illustrate the formula-writing as fully as possible, I
have included small sections of our model showing the formulas
that need to be written into the various rows. The notation ˜˜>>>™™
means ˜˜copy this across the columns to the right.™™ For space
reasons, the illustrations only go to column F. As you set up
your own model, you can copy the columns out beyond this,
to as many columns as you want.




TLFeBOOK
Chapter 9
166




Formulas in the Titles
For all the titles, there should be a way of allowing the labels to
be changed and having those changes read by the output. A
good way to do this, rather than having to retype the labels, is
simply to have the ˜˜output™™ row read the label for that block. For
example, for revenues, the formula in A8 should read as shown:
A8 ¼ A6

You should feel free to add in these formulas yourself in the
model shown below. Use Excel™s Copy and Paste commands to
enter the same data in the cells.
Dates and Revenues

A B C D E F
1 First Corporation
2
3 Proj Proj
4 INCOME STATEMENT 2000 =B4+1 >>> >>> >>>
5
6 Revenues 825.0 900.0 1,000.0
_ 1,“na)
7 Percent growth % na =IF(B6,C6/B6 >>> 10.0% 10.0%
8 =A6 =B6 >>> >>> =IF(E6,E6, >>>
IF(ISNUMBER(E7),
D8*(1+E7),0))


Title
A1 ˜˜First Corporation™™
Date
B4 2000
C4 ¼B4þ1. Copy this cell across to column G.
Each looks to the previous cell and adds
the value 1 to that number. You can use the
DATE function if you want to use a more
sophisticated dating system that uses months
and years as described in Chapter 5 in the
Section ˜˜Functions for Dates.™™
Revenues
B7 This is ˜˜na™™ because there is no prior year for
measuring growth.
C7 ¼IF(B6,C6/B6 À 1, ˜˜na™™)




TLFeBOOK
Putting Everything Together 167




D7 The same formula as C7 with the column
references shifted. Both these formulas simply
calculate the growth rates for the historical
inputs. In this way, you can see the growth
trends in the historical periods to help you as
you make your forecast assumptions.
B8 ¼ B6. Copy this cell across to column D.
These cells are a direct read of row 6, which
may seem to make them redundant. However,
having the full time series of the historical and
forecast revenues numbers in this one row
makes it easy to send these numbers to the
output sheets.
E8 ¼ IF(E6,E6,IF(ISNUMBER(E7),D8*(1þE7),0)).
Copy this cell across to column G.
This formula looks at the hard-coded entry
row first. If that is a zero, it looks to the Percent
growth entry row and checks that it is a number
by using the ISNUMBER function. This means
that if this second row is blank, the formula will
return a zero. If it had not used the ISNUMBER
check, the formula will return the prior year™s
sales number.

COGS and Gross Profit

A B C D E F
10 COGS 450.0 490.0 550.0
11 As % revenues =IF(B$8,B10/B$8,0) >>> >>> 55.0% 55.0%
12 =A10 =B10 >>> >>> IF(E10,E10, >>>
E11*E$8)
13 Gross profit =B8-B12 >>> >>> >>> >>>
14 Gross margin =IF(B$8,B13/B$8,0) >>> >>> >>> >>>


COGS
B11 ¼IF(B$8,B10/B$8,0). Copy this cell across to
column D.
Create these so that you have an indication
of the historical margins as you make your
forecast assumptions.




TLFeBOOK
Chapter 9
168




Note the use of the absolute reference ($) for the
row number only. This allows the formula to be
copied into other rows below without losing
the reference to the revenues row. Note also
that the reference is to the final result of
revenues (row 8), not to the hard-coded input
row (row 6), which may be empty in the
forecast years if you are defining revenues by the
growth rate.
B12 ¼ B10. Copy this cell across to column D.
E12 ¼ IF(E10,E10,E11*E$8). Copy this cell across to
column G.
The use of the absolute reference ($) for the
row number only means you can copy this for-
mula into other rows that use the same pattern.
Again, the formula looks to the final results row
for revenues.
B13 ¼ B8ÀB12. Copy this cell across to column G.
B14 ¼ IF(B$8,B13/B$8,0). Copy this cell across to
column G.
The use of the IF statement here prevents the
#DIV/0! error messages from showing up when
the model is empty. You can write the formulas
without the IF test if you do not mind seeing the
error messages.


SGA and Operating Expenses

A B C D E F
16 SGA 125.0 135.0 150.0
17 As % revenues =IF(B$8,B16/B$8,0) >>> >>> 15.0% 15.0%
18 =A16 =B16 >>> >>> IF(E16,E16, >>>
E17*B$8)
19
20 Operating expenses 25.0 28.0 30.0
21 As % revenues =IF(B$8,B20/B$8,0) >>> >>> 3.0% 3.0%
22 =A20 =B20 >>> >>> IF(E20,E20, >>>
E21*B$8)
23 EBITDA =B13-B18-B22 >>> >>> >>> >>>
24 EBITDA margin =IF(B$8,B23/B$8,0) >>> >>> >>> >>>




TLFeBOOK
Putting Everything Together 169




SGA
B17 ¼ IF(B$8,B16/B$8,0). Copy this formula
across to column D. Alternately, copy the for-
mulas from the COGS section (B11“D11) here to
create these rows quickly.
B18“G18 Likewise, you can copy the formulas from the
COGS section (B18“G18) here to create these rows.
Operating expenses
Rows 20“22 These rows in the model are, except for
the title of the account, the same as the block in
rows 16“18 (for SGA). So a quick way to create
this in the model is to copy those rows and
paste them here. You can use this same
approach in adding other sections in the income
statement.
B23 ¼B13ÀB18ÀB22. Copy this cell across to
column G.
B24“G24 Copy the cells from the gross margin row
(row 14) to the EBITDA margin row.


Depreciation, Amortization of Intangibles, EBIT,
Non-operating Expenses

A B C D E F
26 Depreciation 60.0 75.0 80.0
27 As % prior Net PPE 8.5% 8.5%
28 As % revenues =IF(B$8,B26/B$8,0) >>> >>>
29 =A26 =B26 >>> >>>
30
31 Amort of intangibles 4.0 4.0 4.0 4.0 4.0
32 =A31 =B31 >>> >>> >>> >>>
33 EBIT =B23-B29-B32 >>> >>> >>> >>>
34 EBIT margin =IF(B$8,B33/B$8,0) >>> >>> >>> >>>
35
36 Non-oper expenses 10.0 10.0 8.0 10.0 11.0
37 As % revenues =IF(B$8,B36/B$8,0) >>> >>>
38 =A36 =B36 >>> >>> =IF(E36,E36, >>>
E37*B$8)
39




TLFeBOOK
Chapter 9
170




Depreciation
B27 ˜˜na™™
C27 ¼ IF(B108,C29/B108,0). Copy this to D27.
This formula looks to the prior year™s Net PPE
(that™s why B27 is just an ˜˜na™™), but as we begin
to create this sheet (and the balance sheet is not
yet created at this time), there is no reference for
row 108 for Net PPE. In this case, leave this
formula blank and return to it only after the
balance sheet is complete.
B28 Copy the formulas from any of the lines that
use the ˜˜As % revenues™™ input here, such
as row 17 for SGA. But adjust the reference to
read B33/B$8.
B29 ¼ B26. Copy this across to column D.
E29“G29 As we are still in the process of building this
model and we don™t have a reference for the Net
PPE (row 108) in the model yet, leave this
formula blank and return to it when the balance
sheet is complete.
Amortization of intangibles
B32 ¼ B31. Copy this across to column G.
This input section with only one input line,
followed by a row that directly reads the input
line, may seem to be an exercise in redundancy.
This is true. However, a consistent approach is
always helpful in building models, and here we
are being consistent in having an input row
where we can manipulate our entries, and a
resulting ˜˜output™™ row that is being read for the
model™s calculations.
EBIT
B33 ¼ B23ÀB29ÀB32

<<

. 31
( 62 .)



>>