Inventory Analysis- Warehouse/Distribution Case Study

Import & Distribution of Machinery Parts

(Hover to enlarge)

Figure 25 MMP Long-Term Turnover

inventory turnover history

Figure 25 MMP Long-Term Turnover

Figure 26 Product-Volume Analysis

inventory by production stage

Figure 26 Product-Volume Analysis

Figure 27 Product-Volume FKT Only

Figure 27 Product-Volume FKT Only

Figure 28 Product-Volume Detail FKT

Figure 28 Product-Volume Detail FKT

Midwest Machinery Parts

The firm marketed and distributed replacement parts for a particular type of industrial equipment. They imported the majority of these SKUs from China. Most SKU’s came as finished product, ready for sale. Some arrived as semi-finished product that was completed in MMP’s own shop according to customer specifications. We will emphasize primarily those aspects of the project involving the inventory analysis and what it reveals.

Long-Term History

Figure 25 shows MMPs overall inventory turnover for the previous four years. Earlier history was unavailable.

Turnover based on COG and COGS was 3.1 in 2007. It dropped to 2.0 in 2009, rose to 3.0 in 2010. However, recent additions to inventory indicated another drop in turnover was imminent.

This turnover is way below average for the industry. In fact, very few distributors in any industry have a turnover less than 2.5. Moreover, MMP’s turnover appears to be worsening.

Product-Volume Analysis

Figure 26 show MMP’s product lines and volumes for the previous 6-months. The green bars represent dollar volume at COG. The blue bars represent unit volume. The chart shows that the FKT product group represents 74% of total sales volume and a similar portion of unit volume. Casual observation indicated that the vast majority of inventory was the FKT product.

For this reason, we decided to focus the remainder of the study on FKT products. Most of the data, charts and tables that follow figure 26 include only those FKT products.

Figure 27 is the P-V chart for the 1112 FKT parts. About 100 items account for 75% of FKT sales volume. The remaining 1000 or so items represent only 26% of sales. Figure 28 examines the highest volume 100 items to reveal more detail. Here we see that only about 10 part numbers account for 42% of all FKT sales.

(Hover to enlarge)

Figure 29 FKT Sales & Inventory Cumulative

Cumulative Inventory

Figure 29 FKT Sales & Inventory Cumulative

Usage of Inventory Funds

 Figure 29 is similar to figure 27. Individual part numbers are on the X-axis and are ranked, left to right, by sales volume. The actual part number is not shown due to space constraints on the diagram. The upper curve (green) shows the cumulative percentage of total sales for each part number. The lower (red) line shows corresponding cumulative inventory for those part numbers.

In an ideal situation, inventory level for a part would reflect the sales for that part, the inventory turns would be identical for all parts and the two graphs would coincide. In practice, slower moving parts usually have higher variability, more inventory is required to compensate for the variability and turns are lower. The cumulative inventory curve would fall slightly below the cumulative sales curve in the left portion of the graph but would gradually rise to meet it.

The continued gap on the right of this graph is the result of dead inventory. It represents about $141,000 of inventory with zero sales during the six-month study period.

The gap at the left of this graph indicates that too much inventory is dedicated to slow moving items and probably insufficient inventory to fast moving items.

short term inventory history

Fig 30 Short Term History-All Items

short term inventory history

Figure 30 Short Term History-All Items

Short-Term History

A short-term history was compiled from a spreadsheet maintained by the Accountants. It reflected total daily inventory as entered into the inventory control system from January through June. The blue line in figure 30 shows this daily value while the narrow green line is a 31-day moving average. The patterns provoked several important questions:

• What was creating the steady decline from January through mid-April?

• What created the sudden, periodic, short-term increases?

The sudden, periodic increases resulted from the arrival of shipments from China. Orders were placed one per month (maybe) and consisted of 20-25 containers. These were all scheduled to arrive simultaneously. The red arrows indicate arrival dates.

The decline in inventory originated with a management edict to reduce inventory in the face of falling sales. After 2-3 months, shortages developed and purchasing increased orders. However, it required 60 days or more to build and ship the replenishment material. When these larger orders started arriving in May, inventory increased again.

It would require as much as two weeks to unload and receive such a large number of containers and this is why the increases are not instantaneous. When a shipment arrived, most other operations were shut down and there was a lot of commotion and confusion during receiving. After receiving, it might take several more weeks to put the material in its proper position and this created even more confusion.

Sales Orders

We analyzed the sales orders during this six-month period to profile the size and types of orders. The results, on average are:

• 36.4 Orders per Day

• 3.4 FKT Items per Order

• 1.5 Line Items per Order

• $1134 per Order

The overall conclusion is that MMP’s’s orders typically consist of mostly small orders, probably going to the replacement market. On rare occasions, large orders appear these large orders go to contractors for major distribution center projects or to Original Equipment Manufacturers for their production needs.

Obsolete Parts

Fig 31 Obsolete Parts

obsolete parts

Figure 31 Obsolete Parts

Of the 5870 part numbers, 2770 or 47% show no usage activity during the six-month period of the study. There are several possible reasons for zero usage:

• Part # may be obsolete and superseded by a different part#.

• There were no orders.

• The part was out of stock, unavailable for sales and the sales were lost—hence no usage.

(Hover to enlarge)

MPP Sock Status

Figure Fig 32 MMP Stock Status

MPP Sock Status

Figure Fig 32 MMP Stock Status

Figure 33 High-Volume Stockouts

inventory by production stage

Figure 33 Stockouts on High-Volume Items

low volume inventory stockouts

Figure 34 Low-Volume Stockouts

Figure 34 Low-Volume Stockouts

Stockouts & Slow Movers

Figure 32 displays the results of an aging study of the FKT inventory. This study includes any part numbers that a) have usage during the six-month period or b) have on-hand inventory. We considered that any item with more than 6-months of inventory was overstocked and any item with 0.1 month or less as a stockout.

With the present supply chain, an OH level of 1-6 months might be considered a “normal” or target inventory level. Improvements to the supply chain and reordering system will reduce this significantly and target levels of 1-3 months should be attainable.

Figure 32 shows an astounding 67% stockout rate. If these stockouts include high-volume, popular items it indicates a very large loss of sales. If stockouts are concentrated in slow-moving items, it may not be as serious.

To check on this, we segregated the highest-volume 100 parts and looked at the stockout rate separately. The stockout rate on items that make up 74% of sales was 57%, figure 33.

The 1000 lowest volume parts had a stockout rate of only 22%, figure 34.

Clearly, there was something very dysfunctional in the purchasing and replenishment system.

Figure 35 Sales & Inventory History—

Part# 1254-44

Figure 35 Sales & Inventory History—

Part# 1254-44

Short-Term Historical Analysis

Item #1254-44 was MMP’s sixth largest selling item with an annual volume of $99,792. It was typical of the high volume parts. We constructed a short-term history using the current On-Hand and a transaction history report. The objective was to identify patterns in sales and replenishment that might give insight into the algorithms used for re-ordering and replenishment. Figure 35 shows this chart.

On 1/1 the inventory was 420 pieces or about a five month supply at the average sales rate of 2.6 per day. No significant replenishments were arrived until 5/21. It may be that the management decision to reduce overall inventory in the face of falling sales at the start of the year was partly responsible for this non-replenishment.

On 4/6, the Midwest DC was out of stock. Since this DC accounted for 76% of total sales, total stock at all DCs remained constant because very little was sold from the East and West coast DCs. However, this eliminated any further sales from the Midwest DC. It appears that no replenishment orders were placed until the Midwest DC’s stockout or, at least, until the stock level was very low. Since it takes about 60 days to build and ship the product, new product did not start arriving until 5/21.

The replenishment orders that started arriving on 5/21 were very large. Inventory increased rapidly as these orders arrived until the end of the study on 9/12. At this time, there were more than 500 units in stock and even more units in transit and due to arrive shortly. All this on an item that sold, on average, 2.6 per day.

Accomplishments of The MMP Inventory Analysis

The inventory analysis was not the entire story for this project. Additional work was necessary to identify root causes and develop solutions and recommendations. The inventory analysis did accomplish the following:

  • Established that overall inventory was 2-3 times larger than normal.
  • With all of this excess inventory, customer service was poor and significant sales were lost.
  • Inventory was concentrated on the wrong items.
  • Inventory levels for individual items were erratic and varied greatly.
  • Replenishment policy and practice was dysfunctional.
  • Much additional information and many insights developed indirectly from the gathering of data and discussions.

■ ■ ■ ■ ■ ■ ■

The Strategos Guide To Value Stream and Process Mapping goes  beyond symbols and arrows. In over 163 pages it tells the reader not only how to do it but what to do with it.

Lean Briefing is only sent to those who subscribe or those who download from the Strategos website.

Books & Videos

Guide to Cycle Counting

Warehouse Planning Guide

Human Side of Lean Video

Contact Webmaster


SEP 2007     ©_Permissions