Question

Using Excel fit a simple regression of Sales as a function of Promotional budget (advertising expenditures).

1. Report the estimated regression equation.

2. Conduct the F-test for model significance. Be sure to state the null and alternative hypotheses, report the value of the test statistic, report the critical value or p-level of the test, draw your conclusion, and interpret your results.

3. In one sentence interpret the value of the slope coefficient in your model.

4. Test the slope coefficient to see if it is significantly different than the value 1.0, that is, test H₀: b = 1 against H₁: b ¹ 1. Of what importance is it to the company to know if this slope coefficient differs significantly from 1.0?

ANOVA and Regression Projects

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 H₀: β = 1 against H₁: β ≠ 1.

b. Of what importance is it to the company to know if this slope coefficient differs significantly from 1.0?

5. Be sure to include a copy of your Excel printout.

63.6  44.4  40.6  0.73  2.90  2.76    D

75.8  53.8  36.0  0.76  2.68  2.37    B

    88.7  54.5  38.7  0.77  2.94  1.96    D

    79.3  44.8  37.1  0.77  2.41  1.96    A

   101.5  59.0  42.4  0.75  2.42  2.78    D

   119.0  46.7  39.6  0.68  2.33  2.52    C

    76.6  46.3  39.2  0.76  2.81  1.61    D

   114.5  50.7  44.7  0.78  3.01  2.36    C

   103.1  55.3  38.3  0.66  2.86  2.05    C

   108.3  48.0  43.1  0.77  2.63  2.18    D

    83.9  53.7  38.1  0.70  2.94  2.92    D

   126.2  49.9  39.8  0.85  2.57  2.37    C

   101.7  50.8  40.8  0.68  2.55  1.90    D

    76.0  52.1  37.1  0.66  2.81  1.88    A

    84.8  43.3  43.6  0.72  2.39  3.38    A

    52.6  53.8  38.5  0.86  3.32  1.96    A

    70.5  51.0  38.1  0.70  2.81  3.37    B

    78.9  47.7  39.6  0.78  3.11  1.96    B

    69.8  54.7  37.7  0.76  2.87  2.53    A

    78.3  54.4  35.0  0.80  2.61  2.71    B

    77.0  40.7  36.1  0.78  2.93  2.34    C

    45.8  49.5  36.1  0.74  3.04  1.77    A

    71.3  43.8  39.6  0.80  2.78  2.22    B

    96.2  40.1  42.1  0.79  2.81  2.38    C

    90.0  49.9  39.9  0.81  3.37  2.95    C

    73.3  52.3  42.7  0.68  3.31  2.26    B

    83.9  50.0  42.2  0.72  2.76  2.72    A

    81.0  49.9  36.2  0.71  2.51  1.56    D

    83.9  52.8  36.8  0.82  2.82  2.30    B

   147.5  54.3  43.3  0.64  2.00  2.36    C

   124.5  62.1  42.5  0.72  2.19  2.72    D

   103.2  51.0  36.2  0.74  2.77  2.09    D

   110.9  47.3  44.1  0.79  2.67  3.39    D

    92.6  45.8  41.0  0.76  2.72  2.86    B

    98.5  53.4  37.0  0.83  2.83  2.48    D

   108.6  52.6  36.1  0.80  2.76  3.05    C

    76.8  53.5  38.3  0.82  2.95  2.11    D

   103.4  49.7  40.4  0.69  2.52  3.07    B

    74.4  39.8  38.9  0.76  2.64  3.05    D

   123.7  51.9  44.7  0.83  2.94  3.25    C

    81.3  37.4  43.8  0.79  2.65  2.19    D

   105.1  52.0  35.5  0.69  2.55  3.14    C

   123.8  59.7  44.0  0.77  3.14  2.06    C

    65.5  39.3  39.1  0.74  2.67  2.70    A

    97.6  50.4  44.9  0.68  2.82  3.19    B

    31.9  40.6  35.1  0.68  3.05  2.13    A

    78.9  51.3  35.5  0.75  2.85  1.93    B

    94.8  52.9  38.7  0.75  3.15  3.23    C

   110.6  43.9  41.7  0.78  2.55  2.11    C

    65.5  43.4  35.1  0.72  2.84  1.87    A

    63.7  52.4  40.0  0.81  3.08  1.88    A

    89.7  52.1  42.8  0.68  3.08  1.66    A

    93.1  55.0  39.1  0.79  2.87  2.91    B

    82.6  42.2  35.6  0.80  2.89  1.59    B

   123.7  48.9  41.1  0.84  2.39  2.65    C

   106.7  47.3  41.6  0.76  2.64  2.59    C

   108.4  59.1  35.9  0.76  2.37  3.47    D

    83.6  54.0  35.8  0.72  2.53  2.40    A

    90.3  45.4  36.0  0.76  2.91  2.43    C

   120.0  52.3  40.3  0.81  2.54  3.40    C

Answer 1

From Excel output,

1. The estimated regression equation is

sales = 9.8 + 1.30 Promotional + 2.46 family income + 23.3 Product index
- 35.1 product price - 1.90 price of brand - 12.5 Adv Method1
- 0.84 Adv Method2 + 20.1 Adv Method3

2. H0: The regression equation is not best fit to the given data

H1: The regression equation is best fit to the given data

Let the los be alpha = 5%

Test statistic F = 42.24

F crtiical value = 2.126

P-value =7.31869E-20 =0.000

Here F value > F critical value and P-value < alpha 0.05, so we reject H0

thus we conclude that the regression equation is best fit to the given data

3.

P-value of Promotional < alpha 0.05, so it is significant
i.e.promotional effect on regression equation
P-value of famly income <alpha 0.05, so it is significant
i.e.family income effect on regression equation
P-value of product index > alpha 0.05, so it is not significant
i.e. product index is not effect on regression equation
P-value of product rice <alpha 0.05, so it is significant
i.e.product rice effect on regression equation
P-value of price of brand > alpha 0.05, so it is not significant
i.e. product index is not effect on regression equation
P-value of sports <alpha 0.05, so it is significant
i.e.sports rice effect on regression equation
P-value of Radio Sports > alpha 0.05, so it is not significant
i.e.Radio sports is not effect on regression equation
P-value of TV sports show <alpha 0.05, so it is significant
i.e.TV sports show effect on regression equation