Quality & InspectionFree worksheetPDF, Excel, Word exportDiscrete Manufacturing

Nonconformance Log and Pareto Worksheet (Excel Export)

Most plants keep a nonconformance log in Excel and then spend an afternoon building a Pareto from it. This worksheet gives you the log with cost per event already calculated, so you can see which defect codes are costing the most.

Download:

NCR no	Date found	Part number	Defect code	Found at	Qty affected	Unit cost	Rework hours (h)	Labor rate per hour	Event cost
									$557
									$948
									$588
									$439
									$960
									$114

Total pieces affected

136

Total rework hours

Total event cost

$3,606

Largest single event

$960

Average cost per NCR

$601

Rework labor per NCR

5.17

To build the Pareto, export to Excel, sort by Defect code, sum the event cost for each code with a pivot table, sort descending and add a cumulative percent column. The codes that reach about 80% of the cost are your vital few.

Pareto reports from your ERP rejection data

Optional: we can build a read-only report that ranks rejection reasons by cost directly from your ERP.

Optional. The tool and every export work without this. No spam.

How to use the worksheet

Add one row per NCR. Pick a defect code from the list, record where the problem was found, then enter the quantity, unit cost, rework hours and labor rate. The event cost column multiplies quantity by unit cost and adds rework hours times the rate, so every row shows its own loss. Totals and the average cost per NCR update as you edit. Replace the sample rows with your own, add more rows as the month goes on, and export to Excel or CSV when you want to pivot or share the data.

One row per NCR, not per piece
Use a short fixed list of defect codes
Record where the defect was found
Count rework hours as well as scrap value

Why rank by cost, not count

A Pareto by count shows which defects are frequent, but a rare defect on an expensive assembly can cost more than a hundred cosmetic marks. Ranking by event cost points the corrective action effort where the money is. The usual rule says about 80% of the loss comes from about 20% of the causes, though the real ratio varies. Run the Pareto by defect code first, then by part number, then by where the defect was found, to see whether the problem is a process, a product or a supplier.

Worked example

In the sample rows, dimensional defects on BRK-4410 appear twice and include a customer-found event, with 24 pieces and 12 rework hours in total. Priced at unit cost plus labor, that code dominates the month. A pivot of event cost by code would put Dimensional first, which tells the quality team to open a corrective action on the boring process rather than spreading effort over every code.

Common mistakes

Teams often use free-text defect descriptions, so the same fault appears under five names and the Pareto splits it. Another mistake is omitting customer-found events or hours spent on investigation, which hides the true cost. Finally, a log kept separately from the ERP drifts out of date. If your ERP records rejections by reason code, export those and compare them with the log each month, since the quantity and cost fields usually exist already.

Frequently Asked Questions

How many defect codes should I use?

Between 8 and 15 is practical. Too few hides patterns, and too many splits the same fault across codes. Review the list yearly and keep an Other code that you check regularly.

How do I build a Pareto chart from this log?

Export to Excel, make a pivot table with defect code as rows and event cost as values, sort descending and add a cumulative percent column. Plot costs as bars and the cumulative percent as a line.

Should the log include internal scrap and customer returns?

Yes, both, with the found-at column telling them apart. Customer-found events carry extra cost such as freight, sorting and credit notes, which you can add to the rework hours or notes.

How often should I review the log?

Weekly for open items and monthly for the Pareto. A monthly review that feeds the management review under ISO 9001 or AS9100 is common practice.

If your NCR data lives in the ERP, Netray can build a read-only Pareto report from rejection reason codes and costs, refreshed daily.

Talk to an Expert Explore More Free Tools

Related Tools

Quality & Inspection

More Quality & Inspection tools

See all

Back to All Tools