Map, Design & Optimize Supply Chain Network using Spreadsheet & GIS

This article was originally posted on my firm's website:?https://www.mapmyops.com/map-design-and-optimize-supply-chain-network-using-spreadsheet-and-gis?and is updated there when an edit needs to be made. Hence, I'd recommend you to read this article from the hyperlink above.

________________________________________________________________

The topics which I write about on this Mapmyops Geo-blog usually have a blend of two components - 'Mapping' & 'Operations'. Around two-thirds of each article is devoted to a particular Mapping workflow while the remaining one-third elaborates how it would aid in Operations Improvement. The objective is to share practical, educative content and connect it to my firm's line-of-work which is:

Intelloc Mapping Services | Mapmyops is engaged in selling solutions which capture geodata (Drones & Survey Services), process geodata (Imagery & Location Analytics), and package geodata (Map-based Applications). Together, these help organizations to benefit from Geo-Intelligence which aids in operations planning & improvement.

Only a handful of my articles have the Operational aspects play a dominant role, 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 as part of Supply Chain Design process.

__________________________________________________________________________

Site Selection, as such, is a broad topic to write about. I will focus on the 'Supply Chain Design' aspects of it. 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 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 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.

Figure 1: What does Supply Chain Network Design entail?

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 entail keeping the costs low whereas servicing customers better 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:

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 (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 Supply Chain Design configurations. Thereafter, the configurations are tested using spreadsheet / 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 top-down in nature, beginning with macro-level evaluation and progressing towards micro-level adjustments.

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 -

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

Visually, it can be represented as below -

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

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 first four videos. Also, all video walk-throughs in this article have been prepared, by default, for moderate-fast readers. Video Speed Settings can be adjusted to accommodate personal preferences)

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

Figure 6: Site Selected in Model I

The model determined Chahali in North-east Madhya Pradesh (blue marker in the map above) as the suitable site based on the Weighted-average customer demand. Fairly understandable, given the centrality of its 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)

Let's expand on our previous Greenfield Analysis model.

(Watching the last video is recommended prior to watching the one below)

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.

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, Optimization tools yield more than one optimal results. 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 & Far-Eastern states.

ABC Tyres' leadership now have to decide which design configuration to choose & adopt.

Figure 8: Another Output from Model II

__________________________________________________________________________

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 predetermined 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 prior to watching the one below)

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.

Figure 9: Lowest Transport Cost Output from Model III

However, when we added a new variable - that of DC Capacity configurations, its annual operating costs - and optimized for Lowest Total cost thereafter, we were in for a surprise! (refer to the next map depiction below)

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 had transportation costs in their favor only. Goes on to show how the 'optimal solution' can be heavily influenced by addition / modification of new constraints & parameters.

Figure 10: LTC + Capacity Optimization Output from Model III

__________________________________________________________________________

Hope you are liking it, thus far. It is about to get a whole lot interesting!

In terms of supply chain nomenclature, the 3 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 Service Component 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 prior to watching the one below)

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 customer allocation changes.

Figure 11: LTC, Capacity, Demand + Supply Optimization Output in Model IV

Next, we 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.

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. Solver threw a 3-DC optimal solution with the following configuration as depicted on the map view 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.

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 & 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 models. 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 shorten processing times. However, these are tiny models in reality. Industrial-scale Modeling could entail factoring in thousands of constraints! There are dedicated softwares such as Supply Chain Guru which are much better equipped to handle industrial-grade Network Design 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 basis our constraints, preferences and objectives.

Allow me to establish the Walk-through'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 Mumbai, Maharashtra where it could setup new co-working spaces.

Its Leadership would like to consider the parameters listed below - their Site Selection preferences:

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, Geo-data sourcing mechanisms, Explanation of rationale behind model choices and Alternative options available etc.

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

For those who haven't seen the video, sharing a short summary - the Modeling was done in two phases - a) first by creating Distance-based Rasters from certain Vector datasets to Shortlist favorable Sites via Suitability Modeler tool and b) Scoring & Assigning Weights to other Vector datasets to shortlist suitable Locations. Towards the end, both a) and b) outputs were merged to determine the most favorable spots & regions within Mumbai for setting up the new Co-working spaces.

Figure 14: Representative Image of a) Phase 1: Suitability Modeling

Figure 15: Representative Image of b) Phase 2: Scoring & Assigning Weights

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 even share the combined article's video below with your friends & colleagues -

Video #6: All five videos combined into a single sequential video

__________________________________________________________________________

Do write to me on [email protected] with your feedback, queries and/or mapping solutions requirement.

Regards, Arpit Shah