. 49
( 62 .)


of their changes, and show them as
either a source of cash or a use of cash.
These are the changes in the debt before any
Rows 55“57
changes that are the result of the cash
sweep. This calculation is a way of
identifying the static numbers that we
need to work with.
Row 60 This is available cash for cash sweeps.

The Cash Flow Variation for Cash Sweep 265

These 13 lines do the same job that was
accomplished by five rows in the model
seen in the last chapter (rows 30“34 in
the illustration on page 246).

Apply the Available Cash Flow to the Cash Sweep
60 Available cash flow for cash sweep 272 =SUM(D48,D50:D59)
62 Cash sweep of NTF 0 =-MIN(MAX(D60,0),B69)
63 Cash sweep of debt 1 (272) =-MIN(MAX(D60,0)+D62,
64 Cash sweep of debt 2 0 =-MIN(MAX(D60,0)+D62
65 Cash sweep of debt 3 0 =-MIN(MAX(D60,0)+D62
66 Cash flow after cash sweep 0 =SUM(D60,D62:D65)

Row 60 If this row is positive, then a cash sweep is
possible. If it is negative, then there is no
cash sweep possible.
Row 62 This is an important line, so let™s go slowly.
One of the possibilities we have to
consider is that there is an outstanding
Necessary to finance from the previous
period. As you expand this model to
subsequent years, then this formula will
be very important. Because the MIN
function ignores blank cells, make sure
you enter a 0 in cell B69 to make this
formula work properly.
Essentially, the NTF becomes our fourth
debt item that we need to apply the cash
sweep to. We are putting NTF here so that
it is the first line on which the cash sweep
is applied. However, we could also place
it as the fourth item, because we can
rationalize that the NTF is the revolver
facility that is being renewed from year to
year. Consequently, under this thinking,

Chapter 15

the repayment of debts 1 to 3 would be
considered more important. For this
illustration, however, we will consider it
as the first to be cash swept.
The formula first uses a MAX(D60,0)
because we want to use the number from
the available cash flow only if it is a
positive number. We then use the MIN
function to get the possible repayment
for NTF. In the second year, the formula
returns 0 because there is no NTF to be
The formula also uses a minus sign at the
front, and this is true for all the repay-
ments. Any repayment will show up as a
negative number, consistent with the
presentation in the cash flow statement
that uses of cash are shown as negative
Row 63 This is the calculation for repaying debt 1.
The formula adds the repayment amount
of the previous line to the MAX(D60,0).
This has the effect of reducing the avail-
able cash flow because the repayment is
a negative number and adding a nega-
tive number is the same as subtracting it.
Rows 64“65 These continue the cash sweep calculations
for debt 2 and debt 3.
Row 66 This is the end result of our calculations.

Calculate the Cumulative Surplus Funds
66 Cash flow after cash sweep 0 =SUM(D60,D62:D65)
68 Cumulative surplus funds 0 =MAX(D66,0)+B68
69 Cumulative necessary to finance 0 =-MIN(D66,0)+B69
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

The Cash Flow Variation for Cash Sweep 267

Row 68 We would see a 0 here if the starting total
debt outstanding was more than the
available cash flow, since all the available
cash flow would have been used for
repayment. If the starting debt was
less, then there would be a remaining
positive number here which would be
equivalent to the increase in Surplus
funds for the year.
The cash flow after cash sweep represents
the change in available cash for the balance
sheet, since it is derived by netting all the
changes in the accounts in the balance
sheet. Consequently, if we want to use this
as a balance sheet number, we have to
make it a cumulative number by adding
the previous period™s cash flow number to
it. Hence the formula seen in D68 of
¼MAX(D66,0) þ B68. The MAX function
brings only the positive cash flow number
into this calculation.

Calculate the Cumulative Necessary to Finance
Row 69 If the available cash flow were negative
to begin with, we would see that same
negative number here, since there would
have been no cash sweeps to change that
number. We will also have to convert this
to a cumulative number by adding the
prior period™s negative flow.
As always when you are working with
negative numbers, you have to be extra
careful with the signs. This row uses
only the negative number from row 66
by using the MIN(D66,0) function, but
there is also a minus sign in front. This
turns it back into a positive number,
since this is the number that will appear

Chapter 15

as the NTF on the balance sheet.
Consequently, when we add the previous
cumulative total (which is also a positive
number), we use a þ sign.

Connect the Cumulative Plug Lines to Their
Respective Rows on the Balance Sheet
1 BALANCE SHEET Year 1 Year 2
2 Surplus funds plug There is no plug here 0 =D68
8 Necessary to finance plug There is no plug here 0 =D69
68 Cumulative surplus funds 0 =MAX(D66,0)+B68
69 Cumulative necessary to finance 0 =-MIN(D66,0)+B69

Rows 2 and 8 Write the reference to the lines in the cash
flow statement from the second year

Calculate the Debt Amounts After the Cash
Sweep and Reference Them to the Balance Sheet
11 Debt 1 300 =B71 28 =D71
12 Debt 2 300 =B72 300 =D72
13 Debt 3 300 =B73 300 =D73
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

Rows 71“73 Write the formulas as shown. The first year
is not a cash sweep year, so you can
simply write the reference to the debt
inputs. For the second year and sub-
sequent years, the debt numbers after the
cash sweep are the starting numbers
(rows 22, 25, and 28) less the respective

The Cash Flow Variation for Cash Sweep 269

cash sweep amounts. We use the þ sign
because the cash sweep amounts are
already shown as negative numbers.
Rows 11“13 Once you have rows 71“73, connect them
back to rows 11“13 in the balance sheet.

Add an Error-Trapping Formula
As a last touch, we need to add an error-trapping formula. One
good location is in the net income line, on row 44. There is no
need for an error-trapping formula in the first year since there
are no circular references there. For the second year, for cell D62,
change it from:
and just add the ISERROR function:
And we™re done!

This page intentionally left blank.


Recording Macros

In this chapter, we begin to look at creating macros by recording
our actions on the keyboard or via the mouse. Macros are


. 49
( 62 .)