Dwayne has four independent vendor proposals to contract the nationwide oil recycling services for the Ford Corporation manufacturing plants. All combinations are acceptable, except that vendors B and C cannot both be chosen. Revenue sharing of recycled oil sales with Ford is a part of the requirement. Develop all possible mutu­ally exclusive bundles under the additional fol­lowing restrictions and select the best projects. The corporate MARR is 10% per year. Write both the hand solution PW relations and spreadsheet functions necessary to perform the analysis.

Question

(a) A maximum of $4 million can be invested.

(b) A larger budget of $5.5 million is allowed, but no more than two vendors can be selected.

(c) There is no limit on spending.

Answer 1

By hand: Determine the PW for each project



PWA = -1,500,000 + 360,000(P/A,10%,8) = $420,564

PWB = -3,000,000 + 600,000(P/A,10%,10) = $686,760

PWC = -1,800,000 + 620,000(P/A,10%,5) = $550,296

PWD = -2,000,000 + 520,000(P/A,10%,5) = $-28,784 (not acceptable)

By spreadsheet: Enter the following to display the project PW value.



A: = -PV(10%,8,360000)-1500000 Display: $420,573

B: = -PV(10%,10,600000)-3000000 Display: $686,740

C: = -PV(10%,5,620000)-1800000 Display: $550,288

D: = -PV(10%,5,520000)-2000000 Display: $-28,791 (not acceptable)



Formulate acceptable bundles from the 24 = 16 possibilities, without both B and C,

and select projects with largest total PW of a bundle.



(a) With b = $4 million, select projects A and C with PW = $970,860.