Inventory Analysis Tool

Here is a tool that can help analyse your inventory. It is a follow up from our DDMRP Webinar (view the recording here).

In this webinar, we talked about the profile of inventory in terms of cover – how many items we have in which categories of cover. Have a look at this report:

This is a key output of the tool: It shows that there are a large number of items with a relatively small amount of cover and another bulge with a large amount of cover. The amount of inventory you should be holding depends on a number of factors: Lead-time, variability, desired service level, commonality and so on.

However, if your inventory profile looks something like this, it is an indicator that there may be too much of the wrong items and little of the right ones. If there are items which swing from under-stock to over-stock and back over time, then it is definitely a sign that the stock levels are not in control.

Getting Started with the Inventory Analysis Tool

You will need the following input data:

• Demand. This is a history, usually of sales orders, that the tool can use to calculate a day of demand.
• Inventory. Your on-hand stock.
• Bill of Material
  • BOM. A flat Bill of Materials OR
  • Product BOM. A multilevel Bill of Materials.
• Items. An Item Master. This is needed for the Lead-Time of the component.

Setting up the Inventory Analysis Tool For the First Time

1. Access the tool by completing the form below. You will see a download link. Save the zip file to your computer.

1. Unzip the download directly to your C Drive. You should have a folder called C:\Inventory_Analysis_v02\Data.
2. Open the workbook Inventory_Analysis_v02.xlsm.
3. If you haven’t run macros before, you will need to change the Trust Center settings for Excel 2007+.
4. Run the tool from the “Update from Local” button on the menu sheet.

The tool has example data in it. Here are three ways you can get your own data into the tool:

1. Cut and Paste. Paste your data on-top of the example data. Then hit the “Output” button on each of the sheets. Make sure the columns are in the same place and the data are in a continuous table with no empty rows.
2. Text File Import. The tool pulls data from the folder location indicated by FilePath Data Local. If you change this location, you will need to user the “Update Text File Locations” button. Ensure this is a proper file path and if you are sharing the tool then use a folder directly located in the C Drive. The files should be tab delimited text files with the headings in the first row.
3. ODBC or OLE data connection. If you have tables that match the headings in a database then you can go into each Query sheet (in green) and update the query.

Running the Inventory Analysis Tool

Here is an overview of the logic:

1. Checks to see which BOM to use. If you have a Product BOM (compiled manually or with our BOM_Processor tool) then it will use that. Otherwise, it will take from the BOM table.
2. Filters the Demand table for records within the Horizon. The setting “ADU Horizon” is the period over which the average daily demand is calculated. A higher number means a longer history and more calculations. Start with 30-60 days. This is a simple definition of daily demand and it will suffice for the purpose of this simple analysis.
3. Explodes the Demand with the Bill of Material.
4. Calculates the Average Daily Usage (ADU) and variability of demand (Standard Deviation/mean)
5. Calculates the amount of inventory cover in days. Puts them into categories.
6. Generates the Inventory Profile Items Report as above.
7. Generates the Inventory Variability Report as below.

Making Sense of the Results and Taking Action

Firstly, take a look at the inventory profile by item. Is it a “bi-modal” distribution that has two peaks? Is there good reason for that? Perhaps you have a cluster of low lead-time items and another of high lead-time items. Or low variability and high variability? If not then perhaps the inventory should look at a bit more like this:

The Inventory Variability report goes further to help you identify which items are targets for reduction. In each category, you can now drill down and see which low variability and low lead-time items have a cover that is far bigger than might be justified. And which items with a low cover have high variability and long lead-times- these are the ones that can do with better cover.

Of course, your experience will tell you much of these results. The results would perhaps be not a complete surprise. The first task is to check that the tool and your data are making sense. Do the data represent your reality? Are they complete or are missing records causing errors in the tool?

Improving Performance – Get a Better Service Level to the Customer with Lower Inventory

DDMRP provides a data-driven way to position inventory in the places where it performs the best. Rather than relying on a forecast, the methodology uses actual demand to generate replenishment and places strategic inventory buffers in the places where they will do the most to provide a higher level of service to the customer.

Production-Scheduling.com has a DDMRP System in Excel. You can design and deploy DDMRP without spending the $50,000 and up that a software system would cost. We are offering a DDMRP Starter Kit here and it is currently on offer to early members for half price. Click here to find out more and get started with putting DDMRP to work in your company.