<<

. 32
( 62 .)



>>

B34 ¼ IF(B$8,B33/B$8,0). Copy this across to
column G. You can create this row quickly by
simply copying the formulas from row 24 for
the EBITDA margin.




TLFeBOOK
Putting Everything Together 171




Non-operating expenses
Rows 36“38 This block is similar to rows 20“22. Copy
those rows here and change the titles to show
˜˜non-oper expenses.™™


Interest Income, Interest Expense, and EBT

A B C D E F
40 Interest income 3.0 5.0 6.0
41 Surplus funds 2.3 3.6
42 Cash 4.0 4.0
43 ST investments 1.7 1.7
44 =A40 =B40 >>> >>> =IF(E40,E40, >>>
SUM(E41:E43))
45
46 Interest expense 50.0 50.0 50.0
47 Necessary to finance 0.0 0.0
48 ST notes 1.4 1.4
49 Debt 1 15.0 15.0
50 Debt 2 22.5 22.5
51 Debt 3 8.8 8.8
52 =A46 =B46 >>> >>> =IF(E46,E46, >>>
SUM(D47:51)
53 EBT =B33-B38+B44-B52 >>> >>> >>> >>>
54 EBT margin =IF(B$8,B53/B$8,0) >>> >>> >>> >>>




Interest income
B41“D43 This block of three rows for the historical
rows are blank because, in these three
periods, we expect that the interest income
number will be provided from the
historical reports. We do not need to
calculate them.
E41“G43 The cells in this block will have the interest
income of the related balance sheet assets. Since
we do not have the balance sheet built yet and
therefore don™t know the rows to reference at
the moment, leave these cells blank for the
moment.




TLFeBOOK
Chapter 9
172




B44 ¼ B40. Copy this to column D.
This is to enable the straight read from the input of
the historical interest income.
E44 ¼ IF(E40,E40,SUM(E41:E43)).
This formula will show the number in row 40 if
there is a value other than 0 there (and, if so, it
will disregard other values in rows 41“43).
Otherwise, it will read the sum of the rows 41
to 43.
Interest expense
B46“G52 This block for interest expense is similar to
the block for interest income, and you should
build it the same way. For the moment, leave
the formulas in cells E47“G51 blank since there
are no balance sheet references to link to.
EBT
B53 ¼ B33ÀB38þB44ÀB52. Copy this across the
row to column G.
B54 ¼ IF(B$8,B53/B$8,0). Copy this across the row
to column G. You can create this row quickly by
simply copying the formulas from row 23 for
the EBITDA margin, or row 34 for the EBIT
margin.

Provision for Taxes, Net Income, Dividends,
and Net to Retained Earnings

A B C D E F
56 Provision for taxes 360 40.0 47.0
57 Tax rate % =IF(B53,B56/B53,0) >>> >>> 35.0% 35.0%
58 =A56 =B56 >>> >>> =IF(E56,E56, >>>
E53*E57)
59 Net income =B53-B58 >>> >>> >>> >>>
60 Net margin =IF(B$8,B59/B$8,0) >>> >>> >>> >>>
61
62 Dividends 10.0 12.0 11.0
63 Payout ratio % =IF(B59,B62/B59,0) >>> >>> 10.0% 10.0%
64 =A62 =B62 >>> >>> =IF(E62,E62, >>>
E59*E63)
65 Net to ret™d earnings =B59-B64 >>> >>> >>> >>>




TLFeBOOK
Putting Everything Together 173




Provision for taxes
A reminder here: As we are building the model at the moment,
the numbers that you see in your model will not be the same as
the ones shown in the illustration. Those represent the final
numbers of the working model, so don™t be concerned if your
model-in-progress has different numbers.
B57 ¼ IF(B53,B56/B53,0). Copy this across the row
to column D.
B58 ¼ B56. Copy this across to column D.
E58 ¼ IF(E56,E56,E53*E57). Copy this across to the
end, column G.
Net income
B59 ¼ B53ÀB58. Copy this across to the end,
column G.
B60 ¼ IF(B$8,B59/B$8,0). Copy this across to the
end, column G.
Dividends
B63 ¼ IF(B59,B62/B59,0). Copy this across the row
to column D.
B64 ¼ B62. Copy this across to column D.
E64 ¼ IF(E62,E62,E59*E63). Copy this across to the
end, column G.
Net to retained earnings
B65 ¼ B59ÀB64. Copy this across the row to the
end, column G.




TLFeBOOK
Chapter 9
174




Surplus Fund, Cash, and ST Investments

A B C D E F
68
69 Proj Proj
70 BALANCE SHEET =B4 >>> >>> >>> >>>
71 ASSETS
72 Surplus funds
73 Interest rate % 5.000% 5.000%
74 Interest income =E73* >>>
SUM(D72:E72)/2
75
76 Cash 60.0 75.0 80.0 80.0 80.0
77 % of revenues IF(B$8,B76/B$8,0) >>> >>>
78 =A76 =B76 >>> >>> =IF(E76,E76, >>>
E77*E$8)
79 Interest rate 5.000% 5.000%
80 Interest income =E79* >>>
AVERAGE(D78:E78)
81
82 ST investments 34.0 35.0
30.0 32.0 33.0
83 % of revenues 3.6% 3.6% 3.3%
84 =A82 30.0 32.0 33.0 34.0 35.0
85 Interest rate 5.000% 5.000%
86 Interest income =E79* >>>
AVERAGE(D78:E78)
87


We are now beginning the balance sheet section. Some
cells are left blank (cells E72“G72, and others in later illustra-
tions) to indicate that you should leave them blank until every-
thing else is in place. The numbers that are already there in
the illustration are the numbers that will eventually appear at
completion.
Dates
B70 ¼ B4. Copy this across to column G.
Surplus funds
B72“D72 Blank. These are the cells that otherwise
would be for the asset plug calculations. For
historical numbers, there should be no need for
plugs since the numbers will come from
reported source data.
Note: You may want to put in the ˜˜Surplus
funds™™ (and the ˜˜Necessary to finance™™)




TLFeBOOK
Putting Everything Together 175




formulas for the historical periods, which can
be a useful way of checking if the source
(i.e., hard-coded) data are out of balance.
Just look at the ˜˜Surplus funds™™ and the
˜˜Necessary to finance™™ lines in the historical
periods. There should be 0 numbers on
these two lines.
E72“G72 Since we are in the process of building the
balance sheet and do not yet have a row for the
plug calculations, leave these blank for the
moment.
E74“G74 ¼ E73*SUM(D72:E72)/2. Copy across to column G.
We will need to calculate the interest income from
any surplus funds. Note the use of SUM/2,
rather than AVERAGE. This is because we
expect D72 to be blank, so a formula such as
¼E73*AVERAGE(D72:E72) would be considered
by Excel to be the same as ¼E73*E72. Excel™s
AVERAGE function does not consider a blank
cell as a cell to be counted. Since we want the
interest income to be on the basis of the average
of the starting and ending numbers, we need to
use the SUM/2 approach. In columns F and G,
we can use AVERAGE. Likewise, to use
AVERAGE in column E, another way would be
to put the 0 digit in cell D72.
Cash
B77 IF(B$8,B76/B$8,0). Copy across to column D.
B78 ¼ B76. Copy across to column D.
E78 ¼ IF(E76,E76,E77*E$8). Copy across to
column G.
B80“D80 Leave these cells blank, as we will not be
calculating the interest income for cash for the
historical years. Typically, historical interest
income is shown as one number combining the
earnings of all the different assets; there is no
need to calculate the interest income from the
different assets separately.




TLFeBOOK
Chapter 9
176




E80 ¼ E79*AVERAGE(D78:E78). Copy across to
column G.
We can use the AVERAGE function here because
the formula is looking to cell that will always
have a number (even if it is zero). In this way,
the AVERAGE function will calculate properly.
Compare this with the case of the ˜˜Surplus
funds™™ above, where we have to use the SUM/2
approach.
ST investments
This is similar in all respects to the ˜˜Cash™™ block above. Copy

<<

. 32
( 62 .)



>>