The following figure shows an Excel spreadsheet that calculates the benefit of pooling safety stock. Specically, the

The following figure shows an Excel spreadsheet that calculates the benefit of pooling safety stock. Specifically, the sheet calculates how much could be saved in annual holding costs if the safety stocks for three locations were held in a single location. Re-create this spreadsheet in Excel. You should develop the spreadsheet so that the results will be recalculated if any of the values in the highlighted cells are changed. Your formatting does not have to be exactly the same, but the numbers should be. (As a test,see what happens if you change Location 1's average daily demand and variance of daily demand to 100 and 15, respectively. Your new pooled safety stock should be 30.34, and the total savings due to pooling safety stock should be $108.21.)

Transcribed Image Text:

A B. E 1 Calculating Savings Due to Pooling Safety Stock 2. 3 Annual holding cost per unit: 4 Lead time (fixed): 5 z value (for desired service level): $5.00 8 days 2.33 6. Average demand during lead time 400.00 8. Average Variance of Reorder daily demand daily demand point 413.98 336.41 Safety stock 13.98 9. 10 Location 1 50 4.5 40 6.2 320.00 Location 2 Location 3 11 16.41 12 30 5 254.74 240.00 14.74 13 Total units: 45.13 14 Total annual holding cost: $225.63 15 Average demand during lead time 16 Variance of Reorder Average daily demand daily demand point 17 18 Safety stock 19 Pooled SS 120 15.7 986.11 960.00 26.11 20 Total annual holding cost: $130.56 21 22 Savings due to pooling safety stock: $95.07