ñòð. 49 |

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.

TLFeBOOK

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

A B C D E

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

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,

TLFeBOOK

Chapter 15

266

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

repaid.

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

numbers.

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

A B C D E

66 Cash flow after cash sweep 0 =SUM(D60,D62:D65)

67

68 Cumulative surplus funds 0 =MAX(D66,0)+B68

0

69 Cumulative necessary to finance 0 =-MIN(D66,0)+B69

0

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

TLFeBOOK

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

TLFeBOOK

Chapter 15

268

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

A B C D E

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

onward.

Calculate the Debt Amounts After the Cash

Sweep and Reference Them to the Balance Sheet

A B C D E

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

TLFeBOOK

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:

Â¼D41-D43

and just add the ISERROR function:

Â¼IF(ISERROR(D41-D43),0,D41-43)

And weâ€™re done!

TLFeBOOK

This page intentionally left blank.

TLFeBOOK

CHAPTER 16

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 |