Note: I have answer for this question in excel but now I need a detailed Solution in Ms word format. please solve this like how a numerical problem is to be solved on paper but here is to be in Ms word

question

answer in excel

Question No 2 - Gas Station A gas station near the M1 Motorway is revaluating its diesel fuel reorder point, i.e. the diesel fuel level in its main tank triggering a replenishment order. The daily demand faced by the station for diesel is normally distributed with an average of 8000 liters and a standard deviation of 2500 liters. After the manager places his order , he knows that he will receive it with an exact lead time of 3 days. (Assume that orders are made immediately after the reorder point is reached and that the demand on each day is independent from the other) Q1- What should the reorder point be set to in order to ensure that the stations chance of a stock out during the replenishment cycle is limited to a probability of 2%? Q2- The manager of the gas station is worried she may have underestimated the variability of demand. Assuming she implements the re-order point from the previous part, what will be the stockout probability if the standard deviation of demand is , in fact 3000 litres ? A B 1 2 3 Formula 4 5 6 7 Q.1 Gas Station Average Daily Demand 8000 standard deviation in daily demand 2500 Lead Time 3 Stockout Probability 2% Service level 98.00% z-value 2.054 standard deviation in demand during lead time 4330.127019 Safety Stock 8892.99 Reorder Point 32892.99 8 9 1-Stockout Probability Norm.s.inv(service level) standard deviation in daily demand * sqrt(Lead time) standard deviation of demand during lead time * z-value Daily Demand * (Lead Time) + Safety Stock 10 11 12 13 14 15 16 17 Formula Gas Station 8000 3000 3 18 Q.2 Average Daily Demand standard deviation in daily demand Lead Time Reorder Point Safety Stock standard deviation in demand during lead time z-value Service level Stockout Probability 19 20 Reorder Point - (Average Daily Demand * Lead Time) standard deviation in daily demand * sqrt(Lead time) 32892.99 8892.99 5196.15 1.711 95.65% 4.35% 21 22 23 24 NORM.S.DIST(z, TRUE) 1-Service Level