## Question

# 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

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 |

## Step by Step Solution

There are 3 Steps involved in it

### Step: 1

### Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

### Step: 2

### Step: 3

## Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started