Problem $6$

A typical large oil and gas company operates many explorations and production projects, which involve several billion dollars every year. These companies are annually faced with the problem of where the capital should be spent and which combination of projects should be selected from several possible project mixes. They have the difficult task of portfolio selection from a large number of competing projects for immediate or future operation under a limited amount of investments.

Consider a small firm with $4$ competing oil production projects. Table $1$ presents the production, capital, and the net present value for these projects. They contact you to help select the best combination of projects under a certain amount of investment, while fulfilling the firm$$s goals.

$($a$)$ Formulate an integer program to maximize the net present value $($NPV$)$ subject to a capital limit stating the firm can spend no more than $$32$ million $($M$$)$ and a production level stating the firm must produce at least $73$ million barrels $($Mbbl$).$

$($b$)$ Suppose that there are the following additional constraints:

$($i$)$ If Project A is selected, then Project B is also selected;

$($ii$)$ Either Project A is selected or Project C selected, but not both; $($iii$)$ At least one of Projects A$,$ B$,$ and D is selected.

Extend your integer program to satisfy theses constraints. Table $1$: Portfolio optimization problem Data

Project NPV $($M$$)$ Capital $($M$$)$ Production $($Mbbl$)$

A $251128$ B $20920$ C $191425$ D $281730$

Table $2$: Budget limitation and production level over the next $3$ years

Year Budget $($M$$)$ Production $($Mbbl$)$

$11820210253730$

$($c$)$ Now assume that the selection must satisfy the production level $($required production output$)$ as well as the budget limit over each of the next $3$ years as indicated in Table $2.$ The production output and capital required for each project during the next $3$ years are given in Table $3.$ Write an integer program to determine the most profitable selection.

Table $3$: Portfolio optimization problem data over the next $3$ years

Project first year second year third year

Capital $($M$$)$

A $543$ B$433$ C$654$ D$855$

Production $($Mbbl$)$

A$558$ B$578$ C $6910$ D $81012$ please solve by excel solver ASAP