ñòð. 46 |

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

A B C D E

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)

43

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.

TLFeBOOK

Chapter 14

250

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

A B C D E

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

numbers.

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.

CONNECTING THE INCOME STATEMENT

The illustration so far has excluded the income statement. You

can easily â€˜â€˜attachâ€™â€™ an income statement to this model. These are

TLFeBOOK

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

statement.

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

references.

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

sheet.

TLFeBOOK

Chapter 14

252

A B C D E

50

51 INCOME STATEMENT Year 1 Year 2

52

53 Revenues 250 300

54 Expense 110 120

55 Earnings before interest and tax 140 =B53-B54 100 =D53-D54

56

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)

63

64 Taxes (40%) 20 =B62*40% 41 =D62*40%

65 Net income 30 =B62-B64 62 =D62-D64

66

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

percent.

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

TLFeBOOK

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

TLFeBOOK

Chapter 14

254

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 |