9.
Using the Excel file Weddings, apply the Excel Regression tool using the wedding cost as the
dependent variable and attendance, income, age, value rating as the independent variable.
a. Interpret all key regression results, hypothesis tests, and confidence intervals in the output.
Model Summaryb
Std. Error
Adjusted of the
Model R R Square R Square Estimate
1 .864a 0.747 0.696 $7,297.56
0
a. Predictors: (Constant), Value Rating, Attendance, Bride's
age , Couple's Income
b. Dependent Variable: Wedding cost
- The model explains 74.7% of the wedding costs.
ANOVAa
Model Sum of Squares df Mean Square F Sig.
1 Regression 3,141,674,730.78 4 785418682.695 14.748 .000b
1
Residual 1,065,087,669.21 20 53254383.461
9
Total 4,206,762,400.00 24
0
a. Dependent Variable: Wedding cost
b. Predictors: (Constant), Value Rating, Attendance, Bride's age , Couple's Income
- H0: B1 = B2 = B3 = B4 = 0
- H1: at least one Bi ≠ 0
- p-value < alpha -> At least one Bi ≠ 0
Coefficientsa
Standardize
Unstandardized d
Coefficients Coefficients 95% Confidence Interval for B Collinearity Statistics
Model B Std. Error Beta t Sig. Lower Bound Upper Bound Tolerance VIF
1 (Constant) 910.091 11713.154 0.078 0.939 -23523.121 25343.302
Attendance 46.719 28.835 0.263 1.620 0.121 -13.429 106.867 0.479 2.086
Bride's age -399.720 441.619 -0.120 -0.905 0.376 -1320.921 521.482 0.721 1.387
Couple's 0.325 0.080 0.646 4.049 0.001 0.158 0.493 0.497 2.012
Income
Value 837.652 1055.154 0.103 0.794 0.437 -1363.362 3038.665 0.755 1.325
Rating
a. Dependent Variable: Wedding cost
Equation: Wedding cost = 910.091 + 46.719ns*Attendance – 399.72ns*Bride’s age + 0.325****Couple’s
Income + 837.652ns*Value rating + epsilon
*
: significant at 0.01
ns
: not significant
- With each one more attendee, Wedding cost increases by $46.719
- With each one more year of the bride’s age, Wedding cost decreases by $399.72
- With each one more dollar in the couple’s income, Wedding cost increases by $0.325
- With each one more point in value rating, Wedding cost increases by $837.652
b. Analyze the residuals to determine if the assumptions underlying the regression analysis are valid.
Correlations
Attendanc Bride's Couple's Value
e age Income Rating
Attendanc 1.000
e
Bride's -0.218 1.000
age
Couple's 0.700 -0.087 1.000
Income
Value -0.081 0.481 -0.107 1.000
Rating
Residuals Statisticsa
Std.
Minimum Maximum Mean Deviation N
Predicte $8,374.60 $59,963.50 $25,848.0 $11,441.29 25
d Value 0 0
Residual - $15,734.74 $0.000 $6,661.730 25
$8,839.76 0
2
Std. -1.527 2.982 0.000 1.000 25
Predicte
d Value
Std. -1.211 2.156 0.000 0.913 25
Residual
a. Dependent Variable: Wedding cost
- Due to the bell shape around 0, the residuals are roughly normally distributed
c. Use the standard residuals to determine if any possible outliers exist.
- No outlier exists since there are no data point outside ±3.
d. If a couple is planning a wedding for 175 guests, age 28, rating 3, income 60.000$, how much should
they budget?
- Wedding cost = 910.091 + 46.719ns*Attendance – 399.72ns*Bride’s age + 0.325****Couple’s Income +
837.652ns*Value rating + epsilon
- Attendance: 175
- Bride’s age: 28
- Value rating: 3
- Couple’s income: 60,000
-> 910.091 + 46.719*175 – 399.72*28 + 0.325*60.000 + 837.652*3 = $19,906.712
