Please refer to HW $2$ Data posted in the Assignments folder. The file contains monthly price

data for the S&P $500$ index $($i$.$e$.,$ the market portfolio$),$ and for three stocks: Apple, Nike, and

Target. Use the tables$/$charts from the Excel spreadsheet to report your answers.

a$.$ Using the monthly prices, compute the monthly returns for the index and stocks for all

months in the data. In the homework you will hand in$,$ report only the five most recent

monthly returns for the monthly prices that you were provided.

b$.$ For the index and stocks compute the expected return, standard deviation, variance,

correlation and covariance with the market portfolio $($S&P $500),$ as well as their beta,

using $5$ years of returns. The beta can be computed from covariance and variance data.

c$.$ Beta can also be obtained from a graph.

$$ Plot Nike$$s monthly returns on the y$-$axis and the S&P $500$ returns on the

horizontal axis for $5$ years of returns. Use the $$marked scatter$$ option in Excel$$s

scatter plot facility to do so$.$

$$ Once you have the plot, move your cursor on the graph, right click on any data

point, and choose $$Add Trendline...$.$ In the $$Trendline Options$$ menu choose

$$Linear$$ and tick the $$Display equation on chart$$ and $$Display R$-$squared on

chart$,$ at the bottom$\mathrm{.}1$ You will see a line on the graph with an equation on top.

$$ The coefficient in front of x is the slope of the line. Confirm that the slope of the

line is the same number as the beta you computed in part $($b$).$

d$.$ The line you generated in part $($c$)$ should have a value for R$2$ displayed below the

equation of the line. R$2$ gives you the percentage of total variation in Nike$$s returns that

can be explained by variation in market returns, i$.$e$.,$ by by market risk. Let$$s compute

this:

$$ You can find how much market risk Nike$$s stock has by multiplying R$2$ by the

variance of Nike$$s returns, and then taking the square root of that number.

$$ Alternatively, multiply Nike$$s $$ with the standard deviation of market

returns. The number you obtain should be the same as the one you obtained

above using R$2.$

e$.$ Repeat $($b$)$ using the $3$ most recent years of returns in the dataset. Do the betas remain the

same?

f$.$ Construct portfolios by combining Nike and Target in different proportions, as shown in

the spreadsheet, and compute the expected return and standard deviation of each

portfolio. You should be using $5$ years of returns, as in $($b$),$ and $\backslash$rho should be constant.

g$.$ Plot the expected return $($y$-$axis$)$ and standard deviation $($x$-$axis$)$ for all portfolios in par$($f$)$