How to Calculate Inventory Turnover in Excel

Calculate inventory turnover and days on hand per category, then the store-wide turnover — and see which stock sits on the shelf.

You will learn to

• Divide cost of goods sold by average inventory
• Convert turnover into days on hand
• Calculate an overall turnover correctly from totals

How it works

Inventory turnover tells you how many times you sold through your average stock in a year: cost of goods sold ÷ average inventory, where average inventory is (beginning + ending) ÷ 2. A turnover of 4.8 means stock is replaced almost five times a year.

Divide 365 by turnover — or calculate 365 × average inventory ÷ COGS — to get days on hand, which is easier to explain: about 76 days for a turnover of 4.8. For the whole business, divide total COGS by total average inventory; averaging the category turnovers gives the wrong answer because big and small categories would count equally.

=D2/AVERAGE(B2:C2)