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
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.
- Large Van Cost: €12.85 per delivery
- Corporate Target: €4.40 per delivery
- Variance: 192% above target
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.
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.
Confidentiality & AI Disclaimer
The projects, reports, and methodologies presented are based on real-world professional engagements. To maintain strict client confidentiality, certain financial figures, proprietary data, and sensitive operational details have been anonymized, generalized, or omitted. Furthermore, Artificial Intelligence (AI) tools were utilized to assist in the data structuring, content formatting, and development of this platform.