Solution: The back order problem - Microsoft Excel Tutorial

- [Instructor] In the previous movie, I presented a challenge to calculate the economic order quantity when your store allows back orders. And in this movie, I will use the techniques from chapter two to solve that problem. I'll start by calculating the holding cost and that is the amount that it costs to retain an item in inventory for a year. And I know it's a year because my flow rate or demand is in units per year. So the holding costs will be equal. The item cost, which is in B5, multiplied by the inventory percentage, which is in B6, and enter. That gives us a holding cost of $22.05. Now for the economic order quantity, I can translate the formula here for Q* into the EOQ. So in B8, which I've already selected, I'll type equal and we're taking the square root of the quantity under the radical sign. And that will be two multiplied by the cost of setup. And that is the cost of placing an order, also multiplied by the demand that's in B4. And we'll divide that by the holding cost, which is in B7. There we are. Right parentheses and enter. And we get an EOQ of 162.88, or in this case, we would round up to 163. Now we can shift over to the right side where we have the cost of shortage, which is $15. And this is your best guess or estimate of how much each lost order cost you. So if an item cost is $147, then you're assuming that approximately one out of 10 people will decide not to place a back order and will buy elsewhere. Next, in E4, we have the cost of excess, and that's just your holding cost. That is the amount that it takes to hold on to an item in inventory. So in E4, I'll type equal, and the holding cost is already calculated in B7. So enter and 22.05. Now we need to calculate the inverse of the critical ratio, and that is given here as one over CR. So in cell E5, I'll type equal. And then in parentheses, we have the cost of shortage, which is in E3. And then we will add that to the cost of excess, which is in E4. Then close the parentheses and divide that by the cost of shortage, which again is in E3. That looks good, so I'll press enter and we've got the inverse of the critical ratio as 2.47. And now we can use the bottom formula to find the economic order quantity, assuming we allow back orders by multiplying Q*, which is the original EOQ by the square root of one over the critical ratio. So let's go ahead and do that. In E6, I'll type equal, and the original EOQ is in B8, and we will multiply that by the square root of one over the critical ratio, which we calculated in E5. So we don't need to put one over it. We've already calculated the inverse. So that value is in E5. Then right parentheses and enter, and we get an economic order quantity of 255.99. So basically 256. And I encourage you to play around with the numbers and there's something that I'd like to do here to show you why you might want to change things. As I pointed out, the cost of shortage is very small compared to the item cost. You're assuming, as I mentioned, that only about one out of 10 people will not place a back order, and that's probably not realistic. Let's say instead that 40% or so would not place a back order, they would go somewhere else. So that would be about 70, about $67, let's say that. So if 40% of people wouldn't order, then we would have in E3 a cost of shortage of $47 and enter. And you can see there that because of the higher cost of shortage that the inverse of the critical ratio is much smaller and that you need to order a quantity that is much closer to the original economic order quantity, so you don't lose money from back orders that you could have captured.