Question

х New Tab Week 3 Forecasting Sales Problem ws/kelly/Downloads/Week%203%20Forecasting%20Sales%20Problem.pdf x | + Week 3 Simple Regression Forecasting Sales Problem Homework Assignment Using the Excel file Forecasting Sales Using Ad Dollars Data use Excel to create a forecast of Sales $ using Ad $. Analyze the data using simple linear regression in Excel to answer the following questions. 1. Does more ad spending increase, decrease, or have no effect on sales $? 2. What is the regression equation? Write it and its numerical values. 3. Using the regression equation calculate predicted sales and error values and enter them into the Excel spreadsheet in the appropriate spaces.

Answer 1

Output of regression:

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.847950033
R Square	0.719019259
Adjusted R Square	0.683896667
Standard Error	67.19447214
Observations	10
ANOVA
	df	SS	MS	F	Significance F
Regression	1	92431.72	92431.72	20.4717	0.001938
Residual	8	36120.78	4515.097
Total	9	128552.5
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	828.13	136.13	6.08	0.00	514.21	1,142.04	514.21	1,142.04
X Variable 1	10.79	2.38	4.52	0.00	5.29	16.28	5.29	16.28

Part (1)

Coefficient of x variable (Ad spend) is 10.79 i.e. > 0

Hence, more ad spending will increase the sales in $.

Part (2)

Regression equation:

Y = Ax + B

Or, Sales ($) = 10.79 x Ad Spend ($) + 828.13

Part (3)

Ad	Sales	Predicted	Error
41	1250	1,270	-20
54	1380	1,411	-31
63	1425	1,508	-83
54	1425	1,411	14
48	1450	1,346	104
46	1300	1,324	-24
62	1400	1,497	-97
61	1510	1,486	24
64	1575	1,518	57
71	1650	1,594	56