Courses
Free Excel
Blog
About us

Inventory Turnover ratio: Formulas & Calculation in Excel

Table of Contents

The Inventory Turnover has different names :

  • Inventory Turnover
  • Inventory Turn
  • Stock Turn
  • Days on Hand
  • Days in Inventory
  • Inventory coverage
Download the Excel file

In my opinion, it is one of the most important KPIs in Supply Chain.

I’ve been using this KPI for the last 15 years as a demand planner, Supply Chain Manager, S&OP Manager, Supply Chain Director, and Consultant. And I am always surprised to see so many companies not tracking correctly this crucial indicator, or even not tracking it at all.

What is the inventory turnover ratio? Why is this KPI important? What is the formula?

Check my video below with a step-by-step tutorial. You can also download the Excel used in the video and this article here :

Why is the Inventory Turn KPI so important?

If you want to have a profitable Supply Chain, you need to track 3 main pillars: Service, Costs, and Inventory.

Profitability is achieved if we keep a high level of Service, while minimizing Costs and Inventory. Here, we focus on the bottom right of the pyramid: The on-hand inventory is directly related to the amount of cash available.

As stated by Warren Buffet, “Cash is King”: improving your inventory management strategy is critical for the profitability of your company.

Less inventory means more cash available (free cash flow) for investments. For example, You have more money to invest in new products, better marketing processes… If the investments are a success, you will have more sales and more profits: more free cash flow. This is a virtuous cycle.

So, to lower your stocks and improve your cash flow, you need good inventory management.

This KPI can be tracked at each step of the chain: from the raw material to the factory, the warehouse, the transportation network, and the distribution network. Whether it is stocked items or goods in transit, it is possible to measure the corresponding stock turnover at the SKU level or globally.

The Inventory turnover ratio can be measured at any point in the chain (and also globally)

Inventory Turnover meaning and formulas

Inventory Turnover ratio formula

The inventory turnover ratio is used to assess if the stock is excessive compared to the sales.

In other words, it answers the following question :

“How many times does my stock turn over?”

The formula is the following:

  • Average Inventory Value: the average inventory available over a period.
  • Sales or Consumption: the sales made over that same period. You can use shipments or consumption of components or raw materials. The principle remains the same.

Inventory and Sales can be valued at the purchase price or possibly sales price (I do not recommend using quantities). Be careful, however, to use the same valuation between the two variables.

Days in Inventory formula

I prefer to measure the Inventory Turnover in days, I find it more meaningful. It answers this question:

“How many days does my stock last?”

Here is the formula:

  • Average Inventory Value: the average inventory available over a period.
  • Sales or Consumption: the sales made over that same period.
  • Period: the number of days in the period covered. If you are calculating a global indicator, it is better to take a long enough period, I recommend 1 year or 365 days.

Again, keep the same valuation between Inventory and Sales (purchase price or sales price).

Days in Inventory: Apple vs Samsung

Let’s take the example of Apple and Samsung. Apple has almost 6 times less inventory in value than Samsung, and its turnover is also higher. Applying the formula over 365 days, we get 73 days of inventory turnover for Samsung against only 9 days for Apple.

This means that, on average, Apple’s inventory is sold out 8 times faster over a year than Samsung.

The main reason is that Apple ships its stock by plane, directly from China to its stores, without any intermediate stock, and therefore benefits from very short supply times. The frequency of stock turnover is nevertheless exceptional for this type of business.

Inventory Turnover calculation in Excel

We will now calculate these indicators in Excel with concrete examples. You can download the complete Excel below:

Download the Excel file

Inventory Turnover in days: Excel calculation

The calculation is very simple: simply divide the average stock per product by the sales, multiplying by the period in days (here we are talking about values over 1 year).

Inventory turnover in days

Be careful, to calculate the Stock Turnover on a consolidated basis (by brand, category, etc.), do not make the average of the stock turnovers by brand (because there would be no weighting on the stock value). Therefore, the total of sales and stock must be taken into account in the calculation. We can see it in this example: if product 9 is clearly overstocked (730 days), it doesn’t impact so much the global stock turn (140 days) because its sales are quite low compared to the other products.

Inventory Turnover ratio (cycle): Excel calculation

We can also calculate the frequency at which the stock turns over during the period. This time, we simply divide the sales by the stock (without using the period in the calculation):

Inventory turnover ratio cycle in Excel

Thus, in this example, the entire stock rotates two and a half times during the year.

Inventory Turnover: 5 Questions and common mistakes

1. I don’t have any Inventory data history – what should I do?

I deal with this question regularly from members of my classes. If you are in this situation, my advice is the following: don’t wait. Start today with your available inventory, save your data as you go along, and you’ll be able to calculate your inventory turnover over a short period. Your indicator will get more accurate over time.

2. Stock and Sales valuation

I repeat this because it is a major mistake that I see very often (even with some of the clients I coach). Always make sure you use the same valuation for inventory and sales. Otherwise, the calculation becomes completely wrong.

3. Seasonality: be careful

If your business has a strong seasonality, be careful when interpreting the value of your inventory turnover KPI. Indeed, if you use the data of the last few weeks, you will not be able to anticipate strong sales variations. You will tend to overestimate your stock coverage before a peak in sales and underestimate it before a drop in demand.

To solve this problem, be sure to use a longer period of time (e.g. 52 weeks sales if you have a 1-year seasonality) and possibly a seasonality coefficient (more complex).

The other solution is to use your forecasts in the calculation rather than your past sales.

4. Use your forecasts instead of sales (Inventory Coverage)

So here’s my advice for improving your KPI when seasonality is high: use your forecasts. We tend to call this indicator inventory coverage or stock coverage. It takes into account the current inventory, not the past, with future sales. Of course, the accuracy of the indicator will depend on your forecast accuracy.

Inventory coverage formula in days

stock turn seasonality forecast
It is wise to use your forecasts in the calculation to anticipate changes in demand

If your forecast is unreliable, or if you don’t have a forecast yet, use past sales. It is always better to proceed step by step.

5. How to optimize my Inventory? – Go further

Finally, the last question I am often asked is simply related to performance management. What should you do if your inventory turnover KPI is systematically too high compared to your average lead time? Or constantly equal to 0, because you are experiencing a flood of stock-outs?

Keep in mind that in inventory management, it is essential to measure. It is not possible to improve processes if you are not able to measure them reliably and systematically. Is the inventory turnover ratio set up and automatically calculated over your whole portfolio?

If this is the case, you will be able to analyze and make decisions to manage your stocks more smoothly. Also, your decisions must take into account other factors (ABC Analysis, Service KPI, Safety Stocks & EOQ…). Check out all my free tools here:

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.