How-to: reduce inventory with safety lead-times (part 2)

Part 2 with Excel simulation on how to reduce inventory with safety lead-times

If you haven’t read ‘Part 1: how to reduce inventory with safety lead-times’ it is a short analogy on how electronic board production is like organizing a meeting between Elon Musk and 9 young new interns. Please read if you have any difficulties to understand or even to explain to colleagues why safety lead-times are mandatory in reducing inventory globally.

In part 2, here is  a simple Excel simulation to capture how efficient it can be.

* * *

Overview: reduce inventory

Here are the results of the simulation:

Security lead time simulation

Now, let’s take a deep dive into the file 🙂

Hypothesis: safety lead-times

Here is a real, classic example of a Bill Of Material (BOM)  with 390 components, 131 articles, for a value of $66.68.

As usual, 50% of the cheapest articles only make up a few percent of the value:

Security lead time simulation

We’ve then created a simple law of probability for a component shipment to be delayed:

Security lead time simulation

And have run 15 simulations with/without safety lead-times:reduce inventory simulation


A new simulation is launched every time you open the file, thus changing the results. But you can see that safety lead-times have — on average — a positive output by actually decreasing your inventory:

Safety lead-time simulation

Of course this simulation is a really simple example. Risk of shortage should not be the same for all the components. Optimized safety lead-times do not have to be of 30 days. But the goal here was to easily grab the interest of safety lead-times.

What you would need to take the analysis one step further:

  • calculate a law of probability article by article (taking into account last communicated manufacturer lead-times, last market available inventories, alternative manufacturing part number availabilities, mpn commodity status, mpn life cycle status, etc…)
  • describe BOM by BOM what is to be optimized (inventory? on time production?)
  • calculate BOM by BOM the optimized number of safety days for each article (for example, you can randomly choose safety lead-times and run simulations to calculate their efficiency, then do it again and again and again to keep the best random selection 🙂

Now, it’s your turn to play! No matter the complexity and accuracy of your calculation method, you’ll see very tangible results. And don’t forget the Elon Musk metaphor to convince your company!

Want to see Precogs in action?

Contact us to find out how Precogs lowers costs
while improving purchase processing and efficiency.