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
For the second year, to keep things simple,
we can also just write
Rows 60â€“61 The same applies for the other two
interest expense rows for debt 2 and 3.
Link the Income Statement to the
We now have to link the income statement to the balance sheet.
We have already made some links in the interest calculations, but
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
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
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
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
row 38, which now looks like the
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.
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
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
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.
The Cash Flow Variation for Cash Sweep 259
3. Lay out the income statement and calculate the net
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
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
Lay out the sheet in this way. The flow is:
Liabilities and equity
Input for debt
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.