Question

Please refer to the Anova and Simple Regression Projects.

Your company would like you to complete their sales prediction model. They would like you to ascertain if the other variables for which they have data also affect sales. A complete model will have to include advertising expenditures and package design along with the other variables listed above.

1. Create three dummy variables named DA, DB, and DC to capture the effects of the four levels of the categorical variable. Then use Tools > Data Analysis > Regression, to fit a regression of Sales as a function of all the variables in your data set (variables 3 through 7 above), plus the three dummies DA, DB, and DC.
2. Conduct the F-test for model significance and report your results.
3. Does your model appear to be adequate for the purpose intended? (Refer to goodness-of-fit measures, in particular, R², adjusted R², and the standard error of estimate.)
4. Your boss wants to know what you predict will be the effect on company sales if the company increases its price. What will be your response?
5. Do changes in your competitor's price have a significant impact on your company's sales and, if so, at what significance level?
6. Are any of the other variables in your model significant in determining sales at the 5% significance level or better?
7. Your boss also wants to know about the effectiveness of the various advertising methods. Report your findings with regard to this variable.

       95.2    44.6    41.3    0.78    2.50    3.05        D

       86.2    51.6    42.1    0.78    3.09    1.67        A

      112.8    52.9    38.9    0.68    2.57    3.38        C

       95.4    43.5    36.7    0.73    2.24    3.01        D

       95.0    48.7    38.8    0.78    3.04    2.57        C

       96.1    48.7    40.6    0.75    2.45    3.38        D

       82.4    49.2    37.8    0.76    2.70    2.94        B

       91.9    47.7    38.5    0.75    2.67    2.33        C

       91.8    49.7    38.6    0.75    2.54    1.62        D

      115.5    54.5    40.6    0.74    2.55    1.98        C

      111.4    43.4    42.6    0.74    2.64    3.45        C

       79.6    51.2    39.7    0.73    2.80    2.60        A

       90.9    42.7    44.7    0.77    2.73    2.13        A

      105.4    49.9    43.1    0.80    2.68    2.30        D

       74.2    50.3    35.3    0.73    2.88    3.02        B

      115.6    55.1    44.7    0.77    2.57    3.50        B

      115.2    49.2    43.4    0.70    2.35    2.42        B

       92.9    56.9    37.2    0.71    2.86    2.38        D

      113.0    53.4    38.8    0.71    2.45    1.75        B

       78.2    43.4    43.8    0.82    2.69    2.46        A

       92.2    46.2    41.4    0.78    2.98    1.60        B

       90.6    53.5    40.9    0.78    2.77    2.46        D

      120.0    45.6    37.3    0.68    2.38    2.42        C

       95.5    43.4    44.2    0.78    2.54    3.02        B

       96.0    53.5    40.7    0.72    2.51    2.41        B

       74.2    47.2    41.2    0.82    2.78    2.59        B

      105.5    42.7    35.9    0.80    2.30    2.00        C

      125.0    52.9    38.5    0.79    2.32    2.16        B

       86.3    50.7    42.2    0.75    2.76    3.16        B

       84.5    44.5    43.3    0.74    2.76    2.89        B

       80.3    51.8    39.8    0.81    2.86    3.37        A

       80.8    40.9    42.9    0.69    2.83    2.07        B

       99.9    59.4    37.2    0.79    2.64    2.84        B

    116.4    62.7    35.5    0.76    2.65    3.50        C

      112.6    47.7    40.8    0.87    2.33    2.14        D

       79.1    48.1    37.3    0.79    2.54    3.40        B

      111.0    45.3    42.4    0.73    2.73    3.10        C

       81.0    44.5    35.3    0.65    3.07    3.17        C

       79.1    45.6    37.2    0.66    2.46    2.99        D

      104.9    48.3    43.9    0.68    3.17    2.65        C

       89.2    49.7    43.4    0.73    2.98    2.29        B

      114.2    54.5    37.1    0.77    2.65    2.75        C

       96.2    51.9    37.7    0.82    2.74    3.40        B

      109.2    58.7    42.0    0.72    2.74    1.51        D

       37.8    48.3    37.0    0.74    3.41    2.16        A

       73.7    50.8    43.1    0.65    2.97    3.22        B

       64.7    46.8    41.2    0.72    2.97    2.28        A

       94.7    43.5    40.0    0.77    2.36    1.86        B

       48.8    44.9    42.3    0.75    3.32    3.42        A

       93.1    46.2    42.2    0.79    2.29    1.75        A

       78.3    51.1    39.9    0.72    2.95    2.22        B

       83.1    44.9    37.5    0.79    2.45    3.43        B

       69.8    46.4    35.7    0.78    2.93    1.80        D

      121.3    43.9    43.6    0.76    2.84    2.43        C

       84.3    45.0    39.8    0.77    2.65    2.01        D

       89.6    40.5    44.7    0.79    2.84    2.54        A

       89.2    45.1    35.3    0.66    3.16    1.74        C

       75.3    50.1    35.5    0.80    3.04    1.99        B

      120.0    46.8    42.7    0.84    2.87    3.07        C

       89.5    50.8    44.8    0.76    3.11    2.07        D

Answer 1

The header or the column names in the dataset are not give, so i shall assume the first variables to be ssales , please see the excel screenshots below , we goto data > data analysis tab and select regression

The signficance F value is Significance F
8.57865E-18

we formulte the hypothesis as

H0: the model is not signficant
H1: the model is statisitcally significant

as the p value is less than 0.05 , hence we reject the null hypothesis to conclude that the model is statistically signficant

the r2 value is 0.8413 , this means that the model is able to explain about 84.13% variation in sales due to the predictor variables

the adj r2 is 0.816 , which is similar to r2 except that it penalises the model for the number of predictor used in the mdoel

lets say that the price is var4

so we look at the coefficient of price as -36.46 , this essentially means that price as a negative relation with the sales , for every unit increase in the value of price the sales would go down by 36.4 units

please note that we can answer only 4 subparts of a question at a time ,as per the answering guidelines