1. Use the Excel file Asset Allocation Data to determine the following:

a.Variances for the individual assets

b. Standard deviations for the individual assets

c.Covariances between each pair of Assets (e.g., Covariance between Asset 1 and Asset 2, etc.)

d. Portfolio weights for both Portfolio 1 and Portfolio 2

e. Means (Expected Returns) for both Portfolio 1 and Portfolio 2

f.Variances for both Portfolio 1 and Portfolio 2

g.Standard Deviations for both Portfolio 1 and Portfolio 2

h. Covariance between Portfolio 1 and Portfolio 2

i.Correlation between Portfolio 1 and Portfolio 2

j.Create a one-way data table using EXCELs Data Table feature that determines the different means and standard deviations for combinations of Portfolio 1 and Portfolio 2 by varying the proportion of Portfolio 1. For Portfolio 1 use proportions ranging from -4 to + 4 in increments of 0.5.

k.Graph the combinations of the portfolios from the one-way data table and add the individual asset returns to the graph.

l.Could the portfolio combinations be on the efficient frontier? Why or why not?

m. Using Solver, find an envelope portfolio that would yield an expected return of 10%. What would be the corresponding portfolio standard deviation?

n. Using Solver, find two envelope portfolios that would yield a standard deviation of 40%. What would be the corresponding expected returns for each of these portfolios?

o. Using Solver, find the envelope portfolio that would yield the smallest (minimum) standard deviation. What would be the corresponding expected return for this portfolio?

Asset Allocation Data
	Variance-covariance matrix							Means			Asset	Port. 1 Investment	Port. 2 Investment
Asset	1	2	3	4
1	0.2000	0.0007	0.0005	0.0009				7.0000%			1	$400	$1,800
2	0.0007	0.3000	0.6000	0.0064				12.0000%			2	$500	$300
3	0.0005	0.6000	0.5000	0.0070				10.0000%			3	$600	$600
4	0.0009	0.0064	0.0070	0.6000				20.0000%			4	$1,500	$300