To build a supplier scorecard from delivery and quality records, export your purchase order lines with promised date, received date, ordered quantity, received quantity and rejected quantity. Add three columns: on-time, in-full and defect rate. Convert each to a 0-100 score, apply weights you choose, and total them. AI writes the formulas and the commentary; the spreadsheet does the counting.
The weights are the part that makes a scorecard yours. A bakery that cannot open without flour weights delivery above everything else; a food packer may weight quality at 50%. The example below uses three suppliers and one quarter of illustrative data, so you can see the arithmetic and then swap in your own numbers.
What goes into the export, and what to leave out
Most purchasing systems, accounting packages and even a shared inbox of order confirmations can produce a line-level export. What matters is that each row is one order line, not one order, because a PO with ten lines can be late on two and short on one. You need these fields, and the names will vary by system:
- PO number and line number so that a line can be traced back to the order.
- Supplier name, spelled the same way every time (this is the field that goes wrong; more on that below).
- Promised date: the date the supplier confirmed, not the date you asked for. If your system only stores the requested date, use it, but know that it will punish suppliers who honestly quoted a longer lead time.
- Received date from the goods-in record. If a line arrives in two deliveries, use the date the last part arrived for the in-full test and record the first date separately if you care about partials.
- Quantity ordered, quantity received, quantity rejected in the same unit of measure.
- Line value, which lets you weight problems by money rather than by count.
Leave out cancelled lines, lines you cancelled yourself, and lines where you changed the date after the supplier had delivered. Those are your changes, and counting them against the supplier makes the scorecard unfair and unusable in a conversation. If you are not sure what to exclude, ask the AI to list the statuses in your export with a count for each, then decide.
Step 1: Check what the export actually contains
Before any formula, look at the file. A short prompt does most of the work, as long as you paste a sample (twenty or so rows with any private details removed) and not the whole file.
Here are 20 rows from my purchase order line export (supplier names are
real; prices removed). I want to score suppliers on on-time, in-full and
defect rate. Tell me: (1) which column holds each of the fields I need, (2)
anything in these rows that would break a calculation, such as blank dates,
text in number columns, dates in two formats, or one supplier spelled two
ways, (3) which statuses I should exclude. Do not calculate anything yet.
[paste 20 rows]Illustrative output: it maps "Req Date" to promised date (and warns that it may be the requested date), flags that 4 of 20 rows have a blank "Goods Received" date (still open orders, to exclude), notices "Mill Co" and "Mill Co." and "MILL CO" as three spellings, and spots that quantity received is sometimes entered as "12 cs" with text. Each of those would have produced a wrong score. If your export needs more tidying than this, cleaning messy data with AI covers the standard fixes, and the supplier-name fix below takes five minutes.
Fixing supplier names once
Copy the list of distinct supplier names to a new tab, and add a column "Clean name". Ask the AI to group the variants. Check its grouping, then use a lookup to put the clean name on every row. You do this once and reuse the lookup table every quarter, adding a row whenever a new variant appears.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowStep 2: Three calculated columns on every line
Assume columns are: D ordered qty, E received qty, F rejected qty, G promised date, H received date. In Excel or Google Sheets:
On-time (I): =IF(H2<=G2,1,0)
In-full (J): =IF(E2>=D2*(1-Rules!$B$3),1,0)
OTIF (K): =I2*J2The tolerance cell Rules!B3 holds how short a delivery can be and still count as in full. Set it to 0 for products where the exact quantity matters (a batch of printed labels), 0.02 for bulk goods weighed in kilos. To allow a day's grace on lateness, change the first formula to H2<=G2+Rules!$B$4 with one day in the cell.
Defect rate is not a line-level yes or no. It is a ratio that you compute per supplier: units rejected divided by units received. Do not average line-level rates, because a line of five units with one reject (20%) would count the same as a line of 5,000 units with 50 rejects (1%). Add up first, divide second.
Defect rate for a supplier:
=SUMIFS(F:F, Supplier, "Mill Co") / SUMIFS(E:E, Supplier, "Mill Co")Here Supplier is a named range covering the clean supplier-name column, and SUMIFS takes the column to add first, then the column to test and the value to match. Each rounded figure in the tables below comes from unrounded scores, so a total can differ by 0.1 from the sum of the rounded parts.
If you want the AI to help build these, describe your columns and ask for the formulas with two test rows worked out by hand. Then test them on three real lines you can check against the paperwork. Spending ten minutes here is cheaper than finding out in a supplier meeting that the on-time column treats blank dates as zero, which is before any promised date and therefore "on time".
The worked example: three suppliers, one quarter
An illustrative bakery buys from three suppliers. Over the quarter its PO export shows these totals after the exclusions above:
| Measure | Flour and dry goods | Packaging | Dairy and eggs |
|---|---|---|---|
| Order lines | 120 | 80 | 200 |
| Lines on time | 114 | 60 | 190 |
| On-time % | 95.0% | 75.0% | 95.0% |
| Lines in full | 111 | 76 | 170 |
| In-full % | 92.5% | 95.0% | 85.0% |
| Lines on time and in full (OTIF) | 106 | 58 | 164 |
| OTIF % | 88.3% | 72.5% | 82.0% |
| Units received | 18,000 | 40,000 | 9,000 |
| Units rejected | 90 | 800 | 135 |
| Defect rate | 0.5% | 2.0% | 1.5% |
Notice what OTIF reveals. Flour is 95.0% on time and 92.5% in full, but only 88.3% on time and in full together, because the late lines and the short lines overlap only partly. OTIF is always at or below the lower of the two figures, and the gap shows how often a supplier fails you twice on the same line.
Turning each measure into a score
Percentages on different scales cannot be added together: a 2% defect rate sounds small, while 2% on-time would be a catastrophe. So each measure gets a floor (the value that scores 0), a target (the value that scores 100) and a straight line between them. You pick these; they say what you consider acceptable.
| Measure | Floor (score 0) | Target (score 100) | Score formula |
|---|---|---|---|
| On-time | 70% | 98% | (value minus 70) divided by 28, times 100 |
| In-full | 80% | 99% | (value minus 80) divided by 19, times 100 |
| Defect rate | 5% | 0.2% | (5 minus value) divided by 4.8, times 100 |
In a spreadsheet, wrap the result in MAX(0, MIN(100, …)) so that a value beyond the floor or target stays between 0 and 100. Applying that to the example:
| Score (0-100) | Flour and dry goods | Packaging | Dairy and eggs |
|---|---|---|---|
| On-time score | 89.3 | 17.9 | 89.3 |
| In-full score | 65.8 | 78.9 | 26.3 |
| Quality score | 93.8 | 62.5 | 72.9 |
The weights you set
Now the decision that belongs to the owner. This bakery cannot bake without ingredients arriving on the day, so it weights on-time at 40, in-full at 30 and quality at 30. The weights must sum to 100, and writing them in cells on the Rules tab means you can change them and see the result immediately.
| Weighted total | Flour and dry goods | Packaging | Dairy and eggs |
|---|---|---|---|
| On-time (40%) | 35.7 | 7.1 | 35.7 |
| In-full (30%) | 19.7 | 23.7 | 7.9 |
| Quality (30%) | 28.1 | 18.8 | 21.9 |
| Total score | 83.6 | 49.6 | 65.5 |
Bands make the number actionable: 80 and above is "keep as is", 60 to 79 is "review at the next meeting", below 60 is "written action plan, and find a second source". On those bands the flour supplier is fine, dairy needs a conversation about short deliveries, and packaging needs an action plan driven by lateness, since its quality and in-full figures are mediocre but its on-time score is 17.9.
Does the ranking survive different weights?
Test it. If the same bakery weighted quality at 50 and split the rest 25 and 25, the totals become 85.6 for flour, 55.5 for packaging and 65.4 for dairy. The order does not change, so the conclusion holds. If a small change in weights flips the order of two suppliers, tell the reader of the scorecard that those two are close and decide on other grounds. A scorecard that cannot survive a weight change should not be used to end a relationship.
Step 3: Ask the AI to read the results, not produce them
Once the numbers sit in a small summary table (one row per supplier, a dozen columns), paste only that table into ChatGPT or Claude. Ask for an interpretation with constraints, so it cannot invent causes.
Below is a supplier scorecard summary for the quarter: one row per supplier
with lines, on-time %, in-full %, OTIF %, defect rate, weighted score and the
previous quarter's score. For each supplier write two sentences: what the
numbers show, and one question to ask them. Use only the figures given. Do not
suggest causes unless the table contains evidence; where you are guessing,
say "possible reason, check". Then list the supplier whose score fell most.Illustrative output for packaging: "On-time fell from 88% to 75% while quality and in-full held. Question: has the supplier changed its carrier or lead time? Possible reason, check: a lead-time change not reflected in your promised dates." That last sentence is worth reading twice, because it hints at a common false alarm, which we come to below.
A scorecard that blamed the wrong party
The packaging supplier's 75% on-time looks damning. When the bakery's owner compared the late lines with the confirmation emails, 11 of the 20 late lines had been placed with a note "needed Friday" while the supplier's standard lead time was ten working days; the system stored the requested date as the promised date. The supplier had never agreed to Friday. Re-scored against confirmed dates, on-time rose to 88.8% and the total moved from 49.6 to about 69. The mistake was in the data, not the supplier, and nothing in the formulas would reveal it. The check is simple: take the ten worst lines and read the paperwork before showing anyone the scorecard.
Smaller scorecards for small businesses
The method scales down. If you place a dozen orders a quarter, do not compute percentages from twelve lines. Score each delivery on a four-point scale and keep a running log:
- A dog groomer with one shampoo and consumables supplier scores each order: arrived on time (yes or no), correct items (yes or no), damaged items (count). After six orders, the pattern in a simple list is clearer than a calculated score.
- A café buying milk, coffee and pastries daily has many lines and little time. The useful measure is "short or late deliveries per fifty", tracked on the delivery note, and a weekly look at the supplier with the most failures.
- A driving school treats the garage servicing its cars as the supplier. On-time becomes "car back by the agreed time", in-full becomes "everything on the job list done", and quality becomes "no repeat visit within 30 days". Five measures in a table, once a quarter, is all it needs.
- A manufacturer with hundreds of lines a month can add cost measures, such as price variance against the agreed price, which links to updating prices after supplier increases.
Using the scorecard in a conversation with a supplier
A scorecard is only useful if it leads somewhere. Send the supplier their own figures, the definitions and a specific request, rather than a vague complaint. The AI can draft the message from the table and you edit the tone.
Draft a short, polite message to a supplier of packaging. Facts: over the last
quarter 60 of 80 lines arrived on or before the confirmed date (75%); our
target is 98%; quality was 2.0% rejected against our 0.2% target. Ask for two
things: the cause of the late lines, and a lead-time commitment we can plan
around. Mention that we review scores each quarter. No threats, under 130
words, with "[first name]" and "[your name]" placeholders.Send the line-level list of late deliveries as an attachment, so that disputes begin from facts. If a supplier rejects your dates, ask for theirs; the gap is often a process fix. For deeper negotiation wording, see the supplier negotiation emails tutorial, and for the wider picture of prices, lead times and risk, AI supplier management.
Edge cases that distort the numbers
Five situations come up in almost every export, and each one needs a rule before you calculate anything.
- Split deliveries. A line of 1,000 units arrives as 600 on the promised day and 400 four days later. It is on time for the first delivery and late for the second. Pick one rule (the last receipt decides on-time and in-full) and apply it to every supplier, or the best-organised supplier is penalised for being open about partials.
- Substitutions. A supplier sends an equivalent product with a different code. If you accepted it, count the line as in full; if you had to return it, count it as short. Log substitutions in a notes column so a pattern shows.
- Early deliveries. Goods that arrive eleven days early can cost storage and cash. Allow a day or two early as on time, then count anything earlier as a separate "early" flag. It is a real cost for fresh goods, a cheap one for screws.
- Rejected on delivery versus found later. A flaw found three weeks after delivery still belongs to the supplier's quality score, but it enters in the quarter you discover it. Add a column for "found after receipt", because those defects are costlier.
- Force majeure and one-off events. A flood or a strike hits the whole region, not just your supplier. Keep the lines in the data, mark them with a reason code and show scores with and without them. Don't delete the evidence.
A quality-first version for a small food producer
A small food producer buying ingredients and labels, where one bad batch can mean a recall, would not use the bakery's weights. Illustratively it might set quality at 50, in-full at 25 and on-time at 25, and tighten the quality scale: floor 2% defects, target 0%. It would also split defects into categories (foreign material, wrong specification, damaged packaging, paperwork missing), because one foreign-body finding matters more than ten dented cartons. The AI is good at this sorting: paste the free-text reasons from the goods-in log, ask it to propose five categories and assign each row, then check twenty of its assignments yourself. If a category is wrong, give it a rule ("anything mentioning glass, metal or plastic is foreign material") and run it again. A scorecard with categories points to a corrective action, such as a supplier audit or a tighter specification, where a single defect rate only says "worse".
The same trick helps a manufacturer that holds supplier certificates. Add a column for "certificate in date" (yes or no) and treat a no as a score cap: whatever the delivery performance, a supplier with an expired certificate cannot score above 60 until it is renewed. Caps like this belong on the Rules tab so they are visible, not buried in a formula.
Keeping the scorecard honest over time
- Freeze the definitions. If you change the tolerance or the floor, mark the date on the Rules tab and note that earlier quarters are not comparable.
- Keep the line-level data. The summary is derived; the audit trail is the export. Save each quarter's export unchanged.
- Weight by value when it matters. A supplier who is late on lines worth $200 is not the same as one late on $20,000. Add a value-weighted on-time figure (
SUMPRODUCTof the on-time column and line value, divided by total line value) when spend is uneven. - Watch for gaming. A supplier who learns the promised date is the test may quote longer lead times. Track the average quoted lead time as a column so you notice when it creeps from five days to nine.
- Link it to money. Late deliveries cost you something: staff waiting, a rush order elsewhere, a missed customer promise. Writing one real cost beside a score makes it count in a negotiation.
If you also reconcile what suppliers bill you against what they delivered, matching supplier invoices to purchase orders uses the same PO export, so the same clean data does both jobs.
How to know the scorecard is working
After two quarters, check four things. The scores should move when something real changes (a supplier fixes a problem and the number rises). The suppliers who feel bad to deal with should score lower than those who do not; if your gut and the scorecard disagree, look for a missing measure such as responsiveness. The time to produce it should be under an hour, and decisions should have followed: at least one supplier conversation, one second source found, or one order moved. If nothing changed because of the scorecard, it is a report and not a tool, and the problem is the follow-through, not the formulas.
Supplier scorecard questions
How often should I update a supplier scorecard?
Monthly is enough for most small businesses, with a rolling three-month view so one bad week doesn't dominate. If you place only a few orders a quarter, score each order when it arrives and review the pattern every quarter instead, because a monthly percentage on five lines is meaningless.
What counts as on time?
Decide it once and write it down. A common choice is received on or before the promised date, allowing one day early, with the date taken from the receiving record rather than the delivery note. Measure against the date the supplier confirmed, not the date you wished for.
Does a partial delivery count as in full?
No, a line is in full only when the quantity received meets the quantity ordered, within a tolerance you choose, such as 98%. Track partial deliveries separately as well, because a supplier who delivers 90% on time every time behaves differently from one who delivers 100% a week late.
Can ChatGPT or Claude calculate the scores for me?
They can write the formulas and explain the results, but the calculation should happen in your spreadsheet. A chat model reading hundreds of rows can miscount without any warning. Use it to design the columns, check the formulas on a few lines, and draft the supplier review message.
How many suppliers should I score?
Score the ones that matter: the top five to ten by spend, plus any single-source supplier whose failure would stop you trading. Scoring every small vendor creates admin without insight. Review the list twice a year and add anyone who has caused a problem.
Further reads
- How to Compare Supplier Quotes Side by Side With AI — Use the scores when you ask other suppliers to quote.
- How to Automate Purchase Orders and Supplier Emails With AI — Produce cleaner PO data for next quarter's scorecard.
- AI Quality Control for Small Manufacturers: Where to Start — Catch quality problems before they become supplier defects.
- How to Spot Patterns in Complaints, Returns, and Defects With AI — Spot defect patterns across complaints and returns.
- AI in Hospitality Management: Catch Supplier Price Creep on Invoices — Catch price creep on the invoices that follow late deliveries.
- Can AI Read Emailed Orders Into a Wholesaler's System? — See how order data arrives when it comes by email.
- Event Planning Using AI After the Day: Feedback, Sponsor Reports, Debrief — Post-event only: survey themes with counts you check, a sponsor recap built on promised against delivered, and a debrief that ends in owners and dates.
- CE Marking Technical File Checklist: Draft the Outline With AI — A checklist for building a CE technical file outline with AI, and the checks a competent person still has to make.
- INCI Ingredient List Check With AI: Names, Not Safety Sign-Off — A checklist for indie skincare brands: what AI can check on an INCI list, what it cannot, and what to give your safety assessor.
- Importer Product Compliance Checklist: AI Flags Missing Documents — The supplier documents an importer should hold for CE or UKCA products, with prompts that summarise them and flag what is missing.
- Incoming Inspection Checklist: Build One Per Part With AI — How to turn a drawing into a one-page inspection checklist with AI, and which dimensions an engineer must still verify.
- MRO Inventory Management: AI-Set Minimums for Maintenance Spares — One factory's spares sheet, built from work orders and a criticality score, with min and max rules and an obsolete-parts check.
- Six AI Applications in Manufacturing That Need No New Machines — Six office-side AI jobs for a small factory, each with a worked example, a starting step and the check that keeps it honest.
- Supplier Audit Checklist: Plan and Write Up Visits With AI — A small maker's audit of a powder-coating supplier: pre-visit questions from the NCR log, a 3-hour agenda, an on-site checklist and a findings report.
- Supplier Corrective Action Request: Draft a SCAR With AI — A SCAR built around one failed bracket lot: evidence pack, containment ask, root-cause questions, due dates and a closure test the supplier can meet.
- Supplier Onboarding Automation: AI Forms, Checks and Reminders — How a wholesaler can collect supplier documents with one form, have AI check them, chase gaps on a schedule and stop bank-detail change fraud.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: standard definitions of on-time in-full delivery and defect rate used in purchasing practice; the figures and weights are illustrative.