Build a Warranty Claims Report

A warranty claim is a defect that escaped the factory and found the customer. Each one costs a repair or replacement, and the pattern of claims answers two questions that matter to different people. Quality engineers want to know what is failing, when after sale it fails, and whether a particular production batch is worse than the rest. Finance wants to know how much the claims still to come on products already sold will cost, because that figure is the warranty provision on the balance sheet. Both depend on getting the denominator right: a count of claims means little without the number of units that could have produced them, and units sold last month have not had time to fail yet. This page builds a warranty report with pandas from sales and claims data: claim rates per thousand units by product and month of sale, the time from sale to failure, failure modes ranked by cost, a check for a bad batch, and an estimate of future claims for the provision. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Two inputs: units sold per product and month, with the production batch they came from, from January 2025 to September 2026; and warranty claims with the product, batch, month of sale, month of claim, failure mode and cost. The warranty runs for 24 months from sale.

Step 1 — Claim rates by product, on the right denominator

import pandas as pd

sold = pd.read_excel("units_sold.xlsx")
claims = pd.read_excel("warranty_claims.xlsx")
for df, cols in ((sold, ["Sale month"]), (claims, ["Sale month", "Claim month"])):
    for c in cols:
        df[c] = pd.PeriodIndex(df[c], freq="M")
asof = pd.Period("2026-09", "M")

mature = sold[(asof - sold["Sale month"]).apply(lambda d: d.n) >= 12]           # sold at least 12 months ago
units = mature.groupby("Product")["Units sold"].sum()
early = claims[claims["Sale month"].isin(mature["Sale month"]) & ((claims["Claim month"] - claims["Sale month"]).apply(lambda d: d.n) < 12)]
rate = (early.groupby("Product").size() / units * 1000).round(1).rename("Claims per 1,000 in first 12 months")
cost_per_unit = (early.groupby("Product")["Cost"].sum() / units).round(3).rename("Claim cost per unit sold")
print(pd.concat([units.rename("Units"), rate, cost_per_unit], axis=1).to_string())

The obvious rate — claims this year divided by units sold this year — is wrong in the same way as returns rates: claims arrive months after sale, so recent sales have not yet had time to fail. A fair comparison counts claims made within the same length of time after sale for every unit. Here that window is the first twelve months, and only units sold at least twelve months ago are included, so every unit in the denominator has had its full twelve months. Measured that way, the blender fails about twice as often as the kettle in its first year, and its claims cost more each. The claim cost per unit sold is the figure product management needs when it sets prices: it is the warranty cost built into every unit, whether or not that unit fails.

Step 2 — When do products fail?

claims["Months to failure"] = (claims["Claim month"] - claims["Sale month"]).apply(lambda d: d.n)
old = claims[(asof - claims["Sale month"]).apply(lambda d: d.n) >= 18]          # cohorts observed for 18+ months
counts = old.groupby(["Product", "Months to failure"]).size().unstack(fill_value=0).reindex(columns=range(24), fill_value=0)
curve = counts.cumsum(axis=1).div(counts.sum(axis=1), axis=0)
print(curve.loc[:, [0, 2, 5, 11, 17, 23]].round(2).to_string())

The share of each product's claims that arrive within a given number of months of sale is the claims curve. It is built only from sales months that have been in the field for at least eighteen months; including recent cohorts would bias it towards early failures, because their later claims have not happened yet. Most claims come early — about two fifths within the first three months of sale and two thirds within six — and the curve flattens towards the end of the warranty, with over 90% of claims made within the first year. Its shape matters for two reasons. A failure mode concentrated in the first weeks suggests a manufacturing or assembly defect, while one that rises late suggests wear. And the curve is the basis for estimating claims still to come: if 92% of a product's claims usually arrive within the first twelve months, a batch sold a year ago has less than a tenth of its claims still ahead, while last month's sales have most of theirs to come.

Step 3 — Failure modes and a suspect batch

modes = claims.groupby(["Product", "Failure mode"]).agg(claims=("Claim", "size"), cost=("Cost", "sum")).sort_values("cost", ascending=False)
print(modes.round(0).head(6).to_string())

blender = claims[claims["Product"] == "Blender B3"]
early_b = blender[blender["Months to failure"] < 3]
per_batch = (early_b.groupby("Batch").size() / sold[sold["Product"] == "Blender B3"].set_index("Batch")["Units sold"] * 1000).dropna()
typical = per_batch.median()
suspects = per_batch[per_batch > 2 * typical].round(1)
print(f"blender claims per 1,000 in first 3 months: median {typical:.1f}; suspect batches: {suspects.to_dict()}")
print(blender[blender["Batch"].isin(suspects.index)]["Failure mode"].value_counts().to_dict())

Ranking failure modes by total cost points the engineers at the defects that matter most — for the blender, the motor. The batch check compares each production batch's early claim rate, over the first three months after sale so that recent batches can be included, with the typical rate for that product. Two blender batches, from September and October 2025, have 51.8 and 60.3 early claims per thousand units against a median of 25 — and their claims are dominated by seal leaks rather than the motor failures seen elsewhere. That pattern — a specific mode, in specific batches — points to a component or process change at the time, which quality can trace through the batch records, and it might justify contacting customers who bought from those batches before more units fail.

Step 4 — Claims still to come, for the provision

def expected_remaining(product):
    s = sold[sold["Product"] == product].set_index("Sale month")["Units sold"]
    rate24 = claims[claims["Product"] == product].groupby("Sale month").size().reindex(s.index, fill_value=0) / s
    lifetime_rate = rate24[(asof - rate24.index).map(lambda d: d.n) >= 20].mean()      # nearly complete cohorts
    share_by_age = curve.loc[product]
    rows = []
    for month, units_sold in s.items():
        age = (asof - month).n
        if age >= 24:
            continue
        done = share_by_age.get(min(age, 23), 1.0)
        rows.append(units_sold * lifetime_rate * (1 - done))
    avg_cost = claims.loc[claims["Product"] == product, "Cost"].mean()
    return round(sum(rows)), round(sum(rows) * avg_cost, 2)

provision = pd.DataFrame({p: expected_remaining(p) for p in sold["Product"].unique()}, index=["Claims to come", "Estimated cost"]).T
print(provision.to_string())
print("total estimated cost:", provision["Estimated cost"].sum().round(2))

The provision estimate combines three things for each product: a lifetime claim rate per unit, taken from sales cohorts old enough to have had nearly their whole warranty; the claims curve, which says what share of claims a cohort of a given age has already made; and the number of units still under warranty from each month of sale. For each month, the expected remaining claims are units sold × lifetime rate × the share of the curve still to come; summing over months and multiplying by the average claim cost gives the estimated cost. Here the estimate comes to about 1,560 claims still to come, costing £43,621, of which the blender accounts for more than half.

This is a simple, explainable method that auditors recognise; it assumes the future follows the past, so a known problem such as the suspect batches justifies an extra amount on top, which should be stated separately.

Common pitfalls

• Claims divided by sales in the same period. Compare claims over the same time after sale.
• Including units too new to have failed. Restrict to units with the full observation window.
• Ranking failure modes by count. Rank by cost; an expensive failure matters more.
• Batch rates on long windows. Use an early window so recent batches can be compared.
• A provision that ignores known problems. Add and disclose an amount for identified issues.

Conclusion

A warranty report compares products on claims per thousand units over the same time after sale, using only units old enough to have had that time, and turns the timing of claims into a claims curve. Ranking failure modes by cost directs engineering effort, comparing early claim rates by batch finds production problems, and combining a lifetime claim rate with the curve and the units still under warranty gives an explainable estimate of the claims still to come for the warranty provision.

Frequently asked questions

How do I calculate a warranty claim rate? Divide the claims made within a fixed period after sale, such as twelve months, by the units sold that have been in the field at least that long.

What is a warranty claims curve? The cumulative share of a product's claims that arrive within each number of months after sale.

How do I find a bad production batch from warranty data? Compare each batch's claim rate over the first few months after sale with the typical rate for the product and look for outliers with a distinctive failure mode.

How do I estimate claims still to come for a warranty provision? For each sales month, multiply units sold by the lifetime claim rate and by the share of the claims curve not yet reached, then by the average claim cost.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports.
• Build a Returns Analysis Report — the same denominator problem for returns.
• Build a Production Yield and Scrap Report — defects caught before shipping.
• Build a Cohort Retention Table from Excel Data — analysis by starting month.