Question

At a convenience store, demand during leadtime for bottles of Gatorade is normal with a mean of 50 and standard deviation of 15. How many additional bottles of Gatorade must be on-hand when placing the order so that the service level is increased from 50% to 80%? Note: Round your Z to 2 decimal places. Round up your final answer to the next nearest integer.

Answer 1

Solution 1

Given data: D = 50, $\sigma$ = 15 & Service level = 50% and 90%

Solution:

In the given problem, we have to calculate the Reorder point for both the cases i.e when Service level is 50% and when it is increased to 90%, then we can know about the additional inventory required to increase the service level to 90%.

So, reorder point (ROP) = Average Demand during lead time+ Safety stock

And Safety stock = Z* $\sigma$

Thus, when Service level = 50%, then Z = NORMSINV( 0.5) = 0.0 and thus safety stock = 0*15 = 0.

So, ROP = 50

And, when Service level = 90%, then Z = NORMSINV( 0.9) = 1.29 and thus safety stock = 1.29*15 = 19.35 ~20

So, ROP = 50+20 = 70.

So, in order to increase the service level to 90%, 20 Additional bottles of Gatorade has to be available on hand while placing the order.

Solution 2:

Given data: Demand = 200, $\sigma$ = 50, SL = 95%

Solution:

In this problem, we need to find out the Reorder point of the eggs so that the service level remains atleast 95%.

Thus, Safety stock = Z* $\sigma$ = NORMSINV(0.95)*50 = 1.64*50 = 82

So, the shop should stock at least = 200+82 = 282 baskets of Easter Eggs to achieve the desired service level of more than 95%.

Note: NORMSINV() is an excel function to determine the Z-score corresponding to a given service level. This value can also be obtained by using Z-Tables.