ñòð. 47 |

Interest Calculations: Which Row

Do We Refer To?

Row 59 For the first year, the formula

Â¼AVERAGE(B22,B11)*10% looks at cell

B11 as the ending amount. If we look at

cell B11, it is a reference to B44, which is

the row where the post-sweep debt 1 is

calculated. As a matter of good pro-

gramming, we should write the formula

Â¼AVERAGE(B22,B44)*10% since B44 is

where the number is first calculated. In

this way, we do not ask Excel to calculate

that number twice in B44 and B11 before

we calculate it. But this delay is not at all

important for a small model like the one

we are working on, and I have written

it and the other two similar debt lines this

way because it helps in making the

references clear.

For the second year, to keep things simple,

we can also just write

Â¼AVERAGE(B11,D11)*10%.

Rows 60â€“61 The same applies for the other two

interest expense rows for debt 2 and 3.

Link the Income Statement to the

Retained Earnings

We now have to link the income statement to the balance sheet.

We have already made some links in the interest calculations, but

TLFeBOOK

The Cash Sweep 255

this is bringing the balance sheet data into the income statement.

We have to do the same for bringing the income statement data

into the balance sheet.

A B C D E

15

16 Common stock 500 800

17 Retained earnings 40 102 =B17+D65

18 Shareholdersâ€™ equity (SHE) 540 =SUM(B16:B17) 902 =SUM(D16:D17)

19 Total liabs & SH equity 1,600 =B14+B18 1,720 =D14+D18

20

Row 17 The important change to make is the

formula in the second year (cell D17).

Rather than the input cell that we were

previously working with, this is now a

formula that reads the prior yearâ€™s

retained earnings (B17) and adds the

net income line for the current year

(D65) to it.

The first year remains as an input cell.

In this case, we can assume that the

number 40 is the sum of the net income

for the first year, which is 30, plus the

retained earnings of 10 from previous

years. Remember, the first year in a

model is usually a historical year, so it is

within reason to specify the retained

earnings as a hard-coded number for

the year.

Add an Error-Trapping Formula

Row 34 Now to add the final touch. This is a minor

change, but it is an important one, and it

is something that you should always have

when you are working with models with

iterative calculations. This is an ISERROR

error-trapping formula. I have put it in

TLFeBOOK

Chapter 14

256

row 38, which now looks like the

following:

For cell B34: Â¼ IF(ISERROR(B33 Ã€ B30),0, B33 Ã€ B30)

For cell D34: Â¼ IF(ISERROR(D33 Ã€ D30),0, D33 Ã€ D30)

Each column now has a circular refer-

ence, and the danger there is that any

error messages such as a #DIV/0! or

#VALUE! can get caught in the loop and

remain there even after the source of the

error has been removed or corrected.

When the formula encounters an error,

the ISERROR returns a 0, and this is then

read by the other formulas in the loop.

This has the effect of sweeping out the

error messages such that when the

calculation makes the full cycle and

comes to the ISERROR formula again, it

is no longer carrying the error message.

The ISERROR tests for the error, does not

find it, and so restores the formula

references again. The circular loop is

restored into working order.

TLFeBOOK

CHAPTER 15

The Cash Flow Variation

for Cash Sweep

When we covered the topic of balancing the balancing sheet in

Chapter 7, we said there were two ways that it could be done: by

looking at the balance sheet only (easier approach), or by looking

at the cash flow statement (more difficult approach). A cash

sweep is really a variation of a balancing exercise, but instead

of creating a Surplus funds plug, we reduce debt amounts by

the same amount. In Chapter 14, we looked at the cash sweep by

expanding the balancing approach. In this chapter, in order

to cover the subject more fully, we will look at expanding

the second cash flow approach.

THE BASIC IDEA REMAINS THE SAME

For balancing the same set of company numbers, using the bal-

ance sheet method or the cash flow method will give us the same

results. By the same token, the cash sweep in either approach

will also give us the same results.

The basic idea remains the same: To find the available cash

flow that can be used to repay debt. The available cash flow is

similar to the Surplus funds plug on the balance sheet, but you

257

Copyright Â© 2004 by John S. Tjia. Click here for terms of use.

TLFeBOOK

Chapter 15

258

need to keep in mind one difference. Because the available cash

flow is derived from income statement flow and the changes in

the balance sheet accounts for each year, it represents the changes

for that one particular year. The Surplus funds number, on the

other hand, is the cumulative total of all the changes in the

balance sheet through the years.

Letâ€™s lay out another spreadsheet to show the cash sweep in

this second way. The numbers we will be using will be identical

to those used in the last chapter, and you will see that we will

end up with the same numbers. But the layout will be quite

different.

ITâ€™S MORE COMPLICATED

Whereas we could experiment with creating a cash sweep using

just the balance sheet as a start, we cannot do so in this case. In

fact, we have to go the whole nine yards and include the balance

sheet and the income statement and the cash flow statement. This

is the same approach that would be required for merely balanc-

ing the flows. Having to work with all three statements at once to

make the model run properly is the reason the cash flow

approach is much more cumbersome. Nevertheless, letâ€™s plunge

ahead and explore the complexity involved. It may be that you

will be asked to review a model that uses this approach, and

being familiar with this method will give you a head start in

orienting yourself in that model.

STRUCTURING THE CASH SWEEP

The steps laid out are broadly similar to the steps laid out in

the previous chapter. Again, the name of the game is to work

with the static numbers as opposed to the dynamic numbers.

The static numbers in this case would be the debt numbers

before any cash sweep effects. As we start, make sure that you

have turned on the Iteration for calculations through the Tools >

Options > Calculation tab. The steps are:

1. Lay out the assets side.

2. Lay out the liabilities and equity.

TLFeBOOK

The Cash Flow Variation for Cash Sweep 259

3. Lay out the income statement and calculate the net

income.

4. Find the available cash flow by subtracting all the

changes in the balance sheet from the net income.

Remember that a source of cash is from a decrease

in an asset or an increase in a liability. A use of cash

is from an increase in an asset or a decrease in a

liability.

5. If the available cash flow is negative, then there is no cash

sweep possible. The negative flow must be added to the

prior yearâ€™s NTF before it is connected to the Necessary to

finance plug line in the balance sheet.

6. If the available cash flow is positive, then it is applied for

repayment of the debt 1 amount. If there is any excess

remaining, apply it for repayment of debt 2. Any excess

after that is applied for debt 3. You must also apply

the positive available cash flow for repayment of any

Necessary to finance, and this may in fact be the first level

of the cash sweep. More on this later.

7. Any remaining cash flow is added to the prior yearâ€™s Surplus

funds amount and then connected to the Surplus funds

line.

LETâ€™S BEGIN

Lay out the sheet in this way. The flow is:

Assets

u

Liabilities and equity

u

Input for debt

u

Income statement

u

Cash flow

u

This is different from the layout we used in the last chapter,

but as you work with it, I hope you will see the method in

the madness. Because we will need more lines, I have split the

screen into two pages.

TLFeBOOK

Chapter 15

260

ñòð. 47 |