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.

Apparently, you’re using a browser that has JavaScript disabled. This Web site uses JavaScript, especially for
some of its navigation features. If you want to make those features available, you must enable JavaScript. Please open your browser's
preferences dialog and enable JavaScript at your earliest convenience. Instructions: for Internet Explorer 9.x; for Internet Explorer Version 8.x;Internet Explorer Version 7.x;
for Firefox.

Spreadsheet Models for Managers

How much must you save each month in a savings account that pays 4% per year compounded monthly to reach
a goal of $100 in 10 months?

What’s the future value of that payment stream?

What’s the present value of that future value?

How much would you have to pay each month to repay a loan of that amount if the interest rate is 4% per year,
compounded monthly?

These examples are designed to show how payments, present value and future value are all interlocked.
If you’re building models that use these concepts, you can use these relationships to verify that your
models are correct.

The first question is a fairly simple application of the worksheet function PMT.

The second question is a verification, using FV, that the payment stream we just computed actually
does produce $100, which is the future value we were trying to produce.

The third question tells us the present value of that future value.

The fourth question verifies that the payments required to pay off a loan equal to that present value are
identical to the payments that produce the $100, which is the future value of that present value.

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.