This project analyses logistics performance across 200 shipments, evaluating courier efficiency, delivery delays, return rates, and SLA compliance. The findings are presented through an interactive An An Excel dashboard designed for operational decision-making.
The project mirrors the reporting and analysis responsibilities of a Logistics Analyst or Supply Chain Analyst — tracking KPIs against targets, identifying performance gaps, and communicating actionable insights to operations teams.
Logistics teams need clear, real-time visibility of the courier performance, delivery delays, and SLA compliance to manage customer satisfaction and operational costs. This project addresses that need to be addressed:
- Measuring OTIF performance against an 80% industry benchmark
- Tracking delay trends across 7 months
- Identifying return rate issues by region
- Flagging SLA breaches as a customer satisfaction risk
- Microsoft Excel — PivotTables, KPI cards, benchmark helper tables, line and bar charts, and conditional formatting
- Dashboard design — colour-coded risk indicators, insight annotations, structured three-layer layout
- 200 shipments across 3 courier providers (DHL, FedEx, UPS) and 3 regions (APAC, EMEA, NA)
- Fields include: shipment ID, courier, dispatch date, delivery date, SLA target days, actual delivery days, delay days, status, region, return flag, SLA breach flag
Calculated the on-time-in-full delivery rate across all couriers and benchmarked against the 80% industry standard target.
Key Findings:
- Overall OTIF was just 57.5% — a gap of 22.5 percentage points points below the 80% target
- FedEx was the worst-performing courier at 56.1% OTIF — nearly 24 percentage points below target
- No courier came close to meeting the 80% benchmark, confirming a systemic performance issue across the logistics network
Tracked the proportion of shipments breaching the agreed SLA delivery commitments.
Key Finding:
- 42.5% of all shipments breached SLA — meaning nearly Half of all deliveries failed to meet contractual obligations, representing a significant customer satisfaction and compliance risk
Built a monthly line chart tracking average delay days from January to July to identify seasonal patterns and deteriorating performance.
Key Finding:
- Average delays increased by 235% from January (0.46 days) to July (1.55 days) — a clear and consistent deterioration, suggesting a seasonal peak demand pressure or capacity constraint issue
Calculated return rates by region and benchmarked against a 30% target threshold.
| Region | Return Rate | vs 30% Target |
|---|---|---|
| EMEA | 42.9% | +12.9 points over |
| APAC | 33.3% | +3.3 points over |
| NA | 23.8% | ✅ Below target |
Key Finding:
- EMEA return rate of 42.9% was the highest across all regions and significantly above the 30% benchmark — indicating accuracy or quality issues specific to the EMEA fulfilment operation
Extracted and visualised the most severely delayed shipments to provide operational visibility of the worst performing deliveries.
Key Finding:
- Critical delays of 3 days spanned all 3 couriers and all 3 regions — confirming that performance issues are not isolated to a single vendor or geography, but are systemic across the network
The Excel dashboard is structured across three layers:
- Headline KPI Cards — colour-coded (red/amber/yellow) for immediate risk identification
- Performance Charts — Courier OTIF bar chart with 80% benchmark line, monthly delay trend line chart
- Critical Metrics — Top 5 delays, return rate by region with 30% benchmark comparison
Target benchmark lines were implemented using a helper tables — a practical Excel workaround since PivotCharts do not natively support static reference lines.
- Conduct urgent performance review with all three couriers — None is meeting the 80% OTIF target
- Investigate root causes of the July delay spike — likely linked to seasonal demand peaks requiring capacity planning ahead of the next peak period
- Prioritise EMEA return rate investigation — at 42.9% This represents both a cost and a customer satisfaction risk
- Implement monthly SLA breach reporting to create courier accountability and drive improvement
- Expand the dataset to 12 months for fuller seasonal analysis
- Add cost data to quantify the financial impact of delays and SLA breaches
- Rebuild the dashboard in Power BI for real-time data connectivity
- Incorporate customer satisfaction scores to correlate delivery performance with CSAT outcomes
Complete — open to feedback and collaboration