Question

1. Use “PHXMayoData.xlsx” to answer the following questions: How about the optimized price when not considering the substitution between BF and KR compared to the result including the substitution effects (you can find the optimal prices with substitution effects in the video)? Give an intuitive explanation (you can write the answer to it in the Excel file). Hint: following the same steps but using the following models:
   • Model1: Sales_BF = a + b1* Price_BF + Error
   • Model2: Sales_KR = a + b1* Price_KR + Error

Week	Sales_BF	Sales_KR	Price_BF	Price_KR
1	455	135	1.61	1.02
2	530	63	1.34	1.29
3	527	41	1.38	1.63
4	418	71	1.44	1.53
5	380	34	1.62	1.71
6	267	57	1.67	1.59
7	247	56	1.69	1.59
8	297	72	1.66	1.62
9	303	92	1.65	1.42
10	237	168	1.69	1.32
11	275	85	1.66	1.41
12	426	58	1.38	1.51
13	480	120	1.39	1.60
14	289	153	1.42	1.17
15	366	177	1.43	1.07
16	426	56	1.40	1.13
17	587	106	1.23	1.43
18	277	93	1.29	1.28
19	333	90	1.66	1.29
20	345	38	1.38	1.21
21	375	75	1.45	1.48
22	364	60	1.61	1.50
23	263	72	1.63	1.35
24	225	229	1.69	1.16
25	324	218	1.65	1.08
26	471	43	1.59	1.44
27	516	34	1.57	1.54
28	682	34	1.06	1.58
29	579	24	1.12	1.72
30	403	37	1.29	1.44
31	446	33	1.58	1.63
32	383	52	1.58	1.62
33	376	48	1.59	1.64
34	453	39	1.61	1.59
35	414	34	1.58	1.60
36	481	33	1.58	1.50
37	440	28	1.32	1.23
38	400	54	1.39	1.34
39	406	211	1.58	1.26
40	376	54	1.57	1.33
41	381	46	1.37	1.31
42	293	52	1.49	1.26
43	398	76	1.60	1.35
44	336	62	1.61	1.36
45	330	46	1.64	1.39
46	354	64	1.58	1.16
47	564	62	1.38	1.19
48	434	72	1.41	1.22
49	422	42	1.36	1.38
50	374	41	1.39	1.41
51	362	68	1.39	1.36
52	334	113	1.47	1.29
53	253	106	1.49	1.33
54	312	89	1.46	1.21
55	350	69	1.41	1.21
56	430	81	1.41	1.23
57	331	68	1.44	1.33
58	240	80	1.61	1.35
59	248	63	1.75	1.35
60	338	90	1.73	1.40
61	328	54	1.75	1.46
62	254	33	1.77	1.79
63	240	51	1.79	1.52
64	282	81	1.75	1.53
65	329	55	1.75	1.58
66	288	66	1.77	1.44
67	257	50	1.76	1.48
68	267	37	1.74	1.57
69	353	31	1.75	1.83
70	350	35	1.72	1.89
71	304	45	1.74	1.86
72	237	27	1.76	1.98
73	365	41	1.74	1.85
74	321	57	1.76	1.80
75	291	114	1.74	1.25
76	257	166	1.78	1.11
77	303	102	1.80	1.24
78	327	71	1.73	1.38
79	294	62	1.78	1.65
80	279	72	1.76	1.52
81	239	62	1.76	1.40
82	310	58	1.77	1.54
83	312	60	1.78	1.52
84	368	58	1.73	1.53
85	419	66	1.42	1.46
86	269	172	1.79	1.14
87	349	120	1.77	1.26
88	250	54	1.80	1.61
89	260	77	1.77	1.67
90	243	69	1.81	1.50
91	285	91	1.75	1.53
92	284	65	1.79	1.48
93	312	94	1.49	1.44
94	223	143	1.84	1.15
95	301	131	1.80	1.22
96	250	91	1.79	1.53
97	298	87	1.76	1.39
98	351	114	1.47	1.05
99	385	137	1.79	1.17
100	241	57	1.78	1.41
101	249	69	1.84	1.45
102	242	59	1.86	1.58
103	230	55	1.87	1.73
104	277	75	1.84	1.53
105	209	66	1.82	1.55
106	202	69	1.82	1.50
107	260	46	1.76	1.42
108	386	84	1.44	1.49
109	350	105	1.43	1.18
110	254	84	1.75	1.69
111	277	67	1.74	1.74
112	240	151	1.78	1.27
113	251	128	1.74	1.37
114	273	91	1.77	1.71
115	264	59	1.77	1.74
116	228	43	1.77	1.76
117	291	36	1.76	1.82
118	301	41	1.76	1.85
119	246	82	1.77	1.39
120	267	39	1.73	1.36
121	306	44	1.75	1.74
122	310	54	1.72	1.80
123	250	55	1.74	1.74
124	326	53	1.67	1.44
125	286	65	1.74	1.44
126	350	34	1.71	1.88
127	338	72	1.70	1.68
128	290	150	1.76	1.11
129	381	114	1.70	1.04
130	530	28	1.38	1.99
131	369	65	1.67	1.71
132	256	87	1.74	1.39
133	228	85	1.77	1.32
134	330	47	1.69	1.78
135	319	87	1.72	1.71
136	358	133	1.67	1.09
137	467	97	1.39	1.10
138	354	53	1.71	1.76
139	482	65	1.68	1.75
140	370	52	1.68	1.69
141	356	50	1.69	1.72
142	369	31	1.67	1.72
143	441	39	1.70	1.74
144	527	44	1.34	1.70
145	421	73	1.38	1.65
146	313	115	1.72	1.06
147	326	88	1.71	1.12
148	332	34	1.72	1.74
149	269	79	1.74	1.69
150	416	99	1.66	1.09
151	516	84	1.40	1.16
152	451	56	1.57	1.71
153	415	51	1.69	1.74
154	349	35	1.71	1.84
155	396	52	1.70	1.72
156	373	43	1.73	1.76
157	324	58	1.43	1.53
158	647	91	1.66	1.36
159	505	69	1.71	1.37
160	363	49	1.67	1.72
161	370	31	1.69	1.71
162	569	49	1.39	1.75
163	523	37	1.36	1.73
164	489	48	1.67	1.44
165	370	95	1.70	1.35
166	395	162	1.70	1.28
167	387	141	1.71	1.33
168	349	82	1.73	1.40
169	330	33	1.69	1.74
170	361	44	1.71	1.69
171	425	49	1.72	1.89
172	364	46	1.69	1.37
173	509	80	1.68	1.35

Answer 1

for model 1

using excel>data>data analysis >regression

we have

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.65923
R Square	0.434584
Adjusted R Square	0.431277
Standard Error	69.24122
Observations	173
ANOVA
	df	SS	MS	F	Significance F
Regression	1	630130.6	630130.6	131.432	6.17E-23
Residual	171	819833.3	4794.347
Total	172	1449964
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	963.778	53.79702	17.91508	4.35E-41	857.5862	1069.97
Price_BF	-375.918	32.7901	-11.4644	6.17E-23	-440.644	-311.193

Model1: Sales_BF = 963.778 -375.918* Price_BF + Error

for every one unit increase in the Price_BF there is corresponding increase in the sales_BF .

For model 2

using excel>data>data analysis >regression

we have

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.644487
R Square	0.415364
Adjusted R Square	0.411945
Standard Error	29.36262
Observations	173
ANOVA
	df	SS	MS	F	Significance F
Regression	1	104743.8	104743.8	121.4895	1.1E-21
Residual	171	147429.9	862.1632
Total	172	252173.7
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	233.8303	14.75087	15.85197	2.13E-35	204.7131	262.9475
Price_KR	-108.553	9.84854	-11.0222	1.1E-21	-127.993	-89.1125

Model1: Sales_KR= 233.8303 -108.553* Price_KR + Error

for every one-unit increase in the Price_KR, there is a corresponding increase in the sales_KR.

both model shows that if price will increase tha there is corresponding decrease in sales