Question

2. Plattsburgh Construction Company (PCC) renovates old homes in Plattsburgh. Over time, the company has found that its dollar volume of renovation work is dependent on the Plattsburgh area payroll. The figures for Plattsburgh Construction Company’s sales and the amount of money earned by wage earners in Plattsburg for the past six years are presented in the following table.

Year	1	2	3	4	5	6
Local Payroll ($1,000,000s)	3	4	6	4	2	5
PCC’s Sales ($100,000s)	6	8	9	5	4.5	9.5

Conducts a regression analysis and fill in blanks.

Regression Statistics
Multiple R
R Square
Observation	6

	df	SS	MS	F	Significance F
Regression
Residual
Total
	Coefficient	Standard Error	t-Stat	P-value
Intercept
X variable 1

1. State the prediction equation or the regression model.

2. What percent of the variations in PCC’s sales is explained by the model? Is the model powerful in prediction sales? Explain.

3. What do F-ratio and P-value tell about the above regression model?
4. Based on the regression model, what are PCC’s predicted sales if the local payroll is predicted to be $7 million next year?

Answer 1

X	Y	XY	X²	Y²
3	6	18	9	36
4	8	32	16	64
6	9	54	36	81
4	5	20	16	25
2	4.5	9	4	20.25
5	9.5	47.5	25	90.25
Ʃx =	Ʃy =	Ʃxy =	Ʃx² =	Ʃy² =
24	42	180.5	106	316.5

Sample size, n =	6
x̅ = Ʃx/n = 24/6 =	4
y̅ = Ʃy/n = 42/6 =	7
SSxx = Ʃx² - (Ʃx)²/n = 106 - (24)²/6 =	10
SSyy = Ʃy² - (Ʃy)²/n = 316.5 - (42)²/6 =	22.5
SSxy = Ʃxy - (Ʃx)(Ʃy)/n = 180.5 - (24)(42)/6 =	12.5

Correlation coefficient, r = SSxy/√(SSxx*SSyy) = 12.5/√(10*22.5) = 0.8333333

Coefficient of determination, r² = (SSxy)²/(SSxx*SSyy) = (12.5)²/(10*22.5) = 0.6944444

df(Regression) = 1

df(residual) = n-2 = 4

df(total) = n-1 = 5

SSR = SSxy²/SSxx = (12.5)²/10 = 15.6250

SSE = SSyy -SSxy²/SSxx = 22.5 - (12.5)²/10 = 6.8750

SST = SSyy = Ʃy² - (Ʃy)²/n = 22.5000

MSR = SSR/df(regression) = 15.6250

MSE = SSE/df(residual) = 1.7188

F = MSR/MSE = 9.0909

p-value = F.DIST.RT(9.0909, 1, 4) = 0.0394

Slope, b1 = SSxy/SSxx = 12.5/10 = 1.25

y-intercept, b0 = y̅ -b1* x̅ = 7 - (1.25)*4 = 2

Standard error for slope, se(b1) = se/√SSxx = 1.31101/√10 = 0.414578099

Standard error for Intercept, se(b0) = se*√((1/n) + (x̅²/SSxx)) = 1.31101*√((1/6) + (4²/10)) = 1.742543639

Test statistic, t = b0/se(b0) = 2/1.7425 = 1.14774744

p-value = T.DIST.2T(ABS(1.1477), 4) = 0.315049921

Test statistic, t = b1/se(b1) = 1.25/0.4146 = 3.015113446

p-value = T.DIST.2T(ABS(3.0151), 4) = 0.039351852

Regression Statistics
Multiple R	0.8333333
R Square	0.6944444
Observations	6
ANOVA
	df	SS	MS	F	Significance F
Regression	1	15.6250	15.625	9.0909	0.0394
Residual	4	6.875	1.71875
Total	5	22.5
	Coefficients	Standard Error	t Stat	P-value
Intercept	2	1.74254	1.14775	0.31505
X	1.25	0.41458	3.01511	0.03935

a)

Regression equation :

ŷ = 2 + (1.25) x

b)

R² = 0.6944

69.44% of the variations in PCC’s sales is explained by the model.

Yes, it is powerful in prediction sales

c)

F = 9.0909

p-value = 0.0394.

As p-value < 0.05. we reject the null hypothesis.

The model is significant.

d)

Predicted value of y at x = 7

ŷ = 2 + (1.25) * 7 = 10.75