i will rate if done correctly

Part I. Summary Statistics of Monthly Data ( 40 Points) Please obtain the monthly return data during the period Jan 2012 - Dec 2021 for the following: S\&P500 ETF (SPY), Investment-Grade Corporate Bond ETF (LQD), 30 Industry portfolios, Fama-French 3 factors (Excess market return MKTRF, Small-minus-big SMB, High-minus-low HML ), and risk-free rate, which is the 1 -month Treasury Bill (1MoTB). The data given on the spreadsheet are simple monthly returns (the percent change in price over the previous month). For Part I, we will only be using the SPY, LQD, and 1MoTB series. i) Compute the following for each of the three return series: a. (5pts) monthly geometric average and annualized geometric average. Note: To annualize, raise (1+ monthly geometric average) to the power of 12 and then subtract 1 . b. (3 pt) sample standard deviation, variance, and correlation for each pair. c. (2pt) skewness and kurtosis d. (10 Pts) Generate a histogram for SPY using the histogram tool in the Excel Data Analysis Pack. Use 5 bins of your choice. Do the returns seem to be normally distributed? Comment in 1-2 sentences in terms of skewness and kurtosis found previously. Please consult the search engine to get help on how to generate a histogram on Excel. ii) (10 Pts) Compute the cumulative return over the entire sample period for each series, with $100 as a starting point. That is, how much would you have accumulated by the end of 2020 if you invested $100 at the beginning of 2012 and held it over the period of 20122020 ? iii) (10 Pts) Plot the growth of \$100 over time for S\&P500 ETF, Bond ETF, and 1MoTB. Comment in 2-3 sentences. art II. Optimal Risky Portfolio, Efficient Frontier, and Capital Allocation ( 60 Points) ut yourself in the shoes of a buy-side 1 investment manager in charge of market research and sset allocation. Suppose one of your clients comes to you with the following question: "How an I best allocate my funds across the following four asset choices: stocks, corporate bonds, tovernment bonds, and an industry portfolio?" i) Use the data provided for the period Jan 2012-Dec 2021. We will let S\&P500 ETF (SPDR), iShare Corporate Bond ETF (LQD), and 1MoTB series and your industry portfolio to represent the four assets requested by the client. Your investment portfolio is "Retail." a. (5 Point) We will try something different here from Part I. Let's convert all the monthly simple returns to continuously compounded returns by taking the natural log (use the Excel function 'In') of (1 + the simple returns). Log-returns are convenient in that they are additive. Compute the arithmetic average of monthly log returns and multiply by 12 to get an annualized average return. Do this for all 4 series. b. (5 Points) Compute the sample standard deviation of the monthly log returns (Excel function 'stdev.s') and multiply the sample standard deviation by sqrt(12) to get the annualized sample standard deviation. (Note: We cannot do the same with simple returns to annualize the statistics. Only log-returns allow us to do this, under the assumption of continuous compounding!) ii) (10 Points) Use the correlation tool in the Data Analysis to generate the correlation coefficient matrix using the log returns for the four assets. Comment in 2-3 sentences on the correlation coefficients. iii) We will use the annualized average return, standard deviation, and the correlation computed in Part II (i) and (ii) to construct the two-asset optimal risky portfolio comprising SPY and LQD. a. (5 Points) Compute the portfolio average return, portfolio risk, and the Sharpe ratio for all portfolios with weights ranging from 0% to 100% at increments of 10%. b. (5 Points) Plot the investment opportunity set with portfolio risk on the x-axis and return on the y-axis. c. (5 Points) Bold the portfolio with the highest Sharpe ratio, which is the optimal risky portfolio, a ka. tangency portfolio. What are weights, portfolio retum and risk, and Sharpe ratio for this tangency portfolio? iv) (10 Points) Repeat the above process of the fwo-asset optimal risky portfolio in (iii), now comprising SPY and your retail portfolio. Comment on the results, comparing your findings with (iii). Which optimal risky portfolio seems to be a better choice? Explain. v) Now, it's time to obtain the complete portfolio. That is, how much should be allocated to the optimal risky portfolio and the risk-free asset. For the optimal risky, use the superior one from the previous two exercises (choose either SPY and LQD in iii vs. SPY and industry in iv). a. ( 5 Points) For the complete portfolio, compute the portfolio average retum, portfolio risk, and the Sharpe ratio for all portfolios with weights ranging from 0% to 150% at increments of 10%. (weight above 100% means borrowing to invest more in the optimal risky). b. (5 Points) Plot the capital allocation line (CAL). c. (5 Points) Explain all the findings in (v) in a one-paragraph narrative. Part III. Regression Analysis: Estimating Factor Models (40 Points) You will continue to work with your inchustry portfolio from Part II to investigate to what degree the industry performance is linked to a systematic market factor. In other words, how much of the variation in the industry stock performance can be attributed to the broader movements in the stock market? Throughout Part III, please restrict the time period to the recent 5 years, Jan 2017Dec 2021, for this part. Go back to using the simple retuons (not log retums) for the regression analyses. i) Scatter plot and trendline. a. (2 pt) Create a scatter plot of monthly returns of your industry portfolio is. monthly returns of MKT-RF. b. (3 pt) Add a trendline with an equation shown on the plot: Search online to get help on how to generate a scatter plot and add a trendline c. (5pt) Explain the plot, the trendline, and the equation in 34 sentences. ii) Using the Regression tool in the Excel Data Analysis pack, conduct time-series regression to obtain the following. Your dependent variable would be the industry monthly returns. a. (5 Points) Alpha, beta, t-statistics for the beta estimate, and adjusted R-squared from the single index model using MKTRF factor as the only IV (independent variable). b. (10 Points) Alpha, betas, t-statistics for the beta estimates, and adjusted R-squared from the three factors using the Fama-French 3 factors as the W/s. c. (15 Points) Please explain all your findings from the regression analyses in 2-3 paragraphs. Please interpret the intercept, coefficients, R-squareds, t-statistics, and all other relevant outputs. Link the results to what it would mean for your investment decision-making process. LASTLY: The report will be graded in two aspects: qualitative and quantitative. On the rubric, you will see that 60 out of 150 points have to do with getting numerical values correctly (the highlighted items). You will enter these numerical answers on Canvas "Numerical Answers" such that Canvas can grade them automatically. The rest, 90 out of 160 points have to do with the qualitative aspect of your project, the remaining part of the rubric, which I will do manually by going through each report. The report and the numerical answers are both due by midnight Dec 7th (No extensions). Part I. Summary Statistics of Monthly Data ( 40 Points) Please obtain the monthly return data during the period Jan 2012 - Dec 2021 for the following: S\&P500 ETF (SPY), Investment-Grade Corporate Bond ETF (LQD), 30 Industry portfolios, Fama-French 3 factors (Excess market return MKTRF, Small-minus-big SMB, High-minus-low HML ), and risk-free rate, which is the 1 -month Treasury Bill (1MoTB). The data given on the spreadsheet are simple monthly returns (the percent change in price over the previous month). For Part I, we will only be using the SPY, LQD, and 1MoTB series. i) Compute the following for each of the three return series: a. (5pts) monthly geometric average and annualized geometric average. Note: To annualize, raise (1+ monthly geometric average) to the power of 12 and then subtract 1 . b. (3 pt) sample standard deviation, variance, and correlation for each pair. c. (2pt) skewness and kurtosis d. (10 Pts) Generate a histogram for SPY using the histogram tool in the Excel Data Analysis Pack. Use 5 bins of your choice. Do the returns seem to be normally distributed? Comment in 1-2 sentences in terms of skewness and kurtosis found previously. Please consult the search engine to get help on how to generate a histogram on Excel. ii) (10 Pts) Compute the cumulative return over the entire sample period for each series, with $100 as a starting point. That is, how much would you have accumulated by the end of 2020 if you invested $100 at the beginning of 2012 and held it over the period of 20122020 ? iii) (10 Pts) Plot the growth of \$100 over time for S\&P500 ETF, Bond ETF, and 1MoTB. Comment in 2-3 sentences. art II. Optimal Risky Portfolio, Efficient Frontier, and Capital Allocation ( 60 Points) ut yourself in the shoes of a buy-side 1 investment manager in charge of market research and sset allocation. Suppose one of your clients comes to you with the following question: "How an I best allocate my funds across the following four asset choices: stocks, corporate bonds, tovernment bonds, and an industry portfolio?" i) Use the data provided for the period Jan 2012-Dec 2021. We will let S\&P500 ETF (SPDR), iShare Corporate Bond ETF (LQD), and 1MoTB series and your industry portfolio to represent the four assets requested by the client. Your investment portfolio is "Retail." a. (5 Point) We will try something different here from Part I. Let's convert all the monthly simple returns to continuously compounded returns by taking the natural log (use the Excel function 'In') of (1 + the simple returns). Log-returns are convenient in that they are additive. Compute the arithmetic average of monthly log returns and multiply by 12 to get an annualized average return. Do this for all 4 series. b. (5 Points) Compute the sample standard deviation of the monthly log returns (Excel function 'stdev.s') and multiply the sample standard deviation by sqrt(12) to get the annualized sample standard deviation. (Note: We cannot do the same with simple returns to annualize the statistics. Only log-returns allow us to do this, under the assumption of continuous compounding!) ii) (10 Points) Use the correlation tool in the Data Analysis to generate the correlation coefficient matrix using the log returns for the four assets. Comment in 2-3 sentences on the correlation coefficients. iii) We will use the annualized average return, standard deviation, and the correlation computed in Part II (i) and (ii) to construct the two-asset optimal risky portfolio comprising SPY and LQD. a. (5 Points) Compute the portfolio average return, portfolio risk, and the Sharpe ratio for all portfolios with weights ranging from 0% to 100% at increments of 10%. b. (5 Points) Plot the investment opportunity set with portfolio risk on the x-axis and return on the y-axis. c. (5 Points) Bold the portfolio with the highest Sharpe ratio, which is the optimal risky portfolio, a ka. tangency portfolio. What are weights, portfolio retum and risk, and Sharpe ratio for this tangency portfolio? iv) (10 Points) Repeat the above process of the fwo-asset optimal risky portfolio in (iii), now comprising SPY and your retail portfolio. Comment on the results, comparing your findings with (iii). Which optimal risky portfolio seems to be a better choice? Explain. v) Now, it's time to obtain the complete portfolio. That is, how much should be allocated to the optimal risky portfolio and the risk-free asset. For the optimal risky, use the superior one from the previous two exercises (choose either SPY and LQD in iii vs. SPY and industry in iv). a. ( 5 Points) For the complete portfolio, compute the portfolio average retum, portfolio risk, and the Sharpe ratio for all portfolios with weights ranging from 0% to 150% at increments of 10%. (weight above 100% means borrowing to invest more in the optimal risky). b. (5 Points) Plot the capital allocation line (CAL). c. (5 Points) Explain all the findings in (v) in a one-paragraph narrative. Part III. Regression Analysis: Estimating Factor Models (40 Points) You will continue to work with your inchustry portfolio from Part II to investigate to what degree the industry performance is linked to a systematic market factor. In other words, how much of the variation in the industry stock performance can be attributed to the broader movements in the stock market? Throughout Part III, please restrict the time period to the recent 5 years, Jan 2017Dec 2021, for this part. Go back to using the simple retuons (not log retums) for the regression analyses. i) Scatter plot and trendline. a. (2 pt) Create a scatter plot of monthly returns of your industry portfolio is. monthly returns of MKT-RF. b. (3 pt) Add a trendline with an equation shown on the plot: Search online to get help on how to generate a scatter plot and add a trendline c. (5pt) Explain the plot, the trendline, and the equation in 34 sentences. ii) Using the Regression tool in the Excel Data Analysis pack, conduct time-series regression to obtain the following. Your dependent variable would be the industry monthly returns. a. (5 Points) Alpha, beta, t-statistics for the beta estimate, and adjusted R-squared from the single index model using MKTRF factor as the only IV (independent variable). b. (10 Points) Alpha, betas, t-statistics for the beta estimates, and adjusted R-squared from the three factors using the Fama-French 3 factors as the W/s. c. (15 Points) Please explain all your findings from the regression analyses in 2-3 paragraphs. Please interpret the intercept, coefficients, R-squareds, t-statistics, and all other relevant outputs. Link the results to what it would mean for your investment decision-making process. LASTLY: The report will be graded in two aspects: qualitative and quantitative. On the rubric, you will see that 60 out of 150 points have to do with getting numerical values correctly (the highlighted items). You will enter these numerical answers on Canvas "Numerical Answers" such that Canvas can grade them automatically. The rest, 90 out of 160 points have to do with the qualitative aspect of your project, the remaining part of the rubric, which I will do manually by going through each report. The report and the numerical answers are both due by midnight Dec 7th (No extensions)