2. Monthly adjusted closing price of 12 individual stocks, their market cap values, their log returns, monthly returns on the market index, and risk-free return on T-Bills are recorded in the Excel file name Stock_data. Please download it to perform the following exercises:

a. Calculate the monthly mean, variance, and standard deviation of each asset and then compute the annual mean variance and standard deviation. Save it in the sheet named Data_sum.

b. Do the first step regression by regressing each stock on market index (MKT_Ret) and save the values of Alpha, Beta, and R-square (Using formula only). Save the first-step regressing parameters in the sheet named 1step_Regress.

c. Do the second pass regression by regressing the average return on stocks over the beta of stocks. Use data analysis to show the regression results (Use data analysis to show regression result). Identify the values of coefficients and explain their significance by comparing the t-statistics with the critical value (with 95% confidence level t-critical =1.96). Write down the linear model with the coefficients obtained from the second pass regression. Save the regression results in the sheet name 2step_regress.

d. Find the variance-covariance matrix among the individual stocks and save it in the sheet named Varcov.

e. In the Stock_data, there is a sheet with data on risk-free return (data on T-Bills). Find the expected risk-free return and use it as a CONSTANT value. Then use the variancecovariance matrix and average returns of stocks to find the efficient portfolio proportions (using the proposition 1 formula). Use these proportions to find the portfolio mean, sigma, and beta, then use these values to compute the Sharpe ratio and Treynor ratio. Save it in the sheet named Efficient Portfolio.

f. Copy the Efficient Portfolio in a separate sheet, and find the largest long and largest short positions. Then use Solver to change any negative proportions of assets to positive ones by maximizing the Sharpe ratio. Find the portfolio mean, sigma, and beta and compare them with the results in part (e). Save this sheet as Optimization.