Week 11 | Session 2: SC Digital Twin — Green Field Analysis (GFA) with Excel & AnyLogistix
Course: Supply Chain Digitization — Module 4: Digital Infrastructure
Session Agenda
Section titled “Session Agenda”1. What is Green Field Analysis (GFA)?
Section titled “1. What is Green Field Analysis (GFA)?”This session builds a small SC digital twin to perform GFA — finding the optimal DC location using:
- Excel + Solver for small-scale problems (understanding the mechanics).
- AnyLogistix software for large-scale, real-world problems.
2. Case Study — Pharma Company: DC Location for Western India
Section titled “2. Case Study — Pharma Company: DC Location for Western India”
A pharmaceutical company wants to set up 1 Distribution Center to serve the western region of India.
Objective: find DC location (latitude, longitude) that minimizes total transportation cost.
Market Data
Section titled “Market Data”| Market | Index | Annual Demand (units) | Latitude (xᵢ) | Longitude (yᵢ) |
|---|---|---|---|---|
| Pune | 1 | 1,57,680 | 18.52 | 73.85 |
| Mumbai | 2 | 1,79,580 | 19.09 | 72.87 |
| Ahmedabad | 3 | 1,55,490 | 23.02 | 72.57 |
| Surat | 4 | 43,800 | 21.18 | 72.83 |
Cost assumption: $1 per km per unit of demand transported.
3. Problem Framing — Why Intuition is Insufficient
Section titled “3. Problem Framing — Why Intuition is Insufficient”| DC placed near… | Problem |
|---|---|
| Mumbai (highest demand) | Cost to Ahmedabad and Surat increases sharply |
| Pune | Cost to Mumbai, Ahmedabad, Surat all increase |
| Ahmedabad | Cost to Mumbai, Pune, Surat all increase |
Conclusion: there is a trade-off — need an optimization model. Infinite possible DC locations exist; cannot evaluate manually.
4. Optimization Model — Formulation
Section titled “4. Optimization Model — Formulation”
Decision Variables:
xDC= latitude of the new DC (unknown)yDC= longitude of the new DC (unknown)
Objective: Minimize Z
Z = Σ(i=1 to 4) [ Demand_i × d(xᵢ,yᵢ → xDC,yDC) × Cost per km per unit ]| Component | Detail |
|---|---|
| Distance formula | Haversine / Spherical — accounts for earth’s curved surface → gives accurate km distance |
| Solver method | GRG Non-linear — because the spherical distance formula is non-linear |
| Constraints | None — DC can be placed at any (xDC, yDC) coordinate |
5. Distance Formulas — Three Methods
Section titled “5. Distance Formulas — Three Methods”| Method | Formula | Accuracy |
|---|---|---|
| Direct Line (Euclidean) | √[(xᵢ−xDC)² + (yᵢ−yDC)²] | Low — straight-line, not realistic |
| Corner (Manhattan) | |xᵢ−xDC| + |yᵢ−yDC| | Medium — accounts for turns, flat grid only |
| Spherical (Haversine) | Haversine / Spherical trig formula | High ✓ — used in this case |
Why Spherical Formula is Used
Section titled “Why Spherical Formula is Used”Earth’s surface is not flat — lat/long are coordinates on a curved surface. The spherical formula captures earth’s curvature → most accurate real-world km distance. It makes the objective function non-linear → must use GRG Non-linear solver (not Simplex LP).
6. Solving in Excel — Step-by-Step
Section titled “6. Solving in Excel — Step-by-Step”- Enter market data: Input demand, latitude, longitude for all 4 markets (Pune, Mumbai, Ahmedabad, Surat).
- Set initial DC location:
xDC = 20(latitude),yDC = 72(longitude) — random starting values; not the answer yet. - Calculate distances: Apply spherical distance formula from each DC location to each market → 4 distance values.
- Calculate individual costs:
Cost_i = Demand_i × Distance_i × $1(cost per km per unit). - Calculate total cost Z: Z = Sum of all 4 individual costs. At initial (20, 72): Z = 12,44,89,119.
- Run Solver: Set objective = minimize total cost cell. Changing variables = latitude cell, longitude cell. Method = GRG Non-linear. Solve.
- Read optimal solution: Optimal
xDC = 19.09,yDC = 72.87. Total cost = 9,73,92,135. Location = Mumbai.
Cost Calculation at Initial Location (20, 72)
Section titled “Cost Calculation at Initial Location (20, 72)”| Route | Calculation | Cost |
|---|---|---|
| DC → Pune | 1,57,680 × 254.543 km × $1 | 4,01,59,298 |
| DC → Mumbai | 1,79,580 × 136.197 km × $1 | 2,44,58,875 |
| DC → Ahmedabad | 1,55,490 × 340.945 km × $1 | 5,29,98,141 |
| DC → Surat | 43,800 × 157.099 km × $1 | 68,80,136 |
| Total Z | 12,44,89,119 |
Optimal Solution from Solver
Section titled “Optimal Solution from Solver”
| Parameter | Value |
|---|---|
| Optimal DC latitude (xDC) | 19.09 |
| Optimal DC longitude (yDC) | 72.87 |
| Optimal location | Mumbai |
| Minimized total cost Z | 9,73,92,135 |
| Cost saving from optimization | ~₹2.7 crore / year |
7. Digital Twin Visualization — What it Adds
Section titled “7. Digital Twin Visualization — What it Adds”The Excel model gives the coordinates — but no visual map of the SC. Digital twin visualization adds:
- Exact geographic position of each market plotted using lat/long.
- DC location plotted at the optimized coordinates.
- Routes from DC to each market shown — visual confirmation of the network.
- Immediate intuition about coverage area and travel paths.
This is what tools like AnyLogistix provide — map-based visualization + optimization combined.
8. Scaling Up — AnyLogistix Software for Large Problems
Section titled “8. Scaling Up — AnyLogistix Software for Large Problems”Why Excel Fails at Scale
Section titled “Why Excel Fails at Scale”Excel works for 4 markets — but 4,000 or 40,000 markets make it impractical. Manual lat/long entry for thousands of cities is not feasible. Formula setup, solver configuration, and interpretation become unmanageable.
How AnyLogistix Solves This
Section titled “How AnyLogistix Solves This”| Attribute | Excel Solver | AnyLogistix (DT software) |
|---|---|---|
| Scale | Small (4 markets — manageable manually) | Large (thousands of markets — nationwide / global) |
| Lat/Long input | Must be manually entered for each location | Enter city name → auto-retrieves lat/long from database |
| Visualization | Basic — no map plotting built in | Plots all nodes on actual map automatically after input |
| Algorithm | Non-linear Solver (GRG) — user sets it up | Built-in optimization algorithm runs automatically |
| Result output | Optimal xDC, yDC + total cost (cell values) | Optimal DC location plotted on map + lat/long + connections |
| Speed | Feasible but manual — formula setup required | Click of a button — fully automated for any scale |
| Typical use | Teaching, small-scale understanding | Real-world SC network design and digital twin development |
AnyLogistix result confirmed: GFA optimal DC = lat 19.09, long 72.87 = Mumbai — matches Excel Solver output exactly.
9. Digital Twin Framework Applied to This Example
Section titled “9. Digital Twin Framework Applied to This Example”- Digital Visualization: 4 markets + optimal DC location plotted on geographic map with routes.
- Digital Technology: AnyLogistix software + optimization algorithm (connects physical SC problem to digital model).
- Prescriptive Analytics: model tells WHERE to locate DC to minimize cost — a direct prescriptive output.
This is a full mini digital twin — not just a visualization, but an analytically driven location decision.
Session Summary
Section titled “Session Summary”- GFA: finding optimal location for a NEW DC using optimization — a prescriptive analytics application.
- Case: pharma company, 4 western India markets (Pune, Mumbai, Ahmedabad, Surat), minimize transportation cost.
- Model: Minimize Z = Σ(Demand_i × Distance_i × Cost/km/unit). Decision variables: xDC, yDC.
- 3 distance formulas: Direct line (low acc) → Corner/Manhattan (medium) → Spherical/Haversine (high — used here).
- Spherical formula = non-linear → must use GRG Non-linear solver (not Simplex LP).
- Excel result: Optimal xDC = 19.09, yDC = 72.87 (Mumbai). Cost = ₹9.73 Cr vs ₹12.44 Cr at random start.
- AnyLogistix: same result plotted on map automatically — scales to thousands of markets.
- DT components used: Visualization (map) + Technology (AnyLogistix) + Prescriptive Analytics (location optimization).