代写MIS275留学生、Analytics代做、代做Java，Python，C/C++程序语言

日期：2020-08-27 10:53

MIS275 - Decision Analytics Assignment 1 – Trimester 2, 2020 Page 1

DEAKIN BUSINESS SCHOOL

DEPARTMENT OF INFORMATION SYSTEMS AND BUSINESS ANALYTICS

MIS275 Decision Analytics

Assignment One: Investment Portfolio Optimisation

Background

This is an individual assignment. 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 that summarises your models and results. A template for

your report is provided in MS Word file format. Any other file format, such as pdf, is NOT acceptable and will

not be marked.

In summary, two files should be submitted – one MS Excel spreadsheet and one MS Word file.

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.

Percentage of final grade 20%

Due date Sunday 30 August 2020 at 8.00pm AEDT

The assignment must be submitted by the due date electronically in CloudDeakin. When submitting

electronically, you must check that you have submitted the work correctly by following the instructions

provided in CloudDeakin. Please note that any assignment or part of an assignment submitted after the

deadline or via Email will NOT be accepted.

Any request for an extension must be negotiated at least one week prior to the above deadline by email.

Deakin policy for late submission: 5% will be deducted from the 20 marks allocated to this assessment task

for each day or part day that the assessment is late, up to five days. Penalties also apply on weekend days

and public holidays. When work is submitted more than five days after the due date, the task will not be

marked and the student will receive 0% for the task.

Assurance of Learning

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.

GLO3: Digital literacy: using technologies to find, use

and disseminate information

GLO4: Critical thinking: evaluating information using

critical and analytical thinking and judgment

ULO1: Apply decision models optimisation techniques

to conceptualise and

represent a business scenario

ULO2: Apply optimisation software tools to find

optimal decisions for a given

business scenario

ULO3: Interpret and analyse the results; investigate the

assumptions of the decision model

Feedback

Prior to submission

Students are able to seek assistance from the teaching staff to ascertain whether the assignment conforms to

submission guidelines. Please post your questions on CloudDeakin’s discussion forum for Assignment 1.

After submission

Your assignment feedback will be returned in a rubric (see p. 4) via CloudDeakin with an overall mark together

with comments.

MIS275 - Decision Analytics Assignment 1 – Trimester 2, 2020 Page 2

Assignment Details:

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:

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 Basic Materials Financial Healthcare Telecom & Utilities

1. Technology One

Limited

(TNE.AX)

2. Fletcher Building

Limited (FBU.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 June 2017 – 30 June 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 have 37 consecutive months of opening prices, up to and including 1 June 2020.

• They should be selected from the five industry categories listed in the table, namely Technology,

Basic Materials, Financial, Healthcare, and Telecom & Utilities. 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 one.

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

SECTION 2. OPTIMISATION MODELS

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 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: 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

MIS275 - Decision Analytics Assignment 1 – Trimester 2, 2020 Page 3

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.

2. ILP model (6 marks: Model + Solver and results): In this approach, we assume that a balanced

portfolio of exactly 7 stocks is to be chosen. 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.

3. 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 constraints. Instead

the overall portfolio risk/return profile is optimised. There are three sub-problems here:

a) Achieve the maximum overall return, subject to an upper limit on portfolio risk (your choice

of limit).

b) Achieve the minimum portfolio risk, subject to a requirement to achieve at least a specified

return (your choice of required return).

c) 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 MS Word 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, conclude the report by briefly explaining

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.

MIS275 - Decision Analytics Assignment 1 – Trimester 2, 2020 Page 4

Rubric for Assignment 1

Performance Levels Criteria YET TO ACHIEVE MINIMUM STANDARD MEETS STANDARD EXCEEDS STANDARD

Poor Format

(0‐49)

Satisfactory

(50‐59)

Good

(60‐69)

Very good

(70‐79)

Excellent

(80‐100)

SECTION 1.

PRELIMINARY WORK

ULO1/GLO1

Total: 4 marks

0

Downloaded

data is not

aligned to

assignment

criteria

1.6

Downloaded data

has some errors or

omissions.

Initial processing of

data has some

errors

2.2

Downloaded data has some

errors or omissions.

Initial processing of data has

been correctly performed.

Report includes some evidence

that data satisfies the

assignment criteria

2.6

Appropriate data has been

downloaded.

Initial processing of data has

been correctly undertaken.

Report includes some evidence

that data satisfies the

assignment criteria

3.0

Appropriate data has been

downloaded.

Initial processing of data has

been correctly undertaken.

Report clearly demonstrates that

data aligns with assignment

criteria

4

Appropriate data has been

downloaded.

Initial processing of data has been

correctly undertaken.

Report clearly demonstrates that

data aligns with assignment criteria.

Report includes a clear & relevant

rationale for the choice of risk groups

MS Excel

spreadsheet

&

Report in

MS Word

document

0 ‐ 1.1 1.2 ‐ 1.9 2 – 2.3 2.4 – 2.7 2.8 – 3.1 3.2 ‐ 4

SECTION 2. OPTIMISATION

MODELS

Part 1

ULO1,2,3/GLO1, 4

Total: 6 marks

0

Spreadsheet

model/results

not included or

inappropriate.

Solver not set up

in spreadsheet

2.4

Vague spreadsheet

model is given,

analysed and/or

contains several

modelling errors

3.3

Appropriate spreadsheet

model is given.

Are a few errors & omissions.

Solver is set-up in spreadsheet

and is mostly correct

3.9

Clear presentation of

spreadsheet model.

Solver is set-up in spreadsheet

and is mostly correct.

Sensitivity analysis worksheet

included

4.5

Clear presentation of

spreadsheet model.

Solver is correctly set up in

spreadsheet and results are

correct.

Sensitivity analysis worksheet

included

6

Very clear presentation of

spreadsheet model.

Solver is correctly set up in

spreadsheet and results are correct.

Sensitivity analysis worksheet

included

MS Excel

spreadsheet

0 – 1.7 1.8 – 2.9 3 – 3.5 3.6 – 4.1 4.2 – 4.7 4.8 ‐ 6

SECTION 2. OPTIMISATION

MODELS

Part 2

ULO1,2,3/GLO1, 3, 4

Total: 6 marks

0

Spreadsheet

model/results not

included or

inappropriate.

Solver not set up

in spreadsheet

2.4

Vague spreadsheet

model is given,

analysed and/or

which contains

several modelling

errors

3.3

Appropriate spreadsheet

model is given.

Are a few errors & omissions.

Solver is set-up in spreadsheet

and is mostly correct

3.9

Clear presentation of

spreadsheet model.

Solver is set-up in spreadsheet

and is mostly correct

4.5

Clear spreadsheet model.

Solver is correctly set up in

spreadsheet and results are

correct

6

Very clear presentation of

spreadsheet model.

Solver is correctly set up in

spreadsheet and results are correct

MS Excel

spreadsheet

0 ‐ 1.7 1.8 – 2.9 3 – 3.5 3.6 – 4.1 4.2 – 4.7 4.8 ‐ 6

SECTION 2. OPTIMISATION

MODELS

Parts 3a-c ULO1,2,3/GLO1, 3, 4

Total: 12 marks

0

Spreadsheet

models/results

not included or

inappropriate.

Solver not set up

in spreadsheet

4.8

Vague spreadsheet

models are given,

analysed and/or

which contain

several modelling

errors

6.6

Appropriate spreadsheet

models are given.

Are a few errors & omissions.

Solver is set-up in spreadsheet

and is mostly correct

7.8

Clear presentation of

spreadsheet models.

Solver is set-up in spreadsheet

and is mostly correct

9

Clear presentation of

spreadsheet models.

Solver is correctly set up in

spreadsheet and results are

correct

12

Very clear presentation of

spreadsheet models.

Solver is correctly set up in

spreadsheet and results are correct

MS Excel

spreadsheet

0 – 3.5 3.6 – 5.9 6 – 7.1 7.2 – 8.3 8.4 – 9.5 9.6 ‐ 12

SECTION 3. REPORT

 Conceptual diagrams

 Algebraic formulations

 Optimal solutions

 Interpretation of sensitivity

report

 Comparison of approaches

 Preferred strategy

 Summary table

ULO3/GLO4

Total: 12 marks

0

Report is not

included or is

inappropriate

4.8

Report is not a

standalone

document.

Report displays a

general lack of

clarity or logic in the

interpretation or

analysis of results

6.6

Report is largely a standalone

document.

Some areas of the report

display a lack of clarity or logic

in the interpretation or

analysis of results, or there

are a few errors or omissions

7.8

Report is a completely

standalone document.

Some areas of the report

display a lack of depth in

understanding

9

Report is a completely

standalone document.

Report comprehensively

addresses all areas of the

modelling.

Report displays a depth of

understanding across all areas

12

Report is a completely standalone

document.

Report comprehensively addresses

all areas of the modelling

Report displays a depth of

understanding across all areas.

Report concludes with some key

insights

MS Word

document

0 – 3.5 3.6 – 5.9 6 – 7.1 7.2 – 8.3 8.4 – 9.5 9.6 - 12

Total marks 40 (20%)