Logistics & shipping

Carrier Performance Scorecard

On-time delivery, transit against quoted, damage and invoice accuracy, weighted into one score you can put in front of a carrier

Download the template — free

✓ No account, no email · ✓ Excel 2016+ and Google Sheets · ✓ Free for commercial use

Everyone knows which carrier is the difficult one. Almost nobody can show it, which is why the annual review is a conversation about impressions and the rates do not move. A scorecard is not about being harsh; it is about arriving with four numbers, each traceable to shipments the carrier also has records of. The weighting is deliberate and visible, so the carrier can argue with the weights rather than the arithmetic.

What the worksheet looks like

The actual columns, with sample rows

Scorecard sheet: four measures per carrier, weighted into one number.
A	B	C	D	E	F	G	H
1	Carrier	Shipments	On time %	Transit var.	Damage %	Invoice acc.	Score
2	MSC	48	91.7%	+0.4	0.0%	97.9%	92.1
3	Maersk	31	80.6%	+2.8	3.2%	90.3%	78.4
4	CMA CGM	22	95.5%	-1.1	0.0%	100.0%	97.2

The formulas that do the work

Every one of these is in the file you download

=IFERROR(COUNTIFS(Shipments!$B$5:$B$204,$A5,Shipments!$F$5:$F$204,"Yes")/COUNTIFS(Shipments!$B$5:$B$204,$A5),"") On-time share, computed from the shipment register rather than typed, so the scorecard cannot drift from the data behind it.
=IFERROR(AVERAGEIFS(Shipments!$E$5:$E$204,Shipments!$B$5:$B$204,$A5),"") Average days over or under the quoted transit. A carrier that is reliably two days slow is easier to plan around than one that averages zero by being wildly early and wildly late.
=IFERROR(COUNTIFS(Shipments!$B$5:$B$204,$A5,Shipments!$G$5:$G$204,"Yes")/COUNTIFS(Shipments!$B$5:$B$204,$A5),"") Damage rate. Rare enough that it needs volume behind it before it means anything, which is why the shipment count sits next to it.
=IFERROR(100*($C5*Weights!$B$4+(1-MIN(1,ABS($D5)/Weights!$B$8))*Weights!$B$5+(1-$E5)*Weights!$B$6+$F5*Weights!$B$7),"") The weighted score. Transit variance is scored on its absolute value against a tolerance, because early is also a planning failure, just a cheaper one.
=IF($A5="","",IF(COUNTIFS(Shipments!$B$5:$B$204,$A5)<Weights!$B$9,"Too few shipments","")) Suppresses a score built on too little data. A carrier with three shipments and a perfect record has not earned a number.

The carrier everybody defended

A worked example with real numbers

Maersk is the incumbent and nobody wants to move. The scorecard puts four numbers on the table: 80.6% on time against 91.7% and 95.5%, an average of 2.8 days over quoted transit, a 3.2% damage rate where the others have none, and 90.3% invoice accuracy — meaning roughly one invoice in ten needed querying. Weighted, 78.4 against 92.1 and 97.2. None of those numbers is an opinion, and all of them come from shipments Maersk can check against their own records. That is a different meeting from the one that starts with someone saying the service has felt poor lately.

What is in the workbook

Every tab, and what it holds

Every tab in the workbook and what it is for.
Tab	Contents
README	What to fill in and what is calculated, with the colour key.
Shipments	The source register: carrier, quoted and actual transit, on time, damage, invoice queried.
Scorecard	The four measures and the weighted score per carrier.
Weights	The weighting, the transit tolerance and the minimum shipment count. Change these and the scores follow.

Features and related templates

What is included, and what to look at next

What it does

Four measures, each traceable to individual shipments
Weights held in one place and visible to everyone
Transit scored on absolute variance — early is a failure too
Scores suppressed below a minimum shipment count
Excel 2016+ and Google Sheets

Related templates

Need it built around your own data?

Your own lanes, carriers and milestone names
Connected to your forwarder's exports
Delivered within 24 hours

Get your Excel job done

Questions about this template

The ones that come up most

How should I set the weights?

Start from what actually costs you money. A business holding safety stock cares more about transit variance than headline on-time percentage; one shipping to a production line cares about the opposite. The defaults are a starting point, not a standard.

Is it fair to score a carrier on invoice accuracy?

It is if you count only substantiated queries. Invoice disputes consume real hours in your team and they are entirely within the carrier's control, which is the test for whether something belongs on a scorecard.

Should I show the scorecard to the carrier?

Yes, and it is the reason to keep the weights on their own visible tab. A score you will not show is a score you do not trust; a carrier who can see the weighting argues about the weighting, which is a productive argument.