Question

Engineering Division has been asked to select at least 3 out of 7 possible sites for oil exploration at a cost of $50,000 per site evaluation. Evaluating Sites 1 and 3 will prevent you from exploring Site 7, and evaluating Sites 2 or 4 will prevent you from evaluating Site 5. Site 3 cannot be evaluated unless Site 4 is evaluated.

a) Formulate a mathematical linear program to minimize the total cost. Clearly define your decision variables, objective and constraints

b) Implement the formulation in Excel and solve with Solver to find the optimal solution. Include a snapshot of your solution in the report

(PLEASE SHOW FORMULAS ON EXCEL)

Answer 1

a) Mathematical Linear program is following

Let Xi be a binary variable such that Xi =1 indicates that site i is selected for oil exploration, otherwise not.

Min 50000*(X1+X2+X3+X4+X5+X6+X7)

s.t.

X1+X2+X3+X4+X5+X6+X7 >= 3

X1+X3+X7 <= 2

X2+X4+2X5 <= 2

X4-X3 >= 0

Xi {0,1}

b) Implementation in Excel and solution using Solver is as follows

Formula:

I2 =SUMPRODUCT(B2:H2,$B$8:$H$8) copy to I2:I6

As per Solver output, sites 1,2 and 4 must be selected for oil exploration.