file uploaded, wats the time frame and amount quoted

MIS775 DECISION MODELS FOR BUSINESS ANALYTICS

MODULE 2: DETERMINISTIC DECISION MODELS

ASSIGNMENT 2 (40% OF UNIT MARK)

Requirements:

This assignment is to be completed individually and submitted as a single MS Excel file with the information required in clearly labelled separate sheets along with a cover sheet declaring that the assignment was completed without collusion or plagiarism. (Note that you are required to write a report. This should be written as a Microsoft Word document, which should then be embedded into a sheet in the Excel workbook that you submit (see, for example, http://www.ehow.com/how_7334529_embed-word-document-excel.html.)

The assignment is in four parts, 1,2,3 and 4. The requirements of each part are detailed below. The breakdown of marks (total is 200) to be awarded is given on the last page of this document. The assignment contributes 40% towards the total assessment for this unit. The marks achieved out of 200 will be divided by 5 to give the contribution towards the unit.

Deadline for submission: 8:00AM Monday 18 May 2014.

Assignment Details:

The 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: http://au.finance.yahoo.com/q.

Preliminary Work

The first stage is to identify a set of 15 investment vehicles from which you will subsequently determine optimum portfolios, subject to various optimisation models. You may select any global assets (including indices) whose data is provided on the Yahoo finance website. The 15 assets chosen must satisfy the following general constraints :

• They should be selected from 5 different categories (eg banking, pharmaceuticals, media, technology, government bonds, property trusts, etc), with at least 2 assets in each category.

• They should span a reasonable range of volatilities / risk (below you will be asked to split your assets into a set of 4 risk groups).

• Each must have at least 72 months of monthly data available.

For your portfolio selection, extract the last 72 months’ data up to and including April 2015.

For your portfolio optimisations, you should use the first 60 months of that data (ie, up to and including April 2014). As per the lecture in Class 2, determine the various matrices and

TRIMESTER 1, 2015

MIS775 DECISION MODELS FOR BUSINESS ANALYTICS

correlations between the returns of the assets in that period. Use the spreadsheet model provided in that class, but adjust it for the given number of assets, the term of the data, and to use just the first five years’ data for portfolio construction.

For each asset, and each of your portfolios that you will construct in Parts 1 to 3 below, you should also determine the average return and average risk achieved in the 12 months to April 2015. In that way, you can compare the various portfolios’ performances in the last 12 months. (This is one common way to validate an optimisation approach which is geared to decision-making for the future. One flaw in all of the approaches to be used here is the assumption that historical asset performance, individually and in combination, is a good predictor of future performance. That assumption has to be put to the test, and by looking at the last year, in comparison to choices made using the previous 5 years’ data, you have the opportunity to evaluate the efficacy of the decision making.)

The assignment requires you to consider three different approaches to portfolio optimisation:

1. Choosing according to asset class restrictions, and individual asset risk appetite.

1. Choosing according to portfolio risk and return requirements.

1. Choosing according to portfolio size restrictions and risk appetite.

These three approaches allow exploration of three different optimisation techniques: linear programming, non-linear programming and integer programming.

1. Classify the assets into 4 groups according to (ascending) risk (R1, R2, R3, R4). It is up to you to determine the basis for the classification, but ensure there are at least 3 assets in each of R1 and R4 – this is an arbitrary requirement to make Part 3 of this assignment work! A simple approach would be to divide the range of risks into 4 quartiles. You also have assets classified into 5 categories (C1, C2, .., C5).

In this approach, the aim is to achieve the maximum overall return, subject to specified requirements on risk mix (percentages in R1 to R4) 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 R4”. Other restrictions might be of the form – “at least 25% should be in the banking sector, and no more than 20% in energy”.)

It is up to you to determine the restrictions that you wish to impose.The onlyrequirement is that they should respect the learning aims of this assignment and therefore they should not in any way trivialise the problem. (As an example, there should be realistic range requirements for each of R1 to R4, and C1 to C5. To require all assets in the portfolio to be in risk category R1, for example, would be to trivialise the problem.)

Use a sensitivity analysis report to comment on how changes to the risk and category constraints might affect the optimum portfolio.

TRIMESTER 1, 2015

MIS775 DECISION MODELS FOR BUSINESS ANALYTICS

2. In this approach, the aim is to optimise without category constraint using the methods of Class 2 – ie considering the overall portfolio risk/return profile. 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. Achieve the maximum of risk adjusted return (Sharpe ratio). (NB – You will have to do some research to identify a suitable “risk-free return” rate to use.)

3. In this approach, we assume that a balanced portfolio of exactly 8 stocks is to be chosen, each representing 12.5% of the portfolio value at the beginning. At least 3 asset categories have to be included. In addition, at most 2 of the assets can be in the riskiest group R4, and at least 2 must be in the least risky group R1. Achieve the maximum overall return, subject to the specified requirements.

For each optimisation, explain the optimisation approach taken, and identify the Excel Solver engine to be used (explaining any particular constraints used – eg that a variable needs to be an integer, or binary).

4. In a report written in Microsoft Word, and embedded into your spreadsheet file, present all your results comparatively in a coherent and compelling manner, and then, based on your assessment of the various approaches, write briefly about the strategy that you would prefer to use for portfolio optimisation. Include a summary table of each chosen portfolio and the basis of choice, with percentages of assets, return and risk for the 10 years’ of data used to choose the portfolio, and return and risk in the last year (the data not used to choose the portfolio).

Assignments will be marked based on the methodologies adopted, and the quality of presentation of the results, and your assessment of these. Given the vast range of assets to select from on the yahoo site it is highly unlikely that you will choose the same portfolio of stocks as another student.

You will need to submit:

1. Historic data for the selected assets, and the basic covariance matrices

1. The optimisation models

1. A short report (no more than than 4 pages) outlining the approach taken, and optimal values.

TRIMESTER 1, 2015

MIS775 DECISION MODELS FOR BUSINESS ANALYTICS

Marking Scheme for Assignment 2	Marks
Prelim – 20 marks
Data acquisition	10
Classifications	10
Part 1 – 30 marks
Model	9
Solver	7
Results	4
Sensitivity Analysis	10
Part 2a – 40 marks
Model	18
Solver	14
Results	8
Part 2b – 20 marks
Model	9
Solver	7
Results	4
Part 2c – 20 marks
Model	9
Solver	7
Results	4
Part 3 – 20 marks
Model	9
Solver	7
Results	4
Part 4 – 50 marks
Content	14
Analysis	18
Overall style, coherence, language	8
Last 12 months results	10
TOTAL – 200 marks	200

TRIMESTER 1, 2015