<< Предыдущая стр. 35(из 62 стр.)ОГЛАВЛЕНИЕ Следующая >>
Now that we have completed the first sweep through the income
statement and the balance sheet, letвЂ™s put in the balancing for-
mulas. After that, we will go back and fill in those rows that we
have left blank. When these rows are completed, we will have a
fully functioning model. Reminder: ExcelвЂ™s Iteration setting must
be on.
Remember: There are two ways to balance the model. I will
include both here, but you should choose only one. For the
model at this point, there is no benefit or disadvantage to
using either one. However, at a later point, if you want to include
the automatic repayment of debt through surplus funds, that
feature can be added only if you use the first method.

Balancing Method No. 1

A B C D E F
175
176 Assets without SF =IF(ISERROR(B78+B84+B91+B96+B100+B108+B112+B117),
0, B78+B84+B91+B96+B100+B108+B112+B117)
177 Liabs, Eq w/out NTF =IF(ISERROR(B134+B141+B146+B151+B158+B172),
0, B134+B141+B146+B151+B158+B172)
178
179
180 Surplus funds =MAX(B177-B176,0) >>> >>> >>> >>>
181 Necessary to finance =-MIN(B177-B176,0) >>> >>> >>> >>>
182

TLFeBOOK
Chapter 9
188

This method first takes the totals of the left-hand side and the
right-hand side of the balance sheet, excluding the rows for the
plugs. We take the difference between the two and put
the difference either as вЂ˜вЂ˜Surplus fundsвЂ™вЂ™ (the asset plug) or
вЂ˜вЂ˜Necessary to financeвЂ™вЂ™ (the liabilities plug).
B176 Вј IF(ISERROR(B78ГѕB84ГѕB91ГѕB96ГѕB100Гѕ
B108ГѕB112ГѕB117),0,B78ГѕB84ГѕB91ГѕB96Гѕ
B100ГѕB108ГѕB112ГѕB117).
The use of the ISERROR is very important
here. Since this formula is part of the
circular reference that we are deliberately
establishing in the model, the use of this func-
tion will ensure that if there are any error mes-
sages, the formula will flip to 0, essentially
breaking the loop of the circular reference and
preventing any error messages from being
вЂ˜вЂ˜trappedвЂ™вЂ™ in the loop. With this error trap, once
the error is removed, the formula runs as it
should and the iterative loop is re-established
automatically.
B177 Вј IF(ISERROR(B134ГѕB141ГѕB146ГѕB151Гѕ
B158ГѕB172),0, B134ГѕB141ГѕB146ГѕB151Гѕ
B158ГѕB172)
This is the formula for the right-hand side of
the balance sheet. This also must have the
ISERROR function.
B180 Вј MAX(B177-B176,0).
The use of the MAX function means that this row will
show a number only when liabilities Гѕ equity
exceeds assetsвЂ”precisely the calculation for the
asset plug that we want. This is the row that the
вЂ˜вЂ˜Surplus fundsвЂ™вЂ™ line in the balance sheet will read.
B181 Вј ГЂMIN(B177ГЂB176,0).
The use of the MIN function with the negative
sign means that this row will show a number
when assets exceed liabilities Гѕ equity. This
will give the вЂ˜вЂ˜Necessary to financeвЂ™вЂ™ plug that
we want. You can reverse the calculation and

TLFeBOOK
Putting Everything Together 189

use a MAX but without a negative sign
(i.e., ВјMAX(B176вЂ“B177.0) for the same effect.
Balancing Method No. 2
A B C D E F
175
176 Assets WITH SF =B118 >>> >>> >>> >>>
177 Liabs, Eq WITH NTF =B173 >>> >>> >>> >>>
178 Difference =B177-B176 >>> >>> >>> >>>
179 Accumulated difference IF(ISERROR >>> >>> >>> >>>
(B178+B179),0,
B178+B179)
180 Surplus funds =MAX(B179,0) >>> >>> >>> >>>
181 Necessary to finance =-MIN(B179,0) >>> >>> >>> >>>
182

This method uses the total assets, including the Surplus funds, and
the total liabilities and equity, including the NTF. The lines shown
here are for ease of illustration: row 178, which takes the differ-
ence between the two sides of the balance sheet, could just as
easily look at the totals directly from the balance sheet rows, in
which case we can omit row 176 and row 177.
B176 Вј B118. Copy across all columns to column G.
B177 Вј B173. Copy across all columns to column G.
B178 Вј B177ГЂB176. Copy across all columns to
column G.
The order of using liabilities and equity less assets
is helpful in that if the difference is positive, it
indicates an asset plug; if negative, it indicates a
liabilities plug.
B179 Вј IF(ISERROR(B178ГѕB179),0,B178ГѕB179).
Copy across all columns to column G.
This is the supremely elegant formula that makes
this second method work. The ISERROR func-
tion is included here and is the error-trapping
mechanism in case there is an error message
produced in the model. This is the line that
calculates the plug that the model needs.
B180 Вј MAX(B179,0). Copy across all columns to
column G.

TLFeBOOK
Chapter 9
190

This formula looks to the previous row and shows
the result for an asset plug if the row is positive.
B181 Вј ГЂMIN(B179,0). Copy across all columns to
column G.
This formula looks to the previous row and shows
the result for a liabilities plug if the row is
negative, but shows the result as a positive.

CONNECTING THE BALANCING LINES
In the model shown in the illustrations, the row references for
this section are the same whether for Balancing Method No. 1 or
Balancing Method No. 2.
Surplus Funds
A B C D E F
68
69 Proj Proj
70 BALANCE SHEET 2000 2001 2002 2003 2004
71 ASSETS
72 Surplus funds =E180 >>>
73 Interest rate % 5.000% 5.000%
74 Interest income 2.3 3.6
75

E72 Вј E180. Copy across all columns to column G.
This connects the row to the Surplus funds
balancing calculation.

Necessary to Finance
A B C D E F
136 Necessary to finance =E181 >>>
137 Interest rate 10.000% 10.000%
138 Interest expense 0.0 0.0
139

E136 Вј E181. Copy across all columns to column G.
This connects the row to the Necessary to finance
balancing calculation.

TLFeBOOK
Putting Everything Together 191

CONNECTING OTHER ROWS
TO COMPLETE THE MODEL
When we were building the model, we left some lines blank
because we did not have the rows fully laid out. Now that the
model is set in place in row positions, letвЂ™s go back and make
sure all the connections are now in place.

Interest Income

A B C D E F
40 Interest income 3.0 5.0 6.0
41 Surplus funds =E74 >>>
42 Cash =E80 >>>
43 ST investments =E86 >>>
44 Interest income 3.0 5.0 6.0 7.9 9.3
45

E41 Вј E74. Copy across all columns to G.
E42 Вј E80. Copy across all columns to G.
E43 Вј E86. Copy across all columns to G.

Interest Expense

A B C D E F
46 Interest expense 50.0 50.0 50.0
47 Necessary to finance =E138 >>>
48 ST notes =E124 >>>
49 Debt 1 =E143 >>>
50 Debt 2 =E148 >>>
51 Debt 3 =E153 >>>
52 Interest expense 50.0 50.0 50.0 47.7 47.7

E47 Вј E138. Copy across all columns to G.
E48 Вј E124. Copy across all columns to G.
E49 Вј E143. Copy across all columns to G.
E50 Вј E148. Copy across all columns to G.
E51 Вј E153. Copy across all columns to G.

TLFeBOOK
Chapter 9
192

Depreciation

A B C D E F
26 Depreciation 60.0 75.0 80.0
27 As % prior Net PPE na =IF(B108, >>> 8.5% 8.5%
C29/B108,0)
28 As % revenues 7.3% 8.3% 8.0%
29 Depreciation 60.0 75.0 80.0 =IF(E26,E26,IF(E27, >>>
E27*D108,E28*E8))
30

C27 Вј IF(B108,C29/B108,0). Copy to column D.
E29 Вј IF(E26,E26,IF(E27,E27*D108,E28*E8)).
Copy across columns to column G.

CONGRATULATIONS
If you have followed the steps in this chapter and have used the
numbers shown in the illustrations, you should have a model
that is fully functioning and dynamically balancing with the
same output numbers shown on pages 160вЂ“165. Now begin
experimenting with changing various inputs and see how the
balance sheet always balances, no matter what input changes
you make.
In the next chapter, we will look at how to extract the output
lines from this Input sheet and create other sheets, including the
cash flow statement. In this way, we can organize the presen-
tation of the lines more easily to get the format of the output
we want.

 << Предыдущая стр. 35(из 62 стр.)ОГЛАВЛЕНИЕ Следующая >>