A company is considering seven large investments. Each investment can be made only once. These investments differ in the estimated long-run profit that they will generate as well as the amount of capital required as shown in the table below (in millions of dollars):
Investment Opportunity
1 2 3 4 5 6 7
Estimated Profit 17 10 15 19 7 13 9
Capital Required 43 28 34 48 17 32 23
The total amount of capital available for these investments is $100 million. Investment opportunities 1 and 2 are mutually exclusive (i.e., if you select opportunity 1 you cannot select opportunity 2 and vice versa), and so are opportunities 3 and 4. Furthermore, neither 3 nor 4 can be undertaken unless one of the first two opportunities (i.e., either 1 or 2) is undertaken. There are no such restriction on the rest of the opportunities 5, 6, and 7. The objective is to select the combination of capital investments that will maximize the total estimated long-run profit.
a) Formulate a linear model to achieve this objective.
b) Implement your model using Excel and find the optimal solution using Solver. Include in your report a snapshot of the optimal solution (which investments to select and the value of the objective)