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
Session 3
Cushioning, Crowding, and Quantization
Summary of Pages
Since modeling is inherently inexact, we sometimes build into our models a safety margin. We call this cushioning. The contrary practice of planning to overtax resources is called crowding. In business modeling, there are occasions when both cushioning and crowding are valuable techniques. Quantization is the effect we observe in models when the values of some of the model parameters are restricted to lie in bands, or when they must adhere to specific values. For example, the practice of ”staircasing“ price schedules produces quantization effects in expense or revenue models.
Excel provides some facilities that are useful in all these situations, and we’ll explain them and illustrate their use. Cushioning, crowding and quantization can lead to complex and confusing models, but if you’re systematic about how you handle these effects, your models will be simpler and easier to maintain.
One effect that appears in complex models is usually an error — the circular reference. We’ll talk about circular references, and how to unwind them.
Below is a summary of pages for Session 3.
- Lasttime
- Why conservative modeling?
- Cushioning
- Crowding
- Quantization
- Computational issues
- Excel functions for cushions, crowding, and quantization
- Modeling techniques for cushions
- Circular reference errors
- Unwinding circular references
- Unwinding circular references, continued
- Implicit intersection
- Cautions about implicit intersection
- The main points
- Preview of Next Time
Links to other materials for Session 3.
Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT
Implicit Intersection is one of the most underrated — and at the same time one of the most powerful — techniques in all of Excel. Yet few people truly understand it.
Implicit Intersection is the method by which one cell can retrieve a value from another range by examining the intersection of its row (or column) with that range. If the intersection is unique — a single-cell — then the formula of the cell that depends on implicit intersection can update its value without incident. If not, an error results.
When talking about worksheet functions, it’s important to be careful about your choice of terminology. Technology is like that, and like it or not, Excel is a piece of technology.
Cells can have formulas, as we’ve seen, and those formulas can invoke worksheet functions. Cells do not contain functions — rather, they can contain formulas, which, in turn, can invoke one or more worksheet functions.
User-defined names are not functions.
To invoke a worksheet function in the context of a cell formula, one calls a worksheet function. Often, you hear this described as “applying a worksheet function.” Do not use that terminology. For example, we’ll speak of “calling a function on its arguments,” or “calling a function with its arguments.” We do not say that we “apply a function to its arguments.”
When Excel calculates the value of a cell, and that cell’s formula contains a call to a worksheet function, that function call is evaluated. Its value is then returned to the formula, which uses it, in turn, to compute its own value.
Sometimes you hear worksheet functions referred to as commands. They aren’t commands. Commands are found on Excel’s menus, or perhaps in some dialog boxes. Commands do things, like format a cell, or sort a range. Commands don’t return values — functions return values.