Supply Chain Network Design Modelling using Excel and GIS
- Arpit Shah
- Jun 30, 2022
- 16 min read
Updated: Apr 15
INTRODUCTION
While Mapping is the underlying theme in majority of my posts here on this website, some have Operations play a dominant role too, such as - 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 Indian Railways was well-received by readers - a few of them sent complimentary messages and the editor of Rail Analysis magazine reached out with a request to publish it in their upcoming issue.
I am very excited to bring this comprehensive, practical guide on Supply Chain Network Design Modelling using Spreadsheet Solver and Site Suitability Modelling using Geographic Information System to you - the former utilizes business analytics while the latter employs location analytics.
In many ways, this post best summarizes what I offer via Intelloc Mapping Services - the business entity operating Mapmyops.com - which is to provide Mapping services that can be integrated with Operations Planning, Design and Audit workflows.
HYPERLINKED SECTIONS
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-
Supply Chain is the system in place involving nodes and linkages such as organizations, activities, people, information and resources that come together to accomplish the fundamental customer objective, which is to deliver the Right Product in the Right Quantity at the Right Time to the Right Place.
Supply Chain Network Design or simply, Supply Chain Design is the discipline of determining the optimal quantity, location and capacity of facilities in the Supply Chain as well as the material flows through it i.e. supply and demand allocation.
Supply Chain Network Design decisions are not made in isolation, rather, they take into consideration the business objectives, customer needs, internal value stream and external stakeholder implications. These aspects do not necessarily go hand-in-hand - for example, profitability objectives ordinarily entail lowering the costs whereas the need to service customers better involves making investments. These kind of trade-offs play a vital role in Supply Chain Design decisions. Some of the constituents of Cost and Service that are impacted from trade-offs in a supply chain context are depicted below-
An organization should endeavour to optimize the trade-offs i.e. strike a balance between contrasting objectives. This is the essence of modelling/designing a Supply Chain Network - it entails the use of dynamic mathematical frameworks to quantitatively describe, simulate, analyze and interpret supply chain decisions and scenarios. The next section demonstrates these aspects in detail.
That being said, businesses typically choose to opt for a network design strategy that mimics what is commonly practiced in that industry - for example, cement manufacturing organizations typically focus on economies of scale just as fast food retail chains opt for wider market presence. That being said, supply chain network configurations can defer vastly within the same industry as well, being influenced by internal production configurations - think of Dell's distribution model (Pull-based) vis-à-vis that of any other computer manufacturer (Push-based) during the turn of this century.
2. SPREADSHEET-BASED SUPPLY CHAIN NETWORK DESIGN MODELLING DEMONSTRATION
ABC Tyres Ltd., a multinational tyre manufacturer, intends to utilize Supply Chain Network Design techniques to develop a viable entry strategy for the Indian market. The organization has forecasted the annual demand for its tyres in the country to be at 100,000 units for the first three years of its operations. The state-wise breakdown of the customer demand is as follows-
Since there are location attributes in the dataset, the demand data can be plotted on a map-
2.1 Two-Echelon Supply Chain Modeling (Factory, Customers) - Optimizing for Lowest Transportation Distance
Given this anticipated customer demand, ABC Tyres Ltd. would like to ascertain which location would be ideal to set up a tyre manufacturing plant, purely on the parameter of lowest average distance traveled by its product to reach the customers (indicative of lowest cost of transportation as cost is directly proportional to the distance travelled). I've demonstrated this workflow and derived an optimal solution using a technique called Greenfield Analysis-
Familiarity with Microsoft Excel is necessary to understand all the video demonstrations in Section 2
The model determined Chahali in the Madhya Pradesh (Central India) as the ideal location for setting up a factory based on lowest outbound transportation distance parameter - intuitively, this appears right given the centrality of the suggested location.
2.2 Two-Echelon Supply Chain Modelling (Distribution Centers, Customers) - Optimizing for Lowest Transportation Distance
ABC Tyres next introspects on an alternative Network Design strategy. Instead of setting up a Factory in India, it wishes to examine suitable sites for setting up two Distribution Centers (DCs) instead which would serve to store and dispatch imported tyres to the customers. In the video below, I'll solve for the revised objective using Greenfield analysis once again.
Please note that while the terms Warehouse and Distribution Center are used interchangeably, there is an important distinction between the two - a Warehouse is just a temporary storage location whereas a Distribution Center ships the products directly to the customer from the storage location besides performing other value-added activities.
(watching the previous video is recommended)
Figure 7 depicts the second model's output on a map. The two DCs can be optimally sited within Bihar (East) and Karnataka (South) states of India. The pink-shaded states will be serviced by the DC in Patna, Bihar while the green-shaded states will be serviced by the DC in Koppal, Karnataka. By setting up the DCs at these locations, the customer demand will be fulfilled by having the lowest total outbound Transportation distance travelled by the tyre shipments.
As you would have seen in the demonstration videos, the Solver tool in Microsoft Excel iterates the Decision Variables and identifies the best-fitting configuration that meets the set Objective function. Often, there can be more than one optimal solutions for the Decision Variables as the supplied Constraints are not rigid enough.
Figure 8 below depicts another optimal solution for Model 2 (derived by running the Solver engine again) - the two DCs can also be sited within Madhya Pradesh (Central) and Meghalaya (Northeast) states respectively - the former would cater to all of India barring the Eastern and North-Eastern states, which will be serviced by the latter.
ABC Tyres can choose to add new constraints to refine the model output or contemplate the pros and cons of the two proposed DC configurations in order to hone in on the best option. It can even opt to abandon the idea and move ahead with the decision to set up a factory based on Model 1's recommendation. In real life, Siting decisions such as these cannot be based on the output of mathematical models alone. Feasibility checks and qualitative assessments are often required, such as whether a suitable property is available to lease at the proposed locations, whether there is alternative connectivity in case of primary road blockage, whether the local political environment is favorable for doing business, whether labor is available, how the location would develop over the next five years, and so on.
2.3 Two-Echelon Supply Chain Modelling (Distribution Centers, Customers) - Optimizing Distribution Center Capacity & Cost
So far the criteria for shortlisting the right location, be it for a factory or for distribution centers, has been based on lowest total outbound Transportation distance (the Objective function). I haven't incorporated Material Storage costs into the model - an important consideration in Supply Chain Design. This is exactly what I'll set out to demonstrate next-
(Watching the previous videos is recommended)
As was demonstrated in Video 3, initially I had extended Model 2 by adding the Transportation Cost data to individual Outbound Transportation lanes and subsequently sought an optimal solution for the Lowest Total Outbound Transportation cost (not lowest total outbound transportation distance as was done in the previous models). This is because I am transitioning to optimizing the Total Supply Chain Cost henceforth and all the Decision Variables would thus need to be integrated into the model in Cost-terms (having Distance-based parameters will no longer be of use during optimization).
As Transportation Cost is, in most cases, directly proportional to the Transportation Distance, the new optimal output (Figure 9) is not very different to that derived by Model 2 (Figure 8). Upon comparison, you'll observe that the proposed locations for the two DCs are exactly the same, and even their respective customer-flows are largely the same - with the exception of the state of Odisha, which will now be serviced by the DC in Madhya Pradesh instead of the DC in Meghalaya.
Upon enquiring about property availability at the four optimal DC locations suggested by the model thus far, ABC Tyres figured that they come in three capacity configurations i.e. the maximum quantity of tyres a DC can store at any given point in time. The annual Operating Costs (total of rent, manpower etc.) vary from site to site as well as from a particular capacity configuration to another.
Therefore, I have proceeded to add a new decision variable to the model - the Distribution Center Capacity Configurations with their respective Annual Operating Costs. Subsequently, I have optimized for the lowest Total Supply Chain Cost i.e. minimum of Total Outbound Transportation Cost + Total DC Operating Cost.
As anticipated, the model responded to these inputs and determined the optimal Capacity Configuration of the DC as well as the material flows to customers through it to generate the lowest cost Supply Chain Network Design - it suggested that the DCs be setup in the states of Bihar (East) and Karnataka (South) respectively, solved for the optimum Capacity that should be chosen for both the sites, and optimized the total Outbound Transportation Cost. All in all, it optimized for the lowest Total Supply Chain Cost. I have depicted the new Supply Chain Network in Figure 10 below-
The Map output is just a visual point of reference for the shortlisted locations and the customer allocation from it. The Spreadsheet-based optimization churns out Material Flows, Capacity Configuration to be selected, and minimizes the Total Supply Chain Cost - these aren't depicted on the map and you will have to refer to the video demonstration to get familiar with the workings of these aspects.
2.4 Three-Echelon Supply Chain Modelling (Factories, Distribution Centers, Customers) - Optimizing Product Flows, Factory & Distribution Center Capacity, Service Component & Total Cost
Hope you are enjoying the demonstration thus far! It is about to get a lot more interesting...
So far I had run optimization workflows on two-echelon networks which implies that the Supply Chain Network contained two categories of nodes - as you'd recall, I had run models utilizing Factory & Customers and DCs & Customers previously.
Now, I'll progress to modelling a three-echelon Supply Chain Network involving Factories, Distribution Centers & Customers - ABC Tyres wishes to evaluate the prospect of having a full-fledged Supply Chain Network in India with two factories and two distribution centers so that the customers are serviced in quick-time. Speaking of service, I'll incorporate that into the model as well.
Wait, isn't Service a qualitative parameter?
It is, and qualitative parameters by default cannot be incorporated in an optimization model. However, on certain occasions, these parameters can be converted to quantitative terms, thereby making it suitable to be integrated in the model. For example, the e-commerce giant Amazon offers guaranteed next-day delivery on selected products to customers who avail its Prime membership. The subscription offers priority serviceability to the customers, a trade-off on overall Supply Chain costs because they are bound to increase for Amazon due to this initiative. In terms of the repercussions on the Supply Chain Network, Amazon would have to make it highly-responsive and flexible - a dense, pan-India network of DCs and Pickup touch-points, dedicated Transportation fleet, and robust Last-mile delivery infrastructure and requisite manpower resources. If you were to think of it, one can model in these as parameters and constraints in order to have the model determine the optimal Supply Chain Design that would be effective at serving Prime customers on priority at the lowest overall Supply Chain Cost.
Another example I can think of is the restaurant chain Domino's popular 30-minutes or free Pizza delivery policy. In order to live up to its commitment, Domino's would need to model-in several geographically-dispersed retail outlets in a service area in addition to having a highly-efficient Pizza production process. Not many know that Domino's reserves just 8 minutes out of the available 30 towards the actual pizza delivery. This aspect can be quantified as a parameter in the Network Design Model - the within 8 minutes delivery timeline can be modelled in Geographic Coverage terms i.e. the location of the customers should not be farther than 2 kilometers away from the retail outlet.
I have incorporated the Service component for ABC Tyres on similar lines in the latter half of the three-echelon Supply Chain Network Design demonstration below-
(Watching the previous videos is recommended)
In Video 4, I had initially optimized just the Total Transportation Cost in a three-echelon network which involved shortlisting the optimal location for the two Factories as well as their respective Production Capacity, two DCs from the four available options and their respective Capacity configuration, minimizing the the inbound and outbound Product Flows and the total Transportation Cost from Factory to DCs (inbound logistics) and from DCs to Customers (outbound logistics), and most importantly, the Total Supply Chain Cost.
The optimized Supply Chain Network is depicted in Figure 11 below - the model suggested that the Factories be located in Meerut, Uttar Pradesh (North) and Bhuj, Gujarat (West) with the former supplying solely to the DC in Bihar while the latter supplying solely to the DC in Karnataka. As you'll observe upon comparison this with the previous model output in Figure 10, while the DC locations remain the same, the Customer allocations from it have a couple of changes, the states of Andhra Pradesh and Madhya Pradesh respectively.
Subsequently, I modeled in the Service Component with the objective to have the Weighted-Average distance travelled by a unit of tyre to be not more than 750 kilometers (currently stands at 900 kilometers). By doing so, ABC Tyres reckons that it will be able to offer a within three-day delivery guarantee to its customers across the country - a commitment towards enhanced Service.
In order to make the model throw up an optimal solution, I had to relax/do away with the select 2 DCs out of 4 parameter - this is because the model had already optimized the flow to 900 kilometers weighted average distance travelled per product (a function of Transportation Cost) previously, it wouldn't be able to go any lower any further, everything else remaining unchanged. By adding a new DC i.e. asking the model to select 3 DCs out of 4, the model will obtain valuable breathing space and be better positioned to reduce the Weighted Average Transportation Distance by the targeted 150 kilometers. Do refer Video 4 to see whether increasing the Service level had a positive or a negative impact on the total Supply Chain Cost as well.
The Supply Chain Network Configuration determined by Model 4 is depicted in Figure 12 below-
The model has determined that the DCs be located in Bihar (East), Madhya Pradesh (Central) and Karnataka (South) respectively i.e. the Meghalaya (Northeast) option has has been omitted.
Did you notice something peculiar in this output?
Each of Bihar, Madhya Pradesh and Karnataka are servicing customers in far-flung states - for example, Bihar is servicing Punjab and Rajasthan while Karnataka and Madhya Pradesh are not even servicing the customers within their own state!
And no, this isn't odd and neither has the model gone wonky - local optima always cedes ground in favour of achieving global optimum (the pan-India distance target of 750 kilometers in our case) - this is the essence of Optimization as well as of Supply Chain Management in general - a holistic, target-driven approach where the nodes have to work as a team to meet the business objectives rather than as individual entities optimizing their just own performance.
Pretty cool, isn't it?
Not really. Our planet is warming at a rapid pace, and if individuals, corporations and governments do not take significant steps to reduce the rate of Emissions, it would spell catastrophe for the generations to come. The utility about modelling Supply Chain Networks is that one can factor in the Green component as well - after all, the rate of emissions is a function of the Transportation Distance (inbound + outbound). This is exactly what I will demonstrate in the next section.
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 from the previous one. Using a fictitious example involving the Supply Chain of Kissan, an established food brand in India, I’ll demonstrate how to design an optimal three-echelon Supply Chain Network involving Agri-farms (Suppliers) → Kissan (Manufacturer) → Distributors (Customers) with the objective to achieve lower GHG (CO2) emissions from inbound and outbound Transportation.
Reducing Greenhouse gas (GHG) emissions would help make progress towards United Nations’ Sustainable Development Goal #13 on Climate Change which calls for urgent action to prevent the Earth from warming in excess of 1.5 degrees Celsius above pre-industrial levels (breached 1.1 degrees already).
Video 5 below demonstrates-
Optimizing Material Flows to arrive at the lowest Total Supply Chain Cost
Modeling in a 5% reduction in total GHG emissions (inbound + outbound CO2)
Explaining how the model adapts to the new constraint and re-optimizes Material Flows
Slider 1: Comparing the Optimized and GHG emissions-optimized Supply Chain Network of Kissan
With this, I am concluding this comprehensive portion on Spreadsheet-based Supply Chain Network Design Modelling. I hope you drew insights on how business strategy influences network design decisions and how a mathematical model responds to practical parameters and constraints. Hope that you appreciated the presence of Map-based visualizations - I felt it would complement the Spreadsheet-based output very well.
While I have solved the models using Microsoft Excel's inbuilt Solver tool, as the parameters became complex, Solver took more time to iterate. For example, I had to use the Open Solver plugin while running Model 4 in order to reduce the processing time. However, these demonstrations are just samples by themselves. In real life, Supply Chain Network Design Modelling entails factoring in hundreds of Decision Variables and Constraints! There are dedicated softwares built for complex mathematical programming such as Supply Chain Guru which are much better equipped to handle industrial-grade Network Design Modeling workflows.
3. GEOGRAPHIC INFORMATION SYSTEM (GIS)-BASED SITE SUITABILITY MODELLING DEMONSTRATION
Siting i.e. selecting a Suitable Site for setting up a facility, be it factory, warehouse, store or service center, is an important component within the overall Supply Chain Design workflow and is interlinked with the other two components - Capacity Allocation and Supply & Demand Allocation.
The steps undertaken to make Site Selection decisions in a Supply Chain Design context typically entail- formulating the Supply Chain Strategy → developing Supply Chain Design configurations → testing the configurations using Mathematical modelling → blending the Quantitative output with Qualitative inputs received from affected stakeholders → shortlisting Suitable Sites.
The approach to Site Selection is usually top-down in nature, beginning with macro-level analysis and subsequently making micro-level evaluations prior to finalizing the site location.
While I've used the Map-based depictions purely for visualization purposes in this post thus far - with a Geographic Information System (GIS) platform, one can perform powerful geospatial analytics to determine the suitable locations for setting up new sites based on an organization's business objectives, preferences and constraints. In this section, I'll demonstrate just so and utilizing a powerful technique called Suitability Modelling using Esri's ArcGIS Pro GIS software - this technique can be utilized on a standalone basis or as an extension to the Spreadsheet-based Supply Chain Network Design Modelling which I had covered in the previous sections.
USpace Realty - a fictitious Real Estate company operates Coworking spaces in urban centers of India. As part of its expansion plan, it wants to shortlist five suitable locations in Mumbai city (West India) to develop its new coworking centres. The list below contains the Siting parameters that USpace intends the modeller to incorporate into the site suitability study-
In the demonstration video below, besides incorporating some of these parameters, I've tried to lay emphasis on the methodology of problem-solving rather than focusing purely on the prowess of geospatial technology - I have covered aspects such as aligning siting preferences with business objectives, geodata sourcing mechanisms, the rationale behind using some of these parameters, and the need to explore alternatives.
The Site Suitability Modelling has been executed in three phases-
creating Distance-based rasters from the shortlisted parameter layers which were originally in vector format. This would help the model perform the Suitability Analysis over the study area (Mumbai) in its entirety
Figure 14: Phase 1 - creating Distance-based rasters of the shortlisted parameters and having the Suitability Modeler categorize the study area assigning weights and ranking some of the other parameter layers which remain in vector format
Figure 15: Phase 2: Assigning weights and subsequently, ranking those Vector data layers which cannot be converted into Rasters for Suitability Modelling merging the output of both 1. and 2. to determine the most favorable sites/locations within Mumbai to setup the new Coworking centres
Excited to explore the video demonstration to see the technology workings and the methodology involved? Here it is-
Hope you enjoyed seeing this demonstration. In case you'd like to, you may share all the demonstrations covered in this post with your friends and colleagues - here is a compilation (excludes Video 5 on integrating Green component in Supply Chain Network Design which was created later)-
ABOUT US
Intelloc Mapping Services, Kolkata | Mapmyops.com offers Mapping services that can be integrated with Operations Planning, Design and Audit workflows. These include but are not limited to Drone Services, Subsurface Mapping Services, Location Analytics & App Development, Supply Chain Services, Remote Sensing Services and Wastewater Treatment. The services can be rendered pan-India and will aid your organization to meet its stated objectives pertaining to Operational Excellence, Sustainability and Growth.
Broadly, the firm's area of expertise can be split into two categories - Geographic Mapping and Operations Mapping. The Infographic below highlights our capabilities-
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 brochure. 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,
