USING EXCEL SOLVER WITH STEPS PLEASE

Problem 3. Mean Green Trucking has entered the third party logistics market. The executive team has requested assistance from the LSCM 4560 class to determine the best location for their facility. They recently acquired a new account which has distribution centers at 12 locations throughout the Northeastern United States and Southeastern Canada (see map below for the geographic region served). Your task is to determine the optimal location for a single Mean Green Trucking DC to serve these locations. The CEO wants to obtain the total cost for the optimal location coordinates and total cost for the optimal location. The distribution center will support customers at the following coordinates with corresponding rates and volumes: X Volume 300 10 120 Rate 2.19 2.35 2.30 125 142 210 115 117 90 35 88 200 530 2.25 2.10 150 63 Customer Cleveland Quebec Ottawa Toronto Boston New York Pittsburgh Philadelphia Portland Syracuse Baltimore 111 33 800 23 32 475 20 96 163 500 210 1.95 1.95 1.90 2.25 2.15 90 95 75 185 75 8 525 2.00 QUBEC La Vrendrye Presque Isle Sudbury N.B. Queber Your task is to compute the X and Y coordinates and the total cost for the optimal location. The analysis should appear on a worksheet titled "Problem 3". The total cost for the optimal location should be calculated using either the exact grid or median approach as you believe appropriate. You should explain your selection to the executive team. Your analysis must use Solver to calculate coordinates and the total cost for the optimal location. As a result, you will have columns with the distance from each customer and cost. Problem 3a: Management has decided that they would prefer to locate their distribution facility in Allentown, PA (X= 65, Y=25). How does the total cost associated with this location compare to the optimal decision obtained in problem #3? Homework #1 Facility Location, Spring 2021 Page 7 Problem 3. Mean Green Trucking has entered the third party logistics market. The executive team has requested assistance from the LSCM 4560 class to determine the best location for their facility. They recently acquired a new account which has distribution centers at 12 locations throughout the Northeastern United States and Southeastern Canada (see map below for the geographic region served). Your task is to determine the optimal location for a single Mean Green Trucking DC to serve these locations. The CEO wants to obtain the total cost for the optimal location coordinates and total cost for the optimal location. The distribution center will support customers at the following coordinates with corresponding rates and volumes: X Volume 300 10 120 Rate 2.19 2.35 2.30 125 142 210 115 117 90 35 88 200 530 2.25 2.10 150 63 Customer Cleveland Quebec Ottawa Toronto Boston New York Pittsburgh Philadelphia Portland Syracuse Baltimore 111 33 800 23 32 475 20 96 163 500 210 1.95 1.95 1.90 2.25 2.15 90 95 75 185 75 8 525 2.00 QUBEC La Vrendrye Presque Isle Sudbury N.B. Queber Your task is to compute the X and Y coordinates and the total cost for the optimal location. The analysis should appear on a worksheet titled "Problem 3". The total cost for the optimal location should be calculated using either the exact grid or median approach as you believe appropriate. You should explain your selection to the executive team. Your analysis must use Solver to calculate coordinates and the total cost for the optimal location. As a result, you will have columns with the distance from each customer and cost. Problem 3a: Management has decided that they would prefer to locate their distribution facility in Allentown, PA (X= 65, Y=25). How does the total cost associated with this location compare to the optimal decision obtained in problem #3? Homework #1 Facility Location, Spring 2021 Page 7