Question

1. On top of the its own price effect on sales, Mr. Wok is also interested how the sales will change as Competitor’s price changes.

Please use data in “Linear regressions with competitor price.xsls”

• Estimate the coefficients with Mr. Work’s own price and its competitor
• How do the two prices affect the Mr. Works’s sales?

Data
Market	Sales	Price
1	167	2.5
2	182	2.5
3	167	2.7
4	147	2.8
5	167	2.3
6	195	2.2
7	175	2.7
8	198	2.0
9	147	2.8
10	187	2.6
11	181	2.7
12	221	1.9

Market ID	Sales	Price	Competitor Price (Cprice)
1	228	2.2	2.2
2	216	2.7	2.9
3	223	2.4	2.4
4	207	2.9	2.6
5	216	2.8	2.4
6	247	2.2	2.5
7	233	2.0	2.2
8	249	2.3	2.7
9	239	2.1	2.4
10	209	2.7	2.4
11	214	2.8	2.4
12	236	2.6	3.0
13	218	2.6	2.1
14	191	2.9	2.2
15	223	2.6	3.0

Answer 1

In order to solve this question I used Excel.

Step.1 Enter data in excel sheet.

Step.2 Go to 'Data' menu ---> 'Data Analysis' ---> Select 'Regression'.

Step.3 New window will pop-up on screen. Refer following screen shot and enter information accordingly.

Excel output:

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.899159
R Square	0.808488
Adjusted R Square	0.776569
Standard Error	7.453419
Observations	15
ANOVA
	df	SS	MS	F	Significance F
Regression	2	2814.292	1407.146	25.32959	4.93E-05
Residual	12	666.6414	55.55345
Total	14	3480.933
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	284.8645	22.03959	12.92513	2.11E-08	236.8444	332.8846
Price	-46.6048	6.807572	-6.84603	1.78E-05	-61.4372	-31.7724
Competitor Price (Cprice)	22.39825	6.962266	3.217092	0.007394	7.228775	37.56772

Estimated regression equation is,

Sales = 284.8645 - 46.6048 Price + 22.39825 Competitor price.

It means if Mr. Work's price is increased by 1 unit then his sale decreased by 46.6048 units.

And if  Competitor price increased by 1 unit then Mr. Work's sale increased by 22.39825 units.