Question

3. (30pt) A company considers the following 4 projects: Project Capital Investment in year 0$10,000$6,000 A В С D $4,000 $8,000 -$2,000 $5,000 $1,600 $1,300$1,500 $4,000 $5,000 $8,000 $1,200 Net Cash Flow in 1 year Net Cash Flow in year 2 $4,000 $8,000 $10,200 Net Cash Flow in year The MARR is 10% per year. The company has a $20,000 budget for the capital investment in year 0. a) (10pt) Find NPV8 of 4 Projects, formulate a binary integer programming problem to select projects that maximizes the total NPV, and determine which projects the company should se- lect by solving the problem with the Excel solver b) (10pt) In addition to the budget constraint, we further assume that the total cash flow in year 1 must be nonnegative. Formulate a binary integer programming problem to select projects that maximizes the total NPV, and determine which projects the company should select by solving the problem with the Excel solver c) (10pt) In addition to the constraints in b), we further assume that Project C can be chosen only if Project D has been selected and that Projects B and C are mutually exclusive. Formulate a binary integer programming problem to select projects that maximizes the total NPV, and determine which projects the company should select by solving the problem with the Excel solver

3a, b and c please help

Answer 1

Define 4 binary variables, one for each project such that:

xA = 1, if project A is selected; 0 if project A is not selected

xB = 1, if project B is selected; 0 if project B is not selected

xC = 1, if project C is selected; 0 if project C is not selected

xD = 1, if project D is selected; 0 if project D is not selected

3a). Maximize NPV: (xA*NPV of project A) + (xB*NPV of project B) + (xC*NPV of project C) + (xD*NPV of project D)

Constraints:

Constraint 1: (xA*Capital investment for project A) + (xB*Capital investment for project B) + (xC*Capital investment for project C) + (xD*Capital investment for project D) <= 20,000

Constraint 2: xA, xB, xC, xD $\in$ {0,1}

Using Solver, we get:

Project А 150 151 Year o 152 Year 1 153 Year 2 154 Year 3 155 NPV 10000 16001 4000 8000 770.85 6000 1300 5000 12001 215.63 4000 -1500 4000 80001 3952.67 8000 -2000 5000 10200 1977.46 156 157 Binary variables 158 XA. 159 XB 160 XC 161 XD 162 163 Maximize NPV Binary 0 0 or 1 1 0 or 1 11 Oor 1 1 Oor 1 6145.76 164 165 Constraint: 166 Budget constraint 18000 20000

10000 1600 4000 8000 =NPV(10%,B152:B154)-B151 6000 4000 8000 1300 -1500 -2000 5000 4000 5000 1200 8000 10200 =NPV(10%,C152:C154)-C151 =NPV(10%,D152:D154)-D151 =NPV(10%,E152:E154)-E151 150 Project 151 Year o 152 Year 1 153 Year 2 154 Year 3 155 NPV 156 157 Binary variables 158 xA 159 XB 160 XC 161 XD 162 163 Maximize NPV Binary O or 1 O or 1 O or 1 O or 1 =(B158*B155)+(B159*C155)+(B160*D155)+(B161*E155) 164 165 Constraint: 166 Budget constraint =(B158*B151)+(B159*C151)+(B160*D151)+(B161*E151) 20000

Result: Selected projects for maximum NPV are projects B, C and D.

3b). Maximize NPV: (xA*NPV of project A) + (xB*NPV of project B) + (xC*NPV of project C) + (xD*NPV of project D)

Constraints:

Constraint 1: (xA*Capital investment for project A) + (xB*Capital investment for project B) + (xC*Capital investment for project C) + (xD*Capital investment for project D) <= 20,000

Constraint 2: (xA*Year 1 cash flow for project A) + (xB*Year 1 cash flow for project B) + (xC*Year 1 cash flow for project C) + (xD*Year 1 cash flow for project D) >= 0

Constraint 3: xA, xB, xC, xD $\in$ {0,1}

10000 1600 4000 8000 770.85 B 6000 1300 5000 12001 215.63 C 4000 -1500 4000 8000 3952.67 80001 -2000 5000 10200 1977.46 T 1 150 Project 151 Year o 152 Year 1 153 Year 2 154 Year 3 155 NPV 156 157 Binary variables | 158 XA 159 XB 160 XC 161 xD 162 163 Maximize NPV 164 165 Constraint: 166 Budget constraint 167 Year 1 cash flow non-negative Binary Oor 1 Oor 1 0 or 1 Oor 1 o l 4939.14 20000 20000 14001

150 Project 151 Year 0 10000 6000 4000 8000 152 Year 1 1600 1300 -1500 -2000 153 Year 2 4000 5000 4000 5000 154 Year 3 8000 1200 8000 10200 155 NPV ENPV(10%,B152:B154)-B151 =NPV(10%,C152:C154)-C151 ENPV(10%,D152:D154)-D151 ENPV(10%,E152:E154)-E151 156 157 Binary variables Binary 158 xA O or 1 159 XB O or 1 160 XC Oor 1 161 XD Oor 1 162 163 Maximize NPV =(B158*B155)+(B159*C155)+(B160*D155)+(B161*E155) 164 165 Constraint: 166 Budget constraint =(B158*B151)+(B159*C151)+(B160*D151)+(B161*E151) 20000 167 Year 1 cash flow non-negative =(B158*8152)+(B159*C152)+(8160*D152)+(8161*E152)

Result: Selected projects are projects A, B and C.

3c). Maximize NPV: (xA*NPV of project A) + (xB*NPV of project B) + (xC*NPV of project C) + (xD*NPV of project D)

Constraints:

Constraint 1: (xA*Capital investment for project A) + (xB*Capital investment for project B) + (xC*Capital investment for project C) + (xD*Capital investment for project D) <= 20,000

Constraint 2: (xA*Year 1 cash flow for project A) + (xB*Year 1 cash flow for project B) + (xC*Year 1 cash flow for project C) + (xD*Year 1 cash flow for project D) >= 0

Constraint 3: xD -xC = 0

Constraint 4: xB + xC = 1

Constraint 5: xA, xB, xC, xD $\in$ {0,1}

в с D E 1600 40001 000 770.85 6000 13001 50001 1200 215.63 4000 -1500 4000 80001 3952.67 8000 -2000 5000 10200 1977.46 3 8 150 Project 151 Year o 152 Year 1 153 Year 2 154 Year 155 NPV 156 157 Binary variables 158 xA 159 XB 160 XC 161 XD 162 163 Maximize NPV 164 165 Constraint: 166 Budget constraint 167 Year 1 cash flow non-negative Project C is selected only if 168 project D is selected Projects B and Care mutually 169 exclusive 1. 1 0 0 Binary Oor 1 Oor 1 0 or 1 0 or 1 986.48 20000 2900

4000 8000 -1500 -2000 4000 5000 8000 10200 =NPV(10%,D152:D154)-D151 ENPV(10%, E152:E154)-E151 150 Project 151 Year o 10000 6000 152 Year 1 1600 1300 153 Year 2 4000 5000 154 Year 3 8000 1200 155 NPV ENPV(10%,B152:B154)-B151 =NPV(10%, C152:C154)-C151 156 157 Binary variables Binary 158 xA Oor 1 159 XB O or 1 160 XC Oor 1 161 XD O or 1 162 163 Maximize NPV =(B158*B155)+(B159*C155)+(B160*D155)+(B161*E155) 164 165 Constraint: 166 Budget constraint =(B158*B151)+(B159*C151)+(B160*D151)+(B161*E151) 167 Year 1 cash flow non-negative =(B158*B152)+(8159*C152)+(B160*D152)+(8161*E152)| Project C is selected only if 168 project Dis selected 1 =B161-B160 Projects B and Care mutually 169 exclusive =B159+B160 20000

Result: Selected projects are projects A and B.