Reference no: EM133674517

Decision Modelling for Business Analytics

Learning Outcome 1: Conceptualise, formulate, and represent a business problem as a decision model

Learning Outcome 2: Develop solutions to business problems using advanced decision modelling techniques

Learning Outcome 3: Interpret and analyse the results and evaluate the sensitivity of solutions to the assumptions of the decision models

Assessment Task - Investment Portfolio Optimisation

Purpose
This assignment task is aligned to the learning outcomes GLO1 & ULO1 and skills GLO4 & ULO3 and GLO5 & ULO2 required to build complex decision models and use advanced quantitative modelling techniques, such as optimisation, to analyse and develop solutions to business problems. By completing this task, you will develop your skills in conceptualising, formulating and representing a business problem as a decision model, developing business decision models using software tools, undertaking sensitivity analysis and evaluating the utility of alternative solutions.

Context/Scenario
This assignment is designed to let you explore and evaluate a number of approaches to investment portfolio optimisation, using live real-world data.

In this assignment you will use investment 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 8 investments, such that the percentages are non-negative

Section 1: Preliminary Work
Choose four investments listed on the Australian Stock Exchange, one from each of the categories given in the following table, to complete a set of 8 investments.

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 (High, Low, Close, Adj Close, Volume, etc.). The chosen investments must satisfy the following general requirements:
Each has 37 consecutive months of opening prices, up to and including 1 February 2024.
"Your choice of investment"- They should be selected from the four industry categories (C1 to C4)
listed in the table above, namely Basic Materials, Technology, Telecom & Utilities, and Real Estate. You
must choose only one investment from each of these four categories.
For each of the 8 investments (i.e., the four given investments listed in the table above and the four you chose - "Your choice of investment"), calculate monthly returns (Topic 3, Slide 37), average return and then use their standard deviation to find their risk.
The 8 investments should span a reasonable range of volatilities/risk. For this reason, you might try several investments in a category before settling on a final choice.

Classify each of the 8 investments (i.e. the four given investments and the four you chose) into one of three risk groups R1, R2, and R3, where R1 < R2 < R3. It is up to you to determine the basis for the risk classification, but you must have at least two investments in each risk group.
Each investment must belong to one of the four industry categories and one of the three risk categories.
See the below template. Once you have determined what risk group they belong to, you can write the investment/company name in the body of the table below.

Section 2: Optimisation Models

For your portfolio optimisations, you should use modelling data to undertake parts 1, 2, 3a, 3b, and 3c. The assignment requires you to consider three different approaches to portfolio optimisation:
Choosing according to investment category restrictions, and individual investment risk appetite.
Choosing according to portfolio size restrictions and risk appetite.
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).
LP model (Solver set up and results + Sensitivity Analysis): In this approach, the aim is to achieve the maximum overall return, subject to the specified requirements regarding the risk mix (percentages in R1 to R3) 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 R3" or "high-risk investments shouldn't exceed 30% of the portfolio". Other restrictions might be of the form - "at least 25% should be in the Technology category, and no more
than 20% in the Real Estate 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 C4. For example, requiring all investments 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.

ILP model ( Solver set up and results): In this approach, the goal is to achieve the maximum overall return and we assume that an equal-weighted portfolio of exactly 6 investments is to be chosen, subject to these requirements:
The 4 investment categories must be included.
No more than one-third of the investments can be in the riskiest group R3.
At most two investments can be in the least risky group R1.
At least one investment must be in the risk group R2.

NLP model ( Solver set up 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:
Achieve the maximum overall return, subject to an upper limit on portfolio risk (your choice of
limit).
Achieve the minimum portfolio risk, subject to a requirement to achieve at least a specified return (your choice of required return).
Achieve the maximum risk adjusted return, i.e., Sharpe ratio. (Assume a risk-free rate of 4.35%
per annum. Note: The risk free rate is the rate of return that investors expect to earn on an
investment that carries zero risk.)

Note that, for each optimisation model, your spreadsheet should contain an explanation of each optimisation approach taken, the mathematical formulation, and each constraints used - e.g. that a variable needs to be an integer, or binary. In addition, the Excel Solver dialog box for each optimisation model must be completed in your spreadsheet.

Section 3: Report

The PowerPoint document should present all your results comparatively coherently and compellingly. Each model (i.e. LP model, ILP model, NLP models 3.a, 3.b and 3.c), 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 the LP model only.

Then, based on your assessment of the various approaches, briefly explain the strategy you prefer to use for portfolio optimisation, and why. Include a summary table that includes details of each chosen portfolio and the basis of choice, with percentages of investments, return and risk for the 3 years' of data used to choose the portfolio.

Assignments will be marked based on the criteria given in the rubric that follows. Given the range of investments to select from on the yahoo site it is highly unlikely that you will choose the same portfolio of investments as another student.

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
report - MS PowerPoint file that summarises your models and results. In summary, two files should be
submitted - an Excel spreadsheet and PowerPoint file.