- February 21, 2018
- Posted by: OLC Learning
- Category: Blog
Warehouse Location Optimization with Microsoft Excel Solver Add In
Most organizations in retail distribution (Fast Moving Consumer Goods, Pharmaceuticals, Hardware etc) always battle with the question of where to centrally place their warehouses such that the distance is optimized for the furthest customer and the most nearest. Other questions of concern is the level of automation in handling commodities, storage requirements, the level of consumption vis a vis production speed at the factory/or lead time at the main warehouse.
When zeroing on which warehouse to use, choosing the one with the best physical location is important. The first question to ask yourself is, which region are you looking to serve? Ensuring your product is stored in a region near your customers is important for prompt deliveries. This also factors into considering cost. Calculating landed transportation costs to facility from manufacturing, and expected 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.
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.
Download the Location Generator with VBA here Evolutionary Optimization Web Service and Results
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 and is used in calculating latitude and longitude distances for cities.
d = acos(sin θ1 * sin θ2 + cos θ1 * cos θ2 * cos Δλ ) * R
Geo-coding and distance computation are done behind the scenes by a free web service from Google. Geocoding is the process of converting addresses (like a street address) into geographic coordinates (like latitude and longitude), which you can use to place markers on a map, or position the map. Reverse geocoding is the process of converting geographic coordinates into a human-readable address. Read more on Geocoding here https://developers.google.com/maps/documentation/geocoding/start
Oh forget that – I have a attached an excel file to help you Geo-Code the distances automatically from Excel (Just download the file, enter the towns you want and click on the button below it)
Setting up Solver
In this example, We are going to assume the current Warehouse is based in Migori (with Geocodes -1.069, 34.471)
After you have entered all the towns you can generate the Geo Codes, then go to solver to set it up.
The decision variables are the latitude and longitude values of the warehouse location in columns H3 and I3 respectively.
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 current total distance for all the shipments is 11,689.10 KMS. The management would like to minimize this distance by determining the best location (Minimization Problem).
The objective function to minimize the total distance between the warehouse and the shipment addresses is set to 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:
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.
Now click on Options to view the Evolutionary Solver options. Check Assume Non-Negative and set the population size and mutation rate. Initial population size in this problem is 100 and mutation rate as 25%.
Once the solver finishes its operation, you can view the number of iterations of the Genetic Algorithm in the Population and Results report. The outcome of the solution is shown below in the Population report.
From the solution, the original value was 11,689.10 which solver has optimized by minimizing the Minimum Total Distance to 5,956.28
The Geo Code (Location) has also been optimized to 0.064, 34.732 (Try searching for this location in your Google Maps)
Based on those 16 customer shipping points in this example, the solver by evolutionary algorithm has found the coordinates of (0.064, 34.732 ) as optimal.
Post has been localized to towns in Kenya.
Want to learn more? Talk to David K. Kandie on Twitter @ExcelMaestro or email info[at]opencastlabs.co.ke