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
Demonstrations for Session 5
Course Project
Your next assignment is to develop a proposal for your course project. When you actually begin work on your project, you’ll encounter issues related to sharing Excel workbooks. That’s why we have a demonstration (2007+) for this session on the topic: Merging two workbooks that model hiring.
Merging two workbooks that model hiring (2007+)
[Sheet: Hiring]
This demonstration is a little different from our other demonstrations, because you’ll be doing most of the work. The idea is that you’ll make two workbooks that start from the demonstration workbook, and then you’ll merge them, to see how our merging method works. So let’s get started.
Download the demonstration workbook, and make two copies. Pooh.xlsx (Excel 2007+) will hold the independent sheet set, which contains just the Parameters worksheet. Piglet.xlsx (Excel 2007+) will hold the dependent sheet set, which contains just the Hiring worksheet.
But for now, the two workbooks are identical, just as they would be when the two team members, Pooh and Piglet, went their separate ways to work on their respective parts of their project.
Here’s what they will do. Pooh, working on the independent subset of the sheets of the workbook, adds two new parameters to the sheet Parameters. Let’s say he adds InitHdctEng and InitRev. Meanwhile, Piglet adds a headcount stream for Engineering. That stream uses InitHdctEng, which isn’t actually available in Piglet’s workbook. So Piglet creates it in Piglet’s version of the Parameters worksheet. On Piglet’s copy of the Hiring worksheet, Piglet then creates a running sum that will represent engineering headcount. Piglet uses the same hiring stream as was used for sales headcount, which isn’t very realistic, of course. But it isn’t relevant to this demonstration, so there’s no harm in using this simplification.
Now it’s your turn. Perform these operations on your Pooh and Piglet workbooks, and when you’re finished, save them and come back.
Welcome back. Pooh and Piglet now want to merge their two workbooks. In the end, they each want to have their own copies of an updated workbook that contains Pooh’s Parameters worksheet and Piglet’s Hiring worksheet. Here’s a summary of the procedure they intend to use:
- First they decide who will be merging the two workbooks. In our case, it’s Pooh. So Piglet sends Pooh his workbook. Piglet promises not to make any more changes to his copy until he receives the update, and then all future changes will be made in the update.
- Next, Pooh saves copies of both workbooks in a special place to keep them safe, in case anything goes wrong.
- Next, Pooh opens Pooh.xlsx and deletes the worksheet Hiring, which is now out of date.
- Next, Pooh opens Piglet.xlsx, and moves or copies the worksheet Hiring to the workbook Pooh.xlsx. To do this, Pooh uses the worksheet tab context menu.
- Next, Pooh activates Pooh.xlsx and uses the command Edit>Links… (Excel 2011) or Data>Connections>Edit Links (Excel 2007, 2010, and 2013) to open the Edit Links dialog.
- Next, Pooh clicks Change Source… To expose the Change Source dialog.
- Next, Pooh navigates to the workbook Pooh.xlsx, selects it, and clicks OK. If he’s asked for a specific worksheet, he selects the right one.
- Last, Pooh saves Pooh.xlsx
Your task is now to merge your copies of Pooh’s and Piglet’s workbooks using this method. It is also described in the class notes, and in the readings How to Merge Workbooks.
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.