<<

. 26
( 62 .)



>>

1
2 BALANCE SHEET Year 1 Year 2
3 Surplus funds plug 100 =B22 0 =D22
4 Current assets 1 100 200
5 Current assets 2 100 100
6 Long-term assets 300 400
7 Total assets (TA) 600 =SUM(B3:B6) 700 =SUM(D3:D6)
8
9 Necessary to finance plug 0 =B23 50 =D23
10 Current liabilities 1 80 100
11 Current liabilities 2 80 80
12 Debt 1 320 320
13 Total liabilities (TL) 480 =SUM(B9:B12) 550 =SUM(D9:D12)
14
15 Common stock 80 80
16 Retained earnings 40 70
17 Shareholders™ equity (SHE) 120 =SUM(B15:B16) 150 =SUM(D15:D16)
18 Total liabs & SH equity 600 =B13+B17 700 =D13+D17
19
20 Difference (TL+SHE-TA) 0 =B18-B7 0 =D18-D7
21 Accumulated difference 100 =IF(ISERROR -50 =IF(ISERROR
(B20+B21),0, (D20+D21),0,
B20+B21) D20+D21)
22 SF plug calculation 100 =MAX(B21,0) 0 =MAX(D21,0)
23 NTF plug calculation 0 =-MIN(B21,0) 50 =-MIN(D21,0)




TLFeBOOK
Chapter 7
136




Compared to the first balancing method, this second
approach has the following pluses:
It is simple to set up: you use the totals on the two sides
u

of the balance sheet to get going. Of course, this assumes
that the totals correctly include all the accounts.
It is helpful to use when you are still building your bal-
u

ance sheet and adding new lines here and there. Because
the balancing formula uses the totals of each side of the
balance sheet, you do not have to tinker with the balan-
cing formula every time you insert new accounts. So long
as the new accounts flow properly into the totals, the
balancing system will work.
But it has these minuses:
It takes a little bit of thinking to understand it, and if you use
u

it in a model for distribution to others, expect a few
questions from people asking how it works and quite
possibly voicing their suspicions about the self-
referencing structure.
You cannot use this approach if you need to create a ˜˜cash
u

sweep™™ feature, which is shown in Chapter 14.
The formulas for both approaches to balance sheet balancing
work quickly because we have neither included any effects of the
interest income from Surplus funds (remember, it is assumed to be
cash), nor have we included the interest expense from Necessary to
finance (assumed for the moment to be a form of debt). This is
because we have not linked the income statement, where these
interest numbers would be calculated, to the balance sheet. When
we do, the formulas will still work, but they will have to iterate a
few times, with each round of calculations showing incrementally
smaller additions to the Accumulated difference line as the model
converges.

FOR THE NEXT STEP: EFFECTS OF
THE INCOME STATEMENT
Let™s connect the income statement to the balance sheet. We do
that by having the last line in the income statement flow into the




TLFeBOOK
Balancing the Balance Sheet 137




retained earnings account of the model. The last line in our
simple model is the Net income after taxes line. However,
models often come with other flows after this, such as Extra-
ordinary income or expense and Dividends. In that case, the
last line in the income statement would be something called
˜˜Net to retained earnings™™ and is equivalent to Net income
after taxes less the additional expenses, and that would be the
line we link into the Retained earnings account.



Income
statement



Net income




Net income flows into
the Retained earnings
account in SH Equity

Liabilities

Assets



SH Equity



The Retained earnings account is a collection of the current
year™s earnings from the income statement and all previous
year™s earnings. So, each formula for this account is the sum of
the net income from the previous year and the current year.
Let™s insert about 12 rows or so at the top of the balance
sheet that you have been working on. For the following illustra-
tion, I will be using the balance sheet with the first balancing
method, but all the formula changes will also work for the
second method.




TLFeBOOK
Chapter 7
138




As we insert the lines, Excel will, of course, automatically
adjust all the formula references.
A B C D E
1
2 INCOME STATEMENT Year 1 Year 2
3 Sales 200.0 240.0
4 Expenses 120.0 140.0
5 EBIT 80.0 =B3-B4 100.0 =D3-D4
6
7 Interest income of SF (5%) 4.6 =B17*5% 2.3 =AVERAGE(B17,
D17)*5%
8 Interest expense of NTF (10%) 0.0 =B23*10% 2.4 =AVERAGE(B23,
D23)*10%
9 Interest expense of Debt (10%) 32.0 =B26*10% 32.0 =AVERAGE(B26,
D26)*10%
10 EBT 52.6 =B5+B7-B8-B9 67.9 =D5+D7-D8-D9
11
12 Taxes (40%) 21.0 =B10*40% 27.2 =D10*40%
13 Net income 31.5 =B10-B12 40.7 =D10-D12
14
15
16 BALANCE SHEET Year 1 Year 2
17 Surplus funds plug 91.5 =B37 0.0 =D37
18 Current assets 1 100.0 200.0
19 Current assets 2 100.0 100.0
20 Long-term assets 300.0 400.0
21 Total assets (TA) 591.5 =SUM(B17:B20) 700.0 =SUM(D17:D20)
22
23 Necessary to finance plug 0.0 =B38 47.7 =D38
24 Current liabilities 1 80.0 100.0
25 Current liabilities 2 80.0 80.0
26 Debt 1 320.0 320.0
27 Total liabilities (TL) 480.0 =SUM(B23:B26) 547.7 =SUM(D23:D26)
28
29 Common stock 80.0 80.0
30 Retained earnings 31.5 =B13 72.3 =B30+D13
31 Shareholders™ equity (SHE) 111.5 =SUM(B29:B30) 152.3 =SUM(D29:D30)
32 Total liabs & SH equity 591.5 =B27+B31 700.0 =D27+D31


33
34 Assets without SF 500.0 =SUM(B18:B20) 700 =SUM(D18:D20)
35 Liabs & SHE without NTF 591.5 =SUM(B24:B26)+ 652.3 =SUM(D24:D26+
B31 D31
36 Difference 91.5 =IF(ISERROR -47.7 =IF(ISERROR
(B35-B34),0, (D35-D34),0,
B35-B34) D35-D34)
37 SF plug calculation 91.5 =MAX(B36,0) 0 =MAX(D36,0)
38 NTF plug calculation 0 =-MIN(B36,0) 47.7 =-MIN(D36,0)




TLFeBOOK
Balancing the Balance Sheet 139




In this layout, we now have the complete and fully func-
tioning system of flows between the income statement and the
balance sheet. We have created a model! If you have been follow-
ing these steps on your own computer, you can experiment
by changing any of the static numbers such as the sales
line, the current assets and liabilities, and even the interest and
tax rates in the model you have created. As you make the
changes, the model will recalculate and show a balanced balance
sheet.
The model remains a very rudimentary model at this stage,
however. So, some points to note:
The interest rates used for interest income and expense
u

have been written directly into the formulas in order
to make the illustration more compact. In practice with
a real model, this is not a good idea. Instead, you
should have these rates in separate cells, with the
formulas referencing them. In this way, the rates are
clear and plain to see. Changing them later if you
want to test different assumptions also becomes much
easier.
The calculations of interest income and expense are
u

usually done on the average of the beginning and
ending cash or debt. For the first year, however, we
have just used a full year™s calculations for simplicity of
illustration.
In the second year, the formulas use the average of the
u

balance sheet numbers multiplied by the interest rates.
For the interest on the NTF plug, this is a calculation of
25 multiplied by 10 percent, to yield 2.5. The number 25
is the average of 0 (the first year™s number) and 47.7
(the second year™s number). We use the average because
the NTF number in the second year did not appear on
the first day of that second year. You can imagine that
as the plug was 0 at the end of the first year, it was just
a little over 0 on the first day of the second year. Only
after a full year has passed did the NTF become 47.7.
The plug is the result of the flows occurring during
the year, and in the absence of any other information,




TLFeBOOK
Chapter 7
140




we make the assumption that the NTF grew in a
linear fashion from 0 to 47.7 over the course of the year.
To get the interest expense for this, we have to take the
average of 0 and 47.7, and multiply the result with the
interest rate.
Usually, because the first year of a model is a historical
u

period and you will have the hard-coded interest data in
your source document, you do not need to calculate the
interest numbers. So, you could have the formulas for the

<<

. 26
( 62 .)



>>