An investor puts $15,000 in each of four stocks, labeled A, B, C, and D. The table below contains means and standard deviations of the annual returns of these 4 stocks.

A: Mean = .15 Standard Deviation = .05
B: Mean = .18 Standard Deviation = .07
C: Mean = .14 Standard Deviation = .03
D: Mean = .17 Standard Deviation = .06

1) Assume that the returns of these 4 stocks are independent of each other. Use a spreadsheet to calculate the mean and standard deviation of the total amount that this investor earns in 1 year from these 4 investments as a function of the information in the table.

2) Let "v" denote a market volatility index. The standard deviation of a stock "n" is now v*std dev of n, where std dev is its base volatility level. The volatility index impacts all stocks in the same way.

If v=1 then the std dev of the 4 stocks A, B, C, D are as shown in the table. In general, v can be lower than 1 (low volatility) or higher. For example, if v=1.1, then stock A has volatility 1.1*.05, stock B has volatility 1.1*.07, and so on.

Modify the spreadsheet above to accommodate the possibility of v not equal to 1. Use excel data table (1 dimensional) to check the sensitivity of the portfolio's std dev as a function of v. Vary v from .05 to 1.5 in .05 increments.