<<

. 48
( 62 .)



>>


A B C D E
1 BALANCE SHEET Year 1 Year 2
2 Surplus funds plug There is no plug here 0 =D68
3 Current assets 1 200 220
4 Current assets 2 600 500
5 Long-term assets 800 1,000
6 Total assets (TA) 1,600 =SUM(B2:B5) 1,720 =SUM(D2:D5)
7
8 Necessary to finance plug There is no plug here 0 =D69
9 Current liabilities 1 80 100
10 Current liabilities 2 80 90
11 Debt 1 300 =B71 28 =D71
12 Debt 2 300 =B72 300 =D72
13 Debt 3 300 =B73 300 =D73
14 Total liabilities (TL) 1,060 =SUM(B8:B13) 818 =SUM(D8:D13)
15
16 Common stock 500 800
102 =B17+D44
17 Retained earnings 40
18 Shareholdersí equity (SHE) 540 =SUM(B16:B17) 902 =SUM(D16:D17)
19 Total liabs & SH equity 1,600 =B14+B18 1,720 =D14+D18
20
21 Debt 1 input 300
22 Debt 1 before cash sweep 300 =B21 300 =IF(D21,D21,B71)
23
24 Debt 2 input 300
25 Debt 2 before cash sweep 300 =B24 300 =IF(D24,D24,B72)
26
27 Debt 3 input 300
28 Debt 3 before cash sweep 300 =B27 300 =IF(D27,D27,B73)
29
30 INCOME STATEMENT Year 1 Year 2
31
32 Revenues 250 300
33 Expense 110 120
34 Earnings before interest and tax 140 =B32-B33 180 =D32-D33
35
36 Interest income surplus funds (5%) 00 0 =AVERAGE(B2,D2)*5%
37 Interest expense NTF (10%) 00 0 =AVERAGE(B8,D8)*10%
38 Interest expense debt 1 (10%) 30 =AVERAGE(B22,B11)*10% 16 =AVERAGE(B11,D11)*10%
39 Interest expense debt 2 (10%) 30 =AVERAGE(B25,B12)*10% 30 =AVERAGE(B12,D12)*10%
40 Interest expense debt 3 (10%) 30 =AVERAGE(B28,B13)*10% 30 =AVERAGE(B13,D13)*10%
41 Earnings before taxes 50 =B34+B36-SUM(B37:B40) 104 =D34+D36-SUM(D37:D40)
42
43 Taxes (40%) 20 =B41*40% 41 =D41*40%
44 Net income 30 =B41-B43 62 =IF(ISERROR(D41-
D43),0,D41-D43)
45




TLFeBOOK
The Cash Flow Variation for Cash Sweep 261




A B C D E
46 CASH FLOW STATEMENT Year 1 Year 2
47
48 Net income 62 =D44
49
50 (Inc) dec in current assets 1 (20) =B3-D3
51 (Inc) dec in current assets 2 100 =B4-D4
52 (Inc) dec in long -term assets (200) =B5-D5
53 Inc (dec) in current liabilities 1 20 =D9-B9
54 Inc (dec) in current liabilities 2 10 =D10-B10
55 Non-sweep inc (dec) in debt 1 0 =D22-B22
56 Non-sweep inc (dec) in debt 2 0 =D25-B25
57 Non-sweep inc (dec) in debt 3 0 =D28-B28
58 Inc (dec) in common stock 300 =D16-B16
59 Other inc (dec) in retained earnings 0 = D17-B17-D44
60 Available cash flow for cash sweep 272 =SUM(D48,D50:D59)
61
62 Cash sweep of NTF 0 =-MIN(MAX(D60,0),B69)
63 Cash sweep of debt 1 (272) =-MIN(MAX(D60,0)+D62,
D22)
64 Cash sweep of debt 2 0 =-MIN(MAX(D60,0)+D62
+D63,D25)
65 Cash sweep of debt 3 0 =-MIN(MAX(D60,0)+D62
+D63+D64,0),D28)
66 Cash flow after cash sweep 0 =SUM(D60,D62:D65)
67
68 Cumulative surplus funds 0 =MAX(D66,0)+B68
69 Cumulative necessary to finance 0 Enter 0 to start 0 =-MIN(D66,0)+B69
70
71 Debt 1 after cash sweep 300 =B22 28 =D22+D63
72 Debt 2 after cash sweep 300 =B25 300 =D25+D64
73 Debt 3 after cash sweep 300 =B28 300 =D28+D65



General comments first:
The model shows two years, but the first year must be a
historical year (i.e., one whose numbers are already balanced).
This is because to find the balancing plug, we have to look at the
changes in each account of the balance sheet from one year to the
next. The first year™s changes, without any prior years, cannot be
calculated.
The model still retains the separate debt inputs shown in
rows 21“28. This is constructed in the same way as the debt
inputs in the previous chapter.


LOOKING AT THE MODEL ROW-BY-ROW
We will look at the model using the steps laid out.




TLFeBOOK
Chapter 15
262




Lay Out the Assets Side
A B C D E
1 BALANCE SHEET Year 1 Year 2
2 Surplus funds plug There is no plug here 0 =D68
3 Current assets 1 200 220
4 Current assets 2 600 500
5 Long-term assets 800 1,000
6 Total assets (TA) 1,600 =SUM(B2:B5) 1,720 =SUM(D2:D5)
7


Rows 2“6 This is a simple exercise. It is laying out
the balance sheet items. For year 1, there
is no plug for the Surplus funds, since we
are assuming that it is a historical year.
For the second year, the formula will
ultimately read ¼D68. But at this point,
since you have not set down row 68, you
can leave it blank. You will complete this
later. The ˜˜Total assets™™ is a sum of all the
rows, including the Surplus funds line.


Lay Out the Liabilities and Equity

A B C D E
8 Necessary to finance plug There is no plug here 0 =D69
9 Current liabilities 1 80 100
10 Current liabilities 2 80 90
11 Debt 1 300 =B71 28 =D71
12 Debt 2 300 =B72 300 =D72
13 Debt 3 300 =B73 300 =D73
14 Total liabilities (TL) 1,060 =SUM(B8:B13) 818 =SUM(D8:D13)
15
16 Common stock 500 800
17 Retained earnings 40 102 =B17+D44
18 Shareholders™ equity (SHE) 540 =SUM(B16:B17) 902 =SUM(D16:D17)
19 Total liabs & SH equity 1,600 =B14+B18 1,720 =D14+D18
20
21 Debt 1 input 300
22 Debt 1 before cash sweep 300 =B21 300 =IF(D21,D21,B71)
23
24 Debt 2 input 300
25 Debt 2 before cash sweep 300 =B24 300 =IF(D24,D24,B72)
26
27 Debt 3 input 300
28 Debt 3 before cash sweep 300 =B27 300 =IF(D27,D27,B73)
29




TLFeBOOK
The Cash Flow Variation for Cash Sweep 263




Rows 8“19 This is also laying out the liabilities and
equity. The only parts that need attention
are rows 11“13, for the debt. Write these
formulas now. Their values will appear
when you complete rows 71“73.
Rows 21“28 Create this section as we did in the last
chapter™s model. This represents the
inputs for the debt numbers before the
cash sweep.


Lay Out the Income Statement and
Calculate the Net Income
A B C D E
30 INCOME STATEMENT Year 1 Year 2
31
32 Revenues 250 300
33 Expense 110 120
34 Earnings before interest and tax 140 =B32-B33 180 =D32-D33
35
36 Interest income surplus funds (5%) 00 0 =AVERAGE(B2,D2)*5%
37 Interest expense NTF (10%) 00 0 =AVERAGE(B8,D8)*10%
38 Interest expense debt 1 (10%) 30 =AVERAGE(B22,B11)*10% 16 =AVERAGE(B11,D11)*10%
39 Interest expense debt 2 (10%) 30 =AVERAGE(B25,B12)*10% 30 =AVERAGE(B12,D12)*10%
40 Interest expense debt 3 (10%) 30 =AVERAGE(B28,B13)*10% 30 =AVERAGE(B13,D13)*10%
41 Earnings before taxes 50 =B34+B36-SUM(B37:B40) 104 =D34+D36-SUM(D37:D40)
42
43 Taxes (40%) 20 =B41*40% 41 =D41*40%
44 Net income 30 =B41-B43 62 =IF(ISERROR(D41-
D43),0,D41-D43)
45




Rows 30“44 This is similar to the layout we did in the
last chapter.
Rows 36“37 These are the calculations for the interest
income and expense linked to the plug
lines. They are already in position in the
balance sheet section, so go ahead and
write these formulas. Because there are no
plugs in the first year, you can simply
hard-code the cells as 0 for the first year.
Rows 38“40 Just create these lines for the moment,
even though they will not be calculating




TLFeBOOK
Chapter 15
264




correctly before everything is in place.
This is one of the disadvantages of
creating the balancing and cash sweep
through the cash flow. Nothing will work
properly until all the parts have been
fitted properly together! Right now, since
the debt numbers have not been reduced
by cash sweeps, the interest expenses are
still calculated at the maximum starting
debt numbers.
Rows 41“44 Complete as shown.

Find the Available Cash Flow
A B C D E
46 CASH FLOW STATEMENT Year 1 Year 2
47
48 Net income 62 =D44
49
50 (Inc) dec in current assets 1 (20) =B3-D3
51 (Inc) dec in current assets 2 100 =B4-D4
52 (Inc) dec in long -term assets (200) =B5-D5
53 Inc (dec) in current liabilities 1 20 =D9-B9
54 Inc (dec) in current liabilities 2 10 =D10-B10
55 Non-sweep inc (dec) in debt 1 0 =D22-B22
56 Non-sweep inc (dec) in debt 2 0 =D25-B25
57 Non-sweep inc (dec) in debt 3 0 =D28-B28
58 Inc (dec) in common stock 300 =D16-B16
59 Other inc (dec) in retained earnings 0 = D17-B17-D44
60 Available cash flow for cash sweep 272 =SUM(D48,D50:D59)
61


Rows 48“69 This is where the action happens for both
the balancing and the cash sweep.
Rows 48“59 These 13 lines look at the individual
accounts in the balance sheet, keep track

<<

. 48
( 62 .)



>>