ASSESSMENT DETAILS

In this assessment, you will:

Develop an Excel worksheet model to perform a risk analysis; and

Prepare a written statement that justifies your analysis and communicates the risks to an investor.

INSTRUCTIONS: CREATE YOUR FINANCIAL MODEL

To get started on your assessment task, please follow the instructions below.

1. Identify the 10 best performing stocks on the Australian Stock Exchange (ASX) and get the past five years weekly stock price data using @stockhistory. Once you download the price data, copy, and paste only the values and format into another sheet (in the same workbook).

2. Determine the weekly compound returns for each stock.

3. For each stock, calculate the average return (both geometric and simple average), standard deviation, coefficient of variation, cumulative wealth index, and parametric value at risk.

4. Comment on the estimated statistics. Do you think the above statistics are sufficient for a rational investor?

5. Now, construct the variance and covariance matrix and the correlation matrix. Based on the estimated coefficients of two matrices, rank the investments in order of their appropriateness for an investment portfolio. Comment on your ranking.

6. Assume you are planning to equally invest your money in all 10 stocks. Estimate the portfolio standard deviation and return on your portfolio.

7. Use the Excel Solver and find the portfolio return and standard deviation of the minimum variance portfolio and the optimum portfolio (assuming annual risk-free rate of return is 4%).

8. If you want to forgo 2% of the portfolio return and invest some of your money in risk-free assets, what would be the composition of your investment portfolio? Estimate the return and standard deviation of the portfolio.

WRITTEN STATEMENT/JUSTIFICATION OF RISK ANALYSIS (800 WORDS)

Structure your written statement as follows:

a) Start by explaining how your analysis is useful to a client making investment decisions. (250 words)

b) Now identify the types of risk you have analysed using the Excel worksheet. Explain what they are, with support from evidence. Justify how your modelling mitigates against those risks. (300)

c) Conclude by explaining to the client how your financial modelling can be used for assessing, quantifying, and interpreting the risks associated in investing in ASX stocks. Support each point with evidence/examples from your analysis. (250 words)