ñòð. 26 |

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 |