danielle-clm/freight-cost-optimization-excel

Excel-based freight cost optimization model for Shanghai-Africa trade lanes. Uses Solver (Simplex LP) to minimize shipping costs across 15 route/carrier combinations. # 🚢 Freight Cost Optimization : Shanghai to Africa ### Excel-Based Supply Chain Analytics Project **Tools:** Excel (Solver, VLOOKUP, SUMPRODUCT, Dashboard) **Domain:** Supply Chain | Freight Analytics | Route Optimization **Context:** Built during one of the most disruptive periods in modern shipping history, the 2026 US-Israel-Iran conflict and its aftermath on Africa-bound trade lanes --- ## 📌 The Business Problem A medical supply company needs to ship **300 containers (TEUs)** from Shanghai to 3 African markets: Pointe-Noire (Congo), Mombasa (Kenya), and Lagos (Nigeria). Given multiple carriers and routes, the question is: > *What is the optimal allocation of containers across > route/carrier combinations to minimize total freight cost > while serving all 3 markets?* This project answers that question, and reveals why the cheapest answer isn't always the right answer. --- ## 🗂️ File Structure | Sheet | Purpose | |---|---| | Data_Input | Raw route, carrier & cost component data (15 combinations) | | Cost_Calculator_v1 | Unconstrained Solver model, baseline | | Cost_Calculator_v2 | Constrained Solver model, realistic | | Solver_v1 | V1 setup, constraints & results documentation | | Solver_v2 | V2 setup, constraints & results documentation | | Dashboard | Visual summary : KPIs, charts, V1 vs V2 comparison | | Insights | Full analytical report with findings & recommendations | | Charts_Data | Data source for dashboard charts | | Sources | Data sources & assumptions log | --- ## 🌍 Market Context : Why This Matters in 2026 This model was built against a backdrop of extreme market volatility: - **US-Israel-Iran conflict (Feb-June 2026):** Closed the Strait of Hormuz, forcing all major carriers (Maersk, MSC, CMA CGM) to reroute via Cape of Good Hope, adding 10-20 days to transit times and triggering emergency surcharges of $2,000-$4,000 per TEU industry-wide - **Mombasa port congestion:** 5.33-day average vessel waiting time as of June 2026, our highest-allo …