Week 8 | Session 2: Facility Location Decision - Centre of Gravity Method

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

Session Agenda

1. Recap - Facility Selection vs. Facility Location

Last session: Break-Even Analysis for facility selection - choosing best from existing options at a given volume.
This session: Facility Location Decision - finding the optimal coordinates for a brand new facility.

2. Case Study - Cooking Range Manufacturer

Background:

Manufacturer currently has a single assembly factory near Mumbai serving the entire Indian market alone.
Rapid demand growth observed → CEO decides to build a second factory.
Task for SC Manager: Find the optimal location for this new factory.

Network Structure:

Supply Sources: Parts plants in Chennai, Kolkata, Hyderabad (3 nodes)
Markets: Delhi, Bangalore, Mumbai, Pune, Chennai (5 nodes)
New Factory: (X, Y) Unknown - to be determined.

Given Data:

Coordinate locations (X, Y) of all supply sources and markets
Demand at each market and supply quantity from each parts plant
Shipping cost per unit per mile

3. Centre of Gravity Method - Concept

Worked-example data - supply sources and markets for steel appliances (coordinates, quantity, transport cost, and distance to a trial facility location):

Source / Market	Transport cost (₹/ton·km)	Quantity (tons)	X	Y	Distance (d)
Supply - Chennai	72	450	670	1100	1287.98
Supply - Kolkata	76	320	120	560	572.71
Supply - Hyderabad	68	950	230	450	505.37
Market - Delhi	120	225	500	600	781.02
Market - Mumbai	120	150	1200	1100	1627.88
Market - Bangalore	120	250	700	200	728.01
Market - Pune	120	175	820	1025	1312.64
Market - Chennai	120	300	1300	1100	1702.94

Analytical approach based on coordinate locations of supply sources & markets. Considers demand, supply quantities, and distances to find the location that minimizes total weighted transportation cost.

Variable	Meaning
X, Y	Coordinates of the new facility (decision variables - unknown)
Xi, Yi	Coordinates of supply source / market node `i`
F	Shipping cost per unit per mile
D	Quantity to be shipped
d	Distance between the new facility and a given node (calculated)

4. Key Formulas

Euclidean Distance Formula

Used to calculate the straight-line distance between the new facility (X, Y) and any node (Xi, Yi).

d = √ [ (X - Xi)² + (Y - Yi)² ]

(Applied for each of the 3 supply sources and 5 markets → 8 distances in total)

Total Transportation Cost Formula

Cost = sum of (shipping cost × quantity × distance) over ALL nodes.

Total Cost = Σ (F × D × d)

(In Excel: use SUMPRODUCT function)

5. Solving in Excel - Step-by-Step

Data Entry: Enter shipping cost (F), quantity (D), and coordinates (X, Y) for all nodes.
Define Decision Variables: Create two cells for X and Y (new factory) - initialize both to 0.
Distance Calculation: Use Euclidean distance formula for every supply source and market node.
Total Transportation Cost: Use SUMPRODUCT function: Total Cost = Σ (F × D × d)
Optimize using Solver: Data tab → Solver. Objective = minimize Total Cost. Variables = X, Y cells. Method = GRG Nonlinear.

Excel Solver - Centre of Gravity setup

Set Objective: the total-cost cell → Min
By Changing Variable Cells: the facility X and Y coordinate cells
Solving Method: GRG Nonlinear (the distance formula is non-linear)
Make Unconstrained Variables Non-Negative: ✓

The Solver is set to minimise total transportation cost by adjusting the facility’s X/Y coordinates, using the GRG Nonlinear engine because Euclidean distance makes the objective non-linear.

6. Excel Solver - Settings Detail

Set Objective: Select the Total Cost cell → set to Minimize.
Changing Variable Cells: Select X and Y cells (decision variables).
Solving Method: Use GRG Nonlinear - because the cost function is non-linear (due to the square root in the distance formula).

7. Result & Decision

After optimization: X ≈ 500, Y ≈ 600 (initially 0, 0)
These coordinates correspond to approximately Delhi.
Decision: Locate the new factory near Delhi to minimize total transportation cost.

What if the Optimal Location is Not Feasible?

Real-world constraints may prevent building exactly at (500, 600) due to land unavailability, regulations, lack of infrastructure, etc.
Solution: Explore nearby areas close to the optimal coordinates. Choose the nearest feasible location that meets secondary criteria (infrastructure, labour).

Session Summary

Facility Location: Strategic decision to find WHERE to build a new facility from scratch.
Centre of Gravity: Uses coordinates + demand + shipping cost to find cost-minimizing location.
Euclidean Distance: d = √[(X-Xi)² + (Y-Yi)²]
Total Cost: Σ(F × D × d) - minimized using Excel Solver (GRG Nonlinear).
Result: Optimal (X, Y) ≈ Delhi area; if infeasible, explore nearby locations.