Warranty Cost Forecast Model in Excel | Vose Software

ModelRisk example · Manufacturing

Warranty Cost Forecast

What will this year's sales cost in warranty claims, and when? Failures run off through a two-year warranty.

  • Complexity: ★★★★★
  • Free Excel workbook (MF-05)
  • Needs ModelRisk: free trial

From the model’s saved run

What will warranty cost?

This year's sales, over the whole warranty.

MeasureUnit5th pctMedian95th pctMean
Warranty costUS$k51.368.788.969.3
Warranty cost per toolUS$3.945.256.745.29
Warranty claimsclaims288382493385
MeasureUnitValue
Expected cost, calculated directlyUS$k69.3
Warranty cost of this year's sales — distribution

The business question

A power-tool maker launches a redesigned drill with a two-year warranty. Sales should grow through the year to about 13,000 tools. On the previous model, 448 of 14,000 tools came back under warranty — most within the first months, a typical early-failure pattern — at about US$180 a claim for parts, labour and shipping.

The redesign should be better, but nobody knows by how much; it could even be worse. Finance must provide for the warranty cost of the year's sales, and at the year end hold a reserve for the claims that will arrive over the following two years.

Who it is for
Product, quality and finance teams; anyone setting a warranty provision.
Main outputs
The warranty cost of this year's sales, per tool and in total · the claims by calendar quarter · the reserve needed at the year end, with a prudent percentile.

How the model works

Three sheets after this one, read from left to right. Each one feeds the next.

  1. Inputs sheet

    Sales in each quarter of the year (PERT). The previous model's units and warranty claims; how much better or worse the redesign could be. The Weibull shape and scale of the time to failure; the average and spread of the cost of a claim.

  2. Warranty sheet

    The claim rate is a Beta distribution from the field data, times the redesign factor. Each quarter's sales bring a Binomial number of claims, and VoseRunoff spreads them over the eight quarters of the warranty — each at a Weibull time capped at two years, with a Lognormal cost. The four quarters' runs are added up by calendar quarter.

  3. Results sheet

    The total warranty cost and the cost per tool; the claims by calendar quarter; the reserve for claims after the year end.

ModelRisk techniques

  • Beta claim rate VoseBeta
  • Binomial claims VoseBinomial
  • Claim run-off VoseRunoff
  • Exact expected cost

Find similar models

Product

The results in detail

The year-end reserve

Claims still to come, after the year end, on tools already sold (US$ thousand).

Warranty cost by calendar quarter
MeasureUnit5th pctMedian95th pctMean
Reserve needed at the year endUS$k28.038.550.438.8
MeasureUnitValue
Average reserveUS$k38.8
Average warranty costUS$k69.3
Reserve as a share of the warranty cost56%
Prudent reserve (75th percentile)US$k43.4

How to read these results

  • This year's sales should cost about US$69,000 in warranty claims — about US$5.30 a tool — but anything from about US$51,000 to US$89,000 is plausible (5th to 95th percentile). The redesign factor drives most of that range: the new design is the biggest unknown.
  • Most claims come early — a Weibull shape below 1 means early failures — so the warranty cost peaks in the fourth quarter, when the year's growing sales meet their first months of use, and then fades over two years.
  • At the year end about 56% of the warranty cost is still to come on tools already sold: a reserve of about US$39,000 on average, and about US$43,400 at the 75th percentile. The reserve, not the year's claims, is what the accounts must carry.
  • The direct calculation — expected tools × expected claim rate × average claim — gives the same mean. The simulation adds the range, the timing and the reserve.
  • Watch the claims in the first months after launch: early failures reveal the redesign factor quickly. Updating the claim rate with that data will narrow the range.

See it move. Open the workbook, change an input on the Inputs sheet and run the simulation: every table and chart on this page updates in Excel.

Inputs and assumptions

Every assumption in the model, with its uncertainty and where it comes from. The figures are illustrative.

Sales this year 4 inputs

Tools sold in each quarter.

AssumptionUnitUncertaintyLowMost likelyHigh
Quarter 1Rising as the launch builds.toolsPERT2,2002,5002,900
Quarter 2toolsPERT2,6003,0003,500
Quarter 3toolsPERT3,0003,5004,200
Quarter 4toolsPERT3,3004,0004,900
Field data and the redesign 3 inputs
AssumptionUnitUncertaintyLowMost likelyHigh
Previous model: tools in the fieldTwo full years of warranty.toolsFixed14,000
Previous model: warranty claims3.2% of tools.claimsFixed448
Redesign factor on the claim rateEngineering expects fewer failures, but a new design can surprise.× previousPERT0.600.901.30
Failures and their cost 4 inputs
AssumptionUnitValue
Time to failure: Weibull shapeBelow 1: most failures come early.0.80
Time to failure: Weibull scaleyears3.0
Average cost of a claimParts, labour, shipping.US$180
Standard deviation of the cost of a claimUS$120

Units and conventions

Money
US$; totals in US$ thousand.
Quarters
Calendar quarter 1 is the first quarter of this year; quarters 5 to 11 come after the year end.
Claim rate
The share of tools sold that come back under warranty within two years.
Reserve
The expected cost of claims after the year end on tools already sold.

Key assumptions and limitations

Sales
Tools are sold at the start of their quarter; the warranty runs eight quarters from the sale.
Claims
At most one claim per tool; tools fail independently; claim costs are independent of timing.
Field data
The previous model's experience applies, scaled by the redesign factor.
Data
The product and all figures are invented for this example.

How to use it

  1. Enter your sales forecast by quarter.
  2. Enter your previous product's field data and how different the new design could be.
  3. Run a simulation from the ModelRisk ribbon (10,000 samples with a fixed seed are set).
  4. Book the expected warranty cost per tool, and hold a prudent percentile of the year-end reserve.

Simulate this model with ModelRisk’s classic engine: it does not run in Turbo mode yet.

ModelRisk functions used

VoseBeta
The claim rate the field data support.
VoseBinomial
The number of claims on a quarter's sales.
VoseRunoff
Spreads a number of claims over periods, each at a random time with a random cost (an array).
VoseWeibullObject, VoseXBounds, VoseLognormalObject
Time to failure, capped at the warranty, and the cost of a claim.
VoseSimMean, VoseSimPercentile
Statistics of the simulation on the Results sheet.

Further reading

  • Monte Carlo simulation explained (Vose guide)
  • Blischke, W. R. & Murthy, D. N. P. (1994). Warranty Cost Analysis. Marcel Dekker.
  • Vose, D. (2008). Risk Analysis: A Quantitative Guide, 3rd edition. Wiley.