Blog · Forecast and pipeline · Industrial distributors
Service parts demand is often intermittent, so weekly forecast errors need to be read alongside stock and service criticality. Work through a parts-usage example, spreadsheet formulas and inventory checks by part and site.
Service parts management, demand forecasting and inventory control start with comparing actual usage against the forecast that existed before the repair requests arrived. Review those misses by part and site, then check usable inventory, lead time and the consequence of a stockout. A quiet usage history does not make a critical spare unnecessary.
Use four exports: the dated parts forecast, inventory issues, stock movements and the part master. Keep part number, site, week, unit of measure, forecast version and quantity. Add unit inventory cost, supplier lead time, equipment supported and the service owner's criticality classification.
Keep forecast versions separate. Comparing an updated forecast with usage already known at the update date hides the original planning error. Use an inventory issue date for the stock reconciliation and record request dates separately when investigating unmet demand.
Research on intermittent demand describes long runs of zero demand interrupted by nonzero events. Preserve genuine zero-usage weeks in the export. A missing site extract is unknown, while a confirmed week without issues is zero.
If the decision concerns technician workload, use service demand forecasting; parts usage is a different planning unit. Known installation and overhaul requirements can be linked to project demand forecasting without treating all repair demand as scheduled work.
Suppose a Texas industrial distributor reviews valve kits at its Dallas site. Below is one frozen forecast covering eight completed weeks. Quantities are kits; all weeks are confirmed complete.
| Week | Forecast kits | Actual kits | Forecast minus actual | Absolute error |
|---|---|---|---|---|
| 1 | 1 | 0 | 1 | 1 |
| 2 | 1 | 0 | 1 | 1 |
| 3 | 1 | 3 | -2 | 2 |
| 4 | 1 | 0 | 1 | 1 |
| 5 | 1 | 0 | 1 | 1 |
| 6 | 1 | 0 | 1 | 1 |
| 7 | 1 | 2 | -1 | 1 |
| 8 | 1 | 0 | 1 | 1 |
| Total | 8 | 5 | 3 | 9 |
To assemble actuals in Excel, name the issues worksheet Usage, with part in A, site in B, week identifier in C and issued quantity in D. Put the required part and site in H2 and I2 on the review sheet. With week in A2, enter in C2 and fill down:
=SUMIFS(Usage!$D$2:$D$1000,Usage!$A$2:$A$1000,$H$2,Usage!$B$2:$B$1000,$I$2,Usage!$C$2:$C$1000,A2)
Microsoft documents SUMIFS as summing values that meet multiple criteria. Use a source range that includes every exported row, and verify completeness before interpreting a zero result. Covirage is not affiliated with Microsoft.
With the displayed table in A1:E10, enter these formulas in D2 and E2, then fill through row 9:
=B2-C2
=ABS(D2)
The forecast exceeded usage by 8 - 5 = 3 kits. Absolute weekly errors sum to 9 kits because underforecast weeks do not cancel overforecast weeks.
Signed bias = Sum of forecasts - Sum of actual usage
Mean absolute error = Sum of absolute period errors / Number of periods
Calculate zero-week share, mean absolute error and aggregate weighted absolute percentage error respectively:
=COUNTIF(C2:C9,0)/COUNT(C2:C9)
=SUM(E2:E9)/COUNT(C2:C9)
=IF(SUM(C2:C9)=0,"Not defined",SUM(E2:E9)/SUM(C2:C9))
Six of eight weeks had zero usage: 75%. Mean absolute error is 9 / 8 = 1.125 kits, shown as 1.13. Aggregate error is 9 / 5 = 180%; signed bias relative to actual usage is 3 / 5 = 60%. Percentages here are exact; only the displayed mean absolute error is rounded.
Do not compute a percentage error separately for each zero-demand week. Hyndman's review of intermittent-demand accuracy measures explains why some measures become undefined or infinite. If total usage is also zero, retain error in units and mark the percentage undefined.
Use forecast bias by SKU and site for persistent directional misses across periods. One short review window cannot prove that a forecasting method is systematically biased.
The same distributor supplies these stock movements for the review window. Assume no transfers, returns or adjustments in this example.
| Part / site | Opening units | Receipts | Issues | Closing units | Unit cost (USD) | Closing value (USD) |
|---|---|---|---|---|---|---|
| Valve kit / Dallas | 10 | 4 | 5 | 9 | 200 | 1,800 |
| Seal pack / Dallas | 40 | 0 | 0 | 40 | 15 | 600 |
| Motor / Phoenix | 6 | 2 | 4 | 4 | 100 | 400 |
| Total value | 2,800 |
On a separate Stock sheet with these columns in A:G, closing units in E2 and closing value in G2 are:
=B2+C2-D2
=E2*F2
Fill through row 4. Each computed closing quantity must match the corresponding stock snapshot. The valve issues must also equal the weekly usage total: 5 kits. Closing values reconcile as 1,800 + 600 + 400 = $2,800.
The seal packs have no usage during the review window and hold $600. Flag that value for review; it is not automatically excess. The service owner must check supported equipment and substitution options.
Dallas valve usage averaged 5 / 8 = 0.625 kits per week, so nine closing kits represent 9 / 0.625 = 14.4 weeks at that historical average. That ratio is descriptive: a single repair can consume several kits. Review supplier timing through purchase forecasting, and keep the service decision with the inventory and maintenance owners.
Bring your dated parts forecast, usage export and stock movements; Covirage maps the columns, and its tools compute and check the comparisons by part and site. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Covirage for industrial distributors for the supporting industry context.
Repair events can produce long stretches without usage followed by a large requirement. Equipment age, maintenance schedules and substitutions also change demand. A low weekly average therefore says little about the quantity needed for one repair or the cost of being unable to complete it.
Yes. A part may support essential equipment even when failures are rare. Check its installed equipment base, service commitments, supplier lead time and approved substitutes before treating no recent usage as a reason to remove stock. Slow movement and obsolescence are different findings.
Keep the forecast interval appropriate to the planning decision, and preserve that interval when measuring error. Weekly reviews expose timing misses; longer totals help assess cumulative demand. Evaluate stock exposure over the supplier lead time as well, rather than choosing an interval solely to improve an accuracy score.
Separate serviceable returns from failed cores and parts awaiting inspection. Record a return as usable supply only when it is available for issue under your inventory policy. Otherwise a physical count can overstate the inventory that can actually satisfy a service request.