Question

QUESTION 1 Use the table below to answer the following question(s). Shirley Templeton is a real estate agent for Paralol Realty. Shirley is given the responsibility to manage potential customers for 5 of the Realty's bungalows. These 5 bungalows are situated in close proximity. In order to make traveling easier, Shirley decides to move to a location closer to the 5 bungalows. The table below gives the location (X and Y coordinates) of the 5 bungalows along with the number of trips she would have to make to each bungalow. Create a nonlinear model based on the data given in the table below noting that the objective is to reduce the weighted distance between Shirley's accommodation and the 5 bungalows. Bungalow Location X-coordinate | Y-coordinate Data Location Bungalow 1 Bungalow 2 Bungalow 3 Bungalow 4 Bungalow 5 125 20 90 60 100 75 80 100 Trips/Month 16 10 15 18 10 According to the model, what is the weighted distance between Shirley's new accommodation and Bungalow 5? O 206.58 O 250.78 O 435.89 O 589.25

Answer 1

Set up Excel model as follows:

A B с D E F | xl Location X Y Trips/Month 1 2 Bungalow 1 0 0 6 3 Bungalow 2 25 110 10 4 Bungalow 3 20 75 15 5 Bungalow 4 90 80 18 6 Bungalow 5 60 100 10 7 8 Shirley's new accomr 9 A 1 1 10 Straightline distance Load Distance =SQRT(SUMXMY2(B2:C2,$B$9:$C$9)) =E2*D2 =SQRT(SUMXMY2(B3:03,$B$9:$C$9)) =E3*D3 =SQRT(SUMXMY2(B4:04,$B$9:$C$9)) =E4*D4 =SQRT(SUMXMY2(B5:C5,$B$9:$C$9)) =E5*D5 =SQRT(SUMXMY2(B6:06,$B$9:$C$9)) !=E6*D6 11 Total load-distance = =SUM(F2:F6) 12 13

Note that we need any non zero values for coordinates for Shirley's new accommodation, in order to initialize the model

Set up Solver Parameters as follows:

Solver Parameters Set Objective: $F$111 To: Max Min O Value of: 0 By Changing Variable Cells: $B$9:$C$9 Subject to the Constraints: < Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: GRG Nonlinear Options Solving Method Select the GRG Nonlinear engine for Solver problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

It;s a nonlinear model, so Select a Solve Method: GRG Nonlinear

А B C D G Trips/Month 6 10 15 18 Location X Y 1 2 Bungalow 1 0 0 3 Bungalow 2 25 110 4. Bungalow 3 20 75 5 Bungalow 4 90 80 6 Bungalow 5 60 100 7 8 Shirley's new accommodation: 9 A 49.5261 87.5607 10 11 E Straightline distance 100.60 33.24 32.09 41.17 16.26 F Load Distance 603.58 332.42 481.30 741.13 162.62 10 9 Total load-distance = 2,321.05 12

Solver Results Х Solver found a solution. All Constraints and optimality conditions are satisfied. Reports Answer Sensitivity Limits Keep Solver Solution O Restore Original Values Return to Solver Parameters Dialog Outline Reports OK Cancel Save Scenario... Solver found a solution. All constraints and optimality conditions are satisfied. When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this means Solver has found a global optimal solution.

Click OK

The Weighted distance (or Load distance) between Shirley's new accommodation and Bungalow 5 is 162.62 .

Note that this answer is better than all of the given options. the closest is 206.58

Therefore, answer would be 206.58