# Pareto Defect Cost Ranking Worksheet

> Source: https://netray.co/tools/pareto-defect-cost-ranking-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Editable table to log defect types, counts, unit cost and handling time, with extended cost and hours for ranking.

## Category

quality

## Audience

- Quality engineer
- Continuous improvement
- Quality manager

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Defect type

**Key:** defect

**Type:** text

#### Process

**Key:** process

**Type:** select

**Options:**

- Machining
- Welding
- Assembly
- Coating
- Incoming
- Packaging

#### Count

**Key:** count

**Type:** number

#### Cost per defect

**Key:** unit_cost

**Type:** number

**Format:** currency

#### Extended cost

**Key:** ext

**Type:** number

**Expr:** count * unit_cost

**Format:** currency

#### Handling minutes each

**Key:** mins

**Type:** number

#### Handling hours

**Key:** hours

**Type:** number

**Expr:** count * mins / 60

**Format:** hours

**Rows:**

**Defect:** Out of tolerance bore

**Process:** Machining

**Count:** 46

**Unit cost:** 128

**Mins:** 25

**Defect:** Porosity in weld

**Process:** Welding

**Count:** 31

**Unit cost:** 210

**Mins:** 40

**Defect:** Missing fastener

**Process:** Assembly

**Count:** 58

**Unit cost:** 22

**Mins:** 10

**Defect:** Coating thickness low

**Process:** Coating

**Count:** 19

**Unit cost:** 165

**Mins:** 35

**Defect:** Damaged on receipt

**Process:** Incoming

**Count:** 24

**Unit cost:** 74

**Mins:** 20

**Defect:** Label error

**Process:** Packaging

**Count:** 37

**Unit cost:** 9

**Mins:** 6

**Totals:**

#### Total defects

**Key:** count

**Agg:** sum

**Format:** integer

#### Total defect cost

**Key:** ext

**Agg:** sum

**Format:** currency

#### Total handling hours

**Key:** hours

**Agg:** sum

**Format:** hours

**Summary:**

#### Average cost per defect

**Key:** avg_cost

**Expr:** sum_ext / max(1, sum_count)

**Format:** currency

**Primary:** true

#### Average handling hours per defect

**Key:** avg_hours

**Expr:** sum_hours / max(1, sum_count)

**Format:** decimal

#### Largest single defect cost line

**Key:** max_cost

**Expr:** max_ext

**Format:** currency

#### Largest line as share of total cost

**Key:** top_share

**Expr:** max_ext / max(1, sum_ext) * 100

**Format:** percent

**Primary:** true

**Notes:** Rank by extended cost, not count: the most frequent defect is often not the most expensive. Sort the exported rows in Excel from largest to smallest and add a cumulative percent column to draw the Pareto chart. Aim at the few lines that make up roughly 80 percent of the cost.

## Content

**Intro:** A Pareto analysis tells you where to start. This worksheet lets you list defect types with count, cost and handling time, computes extended cost and hours per line, and exports to Excel for sorting and charting.

**Sections:**

#### How to use it

**Body:** Replace the sample rows with your defect categories from NCRs, scrap reason codes or final inspection records for a fixed period. Enter a realistic cost per defect, including material and labor, and the minutes spent handling each. Add rows for each category and delete the examples. Keep the period and the category list consistent from one review to the next so the results can be compared.

#### Count versus cost

**Body:** A count-based Pareto answers which defect is most common. A cost-based Pareto answers which defect hurts most. They often disagree: a missing fastener may occur most often but cost little, while weld porosity occurs less and costs a great deal. Handling hours add a third view that shows where the inspectors and rework cells spend their time.

#### How to read the result

**Body:** Check the share held by the largest line, then export and sort. If a few lines hold most of the cost, assign each a root cause owner and an A3 or 8D. If costs are evenly spread, the problem is systemic, and a process-level approach such as better work instructions or training may beat chasing individual defects.

**Bullets:**

- Sort descending by extended cost
- Add cumulative percent for the chart
- Review monthly with the same categories
- Revisit after corrective actions to measure the change

#### Common mistakes

**Body:** Having too many categories so nothing stands out, an Other bucket that grows larger than the top defect, and mixing scrap reasons with causes. Define categories by symptom you can observe, and keep cause analysis for the next step with the 5 whys or a fishbone.

**Cta:** If your scrap and NCR reason codes are in an ERP, we can help produce this ranking directly from transactions every week, so the meeting starts with the data.

**Faq:**

#### How many categories should I use?

**Answer:** Between 6 and 12 is workable. If everything falls in Other, split it. If there are dozens of tiny categories, group them by symptom family.

#### Why use cost instead of count?

**Answer:** Cost shows business impact. A rare, expensive defect may deserve attention before a frequent, cheap one. Use both views in your review.

#### Where do I get cost per defect?

**Answer:** Use standard cost of the part at the point it was found, plus rework labor, plus disposal. Finance or cost accounting can supply average values by process.

#### Does it draw the chart?

**Answer:** The worksheet computes the numbers and exports to Excel. Sort by extended cost there, add a cumulative column and insert a combined bar and line chart.

## Search terms

pareto analysis template excel, defect pareto worksheet, pareto chart data table, defect cost ranking, vital few defects, nonconformance pareto, quality pareto template, scrap reason ranking

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json