Consider the following scenario: In January 2018, you joined Tri-Star Management Pty Ltd as an intern. On your rst day, your CEO asks you to analyze information pertaining to share prices of ve companies: AMP Limited (AMRAX), Australia and New Zealand Banking Group Limited (ANZAX), Commonwealth Bank of Australia (CBAAX), National Australia Bank Limited (NAB.AX) and Qantas Airways Limited {QANAX}. The le 2019 FiN510.xlsx contains data on the adjusted end of month share prices of these five companies from January 2015 December 2017. In addition, you are provided with the value of the 5&P/ASX2OO market index over this period. The basic data are provided in columns A - G. You will be asked a series of questions to guide your analysis. You will need to manipulate the data to answer the questions below. Some Excel formulas are provided in the worksheet. Part 1: Average returns [5 marks = 3 + 2 marks] Compute the monthly return on the shares of each company and the ASX200 index by using the following formula: R _Pr_Pr1 wrn?X 100, {1 where P. is the share price (or the value of ASX200 index) in time period t . (Please note that you lose the first observation when calculating returns.) (a) Using the monthly returns, calculate the average return on shares of each company from February 2015 December 2017. Compare them with the average return on the ASX200 index. (b) Suppose an investor created an equally weighted portfolio of these ve companies (i.e. invested 20% of funds in each company). What would be the average return on the portfolio? Explain how you derived your answer. Part 2: Volatility [9 Marks = 2 + 2 + 5 marks] (a) Calculate the standard deviation of returns for the five companies. What does the standard deviation tell us about the overall risk of these companies? (1)) Compute the standard deviation of the equally weighted portfolio of these five shares. How does it compare to the standard deviation of the individual companies? (c) How does the standard deviation of returns of the ASXZOO index compare with that of individual shares and the equally weighted portfolio? Provide a brief explanation