I attached a document, where it has the problems that I need help with. I highlighted where there should be answer that I need help with and I tried answering them so that is why there are some answered that are filled in.

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 3-Jun-96 1-May-96 1-Apr-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 45.93 43.01 38.10 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 7.06 6.78 6.78 Step 1: Capital Appreciation A. Price you received today Price you paid Difference * 100 shares $ 20.31 5 15.31 1,531 In 1995 you spent $500 for 100 shares of SBUX. Today, when you sold them, your capital appreciation is valued at ==> 1,531 B. At the same time your sister bought 100 shares of Coca Cola. What was her stock's capital appreciation? Price she received today 49.43 Price she paid 29.75 Difference 19.68 * 100 shares $ 1,968 Today, when she sold them, her capital appreciation is valued at ==> $ 1,968 Step 2: Dividends A. B. Dividends are given. Dividends per share of SBUX * 100 shares Div earnings from SBUX Dividends per share of KO * 100 shares Div earnings from KO 0 0 $ - $ 4.94 494 494 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 $ 1,531 $ $ 1,531 KO cost (price X 100) KO capital appreciation KO dividends ($4.94 per share) KO cap. app. + div. Total return = (CA + D) / cost $ $ $ 1,968 494 2,462 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 $ 2,021 $ 1,804 $ $ 1,531 $ 1,531 8486.70% 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 $ $ 4,932 4,408 $ 1,968 Question 1. Which stock did better over the holding period? 1-Mar-96 1-Feb-96 2-Jan-96 1-Dec-95 1-Nov-95 2-Oct-95 1-Sep-95 1-Aug-95 38.68 37.63 35.13 34.60 35.30 33.40 32.06 29.75 KO 5.83 4.41 4.19 5.25 5.28 4.91 4.73 5 SBUX 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) 18.58% 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. Year 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 #NAME? Average Squared Return Deviation Deviation 18.58% 14% 1.97% 18.58% -24% 5.55% 18.58% 3% 0.10% 18.58% 4% 0.16% 18.58% -12% 1.54% 18.58% 13% 1.76% 18.58% 0% 0.00% 18.58% -13% 1.79% 18.58% -2% 0.04% 18.58% 13% 1.73% 18.58% -22% 4.71% 18.58% 12% 1.43% 18.58% -11% 1.20% 18.58% -9% 0.72% 18.58% -17% 3.00% 18.58% 19% 3.69% 18.58% 4% 0.17% 18.58% 15% 2.20% 18.58% 10% 0.91% 18.58% 2% 0.06% variance #NAME? st dev #NAME? 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) 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 Average return Standard deviation Return 9 -5 14 16 11 12 9.5 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 -1 -5 0 16 25 50 14.166667