Solve using excel built in functions with screenshots, please use what if data analysis $-$ data table function for Q$2$ of this case study.

CASE $15\mathrm{.}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$-$

ing 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 distribu$-$

tion. 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 produc$-$

tion level from one week to the next is $$3000.$

Management would like to evaluate the following pro$-$

duction 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 pro$-$

duce $110$ units in the next week. Otherwise, Ebony will con$-$

tinue at the previous week's production level.

Ebony currently has $60$ units of inventory on hand. Last

week's production level was $120.$

Questions

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?

Run the simulation for $500$ iterations to estimate the av$-$

erage $52-$week cost with values of $U$ ranging from $30$ to

$80$ in increments of $10.$ Keep $L=30$ throughout.

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?$

What other production policies might be useful to

investigate?