How much inventory do you really need?

If you are following lean methodologies you will have encountered the concept of inventory as waste.   It’s something you have because you cannot instantly manufacture and deliver your product to a shopper when they want it, but not something that the shopper sees any value in.

I find that a very interesting idea as it challenges the reasons that you need inventory, and that’s definitely worthwhile.   However, many of these causes of inventory need more substantial changes in your supply chain (additional production capacity, shorter set-up times, multiple production locations) so as a first step, I suggest that you figure out what inventory your supply chain really needs and why.  Take out the truly wasted, unnecessary stock and then see what structural changes make sense.

Typically you can remove at least 10% of inventory while improving product availability. What’s that worth to you?  If that sounds a little aggressive, I can only say “been there, done that, got the coffee-mug”. (We didn’t do t-shirts).

I’m going to look at this from a manufacturer’s perspective and try to visualize for you why they need inventory and how to quantify the separate components.  Why does a manufacturer have inventory?  Let’s start with an easy one:

Cycle Stock is related to how often you add new inventory to the system.  Manufacturing lines typically make a number of different products, cycling through them on a reasonably consistent schedule.  “We make the blue-widgets around once a month”.  It may not be exactly a month apart and that’s not really important to us.  Once a month, in this case, a batch of blue widgets is made and added to inventory.  Over the course of the next month (or so) that inventory is consumed and inventory drops until we make another batch.  Over the course of 12 months it would look something like the example below.

Cycle Stock across time

Hopefully it’s not too hard to see that while “Cycle Stock” varies from 0 to about 30 days worth of demand, it will average out to about half-way between the peak and trough – roughly 15 days.  If you manufacture your product less frequently, say once every 2 months, Cycle Stock will peak at 60 days of demand and, on average, adds 30 days of inventory to your overall stock position.  If you manufacture your product once a week, Cycle Stock will peak at 7 days of demand and, on average, adds 3.5 days of inventory to your overall stock position.

If you want to reduce Cycle Stock you need to make your product more often.  That probably means reducing the time and lost production associated with line change-overs so changing more frequently is less painful.

Pipeline stock is slightly harder to explain but really easy to calculate.  Pipeline stock is inventory in your possession that is not available for immediate sale.  Good examples would be inventory that is in-transit, or awaiting release from quality testing: you own it but you can’t sell it yet.  Let’s say that from the point of manufacture it takes 3 days to move the product to your warehouse where it can be combined with other products to fulfill customer orders.  This has the effect of increasing your inventory by exactly … 3 days.  It really is that simple.  If you know how long inventory is yours but unavailable to meet demand, you know your Pipeline Stock.

If you want to reduce pipeline stock you need to reduce testing time post production, get your product to market faster even consider adding production capability nearer to your markets to reduce transportation time.

Inventory Build.  This is easy to describe but very difficult to model.   For products with large variations in sales volume across time (typically but not always due to seasonality) there may not be enough production capacity to manufacture everything you need just prior to the demand.    As long as the product can be stock-piled, the manufacturer just makes it earlier and holds it until its ready for sale.  If you want an example, think of Halloween Candy, it hasn’t really just been made in early October. 

So why is it so hard to calculate?  Well inventory models are typically built one product at a time but to know your production capacity availability you need to look at all products using shared resources and production-lines simultaneously and build a production plan that understands all your constraints and your planning policies.  Essentially, you need to build an entire (workable) production plan and that’s typically beyond the scope of an inventory modeling exercise.  Often the best place to get this is from your production planner.

If you want to reduce Inventory Build you may be able to do so by more effective production-planning , (Optimization models may be able to help here).  Alternatively you will need to add production capacity.

Safety Stock is the most complex part of the calculation but thankfully the math is not new and you can buy tools that do this for you.  You can’t make whatever you want whenever you want it (or you have little need for any inventory).  If I was to tell you now that we need another batch of “Red Doodas” it’s going to take some time to organize that.  Apart from purchasing raw and packaging materials you may need to break into the production schedule, reorganize line labor perhaps even organize overtime shifts.  You may say that you could that done in about 7 days by expediting, but you probably do not want to plan on having to expedite very much of your production.  So, think of something more reasonable, an estimate not too conservative but one that you could stick to most of the time…21 days ?  Let’s work with that and call it the “Replenishment Lead Time”.

Now, I want to set my safety stock so that it buffers me from most of the uncertainty I could encounter during the Replenishment Lead Time.  It seems highly unlikely that I will sell exactly what was forecast in the next 21 days.  If I sell less I am safe if unhappy.  If I sell more I need a little extra stock to help cover that possibility.  Similarly, even though I asked for 1000 “Red Doodas”, production does not always deliver what I asked for and sometimes it takes a little longer than it should too.  By measuring (or estimating) each of these sources of uncertainty and then combining them together we can get a picture of the total uncertainty you will face over the replenishment lead-time.  If we also know what level of uncertainty you want the safety stock to cover  we can calculate a safety stock level.

Typically the amount of uncertainty you wish it to cover is expressed in terms of the % of total demand that would be covered.  So, 99% means that safety stock would target fulfilling 99% of all product ordered.  The other 1% would, sadly, be lost  to back-orders; or future orders; or possibly lost completely.    As CPG case-fill rates (as measure of the proportion of cases fulfilled as ordered) are typically closer to 98%, 99% is actually rather high.

[Note: Don’t go asking for the safety stock to cover 100% of all uncertainty as this (theoretically at least) requires an infinite amount of safety stock]

Here’s our previous example with some additional variation (uncertainty) added in demand.  Safety stock has been set so that you should meet 99% of all demand from stock and production kicks off when we project inventory will drop below the safety stock level 30 days ahead.

Cycle and  Safety Stock across time

If there was no uncertainty the inventory would have a low point at exactly the safety stock level with production immediately afterwards.  Clearly actual sales did not turn out exactly as forecast.  Sometimes we sell less (and inventory is a little high when production kicks in).  Sometimes we sell more and sales start to use up the safety stock.

The safety stock level is intended to buffer most of this uncertainty, but as you can see, inventory does occasionally drop to 0 and (for very short periods of time) you would not have enough inventory to meet all orders.  On the days when this happens you will short a lot more than more than 1% of the ordered quantity but over time this would average out to about 1%.

If you want to reduce Safety Stock you have a few options.  Remember that they key inputs are:
·         Replenishment Lead-Time
·         Demand Uncertainty
·         Supply Uncertainty
·         % of uncertainty you want to cover.
If you can reduce any of these, your safety stock will come down.
I've embedded a simple inventory model below that you can use to experiment with the various inputs that drive your need for inventory.

Once you have set up the inputs appropriately for your business take a look at what a change to any of these inputs would do for total inventory.  What if you can:
·         improve Forecast Accuracy by 5 points;
·         reduce Replenishment Lead-Time by 1 week;
·         reduce you Cycle Time by 50%;
·         reduce Pipeline Length by 25% ?


Uncertainty of demand is typically measured by “forecast accuracy”.   There are some variations on the calculation of forecast accuracy but here I am using it as (1 – [Mean Absolute Percentage Error]) measured in monthly buckets.   [Mean Absolute Percentage Error] may seem a little scary, but it actually does exactly what it says, it’s the average, absolute error as a % of the forecast. (Absolute errors treat negative values as positive)

Forecast accuracy is typically measure in fixed periods that are relevant to you.  These may be close to but typically not the same as your Replenishment Lead-Time, so the model will try to estimate the value it needs from the standard metric.

If you are not already measuring your own forecast accuracy, you really do need to start.  A forecast with no sense of how accurate it is, is (relatively) useless.

Disclaimer:  This tool is a reasonable guide  and should give you a good sense of what is driving your need for inventory and what you might do to reduce it.  Ultimately though, its limited by the complexity I wanted to include in the Excel model it’s based off and of course it can only handle one product at a time.   Don't use it to build your inventory policies - invest in the real thing.


  1. Is this sample file no longer available?

  2. No, sorry. This is an old post and I guess the Zoho app is no longer supported.


Note: Only a member of this blog may post a comment.