<<

. 24
( 62 .)



>>

because the sign of the result gives a good reminder of what




TLFeBOOK
Balancing the Balance Sheet 125




kind of a plug it is: A positive difference means it is a plug on
the assets side, the Surplus funds. A negative difference means it
is an NTF amount. There is no need to use the cash flow state-
ment at all for this approach. As mentioned before, the cash flow
becomes nothing more than a reconciliation of the flows from the
income statement and the changes in the balance sheet.

SCHEMATIC OF THE BALANCE SHEET METHOD

Total liabilities and Equity À Total assets
Positive number is the Surplus funds plug
Negative number is the NTF plug

There are two variations in this method, and we will show
both. The first uses a total assets number that excludes the
Surplus funds number, and a total liabilities and equity number
that excludes the NTF number. The second includes them.
Once you have this total and the balance sheet is balanced
(with the interest income and interest expense from the plugs
properly accounted for in the income statement), then the cash
flow statement can be created. This task is made easier by the
fact that all the relevant numbers have been laid out in the
other two statements, and you know what the plug number is
for each year because of the calculations you have made compar-
ing the two sides of the balance sheet. This is a big help,
especially if you are still grappling with the intricacies of the
cash flow statement.

USING THE BALANCE SHEET
METHOD” ”VARIATION 1
We are going to use the balance sheet method in our starting
model. At the end of this chapter, you will also see the cash
flow way, but only as a quick comparison to the balance sheet
approach.
SCHEMATIC OF THE BALANCE SHEET METHOD”VARIATION 1

Total liabilities and Equity (excluding the NTF line) À
Total assets (excluding the Surplus funds line)
Positive number is the Surplus funds plug
Negative number is the NTF plug




TLFeBOOK
Chapter 7
126




To get the most out of the following discussion, I recom-
mend that you switch on your computer and open up Excel
and create the numbers for a test balance sheet as shown below.
For the next set of illustrations, although we will be looking
at the differences between the totals of the left- and right-hand
sides of the balance sheet, the illustrations will put the two sides
in a vertical format, with the assets on top and the liabilities and
shareholders™ equity numbers below.
The only formulas you need to put in are for the rows
shown. Note that the ranges in the SUM functions for the total
assets and total liabilities include a blank row each. This is so that
we can include the rows for the plug numbers (which we have
yet to enter). The two sides of the balance sheet are unbalanced
at the start. For the rows without formulas, enter the numbers
shown as ˜˜hard-coded™™ numbers.
I have put the second year™s numbers in column D so that
column C can be used to show the formulas for the first year.
As you build your own balance sheet, feel free to put the columns
for the two years next to each other.

A B C D E
1
2 BALANCE SHEET Year 1 Year 2
3
4 Current assets 1 100 200
5 Current assets 2 100 100
6 Long-term assets 300 400
7 Total assets (TA) 500 =SUM(B3:B7) 700 =SUM(D3:D7)
8
9
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) 500 =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 650 =D13+D17
19
20




TLFeBOOK
Balancing the Balance Sheet 127




Now enter the formulas shown in row 20.


A B C D E
1
2 BALANCE SHEET Year 1 Year 2
3
4 Current assets 1 100 200
5 Current assets 2 100 100
6 Long-term assets 300 400
7 Total assets (TA) 500 700
8
9
10 Current liabilities 1 80 100
11 Current liabilities 2 80 80
12 Debt 1 320 320
13 Total liabilities (TL) 480 500
14
15 Common stock 80 80
16 Retained earnings 40 70
17 Shareholders™ equity (SHE) 120 150
18 Total liabs & SH equity 600 650
19
20 Difference (TL+SHE-TA) 100 =B18-B7 -50 =D18-D7




Note the imbalances in row 20: in the first year, the imbal-
ance is a positive number when we use the formula total liabil-
ities (TL) þ shareholders™ equity (SHE) À total assets (TA); in the
second year, it is a negative number with the same formula. We
could have written the formula as TA À (TL þ SHE) and gotten to
the same magnitude of difference. However, by doing it the way
we did, we can follow the helpful clue that a positive difference
indicates a need for an asset plug (the Surplus funds plug).
Likewise, a negative number indicates a need for a liability
plug (the Necessary to finance plug).
Now that we know what the plugs are, as a quick way to
illustrate what we want to get to, let™s just enter them as hard-
coded numbers in the respective cells on row 3:
These two plugs on rows 3 and 9 are what we want to
appear in the worksheet. For the next step, since we are in




TLFeBOOK
Chapter 7
128




A B C D E
1
2 BALANCE SHEET Year 1 Year 2
3 Surplus funds plug 100 Hard-coded 0 Hard-coded
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 Hard-coded 50 Hard-coded
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




Excel, let™s have the spreadsheet do the work of showing these
numbers by writing a formula in the plug cells that directly refer
to the Difference line.
Important: Before we go any further, make sure that you
have turned on the Iteration setting in Excel by this sequence:
Tools > Options > Calculation tab, check the Iteration check box,
and set the Maximum iteration setting to 100.
Having done that, we can write formulas that refer directly
to the Difference row. We™ll use the MAX and MIN function to
read the numbers. Note the minus sign in front of the MIN
function: this formula reads the source only if it is a negative
number, but shows that result as a positive number.
But now, something odd happens. The balance sheet ˜˜flip-
flops™™: it seems to bring the plug numbers in properly, but then
the plugs seem to flip to 0. Next the plugs show up again and
then flip back to 0! You can see this more clearly by setting the




TLFeBOOK
Balancing the Balance Sheet 129




A B C D E
1
2 BALANCE SHEET Year 1 Year 2
3 Surplus funds plug 100 =MAX(B20,0) 0 =MAX(D20,0)
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 =-MIN(B20,0) 50 =-MIN(D20,0)
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




˜˜Maximum iteration™™ setting to 1. In this way, Excel only iterates
(calculates) once. Press the F9 key again and again to go through
the calculation stages to see the flip-flop in a one-step-at-a-time
slow motion.
Here is why this happens. Let™s go through the steps just for
the first year:
1. Cell B20 calculates the difference before the plugs appear.
The difference is 100.
2. This number is carried up into the Surplus funds line.
Total assets, because the total includes the Surplus funds
line, go up by 100.
3. Cell B20 now shows 0, because the difference has been
corrected by the inclusion of the plug number.
4. At the next iteration, the 0 difference number in B20 is
carried up to the Surplus funds line. The Surplus funds




TLFeBOOK
Chapter 7
130




line becomes 0. Now Total assets go down, since the 100

<<

. 24
( 62 .)



>>