Inventory turnover: formula, days of inventory and a worked example

By Killian, founder · Updated 5 November 2026 · Markdown

Inventory turnover = cost of goods sold ÷ average inventory, both at cost and for the same period, where average inventory is (opening stock + closing stock) ÷ 2. Days of inventory (also called days inventory outstanding, DIO) turns that into time: days in the period ÷ turnover. A turnover of 1.52 in a 92-day quarter means stock sits for about 61 days on average and turns about 6 times a year. Below is a worked example on a real-looking quarter of sales and stock, with every answer per product, the spreadsheet formulas, three mistakes that inflate the ratio, and how to ask an AI agent the same questions across both sheets.

The example data

A small bike-parts wholesaler that sells to repair shops, third quarter (1 July to 30 September 2026, 92 days), in two sheets:

Sales: one row per order line (35 rows), exported from the shop or invoicing tool. Columns order_id, date, month, sku, quantity, revenue, cost (the cost of the goods on that line).

Products: one row per product (8 rows), exported from the stock system. Columns sku, product, unit_cost, lead_time_days (how long the supplier takes to deliver), stock_start and stock_end (units on hand on 1 July and 30 September), value_start and value_end (the same stock at cost).

The Products sheet:

Made-up data, small enough to check by hand. The winter gloves didn’t sell once in the summer quarter, which matters below.

The four questions, answered

1. How fast does the stock turn overall?

Cost of goods sold in Q3 is the sum of the cost column: 6,230 (on 14,160 of revenue). Average inventory is (4,670 + 3,540) ÷ 2 = 4,105 at cost.

• Turnover for the quarter: 6,230 ÷ 4,105 = 1.52
• Days of inventory: 92 ÷ 1.52 = 60.6 days
• Annualized: 1.52 × 365 ÷ 92 = 6.0 turns a year

There is no “good” turnover in general: groceries turn weekly, bike computers a few times a year. Compare a product with itself over time, and products with similar ones.

2. Which products turn fast and which sit?

The comfort saddle holds 720 of stock for 360 of cost sold: half a year of supply. The light set looks slow too, but its sales went from 5 units in July and 5 in August to 30 in September, as the days got shorter; a quarter-wide ratio hides a seasonal product picking up.

3. What is dead stock?

Dead stock is stock with no sales in the period. Here that is the winter gloves: 480 at cost, 11.7% of average inventory and 13.6% of the stock on 30 September, earning nothing since July. In summer that may be planned (they’ll sell from November); a product with no sales for a full year is the one to discount or return.

Note that dead stock never shows up in a report built from the sales export, because it has no sales rows. You have to start from the product list.

4. What needs reordering?

Days of cover = stock on hand ÷ average units sold per day. If it’s shorter than the supplier’s lead time, the product runs out before a new order arrives. The reorder point is units per day × lead time (plus whatever safety stock you want to hold).

Two products need action. The GPS computer has 10 days of cover against a 21-day lead time: it is already below its reorder point and will be out of stock for about 11 days unless it’s ordered now. The tyres have 34.5 days of cover against 30: they reach the reorder point within a week. The light set has 115 days of cover at the quarter’s average, but only 50 at September’s rate of 1 a day, which is why the rate you use matters for seasonal products.

The formulas in Excel or Google Sheets

With the sales on a sheet called Sales (sku in column D, quantity in E, cost in G) and the products on Products (sku in A, lead time in D, stock_end in F, value_start in G, value_end in H), one row per product:

• COGS per product: =SUMIFS(Sales!$G:$G, Sales!$D:$D, $A2). Over the whole sheet: =SUM(Sales!G:G).
• Average inventory: =(G2+H2)/2.
• Turnover: =IFERROR(COGS/AvgInventory, 0), with the cell references of the two columns above.
• Days of inventory: =IF(Turnover=0, "no sales", 92/Turnover), with 92 the days in the period.
• Units per day and days of cover: =SUMIFS(Sales!$E:$E, Sales!$D:$D, $A2)/92, then =F2/UnitsPerDay.
• Reorder flag: =IF(DaysOfCover < D2, "order now", "").

Build these on the Products sheet, not on the sales export: every product gets a row, including the ones that didn’t sell.

Three mistakes that inflate inventory turnover

1. Sales at price over inventory at cost. 14,160 of revenue ÷ 4,105 of stock at cost gives 3.45, more than twice the real 1.52, and 26.7 days of inventory instead of 60.6. Revenue includes the margin; stock is valued at cost. Use COGS on top, or value both at selling price, but never mix.

2. Closing stock instead of average stock. 6,230 ÷ 3,540 (30 September only) gives 1.76 and 52.3 days. Stock fell during the quarter, so the closing figure flatters the ratio. It’s worse per product: the GPS computer ended with one unit, so COGS ÷ closing stock gives 9.0 where the average gives 3.6. Use (opening + closing) ÷ 2, or the average of month-end counts if you have them.

3. Looking up stock onto every sales row. A common shortcut is an XLOOKUP of each product’s stock value onto the sales sheet, then a pivot on the sales sheet. Summed there, closing stock comes to 14,010 instead of 3,540, because each product’s stock is counted once per order line (inner tubes nine times). And even counted once per product, a sales-side report shows 3,060, because the gloves have no sales rows and drop out. Stock totals belong on the product list; sales totals on the sales list.

Ask an agent instead of rebuilding the sheet

Each new question (“which products cover less than their lead time?”, “turnover by month?”, “what if September’s rate holds?”) means new columns or another pivot, and pasting both sheets into a chat stops working once the export has tens of thousands of lines (the token cost calculator shows how fast a pasted sheet fills the context window). datatomcp keeps both sheets in one workspace and lets any agent (Claude, ChatGPT, Cursor, an n8n flow) query them, so the rows stay with us and only the question and the answer go through the chat.

1. Add both sheets. Sign in at app.datatomcp.com, open Add data, and upload the two CSVs or paste their Google Sheets links. A Google Sheet stays in sync, so next quarter’s sales and stock show up without a re-upload.

2. Check the link. datatomcp finds sku in both sheets, sees that it is unique in Products and that every SKU in Sales is found there (100%), and confirms the link on its own. You’ll see it on the relationship map.

3. Connect your agent (any MCP client, or Claude step by step) and ask in plain words.

We ran these questions through datatomcp’s query engine on the two sheets above; every total on this page is its output. Sales per product is one aggregate call on Sales grouped by sku, summing quantity, cost and revenue, with products.value_start, products.value_end, products.stock_end and products.lead_time_days from the linked sheet. The turnover, days and reorder points are the agent dividing and multiplying those numbers.

What the results say on their own:

• Linked stock is counted once per product, not once per order line. Summing products.value_end from the sales side gives 3,060 (one per product that sold), never the 14,010 of mistake 3.
• Dead stock needs the product list. The same call returns 7 products, because the gloves have no sales rows; a sum on Products gives the full 4,670 and 3,540 and 8 products. Ask “which products had no sales this quarter?” and the agent compares the two.
• Seasonality is one more question. “Light set units by month” is the same call grouped by month with a filter on sku: 5, 5, 30.

FAQ

What is the inventory turnover formula? Cost of goods sold ÷ average inventory at cost, for the same period. Average inventory is (opening + closing inventory) ÷ 2. Annualize a quarterly figure by multiplying by 365 ÷ days in the quarter.

How do I calculate days of inventory (DIO)? Days in the period ÷ inventory turnover, or equivalently average inventory ÷ COGS × days in the period. Here: 4,105 ÷ 6,230 × 92 = 60.6 days.

What is a good inventory turnover ratio? It depends on the product: fresh food turns dozens of times a year, spare parts and equipment a few times. Track each product against its own history and against similar products, and look at days of cover against lead time to see what will run out.

Should I use sales or COGS for inventory turnover? COGS, because inventory is valued at cost. Sales over inventory at cost overstates turnover by the margin: 3.45 instead of 1.52 in the example above.

How do I find dead stock in a spreadsheet? Start from the full product list and add the units sold in the period with SUMIFS; products with 0 are dead stock for that period. A pivot of the sales export can’t show them, because they have no sales rows.

Can ChatGPT or Claude calculate inventory turnover? Yes, if they compute it rather than read it: with a small file uploaded, the chat runs code and can be right. With a full sales export, a separate stock sheet and the same questions every month, connect the sheets so the agent sends a query and gets back counted totals: how datatomcp works with Claude.

Create a free account and try it on your own sales and stock. Free covers 3 datasets of up to 100,000 rows each.

More: Sales analysis: a worked example by month, product, region and customer · Budget vs actual: variance formula and the double-counting trap · Test an AI data tool on linked sheets