Question

1. A company has recorded data on the weekly sales for its product (y) and the unit price of the competitor’s product (x). The data resulting from a random sample of 7 weeks follows. Use Excel’s Regression Tool to answer the following questions.

A company has recorded data on the weekly sales for its product (y) and the unit price of the competitor’s product (x). The data resulting from a random sample of 7 weeks follows. Use Excel’s Regression Tool to answer the following questions. Week Price Sales 1 .33 20 2 .25 14 3 .44 22 4 .40 21 5 .35 16 6 .39 19 7 .29 15 Using the outputs shown above

1. 
   What is the estimated regression equation?
2. Perform a t test and determine whether or not x and y are related. Use a = 0.05.
3. Perform an F test and determine whether or not x and y are related. Use a = 0.05.
4. Find and interpret the coefficient of determination.

Answer 1

Answer a. The Estimated Regression Equation is found looking at the coefficient table, highlighted in yellow :

Answer b.

Null Hypothesis, Ho:

Alternative Hypothesis, Ha:

Test Statistics: Under Ho,

  

  

p-value = 0.009385 (from output)

Decision: Since p-value < 0.05, so we reject Ho at 5% level of significance and conclude i.e. X and Y are related.

Answer c.

Null Hypothesis, Ho: X and Y are not related

Alternative Hypothesis, Ha: X and Y are related

Test Statistics:

Under Ho,

  

Using Output,

Test-Statistics, F = 16.78311

and p-value = 0.009385

Decision: Since p-value < 0.05, so we reject Ho at 5% level of significance and conclude X and Y are related.

Answer d.

R2 = Coefficient of Determination gives the total variation in Y as explained by X.

Here, R2 =0.77046 i.e. 77.05% of the total variation in Y is explained by X.

Excel Output