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

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

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.

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

To find out 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.

(d) [10] 

Now compute a range that shows the actual number of trailers of each item you must order in each quarter to make sure 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 make sure 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 class.

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.

(e) [10] 

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.

(f) [10] 

In part (e) you computed the excess trailer loads acquired in each quarter. To find out 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.

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

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

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

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