Case Study 3 ( Risk and Return ) : ( 15%6 ) Collect the monthly prices of stocks of FMG Led and monthly price's of S& P / ASX 200 ( " AXJOY over the period 1 April 2013 to 31 March 2018 . Using the price data , perform the following calculations and SUBEESt on the suitability of investing in stocks of FMG LEd . 1 . Monthly returns from stacks of FMVIG Led 2. Yearly returns from stacks of FMVIG Led Average yearly returns over the five years from stacks of FMVIGLEd " to Monthly returns from " AXIO 5 Yearly returns from " AXIO Average yearly returns over the five years from " ANJO Total risk associated to investment in FMVIG Led Iperform one calculation using GO monthly returns! B Total risk associated to investment in " A`JO (perform one calculation using GO monthly* returns ! 4 Systematic risk of stocks of FMVIG Led I perform one calculation using 60 monthly returns ! 10 . Unsystematic risk of stocks of FMVIG Led ( perform one calculation using 60 monthly returns !Guidelines for risk and return calculations 1. How can we calculate monthly stock returns? RE =- Pe - Pi-1 PE-1 (1) Where, Rt = Return in month t Pr = Price of the stock (BKL.AX) in the current month (month t) Pr-1= Price of the stock (BKL.AX) in the previous month (month t-1) 2. How can we calculate yearly stock returns from the monthly stock returns? Please follow the steps below: Step 1: Add 1 to the monthly stock returns Step 2: Use the product function in Excel [i.e., = PRODUCT {select the 12 monthly observations of (1+ monthly stock returns) in a year} ] Step 3: Subtract 1 from the product (I have combined step 2 and step 3 in my calculations) 3. How can we calculate the average yearly stock return from the yearly stock returns over the five years? Please calculate the geometric mean of the five yearly returns using the following process: Step 1: Add 1 to the yearly stock returns Step 2: Use the geometric mean function in Excel [i.e., = GEOMEAN {select the 5 yearly observations of (1+ yearly stock returns) } ] Step 3: Subtract 1 from the GEOMEAN (I have combined step 2 and step 3 in my calculations) 4. How can we calculate monthly market returns? RE = Pt - PE-1 Pt-1 (1) Where, Rt = Return in month t Pr = Price of Australian all ordinaries (^AORD) in the current month (month t) Pr-1= Price of Australian all ordinaries (^AORD) in the previous month (month t-1)5. How can we calculate Yearly market returns from the monthly market returns" Please follow the steps below ! Step 1 : Add I to the monthly market returns Step 2 : Use the product function in Excel [L.C., = PRODUCT / select the 12 monthly observations of ! !' + monthly market returns ) in a year ! ! Step 3 : Subtract I from the product !'I have combined step 2 and step } in my calculations* 6 . How can we calculate the average yearly market return from the yearly market returns over the five years?" Please calculate the geometric mean of the five yearly returns using the following PROCESS :" Step 1 : Add I to the yearly market returns Step 2 : Use the geometric mean function in Excel [i.e . = GEOMEAN ( select the } yearly observations of ! !' + yearly, market returns ! ! ] Step 3 : Subtract I from the GEOMEAN IT have combined step ?' and step 3 in my calculations ) 7 . How can we calculate the fatal risk associated to investment in the stacks of HKL.`` using the all monthly stock returns over 5 years?" Please use the Excel formula [_ STDEY. S iselect the data range for all monthly' stock returns !] 8. How can we calculate the fatal risk associated to investment in the market ( " ALORDY using the Gill monthly market returns over 5 years?" Please use the Excel formula [ - STDEY'. S /select the data range for all monthly market returns' ! ! 9. How can we calculate the systematic risk of stacks of HKL. AX ? It involves one calculation using simultaneously the GO monthly returns of AKL. A* and 60 monthly returns of" AORL Steps in Excel . Data_ Analysis_ Regression ( if you do not find analysis tab under Data , add the analysis tool pack from options ! 11 . Select the range of stack return as'I' inputs iji . Select the range of market returns as* inputs* IV . Tick the label box V . Select a cell for the output range and then click OK Vi. The coefficient of the market return is the systematic risk / commonly known as betaj . Please ignore the other statistics .SUMMARY OUTPUT Regression Statistics Multiple R 0.2061 R Squan 0.0425 Adjusted R Square 0.0260 Standard Error 0.0469 Observations 60 ANOVA MS F Significance F Regression 1 0.0057 0.0057 2.5733 0.1141 Residual 58 0.1278 0.0022 Total 0.1335 Coefficients Standard Error : Star P-value Lower 95%% Upper 95% Intercept 0.0154 0.0061 2.5386 0.0138 0.0033 0.0276 Market return 0.2651 0.1652 1.6041 0.1141 -0.0657 0.5958 10. How can we calculate the unsystematic risk of stocks of BKL.AX? Step 1: Use the following model to calculate the daily fitted returns (or forecasted returns) of the stocks of BKL.AX E(R.) = a+BR, Where, a = Intercept B = Coefficient of X Variable 1 (i.e., beta) Rm = Daily market return Step 2: Calculate the residuals (i.e., actual return minus expected return) for every month. E=R - E(R. Step 3: Calculate the yearly standard deviation of the daily residuals. We can define this as the unsystematic risk.Home\\ \\DENE \\VIEW to cut - To Wrap Text Number [ Auto Sum* Calibri ( Body';` [` COPY *\\ * Fill * B = = =\\\\ HE HE * *\\ Merge & Center *\\ Conditional\\ Format Cell Insert* Delete* Format Sort & {} Format Formatting as Table Styles Q Clear * Filter D12 fix = 1 + ( 12 Date Stock price| Monthly Stock returns | 1 + Monthly stock returns\\ Yearly stack returns 1 + Yearly stock returns Average yearly stock returns \\Market price\\\\ \\ Monthly market returns* 1 + Monthly market returns \\ Yearly market returns| 1 + Yearly market* 30. 20 5327 . 00 Jul - 11 31.29 0. 036 : 1. 036: 0. 17 7 7 5310. 40 2. 996 ! AUE - 11 29.56 0 . 0554 0.9441 5447 . 80 2. 0259 Sep - 11 31.97 5316.00 2. 0242 2. 9751 Oct - 11 30. 76 0. 9520 5151. 80 0.9591 Now - 11 31.94 4947 .50 - 0.0390 2. 960* Dec - 11 32.88 0.029: 5056. 50 0. 0220 1 . 0220 33.40 Q. DIED 1. DIED 534 4. 50 2.0570 1.0570 FEG - 12 37.85 0. 013 0 1. 0130 5218. 20 2.9763 Mar - 12 35. 12 0 . 037 : 1. 0373 52 8 8. 50 0 . 0135 Apr - 12 33.38 - 0.0495 0.9505 5058. 50 - 0. 04]` May - 12 35. 88 0.07 45 5722.10 0.0323 Jun - 12 38.8 1. 08:3 4 0.2874 1 . 2 BTA 5681. 70 1. Dead Q . DOGG Jul - 12 38.91 0.0.005 5451. 20 - D. DA`` 2.9591 AUE - 12 39. 64 57 74.50 0. 059` 1.059* Sep - 12 $ 1.05 1. 0355 5773.70 0. 03 3 0 1. 03.30 5851. 50 0. 0153 Nov - 12 $4.20 0.041; 1 . 0417 58.58.50 Get - 12 45. 81 2. 0365 1. 0365 555 1.50 Jan - 13\\ 49. 56 Q. OBIB 1 . 0BIB 538.8.50 0. 029^ 1.9705 Feb0 - 13 30. 10 0. 0110 52.54. 10 -0.DIGE 0.983` Mar - 13 54. 11 Q. OBOD 5:505. 00 0.0391 1.0391 APT - 13 49. 25 - 0.089 1.9103 5296 . 8% - 0. 037{ May - 13. 50. 96 0.0347 1.03.47 5624.50 2.0615 Jun - 13 0.0727 1. 072 : 1.4063 56 23. 10 0.00 013 2.959; - 0. 0103 55. 85 0.0215 1. 021 0 53 8 2. 00 - 0. 04 25 AWE - 13 54.60 0.0274 0.97 76 5473. 80 Q. DIT ! 1. 0171 5. EN - 13\\ 58. 73 1. 0.584 5470. 80 0.0DOS 2.9595 59.67 0.0275 1. 0229 5403. 00 0.9871 Nov - 13 59.65 0.00 03 $415. 40 DEL - 13 56. 91 - 0 . 045 0. 9541 5205. 10 - 0. 038% 59. 30 0.041` 1. 04 18 5353.10 2.02.84 1. 0284 Feb . 1 4 $1.50 0.0372 5314. 30 0.007 2 0.9926 Mar - 14 62 . 6 6 0. 018 ! 1. 0185 5420. 30 0.0195 ART - 14 64. 80 1.0341 5217. 70 - 0. 037` 2. 962 0 May - 14 64 . 24 - 0.ODB 0.9913 5125. 30 Jun - 1 4 0.0355 0 . 2167 1 .2167 - 0.0175 Jul - 14 67 . 17 2.948: AUG . 14 62.19 - 0 . 074 ; 0.925 8 4914 .00 0. 0290 56. 47 0.0.58.5 1. 068.5 5168. 50 1. 0518 Oct . 14 1.0 03 0 1.0030 $975. 50 0. 0365 1.9635 Nav - 14 70.75 0.0.51 1 1. 051 1 5120.40 0.0282 1. 028* " Mean` Return_ total risk\\ Systematic_ unsystematic_ risk.\\ +An example of risk and return calculations helpful for Case Study 3 (2) Q ~ Search Sheet Page Layout Formulas Data 36 Cut Calibri (Body) TO Wrap Text General 2 AutoSum Copy Format Merge & Center $ Cell Delete Format Sort & Formatting as Table Styles Filter :35 fx Date Monthly stock returns Monthly market returns SUMMARY OUTPUT Beta Fitted value of stock returns Resid 31/07/2011 0.0363 0.0154 0.2651 0.0146 0.021 31/08/2011 Regression Statistics 0.0154 0.022 -0.07 30/09/2011 Multiple R 0.2651 0.0090 0.072 31/10/2011 0.0380 0.0309 R Square 0.2651 0.0072 -0.04 30/11/2011 0.0384 0.0396 Adjusted R Square 0.0260 0.0154 0.2651 0.004 0.033 0.0220 0.0154 0.0212 0.008 31/01/2012 0.0570 0.0154 0.2651 0.0305 -0.01 29/02/2012 0.013 0.0237 0.0154 0.2651 0.009 0.004 0.0373 0.0135 NOVA 0.0154 0.018 30/04/2012 0.0435 MS Significance F 0.0154 0.2651 0.0039 -0.05 SS 31/05/2012 0.0749 Regression 0.005: 0.0057 2.5733 0.1141 0.0154 0.2651 0.0240 0.050 30/06/2012 0.0834 0.0880 Residual 0.1278 0.0022 0.0154 0.2651 0.0387 0.044 31/07/2012 0.1335 0.0154 0.2651 -0.003 31/08/2012 0.0181 0.0594 0.0154 0.2651 0.031: -0.01 Standard Error t Stat P-value Lower 95%% Upper 95% 0.0154 0.2651 0.020 0.0336 Intercept 0.0154 0.0061 2.5386 0.0138 0.0276 0.0154 0.2651 0.0195 0.01 30/11/2012 0.0062 Monthly market return: 0.2651 0.1652 1.6041 0.1141 -0.0657 0.5958 0.0154 0.2651 0.0171 0.024 0.0154 0.2651 -0.000 0.036 0.0818 0.0294 Beta (i.e., systematic or market risk) 0.2651 0.0154 0.2651 0.074 28/02/2013 0.0110 0.0168 0.0154 0.2651 0.011 0.00 0.0800 0.0391 0.0154 0.2651 0.0258 0.054 30/04/2013 0.0378 0.0154 0.2651 0.0054 -0.09 0.0619 0.0154 0.2651 0.031 0.002 30/06/2013 0.0003 0.0154 0.2651 0.0153 0.05 31/07/2013 0.0216 0.0429 0.0154 0.2651 0.004 0.01 31/08/2013 0.0224 0.0171 0.0154 0.2651 0.0199 -0.04 0.0154 0.2651 0.053 31/10/2013 0.022 0.0154 0.012: 0.010 30/11/2013 0.0003 0.0023 0.0154 0.2651 0.0160 -0.01 31/12/2013 0.045! 0.0388 0.0154 0.2651 0.005 -0.05. 0.0418 0.0154 0.0229 0.018 28/02/2014 0.0372 0.0154 0.2651 0.0135 0.023 31/03/2014 0.0189 0.0199 0.0154 0.2651 0.020 30/04/2014 0.0154 0.2651 0.0055 0.028 31/05/2014 0.008 0.0154 0.019 0.0355 0.2651 0.0108 0.024 31/07/2014 0.2651 0.0017 0.008 0.0742 0.0154 0.2651 0.023 -0.09 0.0154 0.2651 0.039 0.0154 0.2651 0.005 -0.00 30/11/2014 0.0611 0.0282 0.2651 0.022 31/12/2014 0.0430 0.0428 0.0154 0.2651 0.0041 0.03 Return_total risk Systematic_unsystematic_risk +