(using excel only functions, no @risk functions) Management of Ebony, a leading manufacturer of bath soap, is trying to control its inventory costs. The weekly cost of holding one unit of soap in inventory is $30 (one unit is 1000 cases of soap). The marketing department estimates that weekly demand averages 120 units, with a standard deviation of 15 units, and is reasonably well modeled by a normal distribution. If demand exceeds the amount of soap on hand, those sales are lost—that is, there is no backlogging of demand. The production department can produce at one of three levels: 110, 120, or 130 units per week. The cost of changing the production level from one week to the next is $3000. Management would like to evaluate the following production policy. If the current inventory is less than L = 30 units, they will produce 130 units in the next week. If the current inventory is greater than U = 80 units, they will produce 110 units in the next week. Otherwise, Ebony will continue at the previous week’s production level. Ebony currently has 60 units of inventory on hand. Last week’s production level was 120.
1. Develop a simulation model for 52 weeks of operation at Ebony. Graph the inventory of soap over time. What is the total cost (inventory cost plus production change cost) for the 52 weeks?
2. Run the simulation for 500 iterations to estimate the average 52-week cost with values of U ranging from 30 to 80 in increments of 10. Keep L = 30 throughout.
3. Report the sample mean and standard deviation of the 52-week cost under each policy. Using the simulated results, is it possible to construct valid 90% confidence intervals for the average 52-week cost for each value of U? In any case, graph the average 52-week cost versus U. What is the best value of U for L = 30?
4. What other production policies might be useful to investigate?