<<

. 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 .)



>>