. 45
( 62 .)


Chapter 14

Find the Static Total for the Assets Side

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)
30 Assets without SF 1,600 =SUM(B3:B5) 1,720 =SUM(D3:D5)

Row 30 For each year, do a SUM function of the
assets side. Make sure you do not include
the line for the ˜˜Surplus funds™™ row (row
2). We want this total for the assets to be
the static total”the total will not change
during the balancing iterations.
Rows 21“28 These are the input rows for the starting
(i.e., before the cash sweep) debt

Find the Static Total for the Liabilities
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)
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)
31 Total liabs (no NTF, before sweep) 1,060 =B9+B10+B22+B25+B28 1,090 =D9+D10+D22+D25+D28

The Cash Sweep 245

numbers. In the first year, you will notice
that they are straightforward references to
the input cells: row 22 simply reads the
input cell on row 21 for debt 1, and so on.
This ensures that the debt numbers are
static and do not change with the
iterations. For the second year, however,
row 22 is a little more complicated.
The formula in D22:
gives you the option of hard-coding a
number for the second year. By leaving
the input cell D25 blank, however, you
let the model use the first year™s ending
(i.e., after the cash sweep) debt 1 number,
which it takes from the cell B44. Cell B44
is not shown in this illustration.
Doesn™t this mean that the formulas are
now using dynamic numbers, and that
we run the risk of flip-flopping as we run
the calculations? No, because the key to
thinking whether you are using static or
dynamic numbers is whether they change
during the iterations for the period they
are in. For the second year, the formulas
use post-cash sweep numbers from the
first year. This means that so far as the
second year is concerned, the formula is
using static numbers.
Row 31 Add the ˜˜Current liabilities™™ rows
(rows 9 and 10) and then add starting
debt numbers (rows 22, 25, 28). Again,
make sure that you do not include the
line for ˜˜Necessary to finance˜˜ (row 8)
because we want a static total. As you
develop other models based on this
template and you have more accounts
in the liabilities, the important thing to

Chapter 14

remember while you are creating the total
for the liabilities side is to exclude the NTF
line and to include only the starting debt
numbers that have been marked for the
cash sweep.
This is the total for the liabilities,
representing the static numbers (without
the NTF row) and the pre-cash sweep
debt numbers.

Find the Static Total for the Equity
16 Common stock 500 800
17 Retained earnings 40 102
18 Shareholders™ equity (SHE) 540 =SUM(B16:B17) 902 =SUM(D16:D17)
32 Total SH equity 540 =B18 902 =D18

Row 18 This is a straightforward SUM formula
of the lines we have in ˜˜Shareholders™
Row 32 This is a direct reference to row 18, and
it is there for the visual proximity for
the next set of formulas.

Find the Difference Between (Total
Liabilities þ Equity) and Total Assets
  A  B  C  D  E 
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 

Row 34 Subtract total assets (without surplus
funds) from the total liabilities (without
NTF) and shareholders™ equity.

The Cash Sweep 247

If a Negative Difference, This Is the
Necessary to Finance Plug
34 Difference (TL+SHE)-TA 0 =B33-B30 272 =D33-D30
36 Necessary to finance 0 =-MIN(B34,0) 0 =-MIN(D34,0)

Row 34 When this difference is negative, this indicates
that the model is showing a funding deficit
which will need to be met by the Necessary to
Finance plug.
Row 36 Use the ÀMIN(B34,0) formula (note the minus
sign in front) to have the difference appear on
row 36. The minus sign in the front will make
this negative difference appear as a positive
number. This will be referenced back into the
balance sheet in the last steps in this procedure.
If the calculations show an NTF plug, then
there is no cash sweep to be done in the year,
since the cash sweep is based on the availability
of surplus funds.

If the Difference Is Positive, This Is the Surplus
Funds Available for the Cash Sweep
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)

Row 38 If the difference is positive, then we can start the
cash sweep process. Use the MAX(B34,0)
formula to have the positive difference appear

Chapter 14

on row 38. This is the row that shows the
amount of surplus funds with which we can
repay debt.

Use This Surplus Funds and Apply It for Payment
of the Successive Debt Tranches

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)

Row 39 This is the first debt on which we will be
performing a cash sweep. The formula used is
MIN(B38,B22), which is to say ˜˜the amount that
is the lesser of (1) the surplus funds available for
cash sweeps, and (2) the starting pre-cash sweep
number for debt 1.˜˜ We use the MIN function so
that the formula does not overpay debt 1 by
applying a repayment figure that is greater than
the outstanding debt.
In the first year, since there are no Surplus funds,
there is no cash sweep. In the second year, we
see a cash sweep repayment of 272, thus
reducing debt 1 to 28, from 300.
Row 40 This is the second debt for the cash sweep. In
principle, we are using the same formula as the
one used for debt 1, but with one difference:
Because some of the surplus funds have been
used to repay debt 1, the amount available for
debt 1 must reflect that. Consequently, the
formula is MIN(B38ÀB29,B25), which is to say
˜˜the amount that is the lesser of (1) the surplus
funds available less the cash sweep for debt 1, and
(2) the starting pre-cash sweep number for debt 2.


. 45
( 62 .)