"Final project: delivery performance review"

01The problem

Harbourline Freight's customers judge it on one thing: does the shipment arrive when promised? The operations director wants a review she can take to the board:

"How reliable are we, really? Where are we worst, and is it getting better or worse? And give the sales team something useful for quoting."

A review like that needs everything in this course: the right averages and spreads, percentiles a customer can plan around, outliers investigated rather than deleted, rates with confidence intervals, tests that separate real changes from noise, and a regression the sales team can use. This lesson sets up the project; the full brief and submission are on the course's project page.

02The concept

From questions to statistics

Each of the director's questions maps to a tool:

Definitions first

State them at the top of your workbook, because every number depends on them:

• Transit days = delivery_date − ship_date, for Delivered shipments only.
• On time = transit days ≤ the route's target_transit_days.
• Year = the year of booking_date. 2026 runs from January to August only.

A fair comparison

Routes have very different targets: one day for Lagos–Ibadan by road, 42 for Shanghai–Onne by sea. Comparing raw transit times across routes is meaningless; compare on-time rates, or days late against each route's own target. And watch for small routes: a route with 36 deliveries has a wide confidence interval.

03Example

A first look at on-time performance by route. With a pivot table of delivered shipments (route in rows; count and average of a 1/0 on_time column), sorted by on-time rate, the three least punctual routes are:

All three have a one-day target. That's a finding in itself: the least punctual routes aren't the long sea voyages, they're the short trips with no slack. The question for the director isn't only "why are these routes late?" but also "is a one-day promise realistic?" Before saying either, check the confidence intervals: with 36 to 62 deliveries each, the margins are around ±12 to ±15 points, so these three aren't clearly worse than routes in the low 70s.

Overall, 76.6% of the 2,411 delivered shipments arrived on time.

04Walkthrough

1. Download the logistics dataset. In shipments.csv, add mode, origin, destination and target_transit_days from routes.csv with XLOOKUP.
2. Add transit_days, on_time (1 or 0) and year, and filter to Delivered shipments. Write your three definitions at the top of a Notes sheet.
3. Build a pivot table of on-time rate and count by mode, then by route.
4. For each mode, calculate the median, IQR and 90th percentile of transit days.
5. Pick the route you'll use for the quoting formula and fit freight_charge against containers.
6. Open the project brief on the course page and list which tool answers each of its tasks.

05Practice

06Check your understanding