MIS775 - Decision Modelling for Business Analytics Assignment

Assignment Task

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).

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.

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
• “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 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

• 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>
• Each investment must belong to one of the four industry categories and one of the three risk

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:

1. Choosing according to investment category restrictions, and individual investment risk

2. Choosing according to portfolio size restrictions and risk

3. Choosing according to portfolio risk and return

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 : 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
• No more than one‐third of the investments can be in the riskiest group
• At most two investments can be in the least risky group
• At least one investment must be in the risk group

NLP model: In this approach, the aim is to optimise without imposing any category or risk group constraints. Instead, the overall portfolio risk/return profile is There are three sub‐problems here:

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

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 )

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.

This MIS775 - Business Analytics has been solved by our PhD Experts at My Uni Paper.