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
Problem Set 10Session Links
Capital Leases II
Be sure to check the list of worksheet functions that are needed for the homework assignments, to see which new functions (if any) might help with this assignment.
For a quick way to copy homework problem data into your homework solution, see “Avoid retyping homework problem data.”
Since macros aren’t permitted in this homework assignment, be certain that the workbook you submit for grading has no macros. Read about how to check your workbooks for macros.
Remember that some problems are slight extensions of what we show you in class, in the demonstrations and in the session notes, and some problems are somewhat ambiguous. This is intended to parallel what you’ll frequently encounter at work. If you feel a bit confused, there are some things you can do to help clarify things.
Numbers in square brackets to the right of the problem numbers indicate point values.
Now we revisit Problem 8.1, the one about
Eggplant Technologies, Inc., but this time, instead of buying the equipment, you’ll acquire it
under a capital lease agreement, with a three-year term and an annual interest rate of
4.5%. Payments are made at the end of each quarter. The equipment is depreciated
uniformly over a period of three years. Computers for Sales Representatives and Engineers have
different prices, as shown in the table below (the same as Problem 8.1). The prices
given are in thousands of dollars. In this problem
you can use the Convolve macro, if you like.
 |
Cost (k$) |
| Sales Representatives | 1.5 |
| Engineer | 4.8 |
The hiring stream you’re supporting is shown in the table below, which is also the same as Problem 8.1:
|
Y1Q1 | Y1Q2 | Y1Q3 | Y1Q4 | Y2Q1 | Y2Q2 | Y2Q3 | Y2Q4 | Y3Q1 | Y3Q2 | Y3Q3 | Y3Q4 |
| Sales Rep | 2 | 3 | 4 | 3 | 2 | 2 | 3 | 5 | 2 | 4 | 2 | 2 |
| Engineer | 0 | 1 | 1 | 1 | 2 | 0 | 1 | 1 | 2 | 0 | 2 | 1 |
Create ranges on your worksheet that hold the data EqptCostAnalysis and HireStreamAnalysis, corresponding respectively to the tables above. Show EqptCostAnalysis in thousands. Then, in a 1x12 range, compute the depreciation stream, which gives the fractional depreciation for each quarter. Your result should be a single 1x12 range.
Compute the total cost of equipment, in thousands of dollars, required in each quarter to support the hiring streams. Your result should be a single 1x12 range.
Compute the Lease Characteristic Array for this problem. Use the same order for the rows as we used in demonstrations for Session 10, and create the LCA for a $1000 item. Your result should be a single 5x12 range.
For each quarter, compute, in thousands of dollars:
- The cash required
- The cumulative net increase in assets in each quarter resulting from these purchases, after accounting for depreciation
- The cumulative net increase in liabilities
- The depreciation expense
- The interest expense
Present the rows of your answer in the order given above. Your result should be a single 5x12 range.
In this problem, you’ll extend the LCA concept to include the effects of insurance and maintenance costs. Since both of these elements can be expected to vary over the life of the lease, the LCA extended in this way can account for some fairly complex modeling issues.
Assume that the charges for insurance in any given quarter are 6.5% of the then-remaining undepreciated value of the equipment, and that a maintenance contract for a specific piece of equipment costs 17% of the original equipment cost per quarter. Original equipment cost is the cost of the equipment itself if it were not leased. All other parameters describing the lease itself are the same as Problem 10.1.
Define a parameter called InsuranceRatePQ, which holds the percentage rate for the insurance costs, on a quarterly basis. Define a parameter called MaintenanceRatePQ, which holds the percentage rate for maintenance costs, on a quarterly basis. Construct an extended LCA that consists of not only the LCA for Problem 10.1 (consistent with the Ripple Principle) but also the quarterly effects of insurance and maintenance contracts. Thus, your new LCA should have two additional lines, one for insurance, and one for maintenance, in that order. Compute only the LCA. Do not convolve it with anything. As usual, your result should be an LCA for a $1000 item. Your result should be a single 7x12 range.
Some costs depend not on the monetary value of equipment leased, but on the number of items leased. For example, in computing the cost of labor for software maintenance for computers, although there is some dependence on the cost of the computer, there is a much stronger dependence of costs on the number of computers. The LCA approach does not work well for these kinds of costs. Why not? Enter your explanation directly into the spreadsheet in which you solved (a). Your result should be a single cell.
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.