Spreadsheet Models for Managers

Getting Access to Spreadsheet Models for Managers

If Spreadsheet Models for Managersyou 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:
  • For the download: USD 199.00
  • For access online for three months: USD 199.00
  • For access online for one month: USD 69.95
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 3
Cushioning, Crowding, and Quantization

This session’s topics relate to methods for imposing constraints on models that transcend what can be expressed using ordinary arithmetic operations. These constraints express conditions such as the discrete sizes of certain resources, or the requirement that we meet certain minimum or maximum values in modeled quantities. We have six demonstrations (2007+) for this session:

Techniques for Conservative Modeling (2007+)
[Sheet: Example1]

Here’s a collection of different kinds of cushions, in the block labelled “Simple Cushions”. They illustrate how you go about implementing these kinds of cushions.

In the block labelled “Simple Crowdings” we illustrate some simple crowding schemes. The first (“Additive Constant Crowding”) is additive, and below that (“Constant Percentage Crowding”) is a multiplicative crowding.

“Constant Percentage with Batching” is a crowding scheme that uses a quantization approach. First we subtract from the needed number of vehicles a constant percentage, then we divide by the batch size and use INT to find the number of batches in the result. Multiplying by the batch size gives the total fleet size.

“Batch” is a different approach. Here we reduce the number of vehicles by a constant number of batches, which is given by VehicleBatchCrowding.

You can imagine a huge variety of crowding and cushioning schemes, with and without batching. Perhaps the most useful are those that involve non-uniform batching — batching in which the batch size varies with the size of the controlled resource. This sort of thing often happens when you’re working with leased space.

Circularity in Excel Worksheets (2007+)
[Sheet: Example2]

This example has two cells that implement the simultaneous equations

y = 3 - x x = 1 + y

These two equations have a simple solution: x = 2, y = 1. But Excel can’t find it directly, because the two cells that implement these equations are locked in a circular dependency. Excel can use an iterative approach, but iteration is an expensive, slow, and unreliable approach.

Circular dependencies are bad news, and they can arise even in situations that we can solve in other ways. The best approach to avoiding them is to first solve any equations you can, and then implement the solved system in your spreadsheet model.

Finding Circularities (2007+)
[Sheet: Example3]

It’s really very easy to create models that have circular references even if you don’t intend it. This example is drawn from an actual client model that was intended to project cash needs. To see the circularity, look at the diagram, or use Excel’s tools for investigating circular references.

Excel 2007, 2010, and 2013
Use the auditing tools in the Formulas tab of the ribbon: Formulas>Formula Auditing.
Excel 2011
Use the auditing tools in the Formulas tab of the ribbon: Formulas>Audit Formulas.

In this example, there are several circularities. One is (E10E8E11).

Resolving Circularities by Solving Equations (2007+)
[Sheet: Example4]

This is the same model as Example 3, except that the circularity has been untangled, because we’ve solved the system of simultaneous equations.

Worksheet Functions for Conservative Modeling (2007+)
[Sheet: ExcelFunctions]

This worksheet is a study of the behavior of a number of worksheet functions that are useful for cushioning, crowding and quantization.

CEILING returns the nearest multiple of its second argument that’s on the other side of zero from its first argument. The “other side of zero” means that the result is greater than the first argument if the first argument is positive, and less than the first argument if the first argument is negative. The result is always an integer multiple of the second argument.

In versions of Excel before 2010, the two arguments of CEILING must have the same signs. If they don’t, the result is the #NUM! error value. CEILING(x,0) is always 0. However, this constraint is partially relaxed in Excel 2010. Before using CEILING with arguments of opposite sign, check online help. In Excel 2013, a new optional, third argument controls how rounding occurs when the first argument is negative.

These differences in the behavior of Excel’s various versions actually provides some important lessons about the consistency and compatibility of the different versions of Excel.

Don’t assume that all versions of Excel are identical
As we’ve seen, there are differences, not just between Mac and PC, but between different releases of Excel for the same operating system.
Groups should agree on a standard version of Excel
If you’re working in a company or team where you frequently exchange workbooks, it’s best to standardize on a specific release of Excel. If you don’t, you can encounter unexpected inconsistencies that are difficult to sort out.
Avoid “clever” hacks
Sometimes a particular version of Excel behaves in such a way that you can exploit its behavior to save labor when writing formulas. The more subtle and clever you are when you do this, the less likely it is that anyone else will have tried it, which means that any traps that exist might not be widely known. And that means that you’re more likely to trip over an inconsistency like these.

floor is just like CEILING except that it returns the result that’s closest to the first argument, on its 0 side. That is, if the first argument is positive, it returns the largest multiple of the second argument that’s smaller than the first argument, and if the first argument is negative, it returns a result that’s the most negative multiple of the second argument that’s greater than the first argument. As with CEILING, be aware of slight differences among the various versions of Excel.

INT returns the integer part of its argument. It’s exactly equivalent to CEILING(X, SIGN(X)). Here, sign returns +1 if x is positive, and -1 if x is negative. sign is also an Excel worksheet function, but we won’t be using it.

round is familiar; what might be new is the description of it as:

the multiple of 10 -(second argument)  that’s closest to the first argument

This just means that if the second argument is 1, you’re rounding to the nearest 0.1; if it’s -2, you’re rounding to the nearest 100. Fractional second arguments are permissible. trunc works the same way, except that it truncates toward zero.

mround is less familiar. It returns “the multiple of second argument that’s closest to the first argument.” This just means that if the second argument is 1, you’re rounding to the nearest integer; if it’s 2, you’re rounding to the nearest even integer. Fractional second arguments are permissible, but the first and second arguments must have the same sign.

Using Implicit Intersection (2007+)
[Sheet: ImplicitIntersection]

Implicit Intersection is a name that describes how Excel determines which cell to use when there is some ambiguity about which cell to choose. In a single-cell reference such as C8 or $D7, there can be no ambiguity, because only one cell is specified by the reference. But when a reference refers to a range, and when the “caller” of that reference is a single cell, ambiguity arises: which cell of the range is required? Excel uses Implicit Intersection to resolve the ambiguity.

You might think of this as a problem — resolving the ambiguity. But actually, it can be a useful tool, once you understand how Excel behaves. You can use it to reduce the effort you invest to build your models.

In this demonstration, we explain how Excel selects the parts of the ranges you refer to. The range D12:G17 (white cells with heavy outline) is the range we’ll use for an example. We’ll refer to it from various places around the worksheet, in array formulas or not, and see what we get. We’ll see that the behavior is richly varied, producing different results depending on where you are on the worksheet, and depending on whether or not you used an array formula.

Let’s begin with a description of the rules Excel follows.

Let’s start with non-array formulas. If the result of the reference is a single cell, Excel returns the value of that cell. If the result is more than one cell, Excel computes the intersection of that range of cells with the row and column of the calling cell. If that intersection is a single cell, Excel returns the value of that cell. If the result is more than one cell, Excel returns the #VALUE! error.

For array formulas, Excel returns as many cells as are needed to fill the calling range, after first synchronizing the upper left corner of caller and the cells referred to.

Let’s look at some examples.

In I12, we entered the formula D12:D17. The result is the value of D12, because Excel uses Implicit Intersection: it finds the intersection of the referenced range (D12:D17) with the rows that intersect the cell I12 (the “caller”) and finds D12. The formulas in I13:I17 were produced by filling down from I12, and the values in those cells are analogous to the value of cell I12.

In J13, we entered the formula D12:D17, exactly the same as cell I12. But instead of producing the value of D12, Excel produces the value of D13, because it uses Implicit Intersection, J14:J17 are analogous.

In K13:K17, we entered the formula =D12:D17 as an array formula. Now we can see that Excel does not use Implicit Intersection. It produces the values of the referenced range displaced down one cell. Excel doesn’t use Implicit Intersection in array formulas.

We now define the name LeftColumn to mean $D$12:$D$17. Entering an array reference to LeftColumn in column L, we see that Excel produces the values of LeftColumn faithfully. We can’t actually tell whether Implicit Intersection is being used, or not — the results are the same whether or not it’s being used in this case.

Turning now to rows 21 to 26, we look at similar forms from a new location. The formulas in D21:D25 return errors because they refer to multiple cell ranges whose intersections with the rows and columns of the calling cell contain multiple cells.

F21:F26 works, because it’s an array formula. Excel synchronizes the upper left corners of the calling range and the range referred to, and returns as many values as are needed. This is also why rows 29 and 31 “work.” Row 30 mostly works (non-array formulas), but H30 fails, because there are no cells in column H of the argument reference D12:G12.

Rows 33 through 36 are analogous to Rows 28 through 31, except that now we’re using names. The behavior is exactly the same.

Row 38 contains a range named Indices. Row 39 contains a formula that uses the Excel function INDEX, which you’ll use in the next session, to extract pieces of the named range LeftColumn. Notice that the second argument, the index, is what picks out the piece. Implicit Intersection determines which cell does the picking.

Cautions for Implicit Intersection (2007+)
[Sheet: Cautions]

This demonstration illustrates some quirky behavior of some worksheet functions in connection with implicit intersection. It also provides a method for controlling the quirkiness.

The quirky behavior is best explained with an example. Suppose a cell, say C5, contains a formula of the form =SUM(D1:D9), — that is, column of cells that include a cell in row 5. We might expect, on the basis of implicit intersection, that the SUM function would just return the value in D5. But it actually returns the sum of all values in D1:D9. Other functions also behave this way: for example, PRODUCT, MIN, and MAX. On the other hand, INT and SQRT do not. They behave in a manner consistent with implicit intersection.

Excel’s arithmetic functions, on the other hand, always comply with implicit intersection.

Fortunately, there’s a way to constrain the quirky functions to comply with implicit intersection. The trick is to exploit the fact that Excel’s arithmetic operators always adhere to implicit intersection. By adding zero or multiplying by one, we can force these worksheet functions to comply with implicit intersection.

The behavior, and the trick, are illustrated in the column headed “Result”.

Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT

Understanding Implicit Intersection

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.

Terminology for Worksheet Functions

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.