Question

One late Friday afternoon your obnoxious boss comes into your office as you are about to leave, and shows you 26 observations that he believes to be related. Y, he believes, is the dependent variable and X1 is the independent variable. He also thinks there is a 2nd order polynomial relationship in the data ( Y = B1X1 +B2X12 + B0 ), and, as you casually view the data, you tend to agree. He insists that the determination of B1, B2 and B0 is far more important than your Friday afternoon gathering of young-urban-millennial-professionals (YUMPS) at a local watering hole. So, you perform the analysis using the scatter diagram and Trendline tool in Excel. Then you quietly exit for the YUMPS gathering.

Now, it is Monday morning. You want to use the assumption of a 2nd order polynomial to find the exact values of B1, B2 and B0 . (Hint: generate a new variable that fits the assumed polynomial model and then use the regression tool in Excel)

a) Use a regression tool in Excel to determine the exact values of B1, B2 and B0 .

b) Is the regression model significant? Use alpha 0.05.

Data:

X1	X2	Y
68,067	4,633,116,489	1,598,278,294
70,103	4,914,430,609	1,695,432,796
76,370	5,832,376,900	2,011,698,722
86,686	7,514,462,596	2,592,259,645
86,759	7,527,124,081	2,597,646,596
91,805	8,428,158,025	2,907,666,308
92,306	8,520,397,636	2,940,058,192
93,731	8,785,500,361	3,030,806,599
100,913	10,183,433,569	3,512,923,429
102,199	10,444,635,601	3,603,809,834
109,399	11,968,141,201	4,129,345,241
113,430	12,866,364,900	4,439,623,027
118,133	13,955,405,689	4,814,663,900
122,820	15,084,752,400	5,203,806,895
123,417	15,231,755,889	5,255,000,229
123,054	15,142,286,916	5,224,742,756
127,860	16,348,179,600	5,640,704,277
132,868	17,653,905,424	6,091,148,092
131,160	17,202,945,600	5,935,115,092
132,132	17,458,865,424	6,022,925,591
132,583	17,578,251,889	6,064,201,852
136,859	18,730,385,881	6,462,049,499
140,562	19,757,675,844	6,816,147,332
144,594	20,907,424,836	7,212,915,055
141,493	20,020,269,049	6,906,705,120
139,465	19,450,486,225	6,710,038,170

Answer 1

Data > Data Analysis > Regression.

Output using excel:

SUMMARY OUTPUT
Regression Statistics
Multiple R	1
R Square	1
Adjusted R Square	1
Standard Error	377471.72
Observations	26
ANOVA
	df	SS	MS	F	Significance F
Regression	2	7.816E+19	3.908E+19	274257835	1.4428E-85
Residual	23	3.277E+12	1.425E+11
Total	25	7.816E+19
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	-3667666.87653	1821330.2	-2.0137298	0.0558816	-7435375.5	100041.8
X1	73.380775	34.876768	2.1040016	0.0465141	1.23268486	145.5289
X2	0.3446558	0.0001609	2142.2548	1.846E-62	0.34432302	0.344989

a) B1 = 73.3808

B2 = 0.3447

B0 = -3667666.8765

b) F test statistic using excel:

F = 274257835

p-value = 0.0000

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

And conclude that the regression model is significant.