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 1
Introduction to Spreadsheet Models

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

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.

In many of our problem solutions and demonstrations, you’ll notice that we display the definitions of names in a block near the top or bottom of the solution. We do this as a convenience for students who might want to print the solution, or make an Acrobat version of the solution for use on platforms that don’t have Microsoft Excel running. You’ll also notice that on the right-hand side of the worksheet we display the formulas that are entered in some of the cells of the computations. This too is a convenience for similar circumstances.

Please do not interpret the presence of these items in our solutions as an endorsement of these practices. We do not regard it as useful, except for pedagogical purposes. Although you might find others who like the convenience, both practices create significant maintenance issues, and we do not recommend them for general use in spreadsheet modeling. In particular, do not include these features in your course project.

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.

Once you have names defined, and you want to use them in formulas, you don’t have to actually type them unless you want to. In Windows, Excel offers a keyboard shortcut, F3, valid only when you’re typing in the formula bar, which presents a dialog from which you can select the name that you want to be inserted into the formula bar. Try it. On the Mac, use the menu command Insert>Name>Paste…. By default on the Mac, this command lacks a keyboard shortcut, but MacOS makes it easy for you to create one for it. The complete list of keyboard shortcuts for Excel is available in on-line help: search for keyboard. We’ve prepared these help entries as PDF files for you: Excel 2007 (Windows), Excel 2010 (Windows), Excel 2013 (Windows), and Excel 2011 (MacOS). There are far more shortcuts than you can remember or use. But if you find yourself doing something very often, check to see if a keyboard shortcut exists.

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

Deciding What to Read

The first homework assignment has a fair amount of reading attached to it. Some students feel that the best approach is to read it all, and then try to do the homework. For most of us, such an approach doesn’t work very well.

Before you begin the course, read the general material, such as “Getting Started,” “Software You Need for This Course,” and “How to Work.”

Later, as you begin the homework, let the homework drive your reading choices. For instance, the first homework assignment does require that you master certain techniques. Read “Names” and “The Ripple Principle.” Then, if something confuses you, read up on it: examples are “The Basics of Recalculation” and “References.” Learning something when you need it, and only when you need it, is usually the best way to go.

Avoid Redundant Parentheses

Parentheses sometimes make a real difference. For instance A1*B1+2 is very different from A1*(B1+2). But A1*(B1*2) is exactly the same as A1*B1*2. When the parentheses don’t make any difference in the value of the result, it’s not usually a good idea to include them. They tend to make the formulas harder to read, and there’s always the chance that you’ll put them in the wrong place. More