This is for Management Science Can anyone help me set this u

This is for Management Science. Can anyone help me set this up in excel using screen shots. I need to set this up in excel and then to solve I have to use the solver.

The Bayside Art Gallery is considering installing a video camera security system to reduce its insurance premiums. A diagram of the eight display rooms that Bayside uses for exhibitions is shown in figure below; the openings between the rooms are numbered 1-13. A security firm proposed that two-way cameras be installed at some room openings. Each camera has the ability to monitor the two rooms between which the camera is located. For example, if a camera were located at opening number 4, rooms 1 and 4 would be covered; if a camera were located at opening 11, rooms 7 and 8 would be covered; and so on. Management decided not to locate a camera system at the entrance to the display rooms. The objective is to provide security coverage for all eight rooms using the minimum number of two-way cameras.

A) Formulate a 0-1 integer linear programming model that will enable Baysides management to determine the locations for the camera systems. (in excel and I have to solve using the solver)

B) Solve the model formulated in part (a) to determine how many two-way cameras to purchase and where they should have been located.

Solution

Solution: a) The decision variables are X i = 1 if a camera is located at opening i, = 0 if not, i = 1, 2, 3,…, 13 The objective function is to Minimize Z= X1+X2+X3+X4+X5+X6+X7+X8+X9+X10+X11+X12+X13 Subject to the constraints X1 + X4 + X6 = 1 (Room 1) X6 + X8 + X12 = 1 (Room 2) X1 + X2+ X3 = 1 (Room 3) X3 + X4 + X5 + X7 = 1 (Room 4) X7 + X8 + X9 + X10 = 1 (Room 5) X10 + X12 + X13 = 1 (Room 6) X2 + X5 + X9 + X11 = 1 (Room 7) X11 + X13 = 1 (Room 8) X i ’s are binary b) The Excel solver solution of the problem is shown below:

From above the optimal solution is for four cameras at openings numbered 3, 6, 10 and 11.