Regression Analyses Using Excel

Regression Analyses Using Excel:

Part 1:

BENEFITS and INTRINSIC as independent and dependent variable respectively: (equation 1)

The following is a summary of the results:

Regression  Statistics

1/38

Regression Analyses Using Excel

Multiple  R

0.468804

R Square

0.219778

2/38

Regression Analyses Using Excel

Adjusted  R Square

0.185855

Standard  Error

3/38

Regression Analyses Using Excel

0.932503

Observations

25

4/38

Regression Analyses Using Excel

ANOVA

5/38

Regression Analyses Using Excel

df

SS

MS

F

Significance  F

6/38

Regression Analyses Using Excel

Regression

1

5.63369

5.63369

6.4787733

0.018084926

Residual

23

19.99991

0.869561

7/38

Regression Analyses Using Excel

Total

24

25.6336

8/38

Regression Analyses Using Excel

Coefficients

Standard  Error

t Stat P-value

9/38

Regression Analyses Using Excel

Lower  95%

Upper  95%

Intercept

1.554273

1.411685

1.101006

0.2822837

-1.366019034

4.4745652

BENEFITS

0.664006

10/38

Regression Analyses Using Excel

0.260871

2.545343

0.0180849

0.124353418

1.2036581

Equation 1:

INTRINSIC = 1.554273 + 0.664 BENEFITS

T critical value at the 95% level of test is 2.06; comparing this with the t statistics value it is evident that the slope is significant while the intercept is not significant. (Ken, 2009)

11/38

Regression Analyses Using Excel

Part 2:

BENEFITS and EXTRINSIC as independent and dependent variable respectively:

(equation 2)

The following is a summary of the results:

Regression Statistics

12/38

Regression Analyses Using Excel

Multiple  R

0.34268

R Square

0.11743

13/38

Regression Analyses Using Excel

Adjusted  R Square

0.079057

Standard  Error

14/38

Regression Analyses Using Excel

1.016694

Observations

25

15/38

Regression Analyses Using Excel

ANOVA

16/38

Regression Analyses Using Excel

df

SS

MS

F

Significance F

17/38

Regression Analyses Using Excel

Regression

1

3.163276

3.163276

3.0602491

0.093563229

Residual

23

23.77432

1.033666

18/38

Regression Analyses Using Excel

Total

24

26.9376

19/38

Regression Analyses Using Excel

Coefficients

Standard Error

t Stat P-value Lower 95%

20/38

Regression Analyses Using Excel

Upper 95%

Intercept

7.404902

1.539139

4.811069

7.457E-05

4.220951367

10.588853

BENEFITS

-0.49756

0.284424

21/38

Regression Analyses Using Excel

-1.74936

0.0935632

-1.085933127

0.0908167

Equation 2:

EXTRINSIC = 7.404902-0.49756 BENEFITS

T critical value at the 95% level of test is + or – 2.06, comparing this with the t statistics value it is evident that the slope is not significant while the intercept is significant. (Ken, 2009)

22/38

Regression Analyses Using Excel

Part 3:

BENEFITS and OVERALL as independent and dependent variable respectively:

(Equation 3)

The following is a summary of the results:

Regression Statistics

23/38

Regression Analyses Using Excel

Multiple  R

0.140186

R Square

24/38

Regression Analyses Using Excel

0.019652

Adjusted  R Square

-0.02297

25/38

Regression Analyses Using Excel

Standard  Error

0.990779

Observations

25

26/38

Regression Analyses Using Excel

27/38

Regression Analyses Using Excel

ANOVA

df

SS

MS

28/38

Regression Analyses Using Excel

F

Significance F

Regression

1

0.452594

0.452594

0.4610571

0.503906493

Residual

29/38

Regression Analyses Using Excel

23

22.57781

0.981644

Total

24

23.0304

30/38

Regression Analyses Using Excel

Coefficients

Standard Error

31/38

Regression Analyses Using Excel

t Stat P-value Lower 95% Upper 95%

Intercept

4.162472

1.499908

2.775152

0.0107666

1.059676321

7.2652673

32/38

Regression Analyses Using Excel

BENEFITS

0.188204

0.277174

0.679012

0.5039065

-0.385173504

0.7615822

Equation 3:

33/38

Regression Analyses Using Excel

OVERALL = 4.1625 + 0.1882 BENEFITS

T critical value at the 95% level of test is + or – 2.06, comparing this with the t statistics value it is evident that the slope is not significant while the intercept is significant. (Ken, 2009)

Part 4:

Graphs and Regression line equations:

Equation 1:

INTRINSIC = 1.554273 + 0.664 BENEFITS

The chart below summarizes the results for equation 1:

34/38

Regression Analyses Using Excel

From the chart there is a positive relationship between the two variables, when benefit increase then Intrinsic increases.

Slope and intercept:

The slope of the regression model is 0.664 and the Y intercept value is 1.554273. This means if benefits increase by one unit then intrinsic will increase by 0.664 units. (Bowerman, 2003)

R squared:

The R squared value for this model is 0.219778, meaning benefits explain 21.98% of changes in Intrinsic variable. (Bowerman, 2003)

Equation 2:

EXTRINSIC = 7.404902-0.49756 BENEFITS

The chart below summarizes the results for equation 2:

From the chart there is an inverse relationship between the two variables, when benefit increase then extrinsic decreases.

35/38

Regression Analyses Using Excel

Slope and intercept:

The slope of the regression model is -0.49756 and the Y intercept value is 7.4. This means if benefits increase by one unit then extrinsic will increase by –

0.49756

units. (Bowerman, 2003)

R squared:

The R squared value for this model is 0.11743, meaning benefits explain 11.74% of changes in extrinsic variable. (Bowerman, 2003)

Equation 3:

OVERALL = 4.1625 + 0.1882 BENEFITS

36/38

Regression Analyses Using Excel

The chart below summarizes the results for equation 3:

From the chart there is a positive relationship between the two variables, when benefit increase then overall increases.

Slope and intercept:

The slope of the regression model is 0.1882 and the Y intercept value is 4.1625. This means if benefits increase by one unit then overall will increase by

0.1882

units. (Bowerman, 2003)

R squared:

The R squared value for this model is 0.019652, meaning benefits explain 1.97% of changes in overall variable. (Bowerman, 2003)

37/38

Regression Analyses Using Excel

Comment:

The first equation INTRINSIC = 1.554273 + 0.664 BENEFITS produces the strongest relationship, this model has the highest slope value showing a strong relationship between the variables, the R squared value is 0.2198 which is the highest value and therefore this shows the strongest correlation coefficient. The slope of this model is statistically significant while none of the other models value are significant, the second model EXTRINSIC = 7.404902-0.49756 BENEFIT has a negative slope and from the other models benefits increases satisfaction, therefore this model will not be appropriate in estimating satisfaction values.

References:

Bruce Bowerman (2003) Business statistics in practice‎, New Jersey: Prentice Hall press

Ken Black (2009) Business Statistics: Contemporary Decision Making, New York: McGraw hill press

38/38