6. Use the data in the Tutorial 4.xslx spreadsheet on Moodle. It contains the returns for BHP, NXT and CSL, the market (RM) and the risk-free asset (RF).

a) Estimate the alpha, beta and idiosyncratic risk for BHP and NXT. Discuss and compare the differences between the two stocks.

b) Estimate the betas for BHP, NXT and CSL for two different time periods: Jan 2014 to Dec 2017 and Jan 2018 to Feb 2021. (Hint: use the SLOPE function in Excel). How stable are the betas for each stock over these sub-periods? Discuss with reference to the companies and the economy.

c) Based on your analysis, which stock would you expect to perform the best during an economic downturn?

A B D E F BHP NXT 1 Date 2 31/1/14 -0.0344 0.01853 3 28/2/14 0.05227 0.02726 4 31/3/14 -0.047 -0.1239 5 30/4/14 0.03809 -0.0632 6 30/5/14 -0.0168 0.05125 7 30/6/14 -0.0272 -0.1256 8 31/7/14 0.08072 -0.0821 9 29/8/14 -0.0493 0.18844 10 30/9/14 -0.0734 -0.129 11 31/10/14 0.00675 0.22832 12 28/11/14 -0.0862 -0.0251 13 31/12/14 -0.0457 -0.0309 14 30/1/15 0.00083 0.03724 15 27/2/15 0.1543 0.29225 16 31/3/15 -0.074 0.01987 17 30/4/15 0.03469 -0.0662 18 29/5/15 -0.0056 0 19 30/6/15 -0.0817 -0.0083 20 31/7/15 -0.0164 0.07982 21 31/8/15 -0.0426 -0.0817 22 30/9/15 -0.1126 0 23 30/10/15 0.03996 0.09327 24 30/11/15 -0.2108 -0.082 25 31/12/15 -0.0073 0.03003 26 29/1/16 -0.1356 0.04164 27 29/2/16 0.01997 0.02002 28 31/3/16 0.08889 0.07845 29 29/4/16 0.23248 0.04724 30 31/5/16 -0.0729 0.20142 31 30/6/16 -0.0177 0.01156 32 29/7/16 0.05137 0.06854 33 31/8/16 0.04996 0.06683 34 30/9/16 0.09774 0.06693 35 31/10/16 0.03344 -0.128 36 30/11/16 0.06074 -0.1413 37 30/12/16 0.02909 0.1519 38 31/1/17 0.0655 -0.1538 39 28/2/17 -0.0596 0.20452 40 31/3/17 -0.0359 0.09974 41 28/4/17 -0.0111 0.0196 42 31/5/17 0.01028 0.07213 43 30/6/17 -0.0234 0.01119 44 31/7/17 0.11418 -0.0887 45 31/8/17 0.063 0.11437 46 29/9/17 -0.0554 -0.0481 47 31/10/17 0.03297 0.18348 48 30/11/17 0.03198 0.12793 49 29/12/17 0.08645 0.03092 50 31/1/18 0.02443 -0.05 51 28/2/18 0.01278 0.17017 52 30/3/18 -0.0718 -0.0195 53 30/4/18 0.10071 0.05351 54 31/5/18 0.0629 0.08707 55 29/6/18 0.03719 0.00935 CSL 0.02036 0.02893 -0.0353 -0.0148 0.03302 -0.0559 0.02033 0.09106 0.0056 0.08295 0.03106 0.05127 0.01604 0.05005 -3E-05 -0.0111 0.02898 -0.074 0.14597 -0.0704 -0.0275 0.05315 0.06892 0.05351 -0.0111 -0.011 -0.011 0.03827 0.10201 -0.0297 0.05315 -0.0827 -0.0088 -0.0594 -0.0236 0.02611 0.11994 0.0507 0.0647 0.0587 -0.0235 0.06873 -0.0862 0.02123 0.04345 0.03822 0.03281 -0.0132 0.03735 0.11545 -0.0463 0.0969 0.09197 0.03727 RM RF -0.0251 0.00219 0.04395 0.00218 0.00282 0.00222 0.01836 0.00223 0.00435 0.00225 -0.0141 0.00224 0.04752 0.00221 0.00239 0.00219 -0.0549 0.00226 0.04871 0.00228 -0.0354 0.00228 0.0215 0.00232 0.0356 0.00212 0.0649 0.00193 -0.0034 0.00184 -0.0127 0.00188 0.00227 0.00178 -0.0512 0.00178 0.04778 0.00179 -0.0805 0.00179 -0.0288 0.00183 0.048 0.00175 -0.0094 0.00188 0.03003 0.00198 -0.0498 0.0019 -0.0193 0.0019 0.0449 0.0019 0.03617 0.00179 0.02641 0.00165 -0.0212 0.00163 0.06455 0.00155 -0.0192 0.00144 0.00252 0.00145 -0.0175 0.00145 0.02527 0.00147 0.04392 0.00151 -0.0044 0.00148 0.01998 0.00148 0.02933 0.00149 0.0119 0.00145 -0.0289 0.00144 0.00221 0.00143 0.00526 0.00141 0.00291 0.00143 -0.0036 0.00143 0.04349 0.00141 0.01531 0.00145 0.01997 0.0015 -0.001 0.00146 -0.003 0.0015 -0.0388 0.0017 0.04099 0.00169 0.01033 0.00165 0.03287 0.00177 A B D E F 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 31/7/18 0.03115 -0.0463 0.02155 0.01834 0.00165 31/8/18 -0.0434 -0.0166 0.15717 0.01076 0.00162 28/9/18 0.04677 -0.0832 -0.1145 -0.0135 0.00162 31/10/18 -0.0658 -0.0969 -0.0642 -0.0581 0.00159 30/11/18 -0.043 0.09711 -0.0554 -0.0239 0.00162 31/12/18 0.12001 -0.0513 0.04481 4.4E-05 0.00174 31/1/19 0.02209 0.12112 0.05273 0.04256 0.00173 28/2/19 0.07271 -0.0891 -0.004 0.05484 0.00156 29/3/19 0.03763 0.01122 0.00712 0.00374 0.00148 30/4/19 -0.0247 0.01267 0.01971 0.02631 0.0013 31/5/19 0.01366 0.02819 0.03597 0.01469 0.00118 28/6/19 0.09363 -0.0122 0.04723 0.03887 0.001 31/7/19 -0.0061 0.06011 0.06905 0.03039 0.00085 30/8/19 -0.1058 -0.1279 0.04999 -0.0274 0.00082 30/9/19 0.01514 0.02668 -0.0289 0.01813 0.00079 31/10/19 -0.0185 0.04063 0.09673 -0.0016 0.00078 29/11/19 0.06573 0.0312 0.10817 0.02827 0.00074 31/12/19 0.02027 -0.0045 -0.0264 -0.0201 0.00078 31/1/20 0.01458 0.1535 0.13257 0.05227 0.00073 28/2/20 -0.1455 0.03953 -0.0077 -0.0818 0.00067 31/3/20 -0.135 0.13054 -0.0404 -0.2126 0.0003 30/4/20 0.11935 -0.0056 0.04381 0.09595 8.2 E-05 29/5/20 0.07345 0.04059 -0.1067 0.04846 7.9E-05 30/6/20 0.0365 0.07042 0.03991 0.02792 8.4E-05 31/7/20 0.02842 0.15284 -0.058 0.01057 8.3E-05 31/8/20 0.03478 0.07112 0.05976 0.03019 7.5E-05 30/9/20 -0.0572 0.00655 0.00432 -0.0371 6.7E-05 30/10/20 -0.0472 0.03745 0.0028 0.02419 4.8E-05 30/11/20 0.1314 -0.1169 0.03495 0.10006 1.7E-05 31/12/20 0.11863 0.0871 -0.0469 0.01718 8.3E-06 29/1/21 0.02984 -0.0515 -0.0396 0.00697 8.3E-06 26/2/21 0.13139 -0.0345 -0.0328 0.01288 2.5E-05 A B D E F BHP NXT 1 Date 2 31/1/14 -0.0344 0.01853 3 28/2/14 0.05227 0.02726 4 31/3/14 -0.047 -0.1239 5 30/4/14 0.03809 -0.0632 6 30/5/14 -0.0168 0.05125 7 30/6/14 -0.0272 -0.1256 8 31/7/14 0.08072 -0.0821 9 29/8/14 -0.0493 0.18844 10 30/9/14 -0.0734 -0.129 11 31/10/14 0.00675 0.22832 12 28/11/14 -0.0862 -0.0251 13 31/12/14 -0.0457 -0.0309 14 30/1/15 0.00083 0.03724 15 27/2/15 0.1543 0.29225 16 31/3/15 -0.074 0.01987 17 30/4/15 0.03469 -0.0662 18 29/5/15 -0.0056 0 19 30/6/15 -0.0817 -0.0083 20 31/7/15 -0.0164 0.07982 21 31/8/15 -0.0426 -0.0817 22 30/9/15 -0.1126 0 23 30/10/15 0.03996 0.09327 24 30/11/15 -0.2108 -0.082 25 31/12/15 -0.0073 0.03003 26 29/1/16 -0.1356 0.04164 27 29/2/16 0.01997 0.02002 28 31/3/16 0.08889 0.07845 29 29/4/16 0.23248 0.04724 30 31/5/16 -0.0729 0.20142 31 30/6/16 -0.0177 0.01156 32 29/7/16 0.05137 0.06854 33 31/8/16 0.04996 0.06683 34 30/9/16 0.09774 0.06693 35 31/10/16 0.03344 -0.128 36 30/11/16 0.06074 -0.1413 37 30/12/16 0.02909 0.1519 38 31/1/17 0.0655 -0.1538 39 28/2/17 -0.0596 0.20452 40 31/3/17 -0.0359 0.09974 41 28/4/17 -0.0111 0.0196 42 31/5/17 0.01028 0.07213 43 30/6/17 -0.0234 0.01119 44 31/7/17 0.11418 -0.0887 45 31/8/17 0.063 0.11437 46 29/9/17 -0.0554 -0.0481 47 31/10/17 0.03297 0.18348 48 30/11/17 0.03198 0.12793 49 29/12/17 0.08645 0.03092 50 31/1/18 0.02443 -0.05 51 28/2/18 0.01278 0.17017 52 30/3/18 -0.0718 -0.0195 53 30/4/18 0.10071 0.05351 54 31/5/18 0.0629 0.08707 55 29/6/18 0.03719 0.00935 CSL 0.02036 0.02893 -0.0353 -0.0148 0.03302 -0.0559 0.02033 0.09106 0.0056 0.08295 0.03106 0.05127 0.01604 0.05005 -3E-05 -0.0111 0.02898 -0.074 0.14597 -0.0704 -0.0275 0.05315 0.06892 0.05351 -0.0111 -0.011 -0.011 0.03827 0.10201 -0.0297 0.05315 -0.0827 -0.0088 -0.0594 -0.0236 0.02611 0.11994 0.0507 0.0647 0.0587 -0.0235 0.06873 -0.0862 0.02123 0.04345 0.03822 0.03281 -0.0132 0.03735 0.11545 -0.0463 0.0969 0.09197 0.03727 RM RF -0.0251 0.00219 0.04395 0.00218 0.00282 0.00222 0.01836 0.00223 0.00435 0.00225 -0.0141 0.00224 0.04752 0.00221 0.00239 0.00219 -0.0549 0.00226 0.04871 0.00228 -0.0354 0.00228 0.0215 0.00232 0.0356 0.00212 0.0649 0.00193 -0.0034 0.00184 -0.0127 0.00188 0.00227 0.00178 -0.0512 0.00178 0.04778 0.00179 -0.0805 0.00179 -0.0288 0.00183 0.048 0.00175 -0.0094 0.00188 0.03003 0.00198 -0.0498 0.0019 -0.0193 0.0019 0.0449 0.0019 0.03617 0.00179 0.02641 0.00165 -0.0212 0.00163 0.06455 0.00155 -0.0192 0.00144 0.00252 0.00145 -0.0175 0.00145 0.02527 0.00147 0.04392 0.00151 -0.0044 0.00148 0.01998 0.00148 0.02933 0.00149 0.0119 0.00145 -0.0289 0.00144 0.00221 0.00143 0.00526 0.00141 0.00291 0.00143 -0.0036 0.00143 0.04349 0.00141 0.01531 0.00145 0.01997 0.0015 -0.001 0.00146 -0.003 0.0015 -0.0388 0.0017 0.04099 0.00169 0.01033 0.00165 0.03287 0.00177 A B D E F 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 31/7/18 0.03115 -0.0463 0.02155 0.01834 0.00165 31/8/18 -0.0434 -0.0166 0.15717 0.01076 0.00162 28/9/18 0.04677 -0.0832 -0.1145 -0.0135 0.00162 31/10/18 -0.0658 -0.0969 -0.0642 -0.0581 0.00159 30/11/18 -0.043 0.09711 -0.0554 -0.0239 0.00162 31/12/18 0.12001 -0.0513 0.04481 4.4E-05 0.00174 31/1/19 0.02209 0.12112 0.05273 0.04256 0.00173 28/2/19 0.07271 -0.0891 -0.004 0.05484 0.00156 29/3/19 0.03763 0.01122 0.00712 0.00374 0.00148 30/4/19 -0.0247 0.01267 0.01971 0.02631 0.0013 31/5/19 0.01366 0.02819 0.03597 0.01469 0.00118 28/6/19 0.09363 -0.0122 0.04723 0.03887 0.001 31/7/19 -0.0061 0.06011 0.06905 0.03039 0.00085 30/8/19 -0.1058 -0.1279 0.04999 -0.0274 0.00082 30/9/19 0.01514 0.02668 -0.0289 0.01813 0.00079 31/10/19 -0.0185 0.04063 0.09673 -0.0016 0.00078 29/11/19 0.06573 0.0312 0.10817 0.02827 0.00074 31/12/19 0.02027 -0.0045 -0.0264 -0.0201 0.00078 31/1/20 0.01458 0.1535 0.13257 0.05227 0.00073 28/2/20 -0.1455 0.03953 -0.0077 -0.0818 0.00067 31/3/20 -0.135 0.13054 -0.0404 -0.2126 0.0003 30/4/20 0.11935 -0.0056 0.04381 0.09595 8.2 E-05 29/5/20 0.07345 0.04059 -0.1067 0.04846 7.9E-05 30/6/20 0.0365 0.07042 0.03991 0.02792 8.4E-05 31/7/20 0.02842 0.15284 -0.058 0.01057 8.3E-05 31/8/20 0.03478 0.07112 0.05976 0.03019 7.5E-05 30/9/20 -0.0572 0.00655 0.00432 -0.0371 6.7E-05 30/10/20 -0.0472 0.03745 0.0028 0.02419 4.8E-05 30/11/20 0.1314 -0.1169 0.03495 0.10006 1.7E-05 31/12/20 0.11863 0.0871 -0.0469 0.01718 8.3E-06 29/1/21 0.02984 -0.0515 -0.0396 0.00697 8.3E-06 26/2/21 0.13139 -0.0345 -0.0328 0.01288 2.5E-05