Assessment Task 1: Investment Portfolio Optimisation

Assessment Task 1: Investment Portfolio Optimisation

MIS775 – Decision Modelling for Business Analytics – Trimester 1 2024 Assessment Task 1 – Investment Portfolio Optimisation – Individual
PERCENTAGE OF FINAL GRADE: 20%
WORD COUNT: 1000 Maximum number of words

Description

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.

The relevant URL for finding stock prices is: https://au.finance.yahoo.com

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 and sum to 100% (or 1).

Specific Requirements

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.

Section 1: Preliminary Work (4 marks)

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.

Basic Materials   C1	Technology C2	Telecom & Utilities C3	Real Estate C4
1. BHP Group Limited    (BHP.AX)	2. CAR Group Limited     (CAR.AX)	3. Telstra Group Limited      (TLS.AX)	4. Lendlease Group (LLC.AX)
5. Your choice of investment	6. Your choice of      investment	7. Your choice of     investment	8. Your choice of      investment

To access the investments, click Industries on the ribbon menu (via the home page), and select a category.

Then click on the symbol for the investment you want to include in your portfolio.

Next, click Historical data on the ribbon menu,  then set Time period to 1 February 2021 – 1 February 2024 and Frequency to Monthly, then press 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 (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.

	Basic Materials C1	Technology C2	Telcom & Utilities C3	Real Estate C4	Total
R1					at least two investments
R2					at least two investments
R3					at least two investments
Total	two investments	two investments	two investments	two investments

Section 2: Optimisation Models (18 marks)

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:

1. Choosing according to investment category restrictions, and individual investment 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: 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 (6 marks: 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:

2. • 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 (6 marks: 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:

1. 1. Achieve the maximum overall return, subject to an upper limit on portfolio risk (your choice of limit).
   2. Achieve the minimum portfolio risk, subject to a requirement to achieve at least a specified return (your choice of required return).
   3. Achieve the maximum risk adjusted return,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 (18 marks)

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. (Use Solver’s sensitivity analysis report 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 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.

Learning Outcomes

This task allows you to demonstrate your achievement towards the Unit Learning Outcomes (ULOs) which have been aligned to the Deakin Graduate Learning Outcomes (GLOs). Deakin GLOs describe the knowledge and capabilities graduates acquire and can demonstrate on completion of their course. This assessment task is an important tool in determining your achievement of the ULOs. If you do not demonstrate achievement of the ULOs you will not be successful in this unit. You are advised to familiarise yourself with these ULOs and GLOs as they will inform you on what you are expected to demonstrate for successful completion of this unit.

The learning outcomes that are aligned with this assessment task are:

Unit Learning Outcomes (ULOs)		Graduate Learning Outcomes (GLOs)
ULO1	Conceptualise, formulate, and represent a business problem as a decision model	GLO1: Discipline‐specific knowledge and capabilities: appropriate to the level of study related to a discipline or profession
ULO2	Develop solutions to business problems using advanced decision modelling techniques	GLO5: Problem‐solving: creating solutions to authentic (real‐world and ill‐defined) problems
ULO3	Interpret and analyse the results and evaluate the sensitivity of solutions to the assumptions of the decision models	GLO4: Critical thinking: evaluating information using critical and analytical thinking and judgment