Your company would like you to complete their sales prediction model. They would
ID: 3295036 • Letter: Y
Question
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.
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
1)
data
2. Conduct the F-test for model significance and report your results.
significanc e F = 7.95e-20 << 0.05
hence the model is significant
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.)
R^2 = 0.86844
which is very large and hence it is adequate
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?
x4 is average retail price of product in dollars.
beta coefficient is -34.379
hence it will decrease
5. Do changes in your competitor's price have a significant impact on your company's sales and, if so, at what significance level?
x5 = Average retail price of leading competitor brand in dollars.
beta coefficient = 0.7673
p-value = 0.66323 ,
at 5 % , it is not signiifcant
6. Are any of the other variables in your model significant in determining sales at the 5% significance level or better?
if p-value < 0.05
the variable is significant
here
Promotional budget for the sales area, in thousands of dollars.
Median family income in the sales area, in thousands of dollars.
Average retail price of product in dollars.
and all dummy variable are significant
7. Your boss also wants to know about the effectiveness of the various advertising methods. Report your findings with regard to this variable.
here all dummy variables are signiifcant
hence advertising is significant
Please don't forget to rate positively if you found this response helpful.
Feel free to comment on the answer if some part is not clear or you would like to be elaborated upon.
Thanks and have a good day!
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.