ñòð. 45 |

TLFeBOOK

Chapter 14

244

Find the Static Total for the Assets Side

A B C D E

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

A B C D E

7

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)

23

24 Debt 2 input 300

25 Debt 2 before cash sweep 300 =B24 300 =IF(D24,D24,B45)

26

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

TLFeBOOK

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:

Â¼IF(D21,D21,B44)

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

TLFeBOOK

Chapter 14

246

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

A B C D E

15

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â€™

equity.â€˜â€˜

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Â

35Â Â Â Â

Row 34 Subtract total assets (without surplus

funds) from the total liabilities (without

NTF) and shareholdersâ€™ equity.

TLFeBOOK

The Cash Sweep 247

If a Negative Difference, This Is the

Necessary to Finance Plug

A B C D E

34 Difference (TL+SHE)-TA 0 =B33-B30 272 =D33-D30

35

36 Necessary to finance 0 =-MIN(B34,0) 0 =-MIN(D34,0)

37

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

A B C D E

34 Difference (TL+SHE)-TA 0 =B33-B30 272 =D33-D30

35

36 Necessary to finance 0 =-MIN(B34,0) 0 =-MIN(D34,0)

37

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)

43

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

TLFeBOOK

Chapter 14

248

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

A B C D E

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)

43

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 |