Using On-hand Inventory for Predictive Analytics

The transaction activity and on-hand inventory that is available from retail perpetual inventory systems provides a wealth of information.  While some retailers apply robust analysis tools, techniques and procedures to this data, many small to medium-sized entities (SMEs) do not.  The flow of products through your store or your distribution center (DC) is the lifeblood of your business. A well-run business leverages this information to identify issues and drive success.

Using On-hand Inventory for Predictive Analytics

Using On-hand Inventory for Predictive Analytics

The precision applied to inventory management in a manufacturing facility is not realistically achievable in a retail setting, nor cost-justified, but some of the tools and techniques used are relevant.  Analyzing the flow of materials through a factory is a technique to identify and resolve bottlenecks, ensure planned product priorities are maintained and to minimize the cost invested in the product pipeline.   Optimizing the balance of materials, labor, equipment and, very important, time increases the contribution of the facility to the enterprise’s success.  Similarly, optimizing the flow of product in (receipts) and out (sales) of your stores (i.e., inventory turn) will dramatically improve both customer service (e.g., reduced out of stocks) and profitability.

Optimization is a strong word and perhaps unrealistic for many SMEs, however, detailed analysis to identify any inventory that falls into the below classifications creates opportunities to improve your operations, sales and inventory management, yielding savings and/or increased profitability well in excess of the effort required:

Negative Inventory – arises from selling more units than the system calculates as received/on-hand.  Common causes are:

  1. Mislabeling of the product (usually a vendor issue) – two similar items made by the same vendor both have the same item number/SKU on their label.
  2. Deliveries that are not received in the system and the items are sold.
  3. Transfers from other stores that are not received in the system (or cannot be received due to an error by the sending store) and the items are sold.
  4. Errors in setup of the item in the Item Master File – associating more than one item number/SKU with a particular item.

Overages – inventory balances at a location in excess of the maximum planned based on the reorder point and reorder quantity.  Common causes are:

  1. Mislabeling of the product (usually a vendor issue) – mirrors 1 above.  One item will have negative stock and the other mislabeled will show an overage.
  2. Errors in setup of the item in the Item Master File – mirrors 4 above although this will only be the case if the company does not utilize system defined re-order points and re-order quantities, which many SMEs do not.

“Stale” Inventory – inventory that is not moving.  Everyone makes mistakes creating or selecting products to stock.  Items that are not moving need to be identified and either transferred to a location where they will sell or discounted to sell.  The longer items remain in a store, the more likely they are to be damaged or otherwise rendered unsaleable.  More importantly, these stale items take up store space that should be used for more successful items.   Recall the “time” factor noted above.  One final possibility, a store cannot sell something they do not have and perhaps these “stale” items are not physically in the store because they were stolen.  Investigation is needed to determine if this is the case.

Relative Sales Performance – extending the concept of analyzing “stale” inventory is to pay special attention to stale items at certain stores that have relatively good to excellent sales performance at other stores.  This is a very strong indicator of theft (i.e., the items aren’t selling because they are not physically in the store to sell).  Research that leads to identify theft/shrink will lead to re-order of these items and increased store sales as once the items items are on-hand, sales of the items should mimic the good to excellent sales performance exhibited at similar stores.

“Dummy” SKUs – generic SKUs used to complete a sale if an item is not recognized by the register/POS.  Customer service takes precedence so “dummy” SKUs are necessary to avoid delaying the customer at checkout.  Timely research into these sales is needed to correct the issue in the POS system and avoid re-occurrence at that or likely all stores.  If practical, these prior sales should be adjusted to the proper SKU.  Analysis of “dummy” SKUs may also identify internal theft generally via excessive discounts.

Velocity – another term for inventory turns or the speed with which products are converted from receipts to sales.  Increasing velocity will have a dramatic effect on profitability because the increased sales have no associated increase in fixed cost.  Sales per square foot is a related measure to velocity.  If the perpetual inventory system does not have reporting for velocity, there are many data analysis programs including “cloud” solutions that can provide this.  Certainly the perpetual system has the transaction information needed to calculate velocity.

Predictive analytics applied to inventory transaction data is no longer restricted to large retailers.  Powerful computers and/or “cloud” technologies are now well within reach of SMEs to implement predictive analytics to improve their business process.  If you are not already analyzing the above classifications of inventory, that is a great place to start because all are certain indicators of inventory/product issues.  Most inventory systems have reports available to analyze these classifications or to output to EXCEL for simple analysis.  Great retailers know what their customers want and track sales, on-hands, inventory turns, etc. very closely.  A quick look at your perpetual will give an indication of how well your Company is doing.  The presence of any of the above conditions in your perpetual inventory indicates opportunities to improve inventory management.

The above are but a few common opportunities to identify issues by analysis of the perpetual records.  Please comment to share the classifications you analyze to identify issues and contribute to our dialog.  Analysis of information readily available from your perpetual inventory system or available via download and analysis is but one more way we can all leverage compliance.


About the Author

Glenn Murphy, the co-founder of BestGRC and founder of GRC Management Consulting, primarily focuses on empowering entities to leverage their compliance activities through the BestGRC “cloud” software, his consulting work, publications and the “Leverage Compliance” blog.  Find Glenn’s full profile at , follow him @GlennMurphyGRC and subscribe to the Leverage Compliance blog at