Smart Statistics · Excel practical tutorial
Excel Supplier Scorecard: Make Weighted Decisions Transparent
The cheapest proposal is not always the best fit. Build a scorecard that shows your priorities, blocks incomplete inputs and keeps mandatory requirements outside the weighted average.
A shortlist you can explain
Illustrative example · fictional suppliersEligible weighted scores
Illustrative example · all chart figuresDecision controls
Weights: agreed before scoring
Evidence: linked to each assessment
Gate: Pass required for a score
Decision: reviewed by the buyer
Start with a decision, not a formula
A UK business is choosing a support supplier. One proposal is cheaper, another offers stronger service evidence, and a third cannot meet a mandatory requirement. An unweighted average treats every criterion as equally important; a weighted average can hide a failed requirement if the spreadsheet allows other strengths to compensate.
This tutorial creates a compact scorecard in Excel for Microsoft 365. It uses four criteria, scores from zero to five, and a separate mandatory gate. It supports a documented comparison; the highest percentage is not an automatic award decision.
Use a blank worksheet named Scorecard. The examples are fictional and this package supplies formulas, not an attached workbook. Save a test copy before adapting an existing procurement file.
Agree the rules before opening the bids
Comparable scores
Use the same direction for every criterion: higher is better. Convert evidence such as price or response time into an agreed zero-to-five rubric. Never put raw pounds beside quality scores in the weighted calculation.
Non-negotiable requirements
Document the mandatory requirements separately. Mark Pass only when all have evidence. Fail means a confirmed failure; Pending means evidence is incomplete. A strong weighted score cannot override the gate.
Write criterion-specific anchors for zero, three and five before assessment, and explain intermediate values. For example, a support score of three might mean the required service hours are evidenced, while five requires additional agreed coverage and verified escalation arrangements. Do not invent the rubric after seeing which supplier it favours.
The model assumes score intervals are meaningful enough to combine and that trade-offs between these four criteria are acceptable. If the team cannot defend those assumptions, use a structured qualitative comparison instead.
Lay out the inputs precisely
Enter Cost, Quality, Delivery and Support in B2:E2. Enter the weights below in B3:E3 as percentages. Put Weight check in A4; B4 will hold the validation formula.
| B3: Cost | C3: Quality | D3: Delivery | E3: Support |
|---|---|---|---|
| 30% | 35% | 25% | 10% |
In A6:H6 enter Supplier, Cost, Quality, Delivery, Support, Gate, Status and Weighted score. Then enter the following data in A7:F9. Leave G:H for formulas.
| Row | Supplier | Cost | Quality | Delivery | Support | Gate |
|---|---|---|---|---|---|---|
| 7 | Aster | 5 | 3 | 4 | 3 | Pass |
| 8 | Birch | 3 | 5 | 4 | 4 | Pass |
| 9 | Cedar | 4 | 4 | 3 | 5 | Fail |
Create a separate Evidence sheet with supplier, criterion, source reference, assessor, assessment date and rationale. Record mandatory checks there too. The summary Gate is a reviewed conclusion, not a substitute for that record.
Validate first, calculate second
Use Data → Data Validation. Set B3:E3 to Decimal between 0 and 1. Set B7:E9 to Decimal between 0 and 5; this permits agreed fractional scores. Set F7:F9 to a List containing Pass, Fail and Pending. Use a Stop error alert and an input message explaining the scale.
The formulas also check the inputs. A missing score must not silently become a zero contribution. Keep Status visible beside the result so a blank score has an explanation.
B4 — weight check
=IFERROR(
IF(
AND(
COUNT(B3:E3)=4,
MIN(B3:E3)>=0,
MAX(B3:E3)<=1,
ABS(SUM(B3:E3)-1)<0.000000001
),
"Ready",
"Check weights"
),
"Check weights"
)The small tolerance handles floating-point rounding around a total of 100%. Zero weights are allowed deliberately; a blank weight is not.
G7 — supplier status; copy down to G9
=IFERROR(
IF(
$B$4<>"Ready",
"Check weights",
IF(
F7<>"Pass",
IF(F7="Fail","Not eligible","Check gate"),
IF(
AND(COUNT(B7:E7)=4,MIN(B7:E7)>=0,MAX(B7:E7)<=5),
"Ready",
"Check scores"
)
)
),
"Check inputs"
)H7 — weighted score; copy down to H9
=IF(G7="Ready",SUMPRODUCT($B$3:$E$3,B7:E7)/5,"")Format H7:H9 as Percentage with one decimal place. The absolute references keep the weights fixed when copying down. SUMPRODUCT multiplies corresponding weights and scores; division by five expresses the result as a share of the maximum score. It is not a probability of supplier success.
Keep each weight aligned with the matching score column. If you add a criterion, update the ranges and the expected count of four in both validation formulas. These are complete English-language formulas; some regional Excel settings require semicolons instead of commas.
Prove the result with a hand calculation
Illustrative example: Aster scores (30% × 5 + 35% × 3 + 25% × 4 + 10% × 3) ÷ 5 = 77%. Birch scores 81%. Cedar shows Not eligible in Status and no weighted score.
Sort the complete A6:H9 range, including all columns, only after preserving a copy. Review eligible suppliers together and retain the excluded and pending entries in the record. Do not sort a single score column independently from supplier names.
A tie is a prompt for further evidence or an agreed tie-break rule. More decimal places do not make subjective assessments more certain.
Change the priorities and see what moves
In a separate scenario copy, change Cost to 50% and Quality to 15%; keep Delivery at 25% and Support at 10%. The weights still total 100%.
| Supplier | Original weights | Higher cost weight |
|---|---|---|
| Aster | 77% | 85% |
| Birch | 81% | 73% |
| Cedar | Not eligible | Not eligible |
The eligible leader changes. That does not prove either supplier is objectively best: it shows the decision depends on the priority given to cost. Keep the approved baseline and record the scenario separately. Do not replace agreed weights simply to obtain a preferred winner.
Try to break the scorecard before using it
Remove a score
Clear C7. G7 should show Check scores and H7 should become blank. Restore the score before the next test.
Break the weights
Change B3 to 40% without balancing another weight. B4 should show Check weights and supplier results should be blank.
Test the boundary
Zero and five are valid scores. A value above five, negative value or text must not produce a result. Test the formula checks in a copy where validation is temporarily removed.
Leave evidence pending
Change F8 to Pending. Birch should show Check gate with a blank score. A confirmed Fail must show Not eligible.
Also test text in a weight cell, a blank weight, an Excel error value and an unknown gate label. Preserve the original formulas and confirm the expected status before releasing the file.
Make the decision record useful after the meeting
The procurement owner approves criteria, weights and mandatory requirements. Assessors supply evidence and explain scores. The workbook owner protects formula cells against accidental editing, maintains the file and reruns tests after changes. The decision-maker records the final choice and any justified departure from the score.
Give reviewers a short calibration exercise using the same fictional proposal before they assess real suppliers. Resolve differences in interpretation rather than averaging incompatible scoring habits. Keep commercial information in a location with appropriate access.
Measure practical outcomes: time spent reconciling assessments, missing-evidence queries and decisions that can be reconstructed from the record. After appointment, compare actual service performance with the evidence used. A polished scorecard cannot repair weak evidence, but it can make the weakness visible.
Is your scorecard ready for review?
Common questions
Is an 81% score an 81% chance of success?
No. It is the weighted result divided by the maximum score of five. It reflects the chosen criteria, evidence and weights, not a probability.
Can a supplier compensate for a failed mandatory requirement?
Not in this model. A Fail gate suppresses the weighted score, regardless of the other scores.
Why not treat missing scores as zero?
Missing evidence is different from evidence supporting the lowest score. This model withholds the result until every required score is numeric and in range.
Can I add more criteria?
Yes. Extend the aligned weight and score ranges, update the expected numeric counts in the validation formulas and repeat the boundary tests.
Turn spreadsheet scores into clear decisions.
Smart Statistics helps UK businesses build practical Excel tools with transparent rules, tested calculations and reporting people can trust.