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

This reading is especially relevant for Sessions 1, 2, 3, 4, 8, 9 and 11Worksheet Functions
 

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. This page describes their use, how to determine which ones to use, and the language we use to discuss them.

Cells in a worksheet can contain formulas that are used to determine the value of the cell. Perhaps the simplest formula is a simple number or string of text. More complex formulas can combine the values of other cells, or the results of functions, which in turn can depend on the values of other cells. These more complex formulas must begin with an equal sign (=). For example, a cell that contains the formula

=D5+D4

computes its own value by adding together the values of D5 and D4.

This page has a variety of information about worksheet functions:

How we talk about worksheet functions

Since the language we use to talk about worksheet functions can affect how we understand them, it’s important that we speak about them in a consistent way. You can find a variety of “dialects” of pseudo-mathematical language floating about, and probably one isn’t much better than another. In this course, though, we’ll agree to use certain standard terminology, described here.

Cells can have formulas, as we’ve seen, and those formulas can invoke worksheet functions. Cells do not contain functions — rather, they can contain formulas, which, in turn, can invoke one or more worksheet functions.

User-defined names are not functions.

To invoke a worksheet function in the context of a cell formula, one calls a worksheet function. Often, you hear this described as “applying a worksheet function.” Do not use that terminology. For example, we’ll speak of “calling a function on its arguments,” or “calling a function with its arguments.” We do not say that we “apply a function to its arguments.”

When Excel calculates the value of a cell, and that cell’s formula contains a call to a worksheet function, that function call is evaluated. Its value is then returned to the formula, which uses it, in turn, to compute its own value.

Sometimes you hear worksheet functions referred to as commands. They aren’t commands. Commands are found on Excel’s menus, ribbons, or perhaps in some dialog boxes. Commands do things, like format a cell, or sort a range. Commands don’t return values — functions return values.

How to enter a worksheet function into a formula

Formulas in Excel worksheet cells can include calls to worksheet functions. The syntax of worksheet functions is

<snip>…

Spreadsheet Models for ManagersSome of the page you’ve been reading has been suppressed from this sampler. To see the complete version of this article, why not order the full course? It’s available at this Web site or in downloadable format.

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.

More Info

…<end snip>

A word of warning about Excel’s worksheet functions

Every once in a while, the behavior of some worksheet function or other changes with new releases of Excel. For instance, in Excel 2010, the behavior of ceiling changed. With respect to the form CEILING(number, significance), the Excel 2010 Help file says about opposite sign arguments for CEILING:

If number is negative, and significance is positive, the value is rounded up towards zero.

And here’s what the Excel 2007 and 2011 help files say:

If number and significance have different signs, ceiling returns the #NUM! error value.

Excel 2013 is different again. ceiling now accepts a third optional argument called “mode” which controls which way ceiling does its rounding when its first argument is negative.

They’re different. (Note: Although the 2011 help file aligns with 2007, the actual behavior of 2011 aligns with 2010. The online help for ceiling is incorrect).

Checking FLOOR to see if a similar change had occurred there for Excel 2010, we find a change, but it’s a different change. FLOOR’s online help in Excel 2010 reads:

If number is positive, and significance is negative, FLOOR returns the #NUM! error value.

For Excel 2007 and 2011:

If number and significance have different signs, FLOOR returns the #NUM! error value.

Again, they’re different. (Note: Again, although the 2011 help file aligns with 2007, the actual behavior of 2011 aligns with 2010. The online help for floor is incorrect). For Excel 2013, floor accepts a third mode argument.

This incident actually provides some important lessons about the consistency and compatibility of the different versions of Excel.

  1. Don’t assume that all versions of Excel are identical
    As we’ve seen, there are differences, not just between Mac and PC, but between different releases of Excel for the same operating system.
  2. Groups should agree on a standard version of Excel
    If you’re working in a company or team where you frequently exchange workbooks, it’s best to standardize on a specific release of Excel. If you don’t, you can encounter unexpected inconsistencies that are difficult to sort out. To avoid that problem, if you must use different releases of Excel, have a thorough knowledge of the differences between those releases and avoid contact with those differences.
  3. Avoid “clever” hacks
    Sometimes a particular version of Excel behaves in such a way that you can exploit its behavior to save labor when writing formulas. The more subtle and clever you are when you do this, the less likely it is that anyone else will have tried it, which means that any traps that exist might not be widely known. And that means that if you use a hack, you’re more likely to trip over an inconsistency like these.

Excel’s worksheet functions

A complete list of worksheet functions, with links to detailed help on each one, is available at Microsoft’s Web site: Excel 2007; Excel 2010; Excel 2013; Excel 2011.

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

Matrix Multiplication and Array Arithmetic

For many of you, matrix multiplication and array arithmetic are new ideas. It’s easy to get lost in the details of how they work and then forget about why we use them.

To keep a clear view of the forest and avoid focusing only on the trees, remember why we use matrix multiplication and array arithmetic. Briefly, we use them because we find that it’s very often helpful to decompose a problem into parts (analysis), then do calculations on the parts, and finally reassemble the final solution from the results of those partial calculations (synthesis).

Matrix multiplication and array arithmetic provide us with very convenient methods for performing those intermediate calculations on the parts. They’re the tools that make analysis and synthesis so powerful.

Understanding Implicit Intersection

Implicit Intersection is one of the most underrated — and at the same time one of the most powerful — techniques in all of Excel. Yet few people truly understand it.

Implicit Intersection is the method by which one cell can retrieve a value from another range by examining the intersection of its row (or column) with that range. If the intersection is unique — a single-cell — then the formula of the cell that depends on implicit intersection can update its value without incident. If not, an error results.

Terminology for Worksheet Functions

When talking about worksheet functions, it’s important to be careful about your choice of terminology. Technology is like that, and like it or not, Excel is a piece of technology.

Cells can have formulas, as we’ve seen, and those formulas can invoke worksheet functions. Cells do not contain functions — rather, they can contain formulas, which, in turn, can invoke one or more worksheet functions.

User-defined names are not functions.

To invoke a worksheet function in the context of a cell formula, one calls a worksheet function. Often, you hear this described as “applying a worksheet function.” Do not use that terminology. For example, we’ll speak of “calling a function on its arguments,” or “calling a function with its arguments.” We do not say that we “apply a function to its arguments.”

When Excel calculates the value of a cell, and that cell’s formula contains a call to a worksheet function, that function call is evaluated. Its value is then returned to the formula, which uses it, in turn, to compute its own value.

Sometimes you hear worksheet functions referred to as commands. They aren’t commands. Commands are found on Excel’s menus, or perhaps in some dialog boxes. Commands do things, like format a cell, or sort a range. Commands don’t return values — functions return values.

Avoid Unnecessary Spaces in Formulas

The space character, in many cases, doesn’t change the value of a formula. For instance, these two formulas return the same value:

  • =A1 + 2
  • =A1+2

Some people think that well-placed spaces make formulas easier to read. Although that might be true, the practice is both inconvenient and extremely dangerous. More

Keyboard Power Tips

Excel’s online help, and many of the how-to books you can buy, provide long lists of keystroke shortcuts for carrying out specific operations, such as inserting rows, selecting regions, or deleting columns. And they are useful.

But the true power of the keyboard comes not from using these particular commands. Rather, it comes from learning combinations that are useful for particular situations that you encounter frequently.

For instance, there’s no command for deleting the rows that contain the selected cells, but there is a combination:

  • Shift+Space selects the rows that contain the selection.
  • Ctrl+- deletes the now-selected rows.

And so, Shift+Space Ctrl+- deletes the rows containing the selection.

Learning a vast array of keystroke commands is probably less useful than learning the keystroke combinations that do exactly what you need to do most often.

Using Named Formulas

In the demonstration for this session, we installed a formula for depreciation that looked pretty complicated. It does save maintenance trouble, though, when the depreciation term changes for any reason. But what happens when the depreciation schedule changes in a more radical way? What if the depreciation schedule is made to be some form other than linear?

The end of this session’s demonstration gives an example of an alternative schedule, but as you can see, its formula is very different. If we’re developing a complex model with several applications of depreciation formulas, and the depreciation formulas must be changed, we would have a significant maintenance task on our hands. To avoid that kind of labor, we can define a user-defined name that contains the depreciation formula. For more about this technique, see the tip box in the narrative for this session’s demonstration.

Nesting Worksheet Function Calls

Nesting invocations of worksheet functions can be a bit tricky, because nested function calls are difficult to think about. Sometimes, in developing a spreadsheet model, we can gain clarity by avoiding nesting. That is, while we’re still thinking about how to approach a modeling problem, we intentionally choose to avoid nesting function calls. After we understand the problem better — and only then — we might go back and replace what we’ve done with a more compact version that exploits nesting. In addition to producing forms that are easier to think about, this practice of developing a simpler form first has another benefit. It enables us to examine intermediate values more easily, which enables us to confirm that the calculations we’re performing make sense.

Some feel that building something that you intend to replace is a waste of effort — that it’s far easier to build things in final form from the start. When that approach works, it is faster and more efficient. But when we think we’re likely to make mistakes, the “slower” way is faster.

Do You Know What a Dynamic Model Is?

In years past, we’ve learned that what makes a model dynamic — as opposed to static — can be difficult to grasp. If you have some doubts yourself, and you haven’t yet looked at the reading on Models vs. Tools, we believe that you will find it helpful.

Assuming Constant Demand

Although the assumption of constant demand is critical to justifying the derivation of the formula for Economic Order Quantity, most problems don’t satisfy that requirement in the strict sense. But EOQ is nevertheless a valuable concept in two kinds of circumstances. The first case is when the time scale of the inventory management decisions is much shorter than the time scale of the variations in demand. And the second is when the fluctuations in demand occur much more rapidly than the inventory management decisions.

These two approximations occur repeatedly in modeling problems. Watch for opportunities to apply them elsewhere.