Rework Problem 18-8 using a spreadsheet, and do sensitivity analysis with respect to quantity ordered.

Data from Problem 18-8:

Lucas Mitchell, financial manager of Inland Industrial, has been asked by the firm's CEO to evaluate the company's inventory control techniques and to lead a discussion of the subject with the senior executives. Lucas plans to use as an example one of Inland's "big ticket" items, a customized high-pressure valve system. Each system costs Inlands $125 and in addition it must pay its supplier a $1,000 setup fee on each order. Furthermore, the minimum order size is 300 units; Inland's annual usage forecast is 4,000 units; and the annual carrying cost of this item is estimated to be 10% of the average inventory value. 

Lucas plans to begin his session with the senior executives by reviewing some basic inventory concepts, after which he will apply the EOQ model to Inland's valve inventory.

Lucas plans to begin his session with the senior executives by reviewing some basic inventory concepts, after which he will apply the EOQ model to Inland's valve inventory. As his assistant, you have been asked to help him by answering the following questions:
a. What is the EOQ for custom valves? What are total inventory costs if the EOQ is ordered?
b. What is Inland's added cost if it orders 600 units at a time rather than the EOQ quantity? What if it orders 1000 units?
c. Suppose it takes 2 weeks for Inland's supplier to set up production, make and test the valves, and deliver them to Inland's plant. Assuming certainty in delivery times and usage, at what inventory level should Inland reorder? (Assume a 52-week year, and assume that Inland orders the EOQ amount.)
d. Of course, there is uncertainty in Inland's usage rate as well as in delivery times, so the company must carry a safety stock to avoid running out of valves and having to halt production. If a 200-unit safety stock is carried, what effect would this have on total inventory costs? What is the new reorder point? What protection does the safety stock provide if usage increases, or if delivery is delayed?