Logistics Network Optimizing Warehouse Location

There are many factors to consider when building a new warehouse facility. Making the right decisions with regards to both warehousing and distribution methods could make all the difference for your firm. This can range from storage requirements, the level of automation in material handling, and workforce availability.  An important aspect among all is the Physical Location.

Physical Location of Warehouse

Finding a warehouse with desirable physical location is important. The first question to ask- which region are you looking to serve? Ensuring your products are stored in a region close to your customers are important for prompt deliveries. Calculating land transportation costs to facility from manufacturing, and transportation costs from facility to end customer, will help decide where you can afford to keep your product.

The location’s proximity to carrier facilities should also be taken into consideration. Look for an all-encompassing solution that offers both warehousing and transportation to get the most value out of your investment, or ensure your storage facility is as close to your carrier as possible.The general objective of transport optimization is minimizing transport cost subjected to the customer service policy. Normally, the transport solution affects inventory carrying and warehousing cost considerably. Few factors to be considered are:

1. Geographic distribution network
2. Proximity of major logistics carrier hub such as UPS
3. Availability of talent pool in  the area
4. State and Local Tax incentives
5. Construction codes and zoning restrictions
6. Easy access to major interstate highways and seaport locations

Consider a simple scenario – A company stores all of its products in a single warehouse. All of its customers are located in the eastern sea board of the US. The management wishes to determine the best location for their warehouse in order to minimize total transportation costs. The number of shipments made to each of its customers for a period is given. The main objective is to determine the warehouse location based on its latitude and longitude coordinates.

Find Optimal Warehouse Location in Excel – Model Formulation

The input for this problem is the location of each address identified by geographical coordinates. The user is asked to fill in the shipping addresses of the customer and the number of shipments in a given period, and the spreadsheet works behind the scenes to determine the optimal lat-long coordinates of the warehouse to minimize transportation costs.

Once you have filled out the shipping point addresses, simply click on “Geocode” command button. Lat, Long, and Distance are computed using Google Geo web service.

Google is nice enough to geo-code for free but they won’t put up with thousands of requests per hour from the same IP. After about 10 seconds, they will stop sending coordinates for a brief period. You may occasionally get “Not Found” message. You can wait for few seconds and try. The underlying code has an intentional delay of 1 second for every address that needs to be geo-coded. If you persistently get the “Not Found” message, check to see the address is filled completely.

The distance formula is based on spherical law of cosines and bears more accuracy than the straightforward orthogonal distance. R is 69 and is based on earth’s curvature. It is used in calculating distance between shipping cities and the warehouse.

Setting up Solver

The decision variables are the latitude and longitude values of the warehouse location in columns H3 and I3. Latitude and Longitude coordinates are the only constraint to this problem and they must be between 0 and 120 degrees. We only need to add the constraint that they be less than or equal to 120 and they must be non-negative.

The objective function to minimize the total distance between the warehouse and the shipment addresses is set in cell J12. It is the sum of the array product between the column of distances and the column of shipments made to each shipping addresses from the input table. The column range for shipments is “WhouseShipments” and distance as “WHouseDistance”. Thus, the formula for the objective function becomes:

=SUMPRODUCT(WhouseShipments,WHouseDistance)

This is the non-optimized load distance.We need to minimize the load distance, so that loads travel short distances. The goal is to find one acceptable warehouse location that minimizes the score, where the location is defined by by lat and long.

The decision variables, cells H3 and I3 are to be determined by the solver. Cells H3 and I3 must be non-negative. Choose “Evolutionary” as the solving method since the function is non-smooth. Now click on Options to view the Evolutionary Solver options. Check Assume Non-Negative and set the population size and mutation rate. The spreadsheet will flicker for few seconds as the evolutionary algorithm finds the shortest distance.

Solver found a global minimum and the load distance score is 54,448 miles if geographical coordinates of warehouse is (41.34, -77.27). Solver was able to minimize the load distance by a whopping 47,726 miles or 46% !

Practical considerations rarely allow managers to select exact location with the lowest possible score. For example,land might not be available there at a reasonable price, or other location factors may make the site undesirable. But it gives some guidelines on the geographical coordinates.

Once the solver has finished computing, you can view the number of iterations of in Population and Results report.

Based on those 24 customer shipping points, the solver found optimal warehouse location at coordinates (41.74, 76.25).

**Update: Google Maps API is now part of Google Cloud. Google made recent account policy changes and Maps API is not a free service anymore. You must use an API key to authenticate each request to Google Maps Platform APIs. For additional information, refer to http://g.co/dev/maps-no-account

[sociallocker]Download Logistics Network Optimizing Your Warehouse Location Model[/sociallocker]