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 hundreds 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).

PLEASE ANSWER:

1. Report the estimated regression
equation.

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

3. 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₀: b = 1 against H₁: b ¹
1.
b.    Of what importance is it to the company to
know if this slope coefficient differs
       significantly from
1.0?

4. Include screenshot of your Excel
printout.

       59.7   
49.2    40.4   
0.77    2.97   
2.45        A

      113.3   
50.9    36.2   
0.68    2.59   
2.29        C

       98.7   
53.6    43.3   
0.65    2.45   
2.09        A

      114.4   
55.1    41.5   
0.81    2.19   
2.51        D

       69.7   
47.7    36.8   
0.66    2.45   
2.49        A

       56.2   
46.4    37.9   
0.73    3.22   
3.20        B

       81.8   
50.2    35.5   
0.85    2.85   
3.19        B

       96.2   
55.0    38.9   
0.77    2.72   
1.90        D

       75.1   
48.1    35.7   
0.77    2.65   
3.17        B

      100.9   
52.1    40.5   
0.76    2.89   
3.42        C

      104.7   
50.5    41.8   
0.71    2.53   
2.58        D

       48.3   
48.2    36.1   
0.82    2.78   
3.43        A

      100.0   
47.8    42.1   
0.84    2.64   
1.73        B

      111.6   
42.2    44.0   
0.79    2.45   
2.12        B

       89.4   
54.1    43.5   
0.71    3.14   
3.15        D

       80.7   
49.3    44.1   
0.75    3.20   
2.22        D

       98.5   
49.0    35.5   
0.79    2.32   
2.10        B

       85.4   
42.6    37.0   
0.78    2.78   
2.20        B

       69.7   
39.9    40.3   
0.79    3.03   
3.10        B

       87.5   
54.1    44.2   
0.73    2.65   
3.22        A

       99.5   
53.2    40.3   
0.73    2.67   
3.41        B

       98.8   
56.1    40.1   
0.72    2.57   
2.60        B

      108.8   
41.0    38.1   
0.71    2.62   
1.55        C

       86.5   
43.2    35.5   
0.70    2.92   
2.47        C

      128.7   
57.6    42.8   
0.77    2.58   
1.52        C

      111.6   
53.3    44.3   
0.77    2.63   
1.90        B

      114.6   
52.1    37.8   
0.73    2.63   
2.65        C

       81.8   
56.5    40.8   
0.77    3.51   
1.90        B

      116.0   
53.2    39.5   
0.74    2.65   
2.67        C

       84.4   
59.3    39.5   
0.86    2.92   
3.37        A

       98.3   
56.9    37.9   
0.79    2.67   
2.31        B

      111.6   
52.7    39.8   
0.73    2.37   
1.98        B

       61.2   
44.0    37.4   
0.70    2.57   
1.99        A

     53.5   
49.1    38.8   
0.89    3.05   
3.32        D

      122.3   
52.8    40.0   
0.82    2.79   
2.89        C

      107.8   
54.3    39.7   
0.73    3.12   
3.48        C

       72.2   
49.5    36.6   
0.74    2.55   
2.94        B

       83.6   
42.2    38.7   
0.75    2.80   
1.99        C

       65.7   
56.3    35.3   
0.66    3.18   
2.67        B

       93.3   
55.3    37.4   
0.74    2.70   
3.50        D

       89.6   
54.3    39.2   
0.80    2.51   
3.37        A

       92.5   
48.8    38.3   
0.82    2.63   
1.75        D

       43.1   
42.1    38.2   
0.74    3.16   
2.72        A

      112.8   
47.3    38.3   
0.73    2.35   
2.36        C

      102.4   
50.1    41.7   
0.77    2.50   
3.42        D

       89.9   
58.7    39.8   
0.71    3.00   
2.97        A

       89.3   
52.6    37.8   
0.67    2.97   
2.78        D

      114.8   
56.3    39.8   
0.75    3.04   
3.21        C

       64.3   
51.6    38.7   
0.72    2.71   
1.75        A

       67.1   
49.3    39.8   
0.68    3.02   
3.24        B

       74.7   
52.3    39.3   
0.71    3.28   
1.83        B

      106.3   
51.4    42.1   
0.74    2.44   
2.08        D

      101.2   
56.0    39.8   
0.77    2.48   
2.44        D

       85.1   
44.9    38.5   
0.73    2.80   
3.25        B

       95.3   
53.0    42.0   
0.76    2.90   
2.10        D

       69.6   
52.4    36.3   
0.73    2.90   
3.22        A

      102.4   
60.2    44.5   
0.67    3.23   
2.16        C

      106.5   
48.0    41.6   
0.82    2.80   
2.24        C

       62.3   
44.6    38.4   
0.75    2.65   
2.98        A

       93.0   
51.9    39.0   
0.75    2.61   
2.45        A