Build a Production Yield and Scrap Report

Every production line loses some of what it makes: parts that fail inspection and are scrapped, and parts that fail but can be reworked into good ones at extra cost. Yield — the share of what went in that came out good — and scrap cost are the two numbers a production meeting looks at every week, and the conversation is always about why: which line, which product, which shift, and above all which defect is costing the most. The data comes from production orders, usually exported from a shop-floor system or typed in by supervisors: quantity started, quantity good first time, quantity reworked, quantity scrapped and a reason code for the scrap. This page builds the report with pandas: checks that the recorded quantities add up, yield and first-pass yield by line, product and shift, scrap cost at standard cost, a Pareto of scrap reasons, and a weekly trend against target. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

The export covers eight weeks of production orders on three moulding lines, two shifts a day, with the quantity started, good first time, reworked and scrapped, and the main reason for scrap on each order. A second sheet gives each product's standard cost, used to value the scrap. The yield target is 95%.

Step 1 — Check the quantities add up

import pandas as pd

po = pd.read_excel("production_orders.xlsx", parse_dates=["Date"])
costs = pd.read_excel("standard_costs.xlsx").set_index("Product")["Standard cost"]

po["Accounted"] = po["Good first time"] + po["Reworked"] + po["Scrapped"]
mismatch = po[po["Accounted"] != po["Started"]]
print(f"{len(po)} orders; {len(mismatch)} where good + reworked + scrapped ≠ started")
print(mismatch[["Order", "Date", "Line", "Started", "Good first time", "Reworked", "Scrapped", "Accounted"]].to_string(index=False))
po = po[po["Accounted"] == po["Started"]].copy()

Every unit started must end up as good first time, reworked or scrapped, so the three must add up to the quantity started. When they do not, a count was mistyped or a batch was recorded twice, and every yield figure that includes the order is wrong. The check lists the orders that fail — here one order claims forty more good parts than could have been made — so a supervisor can correct them, and they are left out of the yield figures until then. Excluding them is better than including them, because a single inflated order can hide a real problem on a line.

Step 2 — Yield and first-pass yield by line, product and shift

po["Good"] = po["Good first time"] + po["Reworked"]
po["Scrap cost"] = po["Scrapped"] * po["Product"].map(costs)

def yields(g):
    return pd.Series({"Started": g["Started"].sum(), "Yield %": g["Good"].sum() / g["Started"].sum() * 100,
                      "First-pass %": g["Good first time"].sum() / g["Started"].sum() * 100,
                      "Scrap cost": g["Scrap cost"].sum()})

by_line = po.groupby("Line").apply(yields, include_groups=False).round(1)
by_line_shift = po.groupby(["Line", "Shift"]).apply(yields, include_groups=False)["Yield %"].unstack().round(1)
print(by_line.to_string())
print(by_line_shift.to_string())

Two yields tell different stories. Yield counts reworked parts as good — they shipped in the end — and is what determines how much material is lost. First-pass yield counts only parts that were right first time, and the gap between the two is the rework the line had to do, which costs labour even though it saves material. Both are calculated from summed quantities rather than by averaging each order's percentage, so large orders weigh more than small ones, as they should. Line 2 is clearly the weakest, and splitting it by shift shows where: its night shift yields several points lower than its day shift, while Lines 1 and 3 run about the same on both shifts. That points at something specific to Line 2 at night — set-up, staffing or a machine that runs hotter overnight — rather than at the product.

In money, Line 2 scrapped £23,438 of product over the eight weeks, nearly three times Line 1's £8,330, even though the two lines started a similar number of parts. That figure, rather than the yield percentage, is usually what gets a problem onto the engineering team's list: at this rate the gap between Line 2 and Line 1 is worth close to £100,000 a year.

Step 3 — Which scrap reasons matter?

pareto = po.groupby("Scrap reason").agg(units=("Scrapped", "sum"), cost=("Scrap cost", "sum")).sort_values("cost", ascending=False)
pareto["Share"] = pareto["cost"] / pareto["cost"].sum()
pareto["Cumulative"] = pareto["Share"].cumsum()
print(pareto.assign(Share=pareto["Share"].map("{:.0%}".format), Cumulative=pareto["Cumulative"].map("{:.0%}".format)).round(0).to_string())

by_line_reason = po.pivot_table(index="Line", columns="Scrap reason", values="Scrap cost", aggfunc="sum", fill_value=0).round(0)
print(by_line_reason.to_string())

Ranking scrap reasons by cost and adding a cumulative share gives the classic Pareto view: a couple of reasons — short shots and flash, both moulding faults — account for more than half the scrap cost, which tells the engineers where to look first. Ranking by cost rather than by units matters when products have different values: a defect that mostly affects the expensive housing can cost more than a more frequent one on a cheap bracket. The reason-by-line table adds the second dimension: Line 3's scrap is dominated by contamination, unlike the other two lines, which suggests a material-handling problem on that line rather than a moulding one. For the general technique, see Run a Pareto Analysis on Excel Data with pandas.

What the scrap cost leaves out

Valuing scrap at standard cost captures the material and the machine time built into each scrapped part, which is what finance books as a scrap write-off. It does not capture everything a defect costs. Reworked parts are counted as good in the yield, but each one took extra labour to fix — the gap between yield and first-pass yield, 1.2 to 1.3 points on every line here, is hours of somebody's time. Scrap found late, after further processing or assembly, costs more than its standard cost at the moulding stage. And capacity used to make parts that are thrown away is capacity not available for orders. Adding a rework cost per unit, and valuing late-found scrap at the cost of the later stage, makes the report a better guide to where improvement pays, though the standard-cost figure remains the one that matches the accounts.

Step 4 — Weekly trend against target, and the report

TARGET = 95.0
po["Week"] = po["Date"].dt.to_period("W-SUN").dt.start_time            # a real date, so weeks sort in order
weekly = po.groupby(["Week", "Line"]).apply(lambda g: g["Good"].sum() / g["Started"].sum() * 100, include_groups=False).unstack().round(1)
weekly.index = weekly.index.strftime("%d %b")
below = weekly.lt(TARGET)
print(weekly.to_string())
print("weeks below target:", {line: list(weekly.index[below[line]]) for line in weekly.columns if below[line].any()})

with pd.ExcelWriter("yield_scrap_report.xlsx") as xw:
    by_line.to_excel(xw, sheet_name="By line")
    by_line_shift.to_excel(xw, sheet_name="Line x shift")
    pareto.to_excel(xw, sheet_name="Scrap reasons")
    by_line_reason.to_excel(xw, sheet_name="Reason x line")
    weekly.to_excel(xw, sheet_name="Weekly yield")
    mismatch.to_excel(xw, sheet_name="Records to correct", index=False)

A weekly yield per line, compared with the 95% target, shows whether a problem is constant or recent. Line 2 is below target every week — a persistent issue already visible in the totals — while Line 3, normally comfortably above target, drops below it in the last two weeks, which is the kind of new problem a monthly total would hide for weeks. Taken together with its contamination-heavy scrap, it suggests something changed on Line 3 in mid-September — a new batch of material, a change of granulate supplier, a dryer fault — and the date the dip began is exactly the information needed to find it. The report collects all of this: yield, first-pass yield and scrap cost by line, the line-by-shift yields, the Pareto of reasons, reasons by line, the weekly trend and the orders whose quantities need correcting. Conditional formatting that colours weekly yields below target red makes the trend sheet readable at a glance, as in Highlight Values Outside a Tolerance of Target with openpyxl.

Common pitfalls

• Quantities that do not add up. Check good + reworked + scrapped = started and correct the records.
• Averaging order percentages. Sum quantities, then divide.
• Only one yield figure. Show first-pass yield too; the gap is rework.
• Ranking scrap reasons by units. Rank by cost when products differ in value.
• Monthly totals only. A weekly trend shows new problems while they are new.

Conclusion

A yield and scrap report starts by checking that every order's good, reworked and scrapped quantities add up to what was started. From the clean orders, sum quantities to get yield and first-pass yield by line, product and shift, value scrap at standard cost, rank scrap reasons by cost with a cumulative share, and compare weekly yields with the target. The combination separates persistent problems from new ones and points each at a likely cause.

Frequently asked questions

How do I calculate production yield in pandas? Sum good units (first time plus reworked) and units started for each group, and divide the totals.

What is the difference between yield and first-pass yield? Yield counts reworked units as good; first-pass yield counts only units that were right the first time.

How do I find the scrap reasons that cost the most? Sum scrap cost by reason, sort descending and add a cumulative share — a Pareto table.

Why check that production quantities add up? A mistyped count makes yield wrong for that order and can hide or invent a problem; exclude and correct such orders.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports.
• Run a Pareto Analysis on Excel Data with pandas — Pareto in general.
• Build a Bill of Materials Cost Rollup — the standard costs.
• Build a Warranty Claims Report — the defects that escape.