Question

Mark Price, the new productions manager for Speakers and Company, needs to find out which variable most affects the demand for their line of stereo speakers. He is uncertain whether the unit price of the product or the effects of increased marketing are the main drivers in sales and wants to use regression analysis to figure out which factor drives more demand for its particular market. Pertinent information was collected by an extensive marketing project that lasted over the past 12 years and was reduced to the data that follow:

YEAR	UNIT SALES (THOUSANDS)	PRICE $/UNIT	ADVERTISING ($000)
1	388	289	580
2	688	217	826
3	888	209	1,114
4	1,310	215	1,410
5	1,153	217	1,212
6	1,189	206	1,310
7	888	215	904
8	1,114	203	1,114
9	983	217	705
10	1,331	209	904
11	923	222	705
12	817	239	685

a. Perform a regression analysis based on these data using Excel. (Negative values should be indicated by a minus sign. Round your answers to 4 decimal places.)

y¯y¯ =  +  Price +  Advertising

c. Predict average yearly speaker sales for Speakers and Company based on the regression results if the price was $288 per unit and the amount spent on advertising (in thousands) was $888. (Enter your answer in thousands. Do not round intermediate calculations. Round your answer to 2 decimal places.)

Forecasted sales             thousand units

Answer 1

to I Refresh z l Sort Filterp Stocks Text to Data ▼ AlEdit Links Regression Get & Transform Data Queries & Connections Input OK Input Y Range: SBS2:SBS13 CS2 SDS131 Constant is Zero Cancel Input X Range: UNIT PRICE Help YEAR |ADVERTISING! Labels SALES | S/ UNIT 289 217 209 215 217 Confidence Level: 95 % 388 688 888 I 1,310 1,153 i 1,189 826 1,114 1,410 1,212 1,310 904 1,114 705 904 705 IOutput options O Qutput Range: O New Worksheet Ply: O New Workbook Residuals 206 888 I 1,114 983 1,331 923I 817 Residuals 215 203 217 209 Residual Plots □ Line Fit Plots □ Standardized Residuals 10 Normal Probability 10 12 11 13 12 Normal Probability Plots 16

M11 1 SUMMARY OUTPUT 3 Rearession Statistics 4 Multiple F 0.829069 5 R Square 0.687355 6 Adjusted I 0.617878 7 Standard 167.2779 12 8 Observati 10 ANOVA MS gnificance F 12 Regressio 13 Residual 14 Total 2 553667.5 276833.8 9.893313 0.005342 9 251837.2 27981.91 11 805504.7 Coefficientandard Err t stat P-value Lower 95%Up er 95%ower 95.0 p er 95.0% 17 Intercept 1945.974765.789 2.541136 0.031652 213.6387 3678.309 213.6387 3678.309 18x variable -6.11255 2.741964-2.22926 0.052761-12.3153 0.090205-12.3153 0.090205 19 X Variable 0.398245 0.234072 1.7013770.123082 -0.13126 0.927753-0.13126 0.927753 21

The equation is

Y = 1945.974 - 6.1126 * Price + 0.3982 * Advertising

c)

Price = 288

Advertising = 888

Forecasted Sales = 1945.974 - 6.1126 * 288 + 0.3982 * 888 = 539.15 thousands