Note: Use the data in the Excel work sheet named Mini Assignment 3_data for this assignment. Using the data in Sheet 1 of the Excel data sheet, estimate the index model, = + ( ) + for the three stocks AMBERTECH, AGL ENERGY and JB HI-FI. You need to use monthly return observations for the above companies and the market (All ordinaries index) together with the risk-free rate (three-month bank bill rate) for the period from 01 January 2017 to 01 December 2019 to estimate the index models.

1.Report the following for each stock: alpha, beta and correlation with the market.

2.Which stock has the highest sensitivity to the movements in the market?

3.Which stock has the lowest sensitivity to the movements in the market?

4.What proportion of the variation in the return of each stock is explained by the movements in the market?

5.Calculate the market risk and the unique risk for each stock, and show that the sum of these two is equal to the variance of the stock.

6.Assume that you create a portfolio with the following weights: 25% in three-month bank bills, 20% in AMBERTECH, 30% in AGL ENERGY and 25% in JB HI-FI. i.What is the beta of your portfolio? ii.What is the market risk of your portfolio? iii.What is the unique risk of your portfolio?

7.Using the data in sheet 2 of the Excel data sheet, calculate the following on a monthly basis for your portfolio from January to June 2017: actual return, expected return, abnormal return.

8.Based on your calculations in (7) above, explain whether your portfolio has outperformed or underperformed the market during the six-month period ending in June 2017.

Date Price-Am Price-AG Price-JBF Price-All 90 days bank A 2017/1/1 0.16 22.6 27.65 5675 2.61 2017/2/1 0.16 24.12 26.82 5761 2.62 2017/3/1 0.2 26.37 24.7 5903.8 2.63 2017/4/1 0.2 26.76 24.69 5947.6 2.66 2017/5/1 0.2 26.35 23.12 5761.3 2.68 2017/6/1 0.15 25.5 23.37 5764 2.69 2017/7/1 0.13 24.1 26.01 5773.9 2.70 2017/8/1 0.14 23.99 23.13 5776.3 2.65 2017/9/1 0.13 23.37 22.92 5744.9 2.63 2017/10/1 0.14 25.27 22.9 5976.4 2.66 2017/11/1 0.145 24.99 23.61 6023.5 2.72 2017/12/1 0.125 24.36 24.94 6167.3 2.75 2018/1/1 0.125 23.44 29.23 6146.5 2.75 2018/2/1 0.12 21.86 25.95 6117.3 2.70 2018/3/1 0.125 21.68 25.81 5868.9 2.36 2018/4/1 0.14 21.73 25.7 6071.6 2.30 2018/5/1 0.155 22.1 23.99 6123.5 2.25 2018/6/1 0.16 22.48 22.52 6289.7 2.15 2018/7/1 0.155 21.99 23.85 6366.2 2.15 2018/8/1 0.14 20.78 26.4 6427.8 2.14 2018/9/1 0.185 19.5 25.19 6325.5 2.14 2018/10/1 0.145 18 23 5913.3 2.17 2018/11/1 0.145 18.81 23.17 5749.3 2.15 2018/12/1 0.145 20.6 22.14 5709.4 2.22 2019/1/1 0.12 21.42 22.37 5937.3 2.34 2019/2/1 0.12 21.21 21.7 6252.7 2.30 2019/3/1 0.12 21.77 24.95 6261.7 2.28 2019/4/1 0.12 22.23 25.77 6418.4 2.31 2019/5/1 0.1 20.55 28.12 6491.8 2.26 2019/6/1 0.1 20.01 25.85 6699.2 2.01 2019/7/1 0.11 20.97 30.09 6896.7 1.99 2019/8/1 0.1 18.96 32.9 6698.2 1.93 2019/9/1 0.1 19.16 34 6800.6 1.76 2019/10/1 0.11 19.78 37.04 6772.9 1.73 2019/11/1 0.125 20.41 37.41 6948 1.75 2019/12/1 0.09 20.53 37.67 6802.4 1.76 Date Price-Am Price-AG Price-JBF Price-All 90 days bank A 2017/1/1 0.16 22.6 27.65 5675 2.61 2017/2/1 0.16 24.12 26.82 5761 2.62 2017/3/1 0.2 26.37 24.7 5903.8 2.63 2017/4/1 0.2 26.76 24.69 5947.6 2.66 2017/5/1 0.2 26.35 23.12 5761.3 2.68 2017/6/1 0.15 25.5 23.37 5764 2.69 2017/7/1 0.13 24.1 26.01 5773.9 2.70 2017/8/1 0.14 23.99 23.13 5776.3 2.65 2017/9/1 0.13 23.37 22.92 5744.9 2.63 2017/10/1 0.14 25.27 22.9 5976.4 2.66 2017/11/1 0.145 24.99 23.61 6023.5 2.72 2017/12/1 0.125 24.36 24.94 6167.3 2.75 2018/1/1 0.125 23.44 29.23 6146.5 2.75 2018/2/1 0.12 21.86 25.95 6117.3 2.70 2018/3/1 0.125 21.68 25.81 5868.9 2.36 2018/4/1 0.14 21.73 25.7 6071.6 2.30 2018/5/1 0.155 22.1 23.99 6123.5 2.25 2018/6/1 0.16 22.48 22.52 6289.7 2.15 2018/7/1 0.155 21.99 23.85 6366.2 2.15 2018/8/1 0.14 20.78 26.4 6427.8 2.14 2018/9/1 0.185 19.5 25.19 6325.5 2.14 2018/10/1 0.145 18 23 5913.3 2.17 2018/11/1 0.145 18.81 23.17 5749.3 2.15 2018/12/1 0.145 20.6 22.14 5709.4 2.22 2019/1/1 0.12 21.42 22.37 5937.3 2.34 2019/2/1 0.12 21.21 21.7 6252.7 2.30 2019/3/1 0.12 21.77 24.95 6261.7 2.28 2019/4/1 0.12 22.23 25.77 6418.4 2.31 2019/5/1 0.1 20.55 28.12 6491.8 2.26 2019/6/1 0.1 20.01 25.85 6699.2 2.01 2019/7/1 0.11 20.97 30.09 6896.7 1.99 2019/8/1 0.1 18.96 32.9 6698.2 1.93 2019/9/1 0.1 19.16 34 6800.6 1.76 2019/10/1 0.11 19.78 37.04 6772.9 1.73 2019/11/1 0.125 20.41 37.41 6948 1.75 2019/12/1 0.09 20.53 37.67 6802.4 1.76