Reference no: EM133567766

Assignment: Financial Modeling & Forecast Project

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

1. Using EXCEL's Data Table Feature, create a one-way data table that determines the different means and standard deviations for combinations of Portfolio 1 and Portfolio 2 by varying the proportion of Portfolio 1.

2. Graph the combinations of the portfolios from the one-way data table and add the individual asset means and standard deviations to the graph.

3. Using EXCEL's Text Box feature, explain why the envelope portfolios may or may not be on the efficient frontier?

4. Using Solver or Goal Seek, find an envelope portfolio that would yield an expected return of 6%. What would be the corresponding portfolio standard deviation?

5. Using Solver or Goal Seek fined two envelope portfolios that would yield a standard deviation of 10%. What would be the corresponding expected returns for each of these portfolios?

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

II. Use the Excel file Data for Two Stocks to determine the following:

1. Using EXCEL's Data Table Feature, create a one-way data table that determines the different means and standard deviations for portfolios consisting of combinations of Stock A and Stock B by varying the correlation coefficient value between Stock A and Stock B through the full range of possible correlation coefficient values. Use increments of 0.20 for the possible correlation coefficient values.

2. Graph the correlation coefficients, the means, and the standard deviations of the portfolios from the one-way data table. Be sure to include a title for the graph and label the axes.

3. Use Excel's Text Box Feature to explain how the portfolio means are affected by changing the correlation coefficient values.

4. Use Excel's Text Box Feature to explain how the portfolio standard deviations are affected by changing the correlation coefficient values.

III. Use the Variance - Covariance matrix in the Excel file: Efficient Portfolios Data.
Construct a model to determine the following:

1. Calculate an envelope portfolio assuming the risk-free rate is 6%.

2. Calculate an envelope portfolio assuming the risk-free rate is 12%.

3. Using EXCEL's Data Table Feature, create a one-way data table that determines the different means and standard deviations for combinations of Envelope Portfolio 1 and Envelope Portfolio 2 by varying the proportion of Portfolio 1 from -5 to +5 in increments of 0.50.

4. Graph the combinations of the portfolios from the one-way data table and add the individual asset means and standard deviations to the graph.

5. Provide a title on the graph and label the axes of the graph.

6. Using EXCEL's Text Box feature, explain whether the portfolio combinations could be on the efficient frontier.

7. The efficient frontier is said to provide "a set of equally good portfolios" for investors. Use EXCEL's Text Box feature to explain the meaning of this statement?

IV. The Excel file Stock Data contains monthly return data for seven stocks.

1. Use these returns and the Matrix of Excess Returns to compute the Variance-Covariance Matrix for these seven stocks. (Do not use the varcovar VBA function).

2. Use the Variance - Covariance Matrix for these seven stocks to compute the individual stock proportions for the Global Minimum Variance Portfolio (GMVP).

3. Calculate the Expected Return and Risk (Standard Deviation) for the Global Minimum Variance Portfolio (GMVP).

V. The Excel file Correlation Matrix Data contains monthly return data for eight (8) stocks.

1. Compute the Correlation Matrix for the eight stocks.

2. Using EXCEL's Text Box feature explain/identify

a. The characteristics of the Correlation Matrix.
b. What the correlation coefficient measures.
c. How the correlation coefficient values are used in portfolio theory, i.e., the role of the correlation coefficient in portfolio theory.

VI. Provide Benninga's definitions for the following:

1. Envelope Portfolio
2. Efficient Portfolio
3. Feasible Portfolio
4. Infeasible Portfolio

VII. Solve the following system of equations using Matrix Algebra.

3A - 6B- 8D+4E+ 6F +4G= 65
5A+2B+ 6C -4D+ 3E + 2G= 59
2A-7B + 4C +3D + 7E+ 3G = 34
6A - 4B-3D+ 4F + 3G= 28
7A - 5C - 4D + 3E - 7F = 72
2A + 3B + 5D + 8E - 4F + 9G = 85
4A + 2B + 3C - 4D + 3F + 2G = 29

VIII. The Excel file Portfolio Weights Data contains information about two portfolios.

1. Using Matrix Algebra compute the portfolio asset weights for Portfolio C if Portfolio C has 44% of its funds invested in Portfolio A and 56% of its funds invested in Portfolio B.

2. Using EXCEL's Data Table Feature, create a one-way data table to compute the Portfolio C asset weights by varying the proportion of funds invested in Portfolio A from 0 to 1 in increments of 0.20. Portfolio C will consist of the appropriate proportion of funds invested in Portfolio A and the remainder of the funds invested in Portfolio B.

IX. Describe the characteristics of a variance-covariance matrix. Also, explain how to read a variance-covariance matrix, what the individual values in a variance-covariance matrix represent, what the individual values in a variance-covariance matrix measure, and how the variance-covariance matrix is used. Use EXCEL's Text Box feature when you input your response.

X. The EXCEL file Stock Return Probability Distribution contains the probability distribution for a stock.

1. Compute the Expected Return for this stock.
2. Compute the Risk for this stock.