3 2022 . Saved Search (Alt +Q) Layout Formulas Data Review View Help INDIVIDUAL STOCK AND RISK OF A PORTFOLIO - A B C D E G H 22 January 2013, the return on your portfolio would have been: 1/2 * 2.7% + 1/2* 6.5% = 4.6%. Repeat this calculation in cells E37-E101 for all the historical months 1/2013-6/2018. 1 K L M N 0 P Q 23 What is the volatility of the portfolio? Is it higher or lower than the volatility of Pepsico? The volatility of Coca Cola? If you were worried about risk only, would you choose this portfolio over the sto 25 24 Why do you think the portfolio of Coca Cola and Pepsico led to lower risk than Pepsico alone even though Pepsico is less risky than Coca Cola? 26 d. Optimization of the portfolio weights: 27 What you did in section c can be extended to other portfolio weights. For example, if you wanted to know what is the volatility of a portoflio that has x in Coca Cola and (1-x) in 28 Pepsico you would calculate x*r_KO + (1-x)"r_PEP where r_KO and r_PEP are the returns of Coca Cola and Pepsico in that month. Repeat the calculation for weights 0%, 10%, 20%, ..100% 29 in Coca Cola in cells F36-0101. (Hint: this can be done very efficiently if you use the F4 function wisely). 30 Calculate the standard deviation of each portfolio. Which portfolio weights will minimize your risk? 31 32 33 34 Percent invested in Coca Cola Return Coca Return Return on a portfolio 1/2 35 Year Month Cola Pepsi in Coca Cola and 1/2 in Pepsico 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% 36 2013 01 2.7% 6.5% 37 2013 02 4.0% 4.7% 38 2013 102 5.2% 4.4% 39 2013 4.7% 4.2% 40 2013 105 -5.5% -2.1% 6013