Week 8 | Session 3: Demand Allocation Model & Capacitated Plant Location Problem
Course: Supply Chain Digitization - Module 3: Analytics in SCM
Session Agenda
Section titled “Session Agenda”1. Recap - Week 8 So Far
Section titled “1. Recap - Week 8 So Far”Production + transportation cost per thousand units (₹ thousand), plus each plant’s capacity and monthly fixed cost:
| Supply City ↓ / Market → | New Delhi | Kolkata | Lucknow | Chennai | Hyderabad | Mumbai | Capacity (K) | Fixed cost (K₹) |
|---|---|---|---|---|---|---|---|---|
| Delhi | 1,500 | 500 | 670 | 1,600 | 1,200 | 2,900 | 20 | 8,000 |
| Chennai | 1,540 | 2,390 | 960 | 90 | 500 | 1,300 | 23 | 4,000 |
| Mumbai | 2,000 | 2,500 | 1,300 | 450 | 950 | 900 | 25 | 5,600 |
| Gurugram | 400 | 1,400 | 600 | 1,200 | 560 | 2,400 | 24 | 4,200 |
| Kolkata | 1,080 | 1,700 | 650 | 540 | 321 | 1,800 | 32 | 2,400 |
| Monthly demand (K) | 12 | 7 | 15 | 8 | 12 | 10 | - | - |
| Session | Topic | Method / Tool |
|---|---|---|
| W8 S1 | Facility Selection | Break-Even Analysis |
| W8 S2 | Facility Location | Centre of Gravity + Excel Solver (GRG Nonlinear) |
| W8 S3 | Demand Allocation + CPLP | LP formulation + Excel Solver (Simplex LP / Binary) |
2. Case Study - Two Telecom Equipment Manufacturers
Section titled “2. Case Study - Two Telecom Equipment Manufacturers”The Companies: Maxxio (North+East India) and Wavie Communications (South+West India). Both companies have enough capacity to fulfill all market demand. The decision is HOW to allocate, not whether.
Key Solver formulas:
| Cell | Formula | Purpose | Copied to |
|---|---|---|---|
| B23 | = H5*H15 − SUM(B15:G15) | Excess capacity (per plant) | B24:B27 |
| B29 | = B10 − SUM(B15:B19) | Unmet demand (per market) | C29:G29 |
| B32 | = SUMPRODUCT(B5:G9, B15:G19) + SUMPRODUCT(I5:I9, H15:H19) | Objective (total cost) | - |
3. Demand Allocation Model
Section titled “3. Demand Allocation Model”Question: How much quantity should be shipped from which plant to which market to minimize total cost?
Notation
Section titled “Notation”Cij= unit cost of production + transportation from plant i to market jXij= quantity to be shipped from plant i to market j (decision variable)Si= supply capacity of plant iDj= demand at market j
LP Formulation - Demand Allocation
Section titled “LP Formulation - Demand Allocation”- Minimize Z:
Σi Σj (Cij × Xij)(total production + transportation cost) - Supply constraint:
Σj Xij ≤ Sifor all i (supply ≤ capacity) - Demand constraint:
Σi Xij = Djfor all j (demand fully met) - Non-negativity:
Xij ≥ 0for all i, j
Optimal demand allocation (thousand units shipped from each plant to each market):
| Plant ↓ / Market → | New Delhi | Kolkata | Lucknow | Chennai | Hyderabad | Mumbai | Capacity used |
|---|---|---|---|---|---|---|---|
| Delhi (Maxxio) | 0 | 7 | 0 | 0 | 0 | 0 | 7 |
| Gurugram (Maxxio) | 12 | 0 | 12 | 0 | 0 | 0 | 24 |
| Kolkata (Maxxio) | 0 | 0 | 3 | 0 | 0 | 0 | 3 |
| Chennai (Wavie) | 0 | 0 | 0 | 8 | 12 | 0 | 20 |
| Mumbai (Wavie) | 0 | 0 | 0 | 0 | 0 | 10 | 10 |
| Demand satisfied | 12 | 7 | 15 | 8 | 12 | 10 | obj = 33,170 |
Key Insight from Solution (Maxxio)
Section titled “Key Insight from Solution (Maxxio)”The Kolkata facility is barely utilized despite fixed cost being incurred because of high production + shipping costs. But the fixed cost is still paid → inefficiency. This signals a need for a smarter model that also decides which plants to open.
4. Merger Scenario - Maxvie (Maxxio + Wavie)
Section titled “4. Merger Scenario - Maxvie (Maxxio + Wavie)”Management merges the companies into a new company: Maxvie.
- Now has 5 plants and needs to serve 6 markets.
- New question: Which plants to keep open? Can any be shut to reduce cost?
This is a Capacitated Plant Location Problem (CPLP).
5. Capacitated Plant Location Problem (CPLP)
Section titled “5. Capacitated Plant Location Problem (CPLP)”What Changes from Demand Allocation?
Section titled “What Changes from Demand Allocation?”- Add a binary decision variable
yifor each plant i:yi = 1→ plant i is openyi = 0→ plant i is closed
- Add fixed cost
fifor each plant to the objective function.
LP Formulation - CPLP
Section titled “LP Formulation - CPLP”- Minimize Z:
Σi Σj (Cij × Xij) + Σi (fi × yi)(variable cost + fixed cost of open plants) - Supply constraint:
Σj Xij ≤ Si × yifor all i (plant can only supply if open) - Demand constraint:
Σi Xij = Djfor all j - Binary variable:
yi ∈ {0, 1}for all i
Solving in Excel - Solver Settings
Section titled “Solving in Excel - Solver Settings”- Solving Method: Simplex LP (model is linear - yi is handled via integer constraint).
- Set Objective: total-cost cell (B32) → Min
- By Changing Cells: allocations Xij and plant open/close flags yi
- Constraints: excess capacity ≥ 0 · unmet demand = 0 · yi = binary
- Solving Method: Simplex LP (linear; yi enforced as binary)
6. Results & Cost Comparison
Section titled “6. Results & Cost Comparison”CPLP Solution (Maxvie - Merged):
- Optimal plants to keep open: Chennai, Gurugram, Kolkata (only 3 out of 5).
- Plants closed: Delhi and Mumbai (saving their fixed costs).
| Cost Component | Maxxio | Wavie | Combined (Pre-merger) | Maxvie Merged (CPLP) |
|---|---|---|---|---|
| Total Monthly Cost | 3.20 Cr | 2.53 Cr | ~5.73 Cr | 5.22 Cr |
Monthly saving from merger + network redesign: approximately ₹50 lakhs / month.
Session Summary
Section titled “Session Summary”- Demand Allocation Model: Decides
Xijto minimize variable cost - plants are fixed. (Simplex LP) - CPLP: Decides BOTH
Xij(flow) ANDyi(open/close plant) - minimizes variable + fixed cost. (Simplex LP with binary constraint) - Merger result: MaxV operates 3 plants instead of 5 → ~₹50L/month saving.