Step 1) Opportunity Set Graph: In this step, you will use two of your 4 stocks, which I will refer to as “stock 1” and “stock 2”. Which of the 2 of the 4 stocks you choose to use is up to you. In order for the plot to look nice, it is advisable to pick a lower risk/lower return stock for 1 of the 2 and a higher risk/higher return stock for the other. Combine stocks 1 and 2 in a portfolio using the weights in columns B and C. Construct an investment opportunity set (the curved set) between the two risky assets. Graph the resulting curve and on a Mean-StDev graph. Use the X Y (Scatter) option and allow the lines to cross. On the same graph also graph the capital allocation line for one of your portfolios and the risk-free asset. The risky asset for the capital allocation line should be a portfolio consisting of 60% of Stock 1 and 40% of stock 2.

Step 2) Regressions: Use the “data analysis” and “regression” features in Excel to complete a regression equation for each of your four stocks. Have Excel display the regression statistics on the “Regression Output” page. Enter the alpha, beta, and R-squared from this in your analysis table. Also, use the “slope” function in Excel to calculate the slope of your line. For both the regression analysis and the slope function, specify the data range for the excess return for your stocks from the “Regression Data” page as the y-variable and the data range for the excess return for the S&P index on the “Regression Data” page as your x-variable.

Step 3) Scatter-Plots: Create a scatter-plot of each of your four stocks where the excess returns for the stock are on the y-axis and the excess returns for the S&P 500 are on the x-axis. Have Excel display the plot on the “Scatter Plot” page. Add a trend-line line by selecting (highlighting) the data series, then right click and select “add trend-line.” Also display the regression equation and the R2 value on the chart by right-clicking on the trend-line and then selecting “format trend-line.” Enter the alpha and beta from this in your analysis table. At this point, you should return to the “Analysis” page and complete the remaining information pertaining to excess returns and estimates of alphas and betas. Be sure to use the appropriate cell references where applicable.

Step 4) Graph Rolling Estimates: Next, you will construct three graphs that illustrate the sensitivity of key parameter estimates (average stock returns, standard deviations, and correlations with the market return) to the particular data used for estimation. To complete this step, do the following:

1) On the “Regression Data” worksheet, add three new columns for your first stock (a DJIA stock). (If you have followed previous instructions, these should be columns M, N, and O on the worksheet.) In the first of these columns, compute a five-year rolling average of the excess return on your first stock. The first estimate of the average excess return should be based on data spanning January 2006 – December 2010. This estimate should be entered in the row corresponding to December 2010. (Leave previous rows in this column blank.) Your next estimate will appear in the row corresponding to January 2011, and will be based on data from February 2006 – January 2011. Continue in this fashion through December 2019. Note: once you have computed the first five-year estimate, you should be able to copy down the formula to complete the remainder of the rolling estimates. The second and third added columns should contain similar five-year rolling estimates of the standard deviation of excess stock returns and the correlation of the stock’s excess returns with the S&P 500 excess return (you can use the “Correl” function).

2) Repeat the above step for a second stock (a non-DJIA stock). After completing this step, you will have rolling five-year estimates of the average, standard deviation, and correlation with the market for both stocks (the DJIA stock and a non-DJIA stock) from December 2010 – December 2019. These estimates should appear in rows M – R.

3) Create three graphs. The first graph should plot the rolling estimate of the average stock return for both stocks as a function of time for December 2010 – December 2019. You should include an appropriate title and a legend that clearly identifies each stock on the plot. The second and third graphs should be similar to the first, except these should plot the rolling standard deviation and rolling correlation with the S&P 500 return, respectively. Place all three graphs on the worksheet titled “RollingEstimates.”

Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Phasellus hendrerit. Pellentesque aliquet nibh nec urna. In nisi neque, aliquet vel, dapibus id, mattis vel, nisi. Sed pretium, ligula sollicitudin laoreet viverra, tortor libero sodales leo, eget blandit nunc tortor eu nibh. Nullam mollis. Ut justo. Suspendisse potenti.

Sed egestas, ante et vulputate volutpat, eros pede semper est, vitae luctus metus libero eu augue. Morbi purus libero, faucibus adipiscing, commodo quis, gravida id, est. Sed lectus. Praesent elementum hendrerit tortor. Sed semper lorem at felis. Vestibulum volutpat, lacus a ultrices sagittis, mi neque euismod dui, eu pulvinar nunc sapien ornare nisl. Phasellus pede arcu, dapibus eu, fermentum et, dapibus sed, urna.

Morbi interdum mollis sapien. Sed ac risus. Phasellus lacinia, magna a ullamcorper laoreet, lectus arcu pulvinar risus, vitae facilisis libero dolor a purus. Sed vel lacus. Mauris nibh felis, adipiscing varius, adipiscing in, lacinia vel, tellus. Suspendisse ac urna. Etiam pellentesque mauris ut lectus. Nunc tellus ante, mattis eget, gravida vitae, ultricies ac, leo. Integer leo pede, ornare a, lacinia eu, vulputate vel, nisl.

Suspendisse mauris. Fusce accumsan mollis eros. Pellentesque a diam sit amet mi ullamcorper vehicula. Integer adipiscing risus a sem. Nullam quis massa sit amet nibh viverra malesuada. Nunc sem lacus, accumsan quis, faucibus non, congue vel, arcu. Ut scelerisque hendrerit tellus. Integer sagittis. Vivamus a mauris eget arcu gravida tristique. Nunc iaculis mi in ante. Vivamus imperdiet nibh feugiat est.

Ut convallis, sem sit amet interdum consectetuer, odio augue aliquam leo, nec dapibus tortor nibh sed augue. Integer eu magna sit amet metus fermentum posuere. Morbi sit amet nulla sed dolor elementum imperdiet. Quisque fermentum. Cum sociis natoque penatibus et magnis xdis parturient montes, nascetur ridiculus mus. Pellentesque adipiscing eros ut libero. Ut condimentum mi vel tellus. Suspendisse laoreet. Fusce ut est sed dolor gravida convallis. Morbi vitae ante. Vivamus ultrices luctus nunc. Suspendisse et dolor. Etiam dignissim. Proin malesuada adipiscing lacus. Donec metus. Curabitur gravida

WhatsApp us