Portfolio Construction:

Collect price data for your 4 assets from the investment horizon – 10 years commencing June 30, 2007 to June 30 2017.

• The price data should be MONTHLY. You will find the price data from: Yahoo finance; the ASX; or the company's own website
• Download the price data for the sample period required.

1. Calculate the mean return and standard deviation of each investment asset
2. Calculate the mean return and standard deviation of the market portfolio (ASX/S&P200)

Generate the variance/covariance matrix for: the individual assets; your portfolio; and the market portfolio

1. Calculate the mean return and standard deviation of an equally weighted portfolio comprised of these four assets
2. Calculate the Sharpe ratio for: the individual assets; your portfolio; and the market portfolio.
3. Construct the CML based on the market portfolio and the risk free asset. Chart the CML.

(10 marks)

Portfolio Optimisation:

Use the Solver Addin function in Excel, determine the optimal weights for the four assets in your portfolio. Optimal weights will be those that give you a portfolio with the highest mean return for a given level of risk (ie. you want to maximise the Sharpe Ratio by allowing the weights for each asset to change). (10 marks)

Empirical Estimation with regression:

You are now required to estimate the data with a regression analysis.

Step 1. Run a regression using the market model for each individual asset and your equally weighted portfolio and report your output.

Download Asian Pacific ex Japan Fama French factors from the website

Step 2. Run a regression using Fama French three factors models for each individual asset and your equally weighted portfolio and report your output.

To use regression in excel, you need to add in the data analysis to your excel (for help, please refer to the link). There are also plenty of youtube videos on simple regression. (10 marks)

Evaluation:

Provide a brief summary of your analysis in a word document containing charts or tables of your key findings and a brief discussion of the important aspects of your work from Questions 1 to 4. (10 marks)

An equity investment portfolio was created by picking 4 stocks, namely Caltex Limited, Flexi group Limited, Spark Infrastructure Limited and Telstra Corporation from the ASX 200. The analysis has been done using monthly rates of stock returns.

Portfolio construction: All the stocks selected for the portfolio were given equal weightage (25%) to start. Out of the 4 stocks, Flexi group Ltd gives the highest monthly returns with the highest standard deviation.

Higher the Sharpe ratio, the more is the suitability of the security for investment. As mentioned in the table below, Flexi group Ltd has the highest Sharpe ratio. Hence, Flexi group is the most suitable for equity investment. On the other hand, a negative Sharpe ratio may arise because of abnormal market conditions in which equity return is lesser than the risk-free rate of government securities.

(Some parts of the solution has been blurred due to privacy protection policy)