Return Data (weekly )for a sample of companies drawn from NZX

Amanda Nguyen is considering investing in four different two-factor portfolios. She can obtain the weekly returns of securities A, B, C and D for the period from August 2012 to August 2017 (The attached file (Stock returns Aug 2012-Aug 2017) gives 260 weekly returns for four securities along with the returns of the NZX all share index for the period from August  2012 to August 2017). In any of the possible two-factor portfolios, the weight of each security in the portfolio will be 50%. The four possible portfolio combinations are A&B; B&C; A&C; C&D; A&D and B&D.
Required:
a)     Determine, using the appropriate Excel function (see fx) the average weekly return, the standard deviation, and variance for each of the companies. (Use the 260 weekly returns data in the calculations and use the Excel functions identified as "Variance P" and "Standard Deviation P.")
                                                                                                                                                                     (4 marks)
b)     Determine, using the appropriate Excel function the covariance between securities A&B; B&C; A&C; C&D; A&D and B&D. (Use the 260 weekly returns data in the calculations and use the Excel function "COVARIANCE P.")                                                                                                                                    (4 marks)

c)     Calculate using the two-factor portfolio equations, the portfolio returns and risks (both standard deviation and variance) for the following portfolios:
a.     A and B
b.     B and C
c.      A and C
d.     C and D
e.     A and D
f.      B and D                                                                                                                                     (6 marks)

d)       By computing the ratio of () select the best investment that Amanda should undertake, assuming she is risk averse. Explain the rationale that you have used in making this choice.                   (4 marks)

e)     Draw a portfolio graph (showing Risk on the X-axis and Return on the Y-axis) for the investment portfolio that you have recommended in (d) above for a range of investment weights that Amanda could choose from (i.e., Amanda could invest 0% in one company and 100% in the other or 5% in one company and 95% in the other and so on). Determine from the portfolio graph, the minimum risk that Amanda could obtain for this portfolio and the respective weightings that should be invested in each of the securities in the portfolio.                                                                                                           (6 marks)
f)      Determine the betas for Securities A, B, C and D by regressing the returns of each of the companies on the returns for the NZX ALL Share Index (the first column in the spreadsheet). Security A is in construction industry, securities B and D are in Health care sector, and security C is in entertainment sector,  
(Regression Calculation: Go to Data Analysis - far right under Data- and choose regression. If Data Analysis does not appear it must be added, it will be available in Excel. Go to Options under File and choose Add-ins and then Data Analysis the company returns constitute the Y input and the index returns the X input. Alternatively, the "slope" found in f(x) also represents Beta).

a.     Explain what the values of the betas (the slope coefficients in the regression) indicate and discuss the factors that might explain the differences in the values of the betas of the three companies.                                                                                                                                 (8 marks)

b.     Should Amanda decide to invest in all the four companies in equal proportions? Compute the return and risk of the portfolio?                                                                                             (3 marks)