This portfolio analysis has three major components that you will submit. It is imperative that you not only review the requirements listed but also view the rubric for this assignment. You will upload an Excel Spreadsheet and a one-page summary document. Case Details: Mr. Bailey has approached you regarding his current portfolio of stock investments worth $250,000. Mr. Bailey believes he has created a well-diversified portfolio for his financial profile. Mr. Bailey's current investment portfolio consists of of Johnson & Johnson (JNJ), Microsoft (MSFT), Chipotle (CMG), Nike (NKE), Exxon Mobile (XOM), Ford (F), and Pepsi (PEP) on the traditional stock side. He also owns several alternative investments--Bitcoin (BTC), Gold (GLD), Vanguard Mid-Cap Value ETF (VNQ), and Invesco QQQ Trust (QQQ).

The annual returns for these five companies over the past five years and Mr. Bailey's total investment (in dollars) for each stock are provided.

Submit Excel Spreadsheet:

The annual returns Mr. Bailey achieved over the past five years are already provided. That will be your baseline for the following calculations.

Prepare a spreadsheet and calculate the following:

1. Calculate the average for the five-year returns of each stock/investment.
2. Calculate the five-year standard deviation of each stock/investment.
3. Calculate the non-weighted average/expected return of Mr. Bailey's portfolio.
4. Calculate the weighted average return of Mr. Bailey's portfolio.
5. Create a Sample Variance/Covariance Matrix.
6. Calculate the standard deviation of Mr. Bailey's portfolio using the Sample Variance/Covariance Matrix.

Submit a One-Page Summary Document:

Summarize and interpret your findings based on the following questions:

1. What are your initial thoughts on Mr. Bailey's portfolio?
2. Do any of Mr. Bailey's investments concern you?
3. What about portfolio diversification? (Hint: think about correlation and its effect on a portfolio)
4. Which investment is performing the best annually vs. five-year return?
5. What is the riskiest investment in the portfolio?
6. What are your initial ideas for rebalancing Mr. Bailey's portfolio?
   1. What investment(s) would you remove?
   2. Any initial ideas on new investments you may recommend for the portfolio?