Your company would like you to complete their sales prediction model. They would
ID: 3268660 • 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.
Please show in Excel and explain
119.0 60.9 42.0 0.74 2.79 1.60 B
81.8 53.6 36.2 0.76 3.05 3.18 D
86.6 44.5 42.3 0.73 2.77 2.61 A
76.7 52.0 36.6 0.67 2.90 2.51 B
72.9 45.6 44.9 0.72 2.93 3.48 D
77.1 48.8 38.6 0.79 2.99 3.27 D
69.6 44.5 36.3 0.74 2.69 2.67 D
115.7 47.2 44.9 0.76 2.66 3.46 C
87.3 50.5 41.9 0.84 3.36 1.52 D
94.0 51.0 38.6 0.65 2.66 3.02 B
61.4 49.4 37.0 0.73 3.12 1.82 B
68.3 47.9 40.0 0.74 2.77 2.49 A
59.9 49.3 37.9 0.76 3.03 3.16 D
107.4 45.8 35.4 0.73 2.27 2.27 D
103.3 62.4 41.0 0.78 2.44 3.48 A
113.2 53.8 40.3 0.72 3.08 2.37 C
97.9 56.4 40.5 0.68 2.80 3.41 B
123.0 48.4 44.3 0.74 2.95 2.45 C
92.4 45.5 38.7 0.71 2.35 2.02 A
84.8 43.4 37.4 0.75 2.71 2.53 D
102.7 56.1 35.6 0.74 2.69 2.28 B
114.1 53.3 35.4 0.73 2.70 2.72 C
101.1 52.0 42.4 0.70 2.64 3.20 B
82.8 46.6 42.0 0.74 2.80 1.63 A
89.0 43.3 38.8 0.75 3.02 1.72 D
104.8 48.5 42.8 0.72 2.88 2.45 C
68.7 52.5 36.8 0.75 2.87 1.58 A
137.7 51.8 42.8 0.75 2.63 1.75 C
87.7 49.2 42.2 0.82 2.81 2.71 A
118.4 42.4 43.0 0.73 2.82 1.81 C
129.2 48.3 43.3 0.74 2.50 2.30 C
71.7 40.9 40.9 0.74 2.95 1.66 A
113.1 47.8 38.7 0.71 2.73 2.76 C
92.3 43.4 40.2 0.77 2.89 1.86 C
109.2 53.0 43.7 0.81 2.40 2.02 D
69.9 46.8 41.5 0.68 2.88 2.22 A
74.0 49.0 37.1 0.79 2.87 3.30 B
72.9 44.8 42.9 0.78 3.23 2.80 A
96.1 42.0 39.9 0.71 3.08 1.51 C
105.8 47.5 39.9 0.80 2.87 2.45 C
94.9 43.9 43.6 0.81 2.84 2.36 B
81.5 41.4 43.3 0.77 2.55 2.72 A
104.9 51.2 42.8 0.75 2.75 1.84 D
112.9 50.3 38.9 0.80 2.49 2.28 C
85.9 50.6 41.4 0.72 3.01 2.96 B
87.5 49.0 39.7 0.69 3.06 2.61 D
74.9 44.4 38.0 0.73 2.35 3.48 B
92.0 45.5 39.7 0.79 2.52 1.54 D
61.0 43.8 35.6 0.83 3.27 3.50 D
98.0 48.2 38.6 0.79 2.46 2.95 D
77.9 50.1 39.1 0.78 2.92 3.23 B
70.0 54.2 38.0 0.68 2.93 2.22 A
73.1 50.8 41.9 0.74 2.85 1.76 A
112.7 50.7 40.8 0.73 2.45 2.65 B
96.0 47.3 41.6 0.79 2.63 3.46 B
117.7 58.0 43.4 0.86 2.72 2.45 B
89.3 51.1 40.8 0.64 2.77 1.72 D
118.3 46.3 44.8 0.77 2.74 2.50 C
100.3 52.4 40.8 0.72 2.72 1.54 D
129.5 56.0 37.1 0.74 2.43 2.38 C
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.
Explanation / Answer
1. Report the estimated regression equation.
y^ = 25.6686 + 1.3089*Promotion + 2.1199 *Median_family + 24.8224*index -32.64167*retail_product-4.99104198*retail_leading_com -12.446272*A +0.5171813*B+22.0765*C
2. Conduct the F-test for model significance. Interpret your results.
p-value for F-test is 1.81*10^(-18) << 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.)
these value are more than 0.8 , which is very good fit
hence model appear to be adequate for the purpose intended
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?
as coefficient of retail_product is
5. Do changes in your competitor's price have a significant impact on your company's sales and, if so, at what significance level?
since p-value of reatil_leading competitor is 1.6483*10^(-9) << 0.05
hence it is significant at every level greater than 1.6483*10^(-9)
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
Promotion, Median_family ,retail_product, retail_leading_comp , DA , DC are 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.