Warehouse Optimization

**Matt Lee** · 12-29-2010, 11:38 AM

I have a quick question!

I have a few warehouses throughout the USA that I distribute product to a number of customers at specific destinations. I was wondering if there is an easy way to use excel to optimize my warehouse locations depending on customer locations?

**Palmetto** · 12-29-2010, 12:23 PM

You don't provide enough details for an answer.
If you mean find the closest warehouse relative a specific customer location, the perhaps a look up table will work.

Such a table might be structured as:
column1 =region (or area, state, etc.)
column2 = warehouse name

You would need to correlate the customer to a region. Then, using a look up of the customer name (or ID, etc.) return the customer's region and use this result an an input for another look up to return the warehouse location.

If you want specific help, then provide specific details.

**Matt Lee** · 12-29-2010, 12:30 PM

I appreciate your response!

For example I have 5 warehouses with maybe 100 customer locations throughout the united states all with different distances between them to each of these warehouses. I am looking for a way or method to determine where the most strategic locations to place my warehouses should be in regards to distances from customer locations, to optimize the travel distances from my warehouse to customer point.

**Palmetto** · 12-29-2010, 12:33 PM

Again, you provide no details. At least, upload a sample workbook with the locations of the customer and warehouses.

**shg** · 12-29-2010, 12:50 PM

Homework?

I think this is a hugely complex problem. The simplest part, placing warehouses to minimize Euclidean distances to customers, requires a non-trivial clustering algorithm.

Then factor in actual driving distances, road quality, customer volumes, zoning, land prices, construction costs, and taxes ....

Here's an article to get you started: http://ieeexplore.ieee.org/Xplore/lo...hDecision=-203

**Matt Lee** · 12-29-2010, 01:04 PM

shg, Thank you for your response. I wish it was homework, more a mini project that will greatly effect my business savings. I am lucky in the sense I would contract a warehouse so zoning, land prices, construction costs, and taxes are variables I wont have to mess with, however you hit the nail on the head as far as what I am trying to do. Any suggestions?

I looked at the link you sent me, I dont have access to to IEEE.

**optkontek** · 12-29-2010, 03:33 PM

I think you can do well without taking everything into account at once... To get started, if I were you, I would define say 5 regions for your warehouses, with approximately equal areas. Then, within each area I would minimize the average distance to the customers (euclidian norm as mentioned above). That's a start - then you may start to think about adding details later, such as road standards, taxes and so on...

It is always better ot start with the simple assumptions and arrive at a useful solution than to make everything super-realistic but close to impossible to solve. The latter is the academic approach (as I learned from doing PhD research....)

**Matt Lee** · 12-29-2010, 05:00 PM

That is a great point, I guess my problem is implenting the best method in which to produce such data. Any suggestions?

**optkontek** · 12-29-2010, 05:14 PM

Well, I suppose you have the addresses of your customers? :-) Say you have defined a "midwest" region and your customers are scattered around, but concentrations exist (say Twin Cities, Chicago, Wichita... add some yourself... ). Then assign customers to those cities depending on which city they are closest to (this introduced a simplifying assumption - it is wrong, you know it is wrong, but it simplifies life and allows you to proceed with a first solution). Then, you can consider where to put your warehouse - minimizing the average "weighted" distance to those selected cities would be a meaningful approach, where the distance to each city would be weighted by the number of customers assigned to it... this would be managable, don't you think?