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

Problem Set 3Session Links
Cushioning, Crowding, and Quantization

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.

Blue square Problem 3.1 [100]

In this problem you’ll study the effects of round-off 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 round-off error.

You’re in charge of purchasing sofas for Living Rooms Only, Inc., a 500-store nationwide retailer that specializes in fast delivery of living MegaCouch truckroom furniture, stocked in depth. It’s critical to your selling proposition that any item offered for sale be available in stock for next-day 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.

Retrieve homework data in 2007+ format
Item Number of Pieces
per Trailer
Cost per
Trailer ($)
Two Piece Sectional 378150
Sofa/Love seat/Chair 3410205
Single Piece Sofa 488265

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

Retrieve homework data in 2007+ format Y1Q1 Y1Q2 Y1Q3 Y1Q4 Y2Q1 Y2Q2 Y2Q3 Y2Q4 Y3Q1 Y3Q2 Y3Q3 Y3Q4
Two Piece Sectional 691627670709645709670652
Sofa/Love seat/Chair 740770615725727814797795
Single Piece Sofa 871804807775748779807746

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.

Gray square Problem 3.1 (a) [10]

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.

Gray square Problem 3.1 (b) [10]

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.

Gray square Problem 3.1 (c) [10]

The market position of Living Rooms Only is that by concentrating on Living Rooms, the company can maintain stock in-depth, so that customers can receive delivery of whatever they want when they want it. “Stock-outs” 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.

FurnitureTo 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.

Gray square Problem 3.1 (d) [10]

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.

Gray square Problem 3.1 (e) [10]

LRO TruckIf 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.

Gray square Problem 3.1 (f) [10]

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.

Gray square Problem 3.1 (g) [10]

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.

Gray square Problem 3.1 (h) [10]

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.

Gray square Problem 3.1 (i) [10]

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.

Gray square Problem 3.1 (j) [10]

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, 27-Apr-2016 04:15:26 EDT

Understanding Implicit Intersection

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 single-cell — then the formula of the cell that depends on implicit intersection can update its value without incident. If not, an error results.

Terminology for Worksheet Functions

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.

User-defined 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.