Please solve the problems below using Excel Solver. Please use the necessary settings. Also, please continue to adhere the spreadsheet modeling principles. Each page has a separate question. 1. Oil Blending: An oil company produces three brands of oil: Regular, Multi-grade, and Supreme. Each brand of oil is composed of one or more of four crude stocks, each having a different lubrication index. The relevant data concerning the crude stocks are as follows: Crude Stock Lubrication Index Cost ($/barrel) Supply per day (barrels) 1 20 7.10 1000 240 8.50 1100 7.70 1200 4 55 9.00 1100 Each brand of oil must meet a minimum standard for a lubrication index, and each brand thus sells at a different price. The relevant data concerning the three brands of oil are as follows: AwN 25 Brand Min. Lubrication Index Regular Multi-grade 35 Supreme 50 Selling Price ($/barrel) $8.50 $ 9.00 $ 10.00 Daily Demand (barrels) 2000 1500 750 Determine an optimal output plan for a single day, assuming that production can be either sold or else stored at negligible cost. The daily demand figures are subject to alternative interpretations. Investigate the following three interpretations: a. Daily demands are strict obligations. In other words, the model should contain demand constraints that are met precisely. What is the optimal profit? b. Daily demands represent minimum sales commitments, but all output could be sold. In other words, model should permit production to exceed the daily commitments. What is the optimal profit? 2. Selecting a Portfolio: A portfolio manager has developed a list of six investment alternatives for a multi-year horizon. These are Treasury bills, Common stock, Corporate bonds, Real Estate, Growth Funds, and Savings and Loans. These investments and their various financial factors are described below. In the table, the length represents the estimated number of years required for the annual rate of return to be realized. The annual rate of return is the expected rate over the multi-year horizon. The risk coefficient is a subjective estimate representing the manager's appraisal of the relative safety of each alternative, on a scale of 10. The growth potential is also a subjective estimate of the potential increase in value over the horizon. Alternative RE TB 6 15 CB 12 RE GE 24 - Alternative Length Annual Return (%) Risk Coefficient Growth Potential 0 18 10 32 The manager wishes to maximize the annual rate of return on a $ 3 million portfolio, subject to the following restrictions: The weighted average length should not exceed 7 years. The weighted average risk coefficient should not exceed 5. The weighted average growth potential should be at least 10 percent. The investment in real estate should be no more than twice the investment in stocks and bonds (CS, CB, and GF) combined. 1. What is the optimal return (as a percentage) and the optimal allocation of investment funds? 2. What is the marginal rate of return? That is, what would be the return on the next dollar invested, if there were one more dollar in the portfolio? 3. For additional investment beyond the original $3 million, how will the optimal allocation change? 3. Coffee Blending and Sales Hill-o-beans Coffee Company blends four component beans intro three final blends of coffee: one is sold to luxury hotels, another to restaurants, and the third to supermarkets for store-label brands. The company has four reliable bean supplies: Argentine Abundo, Peruvian Colmado, Brazilian Maximo, and Chilean Saboro. The table below summarizes the very precise recipes for the final coffee blends, the cost and availability of four components, and the wholesale price per pound of the blends. The percentages indicate the fraction of each component that can be used in each blend. Rest Market Cost/lb. undo 20% Component Hotel (pounds) Abundo Colmado 40% Maximo 15% Saboro 25% Wholesale $1.25 price/lb. 35% 15% 20% 30% $1.50 10% 35% 40% 15% $1.40 $0.60 $0.80 $0.55 $0.70 Max. Weekly Availability 40,000 25,000 20,000 45,000 The processor's plant can handle no more than 100,000 lb per week, and Hill-o-beans would like to operate at capacity, if possible. Selling the final blends is not a problem, although the Marketing department requires minimum production levels of 10,000; 25,000; and 30,000 lbs, respectively, for the hotel, restaurant, and market blends. a. To maximize weekly profit, how many pounds of each component should be purchased? b. How should the optimal profit change if there were a 1000-lbs increase in the availability of Abundo beans? Colmado? Maximo? Saboro