1. On the Simulation1 worksheet, in column L, calculate the total demand by day for the 10 customers in cells L4:L243. Use this daily demand total data to calculate the overall mean, mode, median, and standard deviation (in this order) for the combined requirements of all 10 customers in cells P21:P24 respectively. Label the cells in O21:O24. Use cell shading to clearly identify your calculation area.
2. Generate a ranking of the data (1 to 240) in cells M4:M243 so that the day with the least total demand has a rank of 1 (HINT: Use the RANK.EQ function). Put rank in cell M3. Again, use cell shading to clearly identify columns with your calculations. ,
3. Create a listing of the top (cells P4:P8) and bottom (cells Q4:Q8) five daily combined demands from the simulated instances (HINT: Use the LARGE and SMALL formulas). Clearly identify this listing using borders and shading.
4. Recommend a target inventory level needed for a five-day period based on the following: to be on the cautious side, assume each days supply will be equal to the average daily demand for all locations combined, plus three standard deviations. Since the mean and standard deviations might not already be integers, round the daily demand up to the nearest whole number in your formula. Then, use this calculated daily demand (average plus 3 standard deviations) to calculate demand over a five-day period. Place this recommendation in cell P14, again clearly identifying it. Add the label Bearing Recommended to identify the cell containing the actual value.