Question

Table 8.4 The Furniture Super Mart is a furniture retailer in Evansville, Indiana. The Marketing Manager wants to prepare a media budget based on the next quarter's business plan. The manager wants to decide the mix of radio advertising and newspaper advertising needed to generate varying levels of Weekly Gross Revenue. The manager has collected data for the past five weeks, and has recorded the following average Weekly Gross Revenues and expenditures for Weekly Radio (X1) and Newspaper (X2) advertising:

WEEK	AVERAGE WEEKLY GROSS REVENUE ($000)	AVERAGE WEEKLY RADIO ADVERTISING ($000)	AVERAGE WEEKLY NEWSPAPER ADVERTISING ($000)
1	60	6	1
2	45	3	3
3	55	4	2
4	70	5	3
5	40	2	1

The Manager uses the multiple regression model in OM Explorer and obtains the following results:

Solver - Regression Analysis

R-squared 0.840

R 0.916

Constant 20.5

Standard Error of Estimate 6.755

Trial X1 Value 7 X1 Coefficient 6.500

Trial X2 Value 4 X2 Coefficient 3.750

Trial X3 Value X3 Coefficient 0.000

Trial X4 Value X4 Coefficient 0.000

Trial X5 Value X5 Coefficient 0.000

Predicted Y value 81.000

a) Use the information provided in Table 8.4. Adding $1,000 of Weekly Radio Advertising (X1) can be expected to increase Weekly Gross Revenues by what amount? (Assume all other variables are held constant.)

$20,500 $3,750 $10,250 $6,500

b) Use the information provided in Table 8.4. Adding $1,000 of Weekly Newspaper Advertising (X2) can be expected to increase Weekly Gross Revenues by what amount? (Assume all other variables are held constant.)

$6,500 $20,500 $10,250 $3,750

c) Use the information provided in Table 8.4. What amount of Weekly Gross Revenue can be expected for a week in which no radio or newspaper advertising is purchased? (Assume all other variables are held constant.)

$6,500 $20,500 $10,250 $3,750

d) Use the information provided in Table 8.4. What is the estimated Weekly Gross Revenue if $7,000 is spent on Radio Advertising (X1) and $4,000 is spent on Newspaper Advertising (X2)?

$81,000 $15,000 $60,500 $45,500

Answer 1

using excel>data>data analysis>Regression

we have

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.916467
R Square	0.839912
Adjusted R Square	0.679825
Standard Error	6.754628
Observations	5
ANOVA
	df	SS	MS	F	Significance F
Regression	2	478.75	239.375	5.246575	0.160088
Residual	2	91.25	45.625
Total	4	570
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	20.5	11.30265	1.813733	0.211393	-28.1314	69.1314
Radio Ads	6.5	2.136001	3.04307	0.093146	-2.69047	15.69047
NewsAds	3.75	3.377314	1.11035	0.38246	-10.7814	18.28141

Revenue = 20.5 +6.5 Radio Ads +3.75 News Ads

a) Adding $1,000 of Weekly Radio Advertising (X1) can be expected to increase Weekly Gross Revenues by 6500

$6,500

b) Adding $1,000 of Weekly Newspaper Advertising (X2) can be expected to increase Weekly Gross Revenues by 3750

$3,750

c) The amount of Weekly Gross Revenue can be expected for a week in which no radio or newspaper advertising is purchased

$20,500

d) the estimated Weekly Gross Revenue if $7,000 is spent on Radio Advertising (X1) and $4,000 is spent on Newspaper Advertising (X2) is

Revenue = 20.5 +6.5*7 +3.75*4 =81

$81,000