Question

You are an owner of Carrefour supermarket. You have made feature advertisings for last three years. You want to know the effectiveness of this feature advertising on store traffic(numbers of shoppers) in different week. In data set, you have: average numbers of shoppers, average numbers of feature advertising, and average price each week.

With the tables bellow it was done a REGRESSION model in Excel and you should interpret the results obtained from the equation based on the questions.

Q1. Consider a regression model (Model I) that has feature advertising as a single independent variable with intercept. Estimate your model and interpret your estimation results.

Regression Statistics
Multiple R	0,53969388
R Square	0,29126948
Adjusted R Square	0,28666733
Standard Error	205,827509
Observations	156
ANOVA
	df	SS	MS	F	Significance F
Regression	1	2681275,289	2681275,3	63,28992304	3,59619E-13
Residual	154	6524204,403	42364,964
Total	155	9205479,692
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95,0%	Upper 95,0%
Intercept	609,046483	19,25202773	31,635446	2,91178E-69	571,0143324	647,0786342	571,0143324	647,0786342
feature	9,48205612	1,191887424	7,9554964	3,59619E-13	7,127496744	11,83661549	7,127496744	11,83661549

Q2. Update above regression model (Model II) by adding an additional independent variable. average price in order to capture the effect of price promotion activities such as coupon during week. Estimate your model and interpret your estimation results. Do you think which model makes more sense between Model I and Model II? Why?

Regression Statistics
Multiple R	0,547853773
R Square	0,300143756
Adjusted R Square	0,290995309
Standard Error	205,202155
Observations	156
ANOVA
	df	SS	MS	F	Significance F
Regression	2	2762967,255	1381483,628	32,80816251	1,39052E-12
Residual	153	6442512,437	42107,92443
Total	155	9205479,692
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95,0%	Upper 95,0%
Intercept	960,3924697	252,9768793	3,796364602	0,000211191	460,6137971	1460,171142	460,6137971	1460,171142
feature	7,806303253	1,690984733	4,616424443	8,21304E-06	4,465610192	11,14699631	4,465610192	11,14699631
price	-65,10617381	46,74276697	-1,392860929	0,165682458	-157,4507315	27,2383839	-157,4507315	27,2383839

year	month	week_id	shoppers	feature	price
2001	200101	1	673	0	5,283581
2001	200101	2	225	2,75	5,485372
2001	200101	3	614	1,5	5,458567
2001	200101	4	537	41	4,496669
2001	200102	5	592	0	5,133277
2001	200102	6	984	11,75	4,792256
2001	200102	7	946	0	5,236702
2001	200102	8	830	0	5,391892
2001	200103	9	774	2	5,478373
2001	200103	10	1102	2	5,336039
2001	200103	11	605	2	5,456661
2001	200103	12	677	6	5,450694
2001	200104	13	509	0	5,633399
2001	200104	14	758	23,5	4,486229
2001	200104	15	888	22	4,546779
2001	200104	16	616	3	5,220925
2001	200104	17	952	33	4,702368
2001	200105	18	708	0	5,452373
2001	200105	19	701	0	5,387118
2001	200105	20	730	0	5,349753
2001	200105	21	708	0	5,462288
2001	200106	22	792	0	5,401755
2001	200106	23	345	18	4,612751
2001	200106	24	1109	18	4,358693
2001	200106	25	726	15,75	5,033526
2001	200107	26	687	19,5	5,280568
2001	200107	27	687	17,25	5,259636
2001	200107	28	584	0	5,463938
2001	200107	29	571	1	5,530473
2001	200107	30	689	6	5,546923
2001	200108	31	775	2	5,494922
2001	200108	32	556	2,5	5,451241
2001	200108	33	815	19,5	5,083509
2001	200108	34	720	33	4,340006
2001	200109	35	789	0	5,632332
2001	200109	36	659	2,25	5,235402
2001	200109	37	624	2	5,658558
2001	200109	38	595	0	5,615277
2001	200109	39	675	0	5,497289
2001	200110	40	921	21,25	4,682004
2001	200110	41	677	0	5,560798
2001	200110	42	954	33	4,933386
2001	200110	43	768	0	5,616354
2001	200111	44	667	0	5,613973
2001	200111	45	670	0	5,715224
2001	200111	46	858	1,5	5,730711
2001	200111	47	976	19	5,032326
2001	200112	48	733	4,5	5,676139
2001	200112	49	581	2,25	5,690723
2001	200112	50	603	0	5,675589
2001	200112	51	794	0	5,562544
2001	200112	52	1450	27	4,608759
2002	200201	53	654	0	5,770627
2002	200201	54	619	1,5	5,580953
2002	200201	55	703	0	5,646799
2002	200201	56	888	33	4,745466
2002	200202	57	691	0	5,723213
2002	200202	58	625	0	5,720224
2002	200202	59	485	1,5	5,879711
2002	200202	60	549	0	5,846466
2002	200203	61	606	0	5,921452
2002	200203	62	1017	18	4,716539
2002	200203	63	534	0	5,608539
2002	200203	64	467	0	5,876981
2002	200203	65	538	0	5,443104
2002	200204	66	201	0	5,789117
2002	200204	67	492	0	5,638577
2002	200204	68	1120	54	4,25556
2002	200204	69	666	2,5	5,491502
2002	200205	70	577	2	5,725875
2002	200205	71	565	3,75	5,654425
2002	200205	72	606	1,5	5,560462
2002	200205	73	700	17,5	5,266714
2002	200206	74	564	0	5,633777
2002	200206	75	1250	55,5	4,233248
2002	200206	76	727	0	5,382765
2002	200206	77	587	0	5,425247
2002	200206	78	532	0	5,626429
2002	200207	79	523	3	5,600956
2002	200207	80	566	0	5,584081
2002	200207	81	2210	10,5	5,629781
2002	200207	82	493	0	5,655822
2002	200208	83	897	0	5,555638
2002	200208	84	498	0	5,393614
2002	200208	85	534	0	5,545618
2002	200208	86	587	14,25	5,562061
2002	200209	87	1352	38,25	4,343809
2002	200209	88	654	0	5,12393
2002	200209	89	715	0	5,138755
2002	200209	90	422	0	5,746588
2002	200209	91	442	0	5,68457
2002	200210	92	485	0	5,685258
2002	200210	93	815	18,25	4,631718
2002	200210	94	580	0	5,768603
2002	200210	95	550	2,25	5,737655
2002	200211	96	546	1,5	5,725092
2002	200211	97	497	2,25	5,572455
2002	200211	98	853	21	4,699285
2002	200211	99	1049	0	4,516387
2002	200212	100	1003	0	4,465075
2002	200212	101	535	0	5,603925
2002	200212	102	831	18	4,799904
2002	200212	103	967	30	4,534664
2002	200212	104	116	0	4,716788
2003	200301	105	475	0	5,841979
2003	200301	106	253	0	5,973202
2003	200301	107	384	0	5,863129
2003	200301	108	785	27	4,649205
2003	200302	109	521	0	5,647313
2003	200302	110	507	0	5,665049
2003	200302	111	524	0	5,634733
2003	200302	112	1012	63,75	4,11654
2003	200303	113	903	0	4,424862
2003	200303	114	627	0	5,000606
2003	200303	115	454	0	5,729229
2003	200303	116	454	0	5,627445
2003	200303	117	996	48,5	4,306096
2003	200304	118	1120	33	4,346304
2003	200304	119	526	0	5,048175
2003	200304	120	529	0	4,975217
2003	200304	121	635	0	4,986016
2003	200305	122	641	0	4,948768
2003	200305	123	782	34,25	4,508325
2003	200305	124	620	7,5	4,796823
2003	200305	125	542	0	4,881494
2003	200306	126	984	36,75	4,448232
2003	200306	127	718	0	4,490682
2003	200306	128	592	0	4,957027
2003	200306	129	500	0	4,9821
2003	200306	130	856	26,25	4,445394
2003	200307	131	559	0	5,008426
2003	200307	132	365	0	4,886243
2003	200307	133	572	0	4,797745
2003	200307	134	745	40,5	3,891976
2003	200308	135	785	30	3,937432
2003	200308	136	522	0	4,773736
2003	200308	137	658	0	4,868519
2003	200308	138	514	0	4,857374
2003	200308	139	540	0	4,897981
2003	200309	140	985	28,5	4,239318
2003	200309	141	522	0	4,667165
2003	200309	142	515	0	4,707379
2003	200309	143	975	33,75	4,319703
2003	200310	144	582	3	5,138866
2003	200310	145	223	4	5,417828
2003	200310	146	575	1,75	5,136574
2003	200310	147	965	37,5	4,336062
2003	200311	148	659	6	4,778194
2003	200311	149	634	0	4,776562
2003	200311	150	733	30	4,632115
2003	200311	151	716	0	4,749567
2003	200311	152	542	0	5,604184
2003	200312	153	524	0	5,545878
2003	200312	154	801	20	4,696554
2003	200312	155	702	25	5,194544
2003	200312	156	649	0	5,14775

Answer 1

You are the owner of Carrefour supermarket.You have made feature advertising for last three years.you want to know the effectiveness of this feature advertising on store traffic(number of shoppers) in different weeks.
Let, Response variable y is average number of shoppers and independent variables are average number of feature advertising and average price each week.
Q1)
In question 1 Response variable y is average number of shoppers and independent variables are average number of feature advertising(x1).
The estimated regression model is,
Average number of shoppers(y^) = 609.046483 + 9.48205612 * feature advertising(x1)

Interpritation of Slope - If the average number of feature advertising(x1) increase by 1 unit then we predict that average number of shoppers will increase by approximately 9.48205612 number of shoppers.

Interpritation of Intercept - If the average number of feature advertising(x1) is 0 then we predict average number of shoppers are 609.046483

Interpritation - Average number of shoppers(y^) are linearly depend on the intercept(609.046483) of the model and slope(9.48205612) that is for any average number of feature advertising(x1) we predict Average number of shoppers using this model.

Q2)
In question 2 response variable y is average number of shoppers and independent variables are average number of feature advertising(x1) and average price each week(x2)
The updated estimated regression model is,
Average number of shoppers(y^) = 960.3924697 + 7.806303253 * feature advertising(x1) - 65.10617381 * average price each week(x2)

Interpritation of Slope - If the average number of feature advertising(x1) increase by 1 unit and average price each week(x2)is keeping constant then we predict that average number of shoppers will increase by approximately 7.806303253 number of shoppers and if the average number of feature advertising(x1) is keeps constant and average price each week(x2)is increases by one unit then we predict that average number of shoppers will decreases by approximately 65.10617381 number of shoppers.

Interpritation of Intercept - If the average number of feature advertising(x1) is 0 and average price each week(x2) is 0 then we predict average number of shoppers are 960.3924697

Interpritation - Average number of shoppers(y^) are linearly depend on the intercept(960.3924697) of the model and slope1(7.806303253) and slope2(- 65.10617381) that is for any average number of feature advertising(x1) and average price each week(x2) we predict Average number of shoppers using this model.

R Square for the model 1 is 0.29126948 and R Square for the model 2 is 0.300143756. R square for model 2 is quite larger than model 1 therefore Model II makes slightly more sense as compare to model I .