Problem 3. Your consulting firm continues to attract business due to the success achieved with several clients. A small distributor located in Grand Prairie, TX has approached your firm with a request to perform a facility location analysis. The distributor is interested in relocating a distribution operation from Baton Rouge, LA. They acquired this firm and quickly recognized that the location did not meet their needs from a customer service or total cost perspective. The CEO of the distribution firm has asked you to determine the optimal location for a new distribution center. She recognizes that the optimal location will assist in determining the region or area in which to begin the search. The CEO has provided the following information regarding their customers distribution center locations:

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. Insert your coordinates (latitude, longitude) into Google maps. Based on where the optimal location appears, near what city would you recommend locating the DC? Your response should recognize the DC would be located near a workforce and major highway. Problem 3a: In addition to determining the optimal location, the CEO has requested a cost comparison. What is the total cost associated with their facility located in St Louis, MO? How much will their firm save by moving from St Louis, MO? Their Baton Rouge facility is currently located at the coordinates -9678569775 longitude and 32.79086462 latitude.

State MD CA 2.02 AL N N N N N City Frederick Redlands Loxley Tualitin Nitro Gansevoort Chicago Phoenix Salt Lake City Denver Austin Carrollton Houston Breiningsville Oklahoma City Little Rock Baton Rouge St Louis Sioux Falls Detroit Cleveland 6 = 53s Longitude (X- coordinate) -77.3579 -121.407 -87.7531 -122.391 -77.2705 -73.6918 -87.756569 -112.025 -111.893 -105.02 -97.7483 -96.898585 -95.4111 -75.646129 -97.5013 -92.4176 -96.0498 -90.3863 -96.707425 -83.0777 -81.444249 Zip code Volume Rate/unit distance 21704 168,486 $ 92373 199,743 $ 1.98 36551 50,350 $ 2.15 97062 23,008 $ 2.45 25143 15,388 $ 12831 11,160 $ 60630 8,871 $ 85040 7,130 $ 84115 80,996 $ 80204 7,472 $ 78717 529,178 $ 78249 224,125 $ 7700767,088 $ 18031 309,388 $ 73105 16,890 $ 72211 1,434 $ 70816 8,802 $ 63128 27,952 $ 57104 30,910 $ 2.30 48202 35,186 $ 1.95 45899 38,457 $ 2.03 N Latitude (Y- coordinate) 39.35488 37.6711 30.6471 45.47831 37.22138 43.19533 41.972071 33.40648 40.7145 39.73484 30.49241 32.962141 29.77155 40.477974 35.52003 34.75056 41.20561 38.49206 43.604785 42.374749 41.383262 N i State MD CA 2.02 AL N N N N N City Frederick Redlands Loxley Tualitin Nitro Gansevoort Chicago Phoenix Salt Lake City Denver Austin Carrollton Houston Breiningsville Oklahoma City Little Rock Baton Rouge St Louis Sioux Falls Detroit Cleveland 6 = 53s Longitude (X- coordinate) -77.3579 -121.407 -87.7531 -122.391 -77.2705 -73.6918 -87.756569 -112.025 -111.893 -105.02 -97.7483 -96.898585 -95.4111 -75.646129 -97.5013 -92.4176 -96.0498 -90.3863 -96.707425 -83.0777 -81.444249 Zip code Volume Rate/unit distance 21704 168,486 $ 92373 199,743 $ 1.98 36551 50,350 $ 2.15 97062 23,008 $ 2.45 25143 15,388 $ 12831 11,160 $ 60630 8,871 $ 85040 7,130 $ 84115 80,996 $ 80204 7,472 $ 78717 529,178 $ 78249 224,125 $ 7700767,088 $ 18031 309,388 $ 73105 16,890 $ 72211 1,434 $ 70816 8,802 $ 63128 27,952 $ 57104 30,910 $ 2.30 48202 35,186 $ 1.95 45899 38,457 $ 2.03 N Latitude (Y- coordinate) 39.35488 37.6711 30.6471 45.47831 37.22138 43.19533 41.972071 33.40648 40.7145 39.73484 30.49241 32.962141 29.77155 40.477974 35.52003 34.75056 41.20561 38.49206 43.604785 42.374749 41.383262 N