90.2K

Verified Solution

Link Copied!

Use the table below to answer the following question(s) by invoking the binary constraints on the variables using the standard Solver.

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.

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

Solve in excel using the solver function.

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 & Explanation Solved by verified expert

See Answer