Background
This assignment is to be completed in groups of 2 - 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
worksheets. 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 spreadsheet and PowerPoint file.
The assignment has three main sections: Preliminary Work, Optimisation Models and Report.
The requirements of each section are detailed below. The breakdown of marks (total of 40) is given in this
document and the Assignment 1 Rubric.
Percentage of final grade 20%
Due date Sunday 12 April 2020 at 11.59pm AEDT
The assignment must be submitted by the due date electronically in CloudDeakin.When submitting
electronically, you must check that you have submitted the work correctly by following the instructions
provided in CloudDeakin. Please note that any assignment or part of an assignment submitted after the
deadline or via Email will NOT be accepted.
Any request for an extension must be negotiated at least one week prior to the above deadline by email.
Deakin policy for late submission: 5% will be deducted from the 20 marks allocated to this assessment task
for each day or part day that the assessment is late, up to five days. Penalties also apply on weekend days
and public holidays.Where a group submits their work more than five days after the due date, the
submission will not be marked and the members of the group will receive 0% for their assignment mark.
Assurance of Learning
This assignment assesses following Graduate Learning Outcomes and related Unit Learning Outcomes:
Graduate Learning Outcome (GLO) Unit Learning Outcome (ULO)
GLO1: Discipline-specific knowledge and capabilities:
appropriate to the level of study related to a discipline
or profession
GLO5: Problem solving: creating solutions to authentic
(real world and ill-defined) problems
GLO4: Critical thinking: evaluating information using
critical and analytical thinking and judgment
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
assumptions of the decision model
Feedback
Prior to submission
Students are able to seek assistance from the teaching staff to ascertain whether the assignment conforms to
submission guidelines. Please post your questions on CloudDeakin’s discussion forum for Assignment 1.
After submission
Your assignment feedback will be returned in a rubric (see p. 4) via CloudDeakin with an overall mark together
with comments.
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:
https://au.finance.yahoo.com/ under the “Quote lookup” search.
In this assignment you will use asset return data for a period of 3 years to identify the optimum portfolio
by applying a range of optimisation methods. In each case you must determine the percentage (or
proportion) of the portfolio to invest in each of 10 assets, such that the percentages are non-negative and
sum to 100% (or 1).
SECTION 1. PRELIMINARY WORK (4 marks: Data acquisition + Classifications)
Choose five investments listed on the Australian Stock Exchange, one from each of the categories given in
the following table, to complete a set of 10 investments.
Technology Real Estate Financial Healthcare Telecom & Utilities
1. Carsales.com
Limited
(CAR.AX)
2. Shopping Centres
Australasia Property
Group Stapled Units
(SCP.AX)
3. Commonwealth
Bank of Australia
(CBA.AX)
4. CSL Limited
(CSL.AX)
5. AGL Energy
Limited
(AGL.AX)
6. Your choice 7. Your choice 8. Your choice 9. Your choice 10. Your choice
To access the assets, click Industries on the ribbon menu, and select a category. Click on the symbol for the
asset you want to include in your portfolio. Click Historical data on the ribbon menu, set Time period to
1 January 2017 – 1 March 2020 and Frequency to Monthly, then click the Apply button, and download the
data. Delete any rows showing dividends. We are only interested in the opening price, listed in the column
headed Open. Discard the rest of the data.
The chosen assets must satisfy the following general requirements:
• Each must have 37 consecutive months of opening prices, up to and including 1 February 2020.
• They should be selected from the five industry categories listed in the table, namely Technology,
Real Estate, Financial, Healthcare, and Telecom & Utilities. You must choose only one asset from
each of these five categories.
• They should span a reasonable range of volatilities/risk. For this reason you might try several assets
in a category before settling on one.
Classify each of the ten assets into one of three risk groups R1, R2, and R3, where R1
up to you to determine the basis for the classification, but you must have at least three assets in
each risk group.
• Each asset must belong to one of the five industry categories and one of the three risk categories.
SECTION 2. OPTIMISATIONMODELS
For your portfolio optimisations, you should use all of the data to undertake parts 1, 2, 3a, 3b, and 3c.
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
(LP), integer linear programming (ILP), and non-linear programming (NLP):
1. LP model (6 marks: Mathematical Model + Solver and results + Sensitivity Analysis worksheet): In
this approach, the aim is to achieve the maximum overall return, subject to specified requirements
on risk mix (percentages in R1 to R3) and category mix (percentages in C1 to C5). 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 R3” 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 Financial category, and no more than 20% in the Industrial category”. It is up to you to
determine the restrictions that you wish to impose. These should 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 R3, and C1
to C5. For example, requiring all assets in the portfolio to be in risk category R1 would trivialise the
problem.
2. ILP model (6 marks: Model + Solver and results): In this approach, we assume that a balanced
portfolio of exactly 7 stocks is to be chosen. The 5 asset categories have to be included. In addition,
at most 2 of the assets can be in the riskiest group R3, and at least 1 must be in the least risky group
R1. The goal is to achieve the maximum overall return, subject to these requirements.
3. NLP model (3 marks each for parts a-b, 6 marks for part c: Model + Solver and results): In this
approach, the aim is to optimise without imposing any category or risk group constraints. Instead
the overall portfolio risk/return profile is optimised. 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) A third approach is to maximise the following objective function
(1 – r) × (Expected portfolio return) – r × (Portfolio variance)
subject to the portfolio weights being non-negative and summing to 1 (100%).
The parameter r is a measure of an investor’s risk aversion. For example, an investor who
chooses r = 0 is unconcerned with risk, and is instead completely focused on maximising the
expected return. At the other extreme, the investor who chooses r = 1 is focused on
minimising risk. Values of r between 0 and 1 indicate varying degrees of risk aversion.
Your task here is to determine portfolio weights for each of (i) r = 0, (ii) r = 1, and (iii) your
choice of r.
SECTION 3. REPORT (12 marks)
The PowerPoint document should present all your results in a coherent and compelling manner. Each
model should be accompanied by the following:
• A conceptual diagram of the model
• An algebraic formulation of the model
• The optimal solution
• Interpretation of sensitivity analysis output for part 1 of section 2 (Use Solver’s sensitivity
analysis report for part 1 to comment on how changes to risk and category constraints might
affect the optimum portfolio.)
Then, based on your assessment of the various approaches, briefly explain which strategy you might
prefer to use for portfolio optimisation, and why. Include a summary table listing the details of each
optimal portfolio with percentages of assets, portfolio return and risk based on the 3 years of data.
Assignments will be marked based on the criteria given in the rubric that follows. Given the range of assets
to select from on the yahoo site it is highly unlikely that your group will choose the same portfolio of stocks
as another group.