DecisionModellingforBusinessAnalytics MIS775

MIS775DecisionModellingforBusinessAnalyticsTrimester12025 Assessment Task 1 Investment Portfolio Optimisation Individual

DUE DATE:Wednesday,9^thApril2025,by8:00pm(Melbournetime) PERCENTAGE OF FINAL GRADE:20%

WORDCOUNT:1200Maximumnumberofwords(excludingtablesand diagrams)

Description Purpose

This assignment task is aligned to the learning outcomes GLO1 & ULO1 and skills GLO4 & ULO3 andGLO5 & 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 decisionmodel,developingbusinessdecisionmodelsusingsoftwaretools,undertakingsensitivityanalysisand evaluating the utility of alternative solutions.

Context/Scenario

Thisassignmentisdesignedtoletyouexploreandevaluateanumberofapproachestoinvestmentportfolio

optimisationusinglivereal?worlddata.

In this assignment, you will use investment return data for a period of 4 years to identify the optimum portfolio by applying a range of optimisation methods. In each case, youmust determine the percentage (or proportion)oftheportfoliotoinvest ineachof8investments,suchthat thepercentagesarenon?negativeand sum to 100% (or 1).

SpecificRequirements

Theassignmenthasfourmainsectionsincludedin1Excelfileand1PowerPointfile:

ExcelFile:

• Preliminary Work,
• LP Optimisation Model
• ILP Optimization Model and

PowerpointFile:Report(PPTfilebutneedtoincludetherelevantExcelworksheetorscreenshots) The requirementsofeachsectionaredetailedbelow.Thebreakdownofmarks(totalof100)isgiveninthis document and the Assignment 1 Rubric.

Section1:PreliminaryWork(5marks)

ChooseanyfourinvestmentslistedintheAustralianStockExchange(ASX),onefromeachofthesectorsgiven in the following table, to complete a set of 8 investments.

Toaccessthestockprices,pleaserefertoAppendixIIbelow.

Each stock must have49monthly stock prices (January 2021 to January 2025) and compute the monthly rate of returnfor48monthsor for4years.PleaserefertoAppendixIIandtheprovidedAssessment 1Format Excelfile.

Stocksshouldbeselectedfromany4differentsectors/categoriesofyourchoice(letscallthemS1,S2,andS3 and S4) with2 assets in each sector.

Thechoseninvestmentsmustsatisfythefollowinggeneralrequirements:

• Each has 48 consecutive months of closing prices, up to and including 1 January
• Your choice of investment? They should be selected from thefourindustrysectors(S1toS4)listed in the table above. You must chooseonly one investmentfrom each of these four sectors.
• Fore ach of the 8 investments (i.e., the four given investments listed in the table and the rest of the four you chose from those given sectors ? Your choice of investment), calculate monthly rate of returns, average rate of return and then use their standard deviation to find their risk.
• The8investmentsshouldspanareasonablerangeofvolatilities/risk.Forthisreason,youmight try several investments in a sector 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 threerisk groups R1, R2, and R3, where R1 < R2>at least two investmentsineach risk group.
• Define risk categories based on theTherefore, R1 should include investment stocks with the lowest risk (lowest standard deviation) and R3 should be in the highest risk .
• Each investment must belong to one of the four industry sectors and one of the three risk

Seethebelowtemplate.Onceyouhavedeterminedwhatriskgrouptheybelongto,youcan write the investment/company name in the body of the table below.

Foryourportfoliooptimizations,youshouldusemodellingdatathat youcreatedintheworksheet.The assignment requires you to consider two different approaches to portfolio optimization:

1. Choosing according to investment sector restrictions, and individual investment risk
2. Choosing according to portfolio size restrictions and risk

Thesedifferent approachesallowtheexplorationoftwodifferentoptimizationtechniques:linearprogramming (LP), and integer linear programming (ILP)

Section2:LPOptimisationModel(25Marks)

LPModellingusingappropriatecoloursfor variablecellsandobjectivefunctioncell(greencolour) ,constraint commands cells (blue colour) as you learnt in the class:Solver setupand resultsin Excel (refer the given worksheets 2.a and 2.b)

1. LP model (Solver set up and results):In this approach, the aim is to achieve the maximum overall return,subject tothespecifiedrequirementsregardingtheriskmix(percentagesinR1toR3)andsector mix (percentages in S1 to S4).

ThefollowinginvestmentrequirementsandconstraintswillbeappliedtotheLinearProgramming model:

1. Investment in the highest-risk assets shouldnt exceed 15% of the
2. The lowest-risk assets should receive the highest allocation of investments compared to all other risk categories.
3. To ensure diversification, each sector must have a minimum of 15% invested; apart from one sector that you choose (your discretion) to have a minimum of 20% invested.
4. The minimum investment in each asset should be 5%.

5. It is up to you to determineone more restrictionthat you wish to impose. This 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 S1 to S4. For example, requiring all investments in the portfolio to be in risk sector R1 would trivialise the problem.

2. Solve the problem, and show your optimal resultSensitivityAnalysis(intheworksheetb)

Section3:ILPOptimisationModel(20Marks)

1. ILP model(Solversetupinworksheetainexcelfile.Useappropriatecoloursforobjective function, variables and constraint commands)

Inthisapproach,thegoalistoachievethemaximum expectedrateofreturn,andweassumethatthefollowing investment guidelines to be applied:

1. A balanced Portfolio of exactly 5 stocks is to be chosen,
2. The4investmentsectorsmustbe
3. At most 1oftheassetscanbe intheriskiestgroup,
4. At least 2 must be in the least risky
5. Finally, an asset from R3 can be selected only if at least one asset from R2 is
6. It is up to you to determineone more restrictionthat you wish to impose. This 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.

Note that, for each optimisationmodel under LP and ILP, your spreadsheet shouldcontainan explanationofeachoptimisation approach taken, the mathematical formulation, and each constraints used.Inaddition,theExcelSolverdialogboxforeachoptimisationmodelmustbecompletedinyour spreadsheet. You must perform the simplex method to find the optimal solution

Section4:ReportinPPT(50Marks)

Thenumberofslidesshouldnotexceedamaximumof25.Apenaltyof-10markswillbeappliedif this limit is exceeded.

ThePowerPointdocumentshouldpresentallyourresultscomparativelycoherentlyandcompellingly.Each model should be accompanied by the following:

• A conceptualdiagramofthemodel(onlyfortheLPmodel)
• Analgebraicformulationofthemodel(Mathematicalmodelling)

3.3.1TheoptimalsolutionofLP

• clearly show the Excel sheet of the modelling here, insert a copy
• Include a summary table that includes details of each chosen portfolio, their sector and risk categories, andthe basis of choice, with percentages of investments,returnand risk for the 4years of data used to choose the portfolio.
• Sensitivity Analysis:Includethesensitivityanalysisoutputsheetinyourreportanddiscuss

• Use Excel Solver sensitivity Analysis report to comment on binding constraints, Reduce cost, Shadow price, and the impacts of changes to the risk and sector constraints on the optimum portfolio
• Use the sensitivity analysis report to interpret on how changes to the risk and sector constraints

might affect the optimum portfolio. Interpret on the binding constraints in your portfolio optimization model. Discuss strategies to reduce costs while maintaining an optimal portfolio allocation. Assess the impact of modifying risk and sector constraints on the optimal portfolio composition.

• If any asset is excluded from the optimal solution, determine the required change in its return forit to be selected. Provide a clear explanation of how adjustments in expected returns could lead to the asset's inclusion in the optimal portfolio.

3.4TheoptimalsolutionofILP

• Analgebraicformulationofthemodel(Mathematicalmodelling (clearlyshowtheexcelsheetofthemodellinghere,insertacopy)
• Include a summary table that includes details of each chosen portfolio, their sector and risk categories, and the basis of choice, with percentages of investments, return and risk for the 4years of data used to choose the portfolio.

Assignmentswill bemarkedbasedonthecriteriagivenintherubricthatfollows.Giventherangeof investments to select from on the Australian stock exchange, 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 requiredinformationinclearlylabelledseparateworksheets.Inaddition,youarealsorequiredtosubmitareport ? MS PowerPoint file that summarises your models andresults.In summary, two files should be submitted anExcel spreadsheet and PowerPoint file.

LearningOutcomes

This task allows you to demonstrate your achievement towards the Unit Learning Outcomes (ULOs) which havebeenalignedtotheDeakinGraduateLearningOutcomes(GLOs).DeakinGLOsdescribetheknowledge and capabilities graduates acquire and candemonstrateon completionoftheir 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 successfulin thisunit. Youare advisedto familiarise yourselfwith these ULOsand GLOs astheywillinformyouonwhatyouare expectedtodemonstrateforsuccessfulcompletionof this unit.

Thelearningoutcomesthatarealignedwiththisassessmenttaskare:

Submission

YoumustsubmityourassignmentintheAssignmentDropboxontheunitCloudDeakinsiteonorbeforethe due date. When uploading your assignment, name your document using the following syntax:. For example, Jones_Barry_123456789_ABC123.pptx.Applythesamenamingconventionwithanxlsxextensionwhen uploading your MS Excel spreadsheet.