PLEASE SHOW EXCEL FORMULAS

Problem 3: (100 points) Caliente City is located in a particularly warm and arid part of the United States, so prone to the occurrence of fires. However, the city has only one fire station, located in the congested center of the town. The result has been long delays in fire trucks reaching fires in the outer parts of the city, causing more damage than would have occurred with a prompt response. The city residents are very unhappy about this and city council has directed the city manager to develop a plan for locating multiple fire stations throughout the city that would greatly reduce the response time to less than or equal 10 minutes. The city has been divided into 8 tracks. Table 2 shows the estimated response time for a fire in each tract from a potential fire station in each of the eight tracts. The bottom row of the Table 2 shows what the cost would be of acquiring the land and the construction of a fire station in any of the eight tracts. The problem is to determine which tracts should receive a fire station to minimize the total cost of stations while ensuring that each tract has at least one station close enough to respond to a fire in no more than 10 minutes. Formulate and solve a spreadsheet model for this problem. Table 2 Fire Station in Tract 1 2 3 4 5 6 7 8 1 2 8 18 9 23 22 16 28 Response 2 9 3 10 12 16 14 21 25 Times 3 17 8 4 20 21 8 22 17 (Minutes) 4 10 13 19 2 18 21 6 12 for a Fire 5 21 12 16 13 on 5 11 9 12 in Tract 6 25 15 7 21 15 3 14 8 7 14 22 18 7 13 15 2 9 8 30 24 15 14 17 9 8 3 Cost of Station 350 250 450 300 50 400 300 200 ($thousands)