M5.2 An investor wishes to invest all of her $8 million in a diversified portfolio through a commercial lender. The types of investments, the expected annual interest rate for the investment, and the maximum allowed percentage of the total portfolio that the investment can represent are shown in the table below: INVESTMENT EXPECTED INTEREST MAXIMUM ALLOWED (% of total portfolio) Low-income mortgage loans 4.80% 30% Conventional mortgage loans 4.20% 25% Government sponsored mortgage loans 8.20% 15% Bond investments 4.45% 40% Stock investments 7.50% 15% Futures trading 8.50% 10% She wants at least 30% of her total investment in non-mortgage instruments. Furthermore, she wants no more than 20% of her total investment to be in high-yield and high-risk instruments (i.e. expected interest rate of investment is 8% or greater). Formulate and solve this problem in Excel to determine how her money should be diversified in a manner which will meet the requirements and maximize the amount of interest income. (Hint: Make sure that the LHS and RHS of constraints are the same units) SOLUTION: The optimal solution, the value of the interest income, should be $463,400 ( $10 for rounding). The minimum invested in any one investment is $0. The maximum invested in any one investment is $2,800,000. Questions: a) Based on your solution, how much should be invested in government sponsored mortgage loans? b) Based on your solution, how much should be invested in stock investments? c) If the return on low-income mortgage loans was reduced to 4%, how much should be invested in these low-income mortgage loans based on your new solution? Use the LP Template.xlsx provided to you in the Module Documents & Files. Solve the problem using Excel Solver, place the answers to the questions in the appropriate text boxes. I will delete your decision variables in your Excel submission and re-solve your problem. There is no credit for the problem if your Excel submission doesn't solve. See the diagram of a correct solution provided to you on the first problem. If necessary, remove problem constraints so that your Excel file solves. It is better to get partial credit than no credit. 4 M5.3 A student project at WCU was initiated to try to determine the impact of implementation of new technologies. The students want to survey both distance and residential undergraduate students in the four different years at Western (first year, sophomore, junior, and senior). They have estimated that it will cost them $7.00 to survey first year and sophomore residential students and $9.00 to survey junior and senior residential students. The cost to interview distance students is slightly higher. It will cost $7.50 for first year and sophomores and $9.50 for junior and seniors. For statistical validity they want to interview at least 1000 students. They feel that there are certain criteria that they must adhere to: a) At least 30% of first year students surveyed should be distance students (Hint: this is not 30% of all students, this is 30% of the combination of the two types of first year students should be distance first year students. Example: If first year distance student are 50 and first year residential students are 30 - this means 62.5 % (50/(30 + 50) or 50/80) of the students are first year distance students. b) At least 25% of sophomore students surveyed should be distance students c) At least 30% of junior students surveyed should be distance students d) At least 40% of senior students surveyed should be distance students e) No more than 25% of all the students surveyed should be first year students f) Juniors and seniors should be at least 40% of the students surveyed g) Each of the eight types of students must be represented in the survey by at least 8% of the total interviews (Hint: this is 8 separate constraints) Formulate and solve this problem in Excel to determine the number of each type of student that should be surveyed that meets the requirements and minimizes the cost to carry out the interviews. (Hint: Since you know that to minimize cost you will interview exactly 1000 students - you can simplify constraints e, f, and g by knowing the exact value for the RHS. Constraints a ,b, c, and d cannot be simplified in the same way - review example calculation in a.) SOLUTION: The optimal solution, the value of the cost, should be $7,963.75. Some of your decision variables might not be integers - this is OK. You should have a total of 15 constraints (not including non-negativity). Questions: a) If the cost of surveying first year and sophomore residential students increases from $7.00 to $8.50 - what is the new minimum cost in your optimal solution? b) If your survey required that at least 15% of the students in the survey must be residential senior students - what is the new minimum cost in your optimal solution? Use the LP Template.xlsx provided to you in the Module Documents & Files. Solve the problem using Excel Solver, place the answers to the questions in the appropriate text boxes. I will delete your decision variables in your Excel submission and re-solve your problem. There is no credit for the problem if your Excel submission doesn't solve. See the diagram of a correct solution provided to you on the first problem. If necessary, remove pr