Please use Excel and Solver to show steps and answers!

You are discussing your retirement plan with Emma Li when she mentions that Maureen OBrien, a representative from TD Financial Services, is visiting your office today. You decide that you should meet with Maureen, so Emma sets up an appointment for you later in the day. When you sit down with Maureen, she discusses the various investment options available in the companys retirement plan. You mention to Maureen that you researched your new employer before you accepted your new job. Analysis of the company has led to your belief that the company is growing and will achieve a grater market share in the future. You also feel you should support your employer. Given these considerations, along with the fact that you are a conservative investor, you are leaning toward investing 100% of your retirement amount in the company you now work for. Assume the risk-free rate is the historical average T-Bill rate of 3.4%. The correlation between the TD Large-Cap Stock Fund and the TD Bond Fund is 0.15. Note that the spreadsheet graphing and Solver in Excel may assist you in answering the questions.

The Standard Deviation and Average Return of the funds over the past 10 years

	Average Return	Standard Deiviation
Stock A	9.18%	20.43%
Stock B	14.12%	25.13%
Stock C	8.58%	23.82%
Stock D	5.45%	9.85%

a. What are the portfolio weights, expected return, and standard deviation of the tangency portfolio? Using this information graph the capital allocation line alongside the opportunity set of risky assets. How does the Sharpe ratio of the tangency portfolio compare to the Sharpe ratios of the bond fund and the large-cap stock fund?

b. Compute an efficient portfolio of both risky funds and a risk-free asset that would be preferred by an investor that has a low volatility (risk) target of (RP ) = 5%. What is the optimal portfolio for this investor? What about a more risk tolerant investor who has a high expected return target of E(RP ) = 12%. What is the optimal portfolio for this other investor? Report the composition, the expected return, and the volatility of these two portfolios. Comment on the ratio of investment in the TD Large-Cap Stock Fund and the TD Bond Fund for both investors.