This is an optimization question. For this problem you will create a worksheet within the same spreadsheet file to solve the problems (Monte Carlos concept could help):

INVENTORY PLANNING -- DEMAND ANALYSIS Jessica Williams, manager of appliances for the Cincinnati Area XXX Department Stores, feels that her inventory levels of a popular TV model has running high; she's unsure if such levels are necessary. Each day she checks inventory and, if there are less than 30, she orders 20 (this policy was present when she started her job). However, before revising her inventory policy and actions, she decided to gather some data and conduct a first analysis. She reviewed a sample of 300 days over the last year or so & recorded the number of this product demanded each day. She believes that these 300 days are typical of this TV model's current demand for her stores. Number demanded of This TV Model 1 2. 3 4 5 6 7 # Days that number 4 was demanded 51 65 95 60 20 5 300 There were no stockouts in these 300 days. Jessica says that these historical data results can be used to estimate the discrete probability distribution of daily demand. For example, the estimated probability customers demanding 2 of this TV in a day would be 51/300 = 0.170. The lead time for replenishing this TV model inventory is 5 working days (time between placing an order and TVs arriving). Thus, Jessica wants to develop a better understanding of "# of TVs demanded in a 5 day period." Specifically, she would like to know the average, the 85th percentile, and the 95th percentile of #tvs demanded in 5 days and also the likelihood of a stockout -- if there are 24 in inventory to begin a 5 day period. (Stockout is the condition of no products in inventory when a customer requests one. If inventory is X, a stockout occurs if the demand is more than X) Develop a simulation to model the demand for a 5 day period (sum of the demand for 5 independent days). Create 2500 trials of the simulation of the 5 day demand. Question 6: What is the average 5 day demand based on the 2500 trials in your simulation? From the simulation results, what is the 85th percentile for the 5-day period? From your simulation what is the 95th percentile? From your simulation, what is the probability that the 5-day demand is greater than 24? Re-run your simulation where daily demand is normally distributed with a mean of 8.5 and a standard deviation of 2.1. Run 2500 trials with this demand profile. What is the average demand over a 6 day period