Question

Question 5 An investor wants to invest $5 million in different projects. After preliminary assessment, he is focusing on 8 promising projects. Each project is characterized by a budget and an associated net present value (NPV). These budgets and NPVs are displayed in the following table. Project Budget (in $100,000 NPV (in $100,000) 31 22 17 31 28 17 15 33 16 9 10 13 15 8 622 The investor wants to find a portfolio of projects that will maximize the sum of the net present values. Formulate the problem using binary variables. (5 points) Solve the problem using Excel Solver. What is the optimal solution? What is the optimal objective value. (5 points) The investor considers that projects D, E, and F are quite risky. Therefore, he does not want to select more than one out of these risky projects. Write the corresponding model and solve it using Excel Solver. (5 points) 1) 2) 3)

Answer 1

1) Formulation of the problem is following:

Let X = 1, if project X is selected for investment, otherwise X = 0 , where X={A,B,C,D,E,F,G,H}

Max 31A+22B+17C+31D+28E+17F+15G+33H

s.t.

16A+9B+10C+13D+15E+8F+6G+22H <= 50

A,B,C,D,E,F,G,H binary

2) Solution using Excel Solver is following:

EXCEL FORMULAS:

J3 =SUMPRODUCT(B3:I3,$B$8:$I$8) copy to J5

Optimal solution:

Projects A, D, E and G should be selected for investment.

Optimal objective value = 105 ($100k)

3)

In the BIP model developed in part 1, following constraint is added.

D+E+F <= 1

Solution using Excel Solver is following:

Optimal solution:

Projects A,B,C,F and G should be selected for investment

Optimal objective value = 102 ($100k)