Supply Chain Network Design Modelling using Excel and GIS
- Arpit Shah
- Jun 30, 2022
- 13 min read
Updated: Dec 11
INTRODUCTION
While Geographic Mapping is the dominant theme across most posts on this website, several of them also feature strong elements of Operations Mapping—Value Stream Mapping for Lean Manufacturing Operations, Indian Railways - A Massive Exercise in Operations Management, Basic Supply Chain Mapping, GIS-based techniques for Logistical Planning and Shortlisting Suitable Sites using Geospatial Business Analytics. My recent post on the operational aspects of the Indian Railways was well received; a few readers wrote in with appreciative messages, and the editor of Rail Analysis magazine reached out to request publication in an upcoming issue.
I am excited to present this comprehensive, practical guide on Supply Chain Network Design Modelling using Spreadsheet Solver and Site Suitability Modelling using Geographic Information Systems to you. The former demonstrates business analytics, while the latter employs location analytics.
In many ways, this post captures the essence of what I offer through Intelloc Mapping Services—the commercial entity behind Mapmyops.com—which is to provide Mapping solutions integrated with Operations Planning, Design, and Audit workflows.
SECTION HYPERLINKS
Spreadsheet-based Supply Chain Network Design Modelling Demonstration 2.1 Two-Echelon Supply Chain (Factory, Customers) - Optimizing for Lowest Transportation Distance 2.2 Two-Echelon Supply Chain (Distribution Centers, Customers) - Optimizing for Lowest Transportation Distance 2.3 Two-Echelon Supply Chain (Distribution Centers, Customers) - Optimizing Distribution Center Capacity & Total Supply Chain Cost 2.4 Three-Echelon Supply Chain (Factories, Distribution Centers, Customers) - Optimizing Product Flows, Factory & Distribution Center Capacity, Service Component & Total Supply Chain Cost
2.5 Three-Echelon Supply Chain Modelling (Farms, Factory, Distributors) - Lowering Total Supply Chain Cost factoring in Green Component
Geographic Information System (GIS)-based Site Suitability Modelling Demonstration
Credits: Supply Chain Modelling course by Baidhurya Mani and Learn ArcGIS tutorials by Esri
Let's begin with some definitions-
A Supply Chain is the system comprising nodes and linkages—organizations, activities, people, information, and resources—that work together to deliver the Right Product, in the Right Quantity, at the Right Time, to the Right Place.
Supply Chain Network Design is the discipline of determining the optimal number, location, and capacity of facilities within the Supply Chain, as well as the material flows between them—that is, how supply should be allocated to meet demand.
Network design decisions are never made in isolation. They must consider business objectives, customer expectations, internal value streams, and the implications for external stakeholders. These aspects often conflict—for example, profitability goals require cost reduction, while customer service goals require investment. Such trade-offs sit at the heart of Supply Chain Design.
Figure 3 below illustrates key Cost and Service components impacted by these trade-offs.
An organization must optimize these trade-offs—striking the right balance between competing objectives. This is the essence of modelling a Supply Chain Network: applying dynamic mathematical frameworks to describe, simulate, and analyze decisions and scenarios in a quantitative manner. The next section demonstrates this in detail.
Businesses often adopt network strategies commonly practiced in their industry—cement firms pursue economies of scale, while quick-service restaurant chains prioritize widespread presence. Even within the same industry, Supply Chain configurations can differ significantly due to internal operating models—consider Dell’s Pull-based distribution approach versus the traditional Push-based approach of other computer manufacturers in the early 2000s.
2. SPREADSHEET-BASED SUPPLY CHAIN NETWORK DESIGN MODELLING DEMONSTRATION
ABC Tyres Ltd., a multinational tyre manufacturer, intends to use Supply Chain Network Design techniques to develop a viable entry strategy for the Indian market. The company forecasts an annual demand of 100,000 tyres for its first three years of operation. The state-wise demand distribution is as follows:
Since the dataset includes location attributes, the demand can be plotted on a map:
2.1 Two-Echelon Supply Chain Modeling (Factory, Customers) - Optimizing for Lowest Transportation Distance
Given this forecast, ABC Tyres wishes to identify the ideal location for a manufacturing plant—based solely on minimizing average transportation distance from factory to customers (a proxy for minimizing transportation cost). The workflow uses Greenfield Analysis, demonstrated in the video below.
Familiarity with Microsoft Excel is necessary for following all demonstrations in this section.
The model identified Chahali in Madhya Pradesh as the optimal site for the plant—unsurprising given its geographic centrality.
2.2 Two-Echelon Supply Chain Modelling (Distribution Centers, Customers) - Optimizing for Lowest Transportation Distance
ABC Tyres then explores an alternative strategy. Instead of building a factory in India, it considers establishing two Distribution Centers (DCs) to store imported tyres and serve customers nationwide. The revised objective is solved once again using Greenfield Analysis.
A quick note: Although the terms Warehouse and Distribution Center are used interchangeably, they differ significantly. A warehouse stores products; a DC stores and ships products to customers and may perform value-added activities.
Watching Video 1 beforehand is recommended
The model presented in Figure 7 suggests siting the DCs in Bihar (East) and Karnataka (South). The Bihar DC (pink region) services the northern and eastern states, while the Karnataka DC (green region) services the rest. This configuration yields the lowest total outbound transportation distance.
As you would have seen in the demonstration videos, the Solver tool in Microsoft Excel iterates the decision variables to identify the configuration that best satisfies the objective function. Multiple optimal solutions may exist if the constraints are not restrictive enough.
Figure 8 presents an alternative optimal solution: siting the DCs in Madhya Pradesh (Central India) and Meghalaya (Northeast). The MP DC services most of India, while the Meghalaya DC services the eastern and northeastern states.
ABC Tyres can now refine the model by adding more constraints, evaluate the pros and cons of each configuration, or even revert to the Model 1 recommendation of setting up a factory. In reality, siting decisions cannot rely solely on mathematical outputs. They require feasibility checks—availability of industrial land, alternate connectivity in case of road blockages, political climate for doing business, availability of labour, and anticipated regional development over the coming years.
2.3 Two-Echelon Supply Chain Modelling (Distribution Centers, Customers) - Optimizing Distribution Center Capacity & Cost
So far, the criteria for shortlisting suitable locations—whether for a factory or for distribution centers—has been based solely on minimizing total outbound transportation distance (the objective function). Until now, I have not incorporated material storage costs, an important component of Supply Chain Design. This is what I will demonstrate next.
Watching the previous videos is recommended
As demonstrated in Video 3, I first extended Model 2 by adding transportation cost data to individual outbound lanes. I then optimized for the lowest total outbound transportation cost (as opposed to transportation distance, as done earlier). This transition is critical because we are now optimizing the Total Supply Chain Cost, and therefore all decision variables must be expressed in cost terms. Distance-based parameters alone would no longer be meaningful.
Since transportation cost is typically proportional to transportation distance, the new optimal output (Figure 9) does not differ significantly from Model 2 (Figure 8). The proposed DC locations remain the same, and the customer allocations are almost identical—with the exception of Odisha, which is now serviced by the Madhya Pradesh DC instead of the Meghalaya DC.
Upon investigating property availability at the four optimal DC locations suggested thus far, ABC Tyres discovered that each site is available in three different capacity configurations—with varying maximum storage limits and annual operating costs (rent, manpower, and other overheads).
To account for this, I introduced a new decision variable into the model: DC Capacity Configuration, along with the corresponding operating cost. I then optimized for the lowest Total Supply Chain Cost, defined as: Total Outbound Transportation Cost + Total DC Operating Cost.
As expected, the model responded to the new inputs and determined not only the optimal locations but also the most cost-efficient capacity configuration for each DC. Ultimately, it suggested placing the DCs in the states of Bihar (East) and Karnataka (South), optimizing both capacity selection and outbound transportation flows.
The updated Supply Chain Network is depicted in Figure 10 below.
The map output serves only as a high-level visual reference for shortlisted locations and customer allocation. The spreadsheet-based optimization model determines material flows, capacity configurations, and the minimized Total Supply Chain Cost—none of which is visible on the map. You will need to refer to the video demonstration to fully understand these internal workings.
2.4 Three-Echelon Supply Chain Modelling (Factories, Distribution Centers, Customers) - Optimizing Product Flows, Factory & Distribution Center Capacity, Service Component & Total Cost
Hope you’re enjoying the demonstration so far—because this is where it becomes truly interesting.
Until now, we have worked with two-echelon networks, where the Supply Chain comprised two categories of nodes. Earlier models used combinations such as Factory → Customers and Distribution Centers → Customers.
We now progress to a three-echelon Supply Chain Network—Factories → Distribution Centers → Customers. ABC Tyres wishes to evaluate the prospect of establishing a complete Supply Chain within India, featuring two factories and two distribution centers, to ensure faster customer service. Since “service” is in focus now, I will incorporate it into the model as well.
You may wonder: Is Service not a qualitative parameter?
Indeed, it is. Qualitative parameters cannot be directly incorporated in optimization models. However, in certain contexts, they can be converted into quantifiable terms and integrated into the model.
For instance:
Amazon’s next-day delivery promise for Prime customers is a qualitative commitment translated into quantitative operational constraints—dense DC networks, reliable last-mile infrastructure, and higher logistics cost.
Domino’s 30-minute delivery guarantee also becomes quantifiable. Most people do not realize that Domino’s reserves only 8 minutes for the actual delivery run—the remaining time is for cooking and order processing. This 8-minute window translates into a geographic radius of roughly 2 km from each outlet.
Such qualitative commitments can be modeled as quantitative constraints in a Network Design model.
I have incorporated a similar service component for ABC Tyres in the latter half of the three-echelon demonstration below.
Watching the previous videos is recommended
In Video 4, I first optimized the Total Transportation Cost in a three-echelon network. This involved identifying:
The optimal locations for two factories
Their production capacities
Two DCs from the four available options
The capacity configuration for each DC
Optimal inbound flows (Factories → DCs)
Optimal outbound flows (DCs → Customers)
And ultimately, the lowest Total Supply Chain Cost
The optimized network is shown in Figure 11 below - In this configuration, the model suggests locating the factories in Meerut, Uttar Pradesh (North) and Bhuj, Gujarat (West). The Meerut plant supplies exclusively to the Bihar DC, while the Bhuj plant supplies exclusively to the Karnataka DC. Compared with the previous model (Figure 10), DC locations remain unchanged, though certain customer allocations—such as Andhra Pradesh and Madhya Pradesh—are rebalanced.
Next, I incorporated the Service Component, targeting a reduction in the Weighted-Average Transportation Distance—from 900 km down to 750 km per tyre. ABC Tyres believes this will enable a guaranteed three-day delivery to customers nationwide.
To enable the model to meet this new target, I had to relax the constraint that allowed only 2 DCs out of 4. Since the earlier configuration had already been optimized to 900 km, the model would not be able to reduce this any further without additional flexibility. Allowing the selection of 3 DCs out of 4 provided the model with the necessary latitude to achieve the 750 km target.
Whether this improved service level increased or decreased Total Supply Chain Cost is demonstrated in the video. The new network configuration is shown below.
The model now selects DCs in Bihar (East), Madhya Pradesh (Central), and Karnataka (South)—dropping Meghalaya (Northeast) from the earlier options
Did you notice something peculiar?
Bihar now services far-flung states like Punjab and Rajasthan.
Karnataka and Madhya Pradesh are not serving customers within their own states!
This is not an error. It is the nature of optimization: Local optima must give way to global optimum.
In Supply Chain terms, the network must function as a unified system, not as isolated nodes optimizing their own performance. The pan-India objective of achieving 750 km weighted-average travel distance takes precedence.
Not entirely. Our planet is warming rapidly, and without strong intervention from individuals, corporations, and governments, the consequences will be devastating for future generations. One advantage of modelling Supply Chain Networks is the ability to incorporate Environmental or Green components—since emissions are directly linked to transportation distance (inbound + outbound).
That is exactly what I will demonstrate next.
2.5 Three-Echelon Supply Chain Modelling (Farms, Factory, Distributors) - Lowering Total Supply Chain Cost factoring in Green Component
This demonstration is not a continuation of the previous example. Instead, using a fictitious scenario based on the Supply Chain of Kissan, a well-established food brand in India, I’ll show how to design an optimal three-echelon Supply Chain Network involving: Agri-Farms (Suppliers) → Kissan (Manufacturer) → Distributors (Customers)
The objective this time is to minimize Greenhouse Gas (GHG) emissions, specifically CO₂ generated from inbound and outbound transportation. Reducing GHG emissions contributes directly to United Nations Sustainable Development Goal #13 on Climate Action, which calls for urgent measures to prevent global warming from exceeding 1.5°C above pre-industrial levels (we have already crossed 1.1°C).
Video 5 below demonstrates the following:
Optimizing material flows to achieve the lowest Total Supply Chain Cost
Incorporating a 5% reduction target for total GHG emissions (inbound + outbound CO₂)
How the model adapts to the new environmental constraint and re-optimizes flows accordingly
Slider 1: Comparing the optimized network vs. the GHG-optimized network for Kissan
With this, I conclude the comprehensive section on Spreadsheet-based Supply Chain Network Design Modelling. I hope the demonstrations helped you understand how business strategy influences network design decisions, and how a mathematical model dynamically responds to practical parameters and constraints. I also hope the inclusion of map-based visualizations offered useful context and complemented the spreadsheet outputs effectively.
While I solved these models using Microsoft Excel’s built-in Solver tool, the computational load naturally increased as the parameters became more complex. For instance, I had to use the Open Solver plugin for Model 4 to reduce processing time. These demonstrations represent only simplified samples; in real-world scenarios, Supply Chain Network Design Modelling often involves hundreds of decision variables and constraints.
For such industrial-grade problems, organizations typically rely on dedicated mathematical programming software, such as Supply Chain Guru, which is far better equipped to handle these workflows efficiently.
3. GEOGRAPHIC INFORMATION SYSTEM (GIS)-BASED SITE SUITABILITY MODELLING DEMONSTRATION
Siting—the process of selecting a suitable location for a facility (factory, warehouse, retail store, or service center)—is a key component of Supply Chain Design. It is directly linked to two other pillars:
Capacity Allocation, and
Supply & Demand Allocation
The steps involved in making site-selection decisions generally follow a top-down progression:
Formulate the Supply Chain Strategy
Develop Supply Chain Design configurations
Test the configurations using Mathematical Modelling
Blend quantitative output with qualitative feedback from stakeholders
Shortlist the final suitable sites
This approach typically begins with macro-level assessments (regional suitability, connectivity, environmental factors) and gradually moves into micro-level refinement (local feasibility, land availability, regulatory conditions).
Until now in this post, I have used map visualizations largely for illustrative purposes. However, with a Geographic Information System (GIS) platform, one can perform powerful geospatial analytics to determine optimal locations based on the organization’s objectives, preferences, and constraints.
In this section, I will demonstrate precisely that—using a GIS-based Suitability Modelling technique in Esri ArcGIS Pro. This technique can be used independently or as an extension to the Spreadsheet-based Supply Chain Network Design models covered earlier.
USpace Realty—a fictitious real estate company—operates coworking spaces in urban centres across India. As part of its expansion plan, the firm wants to shortlist five suitable locations in Mumbai (West India) to develop new coworking centres. The list below outlines the siting parameters that USpace intends the modeller to incorporate into the study.
In the demonstration video below, I have incorporated several of these parameters while placing equal emphasis on the methodology of problem-solving, not just on the capabilities of geospatial technology. The walkthrough touches upon aligning siting preferences with business objectives, sourcing geodata, understanding the rationale behind each parameter, and exploring viable alternatives.
The Site Suitability Modelling workflow was executed in three phases-
The shortlisted parameter layers—originally in vector format—were converted into distance-based raster layers. This step ensures that the Suitability Modeler can evaluate and score the entire study area (Mumbai) continuously rather than at discrete vector locations.
Figure 14: Phase 1 - creating Distance-based rasters of the shortlisted parameters and having the Suitability Modeler categorize the study area Some parameters cannot be converted into rasters in a meaningful way. These are instead assigned weights and ranks based on their relative importance in the decision-making process.
Figure 15: Phase 2: Assigning weights and subsequently, ranking those Vector data layers which cannot be converted into Rasters for Suitability Modelling The final step merges the weighted raster results from Phase 1 with the ranked vector layers from Phase 2. The synthesized output reveals the most favorable sites within Mumbai for developing USpace Realty’s new coworking centres.
Excited to explore the full methodology and see how the technology works in practice?
Here is the demonstration:
I hope you enjoyed this demonstration. If you'd like to share the modelling workflows covered in this post with friends or colleagues, here is a compilation (note: it excludes Video 5 on integrating the Green Component into Supply Chain Network Design, which was created later):
ABOUT US - OPERATIONS MAPPING SOLUTIONS FOR ORGANIZATIONS
Intelloc Mapping Services, Kolkata | Mapmyops.com offers a suite of Mapping and Analytics solutions that seamlessly integrate with Operations Planning, Design, and Audit workflows. Our capabilities include — but are not limited to — Drone Services, Location Analytics & GIS Applications, Satellite Imagery Analytics, Supply Chain Network Design, Subsurface Mapping and Wastewater Treatment. Projects are executed pan-India, delivering actionable insights and operational efficiency across sectors.
My firm's services can be split into two categories - Geographic Mapping and Operations Mapping. Our range of offerings are listed in the infographic below-
A majority of our Mapping for Operations-themed workflows (50+) can be accessed from this website's landing page. We respond well to documented queries/requirements. Demonstrations/PoC can be facilitated, on a paid-basis. Looking forward to being of service.
Regards,
