Calculate the weekly return for each of the 5 stocks and each of the 2 portfolios

2. Calculate the standard deviation of returns for the 5 stocks and the 2 portfolios.

3. Calculate the weighted average standard deviations of the 2 portfolios, using the portfolio weights established at the start of the project.

4. Compare the calculated numbers above to the realized standard deviation of the 2 portfolios. Calculate the ratio of the realized standard deviation of the portfolios to the weighted average standard deviations calculated in # 3 above. [Note that the weighted average standard deviation calculated in #3 would only be equal to the realized standard deviation if all 5 stocks were perfectly positively correlated, i.e. have a +1 correlation coefficient. In that case, no diversification benefit would be possible. As long as that is not the case, diversification benefits should be realized reflected in reduced risk (standard deviation).]

5. Take the value of each portfolio as of the ending date. Using the percent allocation as of the starting date, how much would you need to have invested in each stock to maintain that distribution. Which stocks have the greatest disparity in values in each portfolio and why?

6. In evaluating the performance of your portfolio, use CAPM to estimate the return you could reasonably expect. You have the beta of your portfolio. You may use the change in the S&P 500 index over the period as your market return and 1% as the risk free rate. How does that compare with the observed return?

FIN 302 Excel Sheet (1) - Excel Insert Page Layout Formulas Data Review View Help Tell me what you want to do 9-2 Wrap Text | Merge & Center , $ ,% , 48 Conditional Format as Cell Paste Insert Delete Format , Format Painter Blu, 1-2. Sort & Find & Filter Select Formatting Table Styles , Number Editing A1 1204.239357 MSFT Close Prics Weokly Return NVOCbse Prizo Weckly Retu SAP Cloae Prc Weckly Retu NO Clore Prio Wookly Rotu WClosa Prias Weckly Re Value of Portfolio Equally Price Weighted Equally Weighked DOW voighkad 35.01 10057)$ 5550 t (Q1001$ 3.25$ ) 0072( 79.45 10.06) 3201 $ (0.00) 35.69 (0.19) 24.737 78 0050t 4990 (00241108.97 0019 7511$ Standard Ueviation ot Companies and Indexe:s MSFT STD DEV Ag ol Price Porto Averege oftaPorttoio SandardDodation Standard Dotation SAP STD VNOSTDD Sheet1 O Type here to search L 00 07 07 34 71 17 28 23 01 96 41 27 26 47 4104 31-20 41 33 63 87 86 2 35 ST 24 AT 75 26 50 ST 21 26 26 90 43 53 75 53 81 39 6 03 08 01 06 on 00 03 on 05 30 00 01 06 16 02 08 04 70 he 45 96 56 84 01 9 7 6 4 3 3 5 5 5 6 6 7 7 5 5 5 9 21 43 16 46 52 31 97 35 77 83 84 76 66 67 67 63 63 65 84 84 85 73 82 85 07 111 001 01 001 02 101 02 01 01 01 01 01 02 03 02 02 02 02 01 01 Flo 43 52 31-11 45 48 16 93 35 03 05 08 56 87-11 84 13 so 34 12 62 31 78 53 Reu 004-ces 012 000-,, 002 oca 005 004 013 001 008 005 140 001 013 ose 000 Fre 31 8 1 3 5 9 3 2 5 31 08 10 98 47 37 8 98 07 16 3 0 9 5 5 84 nce 68 79 41 73 50 96 80 53 48 63 29 25 12 85 30 68 88 01 04 86 27 56 85 50 53 08 nc 35 82 01 61 81 49 ba 86 87 90 91 89 90 86 85 48 39 33 86 44 31 si 34 .5 66 95 49 B5 54 33 32 89 32 31 36 92 gg 96 37 8 9 02 po 101 97 DE DE d B 18 18 18 18 18 78 18 18 18 15 17 t3 13 20 21 2 2 2 2 2 2 2 2 3 3 3 3 4 5 6 Re 123456789 11 12 13 14 FIN 302 Excel Sheet (1) - Excel Insert Page Layout Formulas Data Review View Help Tell me what you want to do 9-2 Wrap Text | Merge & Center , $ ,% , 48 Conditional Format as Cell Paste Insert Delete Format , Format Painter Blu, 1-2. Sort & Find & Filter Select Formatting Table Styles , Number Editing A1 1204.239357 MSFT Close Prics Weokly Return NVOCbse Prizo Weckly Retu SAP Cloae Prc Weckly Retu NO Clore Prio Wookly Rotu WClosa Prias Weckly Re Value of Portfolio Equally Price Weighted Equally Weighked DOW voighkad 35.01 10057)$ 5550 t (Q1001$ 3.25$ ) 0072( 79.45 10.06) 3201 $ (0.00) 35.69 (0.19) 24.737 78 0050t 4990 (00241108.97 0019 7511$ Standard Ueviation ot Companies and Indexe:s MSFT STD DEV Ag ol Price Porto Averege oftaPorttoio SandardDodation Standard Dotation SAP STD VNOSTDD Sheet1 O Type here to search L 00 07 07 34 71 17 28 23 01 96 41 27 26 47 4104 31-20 41 33 63 87 86 2 35 ST 24 AT 75 26 50 ST 21 26 26 90 43 53 75 53 81 39 6 03 08 01 06 on 00 03 on 05 30 00 01 06 16 02 08 04 70 he 45 96 56 84 01 9 7 6 4 3 3 5 5 5 6 6 7 7 5 5 5 9 21 43 16 46 52 31 97 35 77 83 84 76 66 67 67 63 63 65 84 84 85 73 82 85 07 111 001 01 001 02 101 02 01 01 01 01 01 02 03 02 02 02 02 01 01 Flo 43 52 31-11 45 48 16 93 35 03 05 08 56 87-11 84 13 so 34 12 62 31 78 53 Reu 004-ces 012 000-,, 002 oca 005 004 013 001 008 005 140 001 013 ose 000 Fre 31 8 1 3 5 9 3 2 5 31 08 10 98 47 37 8 98 07 16 3 0 9 5 5 84 nce 68 79 41 73 50 96 80 53 48 63 29 25 12 85 30 68 88 01 04 86 27 56 85 50 53 08 nc 35 82 01 61 81 49 ba 86 87 90 91 89 90 86 85 48 39 33 86 44 31 si 34 .5 66 95 49 B5 54 33 32 89 32 31 36 92 gg 96 37 8 9 02 po 101 97 DE DE d B 18 18 18 18 18 78 18 18 18 15 17 t3 13 20 21 2 2 2 2 2 2 2 2 3 3 3 3 4 5 6 Re 123456789 11 12 13 14