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 Session 5How to Merge Workbooks
 

When a group collaborates in developing a single workbook, and its members want to work in parallel, they need methods for merging the results of their efforts into a single workbook. Here’s a survey of available methods for merging workbooks.

Many spreadsheet projects in today’s workplaces are too complex to be developed by a single person working alone. Sometimes a single person could actually do the work, but it would just take too long. Teams can often do the required work faster and better than one person working alone can. In this course, many of you will be developing your course projects in teams, and you’ll face many of the same challenges that teams encounter in the workplace.

One of those challenges relates to the problem of merging the work teammates do independently of each other. That is, when they work in parallel on different versions of a single workbook, what can they do to bring it all together? This page describes a method for merging workbooks.

Why you might want to merge workbooks

When several people work on a single spreadsheet project, we’d like them to be able to work simultaneously. If the project consists of a single workbook, as most do, then a problem arises: how can several people work on the same workbook at the same time?

The short answer is: they can’t. But Excel does have a way to address this situation. It’s called workbook sharing. It works like this:

  • The original workbook is declared shared.
  • Everyone gets a copy of the workbook and makes the changes they want to make.
  • Periodically, by saving their copies, each teammate gets updates that show the latest changes everyone else has made.
  • When they reach a point where they want to consolidate everything, or when they reach the end, they turn off sharing and Excel tries to merge all the changes.
  • In the case of conflicts, there are some tools to help straighten things out.

It sounds pretty good, and for some projects, it works fine. But there’s a catch. Array formulas are not supported for sharing. To make a change in an array formula, the team would have to “check in” all their changes, turn off sharing, resolve any conflicts, save a new copy, insert the array formula, and restart sharing.

For a complete list of features not supported by workbook sharing see the following pages at Microsoft.com: Excel 2007, Excel 2010, and Excel 2013. Excel 2011 is probably similar to Excel 2010.

As we’ve seen in this course, array formulas are powerful and important. Until Excel supports them, Excel’s file sharing mechanism is too limited for most serious modeling efforts. We have to find another way.

This page describes an approach that will let a team work on a project together, provided they divide the work cleverly enough.

The simplest case

To explain the method, let’s start with the simplest possible case. Let’s suppose that Pooh and Piglet are working on a spreadsheet project together. Pooh is responsible for the worksheet that contains all the input streams and input parameters, and Piglet is responsible for the worksheet that does all the computations and produces the outputs. There are just two worksheets in the workbook.

The action starts when Pooh creates some of the inputs and parameters in a worksheet called Inputs, and a blank worksheet Outputs, and passes the workbook to Piglet, who then starts working out some of the computations and outputs.

As time goes by, Piglet decides to add a worksheet called Computations. Pooh adds a worksheet called Internals, which contains some additional parameters. Pooh adds some inputs and parameters to the Inputs worksheet, and adjusts some values of existing parameters.

Thus, the two of them are working in parallel, and their two workbooks are already very different.

The time comes when Piglet is ready for some of the new inputs and parameters that Pooh has implemented, so he would like to incorporate them somehow. Copy and Paste won’t work, though, because there are now some extra worksheets. (Even if there were only one worksheet, copy and paste still wouldn’t work in most circumstances.)

The first of the two workbooks is Pooh.xlsx. It contains three worksheets (Inputs, Internals, and the original empty version of Outputs). The second is Piglet.xlsx. It contains the three worksheets Inputs (the original version), Computations, and Outputs.

So, what do they do? Here’s the procedure:

  • First they decide who will be merging the two workbooks. In our case, it’s Pooh. So Piglet sends Pooh his workbook.
  • 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 Outputs, which is now out of date.
  • Next, Pooh opens Piglet.xlsx, and moves or copies the worksheets Computations and Outputs to the workbook Pooh.xlsx.
  • Next, Pooh activates Pooh.xlsx and uses the command Edit>Links… (Excel 2011) or Data>Connections>Edit Links (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

That’s it. He returns the result to Piglet, and off they go.

More complex cases

This method works for any workbook that can be divided into sets of worksheets that have dependencies that can be ordered, and provided the teammates don’t work on each other’s worksheets. It’s easy to understand this requirement if we consider a slightly more complicated case.

Suppose Pooh had added something to his Inputs worksheet that made it depend on something on the Outputs worksheet. In that case, the dependencies of his worksheets would form a loop: Inputs depends on Outputs which depends on Inputs. When he deletes the worksheet Outputs in preparation for inserting the version Piglet has been working on, the dependency is erased, and the merge fails.

Thus, if there is a dependency loop among the worksheets, a single owner must be assigned to that group of worksheets.

A slight shortcut

You can sometimes avoid merging by telling your teammates how to change their copies of your worksheets. For instance, if Piglet needs a parameter that Pooh hasn’t implemented yet, they have a choice:

  • Pooh implements the parameter, and merges their two workbooks
  • They both implement the parameter, avoiding the merge. At the next merge, Piglet gets the Pooh implementation, but in the meantime, Piglet uses the Piglet implementation.

This works fairly well for simple changes, but don’t try anything complicated — merging is always safer.

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.

Do You Know About the Project Library?

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.

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.