please advise, thanks for the help!

here is the data!

Part II. Optimal Risky Portfolio, Efficient Frontier, and Capital Allocation (60 Points) Put yourself in the shoes of a buy-side 1 investment manager in charge of market research and asset allocation. Suppose one of your clients comes to you with the following question: "How can I best allocate my funds across the following four asset choices: stocks, corporate bonds, government 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 IMoTB 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 retums). 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-retums 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 23 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%. OBS Studio b. (5 Points) Plot the investment opportunity set with portfolio non un wu 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.k.a. tangency portfolio. What are weights, portfolio return and risk, and Sharpe ratio for this tangency portfolio? (10 Points) Repeat the above process of the two-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. 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 return, 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 II. Optimal Risky Portfolio, Efficient Frontier, and Capital Allocation (60 Points) Put yourself in the shoes of a buy-side 1 investment manager in charge of market research and asset allocation. Suppose one of your clients comes to you with the following question: "How can I best allocate my funds across the following four asset choices: stocks, corporate bonds, government 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 IMoTB 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 retums). 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-retums 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 23 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%. OBS Studio b. (5 Points) Plot the investment opportunity set with portfolio non un wu 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.k.a. tangency portfolio. What are weights, portfolio return and risk, and Sharpe ratio for this tangency portfolio? (10 Points) Repeat the above process of the two-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. 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 return, 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