Using Excel fit a simple regression of Sales as a function of Promotional budget

Question

Using Excel fit a simple regression of Sales as a function of Promotional budget (advertising expenditures) with the following data set:

65.8 47.5 42.3 0.70 3.08 2.18 A
90.7 51.4 42.5 0.75 2.75 2.34 D
76.1 59.2 44.0 0.71 3.38 1.95 B
110.4 55.1 44.3 0.69 3.12 2.00 C
98.0 43.6 43.6 0.74 2.81 2.10 D
91.9 57.2 40.3 0.70 2.89 2.04 D
107.2 40.6 36.9 0.83 2.42 2.72 C
106.8 50.2 37.4 0.66 2.65 3.17 C
112.1 46.8 43.3 0.75 2.91 2.52 C
86.0 48.0 40.9 0.85 2.46 2.89 A
102.4 46.9 43.6 0.70 3.22 1.70 C
103.5 46.3 39.5 0.84 2.81 3.41 C
126.4 45.0 41.9 0.62 2.92 2.48 C
120.2 44.8 42.7 0.81 2.65 3.12 C
102.3 50.5 43.1 0.73 2.88 1.90 D
67.6 48.6 35.4 0.69 2.84 3.07 B
97.1 52.7 43.9 0.75 2.32 2.70 A
73.7 41.1 43.6 0.77 2.88 2.30 B
94.5 50.1 36.5 0.75 2.58 2.06 D
72.5 48.6 37.1 0.78 2.57 1.91 A
98.0 45.9 43.4 0.67 2.34 2.86 B
111.0 51.5 36.1 0.83 2.65 2.21 C
78.3 47.1 35.4 0.83 2.90 2.79 A
74.0 58.9 44.0 0.82 3.57 2.92 B
97.1 54.5 43.2 0.78 2.72 3.14 D
83.4 57.3 40.0 0.73 3.01 2.65 A
111.9 44.2 44.0 0.71 2.78 1.57 C
122.4 50.6 39.7 0.69 2.64 2.50 C
81.1 52.6 44.7 0.73 2.95 2.96 D
71.1 44.0 43.5 0.78 2.49 2.27 A
100.4 51.0 37.1 0.84 2.54 2.28 D
90.5 51.4 39.8 0.75 2.53 2.82 A
68.1 46.7 36.7 0.69 2.86 1.67 A
66.4 38.7 43.2 0.76 2.62 2.37 A
70.2 53.0 35.3 0.73 3.33 2.99 B
113.1 46.6 44.4 0.65 2.15 2.17 B
78.9 53.2 35.2 0.76 2.95 3.34 D
80.5 43.2 38.7 0.72 2.70 2.81 A
113.3 57.7 44.4 0.68 2.55 2.55 B
69.2 47.4 35.9 0.68 2.86 2.11 B
74.5 58.5 38.2 0.78 2.93 3.31 A
82.7 55.0 37.6 0.73 2.79 2.92 A
97.0 50.1 44.0 0.73 2.62 2.68 A
79.0 50.2 41.1 0.64 3.06 2.03 A
106.4 54.4 40.3 0.71 2.57 1.60 A
84.9 46.3 42.8 0.81 2.84 1.67 B
113.2 45.9 37.9 0.81 2.66 3.35 C
58.8 47.5 42.4 0.71 2.83 3.20 A
98.6 51.9 39.3 0.67 2.28 1.93 A
63.3 45.2 40.3 0.70 2.92 2.80 A
91.7 49.3 37.5 0.77 2.65 2.79 B
97.3 56.6 41.4 0.75 2.73 1.77 B
108.8 46.0 41.6 0.73 2.87 2.43 C
90.0 47.5 36.1 0.71 2.86 2.29 C
116.3 54.8 44.4 0.84 2.62 3.22 A
117.6 53.6 37.3 0.67 2.59 1.75 C
83.9 48.8 35.5 0.75 2.41 1.73 A
73.0 48.0 36.6 0.72 2.94 2.94 D
61.1 48.6 38.0 0.75 2.79 2.44 B
76.0 53.7 35.7 0.76 2.79 2.60 D

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

Explanation / Answer

1. Let the variable names be Sales, V1, V2, V3, V4, V5, V6

Let V6B = 1, when V6 = "B" else 0, V6C = 1, when V6 = "C" else 0, V6D = 1, when V6 = "D" else 0

V6B = V6C = V6D = 0, when V6 = "A"

The estimated regression equation is,

Sales = 45.251 + 1.301 V1 + 1.661 V2 + 3.715 V3 - 34.553 V4 - 1.740 V5 + 4.535 V6B + 34.721 V6C + 9.406 V6D

2. Null Hypothesis H0: The coefficients of the linear regression are 0. That is, B1 = B2 = B3 = B4 = B5 = B6 = 0

Alterntive hypothesis H1: The coefficients of the linear regression are not equal to 0. That is, B1 <> B2 <> B3 <> B4 <> B5 <> B6 <> 0

F-stat for all the coefficients are : B1 - 0.4220, B2 - 15.8030, B3 - 0.0232, B4 - 37.8152, B5 - 0.3804, B6 - 48.4000

p-value of all the coefficients are: B1 - 0.518846, B2 - 0.000222, B3 - 0.879529, B4 - 1.196e-07, B5 - 0.540159, B6 - 6.008e-15

p-values for B2, B4, B6 are less than 0.05, so we can reject the null hypothesis for B2, B4 and B6 and these coefficients are not zero, so the variables V2, V4 and V6 are significant variable for the model in determining the sales amount. V1, V3, V5 are not significant variable for the model to determine the sales amount.

2. V1 - keeping all variables constant, 1 unit increase in the value of V1 led to 1.301 unit increase in Sales.

V2 - keeping all variables constant, 1 unit increase in the value of V2 led to 1.661 unit increase in Sales.

V3 - keeping all variables constant, 1 unit increase in the value of V3 led to 3.715 unit increase in Sales.

V4 - keeping all variables constant, 1 unit increase in the value of V4 led to 34.553 unit decrease in Sales.

V5 - keeping all variables constant, 1 unit increase in the value of V5 led to 1.740 unit decrease in Sales.

V6B - keeping all variables constant, if V6 = "B" then there is 4.535 unit increase in Sales.

V6C - keeping all variables constant, if V6 = "C" then there is 34.721 unit increase in Sales.

V6D - keeping all variables constant, if V6 = "D" then there is 9.406 unit increase in Sales.

4. t test statistic = (observed value - hypothesized value)/ Standard error = (1.301 - 1)/0.2580 = 1.167

p -value = 0.2255178 which is greater than 0.05, so we fail to reject the null hypothesis and slope coefficient for V1 is not different than the value 1.0.

If the slope coefficient is 1, then 1 unit increase in the value of V1 led to 1 unit increase in Sales. If the slope coefficient differs significantly from 1.0, then the variable may affect more postively or negatively the sales amount.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.