 Home
 Finance & Accounting
 FINANCIAL MODELLING
FINANCIAL MODELLING
 Category:Finance & Accounting
 Document type:Coursework
 Level:Undergraduate
 Page:5
 Words:3477
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

Perfect information
All investors can access the same information
Analysis of information is done in the same manner

Markets are smooth without friction
There are no taxes
No transaction costs/No commissions

Security markets compete perfectly
Many investors who compete perfectly (price takers)

Myopic investors
All investors have one and same holding period

All investors rationally optimize mean and variance
Everyone uses Markowitz portfolio selection criterion

Investments are limited to publicly traded assets with unlimited lending at the riskfree rate

Investors can borrow and lend at the one risk free rate

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

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

All investors have the same planning horizon

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

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.

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

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

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 overforecast 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
i.e the model value p>0.05 implies statistical insignificance of
value. The same holds for technology stock. As for the 10 year period regression, third 10 year regression shows significant
only for the shop. The rest show insignificant
Looking at the data profile, the
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,
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
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:)
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 crosssection of equity portfolios of different sizes and styles (Lohrmann, 2015). The key features of the model include:

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

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
,
, ,
For both the Shop and technology industries, the model constant value is not significant (p>0.0500), hence the null hypothesis
is true. i.e
. However, the model results show that
and
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,
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: Coefficients^{a} 

Unstandardized Coefficients 
Standardized Coefficients 

Std. Error 

(Constant) 

EDUCATION 
Table: Model Summary and regression 

Adjusted R Square 
Std. Error of the Estimate 

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. (2tailed) 

EDUCATION 
Pearson Correlation 

Sig. (2tailed) 

EXPERIENCE 
Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

Correlations 

Pearson Correlation 

Sig. (2tailed) 

EDUCATION 
Pearson Correlation 

Sig. (2tailed) 

EXPERIENCE 
Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

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:

Formula errors which are the easiest errors to make

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

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 computerbased 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
Regressionoutput for Shopall years
Variables Entered/Removed^{a} 

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), MktRF 

Sum of Squares 
Mean Square 

Regression 
8226.228 
8226.228 

Residual 
5394.427 

13620.655 
a. Dependent Variable: ShpRF 
b. Predictors: (Constant), MktRF 
Coefficients^{a} 

Unstandardized Coefficients 
Standardized Coefficients 

Std. Error 

(Constant) 

a. Dependent Variable: ShpRF 
Regressionoutput for technology for all the years
Variables Entered/Removed^{a} 

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), MktRF 
Sum of Squares 
Mean Square 

Regression 
16253.182 
16253.182 

Residual 
11943.635 

28196.817 
a. Dependent Variable: TechRF 

b. Predictors: (Constant), MktRF 

Coefficients^{a} 

Unstandardized Coefficients 
Standardized Coefficients 

Std. Error 

(Constant) 

a. Dependent Variable: TechRF 
Regression output for the 10 year periods
Regression1st 10 year shop
Variables Entered/Removed^{a} 

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), MktRF 

Sum of Squares 
Mean Square 

Regression 
2765.458 
2765.458 

Residual 
1019.567 

3785.025 
a. Dependent Variable: ShpRF 
b. Predictors: (Constant), MktRF 
Coefficients^{a} 

Unstandardized Coefficients 
Standardized Coefficients 

Std. Error 

(Constant) 

a. Dependent Variable: ShpRF 
Regression 1st 10year technology
Variables Entered/Removed^{a} 

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), MktRF 

Sum of Squares 
Mean Square 

Regression 
4630.531 
4630.531 

Residual 
1899.871 

6530.402 
a. Dependent Variable: TechRF 
b. Predictors: (Constant), MktRF 
Coefficients^{a} 

Unstandardized Coefficients 
Standardized Coefficients 

Std. Error 

(Constant) 

a. Dependent Variable: TechRF 
Regression2nd 10 year shop
Variables Entered/Removed^{a} 

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), MktRF 
Sum of Squares 
Mean Square 

Regression 
1666.046 
1666.046 

Residual 
1679.420 

3345.465 
a. Dependent Variable: ShpRF 

b. Predictors: (Constant), MktRF 

Coefficients^{a} 

Unstandardized Coefficients 
Standardized Coefficients 

Std. Error 

(Constant) 

a. Dependent Variable: ShpRF 
Regression2nd 10 year tech
Variables Entered/Removed^{a} 

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), MktRF 

Sum of Squares 
Mean Square 

Regression 
3143.522 
3143.522 

Residual 
3645.956 

6789.478 
a. Dependent Variable: TechRF 
b. Predictors: (Constant), MktRF 
Coefficients^{a} 

Unstandardized Coefficients 
Standardized Coefficients 

Std. Error 

(Constant) 

a. Dependent Variable: TechRF 
Regression3rd 10year shop
Variables Entered/Removed^{a} 

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), MktRF 

Sum of Squares 
Mean Square 

Regression 
4051.805 
4051.805 

Residual 
2432.948 

6484.753 
a. Dependent Variable: ShpRF 
b. Predictors: (Constant), MktRF 
Coefficients^{a} 

Unstandardized Coefficients 
Standardized Coefficients 

Std. Error 

(Constant) 

a. Dependent Variable: ShpRF 
Regression3rd 10 year tech
Variables Entered/Removed^{a} 

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), MktRF 

Sum of Squares 
Mean Square 

Regression 
8908.616 
8908.616 

Residual 
5790.849 

14699.466 
a. Dependent Variable: TechRF 
b. Predictors: (Constant), MktRF 
Coefficients^{a} 

Unstandardized Coefficients 
Standardized Coefficients 

Std. Error 

(Constant) 

a. Dependent Variable: TechRF 
Appendix 1B. Fama French Model
RegressionFama French model shop
Variables Entered/Removed^{a} 

Variables Entered 
Variables Removed 

HML, SMB, MktRF^{b} 
a. Dependent Variable: ShpRF 
b. All requested variables entered. 
Model Summary 

Adjusted R Square 
Std. Error of the Estimate 

a. Predictors: (Constant), HML, SMB, MktRF 
Sum of Squares 
Mean Square 

Regression 
10629.864 
3543.288 

Residual 
2990.791 

13620.655 
a. Dependent Variable: ShpRF 
b. Predictors: (Constant), HML, SMB, MktRF 
Table 1B4. Coefficients^{a} 

Unstandardized Coefficients 
Standardized Coefficients 

Std. Error 

(Constant) 

a. Dependent Variable: ShpRF 
Regression fama french tech
Variables Entered/Removed^{a} 

Variables Entered 
Variables Removed 

HML, SMB, MktRF^{b} 
a. Dependent Variable: TechRF 

b. All requested variables entered. 

Model Summary 

Adjusted R Square 
Std. Error of the Estimate 

a. Predictors: (Constant), HML, SMB, MktRF 

Sum of Squares 
Mean Square 

Regression 
23356.533 
7785.511 

Residual 
4840.284 

28196.817 
a. Dependent Variable: TechRF 
b. Predictors: (Constant), HML, SMB, MktRF 
Table 1B 8. Coefficients^{a} 

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) 
Regressionwages vs education
Variables Entered/Removed^{a} 

Variables Entered 
Variables Removed 

EDUCATION^{b} 
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 
Coefficients^{a} 

Unstandardized Coefficients 
Standardized Coefficients 

Std. Error 

(Constant) 

EDUCATION 
a. Dependent Variable: WAGE 
GGraphWages vs Education
Regressioneducation and experience
Variables Entered/Removed^{a} 

Variables Entered 
Variables Removed 

EXPERIENCE, EDUCATION^{b} 
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 

Coefficients^{a} 

Unstandardized Coefficients 
Standardized Coefficients 

Std. Error 

(Constant) 

EDUCATION 

EXPERIENCE 
a. Dependent Variable: WAGE 
Regressioneducation, gender and experience
Variables Entered/Removed^{a} 

Variables Entered 
Variables Removed 

GENDER, EDUCATION, EXPERIENCE^{b} 
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 
Coefficients^{a} 

Unstandardized Coefficients 
Standardized Coefficients 

Std. Error 

(Constant) 

EDUCATION 

EXPERIENCE 

a. Dependent Variable: WAGE 
Correlations
Correlations 

EDUCATION 
EXPERIENCE 

Pearson Correlation 

Sig. (2tailed) 

EDUCATION 
Pearson Correlation 

Sig. (2tailed) 

EXPERIENCE 
Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

Correlations 

Pearson Correlation 

Sig. (2tailed) 

EDUCATION 
Pearson Correlation 

Sig. (2tailed) 

EXPERIENCE 
Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

Pearson Correlation 

Sig. (2tailed) 

**. Correlation is significant at the 0.01 level (2tailed). 
*. Correlation is significant at the 0.05 level (2tailed). 
Regression
Variables Entered/Removed^{a} 

Variables Entered 
Variables Removed 

UNION, EDUCATION, MARRIED, GENDER, AGE, EXPERIENCE^{b} 
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 

Coefficients^{a} 

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 FamaFrench 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/financialtheory/09/capmerrorproblem.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].