Inventory turnover ratio: formula and Excel example

The formula and its parts

• Inventory turnover = cost of goods sold (COGS) ÷ average inventory
• Average inventory = (opening inventory + closing inventory) ÷ 2
• Days in inventory (days inventory outstanding, DIO) = 365 ÷ inventory turnover, or average inventory ÷ COGS × 365

Both parts of the ratio are measured at cost. Inventory sits on the balance sheet at what the business paid for it, and COGS is that same cost moved to the income statement when the goods are sold. Dividing sales revenue by inventory mixes selling prices with purchase prices and makes the turnover look better than it is. In the example the shop's sales-based figure is 7.55 times against a true 4.53 times.

The average matters because inventory changes during the year. Power tools started the year at $160,000 and finished at $240,000. Using only the closing figure would understate the turnover; using only the opening figure would overstate it.

Step by step in Excel

Put one category per row with COGS in column B, opening inventory in C and closing inventory in D. Then:

1. Average inventory in E2: =(C2+D2)/2
2. Turnover in F2: =B2/E2, formatted with two decimals or as a multiple such as 6.00x
3. Days in G2: =365/F2
4. Fill the three formulas down to the last category.
5. For the total, sum COGS and average inventory first and then divide: =B7/E7. Do not average the five turnover ratios.

The last step is the one most often done wrong. The simple average of the five ratios is 6.5 times, but the shop as a whole turns its stock only 4.53 times (about 81 days), because the slow categories hold most of the inventory. A total ratio must be weighted by the money involved, which is exactly what dividing the totals does.

Worked example: inventory turnover for five product categories

A fictional hardware shop's cost of goods sold and its opening and closing inventory at cost (yellow) for one year. Average inventory, turnover and days are filled down; the block below compares other ways of calculating the ratio.

Worked example: inventory turnover for five product categories

	A	B	C	D	E	F	G
1	Category	COGS	Opening inventory	Closing inventory	Average inventory	Turnover (x)	Days in inventory
2	Garden tools	480,000	90,000	70,000	80,000	6.00x	60.8
3	Paint	360,000	40,000	50,000	45,000	8.00x	45.6
4	Power tools	600,000	160,000	240,000	200,000	3.00x	121.7
5	Fasteners	150,000	12,000	13,000	12,500	12.00x	30.4
6	Lighting	210,000	55,000	65,000	60,000	3.50x	104.3
7	Total shop	1,800,000	357,000	438,000	397,500	4.53x	80.6
8
9	Further checks						Value
10	Turnover on closing inventory only (total shop)						4.11x
11	Sales revenue at selling prices (total shop)						3,000,000
12	Turnover on sales instead of COGS (overstated)						7.55x
13	Inventory freed if power tools turned 6 times						100,000
14	Slowest-turning category						Power tools

Fictional figures for illustration only. This is a calculation guide, not accounting or business advice.

Reading the example

Fasteners turn 12 times a year, so a box of screws sits on the shelf for about a month. Paint turns 8 times and garden tools 6 times. Lighting (3.5 times, 104 days) and above all power tools (3.0 times, 122 days) are slow. Power tools alone hold $200,000 of the shop's $397,500 average inventory, half of the money in stock, while producing a third of the cost of goods sold.

Turnover converts directly into cash. If power tools turned 6 times like garden tools, the shop would need only $600,000 ÷ 6 = $100,000 of average stock for the same sales, and $100,000 of cash would be free for other uses. That is the number to weigh against the risk of running out of popular models.

Variants: 13-month averages, units and GMROI

Seasonal businesses distort the two-point average. A garden centre may hold three times as much stock in April as in December. A better average uses the twelve month-end balances plus the opening balance: put them in a column and use =AVERAGE(B2:B14) as the denominator. Many accounting systems can export month-end stock values directly.

For a single product, turnover can also be measured in units: units sold ÷ average units on hand. This removes price changes and is handy for reorder planning. For a quick view of profitability per dollar of stock, retailers use GMROI, gross margin ÷ average inventory at cost. A category with low turnover can still earn its place if its margin is high, so read turnover and margin together.

What is a good inventory turnover?

There is no single target, because it depends on what is sold. Fresh food may turn 30 times a year or more, fashion and consumer electronics often 4 to 8 times, and spare parts, jewellery or heavy machinery sometimes only 1 to 3 times. Compare a business with its own history and with similar businesses, and compare categories inside the same business, as in the example.

Higher is not automatically better. Very high turnover can mean the shelves are often empty and customers leave without buying. The goal is the lowest stock that still meets the service level the business wants. The order-size side of that trade-off is covered in the EOQ formula guide, and days in inventory is one of the three parts of the cash conversion cycle.

Common mistakes

• Using sales instead of COGS. Revenue includes the margin, inventory does not. The ratio comes out too high, by the markup factor.
• Averaging the ratios of several categories. Divide total COGS by total average inventory. A simple average gives small, fast categories the same weight as large, slow ones.
• Using closing inventory only. When stock grew during the year, closing inventory understates the turnover; when it fell, it overstates it.
• Mixing periods. Quarterly COGS divided by average inventory gives a quarterly turnover. Multiply by 4 or use annual COGS before comparing with annual benchmarks, and use 91 or 92 days for quarterly DIO.
• Comparing different valuation methods. FIFO and weighted-average cost give different inventory and COGS figures when prices change, so turnover ratios of two businesses may not be like for like.