Manage vendor relationships and evaluate supplier performance effectively
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
Supplier scorecards fail when they average unlike things into one number. This template keeps delivery, quality and price as separate scores and only then weights them, so a bad score can be acted on.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Supplier | On-time % | Defect % | Price idx | Score | Rating |
| 2 | Nordic Textiles | 96.2% | 0.8% | 1.02 | 88 | A |
| 3 | Baltic Packaging | 81.4% | 0.3% | 0.94 | 74 | B |
| 4 | Adria Components | 93.0% | 4.1% | 0.88 | 68 | C |
| 5 | Helix Metals | 98.1% | 0.5% | 1.11 | 85 | A |
Why each one is written the way it is
=COUNTIFS(Deliv[Supplier],[@Supplier],Deliv[Late],"No")/COUNTIFS(Deliv[Supplier],[@Supplier])
On-time rate from delivery records rather than impression. Suppliers are remembered by their worst delivery, not their average one.
=SUMIFS(QC[Rejected],QC[Supplier],[@Supplier])/SUMIFS(QC[Received],QC[Supplier],[@Supplier])
Defect rate by units, not by delivery. One bad pallet in a small delivery is not the same problem as one bad pallet in a large one.
=[@[Unit price]]/AVERAGEIFS(Parts[Price],Parts[Category],[@Category])
Price index against the category average, so price is comparable across parts of different value.
=ROUND(OnTime*0.4 + (1-Defect)*0.4 + (2-PriceIdx)*0.2, 0)*100
Weighted score with the weights in named cells. Publishing the weights to suppliers is what makes a scorecard change behaviour.
A worked example with real numbers
A components buyer ranked suppliers on price and used Adria for 60% of volume. Scoring quality separately showed a 4.1% defect rate against under 1% elsewhere — at their volume, roughly €31k a year in rework and expedited replacements against €14k of price saving. Volume moved, and Adria was given the defect figure and six months; they fixed it.
Tab by tab
| Tab | Contents |
|---|---|
| Scorecard | Weighted scores and ratings by supplier. |
| Deliveries | Every delivery with promised and actual dates. |
| Quality | Received and rejected quantities per delivery. |
| Prices | Current prices with category benchmarks. |
| README | The weightings and how to change them. |
What is included, and what to look at next
Specific to this workbook, not generic
40% delivery, 40% quality, 20% price is a reasonable default for production inputs. For non-critical consumables, shift weight toward price. The important part is that the weights are explicit and shared with the supplier.
Quarterly, with a minimum number of deliveries before a score is meaningful — five is a reasonable floor. Scoring a supplier on two deliveries produces noise that damages relationships.
Yes. A scorecard that is never shared changes nothing. Sharing the components and the weights lets a supplier fix the specific thing that is costing them volume.
"This tracker has transformed our supplier management process. We now have clear visibility of performance metrics and can make informed decisions about our supplier partnerships."