Please solve using solver in Excel and show the steps/screen shots from Excel

(25 points) 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) (5 points) 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) (9 points, 3 points each) 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 Table 2: Budget limitation and production level over the next 3 years (c) (5 points) Now assume that the selection must satisfy the production level as well as the budget limit over each of the next 3 years as indicated in Table 2. The production and capital 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