Assignment 2 Portfolio Optimization
Instructions for the Project
Students are required to create a model that is capable of optimizing a portfolio of 5 stocks.
The model should contain the following:
- The optimal portfolio for the selected stocks should be identified o This is the portfolio that maximises the Sharpe ratio
- The minimum variance portfolio for the selected stocks should be identified
- The model should be capable of allowing the user to prevent short-selling and also to set different levels at which short selling may occur o Short-selling is the situation where a portfolio may contain a negative weight on a security (essentially you are selling a security you do not own) with the additional funds received from the sale being used to boost ownership in other assets. As a result the total portfolio should still come to a weight of 100%. o Where levels of short selling are allowed, this should limit the entire portfolio, not just individual stocks i.e. the portfolio may have a total of 10% short selling.
- The model should have a graph that shows individual stocks, the minimum variance portfolio and the optimal portfolio.
- All inputs (if any) and outputs should be shown on a summary page
- The model should conform to the rules of good design
The calculator will only be marked if it is built within the spreadsheet file optimization.xlsm available from Blackboard. Students are expected to be able to explain or demonstrate any features or techniques in their assignments if requested.
To receive a C- Grade, students must ensure their model meets the following requirements:
|
Model |
Model is capable of optimizing a 5 stocks portfolio and identifying both the highest Sharpe ratio portfolio and the minimum variance portfolio where no constraints are placed on short selling | |
|
Layout |
Calculator conforms to the following rules of good design o Contains a summary page showing all relevant information that is free from unnecessary calculations o Formulas linked so that the variance/covariance matrices and all calculations linked to them update for changes in data (assumes replacing one stock with another with the same number of observations). | |
|
Features |
Model should contain a graph that plots the individual stocks, the minimum variance point and the optimal portfolio |
To receive a B Grade, students must ensure their model meets the following requirements:
|
Model |
Model is capable of optimizing a 5 stocks portfolio and identifying both the highest Sharpe ratio portfolio and the minimum variance portfolio where no constraints are placed on short selling Model should be capable of handling a prohibition on short sales Model contains manually calculated optimal portfolios users can select from [i.e. short selling (10%, and 20%) versus no short selling optimal]. |
|
Layout |
Calculator conforms to the following rules of good design o Contains a summary page showing all relevant information that is free from unnecessary calculations o Formulas linked so that the variance/covariance matrices and all calculations linked to them update for changes in data (assumes replacing one stock with another with the same number of observations). o The interface is intuitive and easy to use without excess options |
|
Features |
Model employs suitable Excel control features that are linked correctly and work Model employs VBA programming to some degree, VBA code must be able to be run efficiently from the worksheet and must be predominantly coded by the student rather than recorded in a macro. Model should contain a graph that plots the individual stocks, the minimum variance point, and optimal portfolio for the appropriate option selected. |
To receive an A Grade, students must ensure their model meets the following requirements:
|
Model |
Model is capable of optimizing a 5 stocks portfolio and identifying both the highest Sharpe ratio portfolio and the minimum variance portfolio, where no constraints are placed on short selling Model should be capable of handling a prohibition on short sales Model contains optimal portfolios calculated by using VBA programming users can select from [i.e. short selling (10%, 20%, and unlimited) versus no short selling optimal]. |
|
Layout |
Calculator conforms to the following rules of good design o Contains a summary page showing all relevant information that is free from unnecessary calculations o Model is capable of handling a change in the underlying data (the replacement of one or more stocks with an equal number of replacements with the same number of observations) o The interface is intuitive and easy to use without excess options |
|
Features |
Model should use considerable VBA programming to conduct the optimization (calling solver from VBA is a suitable solution) VBA code must be able to be run efficiently from the worksheet (code linked via Excel control) and must be predominantly coded by the student rather than recorded in a macro. Model should employ suitable advanced Excel features required to make the model intuitive and easy to use i.e. Excel Control features Model should contain a graph that plots the individual stocks, the minimum variance point, the optimal portfolio for the appropriate option selected. |
