Question

You are a new hire at Laurel Woods Real Estate, which specializes in selling foreclosed homes via public auction. Your boss has asked you to use the following data (mortgage balance, monthly payments, payments made before default, and final auction price) on a random sample of recent sales in order to estimate what the actual auction price will be. Add a new variable that describes the potential interaction between the loan amount and the number of payments made.

Loan			Monthly Payments			Payments Made		Auction Price
$	85,613		$	1,003.10		1		$	28,525
	113,255			929.31		36			40,575
	110,315			749.28		7			45,250
	91,935			726.17		8			16,600
	97,600			831.85		21			40,700
	104,400			983.27		18			63,100
	113,800			1,075.54		20			72,600
	116,400			1,087.16		35			72,300
	100,000			900.01		33			58,100
	92,800			683.11		36			37,100
	105,200			915.24		34			52,600
	105,900			905.67		38			51,900
	94,700			810.70		25			43,200
	105,600			891.33		20			52,600
	104,100			864.38		7			42,700
	85,700			1,074.73		30			22,200
	113,600			871.61		24			77,000
	119,400			1,021.23		58			69,000
	90,600			836.46		3			35,600
	104,500			1,056.37		22			63,000

  

  Click here for the Excel Data File

Determine the regression equation. (Round your answers to 3 decimal places. Negative amounts should be indicated by a minus sign.)

Complete the following table. (Round your answers to 3 decimal places. Leave no cells blank - be certain to enter "0" wherever required. Negative amounts should be indicated by a minus sign.)

Compute the t-value corresponding to the interaction term. (Round your answer to 2 decimal places. Negative amount should be indicated by a minus sign.)

Do a test of the null hypothesis to check if the interaction is significant. (Use the 0.05 significance level.)

Answer 1

we will solve it by using excel and the steps are

Enter the Data into excel

take extraa column as interaction between Loan Amound and Payment made.

Click on Data tab

Click on Data Analysis

Select Regression

Select input Y Range as Range of dependent variable.

Select Input X Range as Range of independent variable

click on labels if your selecting data with labels

click on ok.

So this is the output of Regression in Excel.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.851
R Square	0.724
Adjusted R Square	0.650
Standard Error	10094.198
Observations	20.000
ANOVA
	df	SS	MS	F	Significance F
Regression	4.000	4001985190.187	1000496297.547	9.819	0.000
Residual	15.000	1528392434.813	101892828.988
Total	19.000	5530377625.000
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-142843.143	46373.230	-3.080	0.008	-241685.342	-44000.943	-241685.342	-44000.943
Loan	1.547	0.417	3.712	0.002	0.659	2.435	0.659	2.435
Monthly Payments	36.446	21.153	1.723	0.105	-8.639	81.532	-8.639	81.532
Payments Made	1353.611	1447.674	0.935	0.365	-1732.033	4439.254	-1732.033	4439.254
Loan*Payments Made	-0.013	0.014	-0.932	0.366	-0.042	0.017	-0.042	0.017

Determine the regression equation

auction price = -142843.143+1.547*Loan +36.446*Monthly Payments+1353.611*Payments Made - 0.013*Loan*Payments Made

Complete the following table.

	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	-142843.143	46373.230	-3.080	0.008	-241685.342	-44000.943
Loan	1.547	0.417	3.712	0.002	0.659	2.435
Monthly Payments	36.446	21.153	1.723	0.105	-8.639	81.532
Payments Made	1353.611	1447.674	0.935	0.365	-1732.033	4439.254
Loan*Payments Made	-0.013	0.014	-0.932	0.366	-0.042	0.017

Compute the t-value corresponding to the interaction term.

t-value= - 0.932

Do a test of the null hypothesis to check if the interaction is significant.

Since the p-value for interaction term is 0.366 > 0.05 so we reject the null hypothesis and conclude that interaction is not significant.