Financial Modelling and Analysis

This document provides financial data for analysis and modelling. It includes stock market data for a specific period.

Financial Modelling and analysis
1

Table of Contents
Part A...............................................................................................................................................3
Question -1...................................................................................................................................3
1................................................................................................................................................3
2................................................................................................................................................9
3................................................................................................................................................9
Question 2..................................................................................................................................10
1..............................................................................................................................................10
2..............................................................................................................................................10
3..............................................................................................................................................10
Question 3..................................................................................................................................11
1..............................................................................................................................................11
2..............................................................................................................................................11
Question 4..................................................................................................................................12
1..............................................................................................................................................12
2..............................................................................................................................................12
Question 5..................................................................................................................................13
1..............................................................................................................................................13
2..............................................................................................................................................14
3..............................................................................................................................................15
2

Part A
Question -1
1.
Date AllOrd Aoret ( Before)
4/28/2000 3085.1 -1.442416777
5/31/2000 3040.6 7.136749326
6/30/2000 3257.6 -1.350687623
7/31/2000 3213.6 1.496763754
8/31/2000 3261.7 -0.478278198
9/29/2000 3246.1 -1.300021564
10/31/2000 3203.9 0.564936484
11/30/2000 3222 -2.088764742
12/29/2000 3154.7 4.336386978
1/31/2001 3291.5 -0.528634361
2/28/2001 3274.1 -5.412174338
3/30/2001 3096.9 5.599147535
4/30/2001 3270.3 1.434119194
5/31/2001 3317.2 3.255757868
6/29/2001 3425.2 -4.50776597
7/31/2001 3270.8 -1.620398679
8/31/2001 3217.8 -7.141525266
9/28/2001 2988 6.603078983
10/31/2001 3185.3 2.875710294
11/30/2001 3276.9 2.532881687
12/31/2001 3359.9 1.312539064
1/31/2002 3404 -1.333725029
2/28/2002 3358.6 0.13993926
3/29/2002 3363.3 -1.896946451
4/30/2002 3299.5 0.775875133
5/31/2002 3325.1 -4.869026495
6/28/2002 3163.2 -4.1287304
7/31/2002 3032.6 1.355272703
8/30/2002 3073.7 -4.730455152
9/30/2002 2928.3 2.277772086
10/31/2002 2995 1.008347245
11/29/2002 3025.2 -1.642866587
12/31/2002 2975.5 -1.34767266
1/31/2003 2935.4 -5.348504463
3

2/28/2003 2778.4 2.526634034
3/31/2003 2848.6 4.293337078
4/30/2003 2970.9 0.29957252
5/30/2003 2979.8 0.667830056
6/30/2003 2999.7 3.567023369
7/31/2003 3106.7 3.096533299
8/29/2003 3202.9 -0.833619532
9/30/2003 3176.2 3.34361816
10/31/2003 3282.4 -2.641359981
11/28/2003 3195.7 3.451512971
12/31/2003 3306 -0.677555959
1/30/2004 3283.6 2.707394323
2/27/2004 3372.5 1.301704967
3/31/2004 3416.4 -0.254654022
4/30/2004 3407.7 1.443789066
5/31/2004 3456.9 2.123289653
6/30/2004 3530.3 0.447554032
7/30/2004 3546.1 0.445559911
8/31/2004 3561.9 3.166849154
9/30/2004 3674.7 3.036982611
10/29/2004 3786.3 4.133322769
11/30/2004 3942.8 2.797504312
12/31/2004 4053.1 1.322444549
1/31/2005 4106.7 1.212652495
2/28/2005 4156.5 -1.344881511
3/31/2005 4100.6 -3.840901332
4/29/2005 3943.1 3.228424336
5/31/2005 4070.4 3.918533805
6/30/2005 4229.9 2.761294593
7/29/2005 4346.7 1.536798031
8/31/2005 4413.5 4.058003852
9/30/2005 4592.6 -3.917171101
10/31/2005 4412.7 3.872912276
11/30/2005 4583.6 2.731477441
12/30/2005 4708.8 3.639993204
1/31/2006 4880.2 -0.036883734
2/28/2006 4878.4 4.280091833
3/31/2006 5087.2 2.35493002
4/28/2006 5207 -4.507393893
5/31/2006 4972.3 1.240874444
6/30/2006 5034 -1.527612237
4

7/31/2006 4957.1 2.475237538
8/31/2006 5079.8 0.653569038
9/29/2006 5113 4.691961666
10/31/2006 5352.9 2.030674961
11/30/2006 5461.6 3.345173576
12/29/2006 5644.3 2.009106532
1/31/2007 5757.7 1.021241121
2/28/2007 5816.5 2.790337832
3/30/2007 5978.8 3.002274704
4/30/2007 6158.3 2.979718429
5/31/2007 6341.8 -0.491973888
6/29/2007 6310.6 -1.950686147
7/31/2007 6187.5 0.982626263
8/31/2007 6248.3 5.323047869
9/28/2007 6580.9 3.011746114
10/31/2007 6779.1 -2.736351433
11/30/2007 6593.6 -2.617689881
12/31/2007 6421 -11.27550226
1/31/2008 5697 -0.391434088
2/29/2008 5674.7 -4.669850389
3/31/2008 5409.7 4.571418008
4/30/2008 5657 2.066466325
5/30/2008 5773.9 -7.637818459
6/30/2008 5332.9 -5.256052054
7/31/2008 5052.6 3.224082651
8/29/2008 5215.5 -11.20122711
9/30/2008 4631.3 -14.0047071
10/31/2008 3982.7 -7.783664348
11/28/2008 3672.7 -0.364854194
12/31/2008 3659.3 -4.951766731
1/30/2009 3478.1 -5.209740951
2/27/2009 3296.9 7.140040644
3/31/2009 3532.3 6.013079297
4/30/2009 3744.7 1.83192245
5/29/2009 3813.3 3.527128734
6/30/2009 3947.8 7.642231116
7/31/2009 4249.5 5.520649488
8/31/2009 4484.1 5.691220089
9/30/2009 4739.3 -1.949655012
10/30/2009 4646.9 1.476252986
11/30/2009 4715.5 3.545753367
5

12/31/2009 4882.7 -5.85331886
1/29/2010 4596.9 1.17905545
2/26/2010 4651.1 5.203070241
3/31/2010 4893.1 -1.209866956
4/30/2010 4833.9 -7.867353483
5/31/2010 4453.6 -2.892042393
6/30/2010 4324.8 4.222160562
7/30/2010 4507.4 -1.521941696
8/31/2010 4438.8 4.462917906
9/30/2010 4636.9 2.081131791
10/29/2010 4733.4 -1.204208391
11/30/2010 4676.4 3.645966983
12/31/2010 4846.9 0.061895232
1/31/2011 4849.9 1.519618961
2/28/2011 4923.6 0.10155171
3/31/2011 4928.6 -0.600576229
4/29/2011 4899 -2.247397428
5/31/2011 4788.9 -2.695817411
6/30/2011 4659.8 -3.418601657
7/29/2011 4500.5 -2.904121764
8/31/2011 4369.8 -6.858437457
9/30/2011 4070.1 7.134959829
10/31/2011 4360.5 -4.031647747
11/30/2011 4184.7 -1.761177623
12/30/2011 4111 5.222573583
1/31/2012 4325.7 1.442541092
2/29/2012 4388.1 0.726966113
3/30/2012 4420 1.067873303
4/30/2012 4467.2 -7.465526504
5/31/2012 4133.7 0.043544524
6/29/2012 4135.5 3.721436344
7/31/2012 4289.4 1.156338882
8/31/2012 4339 1.551048629
9/28/2012 4406.3 2.929895831
10/31/2012 4535.4 -0.383648631
11/30/2012 4518 3.244798583
12/31/2012 4664.6 5.067958667
1/31/2013 4901 4.476637421
2/28/2013 5120.4 -2.743926256
3/29/2013 4979.9 3.789232716
4/30/2013 5168.6 -4.925898696
6

5/31/2013 4914 -2.820512821
6/28/2013 4775.4 5.450852285
7/31/2013 5035.7 1.779295828
8/30/2013 5125.3 1.802821298
9/30/2013 5217.7 3.882936926
10/31/2013 5420.3 -1.955611313
11/29/2013 5314.3 0.730105564
12/31/2013 5353.1 -2.764753134
1/31/2014 5205.1 4.040268198
2/28/2014 5415.4 -0.228976622
3/31/2014 5403 1.254858412
4/30/2014 5470.8 0.054836587
5/30/2014 5473.8 -1.677079908
6/30/2014 5382 4.479747306
7/31/2014 5623.1 0.026675677
8/29/2014 5624.6 -5.827969989
9/30/2014 5296.8 3.930675125
10/31/2014 5505 -3.758401453
11/28/2014 5298.1 1.708159529
12/31/2014 5388.6 3.024904428
1/30/2015 5551.6 6.248649038
2/27/2015 5898.5 -0.620496736
3/31/2015 5861.9 -1.504631604
4/30/2015 5773.7 0.020783899
5/29/2015 5774.9 -5.605291867
6/30/2015 5451.2 4.228426768
7/31/2015 5681.7 -8.089128254
8/31/2015 5222.1 -3.130924341
9/30/2015 5058.6 4.546712529
10/30/2015 5288.6 -1.331165148
11/30/2015 5218.2 2.422291212
12/31/2015 5344.6 -5.388616548
1/29/2016 5056.6 -2.149665783
2/29/2016 4947.9 4.120940197
3/31/2016 5151.8 3.187235529
4/29/2016 5316 2.47930775
5/31/2016 5447.8 -2.522119021
6/30/2016 5310.4 6.282012654
7/29/2016 5644 -2.030474841
8/31/2016 5529.4 -0.077766123
9/30/2016 5525.1 -2.220774285
7

10/31/2016 5402.4 1.851029172
11/30/2016 5502.4 3.938281477
12/30/2016 5719.1 -0.771100348
1/31/2017 5675 1.515418502
2/28/2017 5761 2.47873633
3/31/2017 5903.8 0.741895051
4/28/2017 5947.6 -3.132355908
5/31/2017 5761.3 0.046864423
6/30/2017 5764 0.171755725
7/31/2017 5773.9 0.041566359
8/31/2017 5776.3 -0.543600575
9/29/2017 5744.9 4.029661091
10/31/2017 5976.4 1.351984472
11/30/2017 6057.2 1.817671531
12/29/2017 6167.3 -0.33726266
1/31/2018 6146.5 -0.475067111
2/28/2018 6117.3 -4.060614977
3/29/2018 5868.9 3.453798838
4/30/2018 6071.6 0.854799394
5/31/2018 6123.5 2.714134074
6/30/2018 6289.7 1.216274226
7/31/2018 6366.2 0.967610191
8/31/2018 6427.8 -1.591524316
9/30/2018 6325.5 -6.516480911
10/31/2018 5913.3 -2.773409095
11/30/2018 5749.3 -0.69399753
12/31/2018 5709.4 3.991662872
1/31/2019 5937.3 5.312178937
2/28/2019 6252.7 0.143937819
3/31/2019 6261.7 -100
8

2.
-1.44241677741402 1.30170496664196 -11.2755022582152 -4.03164774681803 -3.13092434078245
0.0000
0.0200
0.0400
0.0600
0.0800
0.1000
0.1200
0.1400
0.1600
0.1800
0.2000
AOSD
AOSD
3.
AOSD represent standard deviation among Australian listed ordinary share in the market and
Aoret represent Return from an ordinary share of 500 largest listed companies in ASX. Both
variables define the performance of share and business in the market and further identified the
profitable business among the market.
 AOSD and AOret are depended on the return value of stock in the market to the investor.
 Both the variable is interlinked with the performance of the business entity or the market
which provides a detailed analysis of the financial performance of the business and stock
of the particularcompany in the market.
 AOSD indicates a major differencebetween the return and Aoret defines the return value
of the business entity in ASX Market. This majorly impacts the financial decision of the
individual or investor to invest their money in the stock or security market.
9

Question 2
1.
Correlation analysis
Particular AOret AOSD
AOret 1 -0.004707651
AOSD -0.004707651 1
2.
3.
There is a negative correction among Aoret and AOSD which indicates that Return and standard
deviation on the security of the companies impact on the performance of each other (Taylor el.
Al, 2015). There is a correlation of 0.004707651 between the variable of the company which
indicates that both the variable is influenced by each other. A minor change in a variable can
impact on the result of other variables in the market.
10
1 11 21 31 41 51 61 71 81 91 101111121131141151161171181191201211221
-120
-100
-80
-60
-40
-20
0
20
Chart Title
AOret AOSD

Question 3
1.
Autocorrelation of AOret
Number of Data Point 228
Lag Autocorrelation U-Crit Value L-Crit Value
1 0.000461932 0.132453236 -0.132453236
2 0.000460988 0.132453236 -0.132453236
3 0.000436156 0.132453236 -0.132453236
4 0.000417427 0.132453236 -0.132453236
5 0.000420356 0.132453236 -0.132453236
6 0.000432896 0.132453236 -0.132453236
Autocorrelation of AOSD
Number of Data Point 228
Lag Autocorrelation U-Crit Value L-Crit Value
1 -0.006875118 0.132453236 -0.132453236
2 -0.011554569 0.132453236 -0.132453236
3 -0.014756935 0.132453236 -0.132453236
4 -0.010853324 0.132453236 -0.132453236
5 -0.011341047 0.132453236 -0.132453236
6 -0.006040193 0.132453236 -0.132453236
2.
Autocorrelation is calculated above for both variables above which indicate statically relations
among the variables. Aoret indicates autocorrelation of 0.000461932 on Lag one which defied
the significant relation of the variable on Lag one for the performance of security among the
market (Real-statistics. 2019). AOSD Also had autocorrelation of -0.006875118 which is
negative so that it indicates that there is a negative impact on the performance of share AOSD
rises. It indicates that the standard deviationamong the share is low in the market and had a
minimum impact on the return of the security of the business entity listed in the market.
11

Question 4
1.
2.
Correlation is the tool that identified the relationship between two variables for the study. In this
study, two variables are Return and Standar4d deviation for the market which determines the
performance of security in the market. As the global financial crisis happens in 2008 which
impacts the security market of Australia which is identified in the comparison of correlation
among the return and standard deviation of share before and after GFC (Wallstreetmojo. 2019).
In this, it is identified that Return and standard deviation had more relationships among the
variable which means GFC impact on the market and overcome the probability of return in the
market as standard deviation is increased in reference of return from security in the market.
12
Correlation analysis (Before GFC)
Particula
r AOret AOSD
AOret 1 -
0.004707651
AOSD
-
0.004707651 1
Correlation analysis ( After GFC)
Particular AUDSD
Aoret( afte
r)
AUDSD 1
Aoret( afte
r) 0.053886096 1

Question 5
1.
Hypothesis
Null Hypothesis(H0) = the average AOret is the same before and after the GFC.
Alternative Hypothesis (H0) = the average AOret is not the same before and after the GFC.
z-Test: Two Sample for Means
Particular AORET ( Before) AORET (After)
Mean -0.060321366 -0.296306592
Known Variance AORET ( Before) AORET (After)
Observations 228 228
Hypothesized Mean Difference 0.05
Z 1.621381615
P(Z<=z) one-tail 0.052467911
z Critical one-tail 1.644853627
P(Z<=z) two-tail 0.104935821
z Critical two-tail 1.959963985
In this test Z value of one tail is 0.05246 which is lower than the z statistics which is 1.62 which
means the company can reject the null hypothesis and accepted alternative hypothesis for an
experiment which means there is the difference among the return of ordinary share after GFC
2008. The further company also evaluated the return on two tails test which also indicates that
the P value of two tails is lower than Z critical which means a null hypothesis is rejected and the
alternative is accepted by the company.
13

2.
The average AOSD is the same before and after the GFC.
Null Hypothesis (H0) = the average AOSD is the same before and after the GFC.
Alternative Hypothesis (H0) = the average AOSD is not the same before and after the GFC.
Z-Test: Two Sample for Means
Particular AOSD (Before) AOSD(After)
Mean 0.038021714 0.02333295
Known Variance 1 2
Observations 228 32
Hypothesized Mean Difference 0.05
Z -0.136535452
P(Z<=z) one-tail 0.445699
z Critical one-tail 1.644853627
P(Z<=z) two-tail 0.891398001
z Critical two-tail 1.959963985
Z test is applied from the data collected from the market on from which it is identified that z
value for the test is -0.136535452 and Z critical for one-tailed is 1.644853627 which is higher
than Z value for the one-tailed test which is 0.445699 (Excel functions. 2019). On the
calculation, it is identified that Null hypotheses are rejected for the company and an alternative
hypothesis is accepted which means there is a difference among Standard deviation of return
after and before GFC in the market.
14

3.
Average Calculation
Particular Average
AOSD 0.038021714
AUDSD 0.032544666
In this task Standarddeviation of the return on share is evaluated and the average is calculated
which is defined that there is some of the difference exist after the global financial crisis. So
through this analysis, it is identified that the Average of AUDSD is lower than AOSD which
means deviation among the share and security in the market for the companies listed in ASX.
15

Part B
Question 6
1. Volatility clustering is a phenomenon in finance in which large changes in financial
returns tend to be followed by large changes and small changes being followed by small
changes (Marra 2015). As defined by famous mathematician Benoit Mandelbrot,
volatility clustering is the observation that "large changes tend to be followed by large
changes...and small changes tend to be followed by small changes" when it comes to
markets (Moffatt 2019).
2. Volatility can either be measured by using the standard deviation or variance between
returns from that same security or market index. However, the most common way of
measuring past volatility level is standard deviation which is the frequency of “large”
day-to-day swings. Measuring future volatility levels is done using VIX Index (fear
gauge) – The index shows the market’s expectation of volatility. VIX is similar to
annualized expected standard deviation for the S&P 500 over the next 30 days. Chicago
Board Option Exchange (CBOE) avails the index on daily basis (Farasat2016).
3. The determination of whether AOSD have stronger volatility than AUDSD require
testing for hypothesis:
Null Hypothesis(H0) = the average volatility of AOSD is less than or equal to the average
volatility of AUDSD.
Alternative Hypothesis (H0) = the average volatility of AOSD is greater than the average
volatility of AUDSD.
This is a one-tailed t-test for two samples. The results of the test are shown below.
16

Statistics AOSD AUDSD
Mean 0.038021714 0.032545
Variance 0.000419568 0.000247
Observations 228 228
Hypothesized Mean Difference 0
Df 425
t Stat 3.204135974
P(T<=t) one-tail 0.000728371
t Critical one-tail 1.648446842
P(T<=t) two-tail 0.001456742
t Critical two-tail 1.965561459
In this test, t critical value for one-tail is 1.64845 which is lower than the t statistics
which is 3.20414 which means that we reject the null hypothesis and accepted alternative
hypothesis that the average volatility of AOSD is greater than the average volatility of
AUDSD. The decision is supported by the P(T<=t) one – tail equal to 0.000728 less than
critical level of significance alpha equal to 0.05.
4. AUDSD is a bond which always have less volatility on average than stocks (AOSD)
because more is known concerning their income flow. Little is known on the performance
of stocks, their volatility (Smith 2018). They have the potential to generate greater returns
than bonds, and over time have generally done so. But, the potential for greater gain call
for higher risk factor.
17

Question 7
The "leverage effect" refers to the well-established relationship between stock returns and both
implied and realized volatility: volatility increases when the stock price falls. A standard
explanation ties the phenomenon to the effect a change in market valuation of a firm's equity has
on the degree of leverage in its capital structure, with an increase in leverage producing an
increase in stock volatility. The exhibit 1 shows the leverage effect.
18

Question 8
1. Table 1 shows the optimal Parameters and MSE
Table 1: Optimal Parameters and MSE
Optimal parameters MSE in hold-out period
SES 0.6 0.000506462
Holt’s Alpha = 0.65, Beta = 0 0.0005064615
Average 0.00051596
Regression 0.000164
Both models have the same MSE
2. Average does not reduce the MSE due to errors associated with averaging of values.
Question 9
1. The response variable is the AOSD representing volatility of all ordinary index at the end
of each month. The explanatory variables will include (1) TV – Trading volume, number
of shares traded in million shares. The is chosen because it shows how confident the
buyers are with the index. If low volumes of a share are traded then it means it has a high
volatility while if more is sold then it means it has low risk. Therefore, trading volume is
essential in degerming volatility. (2) AUDSD – volatility of US$ per Australian Dollar,
share markets tend to move together therefore a change in share A has an effect on share
B. (3) IR – interest rate, RBA cash rate in percentages, the rate reflects what the buyers
and sellers of a share expect to get when they invest hence it affect volatility. (4) PE –
Price -Earnings Ratio, the PE has a link with return on shares since a higher PE implies
that the asset is highly volatile while a low PE implies the asset is less volatile. Multiple
linear regression will be used to estimate the equation for volatility of all ordinary index.
The assumptions of the model are Independence of the explanatory variables, normality
of the residuals, constant variance (homoskedasticity, linearity between response and the
independent variables. These assumptions will be tested on the residuals.
2. The model takes the form:
Y t =β0 + β1 X1 + β2 X2+ β3 X3 + β4 X 4 +εt
Where:
Y t- AOSD (response variable)
β0 – intercept term, βi- slope estimates (i = 1, 2, 3, 4)
X1 – TV
X2 – AUDSD
X3 – IR
X 4 – PE
19

Table 2 shows the estimation results
Table 2: Regression Results
Estimation Period Full Sample
Coefficient t stat Coefficient t stat
X1 3.83053E-07 1.956049 1.09E-07 1.857784
X2 0.724587528 11.35073 0.738417 11.98179
X3 0.000663109 0.97067 0.000726 1.313448
X4 -0.002188206 -4.03197 -0.00258 -6.34815
Constant 0.042456996 3.103533 0.052694546 5.85534916
R2 0.612473095 0.608412
AdjR2 0.604564383 0.601388
Model F stat 77.44283 86.61915
DW 1.831914 1.065637
Question 10
1. Excel file
2. The variable which has become less important in the recent years is interest rate since its
t-statistic in absolute terms is less than critical value of 1.65 (two- tailed t-value at 5%
significance level and 227 degrees of freedom).
3. The forecast volatility for June 2019 is 0.03889 since the other factors defined in (8)
cannot be obtained for June. The regression is only dependent on time.
20

References –
 Chowdhury, T. S., Habibullah, M., & Nahar, N. (2018). Risk and return analysis of closed-
end mutual funds in Bangladesh. Journal of Accounting, Business and Finance
Research, 3(2), 83-92.
 Excelfunctions. (2019). Excel Z.TEST Function. [Online]. Excelfunctions. Available at:
https://www.excelfunctions.net/excel-z-test-function.html [Accessed on 02.05.2019]
 Minitab. (2019). Autocorrelation function (ACF). [Online]. Minitab. Available at:
https://support.minitab.com/en-us/minitab/18/help-and-how-to/modeling-statistics/time-
series/how-to/autocorrelation/interpret-the-results/autocorrelation-function-acf/ [Accessed on
02.05.2019]
 Real-statistics. (2019). Autocorrelation Function. [Online]. Real-statistics. Available at:
http://www.real-statistics.com/time-series-analysis/stochastic-processes/autocorrelation-
function/ [Accessed on 02.05.2019]
 Taylor, S. J., Bogdan, R., & DeVault, M. (2015). Introduction to qualitative research
methods: A guidebook and resource. John Wiley & Sons.
 Wallstreetmojo. (2019). CORREL Excel Function (Correlation). Wallstreetmojo. Available
at: https://www.wallstreetmojo.com/correl-excel-function/ [Accessed on 02.05.2019]
 Moffatt, M. (2019). How an Investor Should Understand Volatility Clustering. [online]
Available at: https://www.thoughtco.com/volatility-clustering-in-economics-1147328
[Accessed 6 Jun. 2019].
 Farasat, P. (2016). Volatility Clustering FAQ - Loring Ward. [online] Loring Ward.
Available at: https://loringward.com/blog/volatility-clustering-faq/ [Accessed 6 Jun. 2019].
 Smith, T. (2019). Why stocks outperform bonds. [online] Available at:
https://www.investopedia.com/articles/basics/08/stocks-bonds-performance.asp [Accessed 6
Jun. 2019].
 Figlewski, S. and Wang, X. (2000). Is the 'Leverage Effect' a Leverage Effect? [online]
Available at:
https://pdfs.semanticscholar.org/e131/b6bcdef37027d09cb2ddb8ffae35f10fe3f5.pdf
[Accessed 6 Jun. 2019].

21

22