BFC5935: Portfolio Management Theory INDIVIDUAL ASSIGNMENT 01 [ASM01] (calculate Return, Variance, Standard Deviation, and Beta (β)
INDIVIDUAL ASSIGNMENT01
Objectives: This assignment is designed to show the application of the topics covered so far – namely, Topic 1 Asset & Investments, Topic 2 Portfolio Theory and Topic 3 Asset Pricing Models –with real financial data and Excel. It is also to further develop your technical and analytical skills, which will help with your future employability.
Tasks: You are required to construct portfolios using monthly data of JB Hi-Fi Limited (JBH), and Harvey Norman Holdings Limited (HVN), and Woodside Energy Group Ltd (WDS) stocks listed on the ASX (Tip: put ‘.ax’ after the stock ticker and check that the company’s full name is correct), over the following sample period based on your Allocated Tutorial.
PART 1 [7 Marks]
[1]
Download monthly JBH, HVN & WDS (Adj Close) price data from Yahoo Finance: https://au.finance.yahoo.com/
[Note that although this is Yahoo AU and that it shows the required date format to be 'dd/mm/yyyy'
- when you actually input the date, it will be based on the US format with month first - i.e. 'mm/dd/yyyy'. You will have to select 'Monthly' for Frequency, and by default, Yahoo Finance will provide the end of month prices.]
Input the raw data into Worksheet W1-Monthly Data
[Note] You will need to also obtain the month prior to the starting of the sample period so that you can calculate the return in JAN at the beginning of sample period. For example, if your sample period is 2003-2005, then you will also need to obtain the data in DEC 2002.
[2]
In Worksheet W2-Calculations – Calculate Monthly Returns based on the price relative (or HPY) formula [(Pt+1 / Pt) – 1] for JBH & HVN and the portfolio consisting of two stocks – Portfolio 1: JBH & HVN – each with 50% weight. Once the series of monthly returns are obtained, calculate the Arithmetic Average Return (using the simple AVERAGE Function in Excel) of JBH & HVN and Portfolio 1 over the sample period. [Note] Based on the previous example, JAN 2003 will be ‘Month 1’ and DEC 2002 will be ‘Month 0’ in the Template.
[3]
In Worksheet W2-Calculations – calculate Variance and Standard Deviation (based on the formula taught in class – and also apply ‘n-1’ as the denominator ([Note] Do not use excel function for this) for JBH & HVN and Portfolio 1. Monthly Returns, Variance and Standard Deviation are then annualised.
[Note] To annualise, Monthly Returns and Variance are multiplied by 12. Annual Standard Deviation will be the square root of Annual Variance.
[4]
In Worksheet W2-Calculations – Calculate Covariance between these two stocks over the sample period using the formula taught in class.
[Note] To use formula taught in class, first find the difference between return and mean each month for each stock, and second, find the product of the differences of the two stocks.
[5]
In Worksheet W2-Calculations – Calculate Correlation between these two stocks over the sample period using the formula taught in class using monthly (not annual) figures.
[6]
In Worksheet W2-Calculations – Calculate the Optimal Weights of these two stocks using the formula taught in class, and subsequently the Minimum Variance Portfolio 1’s (MVP 1) Return and Risk (standard deviation) based on the optimal weights using monthly (not annual) figures.
[7]
In Worksheet W2-Calculations – Calculate the Coefficient of Variations of JBH & HVN, Portfolio 1 and MVP 1 using monthly (not annual) figures.
PART 2 [7 Marks]
[8]
In Worksheet W3-Calculations – Repeat Steps [2] to [7] using JBH & WDS. In this worksheet, the calculations will be for JBH, WDS, Portfolio 2 and MVP 2.
PART 3 [4 Marks]
[9]
In Worksheet W4-Beta – Calculate the Beta’s of JBH & WDS with the Market (Index) Return. (i.e. each stock with the market)
[Note] You have already calculated stock returns previously. Now you need to calculate Excess Returns for both stocks and Market. To run regression, you will need to first add Analysis Tool Pack in Excel.
Adding Analysis Tool Pack as well as running regression have been discussed & demonstrated Lecture 1 & Lecture 3 – please refer to the recordings.
You need to have the regression results in the same Worksheet W4-Beta – output the results from
column ‘K’ onwards in the same worksheet W4.
PART 4 [12 Marks]
In Worksheet W5-Analysis – Answer the following questions in the empty cells provided.
[Words Limit per question is 200 words]
[10A]
With reference to W2 & W3 - Calculations, compare and contrast the Return and Risk of Portfolio 1 with Portfolio 2. Which is considered to be more efficient? Explain why this is the case.
[10B]
With reference to W3 - Calculations, compare the performances of different assets (JBH, WDS, Portfolio 2 and MVP 2) based on coefficient of variations. Explain why this is expected.
[10C]
With reference to W4 - Beta, provide the interpretations of the regression output, specifically the explanations of Intercept and Beta (X Variable 1) and their significance (p-values) in W4-Beta.
[Note] Null Hypothesis of the Regression Variable(s) is that it is equal to zero (this and running regression in Excel were also covered in Business Statistics). If p-value is less than
0.05 (0.01), then it is considered to be significant at the 5% (1%) level – that is, the coefficient is statistically & significantly different from zero.
[10D]
With reference to W4 - Beta, are the regression results consistent with the CAPM predictions?
Explain. And ‘If’ the results are inconsistent, also explain the potential reason(s), based on the concepts covered in BFC5935.
PART 5 [6 Marks]
[11]
In Worksheet W6-Return Distribution – Transfer the stock returns from W3 - Calculations, then apply ‘Data Analysis’ to obtain the ‘Descriptive Statistics’ of the stock returns. – output the results from Cell ‘F3’ onwards, in the same worksheet W6.
[Note: the demo is at the end of Lecture 1]
[12]
Compare and contrast the Return Distribution (based on materials covered in class) of JBH and WDS. [Words Limit: 100 words]