Question

Populate an excel sheet with the information, below. After you have installed the Analysis ToolPak, open “Data Analysis” and scroll down to “Regression” and click. The regression window will open. Run a regression analysis on the annual car sales, over the last three years, taken from William and Sam Automotive sales. Year Car Sales 2015 200 2016 250 2017 270.

Answer 1

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.970725343
R Square	0.942307692
Adjusted R Square	0.884615385
Standard Error	12.24744871
Observations	3
ANOVA
	df	SS	MS	F	Significance F
Regression	1	2450	2450	16.33333333	0.154420958
Residual	1	150	150
Total	2	2600
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-70320	17459.07357	-4.027705119	0.154927474	-292158.5633	151518.5633	-292158.5633	151518.5633
Year	35	8.660254038	4.041451884	0.154420958	-75.03896087	145.0389609	-75.03896087	145.0389609

Car sales = -70320 + 35 * year

If you consider year 2015 =1 and year 2016=2 and 2017 =3

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.970725343
R Square	0.942307692
Adjusted R Square	0.884615385
Standard Error	12.24744871
Observations	3
ANOVA
	df	SS	MS	F	Significance F
Regression	1	2450	2450	16.33333333	0.154420958
Residual	1	150	150
Total	2	2600
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	170	18.70828693	9.086882225	0.069778423	-67.71132404	407.711324	-67.71132404	407.711324
Year	35	8.660254038	4.041451884	0.154420958	-75.03896087	145.0389609	-75.03896087	145.0389609

Car sales = 170 + 35 * year