INTRODUCTION
While Mapping is the underlying theme in most of my posts here, 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' & 'Shortlisting Suitable Sites using Geospatial Business Analytics'. My recent post on Operations Management in 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 Excel and Site Suitability Modelling using GIS to you - the former is heavy on the operational aspects while the latter has been demonstrated using an advanced mapping software.
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 solutions 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 & Esri's Learn ArcGIS' resources
Let's begin with some definitions:
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 (4 R's), 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 entails 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.
Network Design decisions are not meant to be taken in isolation, rather they are made keeping business objectives, customer needs, internal value stream and external supply chain implications in mind. These aspects do not tend to go hand-in-hand - for example, business objectives ordinarily entail lowering the costs whereas the need to service customers better necessitates spending more. These kind of Trade-offs play a vital role in Network Design decisions. Some of the constituents of Cost and Service, that are impacted from trade-offs in a supply chain context, are depicted below-
Businesses typically choose to opt for a design strategy that is commonly used in that industry - for example, manufacturing organizations typically focus on economies of scale just as fast food chains opt for wider market presence. Sometimes network configurations can defer vastly from organization to organization within the same industry though, influenced by internal production configurations - think of Dell's distribution model (Pull-based) v/s that of any other computing hardware manufacturer (Push-based) during the turn of this century.
That being said, an organization can definitely 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. Let me begin demonstrating it to you-
2. SPREADSHEET-BASED SUPPLY CHAIN NETWORK DESIGN MODELLING DEMONSTRATION
ABC Tyres Ltd., a fictitious multinational tyre manufacturer, is evaluating its Supply Chain Network Design as part of its entry strategy for the Indian market. It has forecasted the annual demand for its tyres in the country to be at 100,000 units. The state-wise breakdown of the 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 (one can consider it as indicative of lowest cost of transportation as it is directly proportional to the distance travelled).
I've used a technique called Greenfield Analysis to derive an optimal solution-
Familiarity with Microsoft Excel is necessary to understand the videos in Section 2 of this post
The model determined Chahali in the Madhya Pradesh (state in central India) as the ideal location for setting up a factory based on lowest outbound transportation distance parameter. The output is understandable intuitively, given the centrality of the suggested site within the nation.
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 dispatch imported tyres to the customers. In the video below, I'll solve for the revised objective by performing Greenfield analysis again.
Please note, the terms Warehouse and Distribution Center are used interchangeably but there is an important distinction between the two - while a warehouse just temporarily stores the products, a Distribution Center also ships the products to the customer directly from the storage location besides performing other value-added activities.
(watching the previous video is recommended)
The map below (Figure 7) depicts the second model's output. The two DCs can be optimally sited in Bihar (eastern) and Karnataka (southern) 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 doing so, the customer demand will be addressed by having the lowest total outbound transportation distance travelled by the tyre shipments.
The Solver tool in Excel iterates the decision variables and identifies the best-fitting configuration that meets the set objective function. Often, there are more than one optimal solutions for the variables based on the supplied constraints.
The map depicted in Figure 8 below depicts another optimal solution derived by running Model 2 again - the two DCs can also be sited within Madhya Pradesh (central) & Meghalaya (eastern) states respectively - the former can cater to all of India barring the East & North-Eastern states, which will be serviced by the latter.
ABC Tyres can choose to add new constraints to add rigidity to the model as well as qualitatively 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 cannot be based on mathematical models alone. Feasibility checks are often required - for example, whether suitable property is available to lease at the proposed locations, whether there is alternate road connectivity in case of a blockage, whether the local political environment is favorable for doing business, whether labor is available, 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 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 'total outbound transportation distance' unlike the previous models. This is because I am transitioning to optimizing the 'total supply chain cost' henceforth - all the decision variables would thus need to be integrated in the model in cost-terms. Having distance-based parameters will no longer work.
Because transportation cost, in most cases, is directly proportional to the transportation distance, the new optimal output (Figure 9) was not very different to that derived by Model 2 (Figure 8). Upon comparison, you'll observe that the proposed location for the two DCs is exactly the same, and even their respective customer-flows are virtually the same - with the exception of the state of Odisha, which will now be serviced by the DC in Madhya Pradesh instead of Meghalaya.
Upon enquiring about property availability at the four optimal DC locations suggested by the model thus far, ABC Tyres figured that they are indeed available and come in three capacity configurations i.e. the maximum quantity of tyres a site can hold for storage at any given point in time. The annual operating costs (rental, handlers etc.) vary from site to site as well as from a particular capacity configuration to another capacity configuration.
Therefore, I have proceeded to add the new decision variable - the Distribution Center capacity configurations with their respective annual operating costs. Subsequently, I optimized for the lowest total supply chain cost (minimum of total outbound transportation cost + total DC operating cost for the selected configuration).
As anticipated, the model responded to the changes and altered the design of the supply chain network - it suggested that the DCs be setup in the states of Bihar & Karnataka respectively, solved for the optimum capacity from the three options at each of the two sites, and optimized the total outbound transportation cost. All in all, it optimized for the lowest supply chain cost - have depicted the new supply chain network in Figure 10 below-
[Please note that the map outputs serve as a visual point of reference for the shortlisted sites and the network view in general. The spreadsheet-based optimization churns out material flows, capacity considerations and lowers the total supply chain cost - these aren't depicted on the map, you'll have to refer to the videos to get familiar with those aspects.]
Hope you are enjoying the demonstration thus far! It is about to get a lot more interesting...
2.4 Three-Echelon Supply Chain Modelling (Factories, Distribution Centers, Customers) - Optimizing Product Flows, Factory & Distribution Center Capacity, Service Component & Total Cost
So far I had run optimization workflows on two-echelon networks. Two-echelon means the Supply Chain Network contains two categories of nodes - as you'd recall, I had run models involving either 'Factory & Customers' or '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 it into the model as well.
'Wait, isn't service a qualitative parameter?...'
It is, and it usually cannot be incorporated in a mathematical model - for example, how happy a customer would be to see a new red sofa in the product catalogue (Ikea could very well contest my claim😊). However, on certain occasions, qualitative 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 enhanced service levels to the customers, a trade-off on overall supply chain costs because they are bound to increase for Amazon. In terms of the repercussions on the network, Amazon would have to make its supply chain 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 manpower capabilities. If you were to think of it, one can model in these new parameters and constraints in order to have the model determine the optimal supply chain design that would be effective in serving Prime customers.
Another example I can think of is the pizza chain Domino's popular '30-minutes or free' policy. In order to live up to its commitment, Domino's would need to model-in several, geographically dispersed outlets within an urban area in addition to having a highly-efficient production process.
Not many would know that the time Domino's reserves for home delivery is just 8 minutes out of the available 30 minutes. This can be quantified as a parameter in the Network Design Model - the 'within 8 minutes' timeline can be modelled in as the desired geographic coverage of a restaurant 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 network design demonstration below-
(Watching the previous videos is recommended)
In this video demonstration, I had initially optimized just the total transportation cost in a three-echelon network involving selecting the ideal location of two factories as well as their respective production capacities, two DCs out of four options and their respective storage capacities, transportation costs from factory to DCs (inbound) and from DCs to customers (outbound), the inbound and outbound tyre flows, and most importantly, the total supply chain cost.
The optimized supply chain network is depicted on a map in Figure 11 below - the model suggests that the factories be located in Meerut (Uttar Pradesh, North India) and Bhuj (Gujarat, West India), with the former supplying solely to the DC in Bihar while the latter services just the DC in Karnataka. As you'll observe upon comparison with Figure 10, while the DCs remain the same, the customer flows have a couple of changes (Andhra Pradesh and Madhya Pradesh).
Subsequently, I've 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, it 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 for enhanced service levels.
In order to make the model throw up an optimal solution, I had to relax the 'select 2 DCs out of 4' parameter - this is because the model had already optimized the network to 900 kilometers weighted average distance travelled per product (a function of transportation cost), it wouldn't be able to go any lower, all else remaining constant. 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 average transportation distance by the targeted 150 kilometers. Do refer Video 4 to see whether increasing service levels had a positive or a negative impact on the total supply chain cost.
The network configuration determined by the model is depicted in Figure 12 below-
The model has chosen the DCs to be situated in Bihar, Madhya Pradesh and Karnataka respectively (Meghalaya 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 - eg. Bihar is servicing Punjab and Rajasthan while Karnataka and Madhya Pradesh are not even servicing 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 objective rather than as entities optimizing their individual 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 beauty about modelling supply chain networks is that one can factor in 'Green component' as well - after all, the rate of emissions is a function of the transportation distance (inbound + outbound). This is exactly what I am about to demonstrate in the upcoming video.
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 2.4)
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 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 help 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-Optimized Supply Chain Network of Kissan
With this, I am concluding this comprehensive section on Spreadsheet-based Supply Chain Network Design Modelling. I hope you drew insights on how Business Strategy influences Supply Chain Network Design decisions and how a mathematical model responds to practical parameters and constraints. I hope you enjoyed the map-based visualizations - it complements spreadsheet output effectively.
While I have solved the models using Microsoft Excel's inbuilt Solver tool, as the parameters became complex, Solver took more time to iterate.
I had to use the Open Solver plugin while executing 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. These 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 to locate any 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 models → 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 site finalization.
While I've used 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 - will be utilizing a powerful technique called Suitability Modelling using Esri's ArcGIS Pro GIS platform - the technique can be utilized on a standalone basis or as an extension to 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 where it could develop new coworking centres.
This list contains the siting parameters that the company intends the modeler to incorporate into the 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 just on the utility of geospatial technology - I have covered aspects such as aligning siting needs with business objectives, geo-dataset sourcing mechanisms, rationale behind using some of the parameters, and the need to explore alternatives.
Site Selection modelling has been done in three phases-
a) by creating distance-based rasters from some parameter layers that are in vector format, in order to have the model perform the suitability analysis over the entire study area (Mumbai),
b) by assigning scores and weights to the other vector parameter layers and ranking compatible sites,
c) and merging the output of both a) and b) to determine the most favorable site locations within Mumbai to set up the new coworking centres
Excited to explore the video demonstration? Here it is-
Hope you enjoyed it!
In case you'd like to, you may share all the demonstrations covered in this post with your friends & colleagues - here is a compilation video (excludes Video 5 on Green component which was created later)-
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 Services, Subsurface Mapping Services, Location Analytics & App Development, Supply Chain Services, Remote Sensing Services & Wastewater Treatment. 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.
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,