Spreadsheet Models for Managers
Getting Access to Spreadsheet Models for Managers
If 
you 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:
|
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.
| Term | What It Means |
|---|---|
absolute reference 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 2 | A rectangular range. |
| array arithmetic 2 | Any of the arithmetic operations as performed on arrays in Excel. |
| array formula 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 12 | In a service system, the statistical distribution of arrival times. We assume that arrivals obey the Poisson distribution. |
| arrival mechanism 12 | In a service system, this is how customers enter the service line. |
| assignment statement 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 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 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 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 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 1 | A contiguous range of streams that lie in adjacent rows and which are dealt with as a single entity. |
| convolution 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 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 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 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 1 | A model of a system whose behavior varies with time. |
| Economic Order Quantity 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 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 1 | A model is said to be faithful when it exhibits behavior analogous to the system it models. |
| fidelity 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 13 | A macro that returns a value. Function macros are not recordable. |
| function statement 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 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 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 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 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 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 7 | A detailed collaborative examination of a work product. |
| issue 7 | In a review or inspection, an element of a work product identified as a deviation from standard. |
| issues list 7 | In a review or inspection, the collection of issues; output of a review/inspection. |
| iteration statement 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 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 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 2 | A rectangular array of data, intended to be used in a matrix product or as a matrix inverse. |
| matrix inverse 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 2 | Matrix Multiplication is an operation performed on two or more matrices to produce the matrix product. |
| matrix product 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 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 1 | A simulation of a system, such as a business or business process. |
| modeling 1 | Modeling is the art of simulating a system, such as a business or business process. |
| moderator 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 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 1 | A range or value that has a user-defined name in Excel. Usually it is a parameter of the model. |
| OpenXML 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 1 | A stream that represents an output of a model. Since it is an output, it is a stream of computed quantities. |
| parameter 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 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 7 | In a review or inspection, the people on the review team responsible for identifying issues. |
| reference 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 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 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 7 | A collaborative examination of a work product, usually at a high level. |
| ripple principle 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 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 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 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 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 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 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 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 13 | A VBA statement (beginning with Dim) that declares that a given variable has a given data type. |
| VBA 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 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.