4. In this problem, we will build an active portfolio, that is, a portfolio consisting of individually selected stocks to complement our passive investments. To do this, create an Excel worksheet. Use the following instructions with a market risk premium rx-r, of 5%. Consider the following annualized information regarding a selection of six stocks (Apple Inc, Amazon.com Inc, Intuitive Surgical Inc, Target Corp, Walmart Inc, and Exxon Mobil Corp) and the S&P 500 that we have obtained from a regression analysis: SD of Excess Return Beta SD of Residual SD of Systematic Component 0.1701 0.2041 0.1701 0.2869 1.00 1.20 0.0000 0.2016 0.2286 0.3554 1.60 0.2721 S&P 500 SAAPL SAMZN $ISRG $TGT SWMT $XOM 0.3409 1.50 0.2551 0.2261 0.80 0.2761 0.2296 0.2163 0.1361 0.1020 0.60 0.85 0.2403 0.2057 0.1609 0.1446 Find the covariance matrix according to the index model, where the covariance between two assets is defined as Cov(rr;) = B:B;o(Hint: You can find the a from the above table if you compute the covariance of the index with itself because Bs&P 500 = 1.] Create a forecast of a; for each company, that is, the return in percent you anticipate in excess over the return expected through its Be in the coming year. [Example: If the of the company were to be 1, you'd expect an excess return of 5% in the absence of a. However, if you anticipated that the company's stock was going to realize an excess return of 4% or 6%, then its a would be -1% or +1%, respectively.) This forecast can just be a guess for this problem! Construct the risky portfolio according to Table 27.1 in your book or according to "How to build your portfolio" in today's lecture slides. Compute and report the following parameters for your portfolio: a) Weights of the securities in your active portfolio and its a. b) Weight of the active portfolio in the overall portfolio. c) Aggregate alpha and beta of the active portfolio and of the overall portfolio. d) Compare the Sharpe ratio of an investment in the market with the Sharpe ratio of the overall portfolio you have constructed! You need to report answers with your own assumptions. But you can check your work with: ISRG TGT AAPL 0.5% AMZN 0.5% WMT 1.0% Alpha 1.0% -0.5% -0.8% Using these values for each stock, your active portfolio should have ap = 3.263%, Bp = 1.584. The overall portfolio should come out to ao = 0.527%, Bo = 1.094. The Sharpe ratio of this overall portfolio is 0.390, whereas the Sharpe ratio of the market portfolio is 0.294. 4. In this problem, we will build an active portfolio, that is, a portfolio consisting of individually selected stocks to complement our passive investments. To do this, create an Excel worksheet. Use the following instructions with a market risk premium rx-r, of 5%. Consider the following annualized information regarding a selection of six stocks (Apple Inc, Amazon.com Inc, Intuitive Surgical Inc, Target Corp, Walmart Inc, and Exxon Mobil Corp) and the S&P 500 that we have obtained from a regression analysis: SD of Excess Return Beta SD of Residual SD of Systematic Component 0.1701 0.2041 0.1701 0.2869 1.00 1.20 0.0000 0.2016 0.2286 0.3554 1.60 0.2721 S&P 500 SAAPL SAMZN $ISRG $TGT SWMT $XOM 0.3409 1.50 0.2551 0.2261 0.80 0.2761 0.2296 0.2163 0.1361 0.1020 0.60 0.85 0.2403 0.2057 0.1609 0.1446 Find the covariance matrix according to the index model, where the covariance between two assets is defined as Cov(rr;) = B:B;o(Hint: You can find the a from the above table if you compute the covariance of the index with itself because Bs&P 500 = 1.] Create a forecast of a; for each company, that is, the return in percent you anticipate in excess over the return expected through its Be in the coming year. [Example: If the of the company were to be 1, you'd expect an excess return of 5% in the absence of a. However, if you anticipated that the company's stock was going to realize an excess return of 4% or 6%, then its a would be -1% or +1%, respectively.) This forecast can just be a guess for this problem! Construct the risky portfolio according to Table 27.1 in your book or according to "How to build your portfolio" in today's lecture slides. Compute and report the following parameters for your portfolio: a) Weights of the securities in your active portfolio and its a. b) Weight of the active portfolio in the overall portfolio. c) Aggregate alpha and beta of the active portfolio and of the overall portfolio. d) Compare the Sharpe ratio of an investment in the market with the Sharpe ratio of the overall portfolio you have constructed! You need to report answers with your own assumptions. But you can check your work with: ISRG TGT AAPL 0.5% AMZN 0.5% WMT 1.0% Alpha 1.0% -0.5% -0.8% Using these values for each stock, your active portfolio should have ap = 3.263%, Bp = 1.584. The overall portfolio should come out to ao = 0.527%, Bo = 1.094. The Sharpe ratio of this overall portfolio is 0.390, whereas the Sharpe ratio of the market portfolio is 0.294