Complete the following excel document. Instructions are all included within the excel document

Table 6.2: Construction of optimal portfolios using the index model Input list Benchmark Portfolio A. Input data Risk premium Standard deviation Sharpe ratio Alpha Beta Residual standard deviation Information ratio = alpha/residual SD Alpha/residual variance (S&P 500) 0.7 4.31 0.16 Portfolio Construction B. Optimal portfolio with Google only in active portfolio Performance data: Sharpe ratio = SQRT(index Sharpe^2 + Google information ratio^2) Composition of optimal portfolio: w0 = (alpha/residual variance) / (index risk premium / index variance) w* = w0 / (1+w0(1-beta)) C. Optimal portfolio with Google and Dana in the active portfolio Composition of the active portfolio w0 of stock (alpha/resdidual SD) w0/Sum w0 of analyzed stocks Performance of the active portfolio alpha = weight in active portfolio x stock alpha beta = weight in active portfolio x stock beta Residual variance = square weight x stock residual variance Residual SD = sqrt(active portfolio residual variance) Information ratio = active portfolio alpha/residual SD Performance of the optimal portfolio Sharpe ratio Composition of optimal portfolio 0 w w* Weight of active portfolio x weight of stock in active portfolio Index he index model Active Portfolio Google Dana 2.20 11.39 not applicabe 1.04 1.65 9.01 1.74 10.49 0.75 1.41 8.55 0.3399697357 Active portfolio (sum) Active Google Dana