Excel Safety Stock Formulas – Know What They Really Do

I like to read others’ opinions of common, popular safety stock formulas – how they are used, how useful they are, what are their shortcomings. I recommend Michel Baudin’s concise, insightful blog written in February, 2012 – Safety Stocks – Beware of Formulas.

In this post, I will expand on some of the excellent points he brought up.

Know what safety-stock formulas really doprobability of no stockouts: Most safety-stock formulas are intended to provide a probability of no stockouts. If you measure your actual service level as a quantity-based fill rate, your actual service level is not directly comparable to a probability of no stockouts. In other words, it takes more safety stock to support a 98% probability of no stockouts than it does to support a 98% quantity-based fill rate. If you base your safety stock on a probability of no stockouts, but you measure your actual service level as a quantity-based on-time-delivery fill rate, your safety stock is likely too high.

Know what safety-stock formulas really doreplenishment interval: Most formulas assume that replenishment interval equals lead time. Yet if these two are the same, it’s pure coincidence. Common sense tells you that when replenishment interval is very short, you need less safety stock. And likewise, you know that when your replenishment interval is very long, you need less safety stock. A formula is unlikely to accommodate that common sense, and guaranteed not to do it correctly.

Know what safety stock formulas really dono past-due backlog: Safety-stock formulas assume that unfulfilled demand is canceled, and does not become past-due backlog. If you have past-due backlog that isn’t canceled, but must be fulfilled even though it’s late, you know that this backlog is disruptive, and often causes other demand to be late. All other things being equal, past-due backlog requires more safety stock than does demand that’s canceled if it’s late.

Know what safety-stock formulas really dounrealistic normal distributions: Most formulas use normal-distribution math to represent demand (and lead time). Most actual demand distributions are not well-represented by a normal distribution (the same is often true of lead time), but are instead right-skewed. This is because demand has a lower bound of zero, but has no such upper bound. It is dangerous to conclude, or hope, that an Excel-friendly safety-stock calculation will get you “close enough.” It can be way off, compromising service level and/or inventory performance.

Non-normal distributions can present challenges to Excel: It’s not the best tool for determining the best right-skewed distribution fit, and it doesn’t determine distribution parameters for many non-normal distributions. Though Central Limit Theorem suggests that demand during a long enough lead-time period tends to have a normal distribution, you can’t rely on this for your demand and lead-time data.

Supply-chain optimization demands that we all use the best tools available to minimize stock levels while meeting customer expectations and achieving financial goals. Why use a tool that doesn’t measure service level the same way you do, excludes variables and factors that are important, and fits Excel better than it fits your data? Common, popular safety-stock formulas are not the best optimization tools, and they can put customer service, inventory performance and financial goals at risk.