Question

Write the reduce the form of the regression line after removing insignificant variables ( using excel)

Sales Y	Price X	Adver X
19.98	14.10	15.19
19.98	18.65	15.19
19.98	18.65	13.87
19.98	18.65	12.83
19.98	18.65	13.16
19.98	18.65	15.19
19.98	18.65	13.92
20.10	18.73	14.42
20.12	18.75	13.83
20.13	18.75	14.50
20.14	18.75	13.87
20.12	18.75	13.64
20.12	13.87	14.31
20.13	14.27	13.85
20.14	18.76	14.20
20.14	18.77	13.64
20.13	13.87	14.33
20.13	14.14	13.14
20.13	18.76	13.81
20.13	18.72	15.19
20.13	18.76	13.13
19.18	18.76	13.63
14.78	18.74	15.19
16.04	18.75	13.89
20.12	18.75	14.28
19.75	18.75	15.19
19.65	18.75	13.12
19.69	13.79	13.78
20.12	13.49	15.19
20.12	14.89	15.19
20.13	13.94	15.19
20.14	13.67	15.19
15.14	14.43	15.19
14.33	18.75	15.19
16.24	18.22	13.14
19.93	14.06	13.45
21.06	14.43	13.00
21.19	19.48	13.60
21.23	15.15	14.46
20.12	13.79	14.94
14.73	14.31	15.19
14.57	19.50	15.19
15.94	13.85	15.19
20.70	14.23	13.43
19.57	19.31	14.37
19.60	19.29	15.19
19.94	13.76	15.19
21.28	13.45	15.19
14.56	15.13	15.19
14.39	19.43	15.19
16.81	13.26	15.19
19.86	13.92	15.19

Answer 1

following regression analysis information has been generated with both Price and Advt. as independent variables.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.406031585
R Square	0.164861648
Adjusted R Square	0.130774368
Standard Error	1.946808789
Observations	52
ANOVA
	df	SS	MS	F	Significance F
Regression	2	36.6609644	18.3304822	4.836456577	0.012108
Residual	49	185.7131587	3.790064463
Total	51	222.3741231
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	37.09107208	5.838796863	6.352519697	6.68162E-08	25.35757	48.82457
Price X	-0.161810671	0.116819143	-1.385138321	0.172284106	-0.39657	0.072946
Adver X	-1.063837057	0.349543943	-3.043500186	0.003753794	-1.76627	-0.3614

it is found that price is not significant as its p-value =0.1723 is more than typical level of significance alpha=0.05.

after removing/deleting price from the model, again regression analysis information has been generated only using Advt as independent variable .

the final model is Sales_Y=32.634-0.942*Adver_X

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.36354
R Square	0.132162
Adjusted R Square	0.114805
Standard Error	1.964611
Observations	52
ANOVA
	df	SS	MS	F	Significance F
Regression	1	29.38932	29.38932	7.614412	0.00807
Residual	50	192.9848	3.859696
Total	51	222.3741
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	32.63404	4.916678	6.637417	2.22E-08	22.7586	42.50948	22.7586	42.50948
Adver X	-0.94208	0.341404	-2.75942	0.00807	-1.62781	-0.25635	-1.62781	-0.25635