From the course: Excel Supply Chain Analysis: Solving Inventory Problems

Challenge: Analyze a back order problem - Microsoft Excel Tutorial

From the course: Excel Supply Chain Analysis: Solving Inventory Problems

Challenge: Analyze a back order problem

“

- [Narrator] In this movie, I would like to present a practice scenario so that you can solve a back order problem using the techniques that you learned in chapter two. For a back order problem, we assume that your company wants to allow orders for out of stock items. Some customers will choose not to buy and that will result in a penalty. Therefore, we want to calculate the base economic order quantity as if you were not allowing back orders. Then calculate something, the critical ratio, which I will describe in my solution. And of course was mentioned in chapter two. And from there we can combine all that information to calculate the EOQ with back orders. So we'll have three steps. The first is to create a plan. You should review the techniques taught in chapter two if you don't remember how to proceed. Secondly, do the practice and complete the worksheet and equations on your own. Then watch the solution video to see how I would do it. And after you have the solution, play around. Play with different demands and costs to generate scenarios and see which combinations lead to interesting results. With that in mind, I'll switch over to Excel and introduce the file that you will use. The sample file that you will use is 0504 back order EOQ, and you can find that in the chapter five folder of the exercise files collection. And here I have all the information that you need to do your calculations. I've provided the setup cost, the flow rate, that's demand, the item cost, and also the inventory percentage. And from there, I have also indicated the cost of shortage. And that is the cost associated with a customer deciding not to place a back order. So it's the lost opportunity for the business. And at the bottom I have described the equations that you will use for the EOQ, that's Q*. Then for the critical ratio. And we're actually taking the inverse of the critical ratio. And then finally, the economic order quantity, assuming that you are allowing back orders. And that will be Q* multiplied by the square root of one over the critical ratio. With that information in place, go ahead and try to solve the exercise. And after you're done, come back and watch the next movie to see how I would do it.

Contents