Spreadsheet Models for Managers

Demonstrations for Session 8
Financial Models

Active Representations of Depreciation Schedules (2000-4) or (2007+)

[Sheet: DepreciationExample]

In this example, we compute the depreciation stream for a series of capital expenditures that are driven by a hiring stream. This hiring stream is held in the range named HireStream. Each time we hire a person, we have to buy him or her a PC that costs $1.2 k, which is held in the cell named PCCost. The depreciation term is 4 years, or 16 quarters, and we'll assume that we use a straight-line depreciation schedule. This parameter is held in the cell named DepreciationTerm. All parameters and input streams are shaded yellow, indicating that the user can adjust them.

We'll compute both the cash expended per quarter and the depreciation cost per quarter required to support this hiring stream. And we'll do it in a way that honors the Ripple Principle — if either of the parameters PCCost or DepreciationTerm, or if any part of th einput stream HireStream is adjusted, the effects propagate correctly. It's possible to obtain the same results without following this principle so rigorously, and in most cases that's just fine. But we'll illustrate here how you go about it so that if you need to, you can do it yourself.

The basic strategy is to compute the capital expenditure stream, then convolve that with the depreciation schedule to obtain the depreciation per quarter. The range CashExpenditure is the capital expenditure stream, obtained by multiplying PCCost by HireStream.

Perhaps the most difficult part of this example is the depreciation schedule itself, DepreciationPQ. We could have filled in the values for the depreciation as a simple formula:

In each of the first 16 cells of the range DepreciationPQ. But later, when someone adjusts DepreciationTerm, the number of cells that have that value would be wrong. For example, if someone sets DepreciationTerm to 12, we want only the first 12 cells to hold the value. So we must face the problem of not only figuring out what the value of one quarter's depreciation is, but also which quarters should hold that value, and which should hold 0.

And there's another problem, too. We'd like whatever formula we use to be transportable anywhere on the worksheet. That is, it shouldn't assume that the depreciation stream always starts in, for example, Column C.

We accomplish all this using the IF and COLUMN worksheet functions. The formula is:

Here's how it works. Let's begin with the IF. It returns either 1 or 0, depending on the result of its first argument. If the first argument is true, the IF returns 0, otherwise 1. So let's now look at its first argument. To understand it, you must understand how column works. With no arguments, column returns just the column number of the cell that contains it in its formula. With a single argument, column returns the column number of the upper left cell of that argument, interpreted as a reference. So the first argument of the IF compares the column number of the cell that owns the formula with a formula that involves the column number of the first cell of DepreciationPQ. If the cell in question has a column number that is more than DepreciationTerm-1 greater than the column number of DepreciationPQ, then the IF returns 0, otherwise 1. This is just what we want. We take this 0 or 1 and multiply by 1/DepreciationTerm.

OK, so now we have a Ripple-proof depreciation schedule. The rest of this example is pretty simple. To get the depreciation stream, Convolve that depreciation schedule with the capital expenditure stream, as is done in the range Depreciation.

Total assets are just cash outlays minus depreciation, and cumulative cash is just a running sum of cash outlays.

This example could have been done more simply if we had violated the Ripple Principle. But then, if we found that the Depreciation Term changed, or if we tried to apply this worksheet in a different model with a different term, we might forget to adjust the depreciation schedule, or do it incorrectly. Following the Ripple Principle is the safest, lowest-cost course in most cases.

By the way, it could be even messier. Suppose that the depreciation term were not an integer number of time periods. That would be most unusual, but it's possible. In that case, the last cell of the non-zero part of the depreciation schedule would have a different value — some fraction of the full cell value. Another possibility is a non-uniform depreciation — different values for different cells. If you can figure out a formula for the schedule, that's best. But it might be very difficult to do. You might just have to fill in the numbers.

It's also possible to use depreciation schedules other than straight-line depreciation, as we did above. The last row, labeled SOYDDepreciationPQ, implements the Sum-of-the-Years digits method of depreciation. In SOYD depreciation, if the useful life is N periods, the amount depreciated in period k, if salvage value is zero, is given by:

Such a depreciation schedule writes off more of the asset in the early periods, as compared to straight-line depreciation. The formula that implements this in Excel is:

We could then carry this depreciation schedule forward just as before. Of course, it's much easier to use the built-in worksheet function syd.