Useful ideas for maximizing the power of Microsoft Excel for supply chain analysis, Part I
This is the first post in a three-part series that describes how to perform desktop network optimization, job scheduling and vehicle routing with nothing but Microsoft Excel.
Introduction
Performing a network analysis is one of the bread and butter tasks for a supply chain consultant. Typically, it takes specialized software, weeks of working with the data going into to the software, and another couple of weeks working with the data that comes out of the software.
The most valuable pieces of information this analysis yields are warehouse locations and approximate transportation spend. However, it can also be extended to understand the classic cost – service tradeoff that results from the cost of placing and operating more warehouses closer to customers and the service benefits they receive because of faster response times.
Given that warehousing and transportation typically accounts for 60 percent to 90 percent of logistics cost, maintaining a solid understanding of the relationship between location and transportation expense is always a valuable exercise.
The problem is that there is seldom the time or the budget to retain a consultancy to do even a small network analysis. Instead, there may only be two weeks, between other duties, for an in-house supply chain analyst to develop a directionally correct, 80 percent solution that could be understood, vetted, and evaluated by a larger audience. This is really like asking if there is a way to do this in Microsoft Excel.
Getting Started
As of Microsoft Office 2010, the answer is yes, this can be done in Microsoft Excel, because of the inclusion of a new algorithm in the Solver: the Evolutionary Solver. Without going into too much detail, this algorithm is excellent for finding solutions to non-smooth, non-continuous, optimization problems. For those with experience using the Excel Solver, you know setting up a problem is as much science as craft. This is no different. The data that is required to perform this analysis is:
- Ship from –An address for an existing warehouse location
- Ship to – An address for a customer location
- Transit time – Check-out to check-in
- Weight – The weight of the order in lbs.
- Distance – The distance
- Cost – Shipping cost
- Mode –Truckload (TL) versus Less-than Truckload (LTL)
Analyzing the raw data is a painstaking process, but it will facilitate an understanding of the current operational baseline. This current state analysis should provide information about:
- Total transportation cost, total transportation weight, total transportation mileage, total TL mileage, total LTL mileage and intermodal, if it is in the data
- Average shipment cost, average shipment mileage, average shipment time, TL average for all three, LTL average for all three, and intermodal, if it is in the data
- Average cost per mile for TL, average cost per mile for LTL, average distance traveled per day
Establishing the operational baseline will result in an understanding of the relationships between delivery distance, time and cost for the current operation. Further, these same relationships will be leveraged to develop a future state operation with re-located warehouses. One of the first things that must be done is the conversion of all addresses to geocodes. Geocodes are the Latitude and Longitude (lat/lon) of a point on the earth and there is an equation that provides the distance between two points on the surface of the earth. Three relatively low-cost ways exist to convert an address to lat/lon:
- Purchase Microsoft MapPoint and write a Visual Basic for Applications (VBA) program that leverages the MapPoint object model and enables a “lookup” from Excel.
- Purchase a subscription to Google Maps ($5 per month) and upload the addresses directly and get the lat/lon.
- Download the latest Census Bureau data. This data contains lat/lon for U.S. zip codes and these can be used as an estimate for the address.
With the customer and warehouse locations converted to geocodes, the shipping distance must now be calculated and corrected because the distance we use is surface distance and this will always be shorter than road distance. By comparing actual to calculated distance, we can develop a multiplier to make a road distance correction. Typically, the multiplier is between 1.15 and 1.35. The steps to follow include:
- Convert the warehouse addresses to geocodes.
- Convert delivery destinations to geocodes.
- Calculate the delivery distance using the geocodes.
- Correct the distance using a multiplier.
Setting up the Spreadsheet
To calculate the distance between two points on the surface of the Earth, one can use the spherical law of cosines or the Haversine formula. I prefer the spherical law of cosines for its simplicity in Excel. It looks like:
The information is consolidated in the spreadsheet with:
- Baseline Network Information
- Network Service Performance Aggregator
- Distance Calculation Table, Warehouse Selection Table, Network Cost Performance, Customer. Location and Freight, and Warehouse Locations.
All of this information is organized to perform the optimization. In Excel, this uses the Solver Optimization tool. Beginning with three warehouses located in Harrisburg, PA, Dallas, TX and Las Vegas, NV, the annual shipping cost is just over $24M with more than 13M miles shipped.
Reviewing the Baseline and Network Performance
The activity profile is detailed below. This model establishes the baseline for our optimization efforts since it represents the current state. Notice that with the warehouses in these locations, fewer than half (7,456 of the 15,341) of the shipments are two days or less from the customer.
Running the Optimization
The solver is set to relocate all three warehouses. Activating the solver finds three new warehouse locations that eliminate about 4.5M miles and more than $8M in cost. Warehouse 1 is close to Richmond, KS; Warehouse 2 is close to Pikeville, KY; Warehouse 3 is close to Pueblo, CO.
The activity profile shows the most improvement. Now, more than 60 percent of the shipments are within 1.5 days of the customer (9,300 of the 15,341).
In addition, the number of customers that have been moved into the 1.5 days or less delivery lead time window has increased from 752 to 1513. The number of shipments that can be delivered in 1.5 days or less grew from 4,195 to 9,300.
Changing the Objective
The versatility and power of the Evolutionary Solver is that it can be used to optimize “non-traditional” objectives. For example, if the objective was not minimizing cost but increasing the number of shipments that could be delivered in the 1.5-day delivery lead time window, different warehouse locations would be generated.
In order to set up the parameters to maximize 1.5 day delivery lead time, the locations change to: Warehouse 1 is close to Marshall, NC; Warehouse 2 is close to Matheson, CO; Warehouse 3 is close to Greentop, MO. The warehouses have moved 100 to 200 miles from their previous locations. This has added overall cost and distance, but it is less than 3 percent to the network.
However, the activity table reveals that a few more customers (14) and more than 600 additional shipments are within 1.5 days of delivery—and example of the classic tradeoff between service and cost.
Extending the Model
Thus far, three warehouses have been relocated to minimize transportation expense and maximize shipments delivered in 1.5 days or less. How much does an additional warehouse impact our transport cost and service performance? This is easily determined with a spreadsheet model that is properly set up. Beginning with Warehouse 1 close to Colorado Springs, CO; Warehouse 2 is close to Hugo, OK; Warehouse 3 is close to Boone, IA; and Warehouse 4 is close to South Williamson, KY.
Locating four warehouses in the network and minimizing cost reduces the transportation expense from the comparable three warehouse model by more than $2M.
In addition, the average delivery lead time is well below 1.5 days. The number of shipments that can be delivered in 1.5 days or less has increased by more than 2,200 and the number of customers served in 1.5 days or less has increased by 227.
The natural question at this point is, “can this be improved?” By setting the solver to maximize the number of shipments that can be delivered n 1.5 days or less, a different set of locations are generated. Warehouse 1 is close to Matheson, CO; Warehouse 2 is close to Lockesburg, AK; warehouse 3 is close to Washington, IA; warehouse 4 is close to Flag Pond, TN.
Locating four warehouses in the network and maximizing the number of shipments that can be delivered in 1.5 days or less reduces the transportation expense from the comparable three warehouse model by more than $2M, but adds $300K to the lowest cost four warehouse solution.
In addition, the average delivery lead time is well below 1.5 days. The number of shipments that can be delivered in 1.5 days or less has increased by 1,985 from the best three warehouse solution and the number of customers served in 1.5 days or less has increased by 217.
Unfortunately, it seems that it would be difficult to reduce the average delivery time from two days to one. However, by relocating the warehouses and adding one additional (bringing the total to five), the average delivery lead time can be reduced to less than 1.5 days while saving several million dollars.
Of course, adding warehouses to the network adds additional cost and the model must be augmented to address this cost increase.
This represents a relatively straightforward way to conduct a network analysis. The supply chain analyst should be comfortable providing fact-based answers to questions regarding the benefits of warehouse location and number. There are other things that need to be considered, such as the availability of warehouse space in the locations identified, the cost of operating a new warehouse and the cost of moving warehouse operations from one location to another. These are all very significant activities that impact the value proposition.
In my next article, I will show you how to develop a solution for job shop scheduling, another difficult optimization problem that can be solved in Excel using the appropriate framework. Until then, keep modeling.
Other published posts in this series:
- How to Perform DIY Production Scheduling to Make the Most of Your Production Assets (Part II)
- How to Conduct DIY Vehicle Routing to Minimize Distance and Transportation Expense (Part III)
Author’s Note: The skills required to understand the concepts are, admittedly, advanced but we feel that anyone with the willingness to learn can work through these samples and 1. Build their own skills and 2. Distinguish themselves by delivering value at their own company.
Granted, this is not completely altruistic. After all, we are consultants. Our hope is that you are sufficiently interested in these articles (and the ones to come) to ask questions and invite us to come to you and discuss ways we may build on what is here.