Project UrbanFlow
A Strategic Analysis of Delivery Performance for Rhein-Main Logistik GmbH, preventing contract termination and recovering margins through data-driven modal shifts.
4.15
Month Payback Period
€6,020
Monthly Hub Savings
€2.4M
Retail Revenue Protected
Project UrbanFlow (60-Sec Executive Brief)
Rhein-Main Logistik's On-Time Delivery rate collapsed to 82.34% with severe €5.20 cost-per-delivery overruns (+18%), risking immediate client SLA contract termination.
Diagnosed excessive €12.85/drop large-van fuel costs in Baden-Württemberg and executed a hybrid modal shift to lightweight e-cargo bikes (€0.81/drop).
Saved €6,020/month per hub, recovered SLA targets to 88.5% OTD, and protected €2.4M in enterprise retail contracts with a 4.15-month payback.
The Operational Crisis
Rhein-Main Logistik GmbH was operating at a critical service deficit. The primary metric for retail distribution success, On-Time Delivery (OTD), had plummeted to 82.34%.
Under existing Service Level Agreements (SLAs), any performance sustained below the 80% threshold triggers immediate contract review and potential termination.
Concurrently, operational costs surged. The Average Cost per Delivery rose to €5.20, an 18% variance above the corporate target of €4.40, causing severe margin compression.
Diagnostic Root Cause
Using granular Excel Pivot Tables and conditional logic (AVERAGEIFS), I isolated vehicle performance within high-expenditure corridors. The data revealed the Baden-Württemberg hub as a significant financial outlier.
The root cause was unoptimised routing of heavy combustion vehicles in topographically complex and highly congested urban zones, leading to immense fuel inefficiency.
Strategic Recommendation
To restore profitability, a Pugh Matrix was used to evaluate four strategic pathways. The winning strategy was a Hybrid Modal Shift: transitioning 40% of inner-city volume to Electric Cargo Bikes while retaining vans for suburban terrain.
By shifting approximately 500 deliveries per month in the Baden-Württemberg hub to e-bikes (which cost only €0.81 per drop compared to €12.85), the company could achieve an immediate €12.04 saving per delivery.
Table of Contents
4 min readAnalytical Methodology
Phase 1: Sanitisation
Deduplication and validation logic (nested IF/ISBLANK) applied to 5,000 records to mitigate GIGO risks.
Phase 2: Benchmarking
Aggregated sanitised data to compute OTD and average fuel costs for regional like-for-like comparison.
Phase 3: Root Cause
Deployed AVERAGEIFS to isolate the Baden-Württemberg cost anomaly by vehicle type.
Phase 4: Scenario Modeling
Developed dynamic What-If scenarios predicting ROI and mitigating terrain/weather risks.