<<

. 25
( 62 .)



>>

of the plug is no longer there.
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
( 62 .)



>>