21
Quantitative Applications in Finance
(FIN201)
Assignment Marking Guide
Total marks: 100
Personal ID:
[Enter your Personal ID]
I have read the Assignment Guide in the ‘General assessment information’ and have applied the word count principles to my work.
My word count for this assignment is:
[Enter your word count] words
Your assignment should be loaded into KapLearn by 11.30 pm AEST on the due date
Checklist
I have completed my assignment using Word and Excel.
I have completed my assignment using Cali
i, Arial, Times New Roman or Verdana fonts.
I have added my Personal ID on this page.
I have added my word count on this page.
I have added my Personal ID in front of the filename in the footer on the second page.
I have saved the file to be uploaded as PersonalID_FIN201_AS_v6
Each question of my assignment is within the word limit guidelines for that question as per the ‘General assessment information’ (Assessment Assignment General assessment information).
My assignment Word file size is no larger than 2 MB.
If tables were required, they are visible as text, not as links or images.
I have not removed the marking grid from the footer.
I have submitted my assignment as per the instructions in KapLearn.
Marker feedback
Comment on overall performance:
For marker use only.
Criteria-based Marking Guide
The Criteria-based Marking Guide provided at the end of each question is designed to assist students to understand what is expected of them in each question and to let them know how their performance will be judged. It provides advice about the criteria used in the marking of the question and what discriminates between an excellent, satisfactory and unsatisfactory answer.
Data file and Excel Template
You have been provided with data on a number of stocks and an index in order to run a number of statistical analysis techniques. The data to enable you to answer Questions 1 through 8 is contained in ‘Assignment Excel template’ (i.e. FIN201_AS_v6.xlsx) under ‘Assignment’ in KapLearn.
The workbook is separated into worksheets for each question, which have been labelled accordingly. As a guide, cells that might need to be populated by formulas (or Excel outputs) in order to answer the question have been shaded yellow.
Note: The excel template and yellow shaded areas are a guide only. You may alter the spreadsheet if you wish providing your workings are shown
Section A (Questions 1 to 5)
You have been provided a sample of historical price level of the following shares and index at the end of each month from June 2007 to June 2017 (‘Section A_Monthly Price&Index’ tab) in order to run a number of statistical analysis techniques.
Qantas Airways Limited (QAN)
Telstra Corporation Limited (TLS)
Westpac Banking Corporation (WBC)
All Ordinaries (AORD)
Question 1 (10 marks | Word limit: 200 words)
(a) Using the historical price level of the QAN, TLS and WBC shares and AORD index available in the tab ‘Section A_Monthly Price&Index’, calculate the monthly rate of returns (%) of these shares and index to two (2) decimal places in the tab ‘Section A_Q1’. From your Excel results, paste a copy of the monthly returns for the shares and index for the months of July 2007 and June 2017 only in the Word document of your assignment (and in the format below). (2 marks)
Month
QAN returns
TLS returns
WBC returns
AORD returns
July 2007
June 2017
(b) Use Excel’s Data Analysis to work out the descriptive statistics for these monthly returns to two (2) decimal places. From your Excel results, paste a copy of the following descriptive statistics in the Word document of your assignment (and in the format below). (4 marks)
QAN returns
TLS returns
WBC returns
AORD returns
Sample size
Sample mean (%)
Sample standard deviation (%)
Sample variance (%)
Skewness
Kurtosis
(c) Evaluate QAN, TLS, WBC and AORD return series, with reference to sample mean, sample standard deviation, skewness and kurtosis statistics. (4 marks)
Criteria-based marking guide for Question 1(a)–(c)
Excellent (Mark range: 8–10 marks)
Satisfactory (Mark range: 5–7.5 marks)
Unsatisfactory (Mark range: 0–4.5 marks)
• Co
ect calculation of monthly rate of returns and presenting the results for the appropriate months in word document
• Co
ect use of Excel’s Data Analysis
• Accurately fill the table of descriptive statistics in word document
• Accurately evaluate four return series with respect to sample mean, sample standard deviation, skewness and kurtosis statistics
• Mostly co
ect calculation of monthly rate of returns
• Encounter problems in the use of Excel’s Data Analysis
• Encounter problems in filling the table of descriptive statistics in word document
• Encounter problems when evaluating four return series with respect to sample mean, sample standard deviation, skewness and kurtosis statistics
• Inco
ect calculation of monthly rate of returns
• Unable to use Excel’s Data Analysis
• Unable to fill the table of descriptive statistics in word document
• Unable to evaluate four return series with respect to sample mean, sample standard deviation, skewness and kurtosis statistics
Insert your answers to Question 1
End of answers to Question 1(a)–(c)
Question 2 (10 marks | Word limit: 200 words)
a) Set up a hypothesis test at 5% significance level to determine whether QAN’s mean return is significantly different from zero. Show all your workings in the Word document.
i. What are the null and alternative hypothesis for this test? (1 marks)
ii. Explain the risks associated with Type I and Type II e
ors in this context. (2 marks)
iii. What type of test and distribution would you recommend for this test? Provide reasons for your recommendation. (1 marks)
iv. Based on your recommendation above, find the appropriate critical value, calculate the test statistic for the hypothesis testing at 5% significance level. (2 marks)
v. What is your conclusion for this hypothesis test? (1 marks)
) Set up the null hypothesis and alternative hypothesis to determine whether TLS’s monthly return is significantly different from WBC’s monthly return.
i. What are the null and alternative hypothesis for this test? (1 marks)
ii. By Using Excel’s paired t-Test (see tab Section A_Q2), find the critical value and test statistic for the hypothesis testing at 5% significance level. (1 marks)
iii. What is your conclusion for this hypothesis test? (1 marks)
Criteria-based marking guide for Question 2(a)–(b)
Excellent (Mark range: 8–10 marks)
Satisfactory (Mark range: 5–7.5 marks)
Unsatisfactory (Mark range: 0–4.5 marks)
• Co
ect set up of the null and alternative hypothesis
• Accurately explain the risks associated with Type I and Type II in in relation to hypothesis testing determining whether QAN’s mean return is significantly different from zero
• Co
ectly identify the appropriate hypothesis test for the QAN’s returns with appropriate justification
• Co
ect critical value, the test statistic at 5% significance level and conclusion
• Co
ect result of paired t-test for BSL and CBA monthly returns and conclusion
• Co
ect set up of the null and alternative hypothesis
• Adequate explanation of the risks associated with Type I and Type II in in relation to hypothesis testing determining whether QAN’s mean return is significantly different from zero
• Mostly co
ect identification of the appropriate hypothesis test for the QAN’s returns with appropriate justification
• Mostly co
ect critical value, the test statistic at 5% significance level and conclusion
• Mostly co
ect result of paired t-test for BSL and CBA monthly returns and conclusion
• Inco
ect set up of the null and alternative hypothesis
• Inadequate explanation of the risks associated with Type I and Type II in in relation to hypothesis testing determining whether QAN’s mean return is significantly different from zero
• Inco
ectly identify the type of hypothesis test for the QAN’s returns and inco
ect justification
• Inco
ect critical value, the test statistic at 5% significance level and conclusion
• Inco
ect result of paired t-test for BSL and CBA monthly returns and conclusion
Insert your answers to Question 2
End of answers to Question 2(a)–(b)
Question 3 (10 marks | Word limit: 500 words)
An outlier of a sample may affect the accurate statistical analysis. If an observation is away from its sample mean by more than 3 standard deviations, it can be considered as an outlier.
(a) Standardise the monthly returns using the formula to two (2) decimal places using tab ‘Section A_Q3’. From your Excel results, paste a copy of the standardised returns for the shares and index for the months of July 2007 and June 2017 only in the Word document of your assignment (and in the format below). (4 marks)
Month
QAN standardised returns
TLS standardised returns
WBC standardised returns
AORD standardised returns
July 2007
June 2017
(b) Identify the only standardised monthly return which is either greater than 3 or smaller than -3 (i.e. more than 3 standard deviations away from zero). Report the stock, month, actual value and standardised value. (2 marks)
(c) You decide to replace the outlier with the mean observation calculated for Question 3 in ‘Section A_Q3’ tab instead. Explain how this is likely to affect the mean return, standard deviation, skewness, and kurtosis of the BSL monthly returns (note: no calculations are required). (2 marks)
(d) What are two alternative adjustments you could make to the data in order to avoid having the outlier skew the results. (2marks)
Criteria-based marking guide for Question 3(a)–(d)
Excellent (Mark range: 8–10 marks)
Satisfactory (Mark range: 5–7.5 marks)
Unsatisfactory (Mark range: 0–4.5 marks)
• Co
ect standardisation of monthly rate of returns and presenting the results for the appropriate months in word document
• Co
ect identification of the outlie
• Accurately explain the effect of replacing the outlier with the mean on descriptive statistics and provide two alternative adjustments to the data.
• Mostly co
ect standardisation of monthly rate of returns
• Encounter problems in the identification of the outlie
• Encounter problems in explaining the effect of replacing the outlier with the mean on descriptive statistics and provide two alternative adjustments to the data.
• Inco
ect standardisation of monthly rate of returns
• Unable to identify outliers
• Unable to explain the effect of replacing the outlier with the mean on descriptive statistics and provide two alternative adjustments to the data.
Insert your answers to Question 3:
End of answers to Question 3(a)–(d)
Question 4 (20 marks | Word limit: 600 words)
Run a regression analysis for WBC stock against the AORD Index using the monthly rate of returns data from July 2007 to June 2017 inclusively (see tab Section A_Q4).
Hint: Use the ‘Regression’ function in the ‘Data Analysis’ add-in. Select ‘Residuals’ and ‘Residual plots’ from the regression window.
(a) Provide the estimated regression equation for WBC, with coefficients to four (4) decimal places.
(2 marks)
(b) Predict the WBC returns when AORD is XXXXXXXXXXto four (4) decimal places. (2 marks)
(c) At 0.05 level of significance, is there evidence of a linear relationship between WBC and AORD? Show all your workings in relation to your conclusion. (3 marks)
(d) What is the R-square statistic and interpret its meaning for this regression? (2 marks)
(e) Generate the residual plot (scatter plot of residuals of the above regression against AORD) for the above regression. Past your residual plot from excel file to word document. Comment on heteroscedasticity of this regression with reference to the residual plot. (2 marks)
(f) Discuss the ways of eliminating heteroscedasticity issues.