This question has six parts (a) – (f). Attempt all of them.Refer to worksheet “Data Q2” in the file 2018 FIN205.xlsx to answer this question. This worksheet contains data on the adjusted end of month share price from January 2015 – June 2018 of two companies: AMP Limited (AMP.AX) and Qanta

Question 2
This question has six parts (a) – (f). Attempt all of them.
Refer to worksheet “Data Q2” in the file 2018 FIN205.xlsx to answer this question. This worksheet contains data on the adjusted end of month share price from January 2015 – June 2018 of two companies: AMP Limited (AMP.AX) and Qantas Airways Limited (QAN.AX). You will also find the value of S&P/ASX200 market index over this period. The basic data is provided in columns A – D. You will need to manipulate the data to answer the questions below. Some excel formulas are provided in the worksheet.
Pick the month you were born. For you the relevant data will be month 2015 – June 2018.
a. Suppose you are a financial analyst working for Star Bank. Use an appropriate graph to plot the AMP and Qantas share prices from month 2015 – June 2018. Provide a brief comment on the pattern you observe.
b. Compute the monthly return on AMP, Qantas shares and the ASX200 index by using the following formula:

where,  is the share price (or the value of ASX200 index) in time period . (Please note that you lose the first observation when calculating returns. For instance, if you were born in June, you will only be able to calculate the returns only from July 2015.)
Compute the mean, variance and standard deviation of all three returns. Based on your calculations what can you infer about the performance of AMP, Qantas and ASX200 index over the last three years? Provide a brief explanation.
c. Calculate the beta of AMP and Qantas. For this you will have to first calculate the covariance of AMP and Qantas returns with the ASX index. Use the following formula in excel:

 Beta’s can then be calculated by

d. Briefly explain why AMP and Qantas have high/low values of beta.
e. Assume the monthly risk-free rate to be 0.14%.  According to the Capital Asset Pricing Model (CAPM) are the AMP and Qantas shares are underpriced? Explain.
f. Write a short report for the management of your company evaluating the past performance and future prospects of AMP and Qantas shares