Week 11 | Session 4: SC Digital Twin - Network Optimization (Excel + AnyLogistix)

Course: Supply Chain Digitization - Module 4: Digital Infrastructure

Session Agenda

1. Context - Moving from GFA to Network Optimization

GFA (Sessions 2 & 3) found the optimal DC location from scratch - 1 DC, minimize transport cost.

This session: demand has doubled, a new factory is needed, and the existing DC lease is expiring. Must simultaneously decide: which factory to open, which DC to open, and how to route flows.

GFA vs Network Optimization - Comparison

Attribute	GFA (Sessions 2 & 3)	Network Optimization (This Session)
Question answered	WHERE to place a new DC from scratch?	Which factories/DCs to open AND how to route flows?
Objective	Minimize transportation cost	Maximize profit (Revenue − all costs)
Decision variables	xDC (lat), yDC (long) - 2 variables only	yᵢ (factory open), zⱼ (DC open), xᵢⱼ (flows), qⱼₖ (flows)
Facility options	Infinite possible coordinates	Fixed candidate locations - choose which to open
Variable type	Continuous - lat/long are real numbers	Mixed Integer - binary (yᵢ, zⱼ) + continuous (xᵢⱼ, qⱼₖ)
Solver method	GRG Non-linear (spherical distance is non-linear)	Simplex LP (all linear constraints and objective)
Cost components	Distance × demand × transport cost only	Fixed + production + inbound/outbound + transport costs

2. Case Study - Pharma Company Network Redesign

Demand data - four markets (yearly demand doubled for the 3-year horizon):

Market	Yearly Demand	Latitude	Longitude
Pune	3,15,360	18.52	73.85
Mumbai	3,59,160	19.09	72.87
Ahmedabad	3,10,980	23.02	72.57
Surat	87,600	21.18	72.83

Background:

Demand in western region will double in next 3 years - current capacity insufficient.
Management decides to set up a new factory at one of: Nashik OR Aurangabad.
DC in Mumbai (from GFA) lease is expiring - must relocate to one of: VAPI OR V1D (Viwandi).
Markets remain: Pune, Mumbai, Ahmedabad, Surat - but demand is now doubled.

All Node Data

Node	Location	Prod/Proc Cost/unit	Key Distances
Factory 1	Nashik	$7/unit production	→ VAPI 144 km, → V1D 129 km
Factory 2	Aurangabad	$5/unit production	→ VAPI 322 km, → V1D 297 km
DC 1	VAPI	$0.25 inbound/outbound	→ Mumbai 169 km, Pune 298 km, Surat 102 km, Ahm 346 km
DC 2	V1D (Viwandi)	$0.50 inbound/outbound	→ Mumbai 35 km, Pune 151 km, Surat 254 km, Ahm 487 km

Transport cost: $0.001/km/unit. Revenue: $15 per unit sold.

Four Decisions to be Made Simultaneously

Open factory at Nashik, Aurangabad, or both?
Open DC at VAPI, V1D, or both?
Which factory supplies which DC (xᵢⱼ)?
Which DC serves which market - and how much (qⱼₖ)?

3. Decision Variables

Candidate factory data:

Factory	Lat	Long	Fixed cost/yr ($)	Prod. cost/unit ($)	→ Vapi (km)	→ Bhiwandi (km)
Nashik	19.99	73.79	2,737,500	7	144	129
Aurangabad	19.88	75.34	1,825,000	5	322	297

Candidate DC data:

DC	Lat	Long	Fixed cost/yr ($)	Inbound/unit	Outbound/unit
Vapi	20.42	72.91	1,825,000	0.25	0.25
Bhiwandi	19.29	73.05	2,737,500	0.50	0.50

DC → market distance (km)	Mumbai	Pune	Surat	Ahmedabad
Vapi	169	298	102	346
Bhiwandi	35	151	244	487

Variable	Type	Meaning
yᵢ	Binary 1	= 1 if ith factory is OPEN; = 0 if NOT open. i = 1 (Nashik), i = 2 (Aurangabad)
zⱼ	Binary 1	= 1 if jth DC is OPEN; = 0 if NOT open. j = 1 (VAPI), j = 2 (V1D)
xᵢⱼ	Continuous ≥ 0	Quantity shipped from ith factory to jth DC
qⱼₖ	Continuous ≥ 0	Quantity shipped from jth DC to kth customer (k = 1..4 markets)

Big M: a very large number. If a facility is open (y=1 or z=1), capacity = M (unlimited). If closed (=0), capacity = 0 - cannot send or receive anything.

4. Objective Function - Maximize Profit

Maximize Z = Revenue − Factory Fixed Cost − DC Fixed Cost
           − Production Cost − Inbound Cost − Outbound Cost
           − Factory→DC Transport Cost − DC→Customer Transport Cost

Cost / Revenue Component	Expression	Explanation
Revenue	Σⱼ Σₖ (Rev × qⱼₖ)	Rev = $15/unit. Total revenue = units sold × price.
Factory Fixed Cost	Σᵢ (FC_factory_i × yᵢ)	If yᵢ = 0: cost = 0 (factory not open). If yᵢ = 1: incur fixed cost.
DC Fixed Cost	Σⱼ (FC_dc_j × zⱼ)	If zⱼ = 0: cost = 0 (DC not open). If zⱼ = 1: incur fixed cost.
Production Cost	Σᵢ Σⱼ (PC_i × xᵢⱼ)	PC_i = production cost per unit at factory i ($7 Nashik, $5 Aurangabad).
Inbound Processing	Σᵢ Σⱼ (IB_j × xᵢⱼ)	IB_j = cost per unit for inbound processing at DC j ($0.25 VAPI, $0.50 V1D).
Outbound Processing	Σⱼ Σₖ (OB_j × qⱼₖ)	OB_j = cost per unit for outbound processing at DC j.
Factory → DC Transport	Σᵢ Σⱼ (TC × dist_ij × xᵢⱼ)	TC = $0.001/km/unit. dist_ij = km from factory i to DC j.
DC → Customer Transport	Σⱼ Σₖ (TC × dist_jk × qⱼₖ)	dist_jk = km from DC j to customer k.

5. Constraints

Constraint	Expression	What it Enforces
Demand satisfaction	Σⱼ qⱼₖ = Dₖ ∀k	Total shipments from all DCs to customer k must equal customer k’s full demand.
Factory capacity	Σⱼ xᵢⱼ ≤ M × yᵢ ∀i	If factory i open (yᵢ=1): can ship up to M units. If closed (yᵢ=0): capacity = 0.
DC capacity	Σₖ qⱼₖ ≤ M × zⱼ ∀j	If DC j open (zⱼ=1): can process up to M units. If closed (zⱼ=0): cannot process.
Flow balance at DC	Σᵢ xᵢⱼ = Σₖ qⱼₖ ∀j	What flows INTO each DC must equal what flows OUT. No inventory held at DC.
Binary variables	yᵢ, zⱼ ∈ 1	Factory open/close and DC open/close are binary decisions.
Non-negativity	xᵢⱼ, qⱼₖ ≥ 0	Flow quantities cannot be negative.

6. Excel Solver - Setup & Results

Objective - maximise profit

max  Σ Rev·Q_jk − [ Σ FixedFac_i·Y_i + Σ FixedDC_j·Z_j + Σ ProdFac_i·X_ij + Σ InbDC_j·X_ij + Σ OutbDC_j·Q_jk + Σ Trans_ij·X_ij + Σ Trans_jk·Q_jk ]

Constraints

Σ_j Q_jk = demand_k  ·  Σ_j X_ij ≤ M·Y_i  ·  Σ_i X_ij ≤ M·Z_j
Σ_i X_ij = Σ_k Q_jk (flow balance)  ·  Y_i, Z_j ∈ 1  ·  X_ij, Q_jk ≥ 0

The full network-optimisation model - binary variables Y_i / Z_j decide which factories and DCs to open, while X_ij / Q_jk set the flows, all chosen to maximise revenue minus fixed, production, handling and transport costs.

Solver Setting	Value / Detail
Set Objective	Total Profit cell (Revenue − Cost)
To	Max (Maximize)
Changing Variables	yᵢ (factory binary), zⱼ (DC binary), xᵢⱼ (factory→DC flows), qⱼₖ (DC→customer flows)
Constraint 1	DC inflow ≤ DC capacity (= M×zⱼ)
Constraint 2	Demand satisfaction at all 4 markets
Constraint 3	Factory outflow ≤ factory capacity (= M×yᵢ)
Constraint 4	Flow balance: DC inflow = DC outflow
Binary constraint	yᵢ and zⱼ set as binary (0 or 1 only)
Method	Simplex LP - objective and all constraints are linear
Result	Aurangabad factory: OPEN. VAPI DC: OPEN. Nashik + V1D: CLOSED. Profit = ₹59,27,495

Why Simplex LP (not GRG Non-linear)?

Distances are given as fixed inputs (km values) - not calculated via spherical formula. Objective function and all constraints are linear in the decision variables. Binary variables are handled as integer constraints - Simplex LP handles this as Mixed Integer LP.

7. Optimal Solution - Results

Facilities to Open:

Factory: Aurangabad - OPEN (lower production cost $5/unit vs Nashik $7/unit)
DC: VAPI - OPEN (lower inbound/outbound cost $0.25 vs V1D $0.50)
Nashik factory: CLOSED. V1D (Viwandi) DC: CLOSED.

Product Flow:

Aurangabad factory → VAPI DC → all 4 markets
VAPI DC → Mumbai: 3,59,160 units
VAPI DC → Pune: 3,15,360 units
VAPI DC → Surat & Ahmedabad: demand matched exactly.

Optimal Profit: ₹59,27,495 - identical from Excel Solver and AnyLogistix.

8. AnyLogistix - Network Optimization Module

Factories

Aurangabad

Nashik

→

Distribution centres

Bhiwandi

Vapi

→

Customers

Mumbai

Pune

Surat

Ahmedabad

The optimised network - the model selects which factories and DCs to open and routes each market’s demand through the least-cost factory→DC→customer path, the result a tool like anyLogistix also plots on a map.

When 4 SKUs are added + daily demand → number of decision variables multiplies. Excel cannot handle large Mixed Integer Programs (MIP) efficiently. AnyLogistix has built-in MIP solver + map visualization + route details + flow breakdown.

Data Entry in AnyLogistix - Network Optimization Module

Customers: Pune, Mumbai, Ahmedabad, Surat - 4 markets.
Facilities: Aurangabad (factory), Nashik (factory), VAPI (DC), V1D (DC).
Demand: per day per SKU (4 SKUs × 4 markets = 16 demand entries).
Revenue: $15 per unit. Facility expenses: fixed cost per day.
Objective: Revenue − Fixed cost − Inbound − Outbound − Production − Transport.
Run → solution in seconds - same result as Excel: Aurangabad + VAPI, profit = ₹59,27,495.

Additional Outputs from AnyLogistix

Map visualization: actual road routes from Aurangabad → VAPI → customers.
Flow details: Aurangabad → VAPI: SKU1 = 3,13,900 units, distance shown, transport cost shown.
Per-route breakdown: every factory→DC and DC→customer flow with units, km, cost.
Multiple cost layers: processing cost, flow cost, transport cost shown separately per arc.

Session Summary

Context: demand doubled → need new factory + DC. Candidates: Nashik/Aurangabad (factory), VAPI/V1D (DC).
4 decisions: which factories to open (yᵢ), which DCs to open (zⱼ), factory→DC flows (xᵢⱼ), DC→customer flows (qⱼₖ).
Objective: Maximize Profit = Revenue − all 7 cost components (fixed + variable + transport).
Big M trick: if facility closed (0) → capacity = 0 → cannot send/receive. Open (1) → capacity = M (unlimited).
Flow balance: inflow to DC = outflow from DC. DCs are pass-through - no inventory held.
Solver: Simplex LP (linear model, binary integer constraints). Contrast with GFA which used GRG Non-linear.
Result: Aurangabad factory + VAPI DC. Profit = ₹59,27,495. Identical from Excel and AnyLogistix.