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 9Session Links
Capital Leases I
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.
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.
Before attempting this homework, you might find it helpful to read about:
Numbers in square brackets to the right of the problem numbers indicate point values.
This problem explores the concept of Present Value in a variety of circumstances.
For the next five years, the prevailing interest rate is expected to be 1.4% per year, compounded quarterly. For the five years following that, the rate is expected to be 2.3% per year, compounded quarterly. Under these assumptions, what is the present value of a stream of ten payments of $50,000 when they are received at the end of each year? Your result should be a single cell.
The interest rate assumptions of part (a) apply in this part (b) as well. Compute the present value of a stream of 25 payments of $12,500 when they arrive at the end of each year for the first five years, and at the end of each quarter for the second five years. Your result should be a single cell.
Again under the interest rate assumptions of part (a), compute the total present value of a stream of 25 payments paid at the end of each year for the first five years, and at the end of each quarter for the second five years. During the first five years, when payments are arriving annually, the payments are $10,000 each. During the second five years, when payments are arriving quarterly, for the first three quarters of each year, the payments are $5,000 each. The last payment of each year during the second five years is $10,000. Your result should be a single cell.
When you retire at age 65, you expect to live for
another 20 years. During that time, you estimate that your living expenses will be
$85,000 per year, and that the prevailing interest rate will be 12.0% per year,
compounded quarterly. If you make identical withdrawals from your retirement account at the end
of every quarter, what is the minimum amount of money that you must have in the account to meet
your expenses over your expected lifetime? Your result should be a single cell.
You have an opportunity to purchase a small gold mine. Net income at the mine is estimated to be $100,000 per year. It’s also estimated that mining can continue for 10 more years, and then the gold will be exhausted. Assuming that net income is realized only at year-end, and that you wish to make a 20% annual return on your investment, what would you be willing to pay for the mine? Your result should be a single cell.
Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT
Nesting invocations of worksheet functions can be a bit tricky, because nested function calls are difficult to think about. Sometimes, in developing a spreadsheet model, we can gain clarity by avoiding nesting. That is, while we’re still thinking about how to approach a modeling problem, we intentionally choose to avoid nesting function calls. After we understand the problem better — and only then — we might go back and replace what we’ve done with a more compact version that exploits nesting. In addition to producing forms that are easier to think about, this practice of developing a simpler form first has another benefit. It enables us to examine intermediate values more easily, which enables us to confirm that the calculations we’re performing make sense.
Some feel that building something that you intend to replace is a waste of effort — that it’s far easier to build things in final form from the start. When that approach works, it is faster and more efficient. But when we think we’re likely to make mistakes, the “slower” way is faster.
In years past, we’ve learned that what makes a model dynamic — as opposed to static — can be difficult to grasp. If you have some doubts yourself, and you haven’t yet looked at the reading on Models vs. Tools, we believe that you will find it helpful.