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 1Special Tools for This Course
Included in this course are some Excel macros that make life a little easier when you work on your homework or course project.
The special tools for this course are contained in a file called an add-in. To get your copy click its name: smm.xlam (Excel 2007+). Your Web browser then asks you where you want to put it. Put it on your desktop for the time being. Once it’s on your desktop, follow the installation instructions to make it part of Excel.
In some cases, the browser places the file into the Downloads folder. If that happens to you, open the Downloads folder and drag the file to your desktop. The location of the Downloads folder depends on the type of operating system you have, and on the particular version number you have. You (or someone else) might also have customized your computer configuration to have a special place for the Downloads folder. For these reasons, we can’t tell you exactly where your Downloads folder is. But you can get some assistance, perhaps, from some of the many helpful Web sites that are available.
The Excel add-ins for this course contain both menu commands and worksheet functions.
- Commands
- The different versions of Excel display our special commands in different ways:
- When smm.xlam is loaded into Excel 2007, 2010, and 2013, the commands appear on a menu in the SMM group of the Formulas tab of the ribbon.
- When smm.xlam is loaded into Excel 2011, the commands appear on the SMM menu.
- Worksheet functions
- When the add-in file is loaded, you can use any of the worksheet functions it contains.
These tools are useful for more than this course. You aren’t required to pay a license fee to use them elsewhere, but please do not redistribute them to others.
About add-in files
An “.xlam” file (Excel 2007+) is a form of an Excel workbook called an add-in. The add-in format is an Excel file format that has the effect of adding capabilities into Excel in such a manner that the added capabilities appear on an almost-equal footing with Excel’s built-in capabilities. This makes them easier for you to use than they would be if we provided them in any other way.
If you’re reading this at the beginning of this course, much of the description below might not make sense to you. Don’t worry. We’ll cover everything you need to know. Skip directly to the installation instructions.
The commands
The commands are:
- Define Above, Below, Left, Right
- Define Local Name…
- Resize Current Array
- Tile Top Two Windows
- Change SMM Links
- Apply Names to Chart Objects
- Test Installation
- Show Version…
- Help
- SMM>Define Above, Below, Left and Right (Excel 2011) or
Formulas>SMM>Menu>Define Above, Below, Left and Right (Excel 2007, 2010, and 2013) - This command defines, for each worksheet in the workbook, the local names Above, Below, Left, and Right, as references relative to the cell in which they’re used — one cell above, below, left, and right, respectively. It also defines the name Myself as a reference to the cell in which it’s used. Each name is a local name. These names are defined on all worksheets in the active workbook.
- If any of these names already exists as a global name, Excel asks whether you want to override the preexisting name. If you agree, it’s done. If not, the operation halts.
- If any of the names is already defined as a local name on any sheet, and that definition differs from the one about to be installed, you’re asked whether you want to override the preexisting name. If you agree, it’s done. If not, the operation halts.
- SMM>Define Local Name… (Excel 2011) or
Formulas>SMM>Menu>Define Local Name… (Excel 2007, 2010, and 2013) - This command defines a local name, which you provide, as the selection on the active sheet. You have three options that affect the behavior of this command relative to the other worksheets in the active workbook. These options determine on which worksheets definitions are installed.
- If you elect “Active worksheet only”, then the definition is installed only on the active worksheet. If you elect “Selected worksheets”, then Excel installs analogous definitions on each one of the selected worksheets. If you elect “All worksheets”, then Excel installs analogous definitions on all worksheets of the active workbook. These analogous definitions refer to the corresponding cells on the other worksheets.
- If the name you chose already has one or more definitions on any of the affected sheets, those definitions are overwritten. Be careful. There is no undo.
- SMM>Resize Current Array (Excel 2011) or
Formulas>SMM>Menu>Resize Current Array (Excel 2007, 2010, and 2013) - This command examines the active cell to determine whether or not it contains an array formula. It then removes that formula from the current array, and applies it to the selection, if possible. If the selection contains cells that are part of, but not all of, another array, then no action is taken.
- The purpose of this command is to make it easy to change the shapes of ranges that contain array formulas that use names only. When array formulas use explicit cell references, this command isn’t recommended. The cell references in such situations often are range references that match the shape of the current array. When resizing that array, you often want to change the cell references, and this command isn’t smart enough to do that.
- SMM>Tile Top Two Windows (Excel 2011) or
Formulas>SMM>Menu>Tile Top Two Windows (Excel 2007, 2010, and 2013) - This command arranges the top two Excel windows to cover the screen. One is on the top half, the other on the bottom. This is most convenient when you need to look at two windows at the same time. If there are fewer than two windows, nothing happens.
- SMM>Change SMM Links (Excel 2011) or
Formulas>SMM>Menu>Change SMM Links (Excel 2007, 2010, and 2013) - Use this command when you’re collaborating with someone who uses a Mac, and you’re using any of the functions contained in the SMM add-in.
- If your collaborator is using Excel 2011 for Macintosh, and you’re using Excel 2007, 2010, and 2013, or vice-versa, and you’re using any of the worksheet functions included in our add-in, then a problem arises. When you open a workbook that you received from your Macintosh collaborator, Excel doesn't automatically recognize that your collaborator used worksheet functions from our add-in. This doesn't happen when users of Excel 2011 exchange workbooks among themselves; nor does it happen when Windows users exchange workbooks among themselves; nor does it happen when Macintosh users receive workbooks from Windows users. It happens only when a Windows user receives a workbook from a Mac user.
- When it does happen, it causes Excel to display a “Change Links” dialog. You could follow Excel’s advice, and correct the problem that way, but it’s a hassle. Just cancel out of Excel’s dialog and use this command. That fixes the problem. Of course, when you (the Windows user) send your version of the workbook back to your collaborator (the Mac user), who then makes changes to it and returns it, you’ll have to change the links yet again. But after a while, this becomes routine, and you’ll barely notice the inconvenience.
- SMM>Apply Names to Chart Objects (Excel 2011) or
Formulas>SMM>Menu>Apply Names to Chart Objects (Excel 2007, 2010, and 2013) - This command examines all the chart objects on all selected sheets, and modifies the SERIES formulas in each chart so that they use names when possible. There is no undo.
- SMM>Test Installation (Excel 2011) or
Formulas>SMM>Menu>Test Installation (Excel 2007, 2010, and 2013) - This command examines the installation of the add-in file for this course, and verifies that you have installed it as an add-in on your machine. It records a notice in the active cell that tells you whether the file has been installed in the add-ins folder of your machine. If you haven’t installed the file there, then we won’t be able to execute your homework or course project.
- SMM>Show Version… (Excel 2011) or
Formulas>SMM>Menu>Show Version… (Excel 2007, 2010, and 2013) - This command displays the version of the course add-in. If you encounter a problem that you think might be related to the add-in, and you want to report the difficulty, providing the version information is helpful to us.
- SMM>Help (Excel 2011) or
Formulas>SMM>Menu>Help (Excel 2007, 2010, and 2013) - This command opens this Web page in your default Web browser.
The worksheet functions
The worksheet functions are:
- MMMult for multiplying matrices
- Convolve for performing convolution
- Probability0 for computing the probability that there are no customers in a multi-server system
- LengthSys for computing the number of customers in a multi-server system
- ProbabilityNleS for computing a property of multi-server systems
<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. |
…<end snip>
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