Please help! Has to be done in Excel (Quantitive Business Analysis

7. Investment Advisors, Inc., is a brokerage firm that manages stock portfolios for a num- ber of clients. A particular portfolio consists of U shares of U.S. Oil and H shares of Huber Steel. The annual return for U.S. Oil is $3 per share and the annual return for Huber Steel is $5 per share. U.S. Oil sells for $25 per share and Huber Steel sells for $50 per share. The portfolio has $80,000 to be invested. The portfolio risk index (0.50 per share of U.S. Oil and 0.25 per share for Huber Steel) has a maximum of 700. In addition, the portfolio is limited to a maximum of 1000 shares of U.S. Oil. The linear programming formulation that will maximize the total annual return of the portfolio is as follows: Max 3U + 5H Maximize total annual return s.t. 25U + 50H = 80,000 Funds available 0.50U + 0.25H 700 Risk maximum 1U s 1000 U.S. Oil maximum U, H20 a. The sensitivity report for this problem is shown in Figure 8.15. What is the optimal solution, and what is the value of the total annual return? b. Which constraints are binding? What is your interpretation of these constraints in terms of the problem? What are the shadow prices for the constraints? Interpret each. d. Would it be beneficial to increase the maximum amount invested in U.S. Oil? Why or why not? c. FIGURE 8.15 SENSITIVITY REPORT FOR THE INVESTMENT ADVISORS PROBLEM Variable Cells Model Variable U H Name U.S. Oil Huber Final Value 800.000 1200.000 Reduced Cost 0.000 0.000 Objective Coefficient 3.000 5.000 Allowable Increase 7.000 1.000 Allowable Decrease 0.500 3.500 Constraints Constraint Number Name Funds available Risk maximum U.S. Oil maximum Final Value 80000,000 700.000 800.000 Shadow Price 0.093 1.333 0.000 Constraint R.H. Side 80000.000 700.000 1000.000 Allowable Increase 60000.000 75.000 1E+30 Allowable Decrease 15000.000 300.000 200.000 3