Historical Risk and Return Case 1. Use the data presented in the power point that I have posted (historical returns over 1926- 2015) to analyze risk-return as follows: a. First enter the return data into an excel sheet. Start with Small Company. b. Calculate average return using both geometric and arithmetic means. C. Calculate variance and standard deviation (again, using both geometric and arithmetic means) as we explained in class. Also see the Excel sheet that I have posted. d. Follow the same steps above for Large Company, Long-term US Bonds, U.S. T-Bills, and Inflation. In that order! 2. The step above will result in 5 tables. Now create a new table that shows the following: a. Asset type (small company, large company, ... etc.) b. Arithmetic and geometric averages C. Highest and lowest return and the spread between the two. d. Variance and standard deviations based on arithmetic mean. e. Variance and standard deviations based on geometric mean. Put EVERYTHING in an Excel sheet i.e. first type the raw data materials THEN do the calculations to produce output. I need to see formulas NOT typed data. 3. Produce a Word document that explains what you have done in the Excel sheet. That is, in strictly one page no more and no less), explain to a potential client what history says about the relationship between returns and risk. Use 12-inch font and double space. The Historical Record: Total Returns on Small-Company Stocks 200 150 100 Total annual returns (in percent) 50 Whenti W -50 -100 1926 1936 1946 1956 1986 1996 2006 2016 1966 1976 Year-end The Historical Record: Total Returns on Large-Company Stocks Total annual returns in percent) -20 -60 1926 1936 1946 1956 1986 1996 2006 2016 1966 1976 Year-end The Historical Record: Total Returns on Long-term U.S. Bonds 50 50 40 30 20 Total annual returns in percent) 10 LLL 10 -20 -30 1926 1936 1946 1956 1986 1996 2006 2016 1966 1976 Year-end The Historical Record: Total Returns on U.S.T-bills 16 14 12 Total annual returns in percent) 1926 1936 1946 1956 1986 1996 2006 2016 1966 1976 Year-end The Historical Record: Inflation 20 15 10 5 Annual inflation in percent) -5 -10 -15 1926 1936 1946 1956 1986 1996 2006 2016 1966 1976 Year-end Geometric versus Arithmetic Averages, 19262015 Geometric Mean Arithmetic Mean Series Standard Deviation Large-company stocks 9.9% 119% 20.0% 11.9 17.5 36.3 Small-company Stocks Long-term corporate bonds 6.3 6.5 70 Long-term government bonds 5.5 6.2 12.5 5.3 5.5 81 Intermediate-term government bonds U.S. Treasury bills 3.5 36 3.2 Inflation 2 2.9 3.0 4.1 Historical Returns, Standard Deviations, and Frequency Distributions: 19262015 Series Average Return Standard Deviation Frequency Distribution Large company stocks 20.0% Small-company stock 17.5% 36.3% Long-term corporate bonds ( 6.5% 7.0% L Long-term U.S. Government bonds 6.26 12.5% Intermediate-term U.S. government bonds 5.5% 8.1% U.S. Treasury bills 3,6% 3.2% ( Inflation 3.0% L -9096 O'M 90% Historical Risk and Return Case 1. Use the data presented in the power point that I have posted (historical returns over 1926- 2015) to analyze risk-return as follows: a. First enter the return data into an excel sheet. Start with Small Company. b. Calculate average return using both geometric and arithmetic means. C. Calculate variance and standard deviation (again, using both geometric and arithmetic means) as we explained in class. Also see the Excel sheet that I have posted. d. Follow the same steps above for Large Company, Long-term US Bonds, U.S. T-Bills, and Inflation. In that order! 2. The step above will result in 5 tables. Now create a new table that shows the following: a. Asset type (small company, large company, ... etc.) b. Arithmetic and geometric averages C. Highest and lowest return and the spread between the two. d. Variance and standard deviations based on arithmetic mean. e. Variance and standard deviations based on geometric mean. Put EVERYTHING in an Excel sheet i.e. first type the raw data materials THEN do the calculations to produce output. I need to see formulas NOT typed data. 3. Produce a Word document that explains what you have done in the Excel sheet. That is, in strictly one page no more and no less), explain to a potential client what history says about the relationship between returns and risk. Use 12-inch font and double space. The Historical Record: Total Returns on Small-Company Stocks 200 150 100 Total annual returns (in percent) 50 Whenti W -50 -100 1926 1936 1946 1956 1986 1996 2006 2016 1966 1976 Year-end The Historical Record: Total Returns on Large-Company Stocks Total annual returns in percent) -20 -60 1926 1936 1946 1956 1986 1996 2006 2016 1966 1976 Year-end The Historical Record: Total Returns on Long-term U.S. Bonds 50 50 40 30 20 Total annual returns in percent) 10 LLL 10 -20 -30 1926 1936 1946 1956 1986 1996 2006 2016 1966 1976 Year-end The Historical Record: Total Returns on U.S.T-bills 16 14 12 Total annual returns in percent) 1926 1936 1946 1956 1986 1996 2006 2016 1966 1976 Year-end The Historical Record: Inflation 20 15 10 5 Annual inflation in percent) -5 -10 -15 1926 1936 1946 1956 1986 1996 2006 2016 1966 1976 Year-end Geometric versus Arithmetic Averages, 19262015 Geometric Mean Arithmetic Mean Series Standard Deviation Large-company stocks 9.9% 119% 20.0% 11.9 17.5 36.3 Small-company Stocks Long-term corporate bonds 6.3 6.5 70 Long-term government bonds 5.5 6.2 12.5 5.3 5.5 81 Intermediate-term government bonds U.S. Treasury bills 3.5 36 3.2 Inflation 2 2.9 3.0 4.1 Historical Returns, Standard Deviations, and Frequency Distributions: 19262015 Series Average Return Standard Deviation Frequency Distribution Large company stocks 20.0% Small-company stock 17.5% 36.3% Long-term corporate bonds ( 6.5% 7.0% L Long-term U.S. Government bonds 6.26 12.5% Intermediate-term U.S. government bonds 5.5% 8.1% U.S. Treasury bills 3,6% 3.2% ( Inflation 3.0% L -9096 O'M 90%