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

Calculate the critical ratio - Microsoft Excel Tutorial

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

Start my 1-month free trial

Calculate the critical ratio

“

- [Narrator] When you sell items and are willing to allow back orders, you need to balance your inventory holding and your shortage costs. There's always a trade-off between inventory savings and lost sales when you allow back orders. If you're willing to sell items that you don't physically have, or at least offer them for sale, then you also have to accept that some customers will decide not to buy from you, maybe just for this one transaction, maybe for more transactions in the future. You can strike a balance between extra inventory costs and lost sales using what's called the critical ratio, and that is the share of shortage costs that are part of total costs, and again, your total cost are the cost of excess and the cost of shortage. This calculation is incorporated into your analysis in several different ways. Graphically, shortage costs versus holding costs look like this. We have our cost of shortage and our inventory cost, and then we have our critical ratio, which is calculated as the proportion of the shortage cost over the sum of shortage and inventory cost, and here's what it looks like in a table. We're assuming that the cost of excess or the annual inventory holding cost is $15, and over on the left, you see that we have the cost of shortage. Here, I have the cost from zero to five, and the critical ratio goes from zero all the way up to .25, where five plus 15 is 20, and the shortage cost of five is 25% of that. If you want to visualize the critical ratio graphically, you can again compare your cost of excess, which we're holding constant at $15 versus the cost of being short. So I have the cost of being short on the X axis and then the critical ratio on the left. The critical ratio should be equal to .5 when the cost of excess is the same as the cost of being short. And if you look on the graph, you'll see that the cost of being short of 15 has a Y intercept of 0.5, and that's because $15, which is the cost of being short divided by 15 plus 15, or 30, is .5. And again, this is a visual representation of how the critical ratio changes. You will use the critical ratio in various forms several times in this chapter.

Contents