I wrapped up that post with a warning/disclaimer that the spreadsheet model was really too simple for real life use, but I didn't tell you why. Now here's the kicker: many packages appear to have the same problem and can cause you to severely underestimate your inventory needs and lose sales.
Just so you know, we are going to dip a toe into statistics here, but if you are a supply chain manger and you need to optimize your inventory usage, you need to know this - stick with it. I'll be gentle, it won't hurt, I promise.
The key problem is that models assume uncertainty in demand follows a Normal distribution. Something like this:
|"Bell-shaped curve" - a Normal distribution|
Let's take a simple example to see why this is a problem. Let's say your forecast for the next month is for 100 units. The normal distribution for your sales then would be centered at 100 units
You could sell more than 100 or less, but just how much more? What if this is a hard-to-forecast product that could sell much more or much less. Let's add in the rest of the horizontal axis.
Sticking with visual analysis for now, it looks as though you expect to sell around 100 units (your forecast) and you could sell as much as 300 (yeah !!) and as little as -100... excuse me ? How exactly are you going to sell -100? Despite the widespread practice of representing returns as negative sales (not a good idea) that is not what this means. This result is a physical impossibility that cannot ever happen in reality. We really need a distribution that understands that negative sales are not possible. Something like this:
The green line tells us that worst case sales are 0 (phew) but could go up to.. about 400 ? Now what I know that you can't tell visually is that both of these distributions have exactly the same average and the same variability (standard deviation), either one could be used to model the same level of forecast and demand uncertainty...but... the green one shows a realistic possibility of much higher sales, sales that you may want to protect by having extra safety stock. Set your safety stocks based off the Normal distribution and you will miss that peak demand when it does happen. How do you feel about cutting 100 units from total demand of 400? Using the Normal distribution here can seriously damage your wealth. (FYI - The green line in this case is from a LogNormal distribution. It's not the only option available to us but I'll hold that detail for another post.)
BTW - If you want to get into Inventory optimization, modeling this correctly will be much more effective in helping you balance inventory and fill-rates.
If it's dangerous then, why is the Normal distribution so heavily used in practice? Well, if your uncertainty around what you are going to sell is much smaller, it does a good job. The chart below shows the same Normal and LogNormal distributions for a product with much less demand uncertainty
The 2 distributions are practically the same, though the LogNormal still predicts slightly higher sales at the upper end - the end we are trying to protect with safety stock.
(It's also true that it's just easier to program the math to use the Normal distribution.)
In my experience, demand uncertainty is often, even normally (pun intended), big enough that using the Normal distribution will cause you to severely underestimate safety-stock and lead to more cut orders (and lost sales) than you planned for.
Does your inventory model have this problem?