Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: zz-x2580
BFC5935: Portfolio Management Theory
Submission: You will need to use the Excel Spreadsheet Template posted on Moodle and submit via Moodle by the due date &
time as stated in the Unit Guide. Late submission will result in 10% penalty each day (including weekend & public holiday).
You are also required to submit the ‘signed’ assignment cover sheet confirming that this is your own work. The assignment without
the signed cover sheet will be deemed incomplete and the late penalty will apply.
[IMPORTANT] After uploading the files (it will appear as draft, but the files are actually not submitted yet), you will need to click
'submit' and the ‘Status’ will be indicated as ‘Submitted for grading’ .
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.
Refer to the ASM Supporting Document on Moodle for the important details of downloading and using data from Yahoo Finance.
[2] In Worksheet W2-Calculations – Calculate Monthly Returns based on the price relative (or HPY) formula [(Pt+1 / Pt) – 1] for NAB
& CBA and the portfolio consisting of two stocks – Portfolio 1:
NAB & CBA - each with 50% weight. Once the series of monthly returns are obtained, calculate the Arithmetic Average Return (using th
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 NAB & CBA 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
Risk (standard deviation) based on the optimal weights using monthly (not annual) figures.
[7] In Worksheet W2-Calculations – Calculate the Coefficient of Variations of NAB & CBA, 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 NAB & QAN. In this
worksheet, the calculations will be for NAB,QAN, Portfolio 2 and MVP 2.
PART 3 [4 Marks]
[9] In Worksheet W4-Beta – Calculate the Beta’sof NAB & QAN 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 r
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? Identify potential factor(s) that cause one portfolio to
be more efficient than the other.
[10B] With reference to W3 - Calculations, compare the performances of different assets (NAB,
QAN, Portfolio 2 and MVP 2) based on coefficient of variations. In reference to the characteristics of NAB and QAN, explain
why this is expected.
[10C] With reference toW4 - 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 toW4 - 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.
