The more manufacturing and distribution companies I visit, the more I find that they all have the same basic issue: they have too much inventory overall, but not enough of certain items to keep customers happy. Here’s how you can solve this with the right tools and approach.

It’s likely that your company is tracking the financial value of inventory by warehouse, or at item level. But the value is only about a third of the overall picture. You need to be able to see what’s going on at item level because that’s where the problems and solution lie. Let’s talk about what you need to see and measure.

Consumption: You cannot determine an appropriate stocking level for an item unless you understand its consumption patterns. That means that you need to know what quantity is consumed each day in each location. ERP systems have a lot of transaction types going on and not all of them are consumption. Filter out stock transfers but keep things like warranty sales and count adjustments.

On-Hand Quantity: This is a very important measurement and you need to gather it daily for each stocking location. It’s the most important way to quickly be able to visualize whether you’re carrying too much or too little inventory. Stock-outs can happen for a single day, so this must be tracked daily.

Lead Time: The amount of time it takes to place and receive an order can have a significant affect on inventory level. You should have a lead time assigned to each item, whether you buy it or produce it.


Once you have this information, it’s time to draw a picture. Below is a tool Marquis provides to clients to help them determine the best ERP settings. Let’s walk through the visualization and see what it tells us.

Tableau Visualization


  • The green area is on-hand quantity. At a glance, it looks a bit high doesn’t it?
  • The small heartbeat-looking line at the bottom is consumption. This looks like a staple item, not something that has a lot of surprises.
  • The line bouncing around between 150 and 300 is Daily Average within Lead Time (DALT). It shows what you consume within the time it takes to obtain more. If you have zero on order and your on-hand quantity comes down to DALT, you’d better order some or you’ll dig into your safety stock.
  • Safety Stock is calculated based on the uncertainty in supply and consumption, which are adjustable as part of the analysis tool. If the vendor is very unreliable or consumption varies widely, safety stock needs to be increased.
  • Reorder Point is calculated based on DALT and safety stock. This is generally entered into the ERP system to use in its algorithms. However, this is where your eyes looking at the chart add significant value. The DALT trend is clearly downward but the reorder point is being calculated across the whole chart. Before changing ERP settings, you should change the settings to look at just October forward to get a more appropriate (lower) set of values.


I provided this tool to a client and he said, “this is really great but the work of going through all our items one-by-one is going pretty slow.” It’s absolutely necessary to look at the details item-by-item to optimize your settings, since all action is taken at item level in the ERP system. But even after you set things correctly, consumption patterns will change over time and it’s impractical to just go look at all your items periodically. The solution is inventory turns.

If you’re not familiar with inventory turn rate, there are a lot of good explanations on the web. The basic idea is that we calculate the number of times you’ll sell through your inventory in a year, as a way to find overstocked items. If an item has a turn rate of 4.0, you’re going to consume it within 3 months of receiving it.

When calculating turn rate, you need to choose the period of consumption to consider. One of our clients said that many of their items have noticeably declining usage so we decided to use the past three-months of history, rather than a longer window. Then we built a picture like below:

Inventory Turns

This type of chart shows higher inventory levels as larger boxes. In the top view, orange items are turning much slower than our target of 2.0. A “good” turn rate varies by industry and item type, so we made this adjustable. In the bottom view we see the trend of the last three months versus six months. Orange indicates a trend toward slower turns – all that orange says the client was right about usage slowing down.

With this sort of visibility, you can target your efforts towards items marked with large orange boxes to have the most impact.


Even with the use of these two approaches in place, you still need to add a bit more to make the system complete.

  • Designate the most important items “Tier 1” and track your progress in managing them.
  • Implement a stock-out tracking system to make sure that decreased inventory levels are not having a negative effect on service levels.

If you analyze your data in the correct way, you can take all the guesswork out of inventory management by letting the numbers drive your decisions. If you need help getting started, reach out to Marquis Leadership today!


Chris Beck is the Principal Consultant for BI Solutions at Marquis Data.