Question

Below is the data for the spreadsheet for a project selection model:

Project              NPV (return)       cash investment personnel requirements

1              75,000                 30,000                          10

2              50,000                 45,000                          5

3              25,000                 75,000                          10

4              85,000                 30,000                          4

5              50,000                 45,000                          8

6              25,000                 75,000                          7

7              95,000                 45,000                          6

8              25,000                 60,000                          1

9              75,000                 30,000                          2

10            65,000                 45,000                          2

You have $250,000 to invest and 36 people. Maximize NPV by approving/rejecting projects.

Multiple Choice #8: How many projects of the ten are approved?

A) 6

B) 4

C) 10
D) 5

Multiple Choice #9: If I told you that you cannot accept both projects #3 and project #4, would your current optimal solution change?

A) Yes; since we current accept both projects, we know the optimal answer would have to be different with an additional constraint.

B) No; since we are not accepting project #3 anyway, our current solution would still be optimal.

C) No; since we are not accepting project #4 anyway, our current solution would still be optimal.

D) No; since we are not accepting either project, our current solution would still be optimal.

E) There is no way we can tell unless we redefine the problem and rerun Solver.

Multiple Choice #10: Has project #3 been approved (in our optimal solution)?

A) Yes; this is one of the projects that has been approved.

B) No; there is enough spare (aka slack aka extra) cash, but not enough spare personnel.

C) No; there is enough spare personnel, but not enough spare cash to invest.
D) No; there is neither enough spare cash nor spare personnel.

Multiple Choice #11: Has project #8 been approved (in our optimal solution)?

A) Yes; this is one of the projects that has been approved.

B) No; there is enough spare (aka slack aka extra) cash, but not enough spare personnel.

C) No; there is enough spare personnel, but not enough spare cash to invest.
D) No; there is neither enough spare cash nor spare personnel.

Multiple Choice #12: What is the total amount of cash used for all accepted projects?

A) $ 480,000

B) $ 250,000

C) $ 360,000
D) $ 225,000

Multiple Choice #13: What is the total amount of personnel used for all accepted projects?

A) 36

B) 32

C) 40
D) 55

Multiple Choice #14: What is the total return obtained from all accepted projects?

A) $ 495,000

B) $ 330,000

C) $ 445,000

D) $ 570,000

Answer 1

Answers are:

(8): Option “a” – 6

(9): Option “b” - No; since we are not accepting project #3 anyway, our current solution would still be optimal.

(10): Option “d” - No; there is neither enough spare cash nor spare personnel.

(11): Option “c” - No; there is enough spare personnel, but not enough spare cash to invest.

(12): Option “d” – 225,000

(13): option “b” – 32

(14): option “c” – 445,000

Explanation:

In this case we will introduce binary variables for each project. Let the binary variables be a for project 1, b for project 2….. j for project 10.

Thus objective function = sum product of (binary variables, NPV) and has to be maximized.

Constraints:

(i): sum product of (binary variables, investment) <= 250,000

(ii) sum product of (binary variables, personnel requirements) <=36.

(iii): All variables (i.e. a to j) are binary

Solving in excel using the solver function we get the following solution:

Project	Binary variable	NPV	Investment	Personnel		NPV * binary variable	binary variable * investment	binary variable * personnel
1	1	75,000	30,000	10		75,000	30,000	10
2	-	50,000	45,000	5		-	-	-
3	-	25,000	75,000	10		-	-	-
4	1	85,000	30,000	4		85,000	30,000	4
5	1	50,000	45,000	8		50,000	45,000	8
6	-	25,000	75,000	7		-	-	-
7	1	95,000	45,000	6		95,000	45,000	6
8	-	25,000	60,000	1		-	-	-
9	1	75,000	30,000	2		75,000	30,000	2
10	1	65,000	45,000	2		65,000	45,000	2
				Total		445,000	225,000	32
Objective function	445,000
Constraints
225,000	<=	250,000
32	<=	36