1 | P a g e FIT3158 Business Decision Modelling Semester 2, 2021 Assignment Two - Case 1 Case 1: Unisuper Investment UniSuper began with a single, compelling idea: that they can deliver better value. They are now one of Australia’s largest super funds, with more than 450,000 members and over $100 billion in funds under management. Quote: “We’re passionate about securing the future of our members, sharing in a wealth of wisdom and collective know-how. We empower our members to be confident about their future and make better financial decisions. …We’re the super fund where future-thinking Australians come together.” (https://www.unisuper.com.au/about-us/who-we-are) UniSuper offers pre-mixed investments for members’ superannuation chosen by their experts, or members can build and manage their own portfolio with sector options. Assume you’re a consultant with UniSuper and would like to build an asset allocation model that would provide recommendation on the investment funds for each client’s portfolio. UniSuper offers 7 investment funds and to maintain diversity in each member’s portfolio, the company places some general guidelines on the percentage of each portfolio that may be invested in each of the investment funds. The following table shows the different types of investment funds, the respective performance yields (returns) and the suggested limits on the amount invested in each investment. The last column shows the company’s risk indicators for each of the investment types. Investment Types: Performance Yield Investment limits Risk Indicators Conservative 4.1% 5% - 20% 0.01 Conservative Balanced 5.3% 5% - 20% 0.02 Balanced 9.6% 20% - 50% 0.05 Growth 10.4% 10% - 30% 0.06 High Growth 11.8% 5% - 20% 0.07 Sustainable High Growth 13.5% 5% - 50% 0.08 Sustainable Balanced 9.0% 5% - 20% 0.06 You would also like to factor in the risk tolerance of each of your clients and adjust the portfolio to meet the needs of each individual. Recently, you have a new client, Bob, who is planning to invest $700,000. Based on your evaluation of Bob’s risk tolerance, you assigned a maximum risk index of 0.05 for him. [Note: Risk Index is based on a number of factors such as the customer’s risk appetite, their risk level tolerance, their acceptance to expected frequency of negative returns, etc. This is calculated using a different application which is outside the context of this case.] The company’s risk indicators are shown in the table above. An overall portfolio risk index can be computed by taking the weighted average of the risk rating for the 7 investment funds where the weights will be the fraction of the client’s portfolio invested in each of the funds. 2 | P a g e Based on the information provided; 1. Develop a linear programming model that will provide the maximum yield for the portfolio. 2. The asset allocation model you developed may be useful in modifying the portfolios for all of UniSuper’s clients whenever the anticipated yields for the investment funds are periodically revised. What is your recommendation as to whether the use of this model is possible? 3. Using your model (and the associated sensitivity reports), develop a report to Bob that includes (but not limited to): i). Your recommendation to Bob on how he should allocate his $700,000 among the different types of investment funds. What is the annual yield you anticipate for the investment recommendation? ii). If you’re able to increase Bob’s risk index to 0.052, would that change the investment recommendation and if so, would this increase the performance yield? iii). If Bob’s risk index stays the same, i.e. at 0.05, how would the investment recommendation change if the annual yield for the ‘Sustainable High Growth’ investment type were 13.2% instead of 13.5%. iv). Bob expressed some concerns with regards to investing in ‘Sustainable High Growth’ which has quite a high risk indicator. How would the original recommendation change if the amount invested in ‘Sustainable High Growth’ should only be at most 10% of the total portfolio? 4. While you do not need to explain the sensitivity analysis in your report to Bob, you will need to include an Appendix showing: i). Your calculation in response to Question 3 (part (i) to (iv)) above based on the sensitivity/answer/limits report(s) generated from your model. ii). To improve the performance yield for Bob, which of the investment limits will you change and by how much. What will be the optimal solution if these new limits are enforced. [Note: you should explain this in the context of shadow price, allowable increase/decrease, etc. However, you can re-run your solver to confirm your answer] 3 | P a g e Delivery structure and submission instructions: 1. Your delivery should include an Excel file comprising the following worksheets: - Title page - Documentation - Case 1 Model - Case 1 Sensitivity/Answer Report - Case 1 Report to Bob - Appendix 1.1. The ‘Title Page’ sheet should include the following information: - The title page - Each team member’s contribution (tasks performed and contribution %) 1.2. The ‘Documentation’ sheet should include the following information: - A brief description of the case - An introduction to the asset allocation model you developed and your recommendation as to the future use of this model. How would it be useful in modifying the portfolios for all of UniSuper’s clients whenever the anticipated yields for the investment funds are periodically revised. - The algebraic formulation of the case 1.3. The ‘Model’ sheet should include your spreadsheet model. - When building your model, bear in mind the goals and guidelines for good spreadsheet design as discussed in Lecture 2. Marks are given for good spreadsheet design. - Your model should produce correct and reliable results. - Every member in the group must know the model well and be able to demonstrate/explain the model when asked. 1.4. The ‘Sensitivity/Answer/Limits’ sheet(s) are the reports generated by the Excel Solver based on your spreadsheet model. 1.5. The ‘Report’ sheet should include your report to Bob which will be assessed based on: - Correct responses to the questions asked. - Good analysis and discussion/recommendations showing understanding. - Well-presented and coherent report. 1.6. The ‘Appendix’ sheet which should include: - Your calculation in response to Question 3 (part (i) to (iv)) in the case, based on the sensitivity/answer/limits report(s) generated from your model. - Your calculation and explanation on which investment limits to change and by how much so as to improve the performance yield for Bob. Clearly indicating the optimal solution if these new limits are enforced. 2. You should name your Excel file as:

Assignment 2_Case 1.xlsx (or xlsm) where