Quantitative
Applications in Finance
(FIN201)
Assignment
Total
marks: 100
| Student No: |
[Enter your Student No.] |
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 on |
Checklist
I have completed my assignment using Word and Excel.
I have completed my assignment using Calibri, Arial, Times New
Roman or Verdana fonts.
I have added my Student No. on this page.
I have added my word count on this page.
I have added my Student No. in front of the filename in the
footer on the second page.
I have saved the file to be uploaded as StudentNo_FIN201_AS_v7A3
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 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. |
|
Instructions • This assignment covers Topics 1 to 10 and accounts for 50% of • There are two (2) sections in this assignment: – Section A comprises six (6) short-answer questions worth a – Section B comprises two (2) short-answer questions worth a • You should answer all questions. • Download the ‘Assignment Excel template’ (i.e. • Your assignment submission must contain a Word • Submit all Excel workings in the appropriate • The overall word limit for the assignment is 3000 words. Marks • Refer to the Criteria-based Marking Guide for • The ‘General assessment information’ section in KapLearn • Full workings must be shown for all calculations. Show all • Answers are to be in your own words. Reference and cite all • Indicative weightings are noted beside each question. Use • State all assumptions used in providing your • Requests for special consideration or information pertaining • Please note: As with most post-graduate, |
|
Learning |
Marks |
|
1. Examine the various quantitative techniques used to describe, |
15 |
|
2. Evaluate the strengths and limitations of statistical analysis |
21 |
|
3. Apply quantitative techniques to describe financial data and |
12 |
|
4. Analyse the factors that influence the risk and return of asset |
32 |
|
5. Assess statistical models used in estimating the value of |
20 |
|
Total marks |
0 |
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_v7.xlsx)
under ‘Assignment’ in KapLearn. Again, your excel file submission
is to help show your workings. All answers must be clearly shown
in your word document submission.
The Excel 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 (70 marks)
|
Instructions |
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)
Question
1 (10 marks | Word limit: 200 words)
LO1: Examine the various quantitative
techniques used to describe, estimate and evaluate data.
LO2: Evaluate the strengths and limitations of
statistical analysis techniques in finance.
(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 answer section of your Word document (and in the format
below). (2 marks)
| Month |
QAN returns |
TLS returns |
WBC returns |
AORD returns |
|
July |
||||
|
June |
(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 answer section of your Word document
(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: |
Satisfactory (Mark range: |
Unsatisfactory (Mark |
|
• correct calculation of • correct use of Excel’s • accurately fill the • accurately evaluate four |
• mostly correct • encounter problems in • encounter problems in • encounter problems when |
• incorrect calculation of • unable to use Excel’s • unable to fill the table • unable to evaluate four |
Insert your answers to
Question 1(a)–(c)
End of answers to Question 1(a)–(c)
Question
2 (10 marks | Word limit: 200 words)
LO1: Examine the various quantitative
techniques used to describe, estimate and evaluate data.
LO2: Evaluate the strengths and limitations of
statistical analysis techniques in finance.
LO3: Apply quantitative techniques to describe
financial data and test claims of performance.
(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
mark)
(ii) Explain the risks associated with Type I and Type II errors
in this context. (2 marks)
(iii) What type of test and distribution would you recommend for
this test? Provide reasons for your recommendation. (1
mark)
(iv) Based on your recommendation above, determine the appropriate
critical value and test statistic for the hypothesis testing at 5%
significance level. (2 marks)
(v) What is your conclusion for this hypothesis test? (1
mark)
(b) 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
mark)
(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 mark)
(iii) What is your conclusion for this hypothesis test? (1
mark)
Criteria-based marking guide for Question
2(a)–(b)
|
Excellent (Mark range: |
Satisfactory (Mark range: |
Unsatisfactory (Mark |
|
• correct set up of the • accurately explain the • correctly identify the • correct critical value, • correctly identify the • correct result of paired |
• correct set up of the • adequate explanation of • mostly correct • mostly correct critical • most correctly identify • mostly correct result of |
• incorrect set up of the • inadequate explanation • incorrectly identify the • incorrect critical • incorrectly identify the • incorrect result of |
Insert your answers to
Question 2(a)–(b)
End of answers to Question 2(a)–(b)
Question
3 (10 marks | Word limit: 500 words)
LO1: Examine the various quantitative
techniques used to describe, estimate and evaluate data.
LO3: Apply quantitative techniques to describe
financial data and test claims of performance.
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 answer section of
your Word document (and in the format below). (4
marks)
|
Month |
QAN |
TLS |
WBC |
AORD |
|
July 2007 |
||||
|
June 2017 |
(b) Identify all the standardised monthly returns which are 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 1 in ‘Section A’ instead.
Explain how this is likely to affect the mean return,
standard deviation, skewness, and kurtosis of the 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. (2
marks)
Criteria-based marking guide for Question
3(a)–(d)
|
Excellent (Mark range: |
Satisfactory (Mark range: |
Unsatisfactory (Mark |
|
• correct standardisation • correct identification • accurately explain the |
• mostly correct • encounter problems in • encounter problems in |
• incorrect • unable to identify • unable to explain the |
Insert your answers to
Question 3(a)–(d)
End of answers to Question 3(a)–(d)
Question
4 (20 marks | Word limit: 600 words)
LO1: Examine the various quantitative
techniques used to describe, estimate and evaluate data.
LO2: Evaluate the strengths and limitations of
statistical analysis techniques in finance.
LO3: Apply quantitative techniques to describe
financial data and test claims of performance.
LO4: Analyse the factors that influence the
risk and return of asset classes and financial securities.
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 the monthly return on the AORD is
7.5%, as a % to two (2) 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. Paste your
residual plot from excel file to word document. Comment on
heteroskedasticity of this regression with reference to the residual
plot. (2 marks)
(f) Discuss the ways of eliminating heteroskedasticity issues. Your
discussion requires three (3) points. (3 marks)
(g) Calculate the Durbin-Watson statistic for the above regression,
and provide this in the Word document of your assignment, to four (4)
decimal places. Comment on the serial correlation of this regression
with reference to Durbin-Watson statistic. (3
marks)
(h) Discuss the ways of eliminating serial correlation issues. Your
discussion requires three (3) points. (3 marks)
Criteria-based marking guide for Question
4(a)–(h)
|
Excellent (Mark range: |
Satisfactory (Mark range: |
Unsatisfactory (Mark |
|
• correct use of excel’s • correctly predict the • correct setup of null • correctly identify and • relevant residual plot • correctly discuss three • accurately calculate the • correctly identify • correctly discuss three |
• encounter problems in • predict the WBC returns • mostly correct setup of • correct identification • mostly correct residual • correctly discuss two • encounter problems in • most correctly identify • correctly discuss two |
• unable to use Excel’s • incorrectly predict the • incorrect setup of null • incorrectly identify and • mostly correct residual • incorrectly discuss ways • unable to calculate the • incorrectly identify • incorrectly discuss ways |
Insert your answers to
Question 4(a)–(h)
End of answers to Question 4(a)–(h)
Question
5 (10 marks | Word limit: 300 words)
LO1: Examine the various quantitative
techniques used to describe, estimate and evaluate data.
LO2: Evaluate the strengths and limitations of
statistical analysis techniques in finance.
LO4: Analyse the factors that influence the
risk and return of asset classes and financial securities.
(a) Discuss the use of autoregressive (AR) models in finance. (4
marks)
(b) Run an AR1 model for TLS stock using the monthly rate of returns
data from August 2007 to June 2017 inclusively (see tab Section
A_Q5).
AR(1) model for TLS stock:
Hint: Use the ‘Regression’ function in the ‘Data Analysis’
add-in.
(i) Provide the estimated AR(1) equation for AR(1) model for TLS
stock, with coefficients to four (4) decimal places. (2
marks)
(ii) What are the implications for estimation and inference if the
errors of the AR(1) model estimated in (i) are also serially
correlated? (2 marks)
(iii) Discuss why is it not appropriate to test for autocorrelation
in an AR model using the
Durbin-Watson test. (2
marks)
Criteria-based marking guide for Question
5(a)–(b)
|
Excellent (Mark range: |
Satisfactory (Mark range: |
Unsatisfactory (Mark |
|
• correctly discuss the • correct use of Excel’s • correct discussion of • correct discussion of |
• most correctly discuss • encounter problems in • mostly correct • mostly correct |
• incorrectly discuss the • unable to use Excel’s • incorrect discussion of • incorrectly discuss the |
Insert your answers to
Question 5(a)–(b)
End of answers to Question 5(a)–(b)
Question
6 (10 marks | Word limit: 300 words)
LO2: Evaluate the strengths and limitations of
statistical analysis techniques in finance.
LO4: Analyse the factors that influence the
risk and return of asset classes and financial securities.
LO5: Assess statistical models used in
estimating the value of financial assets and constructing efficient
portfolios.
An equally weighted three-stock portfolio consist of shareholdings in
Qantas Airways Limited (QAN), Telstra Corporation Limited (TLS)
and Westpac Banking Corporation (WBC).
Calculate the portfolio returns (in the column named ‘ri’)
using the monthly returns data from July 2007 to June 2017 (to two
(2) decimal places) in the tab ‘Section A_Q6’.
Consider the CAPM regression model given below:
Run a regression analysis for above CAPM model using the portfolio
monthly rate of returns data from July 2007 to June 2017
inclusively. Use 3.0% p.a. as the risk-free return () and AORD
monthly returns as market returns ().
Hint: Use the ‘Regression’ function in the ‘Data
Analysis’ add-in.
(a) Provide the estimated CAPM regression equation, with
coefficients to four (4) decimal places. (2 marks)
(b) What is the beta (β) value of the portfolio? (1
mark)
(c) Comment on the portfolio beta value in comparison to the slope
coefficient of the regression equation for WBC (WBC stock’s beta
value) which you have obtained in Question 4. (3
marks)
(d) Discuss the limitations of CAPM. (4
marks)
Criteria-based marking guide for Question
6(a)–(d)
|
Excellent (Mark range: |
Satisfactory (Mark range: |
Unsatisfactory (Mark |
|
• correct use of Excel’s • correctly identify the • accurate comparison • correctly identify four |
• encounter problems of • correctly identify the • mostly accurate • fair identification of |
• encounter problems of • incorrectly identify the • inaccurate comparison • poor discussion of the |
Insert your answers to
Question 6(a)–(d)
End of answers to Question 6(a)–(d)
Section
B (30 marks)
|
Instructions |
Question
7 (11 marks | Word limit: 300 words)
LO2: Evaluate the strengths and limitations of
statistical analysis techniques in finance.
LO4: Analyse the factors that influence the
risk and return of asset classes and financial securities.
LO5: Assess statistical models used in
estimating the value of financial assets and constructing efficient
portfolios.
You have been provided a sample of historical monthly returns of the
following shares and index at the end of each month from July 2007 to
June 2017 (‘Section B_Q7_Monthly Returns’ tab) in order to run a
number of statistical analysis techniques.
AGL Energy Limited (AGL)
Commonwealth Bank of Australia (CBA)
Graincorp Limited (GNC)
Melbourne IT Limited (MLB)
(a) Using the monthly returns from July 2007 to June 2017, calculate
the variance-covariance matrix of AGL, CBA, GNC and MLB to five (5)
decimal places on the’ Section B_Q7’ tab.
From your Excel results, paste a copy of the covariance matrix in
the answer section of your Word document (and in the format below).
(4 marks)
|
Monthly return |
||||
|
AGL |
CBA |
GNC |
MLB |
|
|
AGL |
||||
|
CBA |
||||
|
GNC |
||||
|
MLB |
(b) Suppose you have a portfolio with an equal amount into these
four shares (i.e. the portfolio consists of ¼ of AGL, ¼ of CBA, ¼
of GNC and ¼ of MLB). Using Excel or otherwise, calculate the
variance of this portfolio, to five (5) decimal places and
report it on the word file. (3 marks)
(c) You expect the long-term monthly returns of the stocks to be as
follows:
|
AGL |
0.54% |
|
CBA |
0.55% |
|
GNC |
0.27% |
|
MLB |
0.60% |
Use the variance/covariance matrix you have calculated for part a)
and find the expected optimal allocation (portfolio weight) to each
share in the portfolio (weights must all be greater than
zero and add up to 100%) with least expected variance but with an
expected return of at least 0.55% per month. Provide the weights (%)
of the optimised portfolio and its expected variance to five (5)
decimal places). (4 marks)
Hint: Use Excel’s Solver function.
|
Optimised weights |
|||||
|
AGL |
CBA |
GNC |
MLB |
||
|
Weights (%) |
|||||
|
Returns (%) |
0.54% |
0.55% |
0.27% |
0.60% |
0.55% |
|
Variance |
Criteria-based marking guide for Question
7(a)–(c)
|
Excellent (Mark range: |
Satisfactory (Mark range: |
Unsatisfactory (Mark |
|
• correct calculation of • within the constraints |
• almost correct • encounter problems in |
• incorrect calculation of • unable to work out the |
Insert your answers to
Question 7(a)–(c)
End of answers to Question 7(a)–(c)
Question
8 (19 marks | Word limit: 600 words)
LO2: Evaluate the strengths and limitations of
statistical analysis techniques in finance.
LO4: Analyse the factors that influence the
risk and return of asset classes and financial securities.
LO5: Assess statistical models used in
estimating the value of financial assets and constructing efficient
portfolios.
Note: Use Figure 1 to answer part (a) of this question
below.
Figure
1
(a) (i) What does Figure 1 depict and what is
the financial name or description given to the diagram?
(2 marks)
(ii) Which portfolio (A or B) would be
preferred and why? (2
marks)
Section B_Q8’ tab of the excel workbook gives three portfolio
returns (Portfolio_A, Portfolio_B and Portfolio_C). The risk-free
rate is given as 3.00% p.a.
(b) Based on the monthly returns data from July
2007 to June 2017 and assuming the risk-free rate is 3.0% p.a.,
calculate the Sharpe ratios for each of the three portfolios (A, B,
and C), as a % rounded to two (2) decimal places. Compare the three
portfolios based on their Sharpe ratios. (3
marks)
(c) Discuss the advantages and disadvantages of Sharpe ratio for
measuring portfolio performance. (2 marks)
(d) What is tracking error? (2
marks)
(e) Based on the given monthly returns date from July 2007 to June
2017 and using AORD returns (from Section A of the assignment)
as the benchmark calculate the annualised tracking error (%)
of Portfolio_A, Portfolio_B and Portfolio_C, to two (2) decimal
place. Compare three portfolios based on the tracking error. (3
marks)
Note: Use standard deviation method.
(f) Based on the given monthly returns date from July 2007 to June
2017 and using AORD returns as the benchmark calculate the
information ratio (%) of Portfolio_A, Portfolio_B and Portfolio_C, to
two (2) decimal place. Compare three portfolios based on the
information ratio. (3
marks)
(g) Discuss the advantages and disadvantages of information ratio
for measuring portfolio performance. (2
marks)
Criteria-based marking guide for Question
8(a)–(g)
|
Excellent (Mark range: |
Satisfactory (Mark range: |
Unsatisfactory (Mark |
|
• correct explanation of • correctly calculate • correct identification • correctly calculate • correctly calculate • correct identification |
• mostly correct • most correctly calculate • mostly correct • most correctly calculate • most correctly calculate • mostly correct |
• incorrect explanation of • incorrectly calculate • correct identification • incorrectly calculate • incorrectly calculate • incorrect identification |
Insert your answers to
Question 8(a)–(g)
End of answers to Question 8(a)–(g)
End of
Assignment
The post Quantitative Applications in Finance (FIN201) appeared first on My Assignment Online.