To be done with Excel Solver.

The mayor of Gatorville has decided to completely revamp thecity’s fire services. Towards this end, the mayor has dividedGatorville into seven population areas and identified eightpotential locations for fire stations. The service level criterionis to have a travel time of less than 10minutes from at least one fire station to the mostdistant location in each population area. Table 1 gives the traveltimes, in minutes, from the eight potential fire station locationsto the most distant point in each of the seven populationareas.

Table 1. Travel time matrix

	Location 1	Location 2	Location 3	Location 4	Location 5	Location 6	Location 7	Location 8
Population Area 1	8	12	7	14	6	9	13	22
Population Area 2	16	14	20	6	8	13	11	9
Population Area 3	19	6	9	15	21	14	5	16
Population Area 4	9	8	17	5	18	14	6	8
Population Area 5	23	32	11	8	19	8	12	28
Population Area 6	20	17	7	24	20	15	14	8
Population Area 7	4	9	22	7	23	7	7	18

a. What is the minimum number of fire stations needed to satisfy theservice level objective and where should they be located?

Table 2.

Location	Cost to build a fire station ( in millions of dollars)
1	3.5
2	2.9
3	6.4
4	5.2
5	3.7
6	3.1
7	6.1
8	4.8

2. If the objective is to minimize the totalcost of building fire stations while satisfying the“less than 10 minutes travel time” constraint, how many firestations should be built? Where should these fire stations belocated? And what is the total cost?

3. Now go back to the original problem (i.e., disregard costs).Following are the populations, expressed in thousands of people, inthe seven population areas.

Location	Population (in thousands of people)
1	23
2	14
3	25
4	27
5	16
6	15
7	22

If the objective is to maximize the population coveragewith no more than three firestations, where should the city locate these firestations and what will be the total population covered?

Please note that in maximizing the populationcoverage, a population area’s population may be counted more thanonce.

Also, please note that when you work with binaryvariables you could possibly get a solution in which some changingcell might have a value like 0.9999999. This simply means that thevalue is 1. Also you may end up with a solution value which is somemultiple of E-X (where X is some integer) suchas 3.33E-10. This simply means thevalue is 0.

Answer rating: 100% (QA)

a Excel model Solver inputs Solution Location 1 Location 2 Location 3 Location 4 Location 5 Location ... View the full answer