Skip to content
Ravi Chaudhary

Week 8 | Session 5: SC Network Design - LP Optimization & Final Comparison

Course: Supply Chain Digitization - Module 3: Analytics in SCM

Session Agenda

Section titled “Session Agenda”

1. Recap - Same Problem, Better Method

Section titled “1. Recap - Same Problem, Better Method”

Same SC network as Session 4: 2 Manufacturers, 3 Warehouses, 4 Retailers, 1 product.

  • Objectives: Find optimal distribution strategy to satisfy retailer demand and minimize total distribution costs.
  • Session 4: Solved using Heuristic 1 (₹9,61,000) and Heuristic 2 (₹7,57,000).
  • This session: Solve using Linear Programming (LP) + Excel Solver to get the true optimal.

2. LP Formulation - SC Network Design

Section titled “2. LP Formulation - SC Network Design”

Decision Variables - 18 total

Section titled “Decision Variables - 18 total”
  • 6 variables (M→W tier): Quantity moved between each Manufacturer-Warehouse pair (e.g., QM1W1).
  • 12 variables (W→R tier): Quantity moved between each Warehouse-Retailer pair (e.g., XW1R1).

All initialized to 0 in Excel - Solver finds the optimal values.

Flow-balance constraint (per warehouse)
Qm1w1 + Qm2w1 = Xw1r1 + Xw1r2 + Xw1r3 + Xw1r4
Demand constraint (per retailer)
Xw1r1 + Xw2r1 + Xw3r1 = 45,000  (and 35,000 / 68,000 / 70,000 for r2-r4)
The 18 decision variables (6 manufacturer→warehouse, 12 warehouse→retailer) all start at 0. Flow-balance keeps each warehouse a pass-through node; demand constraints force every retailer’s requirement to be met exactly.

3. Objective Function

Section titled “3. Objective Function”

Minimize total distribution cost = sum of (unit shipping cost × quantity) for all pairs. (In Excel: use SUMPRODUCT)

Minimize Z = Σ (Shipping Cost × Q_MiWk) + Σ (Shipping Cost × X_WkRj)

4. Constraints

Section titled “4. Constraints”

Three types of constraints - all must be satisfied simultaneously for a valid solution.

Constraint TypeExpressionWhat it ensures
Supply (2)QM1W1+QM1W2+QM1W3 ≤ 1,50,000Total shipped from each Mfr ≤ its capacity
Flow Balance (3)Σ(in to W1) = Σ(out of W1)What comes into a WH must go out - no stockpiling
Demand (4)XW1Rj+XW2Rj+XW3Rj = DjAll three WHs together must fulfill each retailer’s demand
Non-negativityAll Q and X variables ≥ 0Quantities cannot be negative

Flow Balance Constraint - Key New Concept

Section titled “Flow Balance Constraint - Key New Concept”

Ensures warehouses are pass-through nodes - they do not hold inventory. For W1: QM1W1 + QM2W1 = XW1R1 + XW1R2 + XW1R3 + XW1R4

5. Solving in Excel - Step-by-Step

Section titled “5. Solving in Excel - Step-by-Step”
  1. Set Objective: Select the Total Cost cell → choose Minimize.
  2. Changing Variable Cells: Select ALL decision variable cells (18 cells).
  3. Add Constraints: Add supply, flow balance, and demand constraints.
  4. Non-negativity: Check ‘Make unconstrained variables non-negative’.
  5. Solving Method: Select Simplex LP (model is fully linear).
  6. Solve: Click Solve → Keep Solver Solution.
Excel Solver - LP network setup
  • Set Objective: total-cost cell → Min  (cell = SUMPRODUCT(costs, quantities))
  • By Changing Cells: all 18 decision-variable cells
  • Constraints: supply ≤ capacity · units-in = units-out (flow balance) · demand = requirement
  • Make Unconstrained Variables Non-Negative: ✓
  • Solving Method: Simplex LP (model is fully linear)
The Solver minimises the SUMPRODUCT total-cost cell by adjusting all 18 flows, subject to supply, flow-balance and demand constraints, using the Simplex LP engine.

6. Result & Final Comparison

Section titled “6. Result & Final Comparison”

LP Optimal Solution

Section titled “LP Optimal Solution”

Solver confirms: all constraints and optimality conditions satisfied. Total Distribution Cost = ₹6,89,000 ← global optimum.

Optimal decision variables (units shipped) - total cost ₹6,89,000:

Warehouse← m1← m2→ r1→ r2→ r3→ r4
w11,50,000045,00035,000070,000
w2000000
w3068,0000068,0000
Manufacturers
M1 → W11,50,000
M2 → W368,000
→
Warehouses
W1
W3
→
Retailers
R1 · 45,000 via W1
R2 · 35,000 via W1
R4 · 70,000 via W1
R3 · 68,000 via W3
LP optimum - W1 serves R1, R2, R4 (fed by M1) and W3 serves R3 (fed by M2). Routing R4 through W1 and R3 through W3 undercuts both heuristics.

Approach Comparison

Section titled “Approach Comparison”
ApproachMethodTotal CostOptimal?Savings vs H1
Heuristic 1Cheapest WH for all₹9,61,000No-
Heuristic 2Cheapest path per retailer₹7,57,000No₹2,04,000
LP OptimizationSimplex LP via Excel Solver₹6,89,000Yes - guaranteed₹2,72,000

Session Summary

Section titled “Session Summary”
  • Problem: 2 Mfr → 3 WH → 4 Retailers, minimize total shipping cost.
  • Decision Variables: 6 (Mfr→WH) + 12 (WH→Retailer) = 18 total.
  • 3 Constraints: Supply (≤ capacity) | Flow Balance (in = out at WH) | Demand (= retailer req).
  • Solver: Simplex LP.
  • Result: LP = ₹6,89,000 (optimal). Beats heuristics significantly.