Financial Modeling: Statistical Package for the Social Sciences - Research Paper Example

FINANCIAL MODELLING Introduction In this report, we attempt to explore whether a company should introduce a profit sharing scheme for all employees. Two theoretical principles have been postulated before. One postulates that introduction of profit sharing scheme will improve employee satisfaction and organisational commitment. The other theory argues that there will be greater alignment of employee and employer interests if such a scheme is introduced. Six variables have been used to conduct a quantitative analysis. The variables are; Profit margin of company (expressed as a percentage), Total assets of company (expressed in £ millions), Extent of team working within the company (expressed as a percentage), Scheme (coded as 1 if company has a profit sharing scheme) and 0 otherwise, Market share of company (expressed as a percentage), the age of the company (since how many years it was established) and lastly, Return on capital employed (also expressed as a percentage). First we show graphical representation of the data to understand any issues or patterns which arise from the data, we then conduct univariate and bivariate analysis to find out if there is a correlation between profit sharing scheme (the dependent variable) with the independent variables (the six aforementioned variables) one by one, later the multiple variable analysis to discover if the overall model is significant or not, which means we explore if the six independent variable all together have an influence on profit sharing scheme or not. Finally a logistic regression analysis was run to find out the impact (positive or negative) that the six independent variables have on the profit sharing scheme. The SPSS computer program was used to conduct the statistical analysis while the excel software was used for the graphical visualizations. a) Graphical representation Graphical visualization helps us to visually explore and understand the pattern of a given data set. In this section we present the graphical visualizations of the variables. We begin by the Profit margin of company (expressed in percentage). Figure 1: Graph of Profit margin of company (expressed as a percentage). The graph clearly shows that that none of the companies has managed to reach a profit margin of 35% and slightly few have a profit margin of below 5%. The highest profit margin recorded is 32.33% and the lowest profit margin is 4.4%. Figure 2: A graph of Total assets of company (expressed in £ million). Figure 2 presents a graph of total assets of company; we observe that most companies had assets of between £ 2000-8000 (millions). The company with the highest assets had £ 13,686.62(millions) worth of assets while the lowest company had £ 270.65 (millions) worth of assets. Figure 3: A graph of Extent of team working within the company (expressed as a percentage) The company with the highest extent of team working within the company had 77% while the lowest extent of team working within a company is 0%. Most companies had teams working within the company to an extent of between 10%-40%. Figure 4: A graph of Market share of company (expressed as a percentage) The highest recorded market share for the companies is 0.59% while the lowest market share is 0.02%. Majority of companies had shares ranging between 0.2%-0.4%. Figure 5: A graph of Age of company (in years) Figure 5 above presents the age of companies in years. The oldest company is aged 96 years old while the youngest company is aged 34 years old. Most the company are aged between 50-70 years old. Figure 6: A graph of Return on capital employed (ROCE) expressed in percentage Most companies employed Return on capital (ROCE) of between 13-14%, with the highest ROCE being 14.92% and the lowest ROCE being 6.9%. b) Univariate and bivariate analysis i) Univariate analysis First of all, before running or applying any form of analysis it is worth looking at the variables to check for the patterns and behaviours exhibited by the data and that nothing in the data looks strange. The descriptive statistics such as: the maximum and minimum value, mean, skewness and kurtosis have been explored. In this section therefore we provide descriptive statistics for the variables. Measures of central tendency have been presented in the table 1 below. We discuss based on variable. Profit margin (Margin) The average profit margin for the companies is 15.65%, the skewness is 0.34>0 meaning that the variable has a Right skewed distribution - most values are concentrated on left of the mean, with extreme values to the right. The kurtosis is given as -1.016 meaning that the variable has a Platykurtic distribution, flatter than a normal distribution with a wider peak. The probability for extreme values is thus less than for a normal distribution, and the values are wider spread around the mean. Table 1: Descriptive Statistics Variable Minimum Maximum Mean Skewness Kurtosis Margin 4.40 32.33 15.6512 .340 -1.016 Size 271 13687 4984.06 .630 .429 Team 0 77 22.12 .799 .416 Share .02 .59 .2768 .005 -.420 Age 34 96 62.60 .019 .001 ROCE 6.90 14.92 13.7910 -4.809 36.120 Total assets of company (Size) The average of the total assets for the companies is £ 4948.06 (millions), the skewness is 0.63>0 meaning that the variable has a Right skewed distribution - most values are concentrated on left of the mean, with extreme values to the right. The kurtosis is given as 0.429 meaning that the variable has a Platykurtic distribution, flatter than a normal distribution with a wider peak. The probability for extreme values is thus less than for a normal distribution, and the values are wider spread around the mean. Extent of team working within the company (Team) The average for the extent of team working within the company is 22.12%, the skewness is 0.799>0 meaning that the variable has a Right skewed distribution - most values are concentrated on left of the mean, with extreme values to the right. The kurtosis is given as 0.416 meaning that the variable has a Platykurtic distribution, flatter than a normal distribution with a wider peak. The probability for extreme values is thus less than for a normal distribution, and the values are wider spread around the mean. Market share of company (Share) The average for the market share of company is 0.277%, the skewness is 0.005>0 meaning that the variable has a Right skewed distribution - most values are concentrated on left of the mean, with extreme values to the right. The kurtosis is given as -0.420 meaning that the variable has a Platykurtic distribution, flatter than a normal distribution with a wider peak. The probability for extreme values is thus less than for a normal distribution, and the values are wider spread around the mean. Age of company (Age) The average age of companies is 62.6 years, the skewness is 0.019>0 meaning that the variable has a Right skewed distribution - most values are concentrated on left of the mean, with extreme values to the right. The kurtosis is given as 0.001 meaning that the variable has a Platykurtic distribution, flatter than a normal distribution with a wider peak. The probability for extreme values is thus less than for a normal distribution, and the values are wider spread around the mean. Return on capital employed (ROCE) The average return on capital employed (ROCE) is 13.79%, the skewness is -4.809 Chi² Intercept -2.748 0.360 Share (%) 11.155 0.056 Age (years) -0.015 0.267 ROCE (%) 0.267 0.252 Margin (%) 0.071 0.183 Size (£m) -0.001 0.047 Team (%) 0.007 0.501 Share - The coefficient (or parameter estimate) for the variable, Market share of company, is 11.155.  This means that for a one-unit increase in Market share of company, we expect a 11.155 increase in the log-odds of the dependent variable Profit share scheme, holding all other independent variables constant.  Age - For every one-unit increase in Age of company (so, for every additional year in the life of the company), we expect a .015 decrease in the log-odds of Profit share scheme, holding all other independent variables constant. ROCE - For every one-unit increase in Return on capital employed, we expect a .267 increase in the log-odds of Profit share scheme, holding all other independent variables constant. Margin - The coefficient (or parameter estimate) for the variable Profit margin of company is .071.  This means that for a one-unit increase in Profit margin of company, we expect a .071 increase in the log-odds of the dependent variable Profit share scheme, holding all other independent variables constant.  Size - For every one-unit increase in Total assets of company (so, for every additional year in the life of the company), we expect a .001 decrease in the log-odds of Profit share scheme, holding all other independent variables constant. Team - For every one-unit increase in Extent of team working within the company, we expect a .007 increase in the log-odds of Profit share scheme, holding all other independent variables constant. Conclusion In conclusion, this report used different types of analysis to determine the factors which influence the company’s profit sharing scheme. The visualization graphs, the univariate analysis, the bivariate analysis, the multiple variable (logistic regression) analysis are some of the analysis that were performed using the SPSS computer program. The overall pattern showed that the data variables have a good distribution. Statistically, only one variables (ROCE) was found to have correlation with Profit sharing scheme. The result also showed that the overall model is significant and that the independent variables have both positive and negative impact on Profit sharing scheme. The logistic regression showed that out of the six independent variables, two variables (Age and Size) had negative impact with the dependent variable (Profit sharing scheme) while the rest had positive impact. APPENDIXES Appendix (1): Univariate analysis results. Descriptive Statistics N Minimum Maximum Mean Std. Deviation Variance Scheme 250 0 1 .78 .418 .175 Age 250 34 96 62.60 11.278 127.198 Share 250 .02 .59 .2768 .11736 .014 ROCE 250 6.90 14.92 13.7910 .77461 .600 Margin 250 4.40 32.33 15.6512 7.87806 62.064 Size 250 271 13687 4984.06 2537.541 6.439E6 Team 250 .00 77.00 22.1240 15.10328 228.109 Valid N (listwise) 250 Appendix (2): Bivariate analysis results. Correlations Scheme Share Age ROCE Margin Size Team Scheme Pearson Correlation 1 .064 -.067 .130* -.085 -.023 .064 Sig. (2-tailed) .314 .291 .039 .181 .715 .316 N 250 250 250 250 250 250 250 Share Pearson Correlation .064 1 -.035 .543** .055 .822** -.042 Sig. (2-tailed) .314 .583 .000 .385 .000 .512 N 250 250 250 250 250 250 250 Age Pearson Correlation -.067 -.035 1 .010 .041 -.022 -.017 Sig. (2-tailed) .291 .583 .880 .520 .725 .792 N 250 250 250 250 250 250 250 ROCE Pearson Correlation .130* .543** .010 1 .089 .445** .117 Sig. (2-tailed) .039 .000 .880 .162 .000 .065 N 250 250 250 250 250 250 250 Margin Pearson Correlation -.085 .055 .041 .089 1 .571** -.077 Sig. (2-tailed) .181 .385 .520 .162 .000 .225 N 250 250 250 250 250 250 250 Size Pearson Correlation -.023 .822** -.022 .445** .571** 1 -.077 Sig. (2-tailed) .715 .000 .725 .000 .000 .223 N 250 250 250 250 250 250 250 Team Pearson Correlation .064 -.042 -.017 .117 -.077 -.077 1 Sig. (2-tailed) .316 .512 .792 .065 .225 .223 N 250 250 250 250 250 250 250 *. Correlation is significant at the 0.05 level (2-tailed). **. Correlation is significant at the 0.01 level (2-tailed). A Matrix Scatterplots ANOVA Tests of Between-Subjects Effects Dependent Variable:Scheme Source Type III Sum of Squares df Mean Square F Sig. Corrected Model 8.595a 49 .175 1.006 .471 Intercept 82.564 1 82.564 473.678 .000 Age 8.595 49 .175 1.006 .471 Error 34.861 200 .174 Total 194.000 250 Corrected Total 43.456 249 a. R Squared = .198 (Adjusted R Squared = .001) Tests of Between-Subjects Effects Dependent Variable:Scheme Source Type III Sum of Squares df Mean Square F Sig. Corrected Model 7.648a 52 .147 .809 .815 Intercept 86.891 1 86.891 478.040 .000 Share 7.648 52 .147 .809 .815 Error 35.808 197 .182 Total 194.000 250 Corrected Total 43.456 249 a. R Squared = .176 (Adjusted R Squared = -.042) Tests of Between-Subjects Effects Dependent Variable:Scheme Source Type III Sum of Squares df Mean Square F Sig. Corrected Model 27.523a 138 .199 1.389 .036 Intercept 114.334 1 114.334 796.510 .000 ROCE 27.523 138 .199 1.389 .036 Error 15.933 111 .144 Total 194.000 250 Corrected Total 43.456 249 a. R Squared = .633 (Adjusted R Squared = .178) Tests of Between-Subjects Effects Dependent Variable:Scheme Source Type III Sum of Squares df Mean Square F Sig. Corrected Model 39.956a 236 .169 .629 .911 Intercept 149.285 1 149.285 554.488 .000 Margin 39.956 236 .169 .629 .911 Error 3.500 13 .269 Total 194.000 250 Corrected Total 43.456 249 a. R Squared = .919 (Adjusted R Squared = -.543) Tests of Between-Subjects Effects Dependent Variable:Scheme Source Type III Sum of Squares df Mean Square F Sig. Corrected Model 6.193a 48 .129 .696 .931 Intercept 68.640 1 68.640 370.249 .000 Team 6.193 48 .129 .696 .931 Error 37.263 201 .185 Total 194.000 250 Corrected Total 43.456 249 a. R Squared = .143 (Adjusted R Squared = -.062) Appendix (3): Multivariate analysis results. Logistic Regression Case Processing Summary Unweighted Casesa N Percent Selected Cases Included in Analysis 250 100.0 Missing Cases 0 .0 Total 250 100.0 Unselected Cases 0 .0 Total 250 100.0 a. If weight is in effect, see classification table for the total number of cases. Dependent Variable Encoding Original Value Internal Value 0 0 1 1 Classification Tablea,b Observed Predicted Scheme Percentage Correct 0 1 Step 0 Scheme 0 0 56 .0 1 0 194 100.0 Overall Percentage 77.6 a. Constant is included in the model. b. The cut value is .500 Variables not in the Equationa Score df Sig. Step 0 Variables Share 1.023 1 .312 Age 1.123 1 .289 ROCE 4.256 1 .039 Margin 1.801 1 .180 Size .135 1 .714 Team 1.012 1 .314 a. Residual Chi-Squares are not computed because of redundancies. Omnibus Tests of Model Coefficients Chi-square df Sig. Step 1 Step 11.729 6 .068 Block 11.729 6 .068 Model 11.729 6 .068 Model Summary Step -2 Log likelihood Cox & Snell R Square Nagelkerke R Square 1 254.233a .046 .070 a. Estimation terminated at iteration number 5 because parameter estimates changed by less than .001. Hosmer and Lemeshow Test Step Chi-square df Sig. 1 7.026 8 .534 Contingency Table for Hosmer and Lemeshow Test Scheme = 0 Scheme = 1 Total Observed Expected Observed Expected Step 1 1 10 10.223 15 14.777 25 2 11 7.384 14 17.616 25 3 7 6.697 18 18.303 25 4 6 6.179 19 18.821 25 5 5 5.685 20 19.315 25 6 3 5.176 22 19.824 25 7 2 4.810 23 20.190 25 8 6 4.236 19 20.764 25 9 3 3.346 22 21.654 25 10 3 2.265 22 22.735 25 Classification Tablea Observed Predicted Scheme Percentage Correct 0 1 Step 1 Scheme 0 1 55 1.8 1 1 193 99.5 Overall Percentage 77.6 a. The cut value is .500 Variables in the Equation B S.E. Wald df Sig. Exp(B) Step 1a Share 11.155 5.849 3.637 1 .056 6.995E4 Age -.015 .014 1.231 1 .267 .985 ROCE .267 .233 1.312 1 .252 1.306 Margin .071 .054 1.775 1 .183 1.074 Size .000 .000 3.949 1 .047 .999 Team .007 .011 .452 1 .501 1.007 Constant -2.748 3.000 .839 1 .360 .064 a. Variable(s) entered on step 1: Share, Age, ROCE, Margin, Size, Team. Correlation Matrix Constant Share Age ROCE Margin Size Team Step 1 Constant 1.000 .069 -.243 -.914 -.044 .042 .042 Share .069 1.000 -.031 -.324 .874 -.963 .026 Age -.243 -.031 1.000 -.039 -.068 .051 .019 ROCE -.914 -.324 -.039 1.000 -.201 .189 -.137 Margin -.044 .874 -.068 -.201 1.000 -.924 .027 Size .042 -.963 .051 .189 -.924 1.000 .002 Team .042 .026 .019 -.137 .027 .002 1.000              Step number: 1              Observed Groups and Predicted Probabilities       20 ┼                                                                                                    ┼          │                                                                                1                   │          │                                                                                1                   │ F        │                                                                               11                   │ R     15 ┼                                                                               11                   ┼ E        │                                                                       1    1  11                   │ Q        │                                                                       1  1111 11 1                 │ U        │                                                                       1  1111 1111                 │ E     10 ┼                                                                       1 1111111111     1           ┼ N        │                                                                       1 1111111111     1           │ C        │                                                                       111111111111 1  11           │ Y        │                                                                       111111111111111 11 1         │        5 ┼                                                                    1  011111111111111 11 1         ┼          │                                                               1    111011101011111111111 1         │          │                                                       1   1  10 11100000010001001001111111111      │          │                     1          0                  1  100 11 0000101000000000000000000100101011 1   │ Predicted ─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼──────────   Prob:   0       .1        .2        .3        .4        .5        .6        .7        .8        .9         1   Group:  0000000000000000000000000000000000000000000000000011111111111111111111111111111111111111111111111111           Predicted Probability is of Membership for 1           The Cut Value is .50           Symbols: 0 - 0                    1 - 1           Each Symbol Represents 1.25 Cases. Read More