. 44
( 62 .)


difference between the two sides of the balance sheet
without the inclusion of the plug lines (Variation 1 in
the balance sheet method of balancing, Chapter 7). The
balancing formulas that look at the totals on the balance
sheet that include the plug numbers (Variation 2) cannot
work for the cash sweep.
4. Having a cash sweep does not mean that the balancing
plug on the liabilities side”the Necessary to finance
line”is put out of commission. The cash sweep is just an
additional feature to the whole balancing mechanism that
produces the Surplus funds line.

Let™s look at a schematic of the balance sheet. On the left side,
I have simplified the balance sheet to show only two distinct
parts: the Surplus funds plug and all other assets. On the right-
hand side, the liabilities are divided into two parts: long-term
bank debt and other liabilities that are not LT bank debt. The
shareholders™ equity is the third part.

The Cash Sweep 239

In a cash sweep, what we want to do is make the model
take any Surplus funds and reduce the LT bank debt.

Surplus funds Non-LT bank
debt liabilities

LT bank debt

SH equity

If the Surplus funds total exactly the LT bank debt, then the
final result will look like this:

Non-LT bank
debt liabilities

SH equity

It is more likely, however, that there is not an exact match,
in which case the end result will show a remaining amount of
Surplus funds with no LT bank debt, or the reverse, with no
Surplus funds, but with some LT bank debt remaining.

Here are the steps for putting the cash sweep into your model.
Let™s assume the following facts to make the discussion a little
Surplus funds will be 100

LT bank debt 1 is 75

LT bank debt 2 is 40

LT bank debt 3 is 50

Chapter 14

If you remember the definitions for the ˜˜static™™ and
˜˜dynamic™™ numbers (see p. 130), the following steps are directed
at finding totals of the static numbers on the balance sheet for each
1. Find the static total for the assets side by summing:
a. All current and long-term assets without the Surplus
funds line.
2. Find the static total for the liabilities and equity side
by summing:
a. All current liabilities.
b. All long-term liabilities without the Necessary to
finance (NTF) plug and long-term bank debt.
c. All long-term bank debt including known new
additions and known amortizations over the forecast
period without any of the automatic repayment that is
the cash sweep for the current period. However, the
long-term bank debt should include any cash sweep
reductions from the previous period.
d. The shareholders™ equity total.
3. Find the difference by subtracting total (1) from total (2).
4. If the difference is negative, this means the model will
show an NTF plug and there is no cash sweep possible
for the period. The NTF plug will be the differences
number, but is shown as a positive.
5. If the difference is positive, then that means that the
model will show a Surplus funds plug, and this Surplus
funds plug can be used to shave down the LT bank debt. In
short, a cash sweep is possible for this period. In our
assumptions for this illustration, we have a surplus funds
amount of 100.
6. Take this starting surplus funds number and compare it
with the LT bank debt 1, which is 75. Because Surplus
funds are more than the debt, LT bank debt 1 is fully
repaid. (We say the cash sweep is 75.)
7. Now we still have 25 of Surplus funds remaining. We
apply all of this to pay down LT debt 2. LT bank debt 2
goes from 40 to 15.

The Cash Sweep 241

8. LT bank debt 3 remains as 50. There are no more Surplus
funds for the cash sweep.
9. At the end of this exercise, we connect the following
numbers back up to the balance sheet:
a. Any remaining Surplus funds number in our
calculation block is referenced into the Surplus
funds line on the balance sheet.
b. Each cash sweep is referenced back to the
corresponding bank debt. The bank debts shown
in the balance sheet now show the reduced totals
after the automatic repayments.
c. Any NTF number is referred into the NTF line on
the balance sheet.
You may be feeling quite confused now, so let™s look at
a simple model to see what everything would look like on
the screen. The numbers are more fully fleshed out, so they
are different from the simple numbers we used above. The
full layout is shown below and is somewhat similar to the illus-
trations you have seen earlier regarding balancing the balance
sheet. However, you will see that this has additional formulas
for the cash sweep calculations. The inputs are also simplified as
direct inputs. The intent of this illustration is to allow you to use
test numbers to see the cash sweep calculations.
The gray areas shown in the model are the input cells. On
the screen, these cells are marked in light yellow. But they come
out as gray in a black-and-white printout.

Chapter 14

1 BALANCE SHEET Year 1 Year 2
2 Surplus funds plug 0 =B42 0 =D42
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)
8 Necessary to finance plug 0 =B36 0 =D36
9 Current liabilities 1 80 100
10 Current liabilities 2 80 90
11 Debt 1 300 =B44 28 =D44
12 Debt 2 300 =B45 300 =D45
13 Debt 3 300 =B46 300 =D46
14 Total liabilities (TL) 1,060 =SUM(B8:B13) 818 =SUM(D8:D13)
16 Common stock 500 800
17 Retained earnings 40 102
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
21 Debt 1 input 300
22 Debt 1 before cash sweep 300 =B21 300 =IF(D21,D21,B44)
24 Debt 2 input 300
25 Debt 2 before cash sweep 300 =B24 300 =IF(D24,D24,B45)
27 Debt 3 input 300
28 Debt 3 before cash sweep 300 =B27 300 =IF(D27,D27,B46)
30 Assets without SF 1,600 =SUM(B3:B5) 1,720 =SUM(D3:D5)
31 Total liabs (no NTF, before sweep) 1,060 =B9+B10+B22+B25+B28 1,090 =D9+D10+D22+D25+D28
32 Total SH equity 540 =B18 902 =D18
33 Total liabs + SHE (no NTF, bef sweep) 1,600 =B31+B32 1,992 =D31+D32
34 Difference (TL+SHE)-TA 0 =B33-B30 272 =D33-D30
36 Necessary to finance 0 =-MIN(B34,0) 0 =-MIN(D34,0)
38 Surplus funds before all cash sweeps 0 =MAX(B34,0) 272 =MAX(D34,0)
39 Cash sweep of debt 1 0 =MIN(B38,B22) 272 =MIN(D38,D22)
40 Cash sweep of debt 2 0 =MIN(B38-B39,B25) 0 =MIN(D38-D39,D25)
41 Cash sweep of debt 3 0 =MIN(B38-B39-B40,B28) 0 =MIN(D38-D39-D40,D28)
42 Surplus funds after all cash sweeps 0 =B38-SUM(B39:B41) 0 =D38-SUM(D39:D41)
44 Debt 1 after cash sweep 300 =B22-B39 28 =D22-D39
45 Debt 2 after cash sweep 300 =B25-B40 300 =D25-D40
46 Debt 3 after cash sweep 300 =B28-B41 300 =D28-D41

The Cash Sweep 243

Some general comments first.
The model shows two years of the balance sheet only. This
is all that is needed for this first look. We will add the income
statement later. For this approach, we do not need the cash flow
Each year is stacked as a column, with assets at the top and
liabilities and shareholders™ equity at the bottom. There are only
10 items being input in each year: 3 for assets, 5 for liabilities,
and 2 for equity.
I have assumed that the first year is a historical year and
have made the numbers balance. There are no Surplus funds or
Necessary to finance plugs. However, the balancing and cash
sweep formulas that we are creating for this balance sheet
will work for the first year. Once you have created this small
example, you may want to change the inputs for the first year
to see the cash sweep in the first year also.
The most unusual feature of the inputs is the separate
inputs for the debt tranches 1, 2, and 3. Whereas all the other
inputs are part of the balance sheet ˜˜block,™™ these debt inputs are
put separately below (rows 21, 24, and 27). The reason for this is
that the inputs are where we specify the starting pre-cash sweep
debt numbers. If there are any Surplus funds, then we want the
cash sweep to automatically repay them to the extent possible.
The debt numbers that appear as a part of the balance sheet
(rows 11 to 14) are the debt outstanding after the cash sweep
We will connect the income statement later by (1) linking net
income to the balance sheet™s retained earnings and (2) linking
the income statement™s interest expense calculations to the final
(post-cash sweep) debt numbers so that the model correctly
reduces the interest as debt outstandings are reduced. The cash
sweep mechanism will continue to work when you do this.

I will explain the model using the procedure laid out for building
the balance sheet in Chapter 9.


. 44
( 62 .)