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
The Basics of Recalculation
Whenever you make a change to one or more of the cells of a worksheet, you could potentially affect the values of cells that depend on the cell or cells you changed. In a spreadsheet, the process that recomputes those dependent values is called recalculation. Here’s a quick summary of how recalculation works.
The power of spreadsheets derives from their ability to quickly recompute the values of cells when changes occur in the values of the cells on which they depend. Whenever you change the value of a cell, the effect of those changes can potentially ripple through the entire worksheet. If cells on other worksheets, or in other workbooks, depend on any of the cells which that change affects, then they, too, must be recalculated.
Most spreadsheet programs, including Excel, give the user some control over recalculation. In Excel, the two basic settings are Manual and Automatic. There are other settings, but these are the most important, and they are the only two we’ll discuss in this course. In essence, the Automatic setting lets Excel determine when to recalculate, and the Manual setting lets the user determine when to recalculate.
The process of recalculating the values of all the cells that depend, directly or indirectly, on whatever you changed is called recalculation. Determining which cells have to change is a very difficult problem, especially if efficiency is important. The designers of spreadsheet programs expend tremendous effort in refining their recalculation algorithms, and they are therefore quite secretive about them. But one concept that they all share is that of the dependency tree, which is essentially a map showing how all cells depend on each other. You can’t view the dependency tree, but knowing of its existence helps you to understand how recalculation works.
The page doesn’t explain how recalculation algorithms work. It does give you a model of how they work, to help you understand how to exploit their properties to make your models easy to use and maintain. Here’s a quick menu of topics on this page:
- Automatic recalculation
- Manual recalculation
- How to decide whether you want automatic or manual recalculation
- How to set Excel’s recalculation to manual or automatic
- Special commands for manual recalculation
<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. |
How to set Excel’s recalculation to manual or automatic
Each different version of Excel has its own unique procedure for setting calculation options.
- Excel 2010 and 2013
- Invoke the menu command File>Options to expose the Excel Options dialog. In the list box to the left, click Formulas. In the “Calculation options” section, set your choice of recalculation style. Click OK or press Enter.
- Excel 2011
- Invoke the menu command Excel>Preferences… to expose the Excel Preferences dialog. (The keystroke Command+, also exposes the Preferences dialog.) In the section Formulas and Lists click Calculation. In the “Calculate sheets” section, set your choice of recalculation style. Click OK or press Enter or Return.
- Excel 2007
- Invoke the menu command Office Button>Excel Options to expose the Excel Options dialog. Click Formulas in the list to the left. In the “Calculation options” section, set your choice of recalculation style. Click OK or press Enter.
Special commands for manual recalculation
When recalculation is manual, and you make changes to a worksheet or worksheets, it’s often useful to inspect the consequences of those changes to ensure that they meet your expectations. To do that, you usually want to recalculate all or part of the workbook or workbooks that are loaded into Excel.
Here are the commands to invoke recalculation on demand, without resorting to setting recalculation to Automatic.
For Excel Excel 2007, 2010, and 2013 (Windows):
| Keystroke | What It Does |
| F9 | Recalculate all worksheets in all open workbooks |
| Shift+F9 | Recalculate the active worksheet |
| Ctrl+Alt+F9 | Recalculate all worksheets in all open workbooks, regardless of whether they have changed since the last calculation. |
| Ctrl+Alt+Shift+F9 | Recheck dependent formulas, and then recalculate all cells in all open workbooks, including cells not marked as needing to be recalculated |
For Excel 2011 (Mac):
| Keystroke | What It Does |
| Command+= or F9 | Recalculate all worksheets in all open workbooks |
| Command+Shift+= or Shift+F9 | Recalculate the active worksheet |
Commands that involve F9 might conflict with the default settings for keystrokes for some operating systems, such as pre-Lion Macintosh systems. It’s possible to reassign keys in Excel, and in the Macintosh system, but these tasks are beyond the scope of this course. See online help on the Macintosh for instructions for your system.
Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT
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.
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.
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.