Please do all in excel$$ and send to me asap. Download to a spreadsheet the last $30$ years of monthly adjusted stock prices for Occidental

Petroleum $($OXY$)$ and Chevron $($CVX$).$ You can download the data from

finance.yahoo.com.

a$)$ Calculate the monthly total returns of each stock.

i$)$ Calculate $3-$year rolling standard deviations of returns. Find the annualized standard

deviations by multiplying by the square root of $12.$ Use the Excel function STDEVP.

Plot the annualized $3-$year rolling standard deviations over time.

ii$)$ Calculate $3-$year rolling mean of the returns. Find the annualized mean by multiplying

by $12.$ Plot the $3-$year rolling annualized means over time.

iii$)$ Which statistic, the mean or the standard deviation, is more stable?

b$)$ For the following exercises use all the $30$ years of monthly data. Use the Excel function

CORREL to calculate the correlation coefficient between the monthly returns of the

stocks.

c$)$ Calculate the annualized standard deviation and mean of each stock return. Calculate the

standard deviation of returns for a portfolio with equal investments in the two stocks.

d$)$ If there is a risk$-$free asset with risk$-$free rate equal to $5\mathrm{.}25\%,$ find the feasible set and the

efficient frontier. Assume that the investment universe consists of the two stocks and the

risk$-$free asset. You can use the historical means, standard deviations, and correlations.

e$)$ Using Markowitz theory, suppose you want to construct an optimal portfolio that will

have a $10\%$ standard deviation. What would be the optimal weights in the stocks and the

risk$-$free asset? What would be the expected return of that optimal portfolio?

f$)$ Obtain the market capitalization of the two stocks. Using these values, calculate the

proportion of each market cap of the total market cap $($sum of the market cap of the two

stocks$).$ Construct a portfolio with those proportions. Called this new portfolio asset E$.$

g$)$ Sharpe's model. Assume that there is a risk$-$free asset with rate equal to $5\mathrm{.}25\%.$ Find the

mix $($weights$)$ of asset $E$ and the risk$-$free asset that has an $10\%$ standard deviation. What

are the portfolio weights in the stocks $($each one$)$ and risk$-$free asset that produce that

mix? What is the expected return of the mix?

h$)$ Compare the weights in e$)$ to the ones in g$).$ Are they the same?

i$)$ Which model, Markowitz' or Sharpe's model, would you use if you wanted to maximize

your expected return? Why?