Question

A Manager wants to consider another variable in determining product location for paper products. In addition to shelf space, the manager wants to consider whether placing the product at the front (= 1) or back (= 0) of the aisle influences weekly sales. Use the dataset below:

a. Run a multiple regression using shelf space (X₁) and location (X₂) to predict sales (Y). Report your regression equation.

b. Is the regression model you ran statistically significant? How can you tell?

c. Using the regression equation you generated in (a), predict the amount of sales if 8 square feet of shelving is used for paper products at the front of the isle and compare it to the sales if 8 square feet of shelving is used for paper products at the back of the isle. Discuss your results.

d. What is the relationship (correlation) between the predictors in the model and sales?

e. How much variance in sales is explained by the predictors?

f. Which of your predictors explain a unique amount of variance in sales?

Shelf Space	Aisle Location	Sales
5	0	160
5	1	220
5	0	140
10	0	190
10	0	240
10	1	260
15	0	230
15	0	270
15	1	280
20	0	260
20	0	290
20	1	310

Answer 1

a. Run a multiple regression using shelf space (X1) and location (X2) to predict sales (Y). Report your regression equation.
Step 1 - Put the data in excel as shown and arrange the variables as shown

Step 2 - Select the regression option from the data analysis tab

Step 3- Input the values as shown below.

Step 4 - The output is generated as follows.

From the the regression output we use the coefficients to get regression equation.(Highlighted in yellow)

y = 130   + 7.4(Shelf Space) + 45(Aisle Location)

b. Is the regression model you ran statistically significant? How can you tell?

Hypothesis:
Ho: All the beta coefficient of the model are equal to zero
H1: At least one of the beta coefficents are not equal to zero.

We check the anova output from the regression, and look at the pvalue = 0.000127081
The pvalue is less than 0.05, hence we reject the null hypothesis and conclude that the model is valid.

c. Using the regression equation you generated in (a), predict the amount of sales if 8 square feet of shelving is used for paper products at the front of the isle and compare it to the sales if 8 square feet of shelving is used for paper products at the back of the isle. Discuss your results.

Case 1 : In front of the isle
y = 130   + 7.4(Shelf Space) + 45(Aisle Location)
y = 130   + 7.4(8) + 45(1) = 234.2

Case 2 : In back of the isle
y = 130   + 7.4(Shelf Space) + 45(Aisle Location)
y = 130   + 7.4(8) + 45(0)=189.2

d. What is the relationship (correlation) between the predictors in the model and sales?
Both the predictors are positively correlated with the sales.

e. How much variance in sales is explained by the predictors?
Variance in sales is explained by the Coefficient of determination is also called rsquare, it measure the amount of variablity in y that is explained by the independent variable. It lies between 0 and 1, higher the value, better is model or stronger is relationship between the two variables.

Rqsuare = 0.863780183 ( highlighted in blue)

f. Which of your predictors explain a unique amount of variance in sales?
Both the predictor explain a unique amount of variance in sales.