Determine the reorder point based on cycle service level - Microsoft Excel Tutorial

- [Instructor] When you purchase goods for sale, your inventory will run through cycles. Specifically, they will run through replenishment cycles where you replace the articles that you've sold. You start with your base stock and then reorder when you need to. You can tell how much to purchase by calculating your economic order quantity but when should you place your order? Well, that depends on your demand and how willing you are to risk being out of stock. In this movie, I will show you how to calculate your reorder point for a given customer service level. My sample file is 04 04 CycleServiceLevel and you can find it in the chapter four folder of your exercise files collection. In this workbook, I have a single worksheet and on it, I have the information that you need to calculate your reorder point for a target cycle service level. We have information on the daily demand, that's in B3. So we sell 36 units of this particular item with a standard deviation of eight. It takes five days to get replacement products. So we need to cover that demand while the order's in place. And we have decided that we have an economic order quantity of 811 units. So it is the most cost efficient based on our demand for us to order 811. To calculate the reorder point for this particular product, we need to know several things. The first is the mean or average demand during our lead time. And that is the number of days of lead time multiplied by the average demand. So I'll go over to cell E3, type an equal sign, multiply B3 by B5 and we should get 180 and we do. So on average, we will need to cover 180 units for sale during the time it takes our order to come in. We also need to know the standard deviation of demand during our lead time. And that will allow us to pinpoint our reorder point. The standard deviation. In E4, type an equal sign is our daily demand, standard deviation from B4 before multiplied by the square root of our lead time, which is five days. And in cell B5. Type a right parentheses and enter. And we've got a standard deviation over those five days of 17.9. The cycle service level, which we'll go into cell E5 is a number that we type in directly. And this is the number of orders that we want to cover. Or if you think about it, is one minus the probability that we are willing to be out of stock of this particular item. So if we were willing to be out of stock 5.5% of the time that a customer wants to buy it, we would subtract .075 from one and get .925. So in cell E5, we'll type equal .925 and enter. Next, we can use that cycle service level to calculate our K value or our safety factor using the standard normal curve. The standard normal curve has a mean of zero and a standard deviation of one. And we're going to use .925 as our probability. So in other words, we want to know the value on the standard normal curve, where 92.5% of all values are to the left or less than that value. And again, that corresponds to our level of demand. So in cell E6, I'll type equal and we will use norm.s.inv which is the standard normal curve inverse, press tab. And our probability is in cell E5. Right parentheses and enter. And we get a safety factor or a K value of 1.44. Now we can calculate our reorder point by going to cell B9, type in equal sign and it will be our daily demand. And that will be our mean demand during lead time. Again, we need to cover our five days of lead time of 10. And to that, we will add our standard deviation which is in cell E4, multiplied by the safety factor in E6. Press enter and we get a reorder point for the cycle service level of 205.75 rounded up to 206. If we want to cover more of our orders for example, by going up 2.975 in cell E5 because we have our equations built in, all we need to do is type in the new value, press enter. And we see the level is 215.06. And if we want to go all the way up to 99%, so .99 and enter, and we get 222. So as you can see, we don't have a lot of variation in our reorder point based on different service levels but it's good to know the range in which they fall.