FINANCIAL MODELLING

Advanced Financial Modeling

Section A

A1. Theoretical assumptions and implications of the Capital Assets Pricing Model (CAPM)

According to Lintner (1965) and Sharpe (1964), the following assumptions are made in the CAPM model

  1. Perfect information

All investors can access the same information

Analysis of information is done in the same manner

  1. Markets are smooth without friction

There are no taxes

No transaction costs/No commissions

  1. Security markets compete perfectly

Many investors who compete perfectly (price takers)

  1. Myopic investors

All investors have one and same holding period

  1. All investors rationally optimize mean and variance

Everyone uses Markowitz portfolio selection criterion

  1. Investments are limited to publicly traded assets with unlimited lending at the risk-free rate

  2. Investors can borrow and lend at the one risk free rate

  3. Investors agree on the prospects of various investments such as expected values, variance, correlation and standard deviations implying homogeneity of beliefs about investments

  4. Any investor can short any asset and hold any fraction of the asset

  5. All investors have the same planning horizon

  6. Common pure rate of interest implying that all investors are able to borrow or lend funds on equal terms

Implications

  1. Investors will combine a risk free asset with a market portfolio of risky assets and make investments in risky assets in accordance with their market value.

  2. Investors should expect returns from their investment according to the risk.

  3. In the event that the investors cannot diversify on a risk, they are compensated.

  4. All efficient combinations will be perfectly correlated

A2. How valid the CAPM Assumptions are and extent to which breach of assumptions invalidate the CAPM model

The CAPM assumptions are mainly valid based on the investor behavior and for a single risk factor. When firms are not sorted on metrics such as price/book or price/earnings, the investors’ subjective reactions may not be exposed. Under these circumstances, they won’t over-forecast past performance that would otherwise lead to increased stock prices for high price/earnings firms and too low for low P/E firms. Under the circumstances, the assumptions of CAPM would be breached. The assumption of a single risk factor then limits the validity of the CAPM model (Schmidt, 2008).

A3. Empirical test of CAPM

This part of assignment refers to the appendix 1 A. The model output for shop all years shows that
FINANCIAL MODELLING i.e the model value p>0.05 implies statistical insignificance of
FINANCIAL MODELLING 1 value. The same holds for technology stock. As for the 10 year period regression, third 10 year regression shows significant
FINANCIAL MODELLING 2 only for the shop. The rest show insignificant
FINANCIAL MODELLING 3

Looking at the data profile, the
FINANCIAL MODELLING 4 is significant if the mean stock difference is closer to the market free rate. The model shows on average that the market condition accounts for over 75% of the stock price. From the model,
FINANCIAL MODELLING 5 implies that at the point of equilibrium (when market value=stock value), then the CAPM’s equilibrium state, through the assumptions shows that all the investors will see their respective alternatives (on the indifference curve) in the same manner as shown below

FINANCIAL MODELLING 6

Figure 1: CAPM under market equilibrium (Sharpe).

This is in agreement with the CAPM model under the assumptions in equilibrium state.

A4. Fama French three factor model:FINANCIAL MODELLING 7)

Background and key features

The Fama French model is used to explain differences in the returns of diversified stocks. It compares a portfolio to 3 distinct risks found in the equity market to assist in decomposing returns. According to this model, portfolio’s beta explains for only about 70% of actual returns and the remaining 30% is accounted for by factors not related to beta. Due to this, the model is known to account for up to 95% of returns for a cross-section of equity portfolios of different sizes and styles (Lohrmann, 2015). The key features of the model include:

  1. The stock risk/return is useful in explaining the return of a portfolio

  2. The return of any stock portfolio can be explained almost entirely by only including an additional two factors which include the Market Cap/(SMB) and book/market (HML) ratio (value). A portfolio with a large size (SM) and a high book/market ratio will have a higher expected return than that of a low book/market ratio.

A5. Empirical test for Fama French model

FINANCIAL MODELLING 8,
FINANCIAL MODELLING 9 ,FINANCIAL MODELLING 10 ,FINANCIAL MODELLING 11

For both the Shop and technology industries, the model constant value is not significant (p>0.0500), hence the null hypothesis
FINANCIAL MODELLING 12 is true. i.e
FINANCIAL MODELLING 13. However, the model results show that
FINANCIAL MODELLING 14FINANCIAL MODELLING 15 and
FINANCIAL MODELLING 16 values are statistically significant hence the null hypotheses for the coefficients is not true.

For a detailed model result see the appendix 1B for Fama French model for both the shop and technology industries under the coefficients table 1B.

For comparison purposes,
FINANCIAL MODELLING 17 intercept is 0 for both cases CAPM and Fama French. However, comparing the model summary for the two models in the cases of shop and technology, it can be seen that there is a difference in what is accounted for by the particular model. Generally shops are smaller stock values and the tech is larger stock values thus the difference between the stock and risk free rate is higher for technology than for shop industry.

The CAPM model accounts approximately 60% for variation in shop stock value and 57% for technology.

On the other hand, Fama French model accounts for approximately 78% for shop and approximately 82% for technology.

From the model results, it can be seen for both small and large stocks that the Fama French model accounts more for a given variation from the free rate as compared to the CAPM model. The CAPM is seen to predict smaller stocks more accurately compared to bigger ones. The Fama French model, predicts bigger stocks more accurately than it would for smaller ones. However, it should be noted that in either cases, the Fama French model is more accurate compared to the CAPM (i.e either small or large stocks).

From this it can be seen that increasing size Market Cap increases the part accounted for the model. It confirms further that depending size and style, Fama French three factor model can account for a very large portion (up to 95%) of the expected returns (Lohrmann, 2015).

Section B: Factors affecting wages in the US

B1. Descriptive statistics

Table 9: Descriptive Statistics for wages and factors affecting the wages

Std. Deviation

EDUCATION

EXPERIENCE

0

0

0

0

Valid N (listwise)

B2. Regression model 1: wages and education

Table 10: Coefficientsa

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

EDUCATION

Table: Model Summary and regression

Adjusted R Square

Std. Error of the Estimate

FINANCIAL MODELLING 18

Figure 2: Graph showing relationship between wage and education

From the tables and the graph, R squared is 0.145 implying that education accounts for 14.5% of the wages earned. This implies that wages increase by 14.5% for every additional year of education.

B3. Collective impact of education and experience

Table: Coefficients for education and experience

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

EDUCATION

EXPERIENCE

Table: Model Summary for combined impact of education and experience on wages

Adjusted R Square

Std. Error of the Estimate

From the tables, it can be concluded that a combined effect of education and experience accounts for 20% (R squared=0.200) of wages. It means wages will increase by 20% for a combined increase of education and experience by one year. This is 6% more than the first model meaning a combination of education and experience lead to increased wages more than education along.

B4. Regression model 3 for education, experience and gender on wages

Table: Coefficients of education, experience and gender

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

EDUCATION

EXPERIENCE

Table: Model Summary- predictions

Adjusted R Square

Std. Error of the Estimate

From the tables, gender affects the wages. The numerical values assigned to gender are dummy. Gender is a dummy variable. A dummy variable is a numerical variable used in regression analysis to represent subgroups of the study sample. The dummy variable is used to distinguish different treatment groups. It can be described as a categorical variable or simply put, it’s a qualitative variable. Since the numerical assignment is with due consideration and/or a measure of the wage weight relative to gender, the index is a measure of the gender impact level. To this effect, it is expected that value 1(female) receives less wages. Therefore, in this case, since the wage coefficient shows a negative relationship, it is expected that wage value 1 gets less wages than age value 0 (male). In this regard, it is considered that male gender would not have any impact on wages in terms of gender but being a female reduces value to gender in terms of wages i.e all factors notwithstanding, a female would get less wages than male.

B5. Correlation matrix

Table: Correlation matrix for factors affecting wages

EDUCATION

EXPERIENCE

Pearson Correlation

Sig. (2-tailed)

EDUCATION

Pearson Correlation

Sig. (2-tailed)

EXPERIENCE

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

Correlations

Pearson Correlation

Sig. (2-tailed)

EDUCATION

Pearson Correlation

Sig. (2-tailed)

EXPERIENCE

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

From the correlation matrix table above, it can be concluded certainly that the wage of a person is significantly determined by both the factors under study which include education, experience, gender, age, marital status and union membership. Except for gender which is negatively correlated with wages ( where females get less by virtue of gender value), all other factors have a positive correlation with wages, meaning that a unit increase in a given factor is accompanied by an increase in wages by a value equal to the correlation factor of the value.

From the table, it can also be seen how the factors interact with each other. Education has a significantly negative correlation with age and experience. That is, higher education is seen amongst less experienced and younger persons. Experience positively correlates with age, marital status (married are more experienced), and union. Gender has a positive correlation with only marital status (where female are more likely to be of married status than male). Age correlates with all other factors except for gender. In short, it is good to note that although, at individual level, the factors are determined to be having effect on the wages, they are also interrelated differently where one factor can influenced by others and/or not the others.

B6. Regression model for all the factors (age, education, gender, married, experience and union)

Table: Coefficients of factors affecting wages

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

EDUCATION

EXPERIENCE

Table: Model Summary for thepart accounted for by the model

Adjusted R Square

Std. Error of the Estimate

This model only accounts for approximately 27% of the total wages. First of all, it is good enough to say that the model cannot be applied to predict the wages of a particular person effectively. It means that some of the high impact factors to determine the wages have been left out. One more thing that makes this model less effective is the fact that in the absence of all other factors, the wage of a person is in negatives and the woman has even more negative wages. This may not be empirically true since the minimum wage of a person is 0 (i.e for a man who is not working) (Doing business in 2011, 2010).

B7 Important factors and problems that need to be considered when designing a financial model

When developing a financial model, the key factors to consider include validity of the data being used and possible sources of error. A consideration of the sources implies that all factors that could affect a dependent variable should all be involved. One other major factor to consider is the source of error. The errors/factors in this respect include the following:

  1. Formula errors which are the easiest errors to make

  2. The assumption/input error. This can be identified by making sure the assumptions are all clearly documented within the model.

  3. Logic errors Which are hard to identify. It particularly comes by when the formulas and assumptions are correct, yet the model behavior shows inaccuracies for a given test situation.

A model is developed based information systems that require key aspects. Some of the key requirements include: knowing what information exists and what it is about, extracting a portion of information for a particular purpose, managing data, including history, for life. Meeting all the requirements of an information system can be difficult and expensive.

One other major problem that faces development of financial models is the reality of the computer-based information. A number of problems occur as a result of the way information systems hold data. Arbitrary or inappropriate restrictions can be placed due to data structures and constraints. False data can be introduced to overcome the imposed restrictions and uncontrolled redundancy arises from the same data occurring and being updated in multiple systems. These and more computer related problems become quite a challenge in financial modeling (Ho and Yi, 2004).

In doing so, therefore, the development of a financial model heavily relies on assumptions. A model cannot be developed without a model. The formula errors and computer related problems can be easily identified. The ultimate solution to the model problems would be to reduce the number of assumptions as much as possible. This can be explained by Occam’s razor which is a logical principle attributed to William of Occam The principle states that “one should not make more assumptions than the minimum needed”. It is a very important guide that helps us to shave away the variables that are not necessary in explaining the phenomenon (Martineau, 2000). It makes the models simpler with the most befitting solution

Appendix 1A: Model Output for CAPM

Regression-output for Shop-all years

Variables Entered/Removeda

Variables Entered

Variables Removed

a. Dependent Variable: ShpRF

b. All requested variables entered.

Model Summary

Adjusted R Square

Std. Error of the Estimate

a. Predictors: (Constant), Mkt-RF

Sum of Squares

Mean Square

Regression

8226.228

8226.228

Residual

5394.427

13620.655

a. Dependent Variable: ShpRF

b. Predictors: (Constant), Mkt-RF

Coefficientsa

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

a. Dependent Variable: ShpRF

Regression-output for technology for all the years

Variables Entered/Removeda

Variables Entered

Variables Removed

a. Dependent Variable: TechRF

b. All requested variables entered.

Model Summary

Adjusted R Square

Std. Error of the Estimate

a. Predictors: (Constant), Mkt-RF

Sum of Squares

Mean Square

Regression

16253.182

16253.182

Residual

11943.635

28196.817

a. Dependent Variable: TechRF

b. Predictors: (Constant), Mkt-RF

Coefficientsa

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

a. Dependent Variable: TechRF

Regression output for the 10 year periods

Regression-1st 10 year shop

Variables Entered/Removeda

Variables Entered

Variables Removed

a. Dependent Variable: ShpRF

b. All requested variables entered.

Model Summary

Adjusted R Square

Std. Error of the Estimate

a. Predictors: (Constant), Mkt-RF

Sum of Squares

Mean Square

Regression

2765.458

2765.458

Residual

1019.567

3785.025

a. Dependent Variable: ShpRF

b. Predictors: (Constant), Mkt-RF

Coefficientsa

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

a. Dependent Variable: ShpRF

Regression 1st 10-year technology

Variables Entered/Removeda

Variables Entered

Variables Removed

a. Dependent Variable: TechRF

b. All requested variables entered.

Model Summary

Adjusted R Square

Std. Error of the Estimate

a. Predictors: (Constant), Mkt-RF

Sum of Squares

Mean Square

Regression

4630.531

4630.531

Residual

1899.871

6530.402

a. Dependent Variable: TechRF

b. Predictors: (Constant), Mkt-RF

Coefficientsa

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

a. Dependent Variable: TechRF

Regression-2nd 10 year shop

Variables Entered/Removeda

Variables Entered

Variables Removed

a. Dependent Variable: ShpRF

b. All requested variables entered.

Model Summary

Adjusted R Square

Std. Error of the Estimate

a. Predictors: (Constant), Mkt-RF

Sum of Squares

Mean Square

Regression

1666.046

1666.046

Residual

1679.420

3345.465

a. Dependent Variable: ShpRF

b. Predictors: (Constant), Mkt-RF

Coefficientsa

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

a. Dependent Variable: ShpRF

Regression-2nd 10 year tech

Variables Entered/Removeda

Variables Entered

Variables Removed

a. Dependent Variable: TechRF

b. All requested variables entered.

Model Summary

Adjusted R Square

Std. Error of the Estimate

a. Predictors: (Constant), Mkt-RF

Sum of Squares

Mean Square

Regression

3143.522

3143.522

Residual

3645.956

6789.478

a. Dependent Variable: TechRF

b. Predictors: (Constant), Mkt-RF

Coefficientsa

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

a. Dependent Variable: TechRF

Regression-3rd 10-year shop

Variables Entered/Removeda

Variables Entered

Variables Removed

a. Dependent Variable: ShpRF

b. All requested variables entered.

Model Summary

Adjusted R Square

Std. Error of the Estimate

a. Predictors: (Constant), Mkt-RF

Sum of Squares

Mean Square

Regression

4051.805

4051.805

Residual

2432.948

6484.753

a. Dependent Variable: ShpRF

b. Predictors: (Constant), Mkt-RF

Coefficientsa

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

a. Dependent Variable: ShpRF

Regression-3rd 10 year tech

Variables Entered/Removeda

Variables Entered

Variables Removed

a. Dependent Variable: TechRF

b. All requested variables entered.

Model Summary

Adjusted R Square

Std. Error of the Estimate

a. Predictors: (Constant), Mkt-RF

Sum of Squares

Mean Square

Regression

8908.616

8908.616

Residual

5790.849

14699.466

a. Dependent Variable: TechRF

b. Predictors: (Constant), Mkt-RF

Coefficientsa

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

a. Dependent Variable: TechRF

Appendix 1B. Fama French Model

Regression-Fama French model shop

Variables Entered/Removeda

Variables Entered

Variables Removed

HML, SMB, Mkt-RFb

a. Dependent Variable: ShpRF

b. All requested variables entered.

Model Summary

Adjusted R Square

Std. Error of the Estimate

a. Predictors: (Constant), HML, SMB, Mkt-RF

Sum of Squares

Mean Square

Regression

10629.864

3543.288

Residual

2990.791

13620.655

a. Dependent Variable: ShpRF

b. Predictors: (Constant), HML, SMB, Mkt-RF

Table 1B4. Coefficientsa

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

a. Dependent Variable: ShpRF

Regression fama french tech

Variables Entered/Removeda

Variables Entered

Variables Removed

HML, SMB, Mkt-RFb

a. Dependent Variable: TechRF

b. All requested variables entered.

Model Summary

Adjusted R Square

Std. Error of the Estimate

a. Predictors: (Constant), HML, SMB, Mkt-RF

Sum of Squares

Mean Square

Regression

23356.533

7785.511

Residual

4840.284

28196.817

a. Dependent Variable: TechRF

b. Predictors: (Constant), HML, SMB, Mkt-RF

Table 1B 8. Coefficientsa

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

a. Dependent Variable: TechRF

Appendix 2: Factors affecting wage for American workers

Descriptives

Descriptive Statistics

Std. Deviation

EDUCATION

EXPERIENCE

0

0

0

0

Valid N (listwise)

Regression-wages vs education

Variables Entered/Removeda

Variables Entered

Variables Removed

EDUCATIONb

a. Dependent Variable: WAGE

b. All requested variables entered.

Model Summary

Adjusted R Square

Std. Error of the Estimate

a. Predictors: (Constant), EDUCATION

Sum of Squares

Mean Square

Regression

4602.214

4602.214

Residual

27141.823

31744.037

a. Dependent Variable: WAGE

b. Predictors: (Constant), EDUCATION

Coefficientsa

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

EDUCATION

a. Dependent Variable: WAGE

GGraph-Wages vs Education

FINANCIAL MODELLING 19

Regression-education and experience

Variables Entered/Removeda

Variables Entered

Variables Removed

EXPERIENCE, EDUCATIONb

a. Dependent Variable: WAGE

b. All requested variables entered.

Model Summary

Adjusted R Square

Std. Error of the Estimate

a. Predictors: (Constant), EXPERIENCE, EDUCATION

Sum of Squares

Mean Square

Regression

6346.984

3173.492

Residual

25397.053

31744.037

a. Dependent Variable: WAGE

b. Predictors: (Constant), EXPERIENCE, EDUCATION

Coefficientsa

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

EDUCATION

EXPERIENCE

a. Dependent Variable: WAGE

Regression-education, gender and experience

Variables Entered/Removeda

Variables Entered

Variables Removed

GENDER, EDUCATION, EXPERIENCEb

a. Dependent Variable: WAGE

b. All requested variables entered.

Model Summary

Adjusted R Square

Std. Error of the Estimate

a. Predictors: (Constant), GENDER, EDUCATION, EXPERIENCE

Sum of Squares

Mean Square

Regression

8008.738

2669.579

Residual

23735.300

31744.037

a. Dependent Variable: WAGE

b. Predictors: (Constant), GENDER, EDUCATION, EXPERIENCE

Coefficientsa

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

EDUCATION

EXPERIENCE

a. Dependent Variable: WAGE

Correlations

Correlations

EDUCATION

EXPERIENCE

Pearson Correlation

Sig. (2-tailed)

EDUCATION

Pearson Correlation

Sig. (2-tailed)

EXPERIENCE

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

Correlations

Pearson Correlation

Sig. (2-tailed)

EDUCATION

Pearson Correlation

Sig. (2-tailed)

EXPERIENCE

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

Pearson Correlation

Sig. (2-tailed)

**. Correlation is significant at the 0.01 level (2-tailed).

*. Correlation is significant at the 0.05 level (2-tailed).

Regression

Variables Entered/Removeda

Variables Entered

Variables Removed

UNION, EDUCATION, MARRIED, GENDER, AGE, EXPERIENCEb

a. Dependent Variable: WAGE

b. All requested variables entered.

Model Summary

Adjusted R Square

Std. Error of the Estimate

a. Predictors: (Constant), UNION, EDUCATION, MARRIED, GENDER, AGE, EXPERIENCE

Sum of Squares

Mean Square

Regression

8454.488

1409.081

Residual

23289.549

31744.037

a. Dependent Variable: WAGE

b. Predictors: (Constant), UNION, EDUCATION, MARRIED, GENDER, AGE, EXPERIENCE

Coefficientsa

Unstandardized Coefficients

Standardized Coefficients

Std. Error

(Constant)

EDUCATION

EXPERIENCE

a. Dependent Variable: WAGE

References

Doing business in 2011. (2010). Washington, USA: World Bank.

Ho, T. and Yi, S. (2004). The Oxford guide to financial modeling. Oxford: Oxford University Press.

Lintner, J. (1965). The Valuation of Risk Assets and the Selection of Risky Investments in Stock Portfolios and Capital Budgets. The Review of Economics and Statistics, 47(1), p.13.

Lohrmann, C. (n.d.). Comparison of the CAPM, the Fama-French Three Factor Model and Modifications.

Martineau, M. (2000). Occam’s razor. Gurnee, IL: Nightengale Press.

Schmidt, M. (2008). Taking Shots At CAPM | Investopedia. [online] Investopedia. Available at: http://www.investopedia.com/articles/financial-theory/09/capm-error-problem.asp [Accessed 17 May 2016].

Sharpe, W. (1964). Capital Asset Prices: A Theory of Market Equilibrium under Conditions of Risk. The Journal of Finance, 19(3), p.425.

Synthesispartnership.com. (2016). Critical Issues 06 Financial Modeling. [online] Available at: http://www.synthesispartnership.com/critical06/ [Accessed 17 May 2016].