Map, Design & Optimize Supply Chain Network using Spreadsheet & GIS
Updated: May 13
The topics which I write about on this Mapmyops Geo-blog 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 that facilitate operations improvement, planning & monitoring workflows. These include but are not limited to Supply Chain Consulting, Drone Services, Location Analytics & Applications, Site Characterization, Satellite Imagery Analytics & Polluted Water Remediation.'
While Mapping is the underlying theme in most of my articles, some of them have the Operational aspects play a dominant role too, notably - '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 reached out and expressed interest to publish it in the next issue of their magazine.
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.
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 Map-based Site Suitability Modeling.
(Supply Chain Modeling by Baidhurya Mani & 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.
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 business location - factory, warehouse, fulfillment center, retail shop, service center etc.
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:
Now, you'll be able to better relate why certain businesses focus on economies of scale (Hypermarkets) whereas others focus on wider customer reach (Telecom & Retail brands). Even within the same industry, Supply chain design can defer vastly - Think of Dell's distribution model (pull) v/s that of any other Computing hardware manufacturer (push).
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 SC Design configurations. Thereafter, the configurations are tested using spreadsheet or SCM software-based mathematical modeling and the final network strategy is selected after blending the model's results with stakeholder inputs. The approach of site-selection is usually top-down in nature, beginning with macro-level evaluation and progressing towards micro-level adjustments before finalizing it.
In this article, we will focus in a practical way, on the Configuration and Testing aspects in particular. Let's begin...
Assume that ABC Tyres Ltd., a multinational manufacturer, is evaluating its supply chain design as part of its entry strategy in the Indian market. It has forecasted the average annual demand for its tyres as 100,000 units. State-wise breakdown of the customer Demand is as follows -
Visually, it can be represented as below -
Given this expected customer Demand, ABC Tyres Ltd. would like to know which location could 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.
Let's build a 'Greenfield Analysis' model using Microsoft Excel and solve it.
(Basic Knowledge of Microsoft Excel necessary for the first four videos below. Also, all video walkthroughs in this article have been prepared keeping moderate-fast readers in mind. Video Speed Settings can be adjusted on YouTube to accommodate 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.
ABC Tyres next introspects on an alternative 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 is used just to 'store' the products whereas a Distribution Center also 'ships' the product to the customer directly from the storage location aside from doing other value-added activities.)
In the next video, we'll compute 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 DCs - Optimizing for Lowest Distance
In case you haven't seen the video above, the map below reflects the 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.
Please note that the model iterates the decision variables and identifies the best-fitting configuration that meets the objective function. Often, Optimization tools yield more than one optimal result. 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 design configuration to adopt.
So far our criteria of selecting a site has been using the 'Lowest Distance' optimization objective. We haven't incorporated 'Costs' into our model. Additionally, site selection may not always involve choosing the optimal mathematical output. Rather, we may have a few design configurations pre-determined and shared with us from which we have to determine the most suitable configuration.
In our next site selection model below, we'll take into account both these parameters.
(Watching the previous videos is recommended)
Video #3 - Model III: Spreadsheet based Network Design - Locating Two DCs for Tyre Co. - Optimizing DC 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 barring Odisha). This can be partly attributed to the fact that transportation costs are in essence, a function of transportation distance.
However, when we added a new variable - that of DC Capacity configurations and its annual operating costs - and optimized the total cost thereafter, we were in for a surprise! The optimal DCs 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 particular capacity configuration selected by the model, when compared to the other two DCs which only had the transportation costs in their favor. Goes on to show how the 'optimal solution' can be heavily influenced by addition / modification of new constraints & parameters.
Hope you are liking it, thus far. It is about to get a whole lot interesting!
In terms of supply chain nomenclature, the three examples which I've used to demonstrate site selection to you till now have been '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 Node categories (Echelons) have been two.
Now, we'll progress to site selection in a Three-Echelon supply chain model involving 'Factories, DCs & Customers'. Not only that, we'll model in a Service Component too.
'What is Service Component?', you may wonder.
Let me explain - you might know that the e-commerce giant - Amazon, offers guaranteed 'Next-day delivery' to those customers who avail its Prime subscription. Because the customers pay a premium for this facility, Amazon extends this superior service to them. If you'll think about this in operational terms, Amazon must have made its supply chain very responsive and flexible to make this possible - a robust network of DCs, multiple pickup touch-points, and super-fast shipping and last-mile delivery infrastructural arrangements.
Had there been no such product delivery guarantees (customer-centricity) on offer, Amazon would have made its supply chain cost-effective with fewer, bulky 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.
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 '30-minute Pizza delivery or Free' policy. The popular restaurant is able to extend this facility to the customer by having several restaurants at key hubs within a city/town which would allow it to transport pizza to customer locations within the set time frame and fulfill its guarantee.
This extra 'Service' can be quantified in supply chain terms - within 30 minutes could very well mean that two restaurants should be located within 10 kms of each other as each restaurant can cater to customers located only within 5 km radius under the predefined time of 30 minutes. Once we 'quantify' this qualitative parameter that is 'Service' - we can model it and optimize it too! as I'll demonstrate in the latter half of the video below-
(Watching the previous videos is recommended)
Video #4 - Model IV: Spreadsheet based Network Design - Locating Factories & DCs - Optimizing Supply & Demand Flows, Capacity, Service Component & Total Cost
For those who haven't seen the video above, at first we solved for the lowest Total 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-DCs. Additionally, we'd modeled in Plant capacity and Supply & Demand flows. The map output below is very similar to our previous model's output, albeit with a couple of changes in customer allocation.
Next, we've modeled in the Service component to bring down Weighted-Average Distance to 750 kms or less from the existing 900 kms so as to facilitate a 'within Three-Day Tyre Delivery' guarantee which ABC Tyres intends to offer to its customers.
At first, we relaxed the 2-DC constraint so as to make the model free-flowing. Thereafter, we plugged in our Service objective as a constraint. Excel's Solver produced a 3-DC recommendation with the following configuration 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 analyst expectations, yet the proposed solution is so deeply entrenched in common sense & wisdom! Check out the last two minutes of the Video #4 above.
This brings an end to this comprehensive section on Spreadsheet-based Site Selection & Modeling. I hope you appreciated how Business & Supply Chain Strategy influences 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'd have likely appreciated the impact Map-based output had alongside the spreadsheet output. 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 the complexities and during the last model, I actually had to use Open Solver plugin to reduce the processing time. However, these are just tiny models. In real life, 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.
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 commonly known as GIS (Geographic Information System). These can be utilized by any industry 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 purely for visualization purposes so far - on a GIS platform one can perform powerful spatial analytics to determine the ideal location for setting up new sites so as to arrive at the optimal Supply Chain design based on our constraints, preferences and objectives.
Allow me to establish the Video Walkthrough #5's case context first. It involves another company - USpace Real Estate - which builds and operates Co-working spaces in urban centers of India. As part of its business expansion plans, it wants to shortlist five suitable locations in the thriving metropolis of Mumbai, Maharashtra where it could acquire new properties suitable to be developed as co-working spaces. USpace's leadership would like to consider the parameters listed below. These are their Site Selection preferences-
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, Geo-data 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 to color-code zones via the Suitability Modeler tool and b) Scoring & assigning Weights to other parameter Vector datasets in an attempt to identify suitable zones compatible with it. Thereafter, both a) and b) outputs were merged to determine the most favorable locations within Mumbai to set up the new Co-working spaces.
Wasn't this interesting?
You can even share the combined video below (sequentially arranged) with your friends & colleagues-
Video #6: All five videos of this article combined into a single chronologically-arranged video
Several other mapping and operations workflows are documented on this Geo-blog. Reach out to us via email - projects@mapmyops.com or book a paid consultation (video meeting) from the link placed at the footer of this site's landing page.
Regards,