Create an Excel optimization file with good modeling techniques. Using Solver and Solver Table.

Not sure how to get started on this problem any help would be appreciated.

Thank you.

Here is the problemt:

Portfolio Optimization Problem

The annual returns for three companies over the last 12 years are given below, where the return for year n is defined as:

(closing price,n) – (closing price,n-1) + (dividends,n) / (closing price,n-1)

Year IBC NMC NBS

1 11.2% 8.0% 10.9%

2 10.8% 9.2% 22.0%

3 11.6% 6.6% 37.9%

4 -1.6% 18.5% -11.8%

5 -4.1% 7.4% 12.9%

6 8.6% 13.0% -7.5%

7 6.8% 22.0% 9.3%

8 11.9% 14.0% 48.7%

9 12.0% 20.5% -1.9%

10 8.3% 14.0% 19.1%

11 6.0% 19.0% -3.4%

12 10.2% 9.0% 43.0%

Part a: Using Excel, determine for each company the estimated meanreturn and standard deviation. Also, calculate the estimated correlations and covariances between the companies’ returns.

Hint: Use the Excel functions AVERAGE, STDEV, and CORREL to determine the means, standard deviations and correlations. The covariances can then be calculated from the correlations (as in the class example). Click on fx in the Formula Bar for help with using these Excel functions.

Part b: Using the three stocks, a financial planner would like to create the least risky portfolio with minimum of a 12% expected return. Implement a spreadsheet model to determine the desired portfolio. Discuss the solution and highlight exactly how risk is minimized in the optimal portfolio, while achieving the desired return.

Part c: Either by repeatedly re-running the model, or by using SolverTable, create the chart for the efficient frontier for this portfolio optimization problem. Explain the usefulness of such a chart.

Step 1:

We put the data into excel.

Step 2:

Compute the mean, standard deviation and covariance matrix. To calculate the covariance matrix, we use the excel function COVAR (check the cells of the covariance matrix to see how I used this function.

Step 3 (part b).

To find the optimal portfolio, we suppose that we buy a amount of IBC, b amount of NMC and c amount of NBS. Here a, b and c could be any real numbers but they have to add up to 1 (meaning that the sum of a b and c must be 100% of your portfolio, …

Portfolio Optimization Problem