A national distributor of \"Eagle\" brand snacks is attempting to develop a mode

Question

A national distributor of "Eagle" brand snacks is attempting to develop a model to explain sales of their product. To do so, data have been gathered on monthly sales (measured in thousands of dollars) from its many marketing areas. From these, sixty observations have been selected at random.

The data reported on the data sheet are as follows:

     Column #1 = Monthly sales in thousands of dollars per marketing area.
                   2 = Promotional budget for the sales area, in thousands of dollars.
                    3 = Median family income in the sales area, in thousands of dollars.
                    4 = Product recognition index, proportion of respondents to a marketing survey in the market
district that recognized the Eagle brand name (reported as a decimal value).
                    5 = Average retail price of product in dollars.
                    6 = Average retail price of leading competitor brand in dollars.
                    7 = A coded variable representing the advertising method employed in the sales area.
                        There are four levels of this categorical variable labeled A, B, C, and D as follows:
                            A = Sports Magazine only
                            B = Radio Sport Show Ads
                            C = TV Sports Show Ad
                            D = TV General Advertising

Simple Regression Project

Of particular interest to the company is the effect of advertising expenditures on sales, the effect their price has on sales, and the effect of the different package designs the company has been using on sales (note: the dependent variable is not unit sales but dollar sales reported in hundreds of dollars).

You will need to adjust your Excel spreadsheet file from the Anova Project to do the following:
In Excel use the Data > Data Analysis > Regression procedure to fit a simple regression of Sales (dependent, or Y, variable) as a function of Advertising Expenditures (independent, or X, variable).

Report the estimated regression equation.

Conduct the F-test for model significance. Interpret your results.

Interpret the slope coefficient and test for significance using a t test.

a.    Use a t test to determine if the slope coefficient is significantly different
       (two-tail test) than the value 1.0 that is, test H0: b = 1 against H1: b ¹ 1.
b.    Of what importance is it to the company to know if this slope coefficient differs
       significantly from 1.0?

Be sure to include a copy of your Excel printout.
140.0 51.2 36.8 0.70 2.22 2.23 C
123.2 48.6 41.8 0.75 2.33 1.53 D
119.8 61.3 41.0 0.74 2.92 1.90 D
61.4 49.7 42.5 0.70 3.18 1.54 B
75.0 48.4 42.4 0.78 2.84 2.27 A
131.9 52.7 44.8 0.82 2.47 1.78 C
79.7 52.7 37.2 0.78 2.57 2.96 B
81.2 53.8 40.3 0.76 2.80 2.81 B
96.3 44.4 41.3 0.83 3.35 2.02 C
94.1 45.6 37.7 0.72 2.37 2.12 B
96.5 51.4 39.0 0.77 3.07 2.03 C
90.1 41.2 35.4 0.77 2.52 2.50 C
51.5 52.1 37.8 0.75 2.98 1.77 A
85.8 56.0 39.0 0.73 2.96 3.26 B
112.3 46.9 42.3 0.84 2.60 2.14 C
119.2 54.3 43.3 0.73 3.21 1.97 C
70.3 53.2 36.9 0.83 3.16 1.54 A
90.5 56.1 43.0 0.67 2.93 3.02 B
80.0 44.6 36.8 0.71 2.52 2.57 D
80.5 46.1 43.8 0.77 2.78 2.23 B
73.8 50.0 36.0 0.84 2.70 3.13 A
110.7 46.2 43.5 0.80 2.68 2.58 C
96.5 50.8 43.7 0.79 2.72 3.06 B
46.6 50.8 35.0 0.69 3.02 2.04 A
87.4 55.6 41.3 0.67 3.12 3.43 B
96.2 48.5 36.2 0.75 2.67 2.54 C
99.3 44.1 38.0 0.90 2.57 2.63 C
111.1 49.9 39.2 0.70 2.19 1.62 D
61.4 45.0 38.8 0.74 2.96 3.25 A
86.0 50.8 39.4 0.79 2.54 2.02 B
92.6 48.5 35.5 0.70 2.26 1.66 B
99.9 50.5 40.8 0.78 2.56 3.20 B
72.5 39.8 35.1 0.79 2.45 3.19 B
115.7 50.3 36.2 0.66 2.71 1.62 C
76.0 44.0 38.4 0.67 2.75 1.83 D
83.4 47.4 44.8 0.82 2.94 2.83 A
60.7 45.1 42.2 0.74 2.76 1.86 A
113.6 41.5 40.1 0.65 2.39 2.44 C
101.4 49.2 41.7 0.72 2.72 1.59 D
112.7 46.0 42.1 0.72 2.76 1.51 C
97.8 50.0 38.2 0.70 2.57 3.02 D
98.2 47.3 44.9 0.69 2.68 3.09 A
108.2 52.0 42.0 0.72 2.75 2.85 D
101.7 51.8 44.3 0.65 2.62 3.11 B
93.7 56.1 42.5 0.76 2.59 1.80 A
84.5 47.0 43.8 0.69 2.99 1.77 B
86.4 47.8 44.9 0.84 3.19 1.58 B
86.5 49.2 38.5 0.73 2.96 2.32 D
106.3 50.5 41.7 0.68 2.81 1.66 C
108.5 51.2 44.5 0.77 2.56 2.39 B
100.2 51.6 37.4 0.69 3.23 2.76 C
85.3 54.0 41.4 0.77 2.69 2.85 A
69.5 42.4 40.1 0.75 3.36 3.28 D
94.5 55.7 40.5 0.84 2.96 2.87 D
77.0 53.2 37.0 0.69 2.93 1.75 B
80.4 44.5 38.1 0.83 2.97 2.15 C
79.7 54.3 37.1 0.71 2.76 1.98 A
71.1 51.4 38.9 0.69 2.93 1.54 A
60.0 46.9 37.7 0.75 2.68 3.24 A
124.4 49.3 37.7 0.77 2.31 2.12 C

Explanation / Answer

In Excel use the Data > Data Analysis > Regression procedure to fit a simple regression of Sales (dependent, or Y, variable) as a function of Advertising Expenditures (independent, or X, variable).

and...
Column #1 = Monthly sales in thousands of dollars per marketing area.
2 = Promotional budget for the sales area, in thousands of dollars.

So, I'm going to regress y = Sales (Column #1) on x = Promotional budget; assuming promotional budget and advertising budget are the same thing.

1.Report the estimated regression equation.

yhat = b0 + b1x

Answer: Predicted sales = 54.01585319 + 0.757363679 * Promotion_Budget

----------------------

2.Conduct the F-test for model significance. Interpret your results.

HO: beta1 = 0 <-- Adv budget is NOT a good predictor of Sales
Ha: beta1 =/ 0 <-- Adv budget IS a good predictor of Sales

F = 1.579

p-value = 0.2139

Since the p-value = .0.2139 is greater than .05, we can NOT reject Ho. This implies that our model is NOT significant, i.e. Adv budget is NOT a good predictor of Sales.

----------------------

3a.Interpret the slope coefficient.

Note: The slope represents how much y is predicted to change for each 1 unit change in x.

y = Monthly sales (in thousands of dollars per marketing area)
x = Promotional budget for the sales area (in thousands of dollars)

Note: Since Promotional budget is NOT significant (see part 2 or 3b), we really shouldn't be interpreting the slope - since it is NOT significant.

Interpretation: With each additional 1,000 spent in advertising, sales are predicted to increase, on average, by .0.757363679 * $1000 = $7573.63679

----------------------

3b. Test the slope for significance using a t test.

HO: beta1 = 0 <-- Adv budget is NOT a good predictor of Sales
H1: beta1 =/ 0 <-- Adv budget IS a good predictor of Sales

t = 1.2566

p-value = .0.2139

Since the p-value = .0.2139 is greater alpha = .05, we can NOT reject Ho. This implies that Adv budget is NOT a good predictor of Sales.

Note: For Simple Linear Regression, the F-test and t-tests are identical,

i.e.
* t^2 = F, 1.2566^2 = 1.57904822
* same p-value (= .0.2139 )
* same conclusion (fail to reject Ho)

----------------------

SUMMARY OUTPUT Regression Statistics Multiple R 0.162798694 R Square 0.026503415 Adjusted R Square 0.009718991 Standard Error 19.60927834 Observations 60 ANOVA df SS MS F Significance F Regression 1 607.1816171 607.1816171 1.57904822 0.213935329 Residual 58 22302.38022 384.5237968 Total 59 22909.56183 Coefficients Standard Error t Stat P-value Lower 95% Intercept 54.01585319 29.94625203 1.803760055 0.076462983 -5.928083072 promotional_budget 0.757363679 0.602707752 1.256601854 0.213935329 -0.449086966

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.