What is the inventory turnover ratio formula?
Inventory turnover ratio = annual issues at cost ÷ average inventory value. It tells you how many times a year your stock value is used and replaced. Days on hand = 365 ÷ turnover.
Retail textbooks use cost of goods sold on top. A mine store does not sell anything, so the equivalent is the value of stock issued to work orders and cost centres over 12 months, at the same cost basis your stock is valued at (usually weighted average cost).
| Term | What it means in a mine store | Where it comes from |
|---|---|---|
| Annual issues at cost | Value of stock consumed by maintenance and production in 12 months, net of returns, excluding transfers | Inventory transaction export |
| Average inventory value | Average of 13 month end stock values (or opening plus closing ÷ 2 if that is all you have) | Stock on hand snapshots or the general ledger inventory account |
| Days on hand | 365 ÷ turnover. How long current stock would last at current usage | Calculated |
| Line level turnover | Annual net issue quantity ÷ average quantity on hand, for one stock code | Transaction export plus stock snapshots |
Use 13 month end values for the average if you can. Opening plus closing ÷ 2 is fine on paper, but if a shutdown or a big capital spares delivery lands near either end of the year, two points will give you a distorted average.
What does inventory turnover look like for a mine site store?
Worked example with made-up figures. A site store has an average stock value of $44.0M over 12 months (opening $42.0M, closing $46.0M). Net issues at cost over the same 12 months were $30.8M.
- Average inventory = ($42.0M + $46.0M) ÷ 2 = $44.0M.
- Turnover = $30.8M ÷ $44.0M = 0.70 times a year.
- Days on hand = 365 ÷ 0.70 = about 521 days.
On its own, 0.70 tells a GM very little. Split by category and the picture changes:
| Category (example) | Average stock | Annual net issues | Turnover | Days on hand |
|---|---|---|---|---|
| Fast moving consumables (filters, wear parts, GET) | $6.0M | $18.0M | 3.00 | 122 |
| Components and repairables | $15.0M | $9.0M | 0.60 | 608 |
| Critical and insurance spares | $14.0M | $0.8M | 0.06 | 6,387 |
| Slow and non moving | $9.0M | $3.0M | 0.33 | 1,095 |
| Total | $44.0M | $30.8M | 0.70 | 521 |
Take the insurance spares out and the rest of the store turns $30.0M ÷ $30.0M = 1.0 times a year, exactly 365 days. That is a much more honest number to manage against, because nobody should be trying to make an insurance spare turn.
The table also shows where the money sits. The $9.0M of slow and non moving stock is the first place to look for working capital that could be released. The dead stock guide covers how to work through it line by line.
What is a good inventory turnover ratio for mining spares?
There is no single good number for a mine store. Turnover depends on the mix of consumables, components and insurance spares you hold, how remote the site is and how long your lead times are. Set a target per category and watch the trend.
- Consumables: should turn several times a year. If they don't, min and max levels are probably set too high. See how to set min max levels.
- Components and repairables: expect lower turns. Check that rotables in the repair loop are tracked separately so they don't drag the number down.
- Critical and insurance spares: leave them out of the turnover target entirely. Review them on risk, not usage. See critical and rotable spares.
- Slow and non moving: the goal is a shrinking value, not a higher turnover.
Compare the same site against itself over time, on the same rules. Comparing two sites is only fair if both use the same treatment of returns, transfers and insurance spares, which is rarely the case.
What mistakes make mining inventory turnover numbers wrong?
Most turnover errors on mine sites come from counting movements that are not real consumption. Returns, transfers and reversals all look like issues in a raw transaction export.
1. Counting returns as usage. Maintenance draws parts for a job, the job changes, the parts come back. If you count the issue but ignore the return, usage is overstated. Example: a line issued 40 units in a year and 12 came back. Average on hand was 14. Gross turnover is 40 ÷ 14 = 2.86. Net turnover is 28 ÷ 14 = 2.00. On an international tier 1 mining operator's stock level work, one rule change (netting returns off usage) took agreement on disputed items from 73% to 98.4%.
2. Counting transfers between warehouses as usage. A transfer from the central warehouse to a satellite store is a movement, not consumption. Count usage where the part is actually issued to a job.
3. Mixing insurance spares into the target. As the example above shows, $14.0M of insurance spares can drag a 1.0 store down to 0.70.
4. Valuing rotables wrongly. A rebuilt component returned from repair may come back at repair cost or at full value depending on how your ERP handles it. Be consistent, or turnover jumps every time a big rotable moves.
5. Using two snapshots. A shutdown or a capital spares delivery near year end will swing an opening plus closing average. Use 13 month ends.
6. Letting lumpy demand set the trend. One large issue of an expensive component can double a category's turnover for a year. Look at the number of issue events, not only the value.
Example of the combined effect: the site above had gross issues of $33.9M in its raw export, including $1.9M of returns and $1.2M of transfers to another warehouse. Using the raw figure gives 33.9 ÷ 44.0 = 0.77. The real figure is 0.70. The store looked about 10% better than it was.
How do you calculate inventory turnover in Excel or from your ERP?
You need two exports: monthly stock on hand values and 12 months of inventory transactions with a transaction type. Then net out returns, exclude transfers and divide.
- Export stock on hand value by stock code and category at each of the last 13 month ends. Put them in a sheet called
Stock. - Export 12 months of inventory transactions with stock code, category, transaction type, quantity, value and warehouse. Put them in a sheet called
Trans. - Map every transaction type to one of: issue, return, transfer, receipt, adjustment. Your ERP will have more types than you expect. Check the list with someone in the store.
- On a summary sheet, list your categories in column A.
- Average stock in column B:
=AVERAGEIFS(Stock!C:C, Stock!B:B, A2)if each row is one category month, or sum the monthly totals and divide by 13. - Issues in column C:
=SUMIFS(Trans!F:F, Trans!B:B, A2, Trans!D:D, "issue"). - Returns in column D:
=SUMIFS(Trans!F:F, Trans!B:B, A2, Trans!D:D, "return"). Make sure returns are positive values here. - Turnover in column E:
=IFERROR((C2-D2)/B2, 0). Days on hand in column F:=IFERROR(365/E2, "").
Transfers are left out because step 6 only counts the issue type. For line level turnover, do the same with stock code in column A and quantities instead of values.
The hard part is not the formula. It is getting the transaction type mapping right and keeping it the same every month.
How does Source Digital automate inventory turnover reporting?
Source Digital builds the calculation once, inside your own data platform, with the returns, transfers and insurance spare rules agreed with your stores team. It then re-runs every month on the same rules.
- Built in your environment: it runs in your own Azure, Databricks or Snowflake, with read-only access to your ERP data.
- Deterministic maths: the turnover figures are calculated by fixed rules, not by AI. The AI only explains the result in plain English.
- Every number links back: click a category and you see the stock codes, then the transaction lines behind each one.
- Questions in Teams: an assistant in Microsoft Teams answers questions like "which consumables turned less than once last year?" At an international tier 1 mining operator, a commercial assistant built this way answers from millions of purchasing, receipt, payment and stock rows in about a minute. See the Teams commercial agent case study.
Getting started is a free 15 minute call with Tristan, or a quick enquiry. No obligation and no pitch. He asks what you run and what is costing you, then tells you straight whether there is something worth fixing. Work is charged at a consulting day rate. The price depends on your sites, systems and data, so you talk first, then Tristan sets out what he would build, how many days it takes and what it costs. See how the pieces fit together on the AI for mining supply chain page.
