Develop and solve business problems using advanced decision modelling techniques such as optimisation, stochastic modelling and risk analysis in spreadsheets

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

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 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. 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
  • They should be selected from the five industry categories listed in the table, namely Technology, Real Estate, Financial, Healthcare, and Telecom & 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

Classify each of the ten assets 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 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



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
  2. Choosing according to portfolio size restrictions and risk
  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.


  1. ILP model (6 marks: Model + Solver and results): In this approach, we assume that a balanced portfolio of exactly 7 stocks is to be 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.


  1. 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 Instead the overall portfolio risk/return profile is optimised. 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. 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.

find the cost of your paper

Critical analysis of Change4Life Campaign

Use Subheadings– but do not use more subheadings than listed below Introduction ● Signpost reader to the contents of your essay Campaign Overview ● Identify the campaign you are focusing….

position in the Apple Valley School district-describes the work of two learning theorists and how you might apply the work of each theorist to the position you are seekin

You are applying for a position in the Apple Valley School district and know that there are several other applicants vying for the position. The district prides itself on employing….

Novel CoronaVirus – COVID-19

-Who is more Susceptible to Corona virus focus on the elderly and how it affect the elderly. -Why the elderly are at the higher risk? – How and why it….