Question 10.8: Using Excel^® to solve a multifacility, minimax location pro...

Recall Example 10.1, in which a single new general purpose machine tool was to be added to a maintenance department, which consisted of five existing machines with the following coordinate locations: P_{1} = (1, 1), P_{2} = (6, 2), P_{3}= (2, 8),P_{4}= (3, 6), and P_{5} = (8, 4). Suppose two new special purpose machine tools are to be added, instead of the more expensive general purpose machine tool, with the work flow divided between the two new machines. Additionally, suppose there will be work flow between the two new machines.
Specifically, suppose the flow data are as follows: w_{11}=10, w_{12}=20, w_{13}=20,w_{14}=0,w_{15}=0,w_{21}=0,w_{22}=0,w_{23}=5,w_{24}=20,w_{25}=25,v_{12}=10.

As shown in Figure 10.15, a solution of ( x^{*}_{1}, y^{*}_{1} ) = (2.07, 3.07) and (x^{*}_{2}, y^{*}_{2}) ) =
(7.02, 6.49) was obtained using SOLVER. Interestingly, either slight or dramatic changes in the initial solution will yield significantly different “optimum solutions.” Examples of other optimum solutions are shown below.

An obvious pattern exists for NF1, y^{*}_{1} = x^{*}_{1} + 1. However, we cannot be sure that this holds for all values of (x^{*}_{1}, y^{*}_{1} ). Other patterns were also observed. For example,
when ( x^{*}_{2}, y^{*}_{2} ) = (7.02, 6.49), we find that ( x^{*}_{1}, y^{*}_{1} ) = (k, k+1) for 2≤ k ≤5.5;
when( x^{*}_{1}, y^{*}_{1} ) (2.00, 3.00), we find that x^{*}_{2}, y^{*}_{2} ) = (k, 3.97) for 4.04≤ k≤ 5.03; and

when (x^{*}_{1}, y^{*}_{1} ) = (2.00, 3.00), we find that (x^{*}_{2}, y^{*}_{2} ) = (4.04, k) for 3.96≤k≤4.04.

The presence of multiple optimum solutions should not be surprising. When one new facility creates the “maximum condition,” the other new facility is “free to roam.” By identifying

an optimum location for one new facility and treating it as a pseudo existing facility, the singlefacility solution procedure can be used to determine the multiple optimum locations for the other new facility.
The maximum is taken over the product of each weight and distance (distance traveled along the x-axis plus distance traveled along the y-axis) for all pairs of facilities, new and existing. The absolute differences are based on values of the decision variables shown in row 16.
A nice feature of the Excel® solution is the ease with which sensitivity analysis can be performed. For example, the impact of alternate distributions of work flow between the two

new machines can be easily determined using SOLVER. Figure 10.16 contains the solution for a different set of weights for the existing facilities: ( x^{*}_{1}, y^{*}_{1} ) = (4.31, 3.81), and ( x^{*}_{2}, y^{*}_{2} )= (3.09, 4.09), and f (x*) = 125. Notice the significant changes in the optimum locations by assigning NM1 to EM1, EM2, and EM4 and assigning NM2 to EM3 and EM5, instead of splitting up between NM1 and NM2 work flowing to/from EM3.
Based on the Excel® solution to the multifacility, minimax location problem,
you might wonder why we didn’t use Excel® to solve the single-facility version of the problem. In addition to wanting to expose you to a variety of ways to solve location problems, we remind you that SOLVER is not guaranteed to produce optimum solutions

since a search procedure is used. Due to the “flatness” of the objective function in a region near the optimum, the search can terminate prematurely. That being said, it is still likely that the solution obtained using Excel® will be a good, if not optimum, solution. But, we cannot state unequivocally that we obtained the optimum solution to Example 10.8.