Question

2. Customers visit EuroPet’s gas stations to buy gasoline and convenience items at the co-located c-stores. For an investigation of the relationship between c-store sales and gasoline sales run a regression of sales against fuel volume.

b. Provide estimates for average c-store sales in the data set for weeks when fuel volume levels were at the smallest, average, and largest observed value, respectively. a. Is there a statistically significant relationship between c-store sales and fuel volume?

c. Provide respective 95 percent intervals for your three estimates in part b. Comment on the widths of the three intervals.

d. Comment on the relationship (cause and effect) between c-store sales and fuel volume.

Answer 1

a)

The regression equation is defined as,

Y = Bo + BLX

where the dependent variable Y = Sales, and the independent variable, X = fuel volume.

Now, the regression analysis is done in excel by following steps

Step 1: Write the data values in excel. The screenshot is shown below,

А в 1 Sales fuel volume 20199 57557 21854 63368 19859 59428

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

DATA REVIEW Data Analysis ? X Гок HE INSERT PAGE LAYOUT FORMULAS AL ZA X - Data Analysis Analysis Tools Fourier Analysis Histogram Moving Average Random Number Generation Rank and Percentile Regression Sampling t-Test: Paired Two Sample for Means t-Test: Two-Sample Assuming Equal Variances t-Test: Two-Sample Assuming Unequal Variances Cancel Help 36.6

Step 3: Select Input Y Range: ''Sales" column, Input X Range: 'Fuel volume' column then OK. The screenshot is shown below,

C A B 1 Sales fuel volume 20199 57557 ALOCH Regression ? X Input Input Y Range: SA$1:$A$102 ок Cancel Input X Range: $B$1:$B$102 Help Labels Confidence Level: Constant is Zero 95 % $F$1 Output options O Output Range: O New Worksheet Ply: O New Workbook

The result is obtained. The screenshot is shown below,

H 1 SUMMARY OUTPUT 3 Regression Statistics 4 Multiple R 0.612767 5 R Square 0.375483 6 Adjusted R Squ 0.369175 7 Standard Error 1946.334 8 Observations 101 10 ANOVA 12 Regression 13 Residual 14 Total df SSMSF gnificance F 1 2.25E+08 2.25E+08 59.52247 9.73E-12 99 3.75E+08 3788214 100 6.01E+08 17 Intercept 18 fuel volume Coefficientsandard Err Stat P-value Lower 95%Upper 95% -17489.7 5259.953 -3.32507 0.001241 -27926.6 -7052.82 0.645213 0.083637.71508 9.73E-12 0.479273 0.811154

Conclusion:

The regression equation is,

Y = -17489.7 +0.6452X

Overall Significance

The significance F value is 9.73E-12 which is less than 0.05 at a 5% significance level which means the model fits the data value at the 5% significance level. Hence there is a statistically significant relationship between c-store sales and fuel volume

b)

The smallest, average, and largest value for the fuel volume levels is obtained in excel. The screenshot is shown below,

E2 - x fx =MIN(B2:B102) C D B 1 fuel volume 57557 63368 59428 62142 Smallest Average Largest E F Excel function 56259 MIN(B2:B102) 62852.76 AVERAGE(B2:B102) 68549 MAX(B2:B102)

Now, the sales for each of these fuel volume levels are obtained from the regression model.

For smallest = Y = -17489.7 +0.6452 x 56259 = 18809.35

For average = Y = -17489.7 +0.6452 x 62852.76 = 23063.73

For largest → Y = -17489.7 +0.6452 X 68549 = 26739.02

c)

For the smallest value, X = 56259

The standard error of the regression is,

SE = 1946.334

Y = 18809.35

Now, the confidence interval for Xpredictor = 56259 is obtained using the formula,

CI =Y 4ti-a/2 X SE-+: 1. (Xpredictor – X)2 η ΣΧ2 - (ΣΧ)2

From the data values,

ΣΧ = 6348129, X = 62852.76, Σx? = 399539081103

The t-critical value is obtained from t-distribution table for significance level = 0.05 and degree of freedom = n - 2 = 101 - 2 = 99

t1-a/2 = 1.9842

Now,

CI = 18809.35+1.9842x1946.334, (56259 - 62852.76)2 399539081103 - 6 (6348129)2

95% CI = (17649.66, 19969.04)

For the average value, X = 62852.76

CI = 23063.73+1.9842 x 1946.334, (62852.76 – 62852.76)2 399539081103 - 6 (6348129)2

95% CI = (22679.45, 23448.01)

For the largest value, X = 68549

CI = 26739.02=1.9842x1946.334, (68549 - 62852.76)2 399539081103 - 6 (6348129)2

95% CI = (25718.66, 27759.38)

Conclusion:

The 95% CI and the width of the interval are obtained as shown below,

	Smallest	Average	Largest
Lower limit	17649.66	22679.45	25718.66
Upper limit	19969.04	23448.01	27759.38
width	2319.38	768.5564	2040.726

We can see that the width of the CI is the smallest for the average value.

d)

In the regression analysis, the dependent variable is the effect and the independent variable is considered as the cause,

From the regression analysis, the estimate of the slope coefficient = 0.645213 which means the one unit increase in the fuel volume, the sales increase by 0.645213.