This is a question from the textbook "Business Analytics: Data Analysis & Decision Making," 7th Edition Albright/Winston

Chapter 15, Case 15.2 Ebony Bath Soap

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 100 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 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?"

In this simulation, I'm required to use the NORMINV function with RAND(), and I need to show my work.

Please help me out with formulas and steps to calculate this by providing screenshots from Excel.

I only need to answer Question 1, and I have a screenshot from the textbook to provide the original context.

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