Operational Inventory Analysis
An operational inventory analysis uses the previously
discussed metrics to identify areas of concern. It
points the way to further investigations and possible
solutions that improve operations and reduce unnecessary
inventory.
There is no fixed format or detailed procedure for an
inventory analysis. The format depends on the data
available. The precise lines of inquiry depend on the
situation as it reveals itself during the investigation.
In many ways, it is like Sherlock Holmes investigating a
murder. Accordingly, we illustrate the analysis with
many examples from past projects rather than a detailed
sequence of tasks and outputs.
Table 2 Typical On-Hand Inventory Report
Table 2 Typical On-Hand Inventory Report
The overall turnover from this report, based on dollar value, is 23.5 and is quite favorable. However, this overall value is dominated by the large amount of PVC resin. Other items
have much lower turnover as shown by column 11 and these might warrant some attention.
Much of the data required comes from the inventory
management system or an ERP/MRP system. This extraction
process requires a person who is familiar with the
system, the contents of various databases and the
extraction process. In most cases, the data is best
extracted to an Excel spreadsheet format. There may be
several of these extraction files. Extraction files
should include the basic fields such as part#,
Description, U/M and On-Hand. There may also be
additional fields that will prove useful. These
additional fields might indicate a class of item,
product line, storage location or other information.
When in doubt about the usefulness of a possible
extraction field, include it in the extraction file. It
is easy to delete fields that prove irrelevant but often
difficult to add them in later. Table 2 illustrates.
Figure 3 Sales By Product
Figure 3 Sales By Product
Figure 3 shows the current product groups and volumes
for a manufacturer of industrial heater modules. One set
of bars denotes the dollar value of sales and is ranked
by sales dollar volume. The second set of bars denotes
units of production. Notice that the tubular heaters
rank highest in sales dollars but lowest in production
units. This is because they are very expensive and,
presumably, have high margins.
Figure 3 is actually not typical. Most firms have a
have a much higher fraction of low-volume products
coupled with only a few very high-volume products. This
can raise important issues of pricing, overhead
allocation and profitability.
This process may take some imagination. For example,
few inventory systems maintain a history of On-Hand
Quantity. However, this can be generated for a
particular SKU by combining the current On-Hand with the
transaction history for that item.
Product-Volume Analysis (PV)
A PV analysis examines the relative production or
sales volume for various products and product groups.
The purpose is to understand the product mix and volume.
The analysis is helpful for many types of projects
including: facilities planning, warehouse design, and
process improvement. It often complements the inventory
analysis and, since it uses some of the same data, we
include it here.
![](/images/Inventory%20History-sm.gif)
Figure 4 Sales History/Forecast
Figure 4 typical Sales History/Forecast
Figure 4 came from a plant layout project for a
company with two major product lines. It shows the sales
history for each product line and the total.
Most of their growth is on the “container” product
line and this has important implications for future
facility planning, Manufacturing Strategy and business
strategy.
A product-volume analysis answers questions such as:
• What is the current annual, monthly, weekly or daily
volume for each item?
• What are the volumes for groups of similar items?
• Will there be significant changes in the relative
volumes?
• How will total volume change in the future?•
Are there seasonal (or other) patterns in the volumes?
|
Figure 6 Typical Turnover Distribution
Industry Turnover Comparison
Turnover is one of the most important inventory
metrics. A firm may track turnover to determine trends,
identify anomalies, make decisions and monitor progress.
It can also benchmark against others in their industry.
Within an industry, the industry average turnover
reflects “average,” almost a definition of mediocrity.
There are often, however, a few firms that turn their
inventory much faster than the average—perhaps 5-10
times faster. This is shown in figure 6. Do not assume
that because a firm is near the industry average that it
is “OK.” Average may be very poor performance compared
to what is possible.
![](/images/inventory_data-sm.jpg)
Figure 5 Turnover Data
Figure 5 Turnover Data
One good source of turnover data is the U.S. Census Bureau’s American Factfinder site.
Since turnover is a common financial measurement,
financial data sources such as Standard & Poor or Dun &
Bradstreet provide such data for specific firms or as
industry averages. Several cautions are in order when
using such data:
• Two firms are rarely alike in all respects and
turnover data may reflect these differences.
• Financial data may come only from public companies
and these tend to be larger firms.
• A given industry may have wide turnover variations
even from apparently similar firms.
• Turnover data is usually buried in a sea of other
financial data and finding it is inconvenient.
Purchase Industry Average Data
One good source of turnover data is the U.S. Census
Bureau’s American Factfinder site. The data includes
firms of all sizes and is arranged by NAIC code
(formerly SIC code). However, calculating turnover from
this extremely detailed data is not easy. Strategos has extracted the relevant parts from the Census Bureau data and put it in a convenient form
for inventory analysis.
A section of this spreadsheet is in figure 5. You may
purchase a copy of the manufacturing or warehouse data
by clicking at right ($8.00):
Data Acquisition
Table 3 Data Acquisition
Table 3 Data Acquisition
Acquiring the data for the various parts of an inventory analysis is rarely difficult. Some data comes from previous year-end financial statements. Most of the
charts that follow derive from a current dump of inventory status. Table 3 shows a small part of the Excel file used for many of the following charts. It is
from a project with a distributor of replacement parts for off-road vehicles. We will call the company V-Parts for confidentiality reasons.
|