Spreadsheet Models for Managers

Demonstrations for Session 1
Introduction to Spreadsheet Models

We have five demonstrations (2007+) for this session:

• Running Sums and Differences
• Scope of Names in Excel
• A Look Ahead
• Organizing Named Parameters in a Workbook
• Absolute, Relative, and Mixed References
• Applications of Mixed References

In this demonstration page, and in the others like it for other sessions, the headings below correspond to the names of the worksheets in the demonstration workbooks, session01.xlsx.

Running Sums and Differences (2007+)
[Sheet: RunningSumsAndDifferences]

In this first example, in the block titled “Running Sum”, the row captioned “Hire” contains a stream of numbers that represents the hiring plan for the first six months of the year. The row below that, captioned “Headcount”, is a running sum of of the row Hire, with the assumption of an initial head count of 5. This illustrates the use of a running sum, and shows the effect of the initial value.

The block just below, titled “Running Difference”, illustrates the use of a running difference. row captioned “Headcount” is again the running sum of the row captioned “Hire”, with an initial headcount of 5. The row captioned “Differences” recovers the hiring plan from the running sum again, by computing the running difference of the headcount with an initial headcount of 5.

In both of these examples we’ve “wired in” the initial headcount, by explicitly using a “5” in the first cell of the running sum or difference. This is a dangerous practice, which we call parameter sprinkling. We avoid these dangers by introducing a named parameter as shown in “InitHdct”. We’ve named that cell InitHdct. In the block titled “Running Sum with Parameter”, we used that name to compute the running sum in the row captioned “Headcount”.

In the bottom row of the block titled “Running Sum with Parameter”, we compute the same running sum, this time using the names Left and Above, which are named relative references to the cells immediately to the left and above the cell that invokes them. Using these names (Left and Above) makes the formulas much more readable.

Notice that some of the cells in the first column of this example have a double-line border. This serves as a reminder that the formulas of these cells are different from the formulas of their same-row neighbors. Such reminders help prevent errors that sometimes arise during maintenance phases of a spreadsheet’s life, when the maintainer fills a cell across a row and that cell differs from its neighbors. There are other techniques for reminding maintainers of this danger, but this one is one of the simpler approaches.

Scope of Names in Excel (2007+)
[Sheet: MarketingDept]

This example illustrates the scope of names, and shows the use of local (sheet-level) and global (book-level) names. Here we have a different hiring plan, and we compute the total headcount as a running sum in the row captioned “Headcount”. We’ve given that row the name “Headcount” as a local, or sheet-level, name. The row captioned “Sheet-Level” refers to the row captioned “Headcount” means of its name, Headcount. The row captioned “Book-Level” refers to the global name Headcount, which is defined on the sheet RunningSumsAndDifferences. Notice that the two names Headcount are different, and that we can still access either one.

A Look Ahead (2007+)
[Sheet: ALookAhead]

This example is a peek ahead into the power of names. A quick look at the formulas shows you how easy they are to understand, as compared to cell references. We’ll understand more about how these formulas work when we discuss reference operators.

Organizing Named Parameters in a Workbook (2007+)
[Sheet: ParameterBlock]

Here’s an example of a parameter block. This example is taken from a real model I once developed for a client. All of the parameters of the model of their business are collected in one place, where they can readily be reviewed and modified. Yellow indicates user-modifiable.

Absolute, Relative, and Mixed References (2007+)
[Sheet: ReferenceExamples]

This example lets you explore how the four different kinds of references behave when you fill or copy/paste them. Each of the four blocks is made from one kind of reference. The center cell of each block points to C8. By replicating that cell onto its eight neighbors you can see the effects of mixed and relative references.

By selecting a cell in one of the blocks you can see what happened to the formula of the center cell of that block when it was replicated.

Applications of Mixed References (2007+)
[Sheet: ReferencesApplied]

In this example, we show three similar applications of mixed references. This particular kind of application is especially useful when you’re making tables in which each of the cells depends on one value at the start of its row, and another value at the head of its column.

In the first table, we’re calculating the products of the numbers in the leftmost column with the numbers in the topmost row. This is easy enough to do by almost any method, but the virtue of using mixed references is that once you develop the formula for one of the cells of the table, you can fill in the rest of the table with the Fill commands or with Copy-and-Paste. Since filling is probably the most convenient, start with the cell in the upper-left-most table entry. Then Fill Down the first column, then Fill that column right across the table. Or Fill Right first, then Fill Down.

The second table demonstrates exactly the same idea, with one new wrinkle. The values in the leftmost column and topmost row are now the Boolean values, TRUE and FALSE. We’re still doing multiplication, but now we’re multiplying TRUE and FALSE instead of numbers. The point here is that when you perform arithmetic with the Boolean values TRUE and FALSE, TRUE behaves like 1 and FALSE behaves like 0. The next table, which is directly analogous, also illustrates this point, except that we’re performing addition instead of multiplication.

Exploiting the fact that Excel interprets TRUE as 1 and FALSE as 0 for arithmetic can dramatically simplify some computations, making them easier to construct, easier to understand, and easier to maintain.

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