Data in the excel template

Please use the excel template sheet here:

http://www66.zippyshare.com/v/iRIU1hbo/file.html

Diversification is the risk reduction that occurs from creating portfolios of investments with less than perfect correlation. Diversification results when the unique or unsystematic risk of one investment offsets the unique or unsystematic risk of another investment. We should be able to identify the benefit of diversification by comparing the risk reduction that occurs from combing two investments in common stock that are highly correlated with the risk reduction that occurs from combining two investments in common stock that are less correlated.

For this exercise you will use the daily returns from owning Apple Computer (AAPL), Google (GOOG) and Walmart (WMT) over the calendar year of 2011. Intuitively it seems that Apple and Google would share more risks than Apple and Walmart. If this is true the correlation coefficient between Apple and Google should be higher than the correlation coefficient between Apple and Walmart.

1 -In the posted template you should calculate the average daily return, median daily return, standard deviation of daily returns, and variance of daily returns for each of the three stock investments.

I have also included the daily returns for a portfolio that is invested in equal dollar amounts of Apple & Walmart and the daily returns for a portfolio that is invested in equal dollar amounts of Apple & Google.

2 - In the posted template you should calculate the average daily return, median daily return, standard deviation of daily returns, and variance of daily returns for each of these two portfolios.

If there is no reduction of risk from diversification the average standard deviation of the two stocks should be the same as the standard deviation of the portfolio combining the two stocks.

3 - Use the average standard deviation compared to the actual standard deviation of each of the pairs of stocks to measure the percentage reduction in risk achieved through diversification.

Use Excel to calculate the correlation coefficient between Apple and Walmart and the correlation coefficient between Apple and Google.

4 - Using the correlation coefficient and the formula for the analytical variance of the combination of 50% weights in each of the stocks found on page 31 of the Risk & Return Notes, calculate the analytical variance and standard deviation of the portfolios.

5 - Comment on whether or not the risk reduction is consistent with the measures correlation coefficients.