How fast is each product's stock turning over?
By the Analistable team · Updated · 3 min read
Join units sold (from the sales export) to opening and closing stock (from the stock report) on SKU. Turnover = units sold ÷ average stock for the period, and days of cover = average stock ÷ daily sales. In September's example, MUG-01 turned 1.53 times (about 20 days of cover) while POT-03 has roughly 190 days of stock — a slow mover.
Part of our guide: How to answer questions across multiple spreadsheets
The data
| SKU | Opening stock | Closing stock | Units sold |
|---|---|---|---|
| MUG-01 | 300 | 236 | 410 |
| TEA-02 | 150 | 120 | 95 |
| POT-03 | 40 | 36 | 6 |
Formulas
Units sold =SUMIFS(Sales[Units], Sales[SKU], [@SKU])
Average stock =([@Opening] + [@Closing]) / 2
Turnover =[@[Units sold]] / [@[Average stock]]
Days of cover =[@[Average stock]] / ([@[Units sold]] / 30)SELECT s.sku, sa.units,
(s.opening + s.closing) / 2.0 AS avg_stock,
ROUND(sa.units / ((s.opening + s.closing) / 2.0), 2) AS turnover
FROM stock s
JOIN sales sa ON sa.sku = s.sku;| SKU | Units sold | Average stock | Turnover (month) | Days of cover |
|---|---|---|---|---|
| MUG-01 | 410 | 268 | 1.53 | 19.6 |
| TEA-02 | 95 | 135 | 0.7 | 42.6 |
| POT-03 | 6 | 38 | 0.16 | 190 |
Reading the numbers
- Monthly turnover × 12 approximates annual turnover (MUG-01 ≈ 18 times a year).
- Days of cover tells purchasing when to reorder: MUG-01 runs out in about three weeks without a delivery.
- Use cost value (units × unit cost) instead of units to get financial inventory turnover; with constant costs the ratio is the same.
- Products with no sales row at all won't appear in an inner join — use a left join from stock to catch stock that didn't sell.
Ask it in Analistable: “Which SKUs have more than 90 days of cover based on September sales?” Checking stock levels first: reconciling a stock count with inventory records.
In Google Sheets
=ARRAYFORMULA(IF(A2:A = "", , SUMIF(Sales!A2:A, A2:A, Sales!B2:B) / ((B2:B + C2:C) / 2)))With SKU in column A and opening and closing stock in B and C, this returns monthly turnover per SKU.
The same numbers by value
| SKU | Unit cost | Cost of goods sold | Average stock value | Share of stock value | Share of COGS |
|---|---|---|---|---|---|
| MUG-01 | 4 | 1640 | 1072 | 41.8% | 70.8% |
| TEA-02 | 6 | 570 | 810 | 31.6% | 24.6% |
| POT-03 | 18 | 108 | 684 | 26.7% | 4.7% |
| Total | 2318 | 2566 | 100% | 100% |
Overall turnover by value is 2,318 ÷ 2,566 = 0.90 for the month. POT-03 ties up over a quarter of the money in stock but produces under 5% of the cost of sales — the units view understates how much it matters. Per SKU, turnover is the same in units or value when unit cost is constant.
Reorder point from days of cover
Daily sales =[@[Units sold]] / 30
Reorder point =[@[Daily sales]] * [@[Lead time days]] + [@[Safety stock]]With a 14-day supplier lead time and no safety stock, MUG-01 (13.7 units a day) needs reordering when stock falls to about 191 units. Its closing stock of 236 is close to that point.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Turnover extremely high | Opening or closing stock near zero (stock-out) | Average more stock snapshots, e.g. weekly |
| SKU missing | No sales row, inner join drops it | Left join from the stock report |
| Sales SKU doesn't match stock SKU | Variants or bundles sold under another code | Map bundles to component SKUs first |
| Units sold include returns | Gross sales exported | Use net units (sold − returned) |
Repeat monthly
Save each month's stock snapshot with its date, stack the sales exports with a Month column, and calculate turnover per SKU and month. A rising days-of-cover trend on one SKU is the early warning for dead stock. Excel 2019 users can replace SUMIFS-based lookups with a pivot table on the stacked sales. Background: joining spreadsheets on a common column.
Frequently asked questions
- How do I calculate inventory turnover in Excel?
- Divide units sold (or cost of goods sold) in the period by average inventory, the average of opening and closing stock.
- What are days of cover?
- How many days current stock would last at the current sales rate: average stock ÷ daily units sold.
- Why are some products missing from my result?
- They had no sales, so an inner join drops them. Use a left join from the stock report.
- Should I calculate turnover in units or value?
- Value (cost of goods sold ÷ average stock value) for finance; units for purchasing decisions. Per SKU they match when unit cost is constant.
- How many stock snapshots do I need?
- Opening and closing is enough for a month; weekly snapshots give a fairer average when stock swings a lot.
- What is a good turnover rate?
- It depends entirely on the product and sector. Compare SKUs with each other and over time.