Explain it with IQR !

Fast JobAre you constantly tasked to reduce inventory? Having difficulty explaining proper inventory levels to higher ups that only see dollars? Is it to the point whereas you’re reducing the good inventory along with the bad? It’s time to adopt Inventory Quality Ratio (IQR) approach to your inventory management toolbox.

Inventory Quality Ratio is a very effective tool that properly classifies inventory into strategic categories which aid in focused inventory reduction activities. In other words it separates good (needed moving) from bad (obsolete and slow moving) inventory. It will allow inventory managers to explain what inventory is doing not just how much it’s worth, which alone mean very little.

Compare this to the correlation of Accounting and Finance. These fields complement one another; just know the result of one without the other dilutes the results. Accounting tells you were the money is at while finance tells you what it’s doing. Think of Inventory Quality Ratio as the finance quotient of your inventory management.

Used in conjunction with inventory turns and COGS tools you should have all the needed weapons in your inventory toolbox.

This is how IQR works.

  1. Divide your inventory into four quality categories.
    1. Active– Items with future requirements and current usage.
    2. Slow Moving– Items with future requirements but no usage for 6 months.
    3. Excess – Items with no demand and no usage for 6 months.
    4. Obsolete – Items with no demand and no usage 12 months.

Using your current ERP system find the dollar amount for each category. A perfect IQR would be 100% however most manufacturing companies start around 40-45% range. IQR will allow you to drill down to the specific item and update order policies if needed.

IQR is quite simply is the percentage of your active inventory.

This is an excellent tool separate good inventory from bad. Looking at total inventory dollars alone doesn’t tell the full story. Plus this is actually a detriment as declining orders or market share would actually be a plus in companies where inventory dollars alone are measured.

Below is an example of two warehouses with 200K of inventory. IQR clearly identifies, which warehouse has better managed inventory and where to look for reductions.

Warehouse 1
Active Inventory – 80K (40%)
Slow Moving Inventory – 30K (15%)
No Moving Inventory – 35K (17.5%)
Obsolete Inventory – 55K (27.5%)
Total Inventory- 200K
IQR Percentage: – 40%

Warehouse 2
Active Inventory – 75K (37.5%)
Slow Moving Inventory – 45K (22.5%)
No Moving Inventory – 60K (30%)
Obsolete Inventory – 20K (10%)
Total Inventory –200K
IQR Percentage: – 37.5%

IQR again is simply the percentage of active inventory in the total. While it’s clear Warehouse 1 has more active inventory they clearly need to work on removing the obsolete inventory. Warehouse 1 for instance may take more discrete orders which generally generates (production overrun) obsolete inventory.

Warehouse 2 possibly needs to start monitoring “No Moving” inventory levels more closely now to reduce the amount that falls into obsolete.

Reading tea leafs with IQR in place becomes a much simpler process.

Additionally each month lean practitioners should look for trends in each category to determine if your organization is on correct path.

As demonstrated IQR (Inventory Quality Ratio) is a powerful tool that allows you to not only monitor inventory but also explain it!

This entry was posted in Inventory Drivers and tagged , , , , , , , , , . Bookmark the permalink.

11 Responses to Explain it with IQR !

  1. Nuno says:

    Hi, is there any excel file as a pratical example of how to make this kind of control on stocks ?
    Thank you.

    • David - LeanPlanet.org says:

      I don’t know of any available spreadsheets as examples. I’m sure there are plenty of expensive software on the market. However once the information is in your ERP system it should be easy to export data and create your own spreadsheet using pivot tables. Also remember this won’t control stock it only identifies how the stock is performing. Stock is controlled by adjusting the products production profile to mirror the current market environment.

  2. Simon T says:

    the theory refers to inventory & target stocking levels – so for a particular forecasted SKU that is overstocked, do you consider the quantity upto the target stocking level as active and the remainder above the stocking level as excess? Or is it all classed as excess?

    Can a particular SKU have inventory in more than one category?

    • David - LeanPlanet.org says:

      Yes you can have a SKU in more than one category that is exactly how you differentiate between other SKU’s. Each SKU typically has inventory associated with multiple categories just like in the example. The example just rolled up all categories of each SKU stocked within the warehouse. Think about Planners of warehouse 2 where do they start to find issues? They simply look at IQR at the SKU level of inventory in warehouse! Which SKU has lowest IQR ratios and address those first!

      What if you had 500 widgets in stock 50 (10% Active) due to ship immediately, 350 (70% Slow Moving) due to ship in 7 months and the balance 100pcs (20% Obsolete) no demand over 12 months. Now simply take cost of the widgets in the active category and divided by the total inventory of all 500 widgets and you will have your IQR percentage. If you had parts in WIP they would need to be included in total. Also you can define each category parameter to suit your business. Hope this helped … good question.

      • David - LeanPlanet.org says:

        Additionally most inventory is calculated by batch or work order. In other words if the 500pcs were put in stock in two batches your report on the 500 widgets might just fall in only two categories. Not exact but it would give you direction. Software could be programmed to be more exact but I wanted to point out real world. Another good reason to drive for smaller lot sizes.

  3. I need to correct the definitions of the IQR categories and provide a little more detail about how they are determined. According to http://www.inventoryperformance.com, the IQR categories are:
    – Active: SKU has future demand and/or recent past usage, and the balance on hand is within user defined limits.
    – Excess: SKU has future demand and/or recent past usage, but the balance on hand is over user defined limits.
    – Slow Moving: no current demand and no past usage in over 6 months, but there is past usage within the last 12 months.
    – No Moving: no demand, and no usage in more than 12 months

    The user defined limits in IQR are defined as rules. You set a rule for each class of inventory, class being the A-B-C code of the SKU. For example, you might set rules like:
    – Class A items can have up to 4 weeks worth of inventory (based on either future demand and/or past usage) and be considered Active
    – Class B items can have 13 weeks worth of inventory (based on either future demand and/or past usage) and be considered Active
    – Class C items can have 26 weeks worth of inventory (based on either future demand and/or past usage) and be considered Active

    The exact weeks that determine if a SKU is considered Active is set by the company doing the IQR analysis. Companies usually set the rules based on their their demand patterns and their desire to control the level of working capital. Obviously the tighter the rules, the less working capital tied up in inventory.

    This means the answer to the question, “do you consider the quantity up to the target stocking level as active and the remainder above the stocking level as excess?”, is Yes. A single SKU can have part of its balance on hand categorized Active because it is within the rule, and part of its on hand balance categorized as Excess because the balance is over the rule. But a SKU cannot be considered both Active and Slow Move. Slow Move and No Move are based on how long since the part was last used anywhere so the entire SKU falls into one of those two categories.

    I hope this helps.

    • David - LeanPlanet.org says:

      Thanks for your input!

    • Simon T says:

      That is much more clear explanation than on http://www.inventoryperformance.com. I have been working with our data here for a few weeks and kept chopping and changing between keeping the SKU in a single bucket or multiple buckets. Multiple buckets makes more sense as for a specific SKU you are looking at where the inventory is positioned. Some of it must be positioned correctly, but is there too much or too little? Solving the multi bucket question is the easy part (thanks for your help on this!!) – now the fun begins digging into the detail to find out why i have so much inventory in E2.

  4. Simon T says:

    Still pushing numbers through the model and I keeping coming back to question why is A1 classed as active inventory? I have no forecast & my last use was within the last 6 months. If I was phasing out a part then I would be aiming to only have inventory in the A1 bucket and that it would be at a minimum. I would not class it as ‘active’. Similarly while the inventory in E2 is excess it is going to be consumed as there is forecast. Therefore I am thinking that the IQR would better be represented as (A2 + E2)/total inventory as A2 and E2 will be consumed. I admit it depends on how overstated is the E2 inventory. What are you thoughts on this?

  5. David - LeanPlanet.org says:

    Example Table
    Active- Items with future requirements and current usage.
    Slow Moving- Items with future requirements but no usage for 6 months.
    Excess – Items with no demand and no usage for 6 months.
    Obsolete – Items with no demand and no usage 12 months.

    I have no forecast & my last use was within the last 6 months.
    This would be excess in my model, it would sit additional 6 months if no new orders arrive it would move to Obsolete.

    If I was phasing out a part then I would be aiming to only have inventory in the A1 bucket and that it would be at a minimum.

    The whole goal is to get as much inventory as possible in the A1 category. Remember we’re trying to segregate good inventory dollars from bad. But unless you considered total inventory (per part) you cannot get a true percentage of of active. In regard to your suggested calculations I think monitoring inventory turns would get you what you need. Your turns should correlate your IQR.

    Beware of large batch (one lot) ordering and processing. This can fool IQR until processing is completed and parts put into stock. Catch this with MRP at order entry make adjustments up front.

  6. Simon – Sorry for the late reply.
    A1 is active because in some environments some parts do not have requirements, some parts are not managed by MRP. Parts maybe managed by min/max or reorder point strategies. IQR is flexible enough to take that into account.

    Your right, A2 is perfect inventory because it has near term future requirements but A1 is still active. If you want to give less weight to your A1, give yourself a shorter rule for A1. For example, if you set your rule for items with future requirements to 4 weeks then you could set you rule for items with no future requirements to 2 weeks. Then your items would move from A1 to E1 quicker while you are allowing yourself a buffer on bringing in A2 inventory. The above description about the rules is an intentional oversimplification because the rules are really set by ABC code by the rationale is correct.

    E2 is excess because you have more than you need based on the rule you set for your about how many days of inventory you want to call active. Yes, you have requirements, but you hurt your cash flow by bringing it in too soon and you are now paying carrying costs.

    You want to look at E2 to do two things. 1. Make sure you not bringing in any more before you need it and further affect cash flow and carrying cost, and 2. To understand the root cause of why you brought it in too soon. Solve the root cause and you’ll possibly prevent more E2 in the future.

    I hope this helps.

Leave a Reply

Your email address will not be published. Required fields are marked *


8 − five =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>