Show all formulas in excel table when needed for certain questions!!! There is nothing else to update on this question. All is provided. Solver function is simply a what if analysis on excel mentioned below, but ignore if unfamiliar with and simply solve using excel however you are familiar with.

You are discussing your 401(k) with Dan Ervin when he mentions that Sarah Brown, a representative from Bledsoe Financial Services, is visiting East Coast Yachts today. You decide that you should meet with Sarah, so Dan sets up an appointment for you later in the day.

When you sit down with Sarah, she discusses the various investment options available in the companys 401(k) account. You mention to Sarah that you researched East Coast Yachts before you accepted your new job. You are confident in managements ability to lead the company. Analysis of the company has led to your belief that the company is growing and will achieve a greater 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 percent of your 401(k) account in East Coast Yachts stock.

East Coast Yachts uses Bledsoe Financial Services as its 401(k) plan administrator. Here are the investment options offered for employees:

Company Stock One option in the 401(k) plan is stock in East Coast Yachts. The company is currently privately held. However, when you interviewed with the owner, Larissa Warren, she informed you the company was expected to go public in the next three to four years. Until then, a company stock price is simply set each year by the board of directors.

Bledsoe S&P 500 Index Fund This mutual fund tracks the S&P 500. Stocks in the fund are weighted exactly the same as the S&P 500. This means the fund return is approximately the return on the S&P 500, minus expenses. Because an index fund purchases assets based on the composition of the index it is following, the fund manager is not required to research stocks and make investment decisions. The result is that the fund expenses are usually low. The Bledsoe S&P 500 Index Fund charges expenses of .15 percent of assets per year.

Bledsoe Small-Cap Fund This fund primarily invests in small-capitalization stocks. As such, the returns of the fund are more volatile. The fund can also invest 10 percent of its assets in companies based outside the United States. This fund charges 1.70 percent in expenses.

Bledsoe Large-Company Stock Fund This fund invests primarily in large-capitalization stocks of companies based in the United States. The fund is managed by Evan Bledsoe and has outperformed the market in six of the last eight years. The fund charges 1.50 percent in expenses.

Bledsoe Bond Fund This fund invests in long-term corporate bonds issued by U.S.domiciled companies. The fund is restricted to investments in bonds with an investment-grade credit rating. This fund charges 1.40 percent in expenses.

Bledsoe Money Market Fund This fund invests in short-term, highcredit quality debt instruments, which include Treasury bills. As such, the return on the money market fund is only slightly higher than the return on Treasury bills. Because of the credit quality and short-term nature of the investments, there is only a very slight risk of negative return. The fund charges .60 percent in expenses.

10-Year Annual Return Standard Deviation

Bledsoe S&P 500 Index Fun 9.18% 20.43%

Bledsoe Small-Cap Fund 14.12 25.13

Bledsoe Large-Company Stock Fund 8.58 23.82

Bledsoe Bond Fund 6.45 9.85

Assume the risk-free rate is 3.2 percent. The correlation between the Bledsoe bond fund and large-cap stock fund is .15. Note that the spreadsheet graphing and solver functions may assist you in answering the following questions.

1. Considering the effects of diversification, how should Sarah respond to the suggestion that you invest 100 percent of your 401(k) account in East Coast Yachts stock?

2. Sarahs response to investing your 401(k) account entirely in East Coast Yachts stock has convinced you that this may not be the best alternative. Because you are a conservative investor, you tell Sarah that a 100 percent investment in the bond fund may be the best alternative. Is it?

3. Using the returns for the Bledsoe Large-Cap Stock Fund and the Bledsoe Bond Fund, graph the opportunity set of feasible portfolios.

4. After examining the opportunity set, you notice that you can invest in a portfolio consisting of the bond fund and the large-cap stock fund that will have exactly the same standard deviation as the bond fund. This portfolio will also have a greater expected return. What are the portfolio weights and expected return of this portfolio?

5. Examining the opportunity set, notice there is a portfolio that has the lowest standard deviation. This is the minimum variance portfolio. What are the portfolio weights, expected return, and standard deviation of this portfolio? Why is the minimum variance portfolio important?

6. A measure of risk-adjusted performance that is often used is the Sharpe ratio. The Sharpe ratio is calculated as the risk premium of an asset divided by its standard deviation. The portfolio with the highest possible Sharpe ratio on the opportunity set is called the Sharpe optimal portfolio. What are the portfolio weights, expected return, and standard deviation of the Sharpe optimal portfolio? How does the Sharpe ratio of this portfolio compare to the Sharpe ratios of the bond fund and the large-cap stock fund? Do you see a connection between the Sharpe optimal portfolio and the CAPM? What is the connection?