Courses
Free Excel
Blog
About us

KPI – Inventory Accuracy: How to track and improve it? (Stocktake, Excel Calculation Tutorial)

Table of Contents

In the supply chain world, or more generally, when a company sells or produces goods, the reliability of the stock is critical.

To see the big picture, there is more or less a 65% average inventory reliability rate in the United States in the retail industry.

I will show you how to improve your Inventory Accuracy and quality in this article.

You can download the Excel file here:

Download the Inventory Accuracy template

Definition of Inventory Accuracy

The Inventory Accuracy rate, or inventory accuracy, inventory quality, is simply the rate of difference between two things:

  • The physical stock, which is the stock you actually have in your warehouses or stores
  • The ‘computerized’ inventory is the inventory status information generally present in your different management tools (ERP, WMS, etc…)

In order to better visualize the concept, let’s see an example together. Here is a display in a supermarket:

Supermarket Example

We can see the difference in quantity between the “IT” stock and the “Actual” stock.

The product in the middle of the image has no variance, it is the best-case scenario 👏.

On the other hand, for the product higher on the left, there is a negative variance of -3 :

One of the reasons for this discrepancy, which we will detail below, is that a confusion could have occurred. Indeed, the difference in color between this product and the one on the right is minimal. The person in charge of receiving or putting the product in stock could have easily made a mistake.

The last product is completely out of stock, even though it is announced that 4 articles are present. Among the reasons: theft, unfortunately very frequent in supermarkets.

Other errors are also to be underlined, such as products stored in the wrong place or products wrongly labeled, wrongly shipped from the supplier… These errors can also lead to a positive discrepancy (which is not necessarily a good thing)

This kind of discrepancy is very common in inventory management and is also very detrimental to your business. The consequences can be very important. We will see why and how in the next point ⬇️

These discrepancies are also present in warehouses, except that the amount and volume of storage is often much higher. As a result, the costs associated with reliability issues are greater 🤑.

Why is Inventory Accuracy so important?

First of all, we must remember the objectives of a profitable supply chain:

What is a profitable supply chain ? (sourced from my article : What is supply chain?)

In this illustration, we see that in order to improve profitability, we must :

  • Reduce costs
  • Improve service
  • Keep the stock low (cash flow, capital investment)

In order to reduce costs, you must avoid losses/negative variances at all costs. Moreover, by reducing this, you can improve the service rate of your customers. Indeed, this type of concern could cause stock shortages, thus reducing your availability rate and losing potential sales.

Positive deviations, which are rare, are also to be avoided, as this will generate dormant stocks, nowadays, the cost of storage can sometimes be very high… It will also increase your invested cash.

The Excel formulas of Stock Tracking

Before you start, download the Excel HERE  

In this file, we have a table with several columns:

  • Item: the name of the item, the SKU, the EAN code, designations, etc…
  • Sector: category of the article, size, family, etc… Do not hesitate to add more columns allowing to group similar articles, that will help you in your future dashboard
  • IT stock 
  • Actual Stock
  • Cogs Price in $ : the unit cost allowing you to value your stock and also to estimate the cost of variances
  • IT Stock $ = Price in $ x IT Stock : the cost of your IT stock
  • Stock check $= Price in $ x Actual Stock : the cost that you really have
  • Qty Gap = Actual Inventory – IT Inventory: the amount of variance of each item counted
  • Value $ Gap = Price in $ x Difference: the value that the differences generate
  • Inventory accuracy : 2 possible values, 0 when there is a difference and 1 when there is no difference

Concerning the cell in red -8%, it is the valuated rate of our variance (TOTAL Value Variance $ / TOTAL Stock IT $). This rate allows to have an idea of the losses easily.

The last row TOTAL is simply the respective sum of the columns of costs and variances EXCEPT for the reliability of the stock which is an average.

How to check your stock (inventory process)

You can also create dashboards with more meaningful visuals to monitor the health of your supply chain.

Dashboard created in my SCM Analytics program.

You can follow the indicators in these dashboards (availability rate, inventory accuracy, etc…). You can see if your objectives are reached at a glance! But there are other advantages:

  • The possibility to visualize the evolution by sectors, categories, product families, brands (hence the importance of feeding a maximum of your databases)
  • Thanks to the filters, you can choose the period, the sector and more
  • You can also see the sales distribution, losses by category, and obviously others according to your choices and needs!

The 8 reasons for poor inventory accuracy

So what are the different reasons for poor inventory accuracy, and how can we improve this KPI?

1- A lack of control

A good control frequency is essential. The mistake is to carry out end-of-year inventories during the tax period. It is important to check your stock regularly, ideally every quarter. There is also another solution: rotating or cyclical inventories. This type of inventory relieves the work of the operational teams. Indeed, instead of checking the whole warehouse in one or two days, you will count the most important references. (The ABC Analysis will help with this.)

2- Supplier delivery issues

Sometimes your suppliers can mess up their preparations or their shipments, products come to miss… It is necessary to set up stricter control systems at reception.

3- Unrecorded quality problems

Unfortunately, in the course of a product’s journey through a supply chain, it is common for breakage, damage or quality problems to occur. These products become unsellable. Often, the information is not recorded in the computer system, resulting in an error in the stock. 

4- Thefts

As previously mentioned, it is not uncommon for theft to occur in stores. But it also happens frequently in warehouses! The more players there are, the higher the risk of theft. Strict controls must be put in place.

5- Poor management of customer returns

The packages will have been to the other side of the world and back… When they arrive back at the warehouse, they are often damaged and the labels are missing. These parcels are generally left abandoned and in the best case, they are stored in a “return zone”. We take care of them when we have time, but in the supply chain, we are usually running out of time. These products therefore generate significant losses when they are disposed of.

6- Lack of process & training

Operators are often poorly trained, whether in the physical flow, in handling or in the IT flow. Companies often neglect the integration and training periods. In order to overcome this problem, you can create simple procedures for each position, and why not, set up a checklist for your employees to follow.

7- Lack of automation

Some companies still work with paper and pencil or, in the best case, with Excel spreadsheets. In today’s world, investing in automated information systems is essential. Investing in scanners is the bare minimum. IT tools are sometimes expensive and not very flexible, but new solutions are emerging in recent years: no-code. With a good support, you can create solutions that are sometimes 20x cheaper and totally flexible.

RFID (Radio Frequency Identification) chips are another solution in favor of a greater reliability of your stocks. They allow controlling the stock remotely and much faster. At Decathlon, for example, we can see a robot that goes through the aisles to check the RFID chips directly, freeing the staff from counting. In addition, the robot can perform inventories much more frequently.

8- Too much stock

The higher the quantities to be stored, the more difficult it will be to manage. The amount of information is likely to increase, and therefore the risk of errors in data entry by operators and sales staff also increases. It is also important to avoid overloading your warehouse or stores. It is often advisable to have a maximum occupancy rate of 80%.

Boost your inventory management skills

To stop suffering from shortages and overstock daily, I reveal my secrets in a free training.

I will share with you:

👉 My method for dealing with demand and supplier uncertainty: 13 metrics to master.
👉 How to reduce your inventory and increase your service rate simply and automatically.
👉 Excel files and case studies presented live (Zara, H&M, Amazon…)

Become a SCM Analytics Expert
Join my free 7-day mini training: “Track and boost your performance Automatically.”
Share
Start building KPI like a pro !

Join my free 7-day mini training: 
“Track and boost your performance Automatically.”

  • Why 87% of companies struggle to measure their performance
  • The 5 main indicators for your Supply Chain (without falling into the expert syndrome)
  • How to prioritize them in 4 levels
  • How to automate your reporting without spending a fortune on IT development
  • Case studies of large groups: Zara, H&M, Amazon…
Ready to boost your skill ?
Discover our courses
See all courses
SCM Foundation
View the course
Excel training label
Excel Expert
View the course
SCM Analytics training label
SCM Analytics
View the course
Inventory management training label
Inventory Management
View the course
Forecasting training label
Forecasting Expert
View the course
Scroll to Top

FIND OUT HOW MUCH YOU ARE WORTH.

You will receive an email with an estimate of your salary along with some advice on how to boost it.

Your information is confidential and will never be given to third parties. You can unsubscribe in 1 click from any of my emails.