Johnson & Johnson and Walgreens Boots Alliance have expected returns and volatilities shown below, with a correlation of 22%. Calculate the expected return and the volatility (standard deviation) of a portfolio consisting of Johnson & Johnsons and Walgreens 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 & Johnsons portfolio weights that yield efficient combinations of the two stocks, rounded to the nearest percentage point.

	In cell E13, by using cell references, calculate the expected return of a portfolio consisting entirely of Walgreens stocks (1 pt.).
	To calculate the expected returns of other portfolios, copy cell E13 and paste it onto cells E14:E33 (1 pt.).
	In cell F13, by using cell references, calculate the standard deviation of a portfolio consisting entirely of Walgreens stocks (1 pt.).
	To calculate the standard deviations of other portfolios, copy cell F13 and paste it onto cells F14:F33 (1 pt.).
	In cells C35:E51, insert a Scatter with Smooth Lines Chart to show the expected return as a function of the portfolio volatility. Inserting a Chart On the Insert tab, in the Charts group, click the arrow next to Insert Scatter (X,Y) or Bubble Chart and choose Scatter with Smooth Lines Chart. Selecting Data Series Then in Select Data Source window, delete any series created automatically. Add new series for the risk-free combination line using cells F13:F33 for the X values and cells E13:E33 for the Y values. Do not name the series. Edit Chart Elements Add a chart title and choose the Above Chart option. Replace Chart Title with Risk-Return Combination. Add axis titles. Replace Axis Title for the horizontal axis with Standard Deviation and Axis Title for the vertical axis with Expected Return (4 pt.).
	In cell F53, select from the dropdown the maximum Johnson & Johnsons portfolio weight that yields an efficient combination of the two stocks (1 pt.).

B E 5 6 Expected Return 7% 10% Standard Deviation 16% 20% 7 8 Johnson & Johnson Walgreens Boots Alliance 9 10 Correlation 22% A B E 11 12 Expected Return Standard Deviation 13 14 15 16 17 18 19 20 21 22 23 Weight in Johnson & Johnson's 0.00 0.05 0.10 0.15 0.20 0.25 0.30 0.35 0.40 0.45 0.50 0.55 0.60 0.65 0.70 0.75 0.80 0.85 0.90 0.95 1.00 Weight in Walgreens' 1.00 0.95 0.90 0.85 0.80 0.75 0.70 0.65 0.60 0.55 0.50 0.45 0.40 0.35 0.30 0.25 0.20 0.15 0.10 0.05 0.00 24 25 26 27 28 29 30 31 32 33 21 A B E F G 32 0.95 1.00 0.05 0.00 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 The set of efficient portfolios is approximately those portfolios with no more than vested in J&J. 0% 53 54 55 56 Requirements 65% 07 80% 11-26 salata + 100% B E 5 6 Expected Return 7% 10% Standard Deviation 16% 20% 7 8 Johnson & Johnson Walgreens Boots Alliance 9 10 Correlation 22% A B E 11 12 Expected Return Standard Deviation 13 14 15 16 17 18 19 20 21 22 23 Weight in Johnson & Johnson's 0.00 0.05 0.10 0.15 0.20 0.25 0.30 0.35 0.40 0.45 0.50 0.55 0.60 0.65 0.70 0.75 0.80 0.85 0.90 0.95 1.00 Weight in Walgreens' 1.00 0.95 0.90 0.85 0.80 0.75 0.70 0.65 0.60 0.55 0.50 0.45 0.40 0.35 0.30 0.25 0.20 0.15 0.10 0.05 0.00 24 25 26 27 28 29 30 31 32 33 21 A B E F G 32 0.95 1.00 0.05 0.00 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 The set of efficient portfolios is approximately those portfolios with no more than vested in J&J. 0% 53 54 55 56 Requirements 65% 07 80% 11-26 salata + 100%