Assignment Details

This assignment is designed to let you explore and evaluate a number of approaches for portfolio optimisation, using live real-world data from the Yahoo finance website. The relevant URL for finding stock prices is https://au.finance.yahoo.com/. Under the “Quote lookup”, you can search for the industry/business you are interested in to explore investment (assets).

Portfolio Optimisation - Risk and Return: An investment portfolio consists of a collection of investments held by an individual or organisation, in which the investor seeks to purchase a variety of assets to gain a good return (profit) through increasing assets value. Individual assets vary in value from minute to minute, and whilst over time, they might grow in value, their value fluctuates over time. The possibility of such fluctuations represents a risk to the investor. Accordingly, in portfolio selection investors should wisely choose to invest across a range of assets, ideally whose total value is less liable to fluctuation than the individual assets.

In this assignment, you are required to use asset return data from a period of 4 years to identify the optimum portfolio using a variety of different optimisation methods. The assignment has three main sections: Preliminary Work, Optimisation Models and Conclusion. The requirements of each part are detailed below. The breakdown of marks (a total of 30) is given on this document and in a separate Rubric file.

 Preliminary Work (5 marks: Data acquisition + Classifications)

The first stage is to identify a set of 10 investment items from which you will subsequently determine optimum portfolios using various optimisation models. You may select any global assets (including indices) whose data is provided on the Yahoo finance website. After you searched for the stock under the “Quote lookup”, go to the “Historical data” tab, then choose the appropriate Time period, and Frequency before downloading the data.

The chosen assets must satisfy the following general conditions:

· Each must have at least 48 months (August 2018 - August 2022) of monthly data available, up to and including August 2022.

· They should be selected from 4 different sectors/categories (let’s called them C1, C2, C3, and C4) e.g., banking, pharmaceuticals, media, technology, government bonds, property trusts, etc. – your choice, with at least 2 assets in each category.

· You need to calculate the return of an investment item each month by (Stock_value_new – Stock_value_old)/(Stock_value_old). Then the overall return of an investment item is then the mean of return over 48 months. Similarly, the risk is calculated as the standard deviation of return over 48 months. A sample Excel file is provided that includes a sample data with the calculation of return and risk.

· Data should span a reasonable range of volatilities/risks. Classify the assets into 4 groups according to (ascending) risk (let’s call them R1, R2, R3, and R4). A simple classification approach would be to calculate the standard deviation of each asset (as explained in the previous dot point) and define risk categories based on the 4 quartiles. Therefore, R1 should include investment stocks with the lowest risk (lowest standard deviation).

· Recall that each asset lies in one of the Rs and in one of the Cs.

 Optimisation Models

The assignment requires you to consider two different optimisation techniques: linear programming, and integer linear programming:

1. LP model (12 marks: Mathematical Model + Excel Solver and Discussion + Sensitivity Analysis): In this approach, the aim is to achieve the maximum overall average return, subject to specified requirements on risk mix (percentages in R1 to R4) and category mix (percentages in C1 to C4). The following investment guidelines are to be applied to the Linear Programming model:

1. Investment in the high-risk assets shouldn’t exceed 15% of the portfolio; while the lowest risk assets should have the highest investments among all other risk categories

2. The ratio investment in R1 to R3 must be at least 2 to 1

3. To ensure diversification, each sector category must have a minimum of 20% invested; apart from the one sector that you choose (your discretion) to have a minimum of 25% invested

Use Excel’s sensitivity analysis report to comment on how changes to the risk and category constraints might affect the optimum portfolio. Further, if an asset(s) is not selected in your optimal solution, how much return should be changed, so that asset be included in your optimal selection.

2. ILP model (8 marks: Mathematical Model + Solver and Discussion): In this approach, we assume that a balanced portfolio of exactly 7 stocks is to be chosen. The 4 asset categories (the C classification) must be included. In addition, at most 1 of the assets can be in the riskiest group, and at least 2 must be in the least risky group. Finally, Investment in R2 should not exceed investment in R1. The goal is to achieve the maximum overall return, subject to these specified requirements.

For each optimisation model, explain the optimisation approach taken, the mathematical formulation and identify the Excel Solver to be used (explain any particular constraints used – e.g., that a variable needs to be an integer or binary).