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.
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
While certain methodologies are based on real-world professional engagements, most case studies, financial models, and project architectures presented are generated or hypothetical scenarios designed specifically for portfolio demonstration purposes. They may or may not have any direct connection to real-world clients or projects. To maintain strict confidentiality, all data utilized is either entirely synthetic, completely anonymized, or sourced from publicly available datasets. Furthermore, Artificial Intelligence (AI) tools were utilized to assist in the data structuring, content formatting, and development of this platform. Read the full disclaimer.