In some ordering problems, like the one for Sam’s Bookstore, whenever demand exceeds existing inven- tory, the excess demand is not lost but is filled by expedited orders at a p

1. In some ordering problems, like the one for Sam’s Bookstore, whenever demand exceeds existing inven- tory, the excess demand is not lost but is filled by expedited orders—at a premium cost to the company. Change Sam’s model to reflect this behavior. Assume that the unit cost of expediting is $40, well above the highest regular unit cost.

2. In the Sam’s Bookstore problem, the quantity discount structure is such that all the units ordered have the same unit cost. For example, if the order quantity is 2500, then each unit costs $22.25. Sometimes the quantity discount structure is such that the unit cost for the first so many items is one value, the unit cost for the next so many units is a slightly lower value, and so on. Modify the model so that Sam’s pays $24 for units 1 to 1500, $23 for units 1501 to 2500, and $22 for units 2501 and above. For example, the total cost for an order quantity of 2750 is 1500(24) 1 1000(23) 1 250(22). (Hint: Use IF functions, not VLOOKUP.)

3. Continuing Problem 1, create a two-way data table for expected profit with order quantity along the side and unit expediting cost along the top. Allow the order quantity to vary from 500 to 4500 in incre- ments of 500, and allow the unit expediting cost to vary from $36 to $45 in increments of $1. Each col- umn of this table will allow you to choose an opti- mal order quantity for a given unit expediting cost. How does this best order quantity change as the unit expediting cost increases? Write up your results in

a concise memo to management. (Hint: You will have to modify the existing spreadsheet model so that there is a cell for expected profit that changes automatically when you change either the order quantity or the unit expediting cost.)