ñòð. 25 |

5. Cell B20 shows 100 again . . . .

If you follow these steps, you will see that there is a

circularity here (this is why it was important to make sure

that the Iteration setting is on). The Difference line in

row 20 is using numbers that include itself through the plug

numbers.

The basic idea of getting to a plug number by looking at the

difference between the two sides of the balance sheet is still

sound. But we need to understand a little more how these

numbers work with each other to get to a good, workable, and

stable balancing system.

â€˜â€˜STATICâ€™â€™ AND â€˜â€˜DYNAMICâ€™â€™ NUMBERS

Letâ€™s look at the same balance sheet again, but now letâ€™s under-

stand that the numbers can fall into two types:

Static numbers are numbers that do not change during any

u

iteration calculations. Of course, you can change them in

order to reflect changes in your assumptions. The main

distinction is that they remain unchanged during the

iteration calculations. All the balance sheet account

numbers in the illustration, except for the Surplus funds

and the Necessary to finance lines (and the sums which

include these plugs), are static numbers.

Dynamic numbers are those that change during the

u

iteration calculations. The Difference numbers and

the Surplus funds and Necessary to finance plugs

are dynamic numbers in the worksheet we are working

with.

The important thing to know at this stage is this: to pre-

vent the â€˜â€˜flip-flops,â€™â€™ we have to keep the dynamic numbers

out of the calculations for the plugs. Hereâ€™s how to do it.

TLFeBOOK

Balancing the Balance Sheet 131

This illustration shows all formulas for the rows used in the

balancing:

A B C D E

1

2 BALANCE SHEET Year 1 Year 2

3 Surplus funds plug 100 =B23 0 =D23

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 =B24 50 =D24

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 700

19

20 Assets without SF 500 =SUM(B4:B6) 700 =SUM(D4:D6)

21 Liabs & SHE without NTF 600 =SUM(B10:B12)+ 650 =SUM(D10:D12)

B17 +D17

22 Difference 100 =IF(ISERROR -50 =IF(ISERROR

(B21-B20),0, (D21-D20),0,

B21-B20) D21-D20)

23 SF plug calculation 100 =MAX(B22,0) 0 =MAX(D22,0)

24 NTF plug calculation 0 =-MIN(B22,0) 50 =-MIN(D22,0)

At the bottom of the block of numbers, we put two addi-

tional lines:

In row 20, Assets without the Surplus funds line. Shown in

u

the C column is the formula, which looks only at the three

rows on rows 4 to 6.

In row 21, Liabilities and Shareholdersâ€™ equity without

u

the Necessary to finance line. The C column shows the

formula being used.

These two lines consist of static numbers. From these

two, we calculate the Difference (row 22). The next two rows

TLFeBOOK

Chapter 7

132

(rows 23 and 24) are a nice way to organize the Difference into

either the Surplus funds plug or the Necessary to finance plug,

using the MAX and MIN functions. The balance sheet lines for

the plugs read these two rows. (We could have written the MAX

and MIN formulas directly in the balance sheet, by the way.

However, doing it this way makes things a little bit clearer.)

Now the balance sheet does not flip-flop. (Remember to

reset the maximum number of iterations to a number such as

100, if you had changed it to 1.)

The Difference row has the ISERROR function as an error

trap. When there is an error message, the ISERROR is true, and

so the formula returns a 0, effectively breaking the circular refer-

ence. This prevents the error from being caught endlessly in the

loop. Once the source of the error is removed, the ISERROR test

is false, and so the Difference formula is active again.

For showing the full totals on the balance sheet, Total Assets

(row 7) and Total Liabilities (row 13) should include the rows for

the plugs. We just donâ€™t use these for our calculations.

USING THE BALANCE SHEET

METHODâ€” â€”VARIATION 2

Here is the second variation for the balance sheet balancing.

Having made a big fuss about static and dynamic numbers,

this second method makes no distinction between static and dynamic

numbers. It uses the difference between Total assets and Total

liabilities Ã¾ Shareholdersâ€™ equity, but in a way that does not lead

to the â€˜â€˜flip-flops.â€™â€™

SCHEMATIC OF THE BALANCE SHEET METHODâ€”VARIATION 2

Find the incremental difference between Total assets (including Surplus funds line)

and Total liabilities and Shareholdersâ€™ equity (including the NTF line) with each iteration

Add incremental difference to a â€˜â€˜holder cellâ€™â€™

Positive number in the holder cell is read by the Surplus funds plug

Negative number is read by the NTF plug

Recall that the flip-flops occur because the plug depends on

the difference between the two sides of the balance sheet.

However, once the plugs are included in the two sides, the

TLFeBOOK

Balancing the Balance Sheet 133

difference no longer exists, and the plug goes back to 0. As Excel

makes another round of iterative calculations, the difference

shows up again, creating the plugs. And so on . . . .

This second method uses two rows of formulas, which

together prevent the plugs from disappearing as the difference

gets balanced out. Look at the illustration below and note the

lines at the bottom of the balance sheet. In addition to the line

labeled â€˜â€˜Difference,â€™â€™ there is a new line in row 21 called

â€˜â€˜Accumulated difference.â€™â€™ The MAX and MIN plug formulas

now read this row.

A B C D E

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 700

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 550

14

15 Common stock 80 80

16 Retained earnings 40 70

17 Shareholdersâ€™ equity (SHE) 120 150

18 Total liabs & SH equity 600 700

19

20 Difference (TL+SHE-TA) 0 =B18-B7 0 =D18-D7

21 Accumulated difference 100 =B20+B21 -50 =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)

At first glance, the formula in row 21 looks just plain wrong.

Look at B21 closely again. It is the sum of the row above it (B20)

and itself (B21). How does this work?

Reminder: We should still be using Excel with the Iteration

setting turned on, as before.

TLFeBOOK

Chapter 7

134

The Difference row produces the difference between the two

sides of the balance sheet. The Accumulated difference row acts

as a bucket that holds this difference; it â€˜â€˜collectsâ€™â€™ the Difference

number. Letâ€™s imagine what happens during successive iterations

in Excel. Letâ€™s assume the same first year balance sheet as above.

At the start, before Excel starts calculating, the two lines read as

follows:

Difference 0

Accumulated difference 0

As the sweep of calculation hits the Difference line, it

changes to 100

Difference 100

Accumulated difference 0

Now it is the Accumulated differenceâ€™s turn to be calculated.

Remember the formula is Accumulated difference Â¼ Difference Ã¾

Accumulated difference. As it calculates, it becomes 100 (the

Difference) Ã¾ 0 (the Accumulated difference value before it is cal-

culated) and so ends up as 100 (the Accumulated difference after

it is calculated).

Difference 100

Accumulated difference 100

The Accumulated difference number is read by the Surplus

funds line. Now the balance sheet is balanced. So, as the iteration

occurs again, the Difference number now flips to 0.

Difference 0

Accumulated difference 100

Now as the calculation hits the Accumulated difference row,

it stays at 100, because the formula reads: Difference (0) Ã¾

Accumulated difference (100, from the prior iteration), which

equals 100. Remember, the plug is using the Accumulated

difference row. This is why there is no flip-flop.

This formula is simplicity itself. Itâ€™s a little puzzling at first,

but it is truly elegant. The only change we need to make beyond

the formula you see is the addition of an ISERROR function to

TLFeBOOK

Balancing the Balance Sheet 135

trap any errors that might be introduced into this loop. Thus, the

formula in cell B21 should read:

Â¼ IFÃ°ISERRORÃ°B20Ã¾B21Ãž,0,B20Ã¾B21Ãž

Of course, the formula in cell D21 should have the same

structure:

Â¼ IFÃ°ISERRORÃ°D20Ã¾D21Ãž,0,D20Ã¾D21Ãž

So here is the final screen, with the rows holding formulas

for the balancing calculations shown:

A B C D E

ñòð. 25 |