Question

Include 1 19.Using Solver, which constraints should be used if you have a capital investment budget of $2,500,000, projects A and B are mutually exclusive, and project D must be accepted? 1 Project 620 200 352.100 1.245 600 814300 6Total =SUM(B2B5°5052 5055) 7 Constraint 1 2,500,000 8 Constraint 2 =SUM(D2 D3) 9 Constraint 3 =D5 NPV 72 658 36,418 212,150 70 925 SUM(C2C5°3052 5056) SUM(O2:05) e) $D$2:$D$5-binary: SBS6 <= $B$7; $B$8 <= 1; and SB$9 = 1 f) $B$2:$B$5 binary: $B$6 <= $B$7; and SBS8 = 1 g) $C$2:$C$5 = binary: $B$6 >= $B$7; $D$6= 1; and $D$5 = 0 h) SAS2:$A$5 = binary and $B$6 >= $B$7 i) $D$2:$D$5 binary: SBS6 <= $B$7; $B$8 = 2; and SDS5 = 0

Answer 1

The correct answer option to the question is (e).

In the given model, the project costs in column B and the NPVs in column C form the inputs. The 'Include' column (in column D) has the binary inputs representing whether the project is included or not - '1' for project getting included and '0' for the project not getting included. So, when =SUMPRODUCT() of the 'Cost' and 'Include' columns and that of 'NPV' and 'Include' columns are calculated in cells B6 and C6 respectively, we will get the total values for only those projects which are included.

The target here is to maximize NPV (C6) while staying within the capital constraint of $2,500,000 (B6). In the answer option, the constraint on capital is represented by the constraint $B$6 <=$B$7.

There are further constraints to achieving this target:

1. Projects A and B are mutually exclusive, which means either A or B can be included. In logical terms, this constraint can be set as sum(D2:D3) <=1. In the answer option, this is represented by the constraint $B$8 <=1.
2. It is also given that project D must be included. Logically, D5 = 1. In the answer option, this is the constraint $B$9 = 1.

In addition to the above constraints, we also need to constrain the values that the cells in 'Include' column can take. The cells can contain either 1 or 0 since it is a yes or no (binary) decision. This is captured in the constraint $D$2:$D$5 = binary in the answer option.

Hence, the constraints captured in option (e) are the correct ones and achieve the maximum NPV within the stipulated capital budget. Screenshot of the Solver inputs is given below. Under these constraints, including projects B, C and D gives the maximum NPV of $319,493 with a total capital outlay of $2,412,000.

Solver Parameters Set Objective: $C$6 To: o Max Min value of: By Changing Variable Cells: $D$2:$D$5 Add Subject to the Constraints: $B$6 <= $B$7 $B$8 <= 1 $B$9 = 1 $D$2:$D$5 = binary Change Delete Reset All Load/Save V Make Unconstrained variables Non-Negative Select a Solving Method: GRG Nonlinear Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Option (f) is incorrect as it assigns binary constraint to the array B2:B5 and does not include the constraint that project D must be included.

Options (g) is incorrect since it assigns binary constraint to the array C2:C5 and says D5 = 0 whereas D5 = 1. It also says $B$6 >= $B$7 which cannot be case since the capital budget is stipulated at $2,500,000.

Option (h) is incorrect since it assigns binary constraint to the array A2:A5 and says $B$6 >= $B$7. It also does not include the constraint that project D must be included.

Option (i) is incorrect since it assigns a constraint that B8 = 2 which could result in both projects A and B getting included whereas only one of the two can be included. It also says D5 = 0 which is wrong.