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