Vendor Comparison Template in Excel: Weighted Scoring, Total Cost and Supplier Scorecard
A vendor comparison template in Excel for small businesses: must-have requirements, weighted scoring criteria, total cost instead of unit price, a worked bakery example and an ongoing supplier scorecard for on-time delivery, quality and invoice accuracy.
A vendor comparison template in Excel puts competing suppliers side by side on the criteria that matter to your business, gives each one a weighted score and compares the total cost, not just the unit price. It turns a choice often made on gut feeling into a decision you can explain, and with a simple scorecard it keeps tracking the chosen supplier afterwards. This guide covers the method, the formulas and a worked example.
Step 1: must-have requirements first
Before scoring, screen out suppliers that fail a non-negotiable requirement. Typical pass/fail checks:
- Can deliver the volume and frequency you need.
- Required licenses, certifications or insurance (food safety, trade licenses, liability coverage).
- Delivers to your location within your time window.
- Accepts payment terms you can live with.
A supplier that fails one of these is out, however cheap.
Step 2: weighted criteria
| Criterion | What to look at | Weight |
|---|---|---|
| Price | Total landed cost: unit price, delivery fees, minimum order, volume discounts | 35% |
| Quality | Samples, consistency, defect or return rate | 25% |
| Delivery | Lead time, reliability, flexibility for urgent orders | 20% |
| Terms | Payment terms, minimums, returns policy | 10% |
| Service | Responsiveness, account contact, problem handling | 10% |
Score each supplier from 1 to 5 per criterion and write the evidence next to the score: the quote, sample notes, references. Weights add up to 100% and should be agreed before you see the quotes, so they are not adjusted to favor anyone.
Step 3: the formulas
- Weighted score, with weights in B2:F2 and a supplier’s scores in row 5:
=SUMPRODUCT($B$2:$F$2,B5:F5) - Monthly total cost:
=UnitPrice*MonthlyQty+DeliveryFees*DeliveriesPerMonth - Best supplier:
=INDEX(A5:A7,MATCH(MAX(G5:G7),G5:G7,0))
Example: a bakery choosing a flour supplier
| Supplier | Price | Quality | Delivery | Terms | Service | Weighted |
|---|---|---|---|---|---|---|
| A | 4 | 3 | 4 | 3 | 3 | 3.55 |
| B | 3 | 5 | 4 | 4 | 4 | 3.90 |
| C | 5 | 3 | 2 | 3 | 2 | 3.40 |
Supplier C has the cheapest flour but late deliveries in the reference checks; for a bakery that bakes every morning, a missed delivery means empty shelves. Supplier B costs about $180 more per month than A for 120 bags, but its flour performed best in test bakes and it offers net 30 terms. B wins at 3.90, and the price gap is small enough to negotiate. Ingredient costs then feed the food cost calculator to check margins per product.
Step 4: the supplier scorecard
Choosing is only half the job. Track each delivery in a log (date, supplier, on time yes or no, issues, invoice correct yes or no) and calculate monthly:
- On-time rate:
=COUNTIFS(Log[Supplier],A2,Log[OnTime],"Yes")/COUNTIF(Log[Supplier],A2) - Delivery issues:
=COUNTIFS(Log[Supplier],A2,Log[Issue],"<>") - Invoice accuracy: share of invoices that match the purchase order and the delivery.
Orders themselves are best placed with the purchase order template, which makes the three-way check between order, delivery and invoice easy. Stock levels that trigger reorders live in the inventory management template, and supplier payments in the business expense tracker.
Good practice
- Get at least three quotes for anything you buy regularly.
- Compare like for like: same quantity, same specification, same delivery terms.
- Re-run the comparison once a year or when prices jump.
- Keep a backup supplier for critical items, even if you rarely use it.
More guides are in Operations & HR.
Frequently asked questions
What criteria should I use to compare vendors?
Usually price as total cost, quality, delivery reliability, terms and service. Add criteria that matter to your trade, such as certifications or sustainability.
Should the cheapest supplier always win?
No. Late deliveries, defects and poor service have costs that do not appear on the quote, which is why price is weighted rather than decisive.
How often should suppliers be reviewed?
Track the scorecard monthly and run a full comparison at least once a year.
Does it work in Google Sheets?
Yes. SUMPRODUCT, INDEX, MATCH and COUNTIFS work the same way.