Excel file includes monthly stock price information for Johnson and Johnson (JNJ) and Walgreens Boots Alliance (WBA) companies over five years, which were downloaded from Yahoo! Finance (finance.yahoo.com). Convert these prices to monthly returns. Note that to compute a return for each month, you need a beginning and ending price, so you will not be able to compute the month for the first month. Holding period return = (end price- begin price)/begin priceCompute the mean monthly returns and standard deviations for the monthly returns of each of the stocks. Convert the monthly statistics to annual statistics for easier interpretation (multiply the mean monthly return by 12, and multiply the monthly standard deviation by ). Calculate correlation coefficient between the two stocks using ?CORREL? function in excel.Calculate the expected return and the volatility (standard deviation) of a portfolio consisting of Johnson & Johnson?s and Walgreen?s stocks using a wide range of portfolio weights. Plot the expected return as a function of the portfolio volatility. Using your graph, identify the range of Johnson & Johnson?s portfolio weights that yield efficient combinations of the two stocks, rounded to the nearest percentage point (refer to figure 11.3 from your textbook, p371 to present your graph).

and

Weight in Johnson & Johnson's	Weight in Walgreen's	E[Rp]	SD[Rp]
0	1
0.05	0.95
0.1	0.9
0.9	0.1
0.95	0.05
1	0

MBA 508: Data Case Due: 11/30/2016 (Wed), 6:00 p.m. (in class) 1. Excel file includes monthly stock price information for Johnson and Johnson (JNJ) and Walgreens Boots Alliance (WBA) companies over five years, which were downloaded from Yahoo! Finance (finance.yahoo.com). 2. Convert these prices to monthly returns. Note that to compute a return for each month, you need a beginning and ending price, so you will not be able to compute the month for the first month. Holding period return = (end price- begin price)/begin price 3. Compute the mean monthly returns and standard deviations for the monthly returns of each of the stocks. Convert the monthly statistics to annual statistics for easier interpretation (multiply the mean monthly return by 12, and multiply the monthly standard deviation by 12 ). 4. Calculate correlation coefficient between the two stocks using \"CORREL\" function in excel. Expected Return Standard Deviation Johnson & Johnson Walgreens Boots Alliance Correlation between Johnson & Johnson and Walgreen's 5. Calculate the expected return and the volatility (standard deviation) of a portfolio consisting of Johnson & Johnson's and Walgreen's stocks using a wide range of portfolio weights. Plot the expected return as a function of the portfolio volatility. Using your graph, identify the range of Johnson & Johnson's portfolio weights that yield efficient combinations of the two stocks, rounded to the nearest percentage point (refer to figure 11.3 from your textbook, p371 to present your graph). P2 12 * 12 22 * 22 2 * Cov(r1 , r2 ) and 12 * 12 22 * 22 2 * 12 * 1 * 2 Weight in Johnson & Johnson's Weight in Walgreen's 0 1 0.05 0.95 0.1 0.9 0.9 0.1 0.95 0.05 SD [ RP ] P2 E[Rp] SD[Rp] 1 0 Date Nov-16 Oct-16 Sep-16 Aug-16 Jul-16 Jun-16 May-16 Apr-16 Mar-16 Feb-16 Jan-16 Dec-15 Nov-15 Oct-15 Sep-15 Aug-15 Jul-15 Jun-15 May-15 Apr-15 Mar-15 Feb-15 Jan-15 Dec-14 Nov-14 Oct-14 Sep-14 Aug-14 Jul-14 Jun-14 May-14 Apr-14 Mar-14 Feb-14 Jan-14 Dec-13 Nov-13 Oct-13 Sep-13 Aug-13 Jul-13 Jun-13 May-13 Apr-13 Mar-13 Feb-13 Jan-13 JNJ WBA 115.11 115.99 118.13 119.34 124.40 120.50 111.94 110.55 106.72 103.77 102.27 100.59 99.14 98.21 90.74 91.36 96.67 94.02 96.60 95.00 96.35 98.17 95.24 99.45 102.95 101.84 100.72 98.01 93.94 98.19 95.22 94.41 91.56 85.86 81.87 84.76 87.60 85.11 79.67 79.41 85.28 78.31 76.78 77.16 73.81 68.90 66.39 80.35 82.73 80.62 80.71 78.89 82.89 77.05 78.56 83.47 78.22 78.63 83.99 82.88 83.15 81.60 84.99 94.51 82.59 83.96 80.80 82.50 80.94 71.55 73.93 66.56 61.99 57.21 58.42 66.02 71.17 69.04 64.88 63.10 64.93 54.54 54.62 56.30 56.04 50.90 45.48 47.23 41.55 44.89 46.28 44.57 38.27 37.11 e.g., Dec-12 Nov-12 Oct-12 Sep-12 Aug-12 Jul-12 Jun-12 May-12 Apr-12 Mar-12 Feb-12 Jan-12 Dec-11 Nov-11 62.96 62.63 63.05 61.35 60.03 61.07 59.60 55.08 56.88 57.63 56.86 57.08 56.80 56.05 34.37 31.49 32.45 33.56 32.94 33.23 27.04 27.89 31.82 30.40 30.10 30.08 29.81 30.41 Returns for JNJ Returns for WBA -0.0075868352 -0.0287683418