A mortgage department of a large bank is studying its recent loans. Of particular interest is how such factors as the value of home (in thousands of dollars), education level of the head of household, current monthly mortgage payment (in dollars), and sex of the head of the household (male = 1, female = 2) relate to the family income. Are these variables effective predictors of the income of the household?
A random sample of 25 recent loans is obtained (attached).
a) Determine the regression equation
b) What is value of R2(squared)? Comment on the value.
c) Conduct a global hypothesis test to determine whether any of the independent variables are different from zero.
d) Conduct individual hypothesis tests to determine whether any of the independent variables can be dropped.
e) If variables can be dropped, recompute the regression equation and R2(squared).
Solve using Excel© BrainMass Inc. brainmass.com June 3, 2020, 4:48 pm ad1c9bdddf
Hi there, I am attaching the excel sheet with the calculations
First, we assume that the Y is the income, and the other variables are the X. In this case, sex is a dummy variable, since it has a value of 0 or 1.
The model of the regression is as such:
y = alpha + b1x1(value) + b2x2 (years) + b3x3(age) + b4x4(mortgage)+v1x5(sex) + error
When I ran the problem on excel, we can then get the coefficients.
X Variable 1 0.028669482
X Variable 2 0.649669027
X Variable 3 -0.048950332
X Variable 4 -0.000404882
X Variable 5 0.722659849
Hence, the regression equation is as such:
y = 28.242 + 0.0286x1 + 0.649x2 + -0.0489x3 + -.0004x4 + 0.7336x5
b) The R ...
This posting will show you step by step how to calculate regressions using Excel.