Question

40.

You are given a group of possible investment projects for your company’s capital. For each project, you are given the NPV the project would add to the firm, as well as the cash outflow required by each project during each year. Determine the investments that maximize the firm’s NPV. The firm has $25 million available during each of the next five years. All numbers are in millions of dollars.

I intend to leave a thumbs up no matter what! I am just happy for a response so Experts please don't worry!!!!

A F G H I J K L M N O 6 в C D E (15 points) Cash outflows in various years, NPV of projects ($ million) Projects 0 Year 1 Year 2 Year 3 Year 4 Year 5 NPV HNEN WPA SPAW WIN mm Nm CONNO P Q R S T U You may change the column widths slightly to accommodate proper formatting, but changing the widths in a significant manner is prohibited. Not following these instructions can result in serious loss of points. All cells in all sheets must be properly formatted. These include aligning contents, using appropriate number format and decimal places for numerical contents, use of bolding, italics, borders, etc. in an appropriate fashion so that the readability of the content is enhanced. oooom Ruw NEU ÚPENWO HS Selected projects (1 if selected, O if not) Projects: 1 Selected projects 2 4 - 3 5 6 7 8 10 9 11 Budget constraints Outflow Budget Year 1 Year 2 Year 3 Year 4 Year 5 Summary of the optimal solution Selected Projects: ?? NPV = ?? Answer key: For the original problem before extensions, C29 = 256 Total NPV

I intend to leave a thumbs up no matter what! I am just happy for a response so Experts please don't worry!!!!

Answer 1

Ans.) Maximize NPV such that

Constraints:

1. Variable assigned for selecting a project is either 0 or 1.

2. Cash outflow for each year is less than or equal than the budget for that year.

Using Solver, optimal NPV = 256;

Projects selected: 1,2,3,7,8,9,10,11

২| Is Project 4 L? Lw | 3 |Year 1 | 4 |Year 2 | 5 |Year 3 | 6 Year 4 7 Year 5 8 NPV |||||| InTw]]] লালিত |||||| নিলা 40] 42 20 10 Selected projects (1 if selected, if not) 11 Projects 12 Selected projects: 4 5 6 7 | ৪ | 9 | | 1০ _T | 11 | 11T T 이 | 이 | 이 14 Budget constraints 15 Outflow Budget 16 Year 1 17 |Year 2 18 Year 3 | 19 Year 4 20 Year 5 25 | 25 22 Total NPV

Solver screenshot:

Solver Parameters B22 А C D E F Set Objective: $B$22 Project To: O Max Min O Value Of: 3 Year 1 4 Year 2 5 Year 3 6 Year 4 7 Year 5 8 NPV By Changing Variable Cells: $B$12:$M$12 Subject to the Constraints: SB$12:SMS12 = binary $B$16:$B$20 < = $C$16:$C$20 Add 2 3 4 5 Change 11 Delete 10 Selected projects (1 if selected, O if not) 11 Projects 1 12 Selected projects: 1 13 14 Budget constraints Outflow 16 Year 1 23 17 Year 2 19 18 Year 3 24 19 Year 4 20 Year 5 24 Reset All Budget 25 25 25 25 25 Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method: GRG Nonlinear Options 22 Total NPV T 256 Solving Method

Extension a:

Maximum NPV = 183

Projects selected: 5,8,10,11,12

DE F G H I K L M Project 3 2 4 3 Year 1 4 Year 2 5 Year 6 Year 4 Year 5 8 NPV 1 2 25 53 13 5 4 6 7 20 30 6 3 31 30 35 40 42 33 35 37 38 10 Selected projects (1 if selected, if not) 11 Projects 12 Selected projects: 11 12 9 0 O O O 8 1 1 O 0 13 14 Budget constraints 15 16 Year 1 17 Year 2 18 Year 3 19 Year 4 20 Year 5 Outflow Budget =SUMPRODUCT($B$12:$M$12,B3:M3) 25 -SUMPRODUCT($B$12:$M$12,04:M4) 25 =SUMPRODUCT($B$12:$M$12,85:M5) 25 =SUMPRODUCT($B$12:$M$12,06:M6) 25 =SUMPRODUCT($B$12:$M$12,87:M7) 25 22 Total NPV -SUMPRODUCT(B12:M12,88:M8) 24 Extension a constraint: 25 At most 1 project can be selected from Proj.1 to 5 =SUM(B12:F12) 26 At most 3 projects can be selected from Proj.1 to 10 "SUM(B12:K12)

Solver screenshot:

Solver Parameters B22 | x ✓ fx Set Objective: SBS22 Project To: O Max Min O Value Of: 3 Year 1 4 Year 2 5 Year 3 6 Year 4 7 Year 5 8 NPV By Changing Variable Cells: $B$12:$M$12 Subject to the Constraints: SB$12:$M$12 = binary SBS 16: SBS20 <= $C$16:$C$20 $B$25 <= $D$25 $B$26 <= $D$26 10 Selected projects (1 if selected, o if not) 11 Projects 12 Selected projects: 13 14 Budget constraints Add Change Delete Reset All 16 Year 1 17 Year 2 18 Year 3 19 Year 4 20 Year 5 Outflow =SUMPRODUCT($B$12:$M$ 12,B3:M =SUMPRODUCT($B$12:$M$12,04:M =SUMPRODUCT($B$12:$M$12,B5:M =SUMPRODUCT($B$12:$M$12,06:M =SUMPRODUCT($B$12:$M$12,87:M Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: GRG Nonlinear Options 22 Total NPV T-SUMPRODUCT(B12:M12,88:M8) 24 Extension a constraint: 25 At most 1 project can be selected from Proj.1 to 5 =SUM(B12:F12) 26 At most 3 projects can be selected from Proj.1 to 10 =SUM(B12:K12) 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. 27

Extension b: Same solution as for the main answer.

Maximum NPV = 256

Projects selected: 1,2,3,7,8,9,10,11

C D E F G M 12 Project 3 4 5 H I J K L 7 8 9 10 11 201139 13lo 12 14 1 3 Year 1 4 Year 2 5 Year 3 6 Year 4 7 Year 5 8 NPV 2 25 1 30 7 30 3 31 35 40 42 20 33 35 37 10 Selected projects (1 if selected, o if not) 11 Projects 12 Selected projects: 12 2 1 3 1 4 0 5 0 6 0 7 1 8 1 9 1 10 1 11 1 1 13 14 Budget constraints 15 16 Year 1 17 Year 2 18 Year 3 19 Year 4 20 Year 5 Outflow Budget =SUMPRODUCT($B$12:$M$12,B3:M3) 25 =SUMPRODUCT($B$12:$M$12,04:M4) 25 =SUMPRODUCT($B$12:$M$ 12,85:M5) 25 =SUMPRODUCT($B$12:$M$12,06:M6) 25 =SUMPRODUCT($B$12:$M$12,87:M7) (25 22 Total NPV =SUMPRODUCT(B12:M12,88:M8) 24 Extension b constraint: 25 If proj.3 is selected, proj.6 is not selected] =D12-G12

Solver screenshot:

Solver Parameters х B22 B Set Objective: SB522 To: O Max Min O Value of: А 2 Project 3 Year 1 4 Year 2 5 Year 3 6 Year 4 7 Year 5 8 NPV C D E F G H 2 3 4 5 6 7 3 4 6 5 4 2 4 4 5 1 5 3 3 3 2 2 1 3 11 21 21 3 5 4 11 3 8 5 6 7 30 35 40 42 20 30 By Changing Variable Cells: $B$12:$M$12 25 Subject to the Constraints: SB$12:SMS12 = binary SBS 16:SB$20 <= SC$16:$C$20 $B$25 >= $D$25 10 Selected projects (1 if selected, if not) 11 Projects 12 Selected projects: 1 Add 5 0 6 0 7 1 110 o Change 14 Budget constraints Delete Outflow Reset All 16 Year 1 17 Year 2 18 Year 3 19 Year 4 20 Year 5 Budget 23 25 19 25 24 25 24 25 24 25 Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: GRG Nonlinear Options 22 Total NPVT 256 24 Extension b constraint: 25 If proj.3 is selected, 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. 1 x

Extension c:

Maximum NPV = 246

Projects selected: 1,2,3,6,8,9,10,11

н тук 8 9 10 11 Project 2 3 4 5 6 7 12 3 4 3 Year 1 4 Year 2 5 Year 3 6 Year 4 7 Year 5 8 NPV 25 30 35 40 42 20 30 31 33 35 37 38 10 Selected projects (1 if selected, if not) 11 Projects 12 Selected projects: 3 4 5 9 10 11 12 6 1 7 0 8 1 1 14 Budget constraints 15 16 Year 1 17 Year 2 18 Year 3 19 Year 4 20 Year 5 Outflow Budget =SUMPRODUCT($B$12:$M$12,B3:M3) 25 =SUMPRODUCT($B$12:$M$12,04:M4) 25 =SUMPRODUCT($B$12:$M$12,85:M5) 25 =SUMPRODUCT($B$12:$M$12,06:M6) 25 =SUMPRODUCT($B$12:$M$12,87:M7) 25 22 Total NPV =SUMPRODUCT(B12:M12,88:M8) 24 Extension c constraint: 25 If proj.788 are selected, proj.5 must be selected =H12+112-F12

Solver screenshot:

B22 1 x ✓ fx Solver Parameters в 1 Project Set Objective: SBS22 To: Max Min O Value Of: O 2 3 Year 1 4 Year 2 5 Year 3 6 Year 4 Year 5 8 NPV 21 11 21 25 30 35 | By Changing Variable Cells: $B$12:$M$12 Subject to the Constraints: $B$12:$M$12 = binary $B$16:$B$20 <= $C$16:$C$20 $B$25 = $D$25 10 Selected projects (1 if selected, if not) 11 Projects 12 Selected projects: Add 1 11 11 13 Change 14 Budget constraints Delete 15 Outflow Reset All 16 Year 1 17 Year 2 18 Year 3 19 Year 4 20 Year 5 Budget 25 25 21 25 22 25 25 25 23 25 Load/Save Make Unconstrained Variables Non-Negative 22 Total NPV 246 Select a Solving Method: GRG Nonlinear Options 24 Extension c constraint: 25 If proj.788 are selected 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.