Seven leading KPIs for Inventory Analysis (Part 3)

This is the third and last part of my article on the series of Inventory Analysis. In the first part, I described how absence of appropriate inventory analysis could kill the retail businesses in general and quick commerce businesses in particular.  In the second article, I described the important features which are needed to make data driven decisions about inventory.

A good dashboard on inventory analysis that empowers actions and insights should include KPIs for following factors in order of importance

  • Demand for the inventory
  • Lead Times
  • Shelf life of inventory
  • Costs

The dashboard must allow the decision makers to find answers to following questions.

◼ How much inventory do we need to service our customer demand?

◼ When do we need that inventory?

◼ How much cash flow do we need to invest in inventory?

◼ When do we need that cash?

◼ Are our inventories valued properly?


As can be quickly seen, these are all questions which require predictive powers. I am a big fan of leading indicators and believe they are extremely undervalued in the BI community. Notice that having the answers to these questions using data from the past may not be the best strategy. Customers change, products and their components change, and production and sourcing methods change. Because of the ongoing changes, the best BI teams use analytical methods to review inventory on a regular basis. This ongoing review helps understand and address the potential effect on inventories brought about by ongoing changes in the business.





KPIs for Inventory Analysis



Below are some of the KPI’s which can help decision makers anticipate critical change in business direction.


  • Inventory on hand less forecasted demand
  • The supply chain lead time

These two KPIs ultimately help you calculate what items in inventory have less than forecasted demand and when you can service the customer and communicate with the customer accordingly.


  • Days on hand
  • Inventory turns
  • Inventory in excess of demand
  • Inventory aging

These KPIs help you determine what inventory is more than demand and should trigger actions to turn the excess inventory into cash as quickly as possible

  • Current recorded cost of excess inventory

The difference between the cost you paid for an item in inventory based on the invoice and what you could sell that item for in the market. If the market value is less than the recorded value, you have overstated the value of the inventory (your asset). The market value can be one of several possibilities:

▸ Zero

 because you must throw away the item. This case would clearly be below your recorded value.

▸ Some scrap value

This case would also clearly be below your recorded value.

▸ The price you could sell the item for.


The Benefits of Inventory Analysis:


Let’s take the example of a fictitious quick commerce company which had inventory worth 17 million USD and hadn’t analyzed its inventory since several quarters.

They had the following issues:

◼ Their existing systems were not used as an ERP or MRP tool.

◼ They didn’t know what the demand going forward was for their product.

◼ Service levels (product delivered on time and complete) had dropped to 40%.

◼ Sales had been declining at a rapid rate.

The quick commerce team was busy trying to meet customer order requirements, without any analysis of its inventory.

Does this sound familiar with the quick commerce companies?

Finally, they hired a BI management consultancy which began their analysis with some basic information and processes.

Here are the initial actions taken by the consultants:

◼ Took steps to get their inventory accurate and measure accuracy with a combination of cycle counts and physical inventories.

◼ Began to forecast the demand by product and customer.

◼ Turned demand into material requirements.

◼ Began to analyze inventory in terms of demand excesses or shortages at the item level.

◼ Began to analyze days on hand.

◼ Implemented a comprehensive Inventory Analysis dashboard using Power BI

As a result of their analysis, the consultants discovered:

◼ Over half of their inventory was significantly in excess of demand.

◼ A full third of the inventory was obsolete.

◼ Critical raw materials and components for products were not ordered within the lead times to meet customer demand for the finished product.

◼ Their cash flow was significantly retarded because of poor inventory management.

With proper implementation of some basic processes like forecasting and tying purchasing decisions to demand and lead time, the company was able to achieve significant results. Those results were:

◼ Inventory required to run the business was reduced, freeing up cash flow.

◼ Existing obsolete inventory was turned into cash.

◼ Critical raw materials and components were ordered on time to meet customer demand.

◼ Service levels rose to 98%

◼ Excess and obsolete inventory was minimized going forward, identified sooner in the product life cycle, and turned into cash more quickly.

◼ Inventory was valued properly.

◼ Sales began to grow again!





Author: Misbahuddin Abdullah

PDF Version