Problem Description.

You have saved up some money and want to invest in stocks for the first time, and you have decided to invest in three high flying stocks: BA Century Inc $($BACE$),$ Sheppard Equality Inc $($SHEQ$)$ Mextronic Materials $($MXM$).$ You want to create a spreadsheet that will allow you to figure out what the expected return and standard deviation of your two$-$stock portfolio will be if you buy $100$ shares of BACE, $150$ shares of SHEQ, and $200$ shares of MXM$.$

From the $$Data$$ worksheet, calculate the historical monthly returns and the historical quarterly dividends of the three stocks from $2018-12-31$ to $2019-12-31.$

Create a $$Portfolio Calculations$$ section to calculate the expected returns, variances, covariances, and correlations of the stocks. Use these to determine the expected return, variance and standard deviation of your portfolio. $($Note that you will need to calculate the portfolio weights using the stock prices on $2019-12-31.)$ Use the INDIRECT function for your references

Exercise $1.$

Create a spreadsheet model that will utilize the data in the excel file to calculate both individual stock expected returns, variances and standard deviations, as well as portfolio expected returns, variances, covariances and standard deviations. The portfolio model should also be able to obtain $$efficient portfolios$.$ Your model should also generate a mean$-$variance plot of the efficient portfolios $($Standard Deviation on the X$-$axis and Target Expected Returns on the Y$-$axis$).$ Use target returns going from $5\%$ to $12\%$ in increments of $0\mathrm{.}25\%$

Exercise $2.$

Use the completed spreadsheet to answer all questions.

What is the monthly expected return and the monthly variance of returns for BACE, SHEQ and MXM$?$

What is the monthly expected return and the monthly standard deviation of your portfolio?

Suppose you wanted a portfolio with an expected return of $7\mathrm{.}5\%.$ What is the efficient portfolio of these three stocks that will provide that expected return? Your answer should be both in terms of weights and in terms of the number of shares of each stock