Calculate Inventory Turnover Ratio in Excel
By ExcelReady TeamUpdated 6 min read
The short answer
Inventory turnover ratio is cost of goods sold divided by average inventory. In Excel, with COGS in B2 and the beginning and ending inventory in C2 and D2, type =ROUND(B2/AVERAGE(C2:D2),1). A result of 6 means you sold through your average stock six times in the period. Divide 365 by it to get days.
Key takeaways
- Inventory turnover = cost of goods sold ÷ average inventory, both for the same period.
- AVERAGE(C2:D2) gives the average of the beginning and ending inventory in one step.
- Days of inventory = 365 ÷ turnover, or average inventory ÷ COGS × 365.
- For a total across products, divide total COGS by total average inventory. Don't average the ratios.
- Use IFERROR to avoid #DIV/0! when a product had no stock.
On this page+
- What is the inventory turnover ratio?
- How to calculate inventory turnover in Excel
- How to round the turnover ratio
- How to convert turnover into days of inventory
- How to calculate a total turnover for all products
- How to use monthly inventory for a better average
- Common mistakes with inventory turnover in Excel
- Practise it
The inventory turnover ratio tells you how many times you sold through your average stock in a period, and in Excel it is one short formula: cost of goods sold divided by average inventory. This guide shows how to calculate the inventory turnover ratio in Excel for each product, round it, turn it into days of stock and total it correctly. It is for anyone in retail, supply chain or a small business who needs the number for a report or an interview task.
What is the inventory turnover ratio?#
Inventory turnover is a ratio that compares how much stock you sold with how much stock you held. The formula is:
Inventory turnover = Cost of goods sold ÷ Average inventory- Cost of goods sold (COGS) is what the products you sold cost you, for the period (usually a year).
- Average inventory is the typical value of stock on hand in the same period. The simple version is (beginning inventory + ending inventory) ÷ 2.
A higher number means stock moves faster. A lower number means stock sits on the shelf longer, which ties up cash. Both figures must be at cost and cover the same period. If you need a refresher on ratios in general, see how to calculate a ratio in Excel.
How to calculate inventory turnover in Excel#
Put each product on its own row, with COGS and the two inventory values in separate columns. This example covers one year:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | COGS | Begin inventory | End inventory | Turnover |
| 2 | Widgets | 120,000 | 18,000 | 22,000 | 6.0 |
| 3 | Gadgets | 84,000 | 30,000 | 26,000 | 3.0 |
| 4 | Cables | 54,000 | 4,000 | 5,000 | 12.0 |
| 5 | Mounts | 60,000 | 16,000 | 14,000 | 4.0 |
| 6 | Chargers | 48,000 | 7,000 | 8,000 | 6.4 |
In E2, type:
=B2/AVERAGE(C2:D2)AVERAGE(C2:D2) adds 18,000 and 22,000 and divides by 2, giving 20,000. Then 120,000 ÷ 20,000 = 6. Widgets sold through their average stock six times in the year.
Fill the formula down to row 6 (Windows: select E2:E6 and press Ctrl+D; Mac: ⌘D). Each row updates to its own cells. If you are new to building formulas from cell references, the lesson on your first formulas covers the basics.
How to round the turnover ratio#
Wrap the formula in ROUND so the result shows a clean, fixed number of decimals:
=ROUND(B2/AVERAGE(C2:D2),1)The ,1 keeps one decimal place. For Chargers, 48,000 ÷ 7,500 = 6.4, so the result is 6.4. ROUND changes the stored value, not just the display. That matters if other formulas use the result. The ROUND function guide explains the decimals argument, and the AVERAGE function guide covers how AVERAGE treats blank cells.
How to convert turnover into days of inventory#
Days of inventory tells you how long, on average, stock sits before it sells. Divide the days in the period by the turnover:
=ROUND(365/E2,0)Or calculate it straight from the inputs, which avoids using a rounded turnover:
=ROUND(AVERAGE(C2:D2)/B2*365,0)| Product | Turnover | Days of inventory |
|---|---|---|
| Widgets | 6.0 | 61 |
| Gadgets | 3.0 | 122 |
| Cables | 12.0 | 30 |
| Mounts | 4.0 | 91 |
| Chargers | 6.4 | 57 |
Gadgets hold about four months of stock, while Cables sell through in about a month. That is the kind of comparison managers ask for. For a quarter, use 91 or 92 days instead of 365, and use the quarter's COGS.
How to calculate a total turnover for all products#
For the whole business, divide total COGS by total average inventory. Don't take the average of the five ratios, because that gives a small product the same weight as a large one.
=SUM(B2:B6)/((SUM(C2:C6)+SUM(D2:D6))/2)Total COGS is 366,000. Total beginning and ending inventory are both 75,000, so the average is 75,000. The result is 4.88. The simple average of the five ratios would be 6.28, which overstates how fast stock moves overall.
Be careful with AVERAGE(C2:D6) here. It averages all ten cells and returns 15,000, which is the average per product, not the total. The guide to AVERAGE, MIN and MAX in Excel explains what AVERAGE includes.
How to use monthly inventory for a better average#
The two-point average can mislead when stock swings during the year, such as a big build-up before a holiday season. A better average uses every month-end balance.
List the 13 balances in one column (the end of last year, then the end of each month this year), for example in B2:B14. Then:
=AVERAGE(B2:B14)Use that result as the average inventory in the turnover formula. If you keep stock counts in a sheet already, an inventory tracker in Excel is a good place to store those month-end values.
Common mistakes with inventory turnover in Excel#
Most wrong answers come from mixing up inputs, not from the formula itself:
- Using ending inventory only.
=B2/D2ignores the start of the year. Use the average. - Mixing sales and cost. Sales at selling price divided by inventory at cost gives an inflated result. Keep both at cost.
- Mixing periods. Quarterly COGS divided by a yearly average inventory understates turnover. Use the same period for both.
- Dividing by zero. A new product with no stock gives #DIV/0!. Use
=IFERROR(ROUND(B2/AVERAGE(C2:D2),1),"No stock")to show a message instead. - Averaging ratios for a total. Divide totals instead, as shown above.
Inventory questions often come up in Excel skills for supply chain jobs and Excel skills for retail jobs, so it is worth being able to build this in under a minute.
Practise it#
The turnover formula is just AVERAGE and division, so the fastest way to get it right under pressure is practice. Practise AVERAGE, MIN and MAX in a live spreadsheet: you type each formula and it is checked instantly. Then try the exercise on this page and calculate the turnover for Chargers yourself.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Find the inventory turnover for Chargers (row 6). In E6, divide the COGS in B6 by the AVERAGE of the beginning and ending inventory (C6:D6), and ROUND the result to 1 decimal place.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Turnover | |||||
| 2 | ||||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 | ||||||
| 9 |
Tip: Put the division inside ROUND: =ROUND(B6/AVERAGE(C6:D6),1). The ,1 keeps one decimal place.
Practise this in a lesson
Frequently asked questions
What is the formula for inventory turnover ratio in Excel?
Divide cost of goods sold by average inventory. With COGS in B2 and beginning and ending inventory in C2 and D2, use =B2/AVERAGE(C2:D2). Wrap it in ROUND to show one decimal place.
How do I calculate average inventory in Excel?
Use AVERAGE on the beginning and ending values, such as =AVERAGE(C2:D2). For a more accurate yearly figure, average the 13 month-end balances, from the end of last year to the end of this year.
What is a good inventory turnover ratio?
It depends on the industry. Fresh food sells through stock much faster than furniture or machinery. Compare a product with its own past results and with similar products, not with a single fixed number.
How do I convert inventory turnover into days?
Divide the number of days in the period by the turnover. For a year, =365/E2 where E2 holds the turnover. A turnover of 6 is about 61 days of stock.
Should I use sales or cost of goods sold for inventory turnover?
Cost of goods sold is the usual choice, because inventory is valued at cost. Using sales gives a higher number. Either can work if you use the same method every time.