business analytics

Problem 4: Capital Budgeting (30 points) Yakima Construction Corporation (YCC) is considering a number of different development projects. The cash outflows that would be required to complete each project are indicated in the table below, along with the expected net present value of each project (all values in millions of dollars). Each project must be done in full (with the corresponding cash flows for all four years) or not done at all. Furthermore, there are the following additional considerations. Project 1 cannot be done unless project 2 is also undertaken, and projects 3 and 4 would compete with each other, so they should not both be chosen. YCC expects to have the following cash available to invest in these projects: $40 million for year 1,$25 million for year 2, \$16 million for year 3, and \$12 million for year 4. Any available money not spent in a given year is then available to spend the following year. YCC's policy is to choose their projects so as to maximize their total expected NPV. Considering the budget limitations and the laws, which of the investments/projects should be chosen to maximize potential NPV? a. Formulate a Binary Integer Programming (i.e., BIP) model in algebraic form for this problem. Define the decision variables, objective function, and constraints. (16 points) b. Formulate this same problem on a spreadsheet and SOLVE using Excel solver (Provide a printout of the corresponding "Excel Spreadsheet" and the "Answer Report"). Describe clearly and completely the optimal solution to this problem using a managerial statement (i.e. describe verbally the results). (14 points)