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:

• Worksheet Functions
• Formula Silliness

Numbers in square brackets to the right of the problem numbers indicate point values.

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

Item Number of Pieces per Trailer Cost per Trailer ($) Two Piece Sectional 37 8150 Sofa/Love seat/Chair 34 10205 Single Piece Sofa 48 8265

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

Y1Q1 Y1Q2 Y1Q3 Y1Q4 Y2Q1 Y2Q2 Y2Q3 Y2Q4 Y3Q1 Y3Q2 Y3Q3 Y3Q4 Two Piece Sectional 691 627 670 709 645 709 670 652 Sofa/Love seat/Chair 740 770 615 725 727 814 797 795 Single Piece Sofa 871 804 807 775 748 779 807 746

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

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