Recent Question/Assignment

This assignment is to be completed in groups (of maximum 3 members). The modelling work should be submitted online in the Assignment Folder as a single MS Excel file with the required information in clearly labelled separate sheets. In addition, you are also required to submit a MS Powerpoint file that summarises your models and results. In summary, two files should be submitted – an excel model and a powerpoint file.
The assignment is in three main sections: Preliminary Work, Optimisation Models and Validation & Conclusion. The requirements of each part are detailed below.
The breakdown of marks (total is 40) is given on this document and a separate Rubric file. The assignment contributes 20% towards the total assessment for this unit.
Deadline for submission: 8:00AM Monday 15 April 2019
Extensions must be negotiated at least one week prior to the above deadline by email. Requests for extensions made within 1 week of the deadline will only be considered if progressive work to date is submitted with the request. Deakin policy for late submission: 5% penalty is applied for every day of late submission up to 5 days (without an agreed extension). Submissions more than 5 days late, without an agreed extension will not be marked but will be considered in a pass/fail situation
Assignment Details:
This assignment is designed to let you explore and evaluate a number of approaches to investment portfolio optimisation, using live real-world data. The relevant URL for finding stock prices is: under the “Quote lookup” search.
In this assignment you will use asset return data from a period of 3 years to identify the optimum portfolio using a variety of different optimisation methods. You will then compare the performance of these portfolios on a further 1 year of data, so that you may observe just how well the optima generated from the first three years of data performed in that subsequent period, by comparison with the optimum portfolios for that period. This will allow you to make some assessment about the validity for future investment decisions of the optimisation methods which just use past data.
Preliminary Work (7 marks: Data acquisition + Classifications)
The first stage is to identify a set of 10 investment items from which you will subsequently determine optimum portfolios, using various optimisation models. You may select any global assets (including indices) whose data is provided on the Yahoo finance website. The chosen assets must satisfy the following general constraints:
• Each must have at least 48 months (March 2015 - March 2019) of monthly data available, up to and including March 2019.
• They should be selected from 4 different sectors/categories (C1, C2, C3 and C4) e.g. banking, pharmaceuticals, media, technology, government bonds, property trusts, etc. – your choice, with at least 2 assets in each category.
• They should span a reasonable range of volatilities/risk. Classify the assets into 4 groups according to (ascending) risk (R1, R2, R3 and R4). It is up to you to determine the basis for the classification, but ensure there are at least 2 assets in each of R1 to R4. A simple, and acceptable approach would be to divide the range of risks into 4 quartiles, as long as the previous requirement is met.
• Recall that each asset lies in one of the Rs and in one of the Cs.
The collected data needs to be divided into two sets:
• Modelling data: For your portfolio optimisations, you should use the first 36 months of data. Perform parts 1, 2, 3a, 3b and 3c on the modelling data (or Training set)
• Holdout sample: The last 12 month of collected data. This is to assess model performance as a means of model validation. Perform all these parts again using Holdout sample. This is called validation task. In validation part, you should include the results of this comparison in a table.
Optimisation part
The assignment requires you to consider three different approaches to portfolio optimisation:
1. Choosing according to asset class restrictions, and individual asset risk appetite.
2. Choosing according to portfolio size restrictions and risk appetite.
3. Choosing according to portfolio risk and return requirements.
These three approaches allow exploration of three different optimisation techniques: linear programming, integer programming and non-linear programming:
1. LP model (7 marks: Mathematical Model + Solver and results + Sensitivity Analysis): In this approach, the aim is to achieve the maximum overall return, subject to specified requirements on risk mix (percentages in R1 to R4) and category mix (percentages in C1 to C4). These requirements may be simple – such as “no more than 10% in R1, or more complex such as “there should be as much invested in R1 as there is in R4” or “Investment in high risk assets shouldn’t exceed the 30% of the portfolio”. Other restrictions might be of the form – “at least 25% should be in the banking sector, and no more than 20% in energy”. It is up to you to determine the restrictions that you wish to impose. I expect these to be “sensible”, respecting a sense of diversity in the portfolio, and a defendable risk acceptance approach. The only requirement is that they should respect the learning aims of this assignment and therefore they should not in any way trivialise the problem. There should be realistic range requirements for each of R1 to R4, and C1 to C4. For example, requiring all assets in the portfolio to be in risk category R1 would trivialise the problem.
Use a sensitivity analysis report to comment on how changes to the risk and category constraints might affect the optimum portfolio.
2. ILP model (7 marks: Mathematical Model + Solver and results): In this approach, we assume that a balanced portfolio of exactly 7 stocks is to be chosen. The 4 asset categories (the C classification) have to be included. In addition, at most 1 of the assets can be in the riskiest group R4, and at least 2 must be in the least risky group R1. The goal is to achieve the maximum overall return, subject to these specified requirements.
3. NLP model (4 marks each: Mathematical Model + Solver and results): In this approach, the aim is to optimise without category constraint using the methods of Module 1, topic 3 – i.e. considering the overall portfolio risk/return profile. There are three sub-problems here:
a. Achieve the maximum overall return, subject to an upper limit on portfolio risk (your choice of limit).
b. Achieve the minimum portfolio risk, subject to a requirement to achieve at least a specified return
(your choice of required return).
c. Achieve the maximum of risk adjusted return (Sharpe ratio).
For each optimisation model, explain about the optimisation approach taken, the mathematical formulation and identify the Excel Solver to be used (explain any particular constraints used – e.g. that a variable needs to be an integer, or binary).
Validation part (7 marks: validation + comparison + conclusion + overall presentation)
Determine the average return and average risk achieved in the last 12 months (Holdout sample). Then calculate the optimum portfolios using the above optimisation criteria in part 1 to 3 for that 12 month-period. In that way, you can compare the various portfolios’ performances in the last 12 months. This is one common way to validate an optimisation approach which is geared to decision-making for the future. One flaw in all of the approaches to be used here is the assumption that historical asset performance, individually and in combination, is a good predictor of future performance. That assumption has to be put to the test, and by looking at the last year, in comparison to choices made using the previous 3 years’ data, you have an opportunity to evaluate the efficacy of the decision making.
Summarise your report (of all above parts) in a Powerpoint file, present all your results comparatively in a coherent and compelling manner, and then, based on your assessment of the various approaches, explain briefly about a strategy that you might prefer to use for portfolio optimisation. Include a summary table that includes details of each chosen portfolio and the basis of choice, with percentages of assets, return and risk for the 3 years’ of data used to choose the portfolio (modelling data), and return and risk in the last year (hold-out data). Compare each of your chosen portfolios’ performance in the last 1 year with the optimum portfolio for this 1 year chosen according to the same objective function, and tabulate for each of your methods the ratio of your portfolio’s performance in the 1 year to the optimum performance in this year.
Assignments will be marked based on the methodologies adopted and the quality of work. Given the vast range of assets to select from on the yahoo site it is highly unlikely that you will choose the same portfolio of stocks as another student.
Feedback prior to submission
Students are able to seek assistance from the teaching staff to ascertain whether their assignment conforms to submission guidelines, through:
- Discussion Forum: other students can also benefit from your questions and replies - Consultation sessions: dates are available in the CloudDeakin page
Feedback after submission
Your assignment feedback will be returned within 3 weeks of due date in a rubric via CloudDeakin with an overall mark together with comments.
Assurance of Learning
This assignment assesses following Graduate Learning Outcomes and related Unit Learning Outcomes:
Unit Learning Outcome (ULO) Graduate Learning Outcome (GLO)
ULO1. Conceptualise, formulate and represent a business problem as a decision model.
ULO2. Develop and solve business problems using advanced decision modelling techniques such as optimisation, stochastic modelling and risk analysis in spreadsheets.
ULO3. Interpret and analyse the results; investigate the sensitivity of the solutions to the assumptions of decision model. GLO1: Discipline-specific knowledge and capabilities
GLO4: Critical thinking
GLO5: Problem solving

Zip Archive file having Microsoft Excel file, Micorsoft PowerPoint file and .csv file

Editable Microsoft Excel Document
Sheets Count: 11 Worksheets

Editable Microsoft PowerPoint Presentation
Slides Count: 15 slides

Buy Now at $19.99 USD
This above price is for already used answers. Please do not submit them directly as it may lead to plagiarism. Once paid, the deal will be non-refundable and there is no after-sale support for the quality or modification of the contents. Either use them for learning purpose or re-write them in your own language. If you are looking for new unused assignment, please use live chat to discuss and get best possible quote.

Looking for answers ?