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
This reading is especially relevant for Session 1Names
Use For examples of defining and using names, see the demonstrations for Session 1.names in your worksheets to make them more understandable. While you’re building a worksheet, you have no trouble remembering that A24, for example, is the Company Average Staff Salary, but after you’ve built several projects, you’ll find that this parameter might be in different places on different worksheets. If you give the parameter a name, and if you use that same name in all your work, it’s easier for you to understand your projects. And it makes it easier for you to explain them to others as well.
- Advantages of using names
- Parameter sprinkling
- Conventions for choosing names
- Namespaces and scope
- Defining names
- Referring to names
- Visual cues and maintainability
- Names for references
Advantages of using names
As a manager, you can insist on the use of names in projects constructed by people who work in your organization. Named parameters make it easier for you to transfer responsibility for project components from one individual or team to another. This makes your organization less dependent on the availability of particular individuals.
Names have numerous other advantages, including lower costs resulting from:
- Higher reliability.
- It’s much easier to tell that a formula that uses the name InterestRateForEquipment instead of the name InterestRateForReceivables is wrong, than it is to tell that it should have used A27 instead of A28. And, as explained below, if you follow certain naming conventions, Excel helps you spot typos.
- Easier copying from one project to another.
- If you use names in formulas, and if you’re consistent from one project to the next, copying formulas across projects is much easier — the names just copy along perfectly. If you don’t use names, then, most likely, literal references such as “A24” would have to be changed when you copy the formulas that contain those references.
- Easier sharing of templates.
- If you make a template for others to use, they’ll have an easier time understanding it if you use names instead of literal references.
- More reliable cross-workbook references.
- In the organizational environment, we often need to link cells across workbooks. If the independent workbook changes, and the cross-workbook reference makes explicit use of rows and column designators, the link might become invalid. Using names ensures that wherever the source range moves, the dependent formulas can find it.
As you become familiar with names, you’ll be able to add to this list of advantages.
If you’re evaluating an organization’s assets, remember that its financial processes use worksheets, and that those worksheets are important assets that help determine organizational effectiveness. If the organization’s worksheet tools don’t use names, what does that tell you about the bandwidth, flexibility, and reliability of the organization’s financial processes? Hint: it’s not a good omen.
Parameter sprinkling
One common practice that raises maintenance costs is parameter sprinkling. Parameter sprinkling is the practice of “hard wiring” numerical parameters into cell formulas. For example, suppose that a depreciation period is 20 quarters. Then in calculating the depreciation amount for one quarter, each cell that computes it must divide by 20. But if later on, a decision is made to depreciate over 12 quarters, all those 20’s must be changed to 12’s. When “20” is used literally in whichever worksheet cells need it, we say that the parameter has been sprinkled. It’s a practice that creates real maintenance headaches.
Conventions for choosing names
The legal characters for names are all the letters and numbers, the underscore and the period. A name must start with a letter or underscore, and cannot look like a literal reference. For example, A1 is illegal as a name because it looks exactly like a reference to cell A1. Prior to Excel 2007, since Excel’s columns were headed A through IV any name of the form
where Number is any positive integer up to 65536, looked like a reference, and was illegal. So, for example, DF27 and IV3123 are just as illegal as A1.
In Excel 2007 (and presumably henceforward), Excel has rows through 1048576, and columns through XFD. Any name that could be a cell reference is still illegal, though now there are many more of them.
Excel won’t let you define a name that looks like a cell reference: F77 for example. But since Excel 2007+ has more rows and columns than earlier versions, the earlier versions sometimes permit you to define names that look like cell references in Excel 2007+. For instance, Excel 2003 won’t object to Cap1 as a name, even though it’s a legal cell reference in Excel 2007+. This isn’t a problem, unless you intend someday to use that workbook in Excel 2007+. Since most of us use current versions of Excel, it’s best to beware of potential problems when loading workbooks constructed in earlier versions of Excel, because they might contain names that could be legal cell references in Excel 2007+. That means: no letter combinations from A to XFD, followed by positive integers less than or equal to 1048576.
It’s a really bad idea to use a name that looks like a column designator. Some of them are legal, such as A and B, but even though they’re legal, such names cause confusion. In Excel 2007+, they range from A through XFD.
More candidates to avoid are the names of Excel’s worksheet functions. There’s no harm in defining a name that’s the same as the name of a function you don’t plan to use, except that it’s sure to confuse you later, or to confuse the poor soul who later comes along and has to maintain what you’ve built. One way to prevent these problems: use mixed case for all your names. This distinguishes your names from Excel’s column designators and worksheet functions, which are always in all UPPER CASE. This is the approach we’ve taken with our names Right and Left. But if that approach doesn’t make you comfortable, avoid the names of Excel’s worksheet functions altogether. See the complete list of worksheet functions.
<snip>…
Some 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. |
Names for references
Suppose that the Staff Salary Per Quarter is in cell A5. Then you want to define the name StaffSalaryPQ to be $A$5, an absolute reference, so that wherever you use it, it points to the same place, even when you copy, paste, or fill. See the explanation of absolute references.
But there is a use for names for relative references. For example, if you’re in cell D4, defining the name Above to be D3 (as opposed to $D$3) gives you a handy way to refer to the cell immediately above wherever you are. Similarly, define Below to be D5, Right to be E4, and Left to be C4. If you include these names in all your worksheets, and use them in formulas, your worksheets will be easier to understand. Of course, it’s best to make these local names. If they’re global names, then they point to the sheet on which you defined them. There is a tool for doing this, included in the add-in for this course.
For example, suppose you need a formula that’s the sum of the cell to the left and the cell above, as we did when we constructed running sums. Using these relative names, the formula would be =Left+Above.
One note of caution about names for relative references: with respect to inserting and deleting cells or rows or columns, their behavior differs from the behavior of the relative references themselves. For example, suppose we have defined the name Above to refer to the cell above, as usual. In two adjacent cells, C8 and D8, enter the two formulas, respectively “=Above” and “=D7”. Next, insert a row above row 8. You’ll notice that row 8 becomes row 9, the formula in C9 still reads “=Above”, and the formula in D9 still reads “=D7”. In other words, the formula with the defined name now refers to a cell in a row different from the row referred to by cell that had the explicit cell reference. Deleting cells also creates different results. Try it. When you use names with relative references, keep in mind that their behavior is different from the formulas that use explicit cell references.
Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT
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.
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