Question

In: Operations Management

Question The towns in Kent with their corresponding x and y coordinates as well as their...

Question

The towns in Kent with their corresponding x and y coordinates as well as their population are given overleaf. This data is also in an excel file that can be downloaded from Moodle under the name “Kent-Towns”.

(a) Using Excel Solver, or otherwise, establish a location in the plane, that minimises:

(i) the sum of distances to all Kent towns,

(ii) the sum of weighted distances to all Kent towns, with populations as weights,

(iii) the maximum distance to any Kent town, and

(iv) the maximum weighted distance to any Kent town.

(b) Assume from now on that facilities can only be established at the given towns, rather than anywhere in the plane.

(i) Establish at which town a single facility should be built, if the aim is to minimise the sum of distances from the facility to all Kent towns.

(ii) Having established this facility, use the ADD heuristic to find the location of a second facility. Allocate every town to its nearest facility. Explain why these two facilities are not necessarily the optimal solution to the p-facility discrete location problem (p=2). Can you find (using your own heuristic thinking) a pair of locations that gives a better result?

In your answers to the above, clearly explain how you have arrived at your results.


TOWN/CITY

X

Y

POPULATION

Ashford

600985

142805

58,178

Broadstairs

639320

167760

24,370

Canterbury

614880

157830

42,249

Chatham

575785

167920

70,540

Dartford

554200

174325

50,000

Deal

637510

152745

29,248

Dover

631650

141835

39,078

Faversham

601530

161425

18,000

Folkestone

622765

135915

53,411

Gillingham

577350

168385

99,773

Gravesend

564730

174170

51,150

Herne Bay

617900

167945

31,000

Maidstone

576150

155705

75,000

Margate

635460

170580

58,465

Northfleet

562235

174310

13,590

Ramsgate

638365

165180

37,967

Rochester

574375

168475

25,000

Royal Tunbridge Wells

558360

139265

45,000

Sevenoaks

552375

155295

18,588

Sheerness

591955

174725

20,000

Sittingbourne

590740

163660

55,000

Tonbridge

559080

146600

31,600

Whitstable

610670

166740

30,000



Solutions

Expert Solution

(a)

(i) Coordinates of the central location that minimizes the sum of distances is determined using Solver as follows

formulas:

E2 =((B2-$B$26)^2+(C2-$C$26)^2)^0.5   copy to E2:E24

E26 =SUM(E2:E24)

Coordinates of central location:

X = 591,389

Y = 163,104

(ii) Coordinates of the central location that minimizes the sum of weighted distances is determined using Solver as follows

Formulas:

E2 =((B2-$B$26)^2+(C2-$C$26)^2)^0.5

F2 =E2*D2  

copy these formulas down to row 24 of excel

F26 =SUM(F2:F24)

Coordinates of the central location are:

X = 575,458

Y = 144,936

(iii) Minimize the maximum distance from any city

Additional formulas:

F2 =$E$26-E2   copy to F2:F24

E26 =F26

Coordinates of the central location :

X = 595,848

Y = 161,527

(iv) Minimize the maximum weighted distance from any city

Additional formulas:

F2 =E2*D2 copy to F2:F24

G2 =$F$26-F2 copy to G2:G24

F26 =G26

Coordinates of the central location are:

X = 597,845

Y = 168,224


Related Solutions

ONLY PART B REQUIRED: Question 1 The towns in Kent with their corresponding x and y...
ONLY PART B REQUIRED: Question 1 The towns in Kent with their corresponding x and y coordinates as well as their population are given overleaf. This data is also in an excel file that can be downloaded from Moodle under the name “Kent-Towns”. (a) Using Excel Solver, or otherwise, establish a location in the plane, that minimises: (i) the sum of distances to all Kent towns, (ii) the sum of weighted distances to all Kent towns, with populations as weights,...
The following charges exist (given coordinates are (x, y) coordinates in the plane of the page):
  The following charges exist (given coordinates are (x, y) coordinates in the plane of the page): • a −3.0 µC point charge located at (0, 0) • a −2.0 µC uniform spherical shell of charge of radius 3.0 cm centered at (+4.0 cm, 0) • a +2.0 µC uniform spherical shell of charge of radius 2.0 cm centered at (0, −4.0 cm) • a +4.0 µC uniform spherical shell of charge of radius 4.0 cm centered at (−1.0 cm,...
Find the coordinates of the point (x, y, z) on the plane z = 4 x...
Find the coordinates of the point (x, y, z) on the plane z = 4 x + 1 y + 4 which is closest to the origin.
Find the coordinates of all local extrema and inflections of y = x − 4√x. Give...
Find the coordinates of all local extrema and inflections of y = x − 4√x. Give the intervals where the function is increasing, decreasing, concave up, and concave down.
For the following u(x, y), show that it is harmonic and then find a corresponding v(x,...
For the following u(x, y), show that it is harmonic and then find a corresponding v(x, y) such that f(z)=u+iv is analytic. u(x, y)=(x^2-y^2) cos(y)e^x-2xysin(y)ex
In the accompanying figure (Figure 1), the X and Y coordinates (in meters) of station A...
In the accompanying figure (Figure 1), the X and Y coordinates (in meters) of station A are 1007.673 and 1869.166, respectively, and those of station B are 1565.021 and 648.185, respectively. Angle BAP was measured as 308∘56′39′′ and angle ABP was measured as 58∘53′30′′. What are the coordinates of station P?
The x and y coordinates of a particle in motion as functionsof time t, are given...
The x and y coordinates of a particle in motion as functionsof time t, are given by: x=5t^2-5t+6 y=3t^3-3t^2-12t-4 At the instant the x-component of velocity is equal to zero,the y component of the acceleration is closest to: A. 12 B. -24 C. -15 D. -1.5 E. 3.0
1-Use the​ (x,y) coordinates in the figure to find the value of the trigonometric function at...
1-Use the​ (x,y) coordinates in the figure to find the value of the trigonometric function at the indicated real​ number, t, or state that the expression is undefined. SIN 11π/6 2-Use the​ (x,y) coordinates in the figure to find the value of the trigonometric function at the indicated real​ number, t, or state that the expression is undefined. COS π//6 3- Use the​ (x,y) coordinates in the figure to find the value of tan 4π/3 or state that the expression...
The coordinates of a point P in the x-y plane are (2;3) meters. q1 = 10...
The coordinates of a point P in the x-y plane are (2;3) meters. q1 = 10 C load (1;0) , q2 = 20 C load (-1;0) is placed in the metre position. The electric flied formed by these charges at the P point, find your size and direction.
in C++ Write a definition for a class ‘point’ to describe a point with coordinates (X,Y)...
in C++ Write a definition for a class ‘point’ to describe a point with coordinates (X,Y) in 2-D space. The private data members are the coordinates X and Y of type ‘double’. The public member functions are: A default constructor (no parameters) to initialize X and Y to zero. An explicit value constructor with two parameters to initialize the values of X and Y. Two functions, one to set the X value and the other to set the Y value...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT