statistics for managers using microsoft excel

12 - 1

© 1998 Prentice-Hall, Inc.

Statistics for Managers Using Microsoft Excel, 1/e

Statistics for Managers Using Microsoft Excel

Multiple Regression Models Chapter 12

Learning Objectives Explain the linear multiple regression model

Interpret linear multiple regression computer output

Explain multicollinearity Describe the types of multiple regression

models

12 - 2



Multiple Regression Models

MultipleRegression

Models

LinearDummyVariable

LinearNon-

Linear

Inter-action

Poly-Nomial

SquareRoot Log Reciprocal Exponential

12 - 3



Linear Multiple Regression Model

Relationship between 1 dependent & 2 or more independent variables is a linear function

Y X X Xi i i P Pi i 0 1 1 2 2

Dependent (response) variable

Independent (explanatory) variables

Population slopes

Population Y-intercept

Random error

12 - 4



Population Multiple Regression Model

X2

Y

X1YX = 0 + 1X 1 i + 2X 2 i

0

Y i = 0 + 1X 1 i + 2X 2 i + i

ResponsePlane

(X 1 i,X 2 i)

(Observed Y )

i

Bivariate modelBivariate model

12 - 5



Sample Multiple Regression Model

X2

Y

X1

b0

Y i = b0 + b1X 1 i + b2X 2 i + e i

ResponsePlane

(X 1 i,X 2 i)

(Observed Y)

^

e i

Y i = b0 + b1X 1 i + b2X 2 i

Bivariate modelBivariate model

12 - 6



Regression Modeling Steps

Define problem or question Specify model Collect data Do descriptive data analysis Estimate unknown parameters Evaluate model Use model for prediction

12 - 7



Multiple Linear Regression Equations

Too complicated

by hand! Ouch!

12 - 8



Interpretation of Estimated Coefficients

Slope (bP) Estimated Y changes by bP for each 1 unit

increase in XP holding all other variables constant

Example: If b1 = 2, then Sales (Y) is expected to increase by 2 for each 1 unit increase in Advertising (X1) given the Number of Sales Rep’s (X2)

Y-Intercept (b0) Average value of Y when XP = 0

12 - 9



Parameter Estimation Example

You work in advertising for the New York Times. You want to find the effect of ad size (sq. in.) & newspaper circulation (000) on the number of ad responses (00).

You’ve collected the following data:

Resp Size Circ

1 1 24 8 81 3 13 5 72 6 44 10 6

12 - 10



Parameter Estimation Excel Output

bbPP

bb00

bb11bb22

12 - 11



Interpretation of Coefficients Solution

Slope (b1) # Responses to Ad is expected to increase

by .2049 (20.49) for each 1 sq. in. increase in Ad Size holding Circulation constant

Slope (b2) # Responses to Ad is expected to increase

by .2805 (28.05) for each 1 unit (1,000) increase in Circulation holding Ad Size constant

12 - 12



Regression Modeling Steps

Define problem or question Specify model Collect data Do descriptive data analysis Estimate unknown parameters

Evaluate model Use model for prediction

Evaluating Multiple Regression Model Steps

12 - 13




Examine variation measures Do residual analysis Test parameter significance

Overall model Portions of model Individual coefficients

Test for multicollinearity

12 - 14







New!Ne

w!New!

Expand

e

d!

12 - 15



Coefficient of Multiple Determination

Proportion of variation in Y ‘explained’ by all X variables taken together

r2Y.12..P = Explained variation = SSR

Total variation SST Never decreases when new X variable

is added to model Only Y values determine SST Disadvantage when comparing models

12 - 16



Proportion of variation in Y ‘explained’ by all X variables taken together

Reflects Sample size Number of independent variables

Smaller than r2Y.12..P

Used to compare models

Adjusted Coefficient of Multiple Determination

12 - 17




Examine variation measures Do residual analysis

Test parameter significance Overall model Portions of model Individual coefficients


New!New!

New!

Expand

ed!

12 - 18



Testing Overall Significance

Shows if there is a linear relationship between all X variables together & Y

Uses F test statistic Hypotheses

H0: 1 = 2 = ... = P = 0 No linear relationship

H1: At least one coefficient is not 0 At least one X variable affects Y

12 - 19







New!Ne

w!New!

Expand

e

d!

12 - 20



Multicollinearity

High correlation between X variables Coefficients measure combined effect Leads to unstable coefficients

depending on X variables in model Always exists; matter of degree Example: Using both Sales & Profit as

explanatory variables in same model

12 - 21



Detecting Multicollinearity

Examine correlation matrix Correlations between pairs of X variables

are more than with Y variable

Examine variance inflation factor (VIF) If VIFj > 5, multicollinearity exists

Few remedies Obtain new sample data Eliminate one correlated X variable

12 - 22




MultipleRegression

Models

LinearDummyVariable

LinearNon-

Linear

Inter-action

Poly-Nomial


12 - 23



Polynomial (Curvilinear) Regression Model

Relationship between 1 dependent & 2 or more independent variables is a quadratic function

Useful 1st model if non-linear relationship suspected

12 - 24



Polynomial (Curvilinear) Regression Model

Relationship between 1 dependent & 2 or more independent variables is a quadratic function

Useful 1st model if non-linear relationship suspected

Polynomial model

Y X Xi i i i 0 1 1 11 12

Linear effect

Curvilinear effect

12 - 25



Y

X1

Polynomial (Curvilinear) Model Relationships

Y

X1

Y

X1

Y

X1

11 > 011 > 0

11 < 011 < 0

12 - 26



Polynomial (Curvilinear) Model Worksheet

Case, i Yi X1i X1i2

1 1 1 1

2 4 8 64

3 1 3 9

4 3 5 25

: : : :Create X1

2 column. Run regression with Y, X1, X1

2

12 - 27




MultipleRegression

Models

Linear DummyVariable

LinearNon-

Linear

Inter-action

Poly-Nomial


12 - 28



Dummy-Variable Regression Model

Involves categorical X variable with 2 levels e.g., male-female, college-no college etc.

Variable levels coded 0 & 1 Assumes only intercept is different

Slopes are constant across categories

12 - 29



Dummy-Variable Regression Model

Involves categorical X variable with 2 levels e.g., male-female, college-no college etc.

Variable levels coded 0 & 1 Assumes only intercept is different

Slopes are constant across categories

Dummy-variable modelY X X Xi i i P Pi i 0 1 1 2 2

12 - 30



Dummy-Variable Model Worksheet

Case, i Yi X1i X2i

1 1 1 1

2 4 8 0

3 1 3 1

4 3 5 1

: : : :X2 levels: 0 = Group 1; 1 = Group 2. Run regression with Y, X1, X2

12 - 31



Interpreting Dummy-Variable Model Equation

Given: Starting salary of college grad'sGPA

iif Female

f Male

Y b b X b XYX

X

i i i

0 1 1 2 2

1

201{

12 - 32





iif Female

Males (

f Male

):

Y b b X b XYX

X

Y b b X b b b X

i i i

i i i

X

0 1 1 2 2

1

2

0 1 1 2 0 1 1

01

(0)2 0

{

12 - 33





iif Female

Males (

f Male

):

Y b b X b XYX

X

Y b b X b b b X

i i i

i

X

0 1 1 2 2

1

2

0 1 1 2 0 1

01

(0)2 0

{

Females (X2 = 1):

Y b b 1 1 2 1 1(1)i 0 b X b

0 b X

1

2b

Same slopes

12 - 34



Dummy-Variable Model Relationships

YY

XX1100

00

Same slopes b1

bb00

b0 + b2

Females

Males

12 - 35



Dummy-Variable Model Example

Computer Output:

f Maleif Female

i

Y X X

X

i i i

3 5 7

01

1 2

2 {

12 - 36




Computer Output:

f Maleif Female

Males (

i

):

Y X X

X

Y X X

i i i

i i i

X

3 5 7

01

3 5 7(0) 3 5

1 2

2

1 1

2 0

{

12 - 37




Computer Output:

f Maleif Female

Males (

i

):

Y X X

X

Y X X

i i i

i i i

X

3 5 7

01

3 5 7(0) 3 5

1 2

2

1 1

2 0

{

Yi 3 5 (1) 51 1

X i 7 10 X i

Females ( ):X 2 1

Same slopes

12 - 38




MultipleRegression

Models

LinearDummyVariable

LinearNon-

Linear

Inter-action

Poly-Nomial


12 - 39



Interaction Regression Model

Hypothesizes interaction between pairs of X variables Response to one X variable varies at

different levels of another X variable

12 - 40






Contains two-way cross product terms Y X X X Xi i i i i i 0 1 1 2 2 3 1 2

12 - 41






Contains two-way cross product terms

Can be combined with other models e.g., dummy variable model

Y X X X Xi i i i i i 0 1 1 2 2 3 1 2

12 - 42



Effect of Interaction

Given:

Y X X X Xi i i i i i 0 1 1 2 2 3 1 2

12 - 43




Given:

Without interaction term, effect of X1 on Y is measured by 1

Y X X X Xi i i i i i 0 1 1 2 2 3 1 2

12 - 44




Given:

Without interaction term, effect of X1 on Y is measured by 1

With interaction term, effect of X1 onY is measured by 1 + 3X2

Effect increases as X2i increases

Y X X X Xi i i i i i 0 1 1 2 2 3 1 2

12 - 45



Interaction Example

XX11

44

88

1212

0000 110.50.5 1.51.5

YY Y = 1 + 2X1 + 3X2 + 4X1X2

12 - 46



Interaction Example

XX11

44

88

1212

0000 110.50.5 1.51.5

YY Y = 1 + 2X1 + 3X2 + 4X1X2

YY = 1 + 2 = 1 + 2XX11 + 3( + 3(00) + 4) + 4XX11((00) = 1 + 2) = 1 + 2XX11

12 - 47



Interaction Example

YY

XX11

44

88

1212

0000 110.50.5 1.51.5

Y = 1 + 2X1 + 3X2 + 4X1X2

YY = 1 + 2 = 1 + 2XX11 + 3( + 3(11) + 4) + 4XX11((11) = 4 + 6) = 4 + 6XX11

YY = 1 + 2 = 1 + 2XX11 + 3( + 3(00) + 4) + 4XX11((00) = 1 + 2) = 1 + 2XX11

12 - 48



Interaction Example

Effect (slope) of X1 on Y does depend on X2 value

XX11

44

88

1212

0000 110.50.5 1.51.5

YY Y = 1 + 2X1 + 3X2 + 4X1X2

YY = 1 + 2 = 1 + 2XX11 + 3( + 3(11) + 4) + 4XX11((11) = 4 + ) = 4 + 66XX11

YY = 1 + 2 = 1 + 2XX11 + 3( + 3(00) + 4) + 4XX11((00) = 1 + ) = 1 + 22XX11

12 - 49



Interaction Regression Model Worksheet

Case, i Yi X1i X2i X1i X2i

1 1 1 3 3

2 4 8 5 40

3 1 3 2 6

4 3 5 6 30

: : : : :Multiply X1 by X2 to get X1X2. Run regression with Y, X1, X2 , X1X2

12 - 50




MultipleRegression

Models

Linear DummyVariable

LinearNon-

Linear

Inter-action

Poly-Nomial

SquareRoot

Log Reciprocal Exponential

12 - 51



Inherently Linear Models

Non-linear models that can be expressed in linear form Can be estimated by LS in linear form

Require data transformation

12 - 52



Inherently Linear Models

Non-linear models that can be expressed in linear form Can be estimated by LS in linear form

Require data transformation Multiplicative model example

iiii

iiii

XXY

XXY

lnlnlnlnln 22110

21021

12 - 53



Square-Root Transformation

Y

X1

Y X Xi i i i 0 1 1 2 2

11 > 0 > 0

11 < 0 < 0

12 - 54



Logarithmic Transformation

Y

X1

11 > 0 > 0

11 < 0 < 0

iiii XXY 22110 lnln

12 - 55



Reciprocal Transformation

Y

X1

11 > 0 > 0

11 < 0 < 0

YX Xi

i ii 0 1

12

2

1 1

AsymptoteAsymptote

12 - 56



Exponential Transformation

Y

X1

11 > 0 > 0

11 < 0 < 0

Y eiX X

ii i 0 1 1 2 2

12 - 57



Inherently Linear Models Worksheet

Case, i Yi X1iX1i lnX1i 1/X1i

1 1 1 1.0 0.00 1.000

2 4 9 3.0 2.20 .1111

3 1 16 4.0 2.77 .0625

4 3 25 5.0 3.22 .0400

: : : : : :

Transform X1.Run regression with Y, & either X1, lnX1 , 1/X1

12 - 58



Conclusion

Explained the linear multiple regression model

Interpreted linear multiple regression computer output

Explained multicollinearity Described the types of multiple

regression models

statistics for managers using microsoft excel

Documents