Financial Modelling - Assignment

Running head: FINANCIAL MODELLING
Financial Modelling
Name of the Student:
Name of the University:
Author’s Note:

1FINANCIAL MODELLING
Abstract
The aim of the assignment is to well cover the financial modelling process that would be well
covered from the investment perspective. The companies that have been selected for the purpose
of analysis is large cap Australian Companies which are actively traded in the Australian Market
and is having a sound liquidity. The trend period that has been specifically covered for the
purpose of analysis is from January 2010 to June 2016. The Analysis of the returns would be
well done with the help of the Capital Asset Pricing Model whereby the expected return for the
stocks will be well calculated with the help of the model developed. The descriptive statistics or
analysis on other hand would be well covered with the help of the STAT Software in which
output would be well used for the purpose of assessing the overall model and the output that has
been generated for the model analyzed.

2FINANCIAL MODELLING
Table of Contents
Introduction......................................................................................................................................3
Discussion and Analysis..................................................................................................................3
Capital Asset Pricing Model........................................................................................................3
Regression Analysis.....................................................................................................................5
Descriptive Statistics...................................................................................................................7
Conclusion and Recommendations..................................................................................................7
References........................................................................................................................................8
Appendix..........................................................................................................................................9
1) STATA Output........................................................................................................................9

3FINANCIAL MODELLING
Introduction
The financial modelling has been well carried on with the help of the portfolio
perspective in which key Australian Stocks were selected in which the expected returns for the
stocks has been well analyzed based on the Capital Asset pricing Model. The returns generated
by the model was assessed on different descriptive statistical tools that were well used for the
purpose of analysis which includes assessing the beta value, mean, standard deviation, Alpha, R-
squared Test, which were well analyzed with the help of excel as a key software. On the other
hand, the STATA Software was well used for the purpose of analyzing the overall model with
the help of Unit Root Test, F Test and Diagnostic Test.
Discussion and Analysis
Capital Asset Pricing Model
The CAPM Model has been well used for the purpose of analyzing the expected returns
that have been generated by each of the stock analyzed (Gustafsson and Gustavsson 2019). The
key formula that has been applied for the purpose of calculating the returns generated by the
stock has been as follows:
Expected Return E(r): Risk Free Rate (Rf) + Beta*(Market Return-Risk Free Rate).
The application of the model would be well applied with the help of the above given data
whereby the risk free data has been taken on a monthly basis. The beta of the stocks has been
well calculated by regressing the returns generated for the stocks with the help of index data. The
beta for each of the stocks has been calculated in particular in order to assess the sensitivity of

4FINANCIAL MODELLING
the stock. The objective of the study is to assess whether the stocks or the combined portfolio
would be creating value in terms of excess returns. The application of CAPM is done widely as it
measures the expected rate of return of a security and well relates with expected risk; however
there has been particularly little or no evidence in relation to the same. The CAPM Model though
aims at using it for the purpose and measurement of CAPM, which is also known as the expected
return on a security it also well correlated with the expected risk from a particular stock or
security. From a empirical evidence view point the same says that it is poor enough for invalidate
the ways it is used for application purpose. The key set of assumptions which are well used for
the purpose of analysis of the CAPM are that investors are generally considered risk averse and
investors will only be selecting a portfolio that is trading off between the risk and return for one
investment period or point of time. Thus, rationale investors will be well selecting a portfolio
that would be maximizing the level of expected return and in turn would be minimizing the
variance level for portfolio return. The portfolio for the considered five stocks has been created
on an equal weightage basis and the same will be well used for analysis and discussion purpose.

5FINANCIAL MODELLING
Regression Analysis
The regression analysis has been well performed by using stocks and data for the period
of Jan 2010 to June 2016 whereby relevant changes in the data has been well considered for
analysis purpose. The descriptive statistics has been well calculated for the purpose of
calculating the R-squared Test, F-Test, Alpha and Beta Value for the stock (Squartini et al.,
2017).
R-Squared: The R-squared statistics has been well carried out for the list of five selected
companies in which we have well covered the various parts of data. The R-squared is a key
statistical measure which is used for representing the proportion of variance for a dependent
variable that is well explained by an independent variable or the set of variables in a regression
model. The R-squared calculated for the Westpac Company has been the highest which has been
around 0.68 times (Andrei, Cujean and Wilson 2018). The key reason for having such a high R-
squared goes to well show that the dependent variable in the given data set are well explained.
However, the portfolio constructed had the highest R-squared which gave us a number of around
0.83, which is god when considered in R-squared terms.
F-Test: The F-test is used for assessing and finding in order to well identify the model that
would be well fitting the population from the data which are taken on a sample basis. The value
of F-test if the same is well less than 0.05 of significance level then it would be well showing
that the overall model is valid and does not have any error or bias. The F-test run for the portfolio
has been 0.00 which got to well show that the overall developed model has been valid. The F-test
for all the stocks considered or analyzed has also been low than 0.05 of significance level which
got to well show that the overall developed model is feasible enough.

6FINANCIAL MODELLING
Alpha: Alpha in the Vertical Intercept goes to well show how much the fund performed than the
predicted level of CAPM. The quality of fit is better given by the statistical number generated by
R-squared discussed above. The Alpha value for the portfolio has been positive to around
0.001191 which goes to well show that the portfolio has outperformed the expected level of
CAPM. Out of the five stocks it is important to note that three of the stocks had positive Alpha
and two had negative Alpha (Kuehn, Simutin and Wang 2017).
Beta: The beta value for the stocks shows the sensitivity that has been well calculated by
regressing the returns that have been generated by the stocks or the portfolio over the market
index. The constructed equal weighted portfolio had a beta of around 1.15 times, this got to well
show that the constructed portfolio is highly sensitive in respect to the market movement
(Hollstein, Prokopczuk and Wese Simen 2020). The beta value for the stocks were well analyzed
based on the fact that if the market moves by around the portfolio is well expected to change by
around 1.15 times in the same direction.
Particula
rs RIO BHP WESTPAC CSL MCQ
Portfoli
o
R
Squared
0.43341
5
0.44418
4
0.6867724
63
0.15846
4
0.34534
3
0.82783
8
F Test 0.0000 0.0000 0.0000 0.0003 0.00000 0.00000
Alpha
-
0.00601
-
0.00964
0.0011458
39
0.01659
4
0.00386
3
0.00119
1
Beta 1.29993
1.28574
7
1.4880284
89
0.55635
7
1.13408
8 1.15283
Empirically it has been found that all the above data description tools are well sued for
the purpose of analyzing the movement in the portfolio or stocks in respect to the market index
which would be well helping the company to better analyze the overall performance it is
currently trading with (Barberis, 2015).

7FINANCIAL MODELLING

8FINANCIAL MODELLING
Descriptive Statistics
The descriptive statistics has been well analyzed and run for all the stocks that have been
well considered for the purpose of analysis and interpretation purpose. The descriptive statistics
has been well carried out for the set of five companies, index and as well for the portfolio so that
we are able to get the Mean, SD Error, Median, Sample Variance, Kurtosis, Skewness and Range
that has been collected for each of the data. The collected data has been better analyzed with the
help of descriptive statistics designed for each of the company.
RIO BHP WESTPAC CSL MCQ
Mean -0.0027 Mean -0.0064 Mean 0.0049 Mean 0.0180 Mean 0.0067
SD Error 0.0081 SD Error 0.0079 SD Error 0.0073 SD Error 0.0057 SD Error 0.0079
Median -0.0071 Median -0.0095 Median 0.0162 Median 0.0254 Median 0.0045
Mode 0.0000 Mode 0.0000 Mode 0.0000 Mode 0.0000 Mode 0.0000
SD 0.0708 SD 0.0692 SD 0.0644 SD 0.0501 SD 0.0692
Sample
Var. 0.0050
Sample
Var. 0.0048
Sample
Var. 0.0041
Sample
Var. 0.0025
Sample
Var. 0.0048
Kurtosis 0.3852 Kurtosis 1.4302 Kurtosis -0.3247 Kurtosis -0.1701 Kurtosis -0.1043
Skewness 0.2829 Skewness 0.1272 Skewness -0.4504 Skewness -0.1355 Skewness -0.0427
Range 0.3554 Range 0.4407 Range 0.2886 Range 0.2553 Range 0.3224
Minimum -0.1478 Minimum -0.2142 Minimum -0.1586 Minimum -0.1109 Minimum -0.1433
Maximum 0.2075 Maximum 0.2266 Maximum 0.1300 Maximum 0.1444 Maximum 0.1791
Sum -0.2108 Sum -0.4934 Sum 0.3764 Sum 1.3855 Sum 0.5171
Count
77.000
0 Count
77.000
0 Count
77.000
0 Count
77.000
0 Count
77.000
0
Return on Portfolio
Mean 0.0041
SD Error 0.0052
Median 0.0059
SD 0.0454
Sample Var. 0.0021
Kurtosis -0.4129
Skewness -0.0734
Range 0.2173
Minimum -0.0987
Maximum 0.1186
Sum 0.315

9FINANCIAL MODELLING
Count 77
Conclusion and Recommendations
The analysis for the portfolio has been well done with the help of descriptive analysis and
as well as regression analysis in which we have analysed various stocks from a portfolio
perspective. The key aim of the model was to find out the returns of a portfolio or stocks and
well match up with the CAPM models. However, it was well found that the expected returns
from the stocks or portfolio differ from that of actual returns generated, this in turn lead to
differences in the form of Alpha. However, it is well recommended that there should be various
factos that should be considered for analysis purpose.

10FINANCIAL MODELLING
References
Aggarwal, R., 2017. The Fama-French three factor model and the capital asset pricing model:
Evidence from the Indian stock market. Indian Journal of Research in Capital Markets, 4(2),
pp.36-47.
Andrei, D., Cujean, J. and Wilson, M.I., 2018. The lost capital asset pricing model.
Barberis, N. et al. 2015 "X-CAPM: An extrapolative capital asset pricing model", Journal of
Financial Economics, 115(1), pp. 1-24. doi: 10.1016/j.jfineco.2014.08.007.
Fernandez, P., 2017. The Capital Asset Pricing Model. In Economic Ideas You Should
Forget (pp. 47-49). Springer, Cham.
Gustafsson, F. and Gustavsson, R., 2019. Testing the Performance of the Capital Asset Pricing
Model and the Fama-French Three-Factor Model-A study on the Swedish Stock Market between
2014-2019.
Hollstein, F., Prokopczuk, M. and Wese Simen, C., 2020. The conditional Capital Asset Pricing
Model revisited: Evidence from high-frequency betas. Management Science.
Kuehn, L.A., Simutin, M. and Wang, J.J., 2017. A labor capital asset pricing model. The Journal
of Finance, 72(5), pp.2131-2178.
Squartini, T., Almog, A., Caldarelli, G., Van Lelyveld, I., Garlaschelli, D. and Cimini, G., 2017.
Enhanced capital-asset pricing model for the reconstruction of bipartite financial
networks. Physical Review E, 96(3), p.032315.

11FINANCIAL MODELLING
Zerbib, O.D., 2019. A Sustainable Capital Asset Pricing Model (S-CAPM): Evidence from green
investing and sin stock exclusion. Available at SSRN 3455090.
Appendix
1) STATA Output