SOLVE THE FOLLOWING PROBLEMS IN TWO WAYS:

(a) USING CALCULATOR (by hand) OR EXCEL BASIC COMPUTATIONS

(b) USING MS EXCEL REGRESSION ANALYSIS

Write out the regression equation.

What is the correlation coefficient?

What is r-square (coefficient of determination)?

Draw the conclusion (very important)

Problem 1: A researcher is interested in determining whether there is a relationship between price and quantity demanded for her firm.

Price(X)

Q-demanded(Y)

2

95

3

90

4

80

5

78

6

72

7

69

8

71

9

70

10

60

11

50

12

44

Here is an example given

Problem (with solution):

A researcher is interested in determining whether there is a relationship between number of packs of cigarettes smoked per day and longevity (in years). n=10.

# packs of cigarettes smoked

(X)

Longevity (Y)

0

80

0

70

1

72

1

70

2

68

2

65

3

69

3

60

4

58

4

55

Answer:

A:

1- You are given data for Xi (independent variable) and Yi (dependent variable).

X = 20; Y = 667; XY = 1247; X2 = 60; Y2 = 44,983 (done with basic excel computations)

2- Calculate the correlation coefficient, r:

r = EMBED Equation.3 -1 r 1

r = (10*1247-20*667)/[squareroot(10*60-400)(10*44983-444889)]=

-870/squareroot(200*4941)= -870/994.08 = -.875

3- Calculate the coefficient of determination: r2 = EMBED Equation.3 = (r)2 = .766

0 r2 1

This is the proportion of the variation in the dependent variable (Yi) explained by the independent variable (Xi)

4- Calculate the regression coefficient b1 (the slope):

b1 = EMBED Equation.3 = -870/(10*60 - 400) = - 4.35

Note that you have already calculated the numerator and the denominator for parts of r. Other than a single division operation, no new calculations are required.

BTW, r and b1 are related. If a correlation is negative, the slope term must be negative; a positive slope means a positive correlation.

5- Calculate the regression coefficient b0 (the Y-intercept, or constant):

b0 = EMBED Equation.3 = 66.7 - (-4.35*2) = 75.4

6- The regression equation (a straight line) is:

EMBED Equation.3 = b0 + b1Xi Longevity = 75.4 - 4.35 (Packs of cigarettes smoked)

B. Excel regression analysis

SHAPE \* MERGEFORMAT

Conclusion (VERY IMPORTANT)

The regression is statistically significant (F-value = 26.18, p =.0009); Explain why; Check file "regression Interpretation"

r = -.875; it shows a strong negative relationship; the higher the cigarettes intake the lower the longevity.

r2 = 76.6% the coefficient of determination; 76.6% of the variation in the dependent variable Longevity is explained by the independent variable number of cigarette packs and 23.4% is not.

The regression equation is:

EMBED Equation.3 = b0 + b1Xi Longevity = 75.4 - 4.35* (Packs of cigarettes smoked)

Every pack a person smokes per day results on average in a loss of 4.35 years of life.

Someone who does not smoke ( solve for x= 0) is expected to live in average 75.4 years.

Theoretically if you smoke 17 packs a day you will live less than a year. (solve for y = 0)