Calculate the base stock required for a target level of service - Microsoft Excel Tutorial

- [Narrator] One of the hardest decisions in business is knowing how much inventory to keep on hand. You're always at risk of running out or stocking out if demand increases unexpectedly, but you can guard against it by buying some safety stock. In this movie, I will show you how to calculate the base stock level for a product where you have some data about its demand. My sample file is 0402 based stock, and you can find it in the chapter four folder of the exercise files collection. This workbooks worksheet contains the data that you need to calculate your base stock. If you look over on the left, you'll see that you have information about demand for a particular product. In B3, you have the daily average or mean of demand of 36, and that you found over time that the standard deviation is eight. And if you want to reorder, you can get your goods after five days, so that is the lead time. It costs you $2 to hold one of these products and inventory, and you figure based on past estimates that you will lose $15 of other sales if you don't have this particular product in stock. With that information in mind we can calculate the other information that we need to calculate the base stock level. First, we need to realize that we want to cover our demand on average, during the lead time. In other words, it will take us five days to get our goods and we have a daily demand of 36 units. So, we multiply five by 36 to get our mean demand during the lead time. So, go to cell E3 type equal, and that would be the mean demand, and B3 times the lead time, five days, and Enter, and we got 180. Next, we need to calculate the standard deviation of demand at during our lead time. And this is a little bit different than at calculating the mean demand. We have our standard deviation for each day of eight, but we need to multiply it by the square root of our lead time in this case five days. To perform that calculation E4 I'll type an equal sign, and we're multiplying the daily demand standard deviation, and before by the square root of our lead time and days, which is five that's in cell B5, right parentheses and Enter, and we have standard deviation during our lead time of 17.9. So, we have mean demand for those five days of 180. And the standard deviation during those same five days would be 17.9. Next, we need to calculate our level of service. And that in this case will be the critical ratio. We've seen the critical ratio earlier in other chapters, so I'll just calculate it here. In cell E5 I'll type equal, and it is the cost of shortage, which is in B7 divided by the sum of the cost of access and the cost of shortage. So, that would be B6 plus B7 type parentheses and Enter, and we get a critical ratio of 0.882. So, what do we do with that level of service or a critical ratio? We use it to calculate a safety factor and the safety factor is the value represented by the critical ratio on the standard normal curve, and that has a mean of zero and a standard deviation of one. So, we can use the standard normal inverse function to find our target value. So, in cell E6, so I'll type equal and the functionally is as NORM.S.INV. Again, we're taking the inverse, so all we need is the probability and that probability is in cell E5 our critical ratio, write parentheses and Enter. And we get a safety factor of 1.187. In statistical terms, that's equivalent to being 1.187 standard deviations above the mean. And now we can calculate our base stock. Remember that we want to cover our lead time and the demand is in cell E3. So, I'll click cell B9 type an equal sign, and then click E3, and so that we will add our safety stock. So, plus and we're multiplying the standard deviation of demand during lead time, by our K factor, which is in cell E6 and press Enter, and we get a base stock of 201.23 items rounded up to 202.