Heavy equipment spare parts procurement analytics
A contractor running fleets at four remote sites spends tens of millions of dollars a year on hydraulics, engines, undercarriage, tyres and wear parts. I analysed two years of purchasing to answer the questions a procurement manager actually asks: where the money goes, which suppliers can be trusted, what urgent buying really costs, where the process stalls, and how much stock of critical parts to keep.
- Data
- Simulated ERP extract: 6,591 PO lines, 14 suppliers, 420 parts, 4 sites (2024–2025)
- Tools
- SQL (DuckDB), Python, pandas, SciPy
- Methods
- Spend and ABC analysis, supplier scorecard, 3-way match, safety stock
- Background
- Built on my experience with operational and parts data in the heavy-equipment industry
Procurement data is commercially sensitive, so this dataset is simulated. It mirrors an ERP (SAP MM-style) export: supplier and part masters, requisition, PO and goods-receipt dates, invoices and daily part usage. The analysis was written as if the patterns were unknown.
Where the money goes
Engine parts are the biggest category at 25% of spend, followed by tyres and rims (17%) and hydraulics (17%). Authorised distributors supply 44% of spend, OEMs 35% and aftermarket suppliers 21%.
Spend is highly concentrated: just 98 of 420 part numbers (23%) make up 80% of spend. These A-class parts deserve negotiated contracts, close supplier management and careful stock planning. The long tail of C-class parts (46% of part numbers, only 5% of spend) should be bought as simply and cheaply as possible.
ABC (Pareto) curve of part spend
Cumulative share of spend by part number, ranked from highest to lowest
Spend by category and supplier type
USD, 2024–2025
Which suppliers can be trusted
Delivery reliability vs lead-time variability
Each bubble is a supplier; bubble size = spend. Top-left is best.
Each supplier gets a composite score out of 100: on-time-in-full (OTIF) 40%, quality (reject rate) 30% and lead-time reliability 30%. Price is compared separately against the contract price for the same part, on planned orders only.
Authorised distributors lead. The best scores 89, with 93.8% OTIF, 0.75% rejects and lead times that vary by less than 2 days.
Cheap is not cheap. Aftermarket suppliers are only 0–2.5% below contract price, yet they deliver on time and in full just 60–85% of the time and reject up to 5.8% of parts. The two weakest score 45–46.
OEM imports are reliable in quality but slow. The Singapore OEM hub averages 32 days with ±7.4 days variability. That uncertainty is what forces buffer stock for critical engine parts.
WITH base AS (
SELECT supplier_id, supplier_name, supplier_type,
COUNT(*) AS po_lines,
SUM(po_value) AS spend,
AVG(CASE WHEN on_time AND in_full THEN 1 ELSE 0 END) AS otif,
AVG(lead_time_days) AS avg_lead_time,
STDDEV_SAMP(lead_time_days) AS sd_lead_time,
SUM(qty_rejected) / NULLIF(SUM(qty_received), 0) AS reject_rate,
AVG(CASE WHEN NOT urgent THEN unit_price_usd / contract_price END) AS price_vs_contract
FROM po GROUP BY ALL
)
SELECT supplier_id, supplier_name, supplier_type, po_lines,
ROUND(spend, 0) AS spend_usd,
ROUND(100 * otif, 1) AS otif_pct,
ROUND(avg_lead_time, 1) AS avg_lead_time_days,
ROUND(sd_lead_time, 1) AS sd_lead_time_days,
ROUND(100 * reject_rate, 2) AS reject_pct,
ROUND(price_vs_contract, 3) AS price_vs_contract,
-- composite score (0-100): delivery 40, quality 30, lead-time reliability 30
ROUND(40 * otif
+ 30 * GREATEST(0, 1 - reject_rate / 0.06)
+ 30 * GREATEST(0, 1 - sd_lead_time / 12), 1) AS score
FROM base
ORDER BY score DESC;Supplier scorecard
Sorted by composite score. Price index = price paid / contract price on planned orders.
| Supplier | Type | Spend | OTIF | Lead time | ± days | Rejects | Price index | Score |
|---|
Urgent buying is a planning problem
Share of order lines placed as urgent
By site
Sulawesi (26%) and Papua (23%) buy urgently twice as often as Kalimantan and Sumatra (~12%). Urgent lines pay 10–18% more and carry air-freight expediting, and almost half of them go to a non-contract supplier.
What urgent and off-contract buying costs
Price paid above contract + expedite charges, USD, 2 years
Across all sites this adds up to about USD 1.37M over two years, money spent on speed and convenience rather than on parts.
Where the process slows down
Days from requisition to approved PO
Planned (non-urgent) orders, by PO value. Hover for the 90th percentile.
POs above USD 5,000 need a second approval and wait a median 7 days (p90: 12), against 2 days for smaller orders. For a critical part, those 5 extra days can mean 5 extra days of a machine standing still.
3-way match: PO vs goods receipt vs invoice
6,591 supplier invoices
3.3% of invoices don't match: prices above the PO and duplicate billing. Paid without checks, that's USD 370k of overbilling in two years. Checking data against its source is exactly the habit this catches.
How much stock of critical parts to hold
For the 45 critical A- and B-class parts, I calculated a reorder point with safety stock at a 95% service level, using each part's daily usage and the lead time of its contract supplier:
ROP = d × L + z × √(L × σd² + d² × σL²)
Here d is average daily usage, L the average lead time, and σ their variability. Reordering at average lead-time demand, with no buffer, runs out of stock in roughly half of replenishment cycles. The recommended buffer cuts that to about 5%, for roughly USD 156k of one-off stock across all 45 parts. That's small next to the cost of a haul truck or excavator waiting for a part.
Recommended reorder points, top 10 by buffer value
Units. Current = average demand during lead time; recommended adds safety stock.
| Part | Category | Use/day | Lead time | Current ROP | New ROP | Buffer value |
|---|
Savings identified, USD per year
Total about USD 522k, 2.0% of annual spend
Savings and how they were sized
Off-contract buying (~USD 194k/yr): the premium paid above the contract price on planned, non-urgent orders bought from a non-contract supplier. This is fully avoidable through purchasing discipline.
Invoice checks (~USD 185k/yr): overbilling found by the 3-way match, recovered if every invoice is matched before payment.
Urgent orders (~USD 144k/yr): the premium saved if every site cut its urgent share to the best site's level (11.5%). This is conservative, because it assumes no site does better than today's best.
Recommendations
Enforce contract buying
Use catalogue-based ordering that defaults to the contract supplier and requires a reason for any exception. Review off-contract spend monthly by buyer and site.
Plan parts with maintenance
Kit parts for scheduled PM and set reorder points with safety stock for the 45 critical parts, starting with Sulawesi and Papua, where urgent buying is twice the norm.
Speed up approvals for contract items
Auto-approve contract-priced POs within budget, or run approvals in parallel, to remove the 5-day wait on orders above USD 5,000.
Manage suppliers by score, not price
Concentrate volume with the top-scoring distributors. Put suppliers below 70% OTIF or above 5% rejects on an improvement plan, and automate the 3-way match before every payment.