Complete the analytical portion of the case assignment in the excel template which can be

found in Canvas. Formulas must reference parameters in other cells using absolute or

relative cell references. DO NOT HARD CODE ANY NUMBERS IN YOUR EXCEL FORMULAS.

$1)$ In the space provided in the Excel spreadsheet, plot the opportunity set for Portfolios A &

B$,$ the CAL & the indifference curve. To do this you will need to calculate the missing

information in the table found in the Excel spreadsheet that accompanies the case using

weights of portfolio A & B in $10$ percentage point increments. This requires knowing how

to program formulas in Excel using absolute and relative cell references from the data

provided. $($The table below already exists in the Excel file$).$

Determine the optimal risky portfolio $($e$.$g$.$ the optimal allocation of A & B$)$ using the

concepts from Modern Portfolio Theory. The approximate optimal allocation can be

determined using the table in Excel like the one shown above.

You do not need to plot the CAL $$ rather you can manually draw the CAL on the Excel

graph $($using Insert Shapes window in Excel$)$ starting at the risk$-$free rate to the tangent

point.

$2)$ Find the optimal complete portfolio graphically using the client$$s indifference curve.

Plot an indifference curve on the same graph you just created using the utility function

formula from Chapter $6.$ To make things easier, you can use the same portfolio risk

numbers from the table above. However, you will first need to calculate the utility of

the optimal complete portfolio in the space provided in the Excel spreadsheet. The

risk aversion coefficient $($A$)$ has been provided. Plot the indifference curve AND the

opportunity set of risky assets on the same graph.

Determine the optimal complete portfolio. You did this graphically above, but you

need to calculate the precise composition of the optimal risky portfolio $($recall ymaxU$)$

and T$-$Bills $(1-$ymaxU$).$

$3)$ In the space provided in the Excel spreadsheet, plot the CAPM regressions of Portfolio

A and Portfolio B $($separate graphs$).$ The market portfolio is represented by the S&P

$500$ and the risk$-$free rate is represented by $90-$day T$-$Bill. Calculate the following for

each portfolio using the following methods in the spaces provided in the spreadsheet:

a$.$ Beta: using the Excel slope function

b$.$ Alpha: using the Excel y$-$intercept function. The return data is monthly, so the alpha

via y$-$intercept function of the CAPM regression represents a MONTHLY alpha.

c$.$ Annualized alpha: using the CAPM formula and inputting annualized returns of each

portfolio $($A & B$),$ S&P $500,$ risk free rate $($T$-$Bills$)$ and the beta calculated in part $$a$$

above. Note that the annualized alphas of A & B are NOT the same as the monthly

alphas multiplied by $12.$ The two may be close, but not exactly equal.

d$.$ Beta: using the beta formula. Recall the formula for beta which we derived and can

be found in the Module $6$ ppt lecture, Module $6$ & $7$ Notes, & the textbook. If you

did parts $$a$$ and $$d$$ correctly, the two beta calculations will be EXACTLY the same.