Question

you recommend adding this time to sewing, cutting, or quality control? (Provide a detailed justification of your recommendation). (10 points) Question 5 An investor wants to invest $5 million in different projects. After 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 A B C D E F G H Budget (in $100,000) NPV (in $100,000) ー6 | 9 | iol 131 1ST 8 | 6 31 22 17 31 28 17 15 33 The investor wants to find a portfolio of projects that will maximize the sum of the net present values. 1) Formulate the problem using binary variables. (5 points) 2) 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) 3)

Answer 1

SOLUTION

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:

SSolver Parameters 3 NPV (in $100k) 31 22 17 31 281715 33 105 i Set Objective: 5 Budget (in $100k) 16 9 10 13 15 8 6 22 50 50 ⓔ Max Min ○ Value Of: 10 By Changing Variable Cells: SBS8:SIS8 Subject to the Constraints: 8 Result: Add 10 SBS8:SIS8 binary Change 12 13 14 15 16 17 18 Delete Reset AlI Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Select the LP Simplex engine for linear Solver Problems. 21 23 24 25 Solve Close

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:

J3 Q fx -SUMPRODUCT(B3:13,SB$8:$I$8) SSolver Parameters 3 NPV (in $100k) 31 22 17 31 2817 15 33 102 Set Objective: To: By Changing Variable Cells: SIS3 O Max O Min O Value Of:o 5 Budget (in $100k) 16 9 10 13 15 8622 49<50 SBS8:SIS8 Subject to the Constraints: 8 Result: Add 10 BS8:SIS8-binary 12 13 14 15 16 17 18 19 Delete Reset All | Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Select the LP Simplex engine for linear Solver Problems. 21 23 24 25 Solve Close

Optimal solution:

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

Optimal objective value = 102 ($100k)