Using a regression model in excel to understand the factors that contribute to c

Question

Using a regression model in excel to understand the factors that contribute to customer satisfaction and spending. Refer to the data provided to identify what variables are significant to predicting overall satisfaction. Develop and interpret the prediction equation and the coefficient of determination. Based on the data, what areas should the business focus on to improve customer satisfaction?

Dine In (1)/Take Out (2) Satisfaction with Service Satisfaction with Food Overall Satisfaction Driving Distance to Restaurant Total Bill 1 4 4 4 5 10 1 2 3 3 5 15 1 3 3 3 10 10 1 5 5 5 12 15 2 3 4 3 10 25 2 2 4 3 15 25 2 3 4 3 10 26 1 4 3 3 16 27 2 3 3 3 2 25 1 2 3 2 10 26 2 1 3 2 15 20 2 2 2 2 10 20 1 5 4 4 12 20 1 4 5 4 16 20 1 4 5 4 18 20 1 3 4 3 20 27 1 4 3 4 18 28 2 3 4 3 20 28 2 3 4 3 16 28 1 4 5 4 7 12 2 4 5 4 9 20 1 2 3 3 10 24 2 3 5 4 6 26 2 3 4 3 10 28 1 3 4 3 9 27 2 4 5 4 8 24 2 3 3 3 10 22 1 4 4 4 6 23 2 3 4 4 10 25 1 4 5 4 10 20 2 2 3 2 15 20 2 2 2 2 16 20 1 4 4 4 18 20 2 3 2 3 16 20 2 3 3 3 14 25 1 3 3 3 20 22 1 3 3 3 16 23 1 4 5 4 17 28 2 3 3 3 16 23 2 3 4 3 5 15 1 4 4 4 10 28 2 3 3 3 6 24 2 2 3 2 10 27 1 3 3 3 6 26 2 4 4 4 7 28 1 2 3 2 6 24 2 4 5 4 8 22 1 4 5 4 6 23 1 5 5 5 8 20

Explanation / Answer

From the Problem,

our dependent variable is Overall Satisfaction (Y).

We have to check , which variables are significant to predicting overall satisfaction.

means which independent variables (X's) are helps to predict the (Y)

here The hypothesis is,

Ho : B_0=B_1=B_2=B_3=B_4=B_5

i.e. variables are insignificant (not significant).

V/s

H1 : At least one of the coefficient is not Zero.

i.e. at least one of the variable is significant.

where B_0,B_1,.....B_5 are the coefficients.

using excel the results are,

Factor

Coefficients

Standard Error

t Stat

P-value

Intercept (B_0)

0.71

0.35

2.03

0.05

Dine In (1)/Take Out (2) (B_1)

-0.02

0.10

-0.20

0.85

Satisfaction with Service (B_2)

0.58

0.07

8.37

0.00

Satisfaction with Food (B_3)

0.27

0.07

4.09

0.00

Driving Distance to Restaurant ((B_4)

-0.01

0.01

-0.66

0.51

Total Bill (B_5)

-0.01

0.01

-0.80

0.43

From the Table,

1]. The coefficients of variable Satisfaction with Service (B_2) and Satisfaction with Food (B_3)

are statistically significant. because ,

p_value < 0.05

at 5% level of significance we reject Ho.

2]. prediction equetion is,

Overall Satisfaction = B_0 + B_1*(Dine In (1)/Take Out (2)) + B_2*(Satisfaction with Service) +B_3*(Satisfaction with Food) + B_4*(Driving Distance to Restaurant) + B_5*(Total Bill)

Here , 'Satisfaction with Service' and 'Satisfaction with Food' are significant thats why we include these variables in the model.

Overall Satisfaction = B_0 + B_2*(Satisfaction with Service) +B_3*(Satisfaction with Food)

Overall Satisfaction = B_0 + 0.58*(Satisfaction with Service) +0.27*(Satisfaction with Food)

3]. Coefficient of Determination = R2 =0.86

i.e. When Coefficient of Determination is high then our model is good.

and here our R2 =0.86 .

so we can conclude that our model is good.

4] We should business focus on :

We should business focus on Satisfaction with Service (B_2) and Satisfaction with Food (B_3) these variables to improve customer satisfaction because ,

These both variables are significant.

Factor

Coefficients

Standard Error

t Stat

P-value

Intercept (B_0)

0.71

0.35

2.03

0.05

Dine In (1)/Take Out (2) (B_1)

-0.02

0.10

-0.20

0.85

Satisfaction with Service (B_2)

0.58

0.07

8.37

0.00

Satisfaction with Food (B_3)

0.27

0.07

4.09

0.00

Driving Distance to Restaurant ((B_4)

-0.01

0.01

-0.66

0.51

Total Bill (B_5)

-0.01

0.01

-0.80

0.43

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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