Gravity Location Models
nortonw
Chapter5_BookExample2_HW_Rebuild_Instruction1.docxExample for Phase III: Gravity Location Models
|
|
|
|
|
|
|
|
|
|
Sources/ |
$/Ton Mile |
Tons |
Coordinates |
|
|
|
|
Markets |
Fn |
Dn |
xn |
yn |
dn |
|
Sources |
Buffalo |
0.90 |
500 |
700 |
1200 |
|
|
|
Memphis |
0.95 |
300 |
250 |
600 |
|
|
|
St. Louis |
0.85 |
700 |
225 |
825 |
|
|
Markets |
Atlanta |
1.50 |
225 |
600 |
500 |
|
|
|
Boston |
1.50 |
150 |
1050 |
1200 |
|
|
|
Jacksonville |
1.50 |
250 |
800 |
300 |
|
|
|
Philadelphia |
1.50 |
175 |
925 |
975 |
|
|
|
New York |
1.50 |
300 |
1000 |
1080 |
|
The example discussed in this subsection is shown in Figure 5-8 of the book.
Associated spreadsheet: Figure 5-8 in the textbook
HW: rebuild the book example worksheet from scratch and solve it. Upload the solved worksheet to Bb.
The data above is provided as input in the associated spreadsheet in Cells A3:F12. The Decision Variables (location of facility) is set up in Cells B16:B17.In Cells G5:G12, we calculate the distance from the facility to each source or destination using Equation 5.4. The calculation of the distance between Buffalo and the facility is shown in Cell G5 to be: (Pay attention to fixed cell address with “$.” The new facility x and y cell address should have “$” sign. )
dn = = SQRT(($B$16-E5)^2 + ($B$17-F5)^2)
The formula is then copied to cells G6:G12.
The Objective function is obtained in Cell B19 using Equation 5.5 to be
Cost = SUMPRODUCT(G5:G12,D5:D12,C5:C12)
The goal in this case is to find a facility location that minimizes total cost. To set up Solver we use Data | Analysis | Solver. In the Solver Dialog box we enter the objective, decision variables, and constraints as shown in Figure 5-8 (and detailed below).
Set Objective: $B$19 (Cell B19 contains the objective function)
To: Min (our goal is to minimize the total cost)
By changing variable cells: $B$16:$B$17 (these cells contain all the decision variables)
Subject to the constraints:
Here no constraints are needed because the optimal facility location may have negative coordinates. Click on “Solve” to obtain the optimal solution. The optimal location is shown by the pink dot on the chart.
Other scenarios that can be tried are as follows:
1. Change the quantity shipped from St. Louis (Cell D7) to 1,700. What does this change to the facility location?
2. Change the quantity shipped from St. Louis (Cell D7) to 2,700. What does this change to the facility location?
