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
• Circularity in Excel Worksheets
• Finding Circularities
• Resolving Circularities by Solving Equations
• Worksheet Functions for Conservative Modeling
• Using Implicit Intersection
• Cautions for Implicit Intersection

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

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 (E10 → E8 → E11).

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.

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