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

Glossary of Terms

This page contains brief definitions of terms we use in this course.

Along with each entry is a number indicating the number of the session in which the term is first introduced. Click that number to go to the session page for that session. Some entries also have small blue squares to the left of the session number. These squares are linked to sections of the Readings that discuss the term in more detail.

You’ll probably find this page useful not only as a place to look things up, but also as a place to find useful links.

TermWhat It Means
absolute reference
Blue square 1 
A reference that has the property that no matter where you copy, move or fill it, it continues to point to the same range. See also mixed reference and relative reference
analysis
2 
The separation or decomposition of a whole into component parts. The partner of synthesis
areas
4 
The elements of a compound range. For instance, A1:C2 is an area of the compound range (A1:C2,D5:G9). 
array
Blue square 2 
A rectangular range
array arithmetic
Blue square 2 
Any of the arithmetic operations as performed on arrays in Excel. 
array formula
Blue square 2 
A kind of formula in Excel in which a contiguous rectangular range of cells can share the formula, and produce a result together that is apportioned amongst the cells. 
array function macro
13 
An array macro is a function macro that returns a rectangular array of values. 
arrival distribution
Blue square 12 
In a service system, the statistical distribution of arrival times. We assume that arrivals obey the Poisson distribution
arrival mechanism
Blue square 12 
In a service system, this is how customers enter the service line. 
assignment statement
Blue square 13 
In Visual Basic for Applications (VBA, Excel’s macro language) a statement that assigns a value to a variable. 
balance sheet
8 
One of the three financial statements, it reports assets and liabilities, which must always balance. 
bar/column chart
6 
A chart form for a few discrete, usually unordered, categories. 
base response
Blue square 4 
The temporal response of a system to a unit stimulus in the first time period. Used in convolution
capital lease
10 
Long-term, non-cancelable contract transferring most risks and rewards of ownership. It is often used for equipment, and title transfers to lessee at the end of the lease term. See also operating lease
capital transaction
8 
Either a purchase or a sale, with impact beyond the current reporting period. Capital transactions tend to affect all three financial statements. 
cash flow
8 
One of the three financial statements, it reports changes in the cash position of the organization, in the form of inflows and outflows. 
category
6 
The abscissa (horizontal axis) of a series. 
cell reference
1 
A cell reference is a reference that refers to a single cell on a worksheet. 
checklist
Blue square 7 
In a review or inspection, the set of standards with which the work product must comply. 
circular reference error
3 
A condition that arises when a chain of cell dependencies closes on itself -- where a cell’s value depends, ultimately, on its own value. Excel begins to chase its own tail, then gives up. 
command macro
Blue square 13 
A macro that changes the state of Excel or its objects. Since it is equivalent to some sequence of menu commands, keystrokes and mouse gestures, it is recordable.  
compound parameter
Blue square 1 
A compound parameter is a contiguous range of parameters that lie in adjacent cells and which are dealt with as a single entity. 
compound range
4 
A compound range is a collection of ranges. For instance (A1:C2,D5:G7) is a compound range with two elements. The elements of a compound range are called areas
compound stream
Blue square 1 
A contiguous range of streams that lie in adjacent rows and which are dealt with as a single entity. 
convolution
Blue square 4 
An operation performed on two data streams. Convolving a stimulus with a system’s base response yields the response of the system to the stimulus. 
cost to hold
Blue square 11 
In inventory management, cost to hold is usually cited as the actual cost of carrying one unit of inventory for one year. 
cost to order
Blue square 11 
In inventory management, cost to order is the actual total cost of processing an order. 
crowding
3 
Crowding is the opposite of cushioning, in which you intentionally plan for insufficient resources. Usually it’s bad business, but if you have to do it, it pays to know how to model it correctly. 
cumulative sum
1 
A cumulative sum, or accumulation, of a stream is a running sum of that stream. 
cushioning
3 
Cushioning is the practice of including safety margins in your models to mitigate the effects of approximations and change. The opposite of crowding
data type
Blue square 13 
One of several possible kinds of data supported by VBA, including Integer, Long, Double, String, Boolean, Object and Variant, and many others. 
departure
12 
In a service system, the process for customer exit after service. 
departure distribution
12 
In a service system, the statistical distribution of service times. We assume an exponential distribution. 
doughnut chart
6 
Multiple series pie chart
dynamic array
14 
A VBA structure that provides the ability to declare the dimensions of an array at runtime. 
dynamic model
Blue square 1 
A model of a system whose behavior varies with time. 
Economic Order Quantity
Blue square 11 
In inventory management, the optimum order quantity for constant demand and constant price. Assumes that the reorder point occurs at exactly the moment of inventory exhaustion. It also assumes that price is independent of the quantity ordered, which is somewhat unrealistic in today’s markets. 
EOQ
Blue square 11 
Economic Order Quantity 
error value
7 
Excel’s error values are values that cells can post when they cannot complete the evaluation of their formulas due to a calculation error. For more about Excel’s error values, see on-line help. 
faithful
Blue square 1 
A model is said to be faithful when it exhibits behavior analogous to the system it models. 
fidelity
Blue square 1 
Said of a model. A model has fidelity when it is faithful -- when it emulates reality with respect to characteristics of interest. 
function macro
Blue square 13 
A macro that returns a value. Function macros are not recordable. 
function statement
Blue square 13 
The statement needed for defining a VBA function. 
future value
9 
The value in the future of an asset stream. In principle, there is no restriction on the spacing of the elements of the stream, or on their size. But Excel’s worksheet function FV does assume uniform spacing and constant size. 
global name
Blue square 1 
A global name is a name that has workbook-level scope. If referenced without a worksheet qualifier, the value returned is the value of the name on the sheet from which it is referenced, or if no name like that is defined on that sheet, the value returned is the value of the global name. 
identity matrix
Blue square 2 
A square matrix consisting of all zeros, except for the elements of the main diagonal (from upper left to lower right), which are all ones. 
if statement
Blue square 13 
A VBA statement that provides branching on a Boolean expression 
implicit intersection
3 
Implicit intersection is the process Excel uses to extract a value from a range when that range when there is some ambiguity as to which part of the range should be used. The cell that lies in the same row or column of the calling cell is the one that is used. 
income statement
8 
One of the three financial statements, it reports revenue, expenses, depreciation and other items needed to compute net income. 
input parameter
Blue square 1 
A parameter of a model that is used as an input. As such, it must be a constant -- a number, text, or Boolean value (TRUE, FALSE). 
input stream
Blue square 1 
A stream that represents the an input of a model. Since it is an input, it consists solely of numbers, text or Boolean values (TRUE, FALSE). 
inspection
Blue square 7 
A detailed collaborative examination of a work product. 
issue
Blue square 7 
In a review or inspection, an element of a work product identified as a deviation from standard. 
issues list
Blue square 7 
In a review or inspection, the collection of issues; output of a review/inspection. 
iteration statement
Blue square 14 
A VBA statement that provides looping control 
Lease Characteristic Array
10 
Summarizes the effect of a one-time lease event on the three financial statements. Each row of the array represents one of the financial effects of the lease, such as interest payments, principal payments, and so on. Use the LCA with convolution, with a stream of lease events, to obtain the results of that stream. 
line chart
6 
A chart form for continuous or time-evolution data. 
link
1 
In spreadsheets, we say that one cell “links” to another if its formula contains a reference to the cell it links to, or to a range containing that cell. 
local name
Blue square 1 
A local name is a name that has worksheet-level scope. If referenced without a worksheet qualifier, the value returned is the value of the name on the sheet from which it is referenced. 
macro
Blue square 13 
A (usually small) program written in conjunction with a large application. In Excel, most macros are written in a language called Visual Basic for Applications (VBA). 
matrix
Blue square 2 
A rectangular array of data, intended to be used in a matrix product or as a matrix inverse
matrix inverse
Blue square 2 
The inverse of a matrix, the matrix inverse, is a matrix such that when multiplied by the original matrix, the result is an identity matrix. Not all matrices are invertible. 
matrix multiplication
Blue square 2 
Matrix Multiplication is an operation performed on two or more matrices to produce the matrix product
matrix product
Blue square 2 
A kind of product of two or more matrices that yields a third matrix. It is required that the number of columns of the first matrix equal the number of rows of the second. Order matters. In Excel, it is obtained by means of the worksheet function MMULT as an array formula
mixed reference
Blue square 1 
A reference that is relative in its column and absolute in its row, or vice-versa. See also relative reference and absolute reference
model
Blue square 1 
A simulation of a system, such as a business or business process. 
modeling
Blue square 1 
Modeling is the art of simulating a system, such as a business or business process. 
moderator
Blue square 7 
In a review or inspection, the owner of the review/inspection process. The moderator recruits the team, chairs the meeting, and ensures that all issues discovered are addressed by the author after the review meeting adjourns. 
module
Blue square 13 
A component of a workbook that holds a VBA macro. Modules are not visible as sheet tabs. To see them, you must open the VBA editor, which you can access through the Visual Basic Editor on the Visual Basic toolbar. If you plan to do much macro work, make the Visual Basic toolbar visible. 
named parameter
Blue square 1 
A range or value that has a user-defined name in Excel. Usually it is a parameter of the model
OpenXML
Blue square 1 
OpenXML is the name of the file format used by versions of Excel (and all other Microsoft Office applications) beginning with Office 2007. 
operating lease
10 
A type of lease, often used for facilities, in which the lessor retains ownership. Payments under terms of an operating lease are treated as rent. 
output stream
Blue square 1 
A stream that represents an output of a model. Since it is an output, it is a stream of computed quantities. 
parameter
Blue square 1 
An element of a model that represents one of the attributes of the system that is being modeled. It can be a single number, or a set of numbers. 
parameter block
1 
A range of cells that holds the values of all model parameters. This is a good practice. When you want to change a parameter, you know exactly where it is in the parameter block. 
parameter sprinkling
Blue square 1 
A practice in which the value of a model parameter appears multiple times in a model. This is a bad thing to do. When you want to change the value of that parameter, you have to chase around and find all the copies, changing each one. 
pie chart
6 
A chart style suitable for a single series. This is the familiar circular form, in which the relative magnitude of each data point is represented as a pie slice of an appropriate angle. 
planned margin
3 
Planned margin is the difference between what you actually need and what you plan to have. It can be positive (cushioning), or negative (crowding). 
Poisson distribution
12 
A statistical distribution used to model independent events. We use it to model customer arrivals in a service system
present value
9 
The value now of a stream of assets received in the future. In principle, there is no restriction on the spacing of the elements of the stream, or on their size. But Excel’s worksheet function PV does assume uniform spacing and constant size. 
process pattern
7 
In organizations, a way of characterizing the process in use as to its overall effectiveness. Usually, process patterns are organized into a hierarchy, sometimes called a “maturity model.” 
quantization
3 
Quantization effects occur when a model attribute is restricted to certain values or bands. For example, price might “staircase” according to the volume ordered, or you might be able to manufacture only in lots of a specific size. 
range
1 
A collection of cells on a worksheet. The cells in a range can be adjacent or nonadjacent. A range can consist of any positive integer number of cells. A 1x20 range is a rectangular range of 1 row by 20 columns. A mxn range is a rectangular range of m rows by n columns. 
reader/reviewer
Blue square 7 
In a review or inspection, the people on the review team responsible for identifying issues. 
reference
Blue square 1 
An element of a formula that denotes a range on a worksheet. For instance, S12 denotes the cell in column S, row 12; S12:T13 denotes a range whose upper left corner is S12 and whose lower right corner is T13. 
reference operator
Blue square 4 
An operator that takes references as operands, and produces a reference result. Excel’s reference operators are space (for intersection), comma (for union) and colon (for range). 
relative reference
Blue square 1 
A reference that has the property that when copied or filled, its new instances point to a range that bears the same relation to the cell that contains the reference, as the original did. See also mixed reference and absolute reference
review
Blue square 7 
A collaborative examination of a work product, usually at a high level. 
ripple principle
Blue square 1 
In homework, as in models, In models, certain values are given as parameters. From these values, we compute results. A model that obeys the Ripple Principle is one in which changing the inputs leads to corresponding changes in results. 
running difference
1 
A running difference is a cell-by-cell subtraction of a range of cells. It is sometimes called a “derivative” of the stream it differences. 
running sum
1 
A running sum is a cell-by-cell cumulative sum of a range of cells. It is sometimes called an “accumulation” or an “integration” of the stream it sums. To compute a running sum of a stream, you need a stream and an initial value. 
scalar function macro
13 
A scalar macro is a function macro that returns a single value. 
scatter chart
6 
A chart style best used for displaying correlations. 
scope
Blue square 1 
The scope of a name definition is the realm in which it is valid. There are two possible scopes -- local (sheet-level) and global (book-level). 
scribe
Blue square 7 
In a review or inspection, the person responsible for recording and disseminating the information generated. 
selection for service
12 
In a service system, the process for selecting the next customer. 
service process
12 
In a service system, this is how customers are serviced. 
service system
Blue square 12 
A service facility containing servers, customer entry and exit facilities, and a waiting facility. 
source population
12 
In a service system, this is where customers come from. 
SOYD depreciation
8 
Sum-of-the-Years-Digits depreciation is depreciation method in which the amount of useful life consumed in year k is (N-k+1)/(N*(N+1)/2), where N is the useful life of the asset. 
stacked area chart
6 
A chart form for time evolution of a pie chart. 
static model
Blue square 1 
A model of a system whose behavior does not vary with time. All parameters are constant. Alternatively, a static model can be a model of a time-varying system at a single moment, or a sequence of single moments. 
straight-line depreciation
8 
A depreciation method in which an equal amount of the assets cost is considered to have been consumed in each year of the asset’s useful life. 
stream
Blue square 1 
A range of cells, usually a portion of a single row. We call it a “stream” because we think of it as representing a time series of a model attribute. For example, it could represent revenue generated in each of the time periods of a model. 
synthesis
2 
The composition or combination of parts or elements into a whole. The partner of analysis
temporal behavior
4 
The behavior of a model’s attributes with time, or actually, simulated time. 
temporal response
Blue square 4 
How a system responds, over time, to a stimulus. 
three financial statements
8 
There are three standard financial statements that are used to model the overall financial condition of organizations: Income Statement, Balance Sheet, and Cash Flow
transpose
Blue square 2 
The transpose of a matrix is the matrix obtained by interchanging its rows and columns. In Excel, obtained by means of the worksheet function TRANSPOSE in an array formula
variable
13 
In Visual Basic, a name that can hold VBA data. 
variable declaration
Blue square 13 
A VBA statement (beginning with Dim) that declares that a given variable has a given data type
VBA
Blue square 13 
Visual Basic for Applications. Excel’s macro language, used for many Microsoft products. 
VBA array
14 
An ordered collection of items with 1 or more dimensions of index. 
waiting line or lines
12 
In a service system, the facility for storing customers. It might not actually be a physical line. For example, in the doctor’s office, we wait in chairs, and the chairs are in no particular order. Indeed, sometimes we are even called into an inner examination room, where the wait resumes. So, waiting lines need not be lines, and need not even be physically co-located. Perhaps the best example of this is the electronic waiting line our computer files pass through when we send them to the printer. 
worksheet function
Blue square 1 
A worksheet function is similar to a mathematical function. It accepts arguments, and returns a value or values. You can use a variety of worksheet functions in cell formulas. 

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

Over the years, students have submitted hundreds of course projects. Having worked with these teams, and graded their submissions, we’ve noticed patterns in the kinds of issues that tend to be challenging for project teams and individuals as they develop their projects. The most common traps are summarized in a Web page: “Common Mistakes in Past Student Projects.” It isn’t required reading, but we do recommend it.

Inspect Your Project Early

Many believe that the main benefit of spreadsheet inspections is that they locate issues so they can be fixed. Certainly they do accomplish that. But spreadsheet inspections, when performed early enough and often enough, can actually prevent problems. And preventing problems is certainly more valuable than locating them.

We hope that you’ll apply what you learn about spreadsheet inspections when you work on your projects. If you’re working in a team, review your project schedule and decide when would be advantageous times to insert an inspection or two. If you’re working alone, ask someone else who’s working alone if they would be willing to inspect your project in exchange for your inspecting theirs.

Since we don’t grade on a curve, helping someone else doesn’t hurt you. Inspection exchanges raise the quality of both projects — and both grades. Whatever you do, don’t wait until the end to do your inspections.

Do You Know About the Project Library?

We’ve collected examples of course projects students have submitted over the years. They’re stored in the Course Project Library.

Because we change the project requirements every year, the projects in the library aren’t necessarily precise examples of what you’ll be doing, but they do give you some insight into the kind of thing we’re looking for.

Most important, in the Final Report is a section called Lessons Learned. If you take time to read the Lessons Learned from these projects, you’ll be able to avoid the troubles many of your predecessors encountered. There’s little point in repeating the mistakes of others, so take a look at their lessons learned.