Don’t forget to share this post

Managing inventory is never an easy task, but always a necessary one. Depending on the types of goods and products you work with, storage and handling costs will differ. In other words, the requirements for a restaurant and a bookstore are quite different. Additionally, your business model will also affect inventory turnover. A bookstore that focuses on bestsellers and popular fiction is likely to use up inventory at a very different rate than a rare-book store.

The inventory turnover ratio shows how frequently a business consumes its inventory. In this article, you will learn to calculate the inventory turnover ratio and how to convert this value so it’s expressed in days. Finally, you will see examples of how to calculate this ratio using Excel or Google Sheets.

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

What is Inventory Turnover?

Inventory turnover shows how quickly a company uses its inventory. The inventory turnover ratio indicates how many times inventory was replenished during a specific timeframe. This value can also be recalculated so that it is expressed in days.

There are different methods available to find the inventory turnover ratio, using net sales or cost of goods sold (COGS). However, the latter is usually preferred, as using the value for COGS provides a more accurate result. When comparing ratio values, remember to check whether they were calculated using the same method. Values calculated using net sales can be significantly and misleadingly higher.

What is a Good Inventory Turnover Ratio?

A high ratio indicates that your products sell well since inventory is used quickly. However, too high a value could indicate a higher likelihood of stock shortages. A low ratio can indicate low sales or overstocking. However, what “high” and “low” means will vary significantly by industry and business model.

Generally speaking, higher values are preferred by all interested parties. However, the values themselves change drastically depending on various factors. If you work with intangibles, inventory turnover can be exceptionally high. For example, the finance and service sectors have the highest averages for inventory turnover.

How to Calculate Inventory Turnover?

To calculate the inventory turnover ratio, divide the cost of goods sold (COGS) for a given period by the average inventory for that same period. The average inventory can be calculated by adding the beginning and end inventories for the period and dividing by 2.

You can use whatever timeframe you prefer, but it’s common to use yearly, quarterly, or monthly data. You can use the following formula to calculate inventory turns for a given period of time.

inventory turnover ratio = COGS / average inventory

where

average inventory = (beginning inventory - end inventory) / 2

You can also quickly convert this to obtain the number of days a turn takes. Use the following formula to calculate the number of days it takes to use up the inventory:

average days to sell inventory = 365 / inventory turnover ratio

How to Calculate Inventory Turnover in Excel or Google Sheets?

As you can see, inventory turnover is a useful financial ratio. It provides insight into how well a company sells its products and manages its inventory. However, you need to recalculate this periodically to make real use of this information. By analyzing the data, you can investigate any unexpected values and uncover potential inventory problems.

In other words, this is not something you should attempt to do manually. Fortunately, tools like Excel and Google Sheets let you create templates to standardize and automate these calculations. In fact, using Excel or Google Sheets together with Layer, you can automate inventory management and control: from initial data collection and synchronization from multiple users to automatically sharing the final reports.

In the next section, you have examples of how to calculate inventory turnover ratios using Google Sheets.

What Is FPA and Why Is It Important
What Is FP&A and Why Is It Important?

Discover what FP&A is, how it fits into the larger Finance function and why it is such an essential part of any successful business.

READ MORE

Inventory Turnover Examples

The following examples show how you can calculate inventory turns and inventory days using Google Sheets. A time period of 1 year is used in both of the examples below.

Example 1. Car Dealership

In this example, I will calculate the inventory turnover ratio for a car dealership, as well as how many days a turn takes.

1. Gather Data

The first step is to gather the required data in your spreadsheet. You’ll need the amounts for COGS, beginning inventory, and end inventory for the period you’re interested in.

How to Calculate Inventory Turnover Formula Examples Car Dealership Data
How to Calculate Inventory Turnover (Formula & Examples) - Car Dealership Data

2. Calculate Average Inventory

To calculate the average inventory, add the beginning and end inventories, then divide by 2.

How to Calculate Inventory Turnover Formula Examples Add Average Inventory Formula
How to Calculate Inventory Turnover (Formula & Examples) - Add Average Inventory Formula

3. Calculate Inventory Turns

To find the inventory turnover ratio, divide the value of COGS by the value of average inventory.

How to Calculate Inventory Turnover Formula Examples Add Inventory Turnover Ratio Formula
How to Calculate Inventory Turnover (Formula & Examples) - Add Inventory Turnover Ratio Formula

As you can see, the car dealership’s inventory turnover ratio is 12.

How to Calculate Inventory Turnover Formula Examples Inventory Turnover Ratio
How to Calculate Inventory Turnover (Formula & Examples) - Inventory Turnover Ratio

4. Calculate Inventory Days

To get the average number of days it takes to turn over inventory, divide 365 by the inventory turnover ratio.

How to Calculate Inventory Turnover Formula Examples Add Inventory Days Formula
How to Calculate Inventory Turnover (Formula & Examples) - Add Inventory Days Formula

That’s it. There are approximately 12 inventory turns per year, which means inventory is renewed about once a month.

How to Calculate Inventory Turnover Formula Examples Inventory Days
How to Calculate Inventory Turnover (Formula & Examples) - Inventory Days

Month-End Close Process: Everything You Need To Know

A good month-end close process is efficient and reduces mistakes in accounting. Here’s everything you need to know about the month-end close process.

READ MORE
Month End Close Process Everything You Need To Know

Example 2. Restaurant

In this example, I will calculate inventory turns and inventory days for a restaurant. Due to the differences between food and beverages, it’s recommended that you calculate the ratios separately.

1. Gather Data

As this example is for a restaurant, I will calculate the ratios for food and beverages separately. If you work with different products and types of goods, it’s a good idea to calculate their ratios separately.

How to Calculate Inventory Turnover Formula Examples Restaurant Data
How to Calculate Inventory Turnover (Formula & Examples) - Restaurant Data

2. Calculate Average Inventory

To calculate the average inventory for each, add the beginning and end inventories, then divide by 2. Since I’m using the table I set up in the previous example, I can just drag the formula to the right.

How to Calculate Inventory Turnover Formula Examples Add Average Inventory Formula 2
How to Calculate Inventory Turnover (Formula & Examples) - Add Average Inventory Formula

3. Calculate Inventory Turns

To find the inventory turnover ratios, divide the values of COGS by the values of average inventory. Below, I have simply dragged the formula to the right.

How to Calculate Inventory Turnover Formula Examples Add Inventory Turnover Ratio Formula 2
How to Calculate Inventory Turnover (Formula & Examples) - Add Inventory Turnover Ratio Formula

4. Calculate Inventory Days

To get the average number of days it takes to turn over inventory, divide 365 by the inventory turnover ratios.

How to Calculate Inventory Turnover Formula Examples Add Inventory Days Formula 2
How to Calculate Inventory Turnover (Formula & Examples) - Add Inventory Days Formula

That’s it. You can see that all three have very different values. This is to be expected, considering they deal with different types of inventory.

How to automate your FP&A on top of Google Sheets?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

Conclusion

As you have seen, inventory turnover varies considerably by industry and business model. As with all financial ratios, it’s important to get appropriate benchmarks to avoid meaningless comparisons. Inventory management and control are crucial to the success of a business, and inventory turnover ratios can help you keep track of progress and identify potential problems.

You now know about inventory turnover and how to calculate inventory turns and the number of days a turn takes. You also know how to use Excel or Google Sheets to find a company’s inventory turnover ratio. Finally, you know how Layer can help you automate inventory management and control, including the calculation of inventory turns.

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Nov 8 2022, Updated Nov 23 2022