Question

An electronics company is looking to develop a regression model to predict the number of units sold for a special running watch. Data is provided below: Sales (units) Price ($) Advertising ($) Holiday 500 100 50 Yes 480 120 40 Yes 485 110 45 No 510 103 55 Yes 490 108 40 No 488 109 30 No 496 106 45 Yes Compile an excel spreadsheet for the above data and determine the regression equation Answer (a) X3 = 1 if it is a holiday and 0 if not. (b) 0.4670

Answers:

Y = 596.02 - 1.09X1 +0.28X2 + 4.34X3 where X1 = Price, X2 = Advertising, X3 = Dummy Variable for Holiday

Y = 596.02 + 1.09X1 +0.28X2 + 4.34X3 where X1 = Price, X2 = Advertising, X3 = Dummy Variable for Holiday

Y = 59.62 - 1.09X1 +0.28X2 + 4.34X3 where X1 = Price, X2 = Advertising, X3 = Dummy Variable for Holiday

596.02 - 0.28X1 + 1.09X2 + 4.34X3 where X1 = Price, X2 = Advertising, X3 = Dummy Variable for Holiday

Answer 1

a) Use regression in excel as shown below. first consider both price and advertising and then consider each alone

The summary of three models are as follows

1. When both price and advertising are considered, R-square = 0.81
2. Only price, R-square = 0.7
3. Only advertising, R-square = 0.53

Since the model 1 has the highest R-square value, it has the highest co-relation and predicts better than the other two models

Following is the output of model 1

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.90437157
R Square	0.817887937
Adjusted R Square	0.726831906
Standard Error	5.284712442
Observations	7
ANOVA
	df
Regression	2
Residual	4
Total	6
	Coefficients
Intercept	578.865608
Price	-0.998865662
Advertising	0.498633393

b) the regression equation from the coefficients is

Sales = 578 – 0.998 * Price +0.498*Advertising

• if price is set at $125 and advertising at $55, sales is given by 578 - 0.998*125 + 0.498*55
• Sales = $481

c) output of model 2

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.840921
R Square	0.707149
Adjusted R Square	0.648578
Standard Error	5.994055
Observations	7
ANOVA
	df
Regression	1
Residual	5
Total	6
	Coefficients
Intercept	637.3093
Price	-1.33884

Regression equation is Sales = 637-1.3*Price

Sales = 469 at price of $125

Output of model 3

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.734221515
R Square	0.539081233
Adjusted R Square	0.44689748
Standard Error	7.519850274
Observations	7
ANOVA
	df
Regression	1
Residual	5
Total	6
	Coefficients
Intercept	452.3703704
Advertising	0.925925926

Regression equation is Sales = 452.3 +0.92* Advertising

Sales = 503 at Advertising of $55

Summary

Model	Prediction
Price X Advertising	481
Price	469
Advertising	503

d) Forecast in (b) is more accurate because the model 1 has higher correlation between dependent (sales) and independent (PriceXAdveritising) variables