Spreadsheet Models for Managers

Demonstrations for Session 4
Temporal Response

The examples for this session focus on Convolution. We have three demonstrations (2007+) for this session, which illustrate (a) what convolution is graphically; (b) alternative methods for carrying out convolution; and (c) how to convolve multiple streams without the use of our macro.

• Graphical Representations of Convolution
• Alternative Methods for Computing Convolution
• When Not to Convolve
• Computing Multiple Convolutions Using Tables
• Advantages of Dynamic Names
• Running Sums of Multiple-Row Ranges

Graphical Representations of Convolution (2007+)
[Sheet: ConvolutionGraphically]

Here we perform convolution using a tabular approach, adding up all the rows of the table to produce the convolution product.

We’re computing the total productivity provided by a hiring stream, given the productivity curve of a single hire. The formulas in the cells of the upper right triangle of the tabular convolution calculation produce delayed and scaled copies of the productivity curve in the rows of that triangle. You can see what those rows look like in the graph directly below.

Each cell computes its value by first extracting the number of hires with a call to INDEX, using implicit intersection on the named range WeekCol, and then multiplying by the appropriate cell of the productivity curve.

The entire computation is also performed using the Convolve macro in the row captioned “Total Productivity”.

Alternative Methods for Computing Convolution (2007+)
[Sheet: Example]

This example is similar to the one above, but now we show the result using four methods. Method 1 uses explicit mixed references. We could have done this one using implicit intersection, as we did the example “ConvolutionGraphically,” but this illustrates another way. Method 2 also uses explicit references, but this one computes the correct cell of the productivity curve using the horizontal and vertical helper ranges. The advantage of this approach is that you can fill one cell across the entire upper triangle, without any additional typing. This is advantageous because typing — especially typing cell references — is a prolific source of errors.

Method 3 is identical to Method 2, except that it uses names for the reference arguments of index. There is one new wrinkle in one of the names, ProductivityWeek. Until now, almost all, if not all, of the names we’ve defined have referred to single cells or ranges. But names can do much, much more. This name, ProductivityWeek, is actually a formula. It computes a sum. We’ll see a defined name for an even more complex formula in the next example.

Method 4 uses the Convolve macro.

In this method, we illustrate the use of the Convolve macro on a range that has multiple rows. Here, we’re computing the load on the trainers, as well as the total productivity. The Convolve macro can easily handle this. Using Method 1 or Method 2 would require another triangular range calculation. You can see that the macro approach is much more compact.

When Not to Convolve (2007+)
[Sheet: Silly]

Occasionally, in student projects, we see attempts to convolve a stream with another range consisting of a single column or a single cell. There is never any reason to do this. It is entirely equivalent to array multiplication. In this example, we compare the result of convolving a stream (ProductivityCurve) and a single cell (Hiring), with the result of array multiplying those same two entities. As you can see, they are identical. Try it yourself with entities that have multiple rows.

Computing Multiple Convolutions Using Tables (2007+)
[Sheet: Multiple]

In this example, we illustrate the use of the Tabular Approach to convolution in situations when we wish to convolve multiple streams. This is especially valuable for those of you who won’t be able to use the Convolve macro, either because of a policy of your employer, or because you must distribute your work to someone who doesn’t have the macro available.

We know that we can convolve multiple streams in a straightforward manner just by using one table for each convolution pair. That works, but because it consumes so many cells, it is laborious and it can be difficult to follow. The method we’re about to demonstrate is much more compact and efficient.

It does have one limitation: it can compute only the total of the convolution of all pairs; that is, the result is the result of adding the separate convolutions. You’ll see why shortly.

Suppose we want to compute the productivity of a work group consisting of a mix of new hires of two categories: very experienced and less experienced — veterans and rookies. Each category has its own hiring schedule. And each category has its own productivity curve, too.

The total productivity of the group is thus the sum of two convolutions: first convolve the veterans’ hiring stream with the veterans’ productivity curve, and then convolve the rookies’ hiring stream with the rookies’ productivity curve. Add the two results, and you have the total productivity of the group. We did just that in the section labelled Method 4, using the macro, and the total appears in the row below. Nothing new here.

Doing the equivalent calculation using the tabular approach that we’ve shown you so far would require two tables. And you can do that yourself. But we can do it in one table, and that’s what we’ll show you next.

Doing this calculation in one table instead of two might not seem like much of a saving. Actually, it isn’t. It’s hardly worth the extra trouble of what we’re about to do if all you have are two stream pairs to convolve. But what if you have three, or six or twenty? In those cases, it makes much more sense.

The method is exactly the same as the tabular method we’ve used up till now, except that each cell of the table contains an array formula that computes the sum of the contributions from each of the stream pairs we’re convolving. Take a look at the upper left cell of the table for Method 1. Its formula extracts a column from the hiring stream array, then multiplies it by a column from the productivity curve array (cell-by-cell), and sums the resulting products to get a single number as a result. Naturally, this has to be done as an array formula.

The particular columns that are picked off, respectively, from the hiring stream array and the productivity stream array, are computed in the manner we used for the simpler single-stream tabular convolution. Nothing new there. What is new is that instead of being able to just multiply two numbers together, we must use an array formula to multiply two columns together, cell-by-sell, and then we use sum to form the total.

Method 2 does exactly the same thing as Method 1, except that it uses names instead of explicit cell references. Method 4 is provided just to show you that the three approaches all do the same thing.

Now you can see why this multiple-stream tabular approach has a limitation. It can produce only the sum of the convolutions of each of the multiple stream pairs. If you need to see each stream pair result, you need to use separate tables.

Now, what about Method 3? It’s the same as Method 2, except that it defines a name DoItAll that’s just the formula we used in Method 2. If you do all your tabular convolutions this way, then they all look alike. The only thing that changes from model to model would be the definitions of the names.

Advantages of Dynamic Names (2007+)
[Sheet: FurnitureDynamic]

In this example, we illustrate how to define names in a way that dramatically reduces maintenance costs. The technique, sometimes known as dynamic names, in this case employs the worksheet function offset, but depending on the situation, you might want to use other worksheet functions, too.

Refer to the demonstrations for Session 2, in which we calculated the cost of furniture needed to support a given set of hiring streams, presumably for a financial model to support a planning effort. One of the features of such planning efforts is that they change often. That is, you make a set of assumptions, then implement them in a model, and examine the results. Rarely are the results acceptable on the first go. So you change the assumptions and recompute. This process might repeat dozens of times. Often, the changes are in the form of a change to values of parameters, and in those cases, the changes are accommodated easily.

Sometimes, though, the changes require redefining user-defined names. For example, we might expand the number of furniture types, or the number of employee categories. In these cases, the consequences can be expensive in terms of labor and delay. And labor-intensive efforts are prone to error. For these reasons, it is sometimes beneficial to define names in ways that are resilient with respect to changes of this kind. And that’s what we did in this example.

Note, for example, the definition of EmployeeAnalysis. Instead of a range reference or a constant, its definition involves a worksheet function. We haven’t seen this before, but it’s perfectly legal. Excel computes the value of the formula, which returns a reference, and so the name is defined as a reference. The advantage here is that the reference that’s returned depends on the range EmployeeCategories. If we change the number of employee categories, the shape of EmployeeAnalysis tracks it. This is nothing more than the ripple principle, applied not to values, but to name definitions.

We took an analogous approach with the name FurniturePrices, which depends on the name FurnitureTypes.

The definition of the name FurnitureAnalysis is a bit more complex. It’s a rectangular range with a number of rows equal to the number of employee categories, and a number of columns equal to the number of furniture types. First we set up the row captions using an array formula referring to EmployeeCategories. Next we set up the column headings using the array formula

Next we define the upper left cell of the enclosed range to be FurnitureAnalysisStart. Finally define the name FurnitureAnalysis:

In this way, whatever the number of rows in EmployeeCategories or columns in FurnitureTypes, FurnitureAnalysis will be correct. FurnitureAllocation is analogous.

We did something similar for HiringAnalysis, whose definition depends on EmployeeCategories and Months, which is the name of the header above HiringAnalysis.

With these definitions, whenever the number of furniture types changes, we have to change the definition of FurnitureTypes, but all other definitions remain what they are. We also have to change the sizes of any array calculations that depend on the number of furniture types. But we needn’t redefine any names for ranges whose shapes depend on the number of furniture types. This saves much labor and time, and reduces the opportunity for errors. The same is true for changes to the number of employee categories, and the number of months.

The sheet FurnitureDynamic0 contains all these definitions as well. It’s identical to the sheet FurnitureDynamic, except that the last employee category and the last furniture type have been removed, and the names FurnitureTypes and EmployeeCategories have been suitably redefined. We provide it for you to try extending the number of employee categories by 1, and the number of furniture categories by 1. Make these changes, change the two names FurnitureTypes and EmployeeCategories, and fill in the data, to get an idea of the labor saved.

Running Sums of Multiple-Row Ranges (2007+)
[Sheet: RunningSum]

We’ve already seen how to compute running sums of single-row ranges. We can use the same method for multiple-row ranges, too. But for multiple-row ranges, using that technique, we must resort to using explicit cell references, because each cell of the multiple-row result has to reference two cells of the multiple-row range whose running sum we’re computing. As we know by now, formulas that use explicit cell references are prone to error, difficult to verify, and are a constant source of maintenance cost.

There is a way to eliminate the explicit cell references for running sums of multiple-row ranges, using the worksheet function INDEX and implicit intersection. That method is illustrated on the worksheet RunningSum.

The multiple-row range that we want to sum is named ProcurementSchedule. In the rows below it is a range labeled Cumulative Procurement, which contains the running sum of ProcurementSchedule. The computation is performed in two parts. The first column of Cumulative Procurement is an array formula that extracts the first column of ProcurementSchedule, using the worksheet function INDEX. Here is where we would add in the initial values, if any. There are none in this example.

The rest of Cumulative Procurement is more interesting. It uses the helper array to the right of Cumulative Procurement, which contains the row numbers of the rows of Cumulative Procurement. References to the helper array are accomplished through the name RowIndex, which is defined to refer to the cell of the helper array’s column that is in the same row as the cell whose formula contains RowIndex. Inspect its definition to see how it uses mixed references to accomplish this.

All of the cells of the rest of Cumulative Procurement have the same formula:

That is, they sum the value of the cell to the left of the current cell, and the value of the cell in the corresponding row and column of ProcurementSchedule. This second term is a reference determined by implicit intersection. The INDEX returns the entire row of ProcurementSchedule, selected by RowIndex, and implicit intersection picks off the cell of that row that lies in the same column as the invoking cell. In this way, we compute a running sum.

The advantage of this method is that it eliminates explicit cell references.

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