Spreadsheet Models for Managers
Getting Access to Spreadsheet Models for Managers
If 
you use Excel to model businesses, business processes, or
business transactions, this course will change your life. You’ll learn how to create tools for yourself that will amaze
even you. Unrestricted use of this material is available in two ways.
- As a stand-alone Web site
- It resides on your computer, and you can use it anywhere. No need for Internet access.
- At this Web site
- If you have access to the Internet whenever you want to view this material, you can purchase on-line access. Unlimited usage. I’m constantly making improvements and you’ll get them as soon as they’re available.
To Order On Line
| Order "Spreadsheet Models for Managers, on-line edition, one month" by credit card, for USD 69.95 each, using our secure server, and receive download instructions by return email. |
| Order "Spreadsheet Models for Managers, on-line edition, three months" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email. |
| Order "Spreadsheet Models for Managers, downloadable hyperbook edition" by credit card, for USD 199.00 each, using our secure server, and receive download instructions by return email. |
To Order by Mail
Make your check payable to Chaco Canyon Consulting, for the amount indicated:
|
And send it to: Chaco Canyon Consulting 700 Huron Avenue, Suite 19C Cambridge, MA 02138 |
To use the course software you’ll need some other applications, which you very probably already have. By placing your order, you’re confirming that you have the software you need, as described on this site.
Spreadsheet Models for Managers
Demonstrations for Session 10
Capital Leases II
In this session we come to the exciting climax of our effort to make sequences of lease events taking place under identical lease terms more manageable. We have two demonstrations for this session. We begin by showing how to handle a single event, and then we consider the more complex case of a stream of events.
Download the demonstration file: (2007+).
Modeling a Capital Lease (2007+)
[Sheet: Winder]
This problem is a warm-up for problems involving the Lease Characteristic Array. In this problem we compute the effects on the three financial statements of a single lease event. In effect, we’re computing the rows of the Lease Characteristic Array.
General Kinematics purchases a coil winder under a capital lease contract. The winder is worth $100,000. The term of the lease is six years, and the interest rate is 9% per year. The useful life of the winder is also six years, at which time GK is obliged to buy it for $1,500. GK believes that this will be the scrap value of the winder at that time. Lease payments are quarterly.
(a)
Find the lease payment.
We first create cells to hold the named parameters Rate, Periods, InitialValue and ScrapValue. The formula for payment size, expressed in terms of these parameters, is inserted into a named cell PaymentSize:
As usual, even though the last two arguments default to 0, we provide them explicitly to remove all ambiguity.
(b)
Find the quarterly effect on the Income Statement.
This is determined by two quantities: the depreciation, and the interest expense. We can compute interest expense using the ipmt worksheet function:
Here we’ve used the name PeriodNumber to refer to the row of integers that indicate the index of that column’s period.
To find the depreciation, we assume straight-line (constant rate) depreciation of the initial value less the scrap value of the winder. We create a cell called Depreciation, and insert in it the formula
To complete the calculation of the effect of depreciation expense on the Income Statement, we have to create a row of cells, each one of which contains the formula
(c)
The quarterly effect on cash flow is just a row of cells each one of which contains the formula
(d)
The quarterly effect on the balance sheet is a net asset value equal to the initial value of the winder less cumulative depreciation, and a liability equal to the remaining principal to be paid on the winder.
To compute cumulative depreciation, make a running sum of the depreciation stream. Thus, the assets line of the effect on the balance sheet is just the InitialValue minus this cumulative depreciation, which appears in the row captioned “Assets: machinery.”
To compute the liabilities, we must compute the principal remaining to be paid. That is just the InitialValue of the winder minus the cumulative principal paid.
The principal part of the lease payment is just
So in the line “CumPrincipal”, we compute a running sum of the principal payments. We then use that in the line “Liabilities: Cap Lease.”
Modeling a Sequence of Capital Lease Events (2007+)
[Sheet: PC]
General Kinematics is expanding. You’re leasing personal computers for new hires as the company expands. Each PC costs $1200. You’re given the hiring stream, as shown in the row “HireStream”. Find the effect of these leases on cash flow, capital equipment assets, and depreciation expense. This problem is different from previous examples, because this time, we lease the equipment instead of buying it. The lease terms are:
- 5-year lease term
- Depreciation 5 years, straight line
- Interest rate 9% per year
We’ll take the approach of using the Lease Characteristic Array. Once we have found the LCA, we’ll just convolve it with the HireStream.
The rows of the LCA are in no special order, but let’s compute them in the order shown on the worksheet. We know that we’ll have some named parameters: Rate, PCCost, DepreciationTerm, and Periods.
The row DepreciationPQ uses our familiar approach that exploits the Ripple Principle for depreciation computations.
Let’s compute the effects of a $1000 purchase. We’ll then rescale by the amounts of the actual purchase when we convolve. So the block labeled “For a $1000 PC” computes these intermediate results.
There are several good reasons for taking this approach. First, using a round number like $1000 (or in other situations, $1,000,000 or $1,000,000,000, etc.) enables us to examine the result and easily detect errors. If we were to use the exact price of the item in question, then detecting an error in, say, the interest row, would be much more difficult. Second, using a round number makes the LCA easily re-scalable. That is, we can use it for any lease item whatsoever, simply by applying a rescaling factor of <price-of-item>/1000. If we compute the LCA for some other difficult-to-remember amount, we might make a mistake. If we compute the LCA for an amount that might change when we later update some prices, then we would have to change the rescaling factor, too, or build it into every usage of the LCA. Using a constant 1000 is easy and safe, once you understand what it’s doing.
The row PrincipalPayment is just the principal part of the lease payments, as in the Winder example. The row CumPrincipal is a running sum of PrincipalPayment. The row CumDepreciation is a running sum of DepreciationPQ. We need all these quantities by analogy with the Winder example. Now we’re ready to compute the LCA.
The first row, PCPayment, is just the lease payment itself, computed with PMT. This line goes directly to the Cash Flow statement. The second row, Assets, is the initial value of the asset (1000) less the cumulative depreciation. The third row, Liabilities, is the initial value of the asset (1000) less the principal that’s already paid off, CumPrincipal. The fourth row, PCDepreciation, is the depreciation in the current quarter. And the fifth row, InterestPayment, is the interest portion of the lease payment, which we compute with IPMT.
Finally, we convolve with the hire stream, and multiply by PCCost/1000 to scale the result properly. NewEquipmentPQ is actually PCCost times the HireStream, so the result is the same as the formula we actually used:
Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT
The Lease Characteristic Array is an example of a construct that makes more effective the modeling of a wide class of problems involving leasing. Certainly, it can be generalized, and certainly there are other possible constructs that could also be helpful with these kinds of problems. But let’s not stop there.
Certainly there are other problems that are susceptible to similar approaches. That is, we can create spreadsheet “idioms” that have applicability to problems more general than the problem immediately at hand. In a field as rich and complex as spreadsheet modeling, it is extremely unlikely that you will find all such techniques described in books, course, or Web sites. From time to time, you’ll have to invent them yourself.
You’ll do best if you can figure out how to produce useful idioms that can help solve wide classes of problems.
Many models are linear with respect to some of their input parameters and input streams. This means, among other things, that when we multiply the input by a constant K, the output also increases by a factor of K. If you know that your model is supposed to be linear with respect to some input parameter or input stream, you can use this fact to check your model: doubling the input should double the output.
You can build these checks into your model from the beginning by defining parameter multiplication factors. For instance, suppose you know that the model’s personal computer expenses should be proportional to the number of employees. By introducing a scaling parameter (normally 1.0), which multiplies the initial number of employees, and multiplies the hiring streams, before they’re used in the rest of the model, you can easily investigate the effects of changing the number of employees, to verify proportionality.