Question

USE α = 0.05

1. A distributor of frozen desert pies wants to evaluate factors thought to influence demand (i.e. number of pies sold). He collects 10 weeks of data on sales volumne, the price, and how much is spent on advertising.

Week	Sales	Price ($)	Advertising ($100s)
1	380.0	7.5	4.0
2	430.0	4.5	3.0
3	470.0	6.4	3.7
4	450.0	7.0	3.5
5	490.0	5.0	4.0
6	340.0	7.2	3.5
7	300.0	7.9	3.2
8	440.0	5.9	4.0
9	450.0	5.0	3.5
10.0	300.0	7.0	2.7

1. Considering the model where only the price is used to predict the sales volume, test the model to determine if the model is valid. Be sure to state you null and alternative hypotheses. What is your p-value? What is your conclusion? As a part of this assignment, submit a copy of your Excel file with this p-value clearly labeled.

2. Using this same model, test the slope to determine if it is significant. Be sure to state you null and alternative hypotheses. What is your p-value? What is your conclusion? As a part of this assignment, submit a copy of your Excel file with this p-value clearly labeled.

3. What is the value of the Coefficient of Determination? As a part of this assignment, submit a copy of your Excel file with this value clearly labeled.

4. What is the value of the standard error of the estimate? As a part of this assignment, submit a copy of your Excel file with this value clearly labeled.

2. Now consider the possibility of using price and advertising to predict the units sold. If you had to pick one variable only, which is a better predictor of the selling price? Why?

Answer 1

a) First, we will insert the data into excel and run the regression by going to Data -> Data Analysis -> Regression. Select the Y range as Sales & X range as Price. We will run this regression and answer the questions. The following screenshot will guide you on how the regression is to be run:

The output of the Regression is:

To test the validity of the regression model, we will use the F-test (the ANOVA table):

Null Hypothesis: The model does not have any predictive ability, β1 = 0

Alternate Hypothesis: The model has predictive ability, β1 =/ 0

The F-ratio is 7.57 and the associated p-value is 0.024. At 0.05 significance level, we can reject the null hypothesis and say that the model has predictive ability & it is valid.

b) Null Hypothesis: β1 = 0
Alternate Hypothesis: β1 =/ 0

We can see the p-value of the same in the row 'X Variable 1' in the above output. The t-statistic value is -2.75 and the p-value is 0.024. Hence, we can reject the null hypothesis and conclude that the slope of the Price variable is significantly different than zero.

c) The value of coefficient of determination is R-square value = 0.486 (under Regression statistics)

d) The standard error of estimate is also shown under Regression statistics which is 53.30.

e) Now we have to run the regression again using advertising as the X-range and Y-range will be the same (Sales). The output is:

We can see from the above output that the model does not have any significant predictive ability as the F-ratio is not significant. The p-value is 0.07 (>0.05). Also, the R-square value is lesser in this model.

Hence, we can say that price is the better predictor of Sales.