. 46
( 62 .)


The Cash Sweep 249

Since all the Surplus funds have been used up,
there is no cash sweep for debt 2.
Row 41 This is the third debt. The formula takes into
account the still reduced amount of Surplus
funds available for debt 3, in this case 0. There
is no cash sweep repayment possible for debt 3
in the second year.
If these lines are confusing for you, feel free to break them
up into smaller ˜˜chunks™™ and insert more intermediate lines.
This is always a good route to take when you are working
with complex formulas doing many steps. For example, you
can order the rows like this:
Surplus funds available for all cash sweeps
Cash sweep of debt 1
Surplus funds available for debt 2 and debt 3
Cash sweep of debt 2
Surplus funds available for debt 3
Cash sweep of debt 3
Surplus funds available after all cash sweeps

Connect the Remaining Surplus Funds, If Any,
to the Balance Sheet; Do the Same for NTF

1 BALANCE SHEET Year 1 Year 2
2 Surplus funds plug 0 =B42 0 =D42
8 Necessary to finance plug 0 =B36 0 =D36
36 Necessary to finance 0 =-MIN(B34,0) 0 =-MIN(D34,0)
42 Surplus funds after all cash sweeps 0 =B38-SUM(B39:B41) 0 =D38-SUM(D39:D41)

Row 2 In row 2 at the top of the model, write a formula
that refers to the ˜˜Surplus funds™™ row after the
cash sweeps.

Chapter 14

Row 8 Likewise, in row 8, write a formula that refers to
the NTF row in row 36.

Calculate the Post-Sweep Debt
Numbers and Connect Those
to the Balance Sheet

11 Debt 1 300 =B44 28 =D44
12 Debt 2 300 =B45 300 =D45
13 Debt 3 300 =B46 300 =D46
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

Rows 44“46 These three rows show the post-cash sweep debt
numbers. They each take the pre-cash sweep
inputs and subtract the calculated cash sweep
Rows 11“13 These are the rows in the balance sheet section.
Write the references to rows 44“46 so that the
final post-sweep numbers now appear in the
balance sheet.
Now you can have a bit of fun changing the input numbers
so that you can test the cash sweep mechanism. The best way
to test is by increasing the liabilities and equity numbers. But
don™t increase the debt numbers: remember that with the way
the balancing formulas are set up, increasing a debt number
increases the starting Surplus funds, which leads to a repayment
of the (increased) debt! Look what happens when you change
common stock in D16 to 1000.

The illustration so far has excluded the income statement. You
can easily ˜˜attach™™ an income statement to this model. These are

The Cash Sweep 251

the steps we will be taking:
1. Important! Turn on the Iteration setting in the calculation
for the model by using the Tools>Options>Calculation
tab and checking the Iteration check box, if this is not
already done. You do not have to make any other changes
to the default setting of Automatic calculation: a
maximum number of iterations of 100, and a maximum
change of 0.001.
2. Select a location in the model and create the income
3. Specify interest income, interest expense, and tax rates,
and set up the formulas in the income statement to make
those calculations properly.
4. Link the income statement flows into the retained
earnings formula in the balance sheet.
5. Introduce an error-trapping formula to ensure that error
messages can clear themselves out of the model.

Turn on the Iteration Setting
It is now important that we turn the Iteration Setting on
because the addition of the income statement creates interactive
flows between it and the balance sheet that create circular

Create the Income Statement
I have added a simple income statement at the bottom of the
balance sheet for ease of illustration and to keep the formula
references we have been working with more or less the same.
In any model you build, where you locate the income statement,
balance sheet, and the cash flow statement (and other sheets) is
up to you and should follow the principle of being the easiest
to comprehend and interact with.
Here is the layout I have added at the bottom of the balance

Chapter 14

53 Revenues 250 300
54 Expense 110 120
55 Earnings before interest and tax 140 =B53-B54 100 =D53-D54
57 Interest income surplus funds (5%) 0 =AVERAGE(0,B2)*5% 0 =AVERAGE(B2,D2)*5%
58 Interest expense NTF (10%) 0 =AVERAGE(0,B8)*10% 0 =AVERAGE(B8,D8)*10%
59 Interest expense debt 1 (10%) 30 =AVERAGE(B22,B11)*10% 16 =AVERAGE(D22,D11)*10%
60 Interest expense debt 2 (10%) 30 =AVERAGE(B25,B12)*10% 30 =AVERAGE(D25,D12)*10%
61 Interest expense debt 3 (10%) 30 =AVERAGE(B28,B13)*10% 30 =AVERAGE(D28,D13)*10%
62 Earnings before taxes 50 =B55+B57-SUM(B58:B61) 104 =D55+D57-SUM(D58:D61)
64 Taxes (40%) 20 =B62*40% 41 =D62*40%
65 Net income 30 =B62-B64 62 =D62-D64

Interest and Tax Rates
Rows 57“64 For interest income, the interest rate used is
5 percent, and for interest expense for debt
(including the NTF debt), the interest rate
used in 10 percent. The tax rate used is 40
You will notice also that I have inserted these
rates directly into the formulas. I have done
this as a ˜˜quick and dirty™™ way of creating
the formulas, but for best practices, don™t
do it this way! Instead, we should keep
these interest rates as separate inputs unto
themselves in order to highlight what the
inputs are, and to make quickly changing
the assumptions possible by changing just
that one cell.

Interest Calculations: Using the AVERAGE
Rows 57“61 For interest income and expense
calculations, it is a good idea to use the
AVERAGE formula to calculate the

The Cash Sweep 253

average values for the year based on the
beginning and ending amounts. However,
the first year is always tricky: What are
the beginning amounts when there is no
prior year? For Surplus funds and the
Necessary to finance plugs, we can
simply take the average of a starting 0
and the final number. Thus, the formula
you see is written ¼AVERAGE(0,B2)*5%
for the first year™s interest income calcu-
lations for surplus funds. Admittedly, this
is the long way of writing ¼B2/2*5%
(half of the value in B2 multiplied by 5%),
and it has been done this way to give a
consistent form to the formula in the first
and second years. The second year™s for-
mula uses the AVERAGE construction in
a more expected way by taking the values
from the first- and second-year columns.
The first column in a model is usually
a historical year and there should not be
any plugs, so the formulas we are
considering here may be moot.
For the debt numbers, however, there
is an additional consideration. Even in
the first year, because there is a cash
sweep, we do have a ˜˜prior™™ amount
and an ˜˜ending™™ amount. The amount
before the sweep can be assumed to
be for the beginning of the year
(equivalent to the ending number of
a prior year). The amount after the
sweep is then the amount for the end
of the year. The formula for the debt
lines, for example, debt 1, reflects this:
¼AVERAGE(B22, B11)*10%. Again, if the
first year is historical, this may be a moot
point. For historical years, you would
have a combined interest expense number

Chapter 14

from the annual report, and you would
not have to calculate it.
The Year 2 formula of ¼AVERAGE
(D22,D11)*10% can also be written as
¼AVERAGE(B11,D11)*10%, as the two
references of B11 and D22 refer to the
same calculations.


. 46
( 62 .)