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.
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
3 Proj Proj
4 INCOME STATEMENT 2000 =B4+1 >>> >>> >>>
6 Revenues 825.0 900.0 1,000.0
7 Percent growth % na =IF(B6,C6/B6 >>> 10.0% 10.0%
8 =A6 =B6 >>> >>> =IF(E6,E6, >>>
A1 â€˜â€˜First Corporationâ€™â€™
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.â€™â€™
B7 This is â€˜â€˜naâ€™â€™ because there is no prior year for
C7 Â¼IF(B6,C6/B6 Ã€ 1, â€˜â€˜naâ€™â€™)
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
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
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, >>>
13 Gross profit =B8-B12 >>> >>> >>> >>>
14 Gross margin =IF(B$8,B13/B$8,0) >>> >>> >>> >>>
B11 Â¼IF(B$8,B10/B$8,0). Copy this cell across to
Create these so that you have an indication
of the historical margins as you make your
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
B12 Â¼ B10. Copy this cell across to column D.
E12 Â¼ IF(E10,E10,E11*E$8). Copy this cell across to
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
B13 Â¼ B8Ã€B12. Copy this cell across to column G.
B14 Â¼ IF(B$8,B13/B$8,0). Copy this cell across to
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
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, >>>
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, >>>
23 EBITDA =B13-B18-B22 >>> >>> >>> >>>
24 EBITDA margin =IF(B$8,B23/B$8,0) >>> >>> >>> >>>
Putting Everything Together 169
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.
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
B23 Â¼B13Ã€B18Ã€B22. Copy this cell across to
B24â€“G24 Copy the cells from the gross margin row
(row 14) to the EBITDA margin row.
Depreciation, Amortization of Intangibles, EBIT,
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 >>> >>>
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) >>> >>> >>> >>>
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, >>>
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
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
B33 Â¼ B23Ã€B29Ã€B32