Question

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. Given the information in the file P06_40.xlsx, 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.

$1	$3	$4	$6	$5	$4	$2	$0	$1	$3	$9	$8
$3	$4	$4	$5	$1	$5	$3	$0	$1	$2	$2	$7
$2	$3	$3	$2	$2	$1	$3	$4	$4	$4	$1	$4
$1	$1	$2	$2	$3	$5	$4	$6	$8	$1	$1	$1
$2	$1	$3	$8	$5	$6	$7	$3	$6	$1	$1	$1
$25	$30	$35	$40	$42	$20	$30	$31	$33	$35	$37	$38

Answer 1

Project 1	Project 2	Project 3	Project 4	Project 5	Project 6	Project 7	Project 8	Project 9	Project 10	Project 11	Project 12
$1	$3	$4	$6	$5	$4	$2	$0	$1	$3	$9	$8
$3	$4	$4	$5	$1	$5	$3	$0	$1	$2	$2	$7
$2	$3	$3	$2	$2	$1	$3	$4	$4	$4	$1	$4
$1	$1	$2	$2	$3	$5	$4	$6	$8	$1	$1	$1
$2	$1	$3	$8	$5	$6	$7	$3	$6	$1	$1	$1
$25	$30	$35	$40	$42	$20	$30	$31	$33	$35	$37	$38

Let,

yi = 1 if project i is selected else o where i = {1,2,3,4,5,6,7,8,9,10,11,12)

Objective is to maximize NPV so objective function = max 25y1+30y2+35y3+40y4+42y5+20y6+30y7+31y8+33y9+35y10+37y11+38y12

Subject to,

year 1 ->y1+3y2+4y3+6y4+5y5+4y6+2y7+0y8+1y9+3y10+9y11+8y12 <= 25

year 2 ->3y1+4y2+4y3+5y4+y5+5y6+3y7+0y8+y9+2y10+2y11+7y12 <= 25

year 3 ->2y1+3y2+3y3+2y4+2y5+y6+3y7+4y8+4y9+4y10+y11+4y12 <= 25

year 4 ->y1+y2+2y3+2y4+3y5+5y6+4y7+6y8+8y9+y10+y11+y12 <= 25

year 5 ->2y1+y2+3y3+8y4+5y5+6y6+7y7+3y8+6y9+y10+y11+y12 <= 25

yi = binary

Solving in solver we get following projects to be selected:

Project 1

Project 2

Project 3

Project 7

Project 8

Project 9

Project 10

Project 11

Maximized NPV = $256

Solver screenshot

Solver formula