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.
To Order On Line
Order "Spreadsheet Models for Managers, online 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, online 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.
Be sure to check the list of worksheet functions that are needed for the homework assignments, to see which new functions (if any) might help with this assignment.
For a quick way to copy homework problem data into your homework solution, see “Avoid retyping homework problem data.”
Since macros aren’t permitted in this homework assignment, be certain that the workbook you submit for grading has no macros. Read about how to check your workbooks for macros.
Remember that some problems are slight extensions of what we show you in class, in the demonstrations and in the session notes, and some problems are somewhat ambiguous. This is intended to parallel what you’ll frequently encounter at work. If you feel a bit confused, there are some things you can do to help clarify things.
Before attempting this homework, you might find it helpful to read about:
Numbers in square brackets to the right of the problem numbers indicate point values.
In this problem you’ll study the effects of roundoff and truncation errors in models. You’ll find that it can be important to consider the effects of rounding things off, especially if the effects are cumulative. Once you demonstrate this, the problem then leads you to discover a way of dealing with cumulative roundoff error.
You’re in charge of purchasing sofas for Living Rooms Only, Inc., a 500store nationwide retailer that specializes in fast delivery of living room furniture, stocked in depth. It’s critical to your selling proposition that any item offered for sale be available in stock for nextday delivery. To hold down inventory costs, you seek suppliers that are willing to deliver to you with short lead times. Your principal supplier, MegaCouch, Inc., is willing to provide rapid response to your orders, provided that you place orders for full trailer loads of a single item. The number of pieces that a trailer can hold varies with the style number, and so does the cost of a trailer load. They’re shown in the table below.

You anticipate that your needs for these three items for the next eight quarters are as shown in the schedule below:

As you work this problem, remember the Ripple Principle. If you were to change any of the numbers above, then your solution to the problems below should automatically update to reflect the consequences of the change.
Throughout this problem, you’ll be calculating projections about the three types of loads. Present those projections in the order specified above. That is, if the result for Two Piece Sectionals is in Row 12 on your worksheet, the result for Sofa/Love seat/Chair should be in Row 13, immediately below the Two Piece Sectionals. And the result for Single Piece Sofa should be in the row immediately below that, Row 14. There’s nothing special about this order, but we ask you to adopt this order to make grading easier for us.
Create a parameter block that holds the data about price and quantity for the three types of loads. Give the name PiecesPerLoad to the three cells (a 3x1 range) that hold the numbers of pieces of each style that can be shipped in each type of load. Give the name PricePerLoad to the 3x1 range that holds the prices of trailer loads of each style. Your result should be a single 3x2 range.
Create a 3x8 range that holds your planned sales for eight quarters, as given in the table above. Give the name StockRequiredAnalysis to this range. Your result should be a single 3x8 range.
In the remainder of this problem, we’ll look at two inventory ordering strategies that meet your company’s needs while satisfying the vendor’s ordering constraints.
The market position of Living Rooms Only is that by concentrating on Living Rooms, the company can maintain stock indepth, so that customers can receive delivery of whatever they want when they want it. “Stockouts” are to be avoided — they create unhappy customers. On the other hand, to control costs, you don’t want to carry more stock than you actually require. So it’s important to know how much stock you need.
To determine how much stock you need, create a 3x8 range that shows how many trailer loads of each item you would have to order in each quarter to meet your needs. Fractional amounts are OK — all we’re looking for here is to convert your sales plan into units of trailer loads. Use an array formula to compute your result. Format your result to show 0.01 trailer loads. Give the name TrailerLoadsAnalysis to this range. Immediately below this range, in a 1x8 range, enter a single array formula that computes the cost per quarter of buying this inventory. You’ll need to use the Excel worksheet function mmult. Your result should be a single 4x8 range.
Now compute a range that shows the actual number of trailers of each item you must order in each quarter to ensure that you’ve received at least as many pieces of each item in each quarter as you plan to sell. In each quarter, order as many trailers as you need of each item, but order no more than are required to ensure that the vendor delivers at least the number of pieces called for in your sales plan. Remember: even though in some quarters you may need fractional trailers, MegaCouch ships only full trailers. Give this range the name IntegerLoadsAnalysis. You should be able to do this using one of the worksheet functions discussed in this course.
Directly below this range, in a 1x8 range, enter a single array formula that computes the cost per quarter of buying this inventory. You’ll need to use the Excel worksheet function mmult. Your result should be a single 4x8 range.
If you follow the strategy in (d), you’ll find that you do in fact meet your sales needs, but if your company keeps to plan, the ordering schedule is too aggressive. The reason for this is that you accumulate in inventory the fractional trailer loads that you had to order above and beyond your actual need. To see what these are, create a 3x8 range called MarginLoadsAnalysis, which contains the difference between what you actually ordered and what your planned needs were. These differences should be expressed in terms of fractional trailer loads, and should be formatted to display two decimal places. Your result should be a single 3x8 range.
In part (e) you computed the excess trailer loads acquired in each quarter. To determine their cumulative effect, we need to make a running sum of the excess. Create a 3x8 range called CumMarginLoadsAnalysis, which contains the running sum, row by row, of MarginLoadsAnalysis. This shows the accumulation in inventory of the fractional trailer loads that you had to buy to meet the vendor’s requirements.
Directly below this range, in a 1x8 range, enter a single array formula that computes the dollar value accumulated by buying this extra inventory. You’ll need to use the Excel worksheet function mmult. Your result should be a single 4x8 range.
In part (f) you found the cumulative excess trailer loads you had in stock. Now, realizing that when the excess stock of any item in any quarter is at least one trailer, you want to figure out whether or not any of this excess can be used to reduce the orders you place with MegaCouch. Create a 3x8 range called CumAvoidableTrailers, which contains the numbers of trailer loads of each item in each quarter that you don’t actually have to order, because they’ve accumulated in your inventory. Another way to understand this is that this range measures, for each item and in each quarter, the integer number of trailer loads that have accumulated in your inventory.
Directly below this range, in a 1x8 range, enter a single array formula that computes the accumulated cost of this inventory. You’ll need to use the Excel worksheet function mmult. Your result should be a single 4x8 range.
Now you’re curious as to how much you save by doing this. Create a 1x8 range that shows the ratio of the cost of the CumAvoidableTrailers to the cost of the planned purchases you calculated in part (d). Express this as a percentage. Note that when average sales are constant, this percentage increases with time. Your result should be a single 1x8 range.
Create a 3x8 range called AvoidableTrailersAnalysis that shows, for each quarter, a plan for drawing down inventory in units of whole trailers such that the accumulation in (g) never gets larger than 1 trailer load. This is, in effect, the number of excess trailer loads you have in inventory, and therefore do not have to order in that quarter. Hint: you can do this by computing the running difference of your answer to (g).
Below this range, in a 1x8 range, enter a single array formula that computes the cost per quarter of buying this inventory. You’ll need to use the Excel worksheet function mmult. This amount represents the value available to you if you choose to draw down inventory rather than order yet more goods. Your result should be a single 4x8 range.
Create a 3x8 range called ProjectedLoadsAnalysis which shows your actual order schedule, assuming that you use the result of (i) to draw down inventory and avoid placing orders when you can. Below this range, in a 1x8 range, enter a single array formula that computes the cost per quarter of buying this inventory. You’ll need to use the Excel worksheet function mmult. Your result should be a single 4x8 range.
Last Modified: Wednesday, 27Apr2016 04:15:26 EDT
Implicit Intersection is one of the most underrated — and at the same time one of the most powerful — techniques in all of Excel. Yet few people truly understand it.
Implicit Intersection is the method by which one cell can retrieve a value from another range by examining the intersection of its row (or column) with that range. If the intersection is unique — a singlecell — then the formula of the cell that depends on implicit intersection can update its value without incident. If not, an error results.
When talking about worksheet functions, it’s important to be careful about your choice of terminology. Technology is like that, and like it or not, Excel is a piece of technology.
Cells can have formulas, as we’ve seen, and those formulas can invoke worksheet functions. Cells do not contain functions — rather, they can contain formulas, which, in turn, can invoke one or more worksheet functions.
Userdefined names are not functions.
To invoke a worksheet function in the context of a cell formula, one calls a worksheet function. Often, you hear this described as “applying a worksheet function.” Do not use that terminology. For example, we’ll speak of “calling a function on its arguments,” or “calling a function with its arguments.” We do not say that we “apply a function to its arguments.”
When Excel calculates the value of a cell, and that cell’s formula contains a call to a worksheet function, that function call is evaluated. Its value is then returned to the formula, which uses it, in turn, to compute its own value.
Sometimes you hear worksheet functions referred to as commands. They aren’t commands. Commands are found on Excel’s menus, or perhaps in some dialog boxes. Commands do things, like format a cell, or sort a range. Commands don’t return values — functions return values.