Question

Multiple Regression Analysis

The company has been able to determine that its sales in dollars depend on advertising and

of the number of sellers and for this reason, it uses the data from previous years to

be able to forecast possible sales for the year 2020.

Y           X1 ($ 000)   X2 ($ 000)

Year          Sales    Advertising   Salesman

2013        $ 10          $ 1                       1

2014       $ 15            $ 2                     1

2015       $ 25            $ 3                      2

2016      $ 40            $ 5 3

2017      $ 70             $6                       3

2018      $ 110         $ 8                        4

2019      $ 150          $ 9                        6

INSTRUCTIONS:

a) Calculate the values ​​of the letters a, b1, b2. (excel)

b) Write down the problem Regression equation

c) Calculate sales by 2020 if the advertising were $ 14,000 and the number of sellers out of 10.

Answer 1

We have the following data from the given problem.

Year	Sales	Advertising	Salesman
2013	10	1	1
2014	15	2	1
2015	25	3	2
2016	40	5	3
2017	70	6	3
2018	110	8	4
2019	150	9	6

a) Now, the regression equation is Y = a +b1x1+ b2x2 +u

here, Y = sales

x1 = Advertisement

x2 = Salesman

Using excel we get the following result.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.97
R Square	0.95
Adjusted R Square	0.92
Standard Error	14.55
Observations	7.00
ANOVA
	df	SS	MS	F	Significance F
Regression	2.00	16003.65	8001.82	37.82	0.00
Residual	4.00	846.35	211.59
Total	6.00	16850.00
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-23.96	11.11	-2.16	0.10	-54.80	6.89	-54.80	6.89
Advertising (A)	7.60	6.96	1.09	0.34	-11.73	26.94	-11.73	26.94
Salesmane (Sa)	16.46	11.88	1.39	0.24	-16.52	49.43	-16.52	49.43

Thus, a = -23.96

b1 = 7.60

b2 = 16.46

b) The regression equation is - Y = -23.96 + 7.60x1 + 16.46x2

c) In 2020, x1 = 14 and x2 = 10

Putting this in the above equation we get,

Y = -23.96 + 7.60 * 14 + 16.46 * 10 = 247

Thus, sales in 2020 = 247000