Question

USE SOLVER IN EXCEL TO ANSWER PROBLEM. Show step by step how you got your answer

SOLVER | 387 18-2. Seven new projects are being proposed for our division. Each would require investments over the next three years, but each ultimately would result in a positive return on our investment Project A B с D E F G Expenditures in $ Millions Year 1 Year 2 Year 3 5 8 4 7 9 3 8 4 3 9 2 7 4 3 2 3 6 8 9 Return in $ Millions 20 36 28 20 18 12 40 - The CEO has decided that we have a maximum of $25 million each year to invest. Just on the basis of the numbers, which combination of projects should we invest in so that the sum of the returns is maximized? Set up a worksheet and use Solver to come up with a solution. You should set up the worksheet so there is a cell for each project that contains either a 1 (meaning we invest in the project) or a 0 (meaning we do not invest in the project). Solver should find the combination of l's and O's that maximizes the sum of the returns subject to the constraints.

Answer 1

Project Return Profit A 20.00 36.00 Year1 5.00 4.00 B 6.00 16.00 13.00 С 3.00 Expenditure Total Year2 Year3 Expendit 1.00 8.00 14.00 7.00 9.00 20.00 8.00 4.00 15.00 9.00 2.00 14.00 4.00 1.00 12.00 2.00 3.00 8.00 8.00 9.00 23.00 23.00 22.00 58.00 D 28.00 20.00 18.00 12.00 6.00 E 6.00 חד 3.00 7.00 3.00 6.00 13.00 4.00 G 40.00 TOTAL(B,C&G) 17.00 46.00 Decision: Better invest in Project B, C and G