Facility Location Models

1
Example 7.6

• Facility Location Models

2
Background Information

• The Lafferty Company wants to locate a warehouse
  from which it will ship products to four
  customers.
• The location (in the x-y plane) of the four
  customers and the number of shipments per year
  needed by each customer are given in this table.

Data for Lafferty Example Data for Lafferty Example Data for Lafferty Example Data for Lafferty Example
x y Shipments per Year
Customer 1 5 10 200
Customer 2 10 5 150
Customer 3 0 12 200
Customer 4 12 0 300
3
Background Information -- continued

• All locations are in miles, relative to the point
  x0 and y0.
• A single warehouse must be used to service all of
  the customers.
• Lafferty wants to determine the location of the
  warehouse that minimizes the total distance
  traveled from the warehouse to the customers.

4
Solution

• For the spreadsheet model we need to keep track
  of the following
• The x and y coordinates of the warehouse and each
  customer
• The distance between the warehouse and each
  customer
• The total annual distance traveled from the
  warehouse to customers.

5
LAFFERTY.XLS

• The model appears on the next slide.
• This file can be used to build the model.

6
(No Transcript)
7
Developing the Model

• The model can be formed as follows.
• Inputs. Enter the given customer data in the
  shaded ranges.
• Coordinates of warehouse. Enter any trial values
  in Location range for the x- and y-coordinates of
  the warehouse.
• Distances from warehouse to customers. Calculate
  the distances from the warehouse to the customers
  in the Distances range. To do so, recall that the
  (straight line) distance between the two points (
  a,b ) and (c,d ) isTherefore, enter the
  formula SQRT((B5-B11)2(C5-C11)2)in
  cell B14 and copy it to the rest of the Distances
  range.

8
Developing the Model -- continued

• Total annual distance. The total annual distance
  traveled from the warehouse to meet the demands
  of all customers iswhere ni is the number of
  trips per year for customer i and di is the
  distance from the warehouse to customer i.
  Therefore, calculate the total annual distance
  traveled from the customers to the warehouse in
  the TotDistance cell with the formula
  SUMPRODUCT(Shipments,Distances).

9
Using the Solver

• This model requires the leanest Solver setup so
  far.
• All we need to specify is that TotDistance should
  be minimized and the Location range contains the
  changing cells.
• There are no constraints, not even nonnegativity
  constraints.
• Also, because of the squares in the straight-line
  distance formula, this model is nonlinear, so the
  Assume Linear Model box should not be checked.

10
Solution

• The warehouse should be located at x9.31 and
  y5.03. Each year a total of 5456.54 miles will
  be traveled annually from the warehouse to the
  customers.
• This solution represents a compromise. On the
  other hand, Lafferty would like to position the
  facility near customer 4 because customer 4 is
  fairly far from the other customers, the
  warehouse is located in a more central position.

11
Sensitivity Analysis

• One possible sensitivity analysis is to see how
  the optimal location of the warehouse changes as
  the annual number of shipments to any particular
  customer increases.
• We do this for customer 4 in the table on the
  following slide.
• We run SolverTable with the number of shipments
  to customer 4 as the single input cell, allowing
  it to vary from 300 to 700 in increments of 50,
  and we keep track of the total annual distance
  and the warehouse location coordinates.

12
Sensitivity Analysis -- continued

• As expected, the total annual distance increases
  as the annual shipments to customer 4 increase.
• Also, the warehouse gradually gets closer to
  customer 4. In fact, when the annual shipments to
  customer 4 are 600 or above, the optimal location
  for the warehouse is at customer 4.

13
Is the Solver Solution Optimal?

• The Lafferty model has no constraints.
• An NLP with no constraints is called an
  unconstrained NLP. Therefore, we know that the
  Solver will find an optimal solution if the
  objective is a convex function of the x- and
  y-coordinates of the warehouse.
• It can be shown that the annual distance traveled
  is indeed a convex function of the coordinates of
  the warehouse.Therefore, we know that the Solver
  solution is optimal.

14
Is the Solver Solution Optimal? -- continued

• However, what if you do not know whether the
  objective is a convex function?
• Then the best strategy is to try different
  starting solutions in the Locations range, run
  the Solver on each of them, and see whether they
  all take you to the same solution.
• In fact, we have made this easy for you in the
  LAFFERTY.XLS file.

15
Is the Solver Solution Optimal? -- continued

• We have written two short macros that are
  automated by clicking on buttons.
• Click on the left buttons to randomly generate a
  new starting location.
• Then click on the right button to run Solver.
• You should find that they always take you to the
  same solution.