FINC3017 Investments and Portfolio Management Report 1: Asset Allocation and Portfolio Construction Due: 4:00pm, Thursday 16th September 2021 (week 6) Weight: 30% You are hired as an investment consultant with the task of identifying optimal portfolios that meet various criteria for prospective clients and explaining aspects of the portfolio construction process. You are to prepare a professional report for your manager which addresses the investment issues for the three clients summarised in Table 1. You also need to prepare a response to a question from each client. The common theme for these clients is an interest in building a US equities portfolio. Table 1: Client list and portfolio criteria Client Investor information and portfolio criteria Amy Zhao (A) - Risk aversion coefficient: 10 - Short-sale restriction: No access to short selling permitted - Lending access: Can lend at the risk-free rate - Borrowing access: Can borrow at the risk-free rate - Client A question: I’ve heard diversification improves portfolio risk-adjusted performance. Would my expected portfolio performance improve if I set a diversification mandate in my portfolio that limits the maximum exposure to any single security in the risky portfolio at 30%? Why? Bill Agrawal (B) - Risk aversion coefficient: 8 - Short-sale access: Unrestricted - Lending access: Can lend at the risk-free rate - Borrowing access: Can borrow at the risk-free rate plus 50bps - Client B question: I’ve heard that it’s always best to use the longest sample period and most recent data, but your manager indicated that you are not using the Covid period in your forecasts. What would happen if you did? Cameron Richards (C) - Risk aversion coefficient: 1.5 - Short-sale access: Unrestricted on stocks with market cap > $500bn as at 30 June 2021, otherwise not permitted. - Client C question: I’ve heard you use some sort of optimization algorithm to determine the portfolio recommendation. Why should I trust a “robot” when I’m paying humans to provide financial advice? An initial investment screen has identified a set of 20 US listed equities presented in Table 2. Your intern has collected monthly total returns for these stocks for the period January 2015 to June 2021, as contained in the spreadsheet ‘Report 1 2021S2 - data.xlsx’. Unless otherwise stated, risky portfolio recommendations are to be comprised exclusively of these stocks and the data compiled by your intern. You determine to use historical data estimate inputs of expected return, volatility and covariance for your portfolio modeling. However, due to the impact of Covid-19 on financial markets you exclude the period after January 2020 in your primary analysis. That is, you need to restrict your main sample to the 60-month period from January 2015 to December 2019. You have also identified that the applicable risk-free rate for your analysis is 2.0% p.a. Table 2: Investment universe Ticker Stock Market Cap ($bn) as at 30 June 2021 AAPL Apple Inc 2,285.5 BA Boeing Co 140.1 MCD McDonald’s Corp 172.4 PFE Pfizer Inc 219.2 WMT Walmart Inc 395.2 MGM MGM Resorts International 20.9 CAKE Cheesecake Factory Inc 2.5 RCL Royal Caribbean Group 21.7 AMZN Amazon.com Inc 1,735.0 RL Ralph Lauren Corp 5.7 YUM YUM Brands Inc 34.3 EBAY eBay Inc 47.8 GS Goldman Sachs Group Inc 129.1 NVDA Nvidia Corporation 498.5 ILMN Illumina Inc 69.1 GME Gamestop Corp 15.4 FB Facebook Inc 833.1 TSLA Tesla Inc 654.8 SAVE Spirit Airlines Inc 3.3 YELP Yelp Inc 3.0 Report structure Your manager has requested the following structure and word limits for the report. Your task is to use the Markowitz approach to calculate the optimal portfolio for each client incorporating their investment criteria and prepare a report that addresses each of the items below. Where applicable, charts and tables are highly recommended. 1. Executive summary of portfolio strategies and key issues. (word limit: 100) 2. Calculate the expected return, volatility and correlation of the 20 stocks, presenting your numbers in a neat table, and provide a concise written discussion of these results. (word limit: 150, excluding numerical tables and figures) 3. Optimal portfolio recommendations for each investor. This section must clearly: a. Identify the weight of each stock in the risky portfolios b. Identify the net borrowing or lending position in the combined portfolio and a brief discussion of the effect of leverage on the investment recommendation c. Determine the annualised expected returns, standard deviation and Sharpe ratio for each combined portfolio as well as the expected utility for each investor. d. Discuss the differences in weights and Sharpe ratios between the different portfolio recommendations for each investor. e. Explain how each of the portfolio recommendations addresses the investor’s portfolio criteria and, if applicable, specifically note any binding constraints on risky assets. This part of the report does not need to consider the specific investor questions. (word limit: 300, excluding numerical tables and figures) 4. Provide a response to Client A’s question. (word limit: 250, excluding numerical tables and figures) 5. Provide a response to Client B’s question, with specific discussion relating to sensitivity of model with and without the sample period 2020 (word limit: 250, excluding numerical tables and figures) 6. Provide a response to Client C’s question (word limit: 250, excluding numerical tables and figures) 7. Conclude with a summary of the key recommendations and responses of your report. (word limit: 150) Further requirements Assume that investor utility can be described by the function U = E(R) – ½As2. Your report will need to present the weights for each portfolio you calculate as well as the expected returns, standard deviation and Sharpe ratio for each portfolio. It is recommended you use the ‘MMULT’ function and Solver package in Excel. If using Solver, please set the initial weights to be equal weights when conducting each optimisation. Address the requirements of each question clearly. In preparing your report, it is recommended you consider how charts, tables, subheadings and dot points can be used enhance the way you present your findings. The data provided in this report is adapted from real stock market data and has been customised for the purposes of this assignment. You should only use the data provided to you in completing this report (you are not required to gather any additional data). Further, ignore any potential transaction costs, fees and taxes in determining your responses. Marks will be awarded for correct quantitative analysis, the clarity of your discussion, the structure of your report and how you present your findings. Please use graphs and/or tables to support your discussion but do not include the raw data in your written report. Please use minimum 11pt font with 2cm margins and include all references, if required, in a bibliography. Faculty and/or university referencing requirements must be followed and academic honesty policies apply. References do not count towards word limit. Written reports must be submitted via the Turnitin link labelled ‘Report 1’ as a word of pdf document. You also need to submit your workings. Workings will not be directly graded. Submit your workings as an Excel spreadsheet via the ‘Report 1 – Supporting workings’ link in Canvas (or code if using an alternative optimization package). Please ensure you have submitted both your report and supporting working and that you document your submission through Turnitin (confirmation email/screenshot) to avoid late submission penalty issues. Some helpful tips • [Sharpe ratio(Annual)] = sqrt(12) * [Sharpe ratio(Monthly)] • [Expected return(Annual)] = 12 * [Expected return(Monthly)] • [Standard deviation(Annual)] = sqrt(12) * [Standard deviation(monthly)] • bps = Basis points = 1/100th of 1%, i.e. 0.01%. For example, 5% plus 1bps = 5.01%