Question

A real estate developer wishes to study the relationship between the size of home a client will purchase (in square feet) and other variables. Possible independent variables include the family income, family size, whether there is a senior adult parent living with the family (1 for yes, 0 for no), and the total years of education beyond high school for the husband and wife. The sample information is reported below.

Family		Square Feet		Income (000s)		Family Size		Senior Parent		Education
1		2,200		60.8		2		0		4
2		2,300		68.4		2		1		6
3		3,400		104.5		3		0		7
4		3,360		89.3		4		1		0
5		3,000		72.2		4		0		2
6		2,900		114		3		1		10
7		4,100		125.4		6		0		6
8		2,520		83.6		3		0		8
9		4,200		133		5		0		2
10		2,800		95		3		0		6

1. Develop an appropriate multiple regression equation using stepwise regression. (Use Excel data analysis and enter number of family members first, then their income and delete any insignificant variables. Leave no cells blank - be certain to enter "0" wherever required. R and R2 adj are in percent values. Round your answers to 3 decimal places.)

Step	1	2
Constant
Family Size
t-statistic
p-value
Income
t-statistic
p-value
S
R-Sq
R-Sq(adj)

Answer 1

1) The regression line is

y = b1x1 + c

Where y= Square feet Home

b1=slope

x1=Family Size

c=Intercept

In order to perform regression analysis using the data in Excel, follow the below steps

• Open Data Data Analysis Regression Analysis
• Select y variable range
• Select x1 variable range
• Select Output location
• Click OK

You will get an output as below

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.909178
R Square	0.826605
Adjusted R Square	0.804931
Standard Error	304.9013
Observations	10
ANOVA
	df	SS	MS	F	Significance F
Regression	1	3545441	3545441	38.13745	0.000266442
Residual	8	743718.6	92964.83
Total	9	4289160
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	1347.31	296.3709	4.546027	0.001884	663.8777333	2030.743
Family Size	494.4828	80.07101	6.175553	0.000266	309.8386673	679.1268

2) The 2nd regression line is

y = b1x1 + b2x2 + c

Where y= Square feet Home

b1,b2=slope

x1=Family Size

x2=Income

c=Intercept

In order to perform regression analysis using the data in Excel, follow the below steps

• Open Data Data Analysis Regression Analysis
• Select y variable range
• Select x1 and X2 variable ranges
• Select Output location
• Click OK

You will get an output as below

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.955883
R Square	0.913711
Adjusted R Square	0.889058
Standard Error	229.9396
Observations	10
ANOVA
	df	SS	MS	F	Significance F
Regression	2	3919055	1959527	37.06158	0.000188728
Residual	7	370105.4	52872.2
Total	9	4289160
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	793.6913	305.4978	2.598027	0.035527	71.30390231	1516.079
Income	0.012034	0.004527	2.65826	0.032549	0.001329247	0.022738
Family Size	327.3406	87.17693	3.754899	0.007122	121.199897	533.4813

3)

The 3rd regression line is

y = b1x1 + b2x2 +b3x3 + c

Where y= Square feet Home

b1,b2,b3=slope

x1=Family Size

x2=Income

x3=Senior Parent

c=Intercept

In order to perform regression analysis using the data in Excel, follow the below steps

• Open Data Data Analysis Regression Analysis
• Select y variable range
• Select x1,x2 and X3 variable ranges
• Select Output location
• Click OK

You will get an output as below

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.955968
R Square	0.913875
Adjusted R Square	0.870813
Standard Error	248.1276
Observations	10
ANOVA
	df	SS	MS	F	Significance F
Regression	3	3919756	1306585	21.22207	0.001351508
Residual	6	369403.8	61567.3
Total	9	4289160
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	803.4015	341.9815	2.349254	0.057117	-33.3970225	1640.2
Income	0.012098	0.004922	2.457938	0.049257	5.42723E-05	0.024142
Family Size	324.4672	97.84777	3.31604	0.016083	85.04229531	563.892
Senior Parent	-19.1374	179.2733	-0.10675	0.918467	-457.8032799	419.5284

4)

The 4th regression line is

y = b1x1 + b2x2 +b3x3 +b4x4+ c

Where y= Square feet Home

b1,b2,b3,b4=slope

x1=Family Size

x2=Income

x3=Senior Parent

x4=Education

c=Intercept

In order to perform regression analysis using the data in Excel, follow the below steps

• Open Data Data Analysis Regression Analysis
• Select y variable range
• Select x1,x2 ,x3 and X4 variable ranges
• Select Output location
• Click OK

You will get an output as below

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.979802
R Square	0.960012
Adjusted R Square	0.928022
Standard Error	185.2105
Observations	10
ANOVA
	df	SS	MS	F	Significance F
Regression	4	4117645	1029411	30.00944	0.001087188
Residual	5	171514.6	34302.92
Total	9	4289160
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	1032.681	272.5312	3.78922	0.012769	332.1169712	1733.245
Income	0.018712	0.004591	4.075456	0.009582	0.006909548	0.030515
Family Size	177.188	95.36455	1.858007	0.122284	-67.9543813	422.3304
Senior Parent	-64.7315	135.1551	-0.47894	0.652192	-412.1587112	282.6958
Education	-63.9159	26.61113	-2.40185	0.06148	-132.3219608	4.49019

Analysis

• All 4 regression equations are significant at 5% significant level.
• Comparing R squared values, 4th regression equation has the highest value which is 96%. Hence this shows a very strong relationship.
• Comparing standerd errors, 4th regression equation has the lowest value which is 185.210.
• considering the significant level, Rsquared value and the standerd error terms 4th regression equation is the best fitted line.However when you consider the p value of x variables in this equation, only intercept and income are less than 0.05 which is our significant level.Hence we can only take these two variables in this regression line.
• So the final best fitted regression line is y = b2x2 + c

y=0.019x2+1032.681