Is there answers to this TVM_12 STD DEV?

I need to know if there's any solutions manual for this file

thank you in advance

Tutorial 12. Stock Returns and Variance What you will learn: 1. How to estimate stock returns 2. How to estimate stock return standard deviation 3. How to work with historical data Fill out the cells with boxes around them like this => Enter your name in this box ===> Tutorial 12 - Stock Returns and Variance Estimating stock returns, variance and standard deviation. Problem 1: You bought 100 shares of Starbucks Corp. (SBUX) 7 years ago (1Aug '95) for $5.00 per share and sold the 100 shares today for $20.31 each. What are your returns? At the same time your sister bought 100 shares of Coca-Cola (KO). How did your returns compare (excluding brokerage fees)? Assume today is July 1, 2002 and Coke paid cumulative dividends of $4.94. Data: Monthly Closing Prices From March 1995 to July 2002 for Coca-Cola and Starbucks. Date 1-Jul-02 3-Jun-02 1-May-02 1-Apr-02 1-Mar-02 1-Feb-02 2-Jan-02 3-Dec-01 1-Nov-01 1-Oct-01 4-Sep-01 1-Aug-01 2-Jul-01 1-Jun-01 1-May-01 2-Apr-01 1-Mar-01 1-Feb-01 2-Jan-01 1-Dec-00 1-Nov-00 2-Oct-00 1-Sep-00 1-Aug-00 3-Jul-00 1-Jun-00 1-May-00 3-Apr-00 1-Mar-00 1-Feb-00 3-Jan-00 1-Dec-99 1-Nov-99 1-Oct-99 1-Sep-99 2-Aug-99 1-Jul-99 1-Jun-99 3-May-99 1-Apr-99 1-Mar-99 1-Feb-99 4-Jan-99 1-Dec-98 2-Nov-98 1-Oct-98 1-Sep-98 3-Aug-98 1-Jul-98 1-Jun-98 1-May-98 1-Apr-98 2-Mar-98 2-Feb-98 2-Jan-98 1-Dec-97 3-Nov-97 1-Oct-97 2-Sep-97 1-Aug-97 1-Jul-97 2-Jun-97 1-May-97 1-Apr-97 3-Mar-97 3-Feb-97 2-Jan-97 2-Dec-96 1-Nov-96 1-Oct-96 3-Sep-96 1-Aug-96 1-Jul-96 KO 49.43 56.00 55.35 55.30 52.07 47.02 43.41 46.78 46.59 47.33 46.31 48.11 44.08 44.48 46.67 45.48 44.46 52.02 56.90 59.78 61.44 59.06 53.93 51.33 59.79 56.01 51.88 45.93 45.62 47.09 55.62 56.41 65.18 57.00 46.61 57.61 58.34 59.72 65.82 65.40 58.98 61.22 62.60 64.22 67.15 64.62 55.12 62.14 76.81 81.58 74.64 72.26 73.75 65.22 61.54 63.38 59.40 53.70 57.85 54.22 65.39 64.33 64.67 60.07 52.63 57.46 54.52 49.57 48.16 47.45 47.81 46.87 43.94 SBUX 20.31 24.85 24.28 22.82 23.13 23.01 23.77 19.05 17.72 17.12 14.94 16.87 18.04 23.00 19.52 19.35 21.22 23.81 24.97 22.12 22.78 22.34 20.03 18.31 18.75 19.09 17.00 15.12 22.41 17.56 16.00 12.12 13.28 13.59 12.39 11.44 11.62 18.78 18.44 18.47 14.03 13.22 13.02 14.03 11.53 10.84 9.05 7.89 10.47 13.36 12.00 12.03 11.33 9.89 9.14 9.59 8.72 8.25 10.45 10.25 10.23 9.73 7.88 7.47 7.41 8.41 8.56 7.16 8.66 8.12 8.25 8.19 6.50 Step 1: Capital Appreciation A. Price you received today Price you paid Difference * 100 shares In 1995 you spent $500 for 100 shares of SBUX. Today, when you sold them, your capital appreciation is valued at ==> B. At the same time your sister bought 100 shares of Coca Cola. What was her stock's capital appreciation? Price she received today Price she paid Difference * 100 shares Today, when she sold them, her capital appreciation is valued at ==> Step 2: Dividends A. B. Dividends are given. Dividends per share of SBUX * 100 shares Div earnings from SBUX 0 $ Dividends per share of KO * 100 shares Div earnings from KO - 4.94 Step 3: Total Return Percentage for Holding Period A. B. SBUX cost (price X 100) SBUX capital appreciation SBUX dividends SBUX cap. app. + div. Total return = (CA + D) / cost $ $ $ - KO cost (price X 100) KO capital appreciation KO dividends ($4.94 per share) KO cap. app. + div. Total return = (CA + D) / cost $ $ $ - For the 6 years and 11 months you and your sister held your stocks, your stock return was and your sister's was => Step 4: One Year Total Return Percentage A. SBUX price on Jul 02*100 SBUX price on Jul 01*100 SBUX dividends capital appreciation SBUX cap. app. + div. TR = (CA + D) / price Jul01 B. KO price on Jul 02*100 KO price on Jul 01*100 KO dividends (.76 per sh) capital appreciation KO cap. app. + div. TR = (CA + D) / price Jul01 3-Jun-96 1-May-96 1-Apr-96 1-Mar-96 1-Feb-96 2-Jan-96 1-Dec-95 1-Nov-95 2-Oct-95 1-Sep-95 1-Aug-95 45.93 43.01 38.10 38.68 37.63 35.13 34.60 35.30 33.40 32.06 29.75 KO 7.06 6.78 6.78 5.83 4.41 4.19 5.25 5.28 4.91 4.73 5 SBUX Question 1. Which stock did better over the holding period? Question 2. Which stock did better over the last year? Question 3. Are you surprised? Problem 2: The following table shows the historical returns for large company stocks from 1980-1999. Let's find the average return and the standard deviation of the large firm returns. Data: Yearly Historical Returns for U. S. Large Company Stocks 1980-1999. Year 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 Large Company Stocks 32.61% -4.97% 21.67% 22.57% 6.19% 31.85% 18.68% 5.22% 16.58% 31.75% -3.13% 30.53% 7.62% 10.07% 1.27% 37.80% 22.74% 33.43% 28.13% 21.03% Step 1: Average Return Calculate the historical average return for large co. stocks Use the Excel function: =average(cell range)/100 We need to divide by 100 because the values are in per cent. A. In cell J116 enter: = average(C108:C127) Step 2: Return Distribution Calculate the variance and standard deviation of large firm historical returns. Variance is the average of the squared deviations from the mean. We calculate the deviation of each individual return from the mean (average), square those numbers, sum the squares, and then divide by the number of returns minus one. The standard deviation is the square root of the variance. It is in percentage form and is used to make comparisons. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Large Co. Average Year Return Return 1980 32.61% 1981 -4.97% 1982 21.67% 1983 22.57% 1984 6.19% 1985 31.85% 1986 18.68% 1987 5.22% 1988 16.58% 1989 31.75% 1990 -3.13% 1991 30.53% 1992 7.62% 1993 10.07% 1994 1.27% 1995 37.80% 1996 22.74% 1997 33.43% 1998 28.13% 1999 21.03% st dev Squared Deviation Deviation Steps for calculating standard deviation: Hint: range means a row or column of cells A. Enter the 1980-1999 historical average from J116. Copy from year 1 to 20. B. Find the yearly deviation by subtracting the average return from the Lg Co Ret. Copy from year 1 to 20. C. Find the squared deviation by multiplying the deviation by itself. Copy. D. In cell F153 enter: =sum(range)/(20-1) variance st dev E. In cell F154 take the square root of F153 Enter: = sqrt(F153) F. We can use the built-in formula: in cell C153 enter: =stdev(range). C153 should be equal to F154 (13.13%). Test Your Skills: Question 1: A stock had annual returns of 9 percent, -5 percent, 14 percent, 16 percent, 11 percent, & 12 percent each for the past six years. What is the average return and standard deviation for this stock? Year 1 2 3 4 5 6 Return Average return Standard deviation Question 2: A stock had annual returns of -1 percent, -5 percent, 0 percent, 16 percent, 25 percent, and 50 percent each for the past six years. What is the average return and standard deviation for this stock? Year 1 2 3 4 5 6 Average return Standard deviation End of Spreadsheet 12. Return