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.
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 mean return 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.
We put the data into excel.
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
Setting up an investment optimization problem
Financial Analysts, Inc. is an investment firm that manages stock portfolios for a number of clients. A new client has just requested that the firm handle an $80,000 portfolio. As an initial investment strategy the client would like to restrict the portfolio to a mix of the following two stocks:
Stock: Oil Alaska
Estimates annual return/share: $6
Maximum possible investment: $50,000
Stock: Southwest Petroleum
Estimates annual return/share: $4
Maximum possible investment: $45,000
Let x = number of shares of Oil Alaska and y = number of shares of Southwest Petroleum
Part A: Develop the objective function, assuming that the client desires to maximize the total annual return
Part B: Show the mathematical expression for each of the following three constraints:
1. Total investment funds available are $80,000
2. Maximum Oil Alaska investment is $50,000
3. Maximum Southwest Petroleum investment is $45,000
Note: this problem only sets up the problem. It does not solve for x and y.View Full Posting Details