<< Ïðåäûäóùàÿ ñòð. 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 ñòð.)ÎÃËÀÂËÅÍÈÅ Ñëåäóþùàÿ >>