Build a Supplier Scorecard

Purchasing teams know which suppliers are a pleasure to deal with and which ones keep causing trouble, but the impression is rarely measured. A supplier scorecard turns it into numbers that can be discussed in a quarterly review: did they deliver on time, did they deliver the full quantity, did the goods pass inspection, and did they charge the agreed price? The data already exists in the purchasing system — orders with promised dates, receipts with actual dates and quantities, inspection results and invoice prices — but scattered across exports. This page builds a quarterly scorecard with pandas: on-time in-full delivery at order-line level, defect rate, price variance, a weighted overall score with thresholds that everyone can understand, the change from the previous quarter, and a colour-coded Excel sheet ready for supplier meetings. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

The input is one row per purchase order line, already joined from the order, receipt, inspection and invoice exports: supplier, promised and received dates, quantity ordered and received, quantity rejected at goods-in inspection, and the agreed and invoiced unit prices. It covers the second and third quarters of 2026 for five suppliers.

Step 1 — The four measures per supplier and quarter

import pandas as pd

lines = pd.read_excel("supplier_lines.xlsx", parse_dates=["Promised", "Received"])
lines["Quarter"] = lines["Promised"].dt.to_period("Q").dt.strftime("%Y-Q%q")
lines["On time"] = lines["Received"] <= lines["Promised"]
lines["In full"] = lines["Received qty"] >= lines["Ordered qty"]
lines["OTIF"] = lines["On time"] & lines["In full"]
lines["Spend"] = lines["Received qty"] * lines["Agreed price"]
lines["Overcharge"] = (lines["Invoiced price"] - lines["Agreed price"]) * lines["Received qty"]

measures = lines.groupby(["Supplier", "Quarter"]).agg(
    lines=("PO line", "size"), on_time=("On time", "mean"), in_full=("In full", "mean"), otif=("OTIF", "mean"),
    rejected=("Rejected qty", "sum"), received=("Received qty", "sum"), spend=("Spend", "sum"), overcharge=("Overcharge", "sum"))
measures["defect_rate"] = measures["rejected"] / measures["received"]
measures["price_variance"] = measures["overcharge"] / measures["spend"]
q3 = measures.xs("2026-Q3", level="Quarter")
print(q3[["lines", "on_time", "in_full", "otif", "defect_rate", "price_variance"]].round(3).to_string())

Each measure is calculated per order line and then averaged, so a supplier with many small orders and one with a few large ones are judged on the same basis. On time compares the receipt date with the date the supplier promised — not the date the buyer asked for, which suppliers cannot be held to if they never agreed it. In full means the whole quantity arrived. OTIF, on time and in full, is the headline delivery measure, and it is always lower than either part on its own, because a line counts only if both are true — Northfield Metals is on time on 96.5% of lines and in full on 93%, but both on only 91.2%. The defect rate is the share of received units rejected at inspection, and the price variance is the extra cost of invoiced prices above agreed prices as a share of spend at agreed prices — positive when the supplier overcharges, negative when they invoice below the agreed price.

Step 2 — Score each measure and weight them

def scale(value, best, worst):
    """Map a measure to 0–100 points between a 'worst acceptable' and a 'best expected' value."""
    return ((value - worst) / (best - worst)).clip(0, 1) * 100

scores = pd.DataFrame({
    "Delivery": scale(measures["otif"], best=0.98, worst=0.70),
    "Quality": scale(measures["defect_rate"], best=0.0, worst=0.05),
    "Price": scale(measures["price_variance"], best=0.0, worst=0.03),
})
WEIGHTS = {"Delivery": 0.5, "Quality": 0.3, "Price": 0.2}
scores["Score"] = sum(scores[k] * w for k, w in WEIGHTS.items()).round(0)
scores["Rating"] = pd.cut(scores["Score"], [0, 60, 80, 101], labels=["Red", "Amber", "Green"], right=False)
print(scores.xs("2026-Q3", level="Quarter").round(0).to_string())

The measures are on different scales — a share of lines, a share of units, a percentage of spend — and better is sometimes higher and sometimes lower. Mapping each one to points between a worst acceptable and a best expected value puts them on a common 0–100 scale that is easy to explain: an OTIF of 98% or better earns full delivery points, 70% or worse earns none, and anything between scores proportionally. For defects and price variance the best value is zero, and the scale runs the other way automatically because the worst value is higher than the best. The weights say what the business cares about most — here delivery half, quality three tenths, price a fifth — and the thresholds turn the score into a rating: green from 80, amber from 60, red below. Agreeing the thresholds and weights with the suppliers in advance is what makes the scorecard fair.

The thresholds deserve as much thought as the weights. Set the best value too high and nobody ever scores full points, which makes the scorecard feel unwinnable; set the worst too low and poor performance still earns a respectable score. A good starting point is the contract: if the agreement promises 95% on-time delivery, a value around there is the natural point at which a supplier is doing what it signed up for.

Step 3 — The trend against last quarter

wide = scores["Score"].unstack("Quarter")
wide["Change"] = wide["2026-Q3"] - wide["2026-Q2"]
board = (scores.xs("2026-Q3", level="Quarter")
         .join(wide["Change"])
         .join(q3[["otif", "defect_rate", "price_variance", "spend"]])
         .sort_values("Score", ascending=False))
print(board[["Score", "Rating", "Change", "otif", "defect_rate", "price_variance"]].round(3).to_string())

A single quarter's score says where a supplier stands; the change from last quarter says where they are heading, which often matters more in a review. A supplier that moved from red to amber after an improvement plan deserves recognition; a green supplier sliding by fifteen points needs a conversation before it turns amber. Joining the scores to the raw measures keeps the explanation next to the number. In the third quarter only Northfield Metals reaches green, at exactly 80, though it slipped six points; Apex Fasteners is amber, held back by an OTIF of 86% despite the best quality and pricing. Three suppliers are red, for different reasons that need different conversations: Coastline Plastics has improved ten points but still delivers late a quarter of the time, Summit Electronics fell thirteen points because 5.5% of its units failed inspection, and Delta Packaging delivered on time on only 63% of lines and invoiced 3.2% above the agreed prices.

Step 4 — The scorecard workbook

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

out = board.reset_index()[["Supplier", "Score", "Rating", "Change", "Delivery", "Quality", "Price", "otif", "defect_rate", "price_variance", "spend"]]
out = out.rename(columns={"otif": "OTIF", "defect_rate": "Defect rate", "price_variance": "Price variance", "spend": "Spend"})
path = "supplier_scorecard_2026-Q3.xlsx"
with pd.ExcelWriter(path) as xw:
    out.to_excel(xw, sheet_name="Scorecard", index=False)
    lines[lines["Quarter"] == "2026-Q3"].to_excel(xw, sheet_name="Order lines", index=False)
wb = load_workbook(path)
ws = wb["Scorecard"]
fills = {"Green": "C6EFCE", "Amber": "FFEB9C", "Red": "FFC7CE"}
for row in ws.iter_rows(min_row=2):
    row[2].fill = PatternFill("solid", fgColor=fills[row[2].value])
    for cell, fmt in zip(row[7:11], ["0.0%", "0.00%", "+0.00%;-0.00%", "#,##0"]):
        cell.number_format = fmt
wb.save(path)
print(out[["Supplier", "Score", "Rating", "Change"]].to_string(index=False))

The scorecard sheet lists suppliers from best to worst with the overall score, the coloured rating, the change from last quarter, the three component scores and the underlying measures, formatted as percentages so they read naturally. The order lines for the quarter sit on a second sheet: when a supplier disputes a late delivery, the line, the promised date and the receipt date are right there. Sending each supplier its own row and lines, rather than the whole table, is a small extension using a loop over suppliers, as in Send Each Recipient Their Own Excel Report with Python.

Common pitfalls

• Measuring against the requested date. Use the date the supplier promised.
• Averaging on-time and in-full separately. OTIF needs both on the same line.
• Raw measures on different scales. Map each to points between agreed worst and best values.
• Weights decided after the fact. Agree weights and thresholds before the quarter starts.
• A score without the reason. Show the measures next to the score so the conversation is specific.

Conclusion

A supplier scorecard needs four measures per supplier and period — on-time in-full delivery against the promised date, defect rate, and price variance against agreed prices — each turned into 0–100 points between an agreed worst and best value and combined with agreed weights. Ratings from fixed thresholds, the change from the previous quarter and the underlying measures make it a basis for supplier reviews rather than a league table.

Frequently asked questions

How do I calculate OTIF in pandas? Flag each order line as on time if received by the promised date and in full if the whole quantity arrived, then take the mean of the combined flag per supplier.

How do I combine measures on different scales into one score? Map each measure to points between a worst acceptable and a best expected value, clip to 0–100, and take a weighted sum.

Should price variance be part of a supplier scorecard? Usually yes, with a lower weight than delivery and quality; it catches invoices above the agreed price.

How do I show a supplier's trend? Calculate the score for each quarter and show the difference from the previous quarter next to the current score.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports.
• Build a Purchase Order Tracking Report — the orders behind the scores.
• Compare Supplier Quotes with Python — choosing suppliers in the first place.
• Check Supplier Invoices Against Contract Prices — the price measure in depth.