What is Inventory Turnover?
Inventory turnover is a ratio that measures how many times a company sells and replaces its inventory in a given period, such as a year, a quarter, or a month. It shows how efficiently a company manages its inventory and how quickly it can generate sales from it. A high inventory turnover ratio indicates that a company has a strong demand for its products and a low level of unsold inventory. A low inventory turnover ratio implies that a company has excess inventory, low sales, or poor inventory management.
How to Calculate Inventory Turnover?
There are two common ways to calculate inventory turnover:
- Using sales and inventory values: Inventory turnover = Sales / Inventory
- Using cost of goods sold (COGS) and average inventory values: Inventory turnover = COGS / Average inventory
The first method uses the sales and inventory values at the end of the period. It is simple and easy to use, but it may not reflect the actual cost of the inventory or the average inventory level during the period. The second method uses the cost of goods sold, which is the direct cost of producing or purchasing the inventory, and the average inventory, which is the average of the beginning and ending inventory values. It is more accurate and realistic, but it requires more data and calculation.
How to Calculate Inventory Turnover in Excel?
To calculate inventory turnover in Excel, you need to have the following data in your spreadsheet:
- Sales or COGS for the period
- Inventory value at the beginning of the period
- Inventory value at the end of the period
You can use the following formulas to calculate inventory turnover in Excel:
- Using sales and inventory values:
=Sales/Inventory - Using COGS and average inventory values:
=COGS/AVERAGE(Inventory_beginning,Inventory_ending)
You can also use the following formulas to calculate the days in inventory, which is the number of days it takes to sell the inventory on hand:
- Using sales and inventory values:
=365/(Sales/Inventory) - Using COGS and average inventory values:
=365/(COGS/AVERAGE(Inventory_beginning,Inventory_ending))
Example of Inventory Turnover Calculation in Excel
Let’s say you want to calculate the inventory turnover and the days in inventory for a company that sells furniture. You have the following data for the year 2023:
| Item | Value |
|---|---|
| Sales | $1,000,000 |
| COGS | $600,000 |
| Inventory at the beginning of the year | $200,000 |
| Inventory at the end of the year | $300,000 |
Using the formulas above, you can calculate the inventory turnover and the days in inventory as follows:
| Item | Formula | Value |
|---|---|---|
| Inventory turnover using sales and inventory values | =Sales/Inventory_ending |
3.33 |
| Inventory turnover using COGS and average inventory values | =COGS/AVERAGE(Inventory_beginning,Inventory_ending) |
2.67 |
| Days in inventory using sales and inventory values | =365/(Sales/Inventory_ending) |
109.5 |
| Days in inventory using COGS and average inventory values | =365/(COGS/AVERAGE(Inventory_beginning,Inventory_ending)) |
136.7 |
The results show that the company sells and replaces its inventory 3.33 times a year using the sales and inventory values method, and 2.67 times a year using the COGS and average inventory values method. It also shows that the company takes 109.5 days to sell the inventory on hand using the sales and inventory values method, and 136.7 days using the COGS and average inventory values method.
Other Approaches to Calculate Inventory Turnover in Excel
There are other approaches to calculate inventory turnover in Excel, such as using pivot tables, data analysis tools, or VBA macros. However, these approaches are more advanced and require more skills and knowledge of Excel. For most purposes, the formulas above are sufficient and easy to use.
