GUIDELINES The format of your report should be professional looking. Make sure to include your name, page number, etc. Keep it concise yet descriptive. Report should be typed and excel file should be sent with your report Before starting your assignment, please read and follow the "case grading rubric" (Appendix II) and make sure you have all the criteria in your report. ANALYSIS OF HISTORICAL DATA You will find historical data (rates of return) for major assets classes from 1927-2016. (Stock Market Index, and T-bills) in the Excel Spreadsheet that provided to you. You need to analysis the following steps: Part A: Calculate the followings statistics for stock return. market index return, and T-bills return during period 1927- 2016 by using the Excel functions. 1. Average return 2. Variance 3. Standard deviation Part B: Calculate the following statistics for stock and market returns each subperiod (1 to 4) and T-bills average return for each period by using the Excel functions. 1. For 1927-2016 period. average return, standard deviation 2. For 1927-1956 period, average return, standard deviation 3. For 1957-1987 period, average return, standard deviation 4. For 1988-2016 period, average return, standard deviation 5. Calculate the T-bills average returns for each period Part C: Calculate the correlation between stock returns and market returns and then calculate the Beta for each period. 1. For 1927-2016 period. correlation, and Beta 2. For 1927-1956 period, correlation, and Beta 3. For 1957-1987 period, correlation, and Beta 4. For 1988-2016 period, correlation, and Beta Part D: Calculate the expected rate of return from CAPM for each period by using the followings 1. Market average return for each period that you calculated in Part B 2. T-bills average return for each period (proxy for the risk free rate) that you calculated in Part B 3. Beta that you calculated for each period in Part C. Part E: Based on your calculation in Part A, B, and C answer the following questions. 1. How stable Beta for each subperiod? Explain? 2. If it does, why expected rates of return from CAPM changes significantly from period to period? Why? Part F: Summary Summarize what you learned from the history based on your analysis? LE ME 10 11 FI LI 16 ( PE w SA 11 NI DI SIS DE IN w w RIE SN NE 21 SH WS II I WE IC TE 1 HE 1135 I FR HE 1841 79 II TE DIE PO PETS 141 113 3 TEET NE I LE IM WS SIT 1 193 NE WE NAI HT 4 NU RE w 14 21 TI INLI IM w 1 LLL 1956 11 -101 51 2 IST W 15 WI W! Ir TE ITH 216 1 DE 111 1.31 H le TIT IN H 1961 169 CE 134 LI SER MI 1 CIR MINDER AS 11 1 GUIDELINES The format of your report should be professional looking. Make sure to include your name, page number, etc. Keep it concise yet descriptive. Report should be typed and excel file should be sent with your report Before starting your assignment, please read and follow the "case grading rubric" (Appendix II) and make sure you have all the criteria in your report. ANALYSIS OF HISTORICAL DATA You will find historical data (rates of return) for major assets classes from 1927-2016. (Stock Market Index, and T-bills) in the Excel Spreadsheet that provided to you. You need to analysis the following steps: Part A: Calculate the followings statistics for stock return. market index return, and T-bills return during period 1927- 2016 by using the Excel functions. 1. Average return 2. Variance 3. Standard deviation Part B: Calculate the following statistics for stock and market returns each subperiod (1 to 4) and T-bills average return for each period by using the Excel functions. 1. For 1927-2016 period. average return, standard deviation 2. For 1927-1956 period, average return, standard deviation 3. For 1957-1987 period, average return, standard deviation 4. For 1988-2016 period, average return, standard deviation 5. Calculate the T-bills average returns for each period Part C: Calculate the correlation between stock returns and market returns and then calculate the Beta for each period. 1. For 1927-2016 period. correlation, and Beta 2. For 1927-1956 period, correlation, and Beta 3. For 1957-1987 period, correlation, and Beta 4. For 1988-2016 period, correlation, and Beta Part D: Calculate the expected rate of return from CAPM for each period by using the followings 1. Market average return for each period that you calculated in Part B 2. T-bills average return for each period (proxy for the risk free rate) that you calculated in Part B 3. Beta that you calculated for each period in Part C. Part E: Based on your calculation in Part A, B, and C answer the following questions. 1. How stable Beta for each subperiod? Explain? 2. If it does, why expected rates of return from CAPM changes significantly from period to period? Why? Part F: Summary Summarize what you learned from the history based on your analysis? LE ME 10 11 FI LI 16 ( PE w SA 11 NI DI SIS DE IN w w RIE SN NE 21 SH WS II I WE IC TE 1 HE 1135 I FR HE 1841 79 II TE DIE PO PETS 141 113 3 TEET NE I LE IM WS SIT 1 193 NE WE NAI HT 4 NU RE w 14 21 TI INLI IM w 1 LLL 1956 11 -101 51 2 IST W 15 WI W! Ir TE ITH 216 1 DE 111 1.31 H le TIT IN H 1961 169 CE 134 LI SER MI 1 CIR MINDER AS 11 1