top of page
Writer's pictureArpit Shah

Supply Chain Network Design Workflows using Excel and GIS

Updated: Aug 29

The topics which I write about on this Mapmyops website usually have a blend of two components - 'Mapping' & 'Operations'. The objective is to share informative content and subsequently connect it to my firm's mission which is 'to provide mapping solutions to organizations which facilitate operations improvement, planning & monitoring workflows'.


While Mapping is the underlying theme in most of my articles, some of them have the Operational aspects play a dominant role too, notably - 'Value Stream Mapping for Lean Manufacturing Operations', 'Indian Railways - A Massive Exercise in Operations', 'Depicting a Supply Chain on a Map', 'Using Mapping for Supply Chain Planning' & 'Selecting the Right Location for Businesses'. The December 2021 article on Indian Railways was particularly well-received by readers. A few of them sent in their complimentary messages and the editor of Rail Analysis magazine reached out and expressed interest to publish it in their upcoming issue.


In this article, I've attempted to place more emphasis on the Operational aspects involved in Site Selection Modeling - an integral of the Supply Chain Network Design workflow.

 

SECTION HYPERLINKED TABLE OF CONTENTS


  1. Site Selection as part of Supply Chain Network Design - the Fundamentals

  2. Spreadsheet-based Supply Chain Network Design for a Tyre Manufacturing Company 2.1 Two-Echelon Supply Chain Modeling Demonstration (Factory, Customers) - Optimizing for Lowest Distance 2.2 Two-Echelon Supply Chain Modeling Demonstration (Distribution Centers, Customers) - Optimizing for Lowest Distance 2.3 Two-Echelon Supply Chain Modeling Demonstration (Distribution Centers, Customers) - Optimizing Distribution Center Capacity & Cost 2.4 Three-Echelon Supply Chain Modeling Demonstration (Factories, Distribution Centers, Customers) - Optimizing Product Flows, Factory & Distribution Center Capacity, Service Component & Total Cost

  3. Comprehensive Geographic Information System (GIS)-based Site Suitability Modelling Demonstration for a Coworking Spaces Developer

 
  1. SITE SELECTION AS PART OF SUPPLY CHAIN NETWORK DESIGN - THE FUNDAMENTALS


Site Selection, as such, is a broad topic to write about. In the beginning, I'll share a theoretical background - thereafter, we'll dive into practical demonstration videos - first with Spreadsheet-based Network Design Modeling and then with Geographic Information System (GIS)-based Site Suitability Modeling.


(Credits: Supply Chain Modeling course by Baidhurya Mani & Esri's Learn ArcGIS' resources contributed immensely towards the formation of this content)


Let's start with the fundamentals first:

Supply Chain is the system in place involving multiple nodes and links - such as organizations, activities, people, information, money and resources - which come together to accomplish the fundamental business objective i.e. to deliver the Right Product in the Right Quantity at the Right Time to the Right Place (4 R's). Supply Chain Design or Network Design entails the discipline involved to determine the optimal location, number & capacity of facilities and the flow through it i.e. supply and demand allocation.
Supply Chain Network Design - A combination of Facility Location, Capacity Allocation & Supply and Demand Allocation
Figure 1: What does Supply Chain Network Design entail?

As you'll gather from the image above, Site Selection is an aspect within the broader Supply Chain Design framework and is inter-connected with the other two components (Capacity & Supply/Demand Allocation). 'Site' could mean any location - Factory, Warehouse, Hospital, Retail Store, Service Center etc.

Supply Chain Depiction on a Static Map
Figure 2: Static Map depicting Supply Chain nodes of a fictional company

Also, Site Selection decisions are not meant to be made in isolation - rather these are made keeping the business objective, customer needs and the overall Supply Chain implications in mind. Now, these don't tend to go hand-in-hand. Business objectives typically entail keeping the costs low whereas servicing customers better almost always means spending more.


This 'Trade-off' plays a crucial role in Supply Chain Design decisions - its implications on the organization's effectiveness to meet its business objectives are immense. These Cost and Service components can be translated into Supply Chain terminology, as depicted in the image below:


Cost v/s Service Trade-off Components
Figure 3: Cost v/s Service Trade-off Components

Now, you'll be able to better relate why certain businesses focus on 'Economies of Scale' (Manufacturing Industry) whereas others focus on wider 'Customer Reach' (Telecom & Retail Industry). Even within the same industry, Supply chain design can defer vastly - Think of Dell's distribution model (Pull-based) v/s that of any other Computing hardware manufacturer (Push-based).

Site Selection forms a crucial part of overall Supply chain Design. There is a method of arriving at Site Selection Decisions - beginning with the formulation of Supply Chain Strategy followed by development of Supply Chain Design configurations. Thereafter, the configurations are tested using spreadsheet or SCM software-based mathematical modeling and the final Network Design Strategy is selected after blending the quantitative Model results with qualitative inputs from concerned stakeholders. The approach of Site Selection is usually top-down in nature, beginning with macro-level evaluation and progressing towards micro-level adjustments, before being finalized.

In this article, I will demonstrate the Configuration and Testing aspects in particular. Let's begin...

 

2. SPREADSHEET-BASED SUPPLY CHAIN NETWORK DESIGN FOR A TYRE MANUFACTURER


Assume that ABC Tyres Ltd., a multinational Tyre manufacturer, is evaluating its Supply Chain Network Design as part of its entry strategy for the Indian market. It has forecasted the average Annual Demand for its Tyres at 100,000 units.


The State-wise breakdown of the Customer Demand is as follows -

State-wise Annual Customer Demand of Tyres
Figure 4: State-wise Annual Customer Demand of Tyres (Fictional)

Visually, it can be represented as below -

State-wise Annual Customer Demand
Figure 5: Map Representation of Figure 4 - State-wise Annual Customer Demand
 

2.1 Two-Echelon Supply Chain Modeling Demonstration (Factory, Customers) - Optimizing for Lowest Distance


Given this expected Customer Demand, ABC Tyres Ltd. would like to know which location would be ideal to set up a Tyre manufacturing plant, purely on the basis of the lowest average distance traveled by its product to reach the customers (Cost of Transportation being directly proportional to the Distance travelled).


Let's build a 'Greenfield Analysis' model using Microsoft Excel and solve it.


(Medium proficiency of Microsoft Excel necessary for the first four demo videos below. Also, all video walkthroughs in this article have been prepared keeping moderate-fast viewers in mind. Video Speed can be adjusted as per personal preferences)


Video #1 - Model I: Spreadsheet-based Network Design - Locating Single Factory for Tyre Co. - Optimizing for Lowest Distance


The model determined Chahali in North-East Madhya Pradesh (blue marker on the map below) as the Suitable Site based on the Weighted Average Customer Demand. Fairly understandable, given the centrality of the location in India.


Site Selection - Supply Chain Design
Figure 6: Site Selected in Model I
 

2.2 Two-Echelon Supply Chain Modeling Demonstration (Distribution Centers, Customers) - Optimizing for Lowest Distance


ABC Tyres next introspects on an alternative Network Design strategy. Instead of a single factory, it wishes to examine the feasibility of setting up 2 Distribution Centers (DCs) in India which would dispatch 'imported' tyres to the customers.


(The term Warehouse and DC is interchangeably used but there is a vital difference between the two - Warehouse just 'stores' the products whereas a Distribution Center also 'ships' the product to the customer directly from storage besides doing other value-added activities.)


In the next video, we'll determine the same by extending our previous 'Greenfield Analysis' model.


(Watching the previous video is recommended)

Video #2 - Model II: Spreadsheet-based Network Design - Locating Two Distribution Centers - Optimizing for Lowest Distance


In case you haven't seen the video above, the map below depicts the spreadsheet-based model's output. The two DCs can be optimally located in the Eastern (Bihar) and Southern (Karnataka) regions of India. Pink-shaded States will be serviced by Patna DC in Bihar, while Green-shaded States will be serviced by Koppal DC in Karnataka so as to arrive at the optimal solution, that of lowest Weighted Average distance for Tyre Transportation.


Site Suitability Modelling using Microsoft Excel
Figure 7: One Output from Model II

Please note that the model iterates the Decision Variables and identifies the best-fitting configuration that meets the Objective Function. Often, there is more than one optimal result based on the Decision Variables supplied. The map below is another 'optimal' output from the same model. The two DCs are now located in Madhya Pradesh & Meghalaya respectively with the former catering to all of India barring the East & North-Eastern states. ABC Tyres' Leadership now have to decide which Network Design 'configuration' to adopt.

Site Suitability Modelling using Spreadsheet
Figure 8: Another Output from Model II
 

2.3 Two-Echelon Supply Chain Modeling Demonstration (Distribution Centers, Customers) - Optimizing Distribution Center Capacity & Cost


So far our criteria of selecting a site has been using the 'Lowest Distance' optimization objective. We haven't incorporated 'Location / Storage Costs' into our model. Also, Site Selection may not always involve choosing the optimal 'mathematical' output as determined by the model. Often, we may have to choose from the configuration options available that are nearest to the model's output (available property locations, for example). Moreover, a model typically captures Quantitative variables only. Qualitative inputs are also critical in determining appropriate Site Locations.


In our next Site Selection model below, we'll take into account both Transportation Distance and Site Cost parameters.


(Watching the previous videos is recommended)

Video #3 - Model III: Spreadsheet based Network Design - Locating Two Distribution Centers for Tyre Co. - Optimizing Distribution Center Capacity & Total Cost


For those who haven't seen the video above, initially, we had solved for lowest Total Transportation Cost and obtained the map result below - similar DC & State Allocation output as in Figure 8 with the exception of Odisha was generated by the model. This can be attributed to the fact that Transportation Costs are in essence, a function of Transportation Distance.

Site Suitability Modelling using Spreadsheet
Figure 9: Lowest Transport Cost Output from Model III

However, when we added a new variable - that of Distribution Center Capacity configurations and its Annual Operating Costs - and optimized for Lowest Total Cost thereafter, we were in for a surprise! The optimal Distribution Center location output changed to Bihar & Karnataka respectively and the Supply Allocation changed significantly from the previous output too. This can be attributed to the selected DCs 'lower operating costs' for the capacity configuration selected by the model, when compared to the other two DCs which only had low Transportation Costs in their favor. Goes on to show how the 'optimal solution' can be heavily influenced by addition / modification of new Constraints & Parameters.

Site Suitability Modelling using Spreadsheet
Figure 10: LTC + Capacity Optimization Output from Model III
 

Hope you are enjoying the demonstration thus far! It is about to get a whole lot interesting...


2.4 Three-Echelon Supply Chain Modeling Demonstration (Factories, Distribution Centers, Customers) - Optimizing Product Flows, Factory & Distribution Center Capacity, Service Component & Total Cost


In terms of Supply Chain nomenclature, the three examples which I've used to demonstrate Site Selection to you till now involved 'Two-Echelon' networks. Two-Echelon means the Supply Chain contains two category of nodes. As you'd recall, we've built models involving 'Factory & Customers' & 'DCs & Customers' previously. Hence, the number of types of Supply Chain nodes were two.


Now, we'll progress to Site Selection modeling in a Three-Echelon Supply Chain Network involving 'Factories, Distribution Centers & Customers'.


Not only that, we'll model in a Service Component too. 'What is this?', you may wonder.


Let me explain - you may know that the e-commerce giant - Amazon, offers guaranteed 'Next-day delivery' to those customers who avail its Prime subscription. Because the customers pay additionally for this facility, Amazon extends this superior service benefit to them. If you'll think about this in Operational terms, Amazon must have made its Supply Chain very flexible to make this high-responsiveness strategy possible - a robust network of DCs, multiple pickup touch-points, super-fast shipping, and robust last-mile delivery infrastructure.


Had there been no such Delivery Guarantees (Customer-centricity) on offer, Amazon would have made its entire Supply Chain Cost-effective with fewer and high-capacity nodes. However, it chose to increase its Supply Chain costs to allow for better Customer Service as the modern-day shopper demands ultra-quick delivery and is willing to pay a premium for it.


Yes, you guessed it right - this is another example of the 'Supply Chain Trade-off' which I explained earlier in this article. Another example of focusing on the Service Component in Supply Chain Design is - Domino's' popular '30-minute Pizza delivered or Free' policy. The popular restaurant chain is able to extend this facility to the customer by having multiple, geographically dispersed outlets within a city/town which allows it to transport Pizza to any Customer Location within the assured time frame.


This additional 'Service Component' can be quantified as a Supply Chain Decision Variable - the 'within 30 minutes' scheme could be interpreted as two restaurants should be located not farther than 10 kilometers from each other as a restaurant can successfully cater to customers in the predefined timeline of 30 minutes only if they are located within a 5 kilometer radius. Now that we've 'quantified' this qualitative Service parameter - we can model and optimize it too! (demonstrated in the latter half of the video below).


(Watching the previous videos is recommended)

Video #4 - Model IV: Spreadsheet based Network Design - Locating Factories & Distribution Centers - Optimizing Supply & Demand Flows, Capacity, Service Component & Total Supply Chain Cost


For those who haven't seen the video above, at first we solved for the lowest Total Transportation Cost in a Three-Echelon Supply Chain Configuration. The three nodes are Factories (2), DCs (4) & Customers (28). ABC Tyres is fixated on setting up only two Distribution Centers. Additionally, I have modeled in Plant Capacity and Supply & Demand Flows as well. The map output below is very similar to our previous model's output, albeit with a couple of changes in DC - Customer linkages.

Site Suitability Modelling (Siting) using Spreadsheet
Figure 11: LTC, Capacity, Demand + Supply Optimization Output in Model IV

Next, we've modeled in the Service Component to bring down Weighted-Average Distance to 750 kilometers or less from the existing 900 kilometers so as to facilitate a 'within three-day Tyre Delivery' guarantee which ABC Tyres intends to offer to its customers in India.


At first, I relaxed the 2 Distribution Center constraint incorporated in the previous output so as to make the new model free-flowing. Thereafter, I plugged in the Service Component as an additional constraint. Microsoft Excel's Solver optimized for a 3-Distribution Center recommendation with the following Network Design as depicted in Figure 12 below.


That's not all, we found some pretty interesting insights about how the optimized Supply Chain Design output defies expectations, yet is so deeply entrenched in common sense & wisdom - Check out the last two minutes of the Video #4 above!

Site Suitability Modelling (Siting) using Spreadsheet
Figure 12: Adding Service Component to Model IV Output generated in Figure 11
 

This brings an end to this comprehensive section on Spreadsheet-based Site Selection & Modeling. I hope you appreciated how Business Strategy influences Supply Chain Network Design in general & Site Selection in particular, through the various scenarios which I've attempted to demonstrate in an easy-to-grasp, practical manner.


Additionally, you may have appreciated the positive impact of the Map-based output - it complements and visualizes spreadsheet output in such a better way.

Maps are a very powerful visualization tool and lend much deeper meaning to overall Supply Chain Design and possibilities.

While we solved our models on Microsoft Excel using the inbuilt Solver Tool, as the models became complex, Excel took considerably more time to iterate and during the last model, I actually had to use Open Solver plugin to reduce the model's processing time. However, these are just tiny models. In real life, Network Design Modeling typically entails factoring in hundreds of constraints! There are dedicated softwares such as Supply Chain Guru which are much better equipped to handle industrial-grade Network Design Modeling requirements.

 

3. COMPREHENSIVE GEOGRAPHIC INFORMATION SYSTEM (GIS)-BASED SITE SUITABILITY MODELLING DEMONSTRATION FOR A CO-WORKING SPACES DEVELOPER


Next, I'll bring to your attention another powerful way of addressing Site Selection requirements - by using Map-based Suitability Modeling on advanced Spatial Analytics platforms popularly known as GIS (Geographic Information System). These can be utilized by any business either on a standalone basis or as a useful extension to Spreadsheet-based Supply Chain Network Design Modeling.


While we've used Maps in this article thus far purely for visualization purposes - on a GIS platform one can perform powerful Geospatial Analytics to determine the ideal locations for setting up new sites and to arrive at the optimal Supply Chain Network Design based on the organization's constraints, preferences and objectives.


Allow me to establish the Video Walkthrough #.5's context first. It involves USpace Realty - a company which operates Co-working Spaces in urban centers of India. As part of its business expansion plans, it wants to identify five suitable locations in Mumbai (India) where it could acquire new properties to be developed as Co-working spaces. USpace's Leadership proposes to consider these Site Selection parameters-

Site Selection (Suitability Modelling)
Figure 13: Site Selection Preferences / Methodology / Flowchart as determined by USpace Leadership

I've built a Site Selection Model using 'Suitability Modeling' on Esri's ArcGIS Pro platform. You may see the elaborate video demonstration below. Beyond the technology involved, I've tried to lay special emphasis on the problem-solving methodology as well: aspects such as business objectives, geodata sourcing mechanisms, explanation of rationale behind model choices, and alternative options available, among others.


Video #5 - Elaborate GIS-based Site Suitability Model (From Data Acquisition & Setup to Analysis & Visualization using 2-Phase Methodology)


The Modeling was done in two phases - a) first by creating Distance-based Rasters from a few parameter Vector datasets which resulted in the entire Area of Interest being color-coded and b) by assigning Weights to other parameter Vector datasets in an attempt to identify suitable Coworking zones compatible with it. Thereafter, both a) and b) output was merged to determine the most favorable locations within Mumbai to set up the new Coworking spaces.


Site Suitability Modelling using GIS - Siting
Figure 14: Representative Image of a) Phase 1: Suitability Modeling
Site Suitability Modelling using GIS - Siting
Figure 15: Representative Image of b) Phase 2: Scoring & Assigning Weights
Site Suitability Modelling using GIS - Siting
Figure 16: Final Output of GIS-based 2-Phase Suitability Modeling: Suitable Sites for USpace Coworking in Mumbai, MH

Wasn't this interesting!

 

You can review / share all the Demonstrations (sequentially compiled) with your friends & colleagues-


Video #.6: All five videos of this article combined into a single chronologically-arranged video

 

ABOUT US


Intelloc Mapping Services | Mapmyops.com is based in Kolkata, India and engages in providing Mapping solutions that can be integrated with Operations Planning, Design and Audit workflows. These include but are not limited to - Drone ServicesSubsurface Mapping ServicesLocation Analytics & App DevelopmentSupply Chain Services & Remote Sensing Services. The services can be rendered pan-India, some even globally, and will aid an organization to meet its stated objectives especially pertaining to Operational Excellence, Cost Reduction, Sustainability and Growth.


Broadly, our area of expertise can be split into two categories - Geographic Mapping and Operations Mapping. The Infographic below highlights our capabilities.

Mapmyops (Intelloc Mapping Services) - Range of Capabilities and Problem Statements that we can help address
Mapmyops (Intelloc Mapping Services) - Range of Capabilities and Problem Statements that we can help address

Our 'Mapping for Operations'-themed workflow demonstrations can be accessed from the firm's Website / YouTube Channel and an overview can be obtained from this flyer. Happy to address queries and respond to documented requirements. Custom Demonstration, Training & Trials are facilitated only on a paid-basis. Looking forward to being of service.


Regards,

659 views

Recent Posts

See All
bottom of page