Supply Chain Engineering

***Please Solve Using Excel***

Carrier Range of Quantity Shipped |(cwt) 200+ Shipping cost ($/cwt) 6.50 AM Railroad Northwest Trucking 100+ 7.50 50-100 8.00 Eastern Electric (EE) is a major appliance manufacturer with a large plant in the Chicago area. EE purchases 120,000 motors each year from Westview at a price of $120 per motor. Each motor averages about 10 pounds in weight, and EE has traditionally purchased lots of 3,000 motors. Westview ships each EE order within a day of receiving it. EE carries a safety inventory equal to 50 percent of the average demand for motors during the delivery lead time. (h=25% for inventory) The plant manager in EE received several proposals (see the following table) for transportation and need to decide on the one to accept. Ship time by rail is 5 days (3 days for trucks) What should the plant manager do? Golden Freightways Golden Freightways Golden Freightways 150-250 6.00 250+ 4.00 I 1 cwt =100 pounds a) Evaluate the total cost for Golden Freightways option (50 -100 CWT) in Example 14.2 on slides 27-28 of Ch 14. b) Assume the standard deviation of demand per day is 80, and the standard deviation of the lead time is 2 days for trucks. The safety inventory is based on the targeted cycle service level of 0.92. Then what is the total cost for the Golden Freightways option