Warehouse Location Optimization

Using Excel to solve the warehouse location problem:

Need to send shipments from warehouse to demand locations.

Given a set of demand locations and the number of shipments required to each location, find a warehouse so that the summation of weighted distance (i.e., the physical distance * the number of shipments) from the warehouse to each location is MINIMIZED.

The warehouse should satisfy certain constraints.

Exercise:

The location information of each demand location are given in the sheet Warehouse Location, with Latitude and Longitude information are provided.

Decision variables, constraints, objective are also provided.

Note that given one location with Latitude and Longitude (a,b) and another location with Latitude and Longitude (c,d), their physical distance can be approximately represented as 2 + 2. Then the weighted distance will be this value times the number of shipments between these two locations.

Invoke the solver tool to solve the problem.

0 D E F G H A 1 Warehouse Location 2 3 4 Inputs 5 Atlanta Boston 7 Chlongo Denver Houston 10 LA 11 Las Vegas 12 Miami 13 New Orleans 14 New York 15 Philadelphia 16 Phoenix 17 Salt Lake City 18 San Francicoo 19 Latitude Longitude Moments 33.8 18 71.0 6 41.8 87.7 16 1049 15 95.4 12 118.4 19 36.2 115.1 2 25.8 80 2 5 300 89.9 9 40.7 730 14 400 75.1 7 33.5 112.1 8 40.8 111.9 12 37.8 122.6 16 41.6 122.4 7 20 |Latitude) Warehouse Longitude 21 Delcion Variable 22 23 24 Constraints 25 27 Objetive Function Both Latitude and Longitude must be 120 and 2-0 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 ANNA les 9 9 9 9 Total Distance is distance from each city to the warehouse multiplied by shipments made Dictanos Atlanta Boston Chlose Denver Houston LA Las Vegas Miami New Orleans New York Philadelphia Phoenix San Francicoo Total Dictanos