How is the quality score calculated from PPM?

Quality score = 100 x (1 - PPM / the PPM that scores zero), never below 0. With the example anchor of 4,000 PPM, a supplier with 16 rejects in 50,000 units runs at 320 PPM and scores 92; 4,000 PPM or worse scores 0. Set the anchor on Setup to suit your parts and inspection levels.

How are the three pillars combined?

Composite = (Wq x quality + Wd x delivery + Wr x responsiveness) / (Wq + Wd + Wr). With 50/30/20 weights, scores of 92, 95 and 70 give 46 + 28.5 + 14 = 88.5 points, which is 3.5 points above a minimum of 85.

Why does the Dashboard not average the monthly scores?

Averaging percentages gives a small month the same weight as a big one. The Dashboard sums the log first: a supplier with 6 rejects in 4,000 units one month and 0 in 2,000 the next runs at 1,000 PPM for the period, not the 750 PPM an average of 1,500 and 0 would suggest. Only the responsiveness rating, which cannot be summed, is averaged.

What weights and minimum should I use?

Set your own on Setup. The example uses 50 percent quality, 30 percent delivery and 20 percent responsiveness with a minimum of 85, which matches the calculator's defaults. Weights are divided by their sum, so 5/3/2 gives the same composite as 50/30/20.

How should I rate responsiveness?

Write a short rubric and use it every month, for example 100 for quotes and corrective actions answered on time with clear root cause, 70 for answers that needed chasing and 40 for missed deadlines. The rating is your judgement, so the Method sheet says so and the period score is the average of the monthly ratings.

Can I score more suppliers?

Yes. The supplier list and the log are Excel tables that grow as you add rows. The Dashboard ranking covers eight suppliers as shipped; the Method sheet explains how to extend it, and the Dashboard shows a warning if your list is longer.

Planning and Supply Chain

Supplier Scorecard Excel Template

Score every supplier each month on quality, delivery and responsiveness from the numbers your receiving dock and buyers already record, weight the pillars your way, and see who is below your minimum and why.

Overview

This supplier scorecard is a working Excel tool for supplier quality engineers, buyers and purchasing managers who need a fair, repeatable way to compare suppliers. It ships with six months of fictional data for a pump maker scoring eight suppliers, including a forging supplier whose quality collapses in June, an electronics supplier that keeps delivering late and a packaging supplier whose responsiveness improves, so the ranking and charts tell a story on day one.

Each month the log turns receipts and rejects into parts per million and a quality score on a linear 0-100 scale that you anchor (the PPM that scores zero), turns deliveries into an on-time rate and a delivery score, and takes your responsiveness rating as typed. The composite is the weighted average of the three pillars with your weights; with 50/30/20 it is exactly the formula the Supplier Scorecard calculator on this site uses.

On the Dashboard every period figure is formed from totals: PPM is total rejected over total received and on-time delivery is total on time over total due, so a large month weighs more than a small one. The ranking shows each supplier's gap to your minimum and a status in words, and the stacked chart shows which pillar cost each supplier its points, which is where the improvement conversation starts.

What this template includes

Setup sheet for the pillar weights, your minimum composite score, the PPM that scores zero on quality, PPM and on-time targets, and the supplier list that feeds the dropdown
Monthly Log with one row per supplier per month: units received and rejected, deliveries due and on time, and a 0-100 responsiveness rating
Per-row PPM, quality score, on-time delivery, delivery score, weighted composite, status against your minimum and a Check column that names data errors in words
Dashboard with supplier and month filters, KPI tiles for suppliers below minimum, the lowest scorer, incoming PPM and on-time delivery
Supplier ranking table built from period totals, with each supplier's gap to the minimum and rank
Charts of composite score by supplier against your minimum, the weighted points each pillar contributes, and pillar scores by month
Method sheet with every formula, and a Verify sheet that re-checks them against an independent reference and the Supplier Scorecard calculator's example
CSV with live formulas of the Monthly Log for any spreadsheet app; the dashboard, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it for monthly or quarterly supplier reviews, to decide who needs a corrective action or a development visit, to support sourcing decisions with data, and to show an IATF 16949 or ISO 9001 auditor how you monitor external providers.

How to use it

On Setup, set the quality, delivery and responsiveness weights, your minimum composite, the PPM that scores zero and your PPM and on-time targets, then list your suppliers.
Each month, add one row per supplier on the Monthly Log with units received and rejected, deliveries due and on time, and your responsiveness rating.
Fix anything the Check column reports, such as more rejects than receipts or a supplier missing from the list.
Open the Dashboard: the ranking shows each supplier's pillar scores, composite, gap to the minimum and status; use the filters to review one supplier or one quarter.

Frequently Asked Questions

How is the quality score calculated from PPM?: Quality score = 100 x (1 - PPM / the PPM that scores zero), never below 0. With the example anchor of 4,000 PPM, a supplier with 16 rejects in 50,000 units runs at 320 PPM and scores 92; 4,000 PPM or worse scores 0. Set the anchor on Setup to suit your parts and inspection levels.
How are the three pillars combined?: Composite = (Wq x quality + Wd x delivery + Wr x responsiveness) / (Wq + Wd + Wr). With 50/30/20 weights, scores of 92, 95 and 70 give 46 + 28.5 + 14 = 88.5 points, which is 3.5 points above a minimum of 85.
Why does the Dashboard not average the monthly scores?: Averaging percentages gives a small month the same weight as a big one. The Dashboard sums the log first: a supplier with 6 rejects in 4,000 units one month and 0 in 2,000 the next runs at 1,000 PPM for the period, not the 750 PPM an average of 1,500 and 0 would suggest. Only the responsiveness rating, which cannot be summed, is averaged.
What weights and minimum should I use?: Set your own on Setup. The example uses 50 percent quality, 30 percent delivery and 20 percent responsiveness with a minimum of 85, which matches the calculator's defaults. Weights are divided by their sum, so 5/3/2 gives the same composite as 50/30/20.
How should I rate responsiveness?: Write a short rubric and use it every month, for example 100 for quotes and corrective actions answered on time with clear root cause, 70 for answers that needed chasing and 40 for missed deadlines. The rating is your judgement, so the Method sheet says so and the period score is the average of the monthly ratings.
Can I score more suppliers?: Yes. The supplier list and the log are Excel tables that grow as you add rows. The Dashboard ranking covers eight suppliers as shipped; the Method sheet explains how to extend it, and the Dashboard shows a warning if your list is longer.