Question

,10. ***( I NEED TO SOLVE THIS WITH EXCEL SPREADSHEET AND SENSITIVITY REPORT) ***

Grave City is considering the relocation of several police substations to obtain better enforcement in high-crime areas. The locations under consideration together

Potential Locations for Substations: Areas Covered:

A 1,5,7

B 1,2,5,7

C 1,3,5

D 2,4,5

E 3,4,6

F 4,5,6

G 1,5,6,7

a. Formulate an integer programming model that could be used to find the minimum number of locations necessary to provide coverage to all areas.

b. Solve the problem in part (a).

Answer 1

(a)

The objective is to allocate the substations at minimum level of the locations to monitor the high crime area.

Formulate the integer programming model to find out the minimum number of locations necessary to provide coverage to all areas.

Objective is to minimize the number of locations at which substations are located.

Objective function Z is .

Here, decision variable is the integer variable with being equal to 1, if the substation is present at A, and 0, if substation is not present at A.

The variablebeing equal to 1, if the substation is present at B, and 0, if substation is not present at B.

The variablebeing equal to 1, if the substation is present at C, and 0, if substation is not present at C.

The variablebeing equal to 1, if the substation is present at D, and 0, if substation is not present at D.

The variablebeing equal to 1, if the substation is present at E, and 0, if substation is not present at E.

The variablebeing equal to 1, if the substation is present at F, and 0, if substation is not present at F.

The variablebeing equal to 1, if the substation is present at G, and 0, if substation is not present at G.

The above formulation is subject to the constraints as below.

1) Coverage locations for area 1.

2) Coverage locations for area 2.

3) Coverage locations for area 3.

4) Coverage locations for area 4.

5) Coverage locations for area 5.

6) Coverage locations for area 6.

7) Coverage locations for area 7.

8) Integer variables,

b)

Setup the sheet with decision variables and objective functions as below in figure (1).

Setup the constraints with their values as shown in figure (2).

Setup the solver dialog box inputs as shown in figure (3).

Show the optimal solution results upon clicking the Solve button in worksheet as in Figure (4).

Refer to Figure (4).

Location B is the coverage highest area and E is the next location to cover most area.

Thus, locate the police substations at and to provide coverage to all areas with minimum number.

Sensitivity Report

Objective Cell (Min)
	Cell	Name	Original Value	Final Value
	$A$5	Objective function	3	2
Variable Cells
	Cell	Name	Original Value	Final Value	Integer
	$A$2	A	0	0	Integer
	$B$2	B	1	1	Integer
	$C$2	C	0	0	Integer
	$D$2	D	1	0	Integer
	$E$2	E	0	1	Integer
	$F$2	F	1	0	Integer
	$G$2	G	0	0	Integer
Constraints
	Cell	Name	Cell Value	Formula	Status	Slack
	$B$10	Area 4 B	1	$B$10>=1	Binding	0
	$B$11	Area 5 B	1	$B$11>=1	Binding	0
	$B$12	Area 6 B	1	$B$12>=1	Binding	0
	$B$13	Area 7 B	1	$B$13>=1	Binding	0
	$B$7	Area 1 B	1	$B$7>=1	Binding	0
	$B$8	Area 2 B	1	$B$8>=1	Binding	0
	$B$9	Area 3 B	1	$B$9>=1	Binding	0
	$A$2:$G$2=Integer

****Please please please LIKE THIS ANSWER, so that I can get a small benefit, Please****