Question $4(40\%)$ EXCEL Spreadsheet Simulation

Consider an example about $($s$,$ S$)$ policy inventory system$$ simulation in the $3$rd lecture note. Simulate

and analyze an inventory system for $25$ periods $($i $=0,...,25)$ to decide how many items it should have in

inventory every day. Assume that there are daily demands and the inventory is reviewed every days $($No

randomness in order arrival time$).$ New order is placed following $($s$,$S$)$ policy. General parameters are given

in Figure $1.$ The random variables are given in Figure $2.$ Solution sheet example is represented at Figure $3.$

$($a$)$ Make an EXCEL simulation sheet refer to Figure $3($Hint: Refer to the lecture note $4$ for generating random demand; Use IF or COUNTIF functions in the EXCEL$)$

$($b$)$ Calculate an average total cost and a standard deviation for an entire periods

$($c$)$ Repeat your simulation $10$ times with different $($s$,$ S$)$ according to your own intuition and draw a plot:

trial index vs$.$ average cost for each trial. Figure out the best $($s$,$S$)$ across your trials.