simple project on excel

The problem: A bank issues five types of loans. In addition, to diversify its portfolio, and to minimize risk, the bank invests in risk-free securities. The loans and the risk-free securities with their annual rate of return are given in the table below. Type of Loan or Security Home mortgage (first) Home mortgage (second) Commercial loan Automobile loan Home improvement loan Risk-free securities Annual Rate of Return (%) 6 8 11 9 10 4 The bank’s objective is to maximize the annual rate of return on investments subject to the following policies, restrictions, and regulations: 1. The bank has $90 million in available funds. 2. Risk-free securities must contain at least 10 percent of the total funds available for investments. 3. The investment in mortgage loans (first & second) must be at least 60 percent of all the funds invested in loans. 4. The investment in first mortgage loans must be at least twice as much as the investment in second mortgage loans. 5. Automobile loans and home improvement loans together may not exceed the commercial loans. 6. Commercial loans cannot exceed 50 percent of the total funds invested in mortgage loans (first & second). (a) Formulate and solve an LP model that will determine the optimal allocation of funds and maximize returns. (b) Set up an Excel spreadsheet for this problem and determine the optimal solution. (c) Obtain the sensitivity report.