Question

You work for a Realtor agency and part of the agency's job is to advise clients on the asking price. Based on your experience you know that buyers tend to focus on the size of the house, the number of bedrooms, number of bathrooms, and the age of house. You take a sample from the hundreds of houses that the agency sold over the past six months and plan to run a regression analysis in order to find out which variables significantly impact price and how. regression analysis in order to hn oms, and the age of (1) State the null and alternative hypothesis for each M (2) Explain how you expect each V to impact the DV (3) Conduct regression analysis, find the final model in which only significant Vs remain. Use the Data for After Class Assignment Excel file. (4) Recommend an asking price for a client whose house is 1800 squared feet, has 3 bedrooms, 2 bathrooms, and was constructed in 1980

data:

Price	Size	Bedrooms	Baths	Age
$235,000.00	1,530	3	2	6
$375,000.00	2,380	4	3	43
$199,950.00	720	2	1	2
$258,000.00	1,040	2	2	40
$96,500.00	484	1	1	43
$237,000.00	1,584	3	3	23
$829,000.00	2,701	5	3	7
$200,000.00	952	2	2	18
$328,500.00	1,098	3	3	75
$365,000.00	2,004	3	2	35
$116,000.00	640	2	1	41
$885,000.00	3,849	6	4	5
$250,000.00	2,010	3	2	84
$165,000.00	575	2	1	32
$159,900.00	984	2	2	43
$275,000.00	864	3	2	70
$144,900.00	1,330	3	2	73
$165,000.00	575	2	1	32
$175,000.00	575	2	1	32
$184,500.00	768	3	1	60
$115,000.00	575	1	1	40
$180,000.00	1,044	3	1	42
$140,000.00	720	2	1	40
$165,000.00	1,144	2	1	88
$149,900.00	831	3	1.5	71
$109,900.00	637	1	1	42

Answer 1

(1) The null and alternative hypotheses for each independent variable are (2) We expected the Size will increase the Price, Bedrooms will increase the price and Age will decrease the price (3) The procedure in Excel to run regression analysis is as written below: 1. Enter the data in Excel 2. Click on Data->Data Analysis->Regression->OK 3. Enter the input and output as shown below:

Regression Input OK Input Y Range: SAS1:SAS27 Cancel Input X Range: SBS1:SES27 Help □ Constant is Zero | 95 '] Labels Confidence Level: | 96 Output options SGS2 Output Range: O New Worksheet Ply: O New Workbook Residuals Residuals Standardized ResidualsLine Fit Plots □ Residual Plots Normal Probability Normal Probability Plots 4. Click on OK. The output will be

Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations 0.927442881 0.860150297 0.833512258 78981.24489 26 ANOVA Significance MS Regression Residual Total 4 8.05712E+11 2.01428E+11 32.29030134 1.07353E-08 21 25 1.30999E+11 6238037045 9.36711E+11 P-value Coefficients Standard Error 21585.28829 96.14250972 49.872473041.92776704 0.0675174947.572975676 199.8579951 Lower 95% Upper 95% t Stat Intercept Size Bedrooms Baths 55833.6739 -0.38659982 0.702942026 -137697.7695 94527.19295 72691.827 31200.00774 2.329865672 0.029869391 7807.858955 137575.795 16831.05096 34396.09172 0.489330332 0.629679983-54699.53758 88361.6395 1548.333819 677.6278716-2.284932312 0.032820489 -2957.538123-139.1295158 The regression equation is -21585.29 +96.14x Size 72691.83x Bedrooms 16831.05x Baths -1548.33x Age If we use a significance level of 5% then the significant independent variables are Bedrooms and Age The procedure in Excel to re-run regression analysis for bedrooms and age only is as written below 1. Enter the data in Excel for Price, Bedrooms, and Age only 2. Click on Data->Data Analysis->Regression->OK 3. Enter the input and output as shown below

Regression Input OK Input Y Range: Input X Range Labels SAS1:SAS27 SBS1:SCS27 Constant is Zero Cancel Help Confidence Level: 95 196 Output options O Qutput Range: O New Worksheet Ply: O New Workbook Residuals SES2 Residuals Residual Plots □ Line Fit Plots □ Standardized Residuals Normal Probability Normal Probability Plots

4. Click on OK. The output will be Regression Statistics MultipleR R Square Adjusted R Square Standard Error Observations 0.904867503 0.818785198 0.803027389 85908.42651 ANOVA Significance F MS Regression Residual Total 2 7.66965E+11 3.83E+11 51.9606 2.94596E-09 23 1.69746E+11 7.38E+09 25 9.36711E+11 Coefficients Standard Error P-value Lower 95% Ue, 95% tstat Intercept Bedrooms -42851.65713 57224.85802-0.74883 0.46155 -161230.2952 75526.98091 15420.0728 9.204324 3.57E-09 110032.4975 173830.1994 141931.3485 1870.412706 721.9169894 2.5909 0.016339 -3363.811781 -377.0136324 The regression equation is Price--42851.66+141931.35x Bedrooms-187041x Age (4) The age will be 38 years if the house was constructed in 1980 considering 2018 as the last year. The prediction of asking price is -21585.29 +96.14x180072691.83x3 16831.05x2-1548.33x38 Price - 4.6x 1010