This case study will utilize Excel fileReal_Estate.xlsx, which consists of 100 homes purchased in 2018.It includes variables regarding the number of bedrooms, number of bathrooms, whether the house has a pool or garage, the age, size and price of the home, what the house is constructed from, and the appraisals in 2016 and 2017.

Assigned Problem 1:It has been expressed by real estate professionals that swimming pools do not increase the value of a home.Conduct a hypothesis test for two independent samples to determine if the mean sales are different for homes with and without a pool.Use a .05 significance level.Describe your findings using proper statistical language.Note: that this problem requires an assumption that homes with a pool versus those without one are not much different otherwise; i.e., if those with a pool are in better locations, made of better materials, are newer and larger, then those homes will be worth more and it will not have anything to do with a pool.For the sake of simplicity, we will assume that the homes or without a pool have similar variability in them.

Assigned Problem 2:We would like to find out how different two real estate agents can be in their appraisals of a property.Conduct a hypothesis test for paired samples and test if there is a difference in the mean appraisal prices given by these agents on the same homes.Use a .05 significance level.Describe your findings using proper statistical language.

Assigned Problem 3:If people are going to invest in their homes by constructing them out of brick, are they going to take the plunge and install a swimming pool?Conduct a hypothesis test of proportions to determine if the proportion of homes made of brick are more likely to have a swimming pool versus homes made of other materials.Use a .05 significance level.Describe your findings using proper statistical language.

Assigned Problem 4:You might expect that homes with more bedrooms are worth more since they are probably larger, but is there more to the value; i.e., location, construction, age, etc.?Using the sample of 100 homes in the data file, conduct a hypothesis test using Analysis of Variance (ANOVA) to determine if there is a difference in the mean sale price of homes with two bedrooms versus those with three, four or five bedrooms.Use a .05 significance level.Since there are homes made of varying sizes at different locations and made of different material for this sample, it would be reasonable to assume that location and construction are not factors in this test.Describe your findings as you do on the other problems.

Due at the end of Module 5

Course Learning Outcomes: 1, 2, 3 & 4

Real_Estate.xlsx

Excel Instructions

We will make answer Excel spreadsheet to write the solution to our problems.After writing your name and Module 5 in the first two rows of the left-hand column and then skipping a row, label cell a4 as "Problem 1"and do likewise after we solve each one.Always start a hypothesis problem with stating the null and alternative hypothesis, in this case:Ho:_p= _np; Ha: _p> _npand = .05 .Find the c.v., which is a t-value with 99 degrees of freedom, 1.66.You need to find the sample mean and sample standard deviation of home with pools, those without pools and write these on you answer spreadsheet.Sorting column E will provide you with the raw information you need.We not know the population standard deviation, so, we must use a t-distribution to find the S.T.S.The standardized test statistic is

t = { (₁- ₂) - (₁-₂) } / (s₁²₁+ s₂²₂) =

Observed difference - Hypothesized difference

Standard error

This formula assumes the variances are not equal, just like when we use a normal dist.Compare this to the c.v. or calculate the p-value.State your conclusion.

For Problem 2, we need to make new column, which will be the difference between column J from K, or vice-versa.Find the mean and standard deviation of the difference column and write these on your answer spreadsheet right after stating the null and alternative hypothesis.We have only sample data therefore, we must use a t-distribution.Find the c.v. on this two-tail test, which I come up with as 1.984.Determine the standardized test statistic, t = ( - ) / (s ) , compare them, reject or fail-to-reject the null hypothesis and then state your conclusion.

In Problem 3, after stating the null and alternative hypothesis, Ho: p_b= p_o, Ha:p_b> p_o, = .05 , you may first sort column E and then sort column F.Calculate p-hat for both the proportion of brick homes with pools and the proportion of (other + frame) homes with pools.Find the c.v. for a z-distribution; i.e., z = 1.645.Test statistic is p₁- p₂, the Standardized Test Statistics is

z ={(p₁- p₂) - (p₁- p₂)} / (p q (1₁+ 1₂))

p = (x_{1 +}x₂) / (n₁+ n₂) = (n₁p_{1 +}n₂ p₂) / (n₁+ n₂) = the weighted estimate

Reject or fail-to-reject the null hypothesis and state your conclusion.

In Problem 4, we need to add the Data Analysis tool to our display.We are going to set up the Data Analysis tool under the "Data" tab.Start by going to "File", then all the way at the bottom, "Options", then on the left highlight "Add-ins" and hit "Go" not enter, highlight Analysis Data Pak and hit Go, when the pop-up window appears check Analysis Data Pak and hit OK.This will now show up under the "Data" tab and be the right most entry.For Apple computers, start by going to "Tools" and then "add-ins".On your answer spreadsheet, label four columns, two-bedroom, three-bedroom, etc.Sort column B on the data sheet.Using the ctrl c capability of your computer, copy column I for all the two-bedroom homes onto your answer spreadsheet.Do likewise for three, four and five-bedroom homes.Arrange each of these columns in ascending order; i.e., the smallest column to the largest.State your null hypothesis and alternative; i.e., Ho:All the means are the same; Ha:At least one mean is different. = .05.With Data Analysis installed, go to "ANOVA Single Factor" and fill in the Input Range.Put in the first entry on your spreadsheet:the last entry; i.e., you are sweeping your ordered array.The Output Range is where on the page you want all the information that Excel will provide.When initiated, the Between Groups shows the degrees of freedomthe number of variables minus one, the F standardized test statistic, the F critical value and the p-value.State your conclusion comparing the p-value and .

10:00 1 5G AA ythos.content.blackboardcdn.com C House_No Bedrooms Size Age Pool Construction Garage Baths SalePrice Appraisal_1 Appraisal_2 2,349 8 No Brick Yes 2 263,000 237,000 249,000 Variable descriptions 2,102 7 W N Yes Wood No 2 182,000 164,000 156,000 Bedrooms = # of bedrooms 2,271 6 Yes Brick No 2 242,000 219.000 228,000 Size = square footage of home 2,188 6 Yes Stucco No 214,000 194,000 186,000 Age = age of home in years 2,148 10 Yes Stucco No 1. 140,000 127,000 131,000 Pool = Yes or No 2,117 2 No Stucco Yes 2 245,000 223,000 216,000 Construction = Brick, Wood, Stucco 2,484 Yes Brick Yes 300,000 298,000 304,000 Garage = Yes or No 2,130 6 Yes Stucco Yes 25 272,000 249,000 244,000 Baths = # of baths 2,254 No Stucco 1.5 221,000 202.000 204,000 SalePrice = Price in 2015 10 2,385 Yes Wood 2 267,000 245,000 243,000 Appraisal_14 = 2014 Appraisal 2,108 Yes Brick Yes 2 292,000 269,000 282,000 Appraisal_15 = 2015 Appraisal NO -UA WN 1,715 Yes Wood 1 . 209,000 193,000 183,000 2.495 Yes Wood Yes 2 271,000 250,000 260,000 2,073 Yes Brick Yes 246,000 228,000 219,000 2,283 Yes Brick No 194,000 180,000 185,000 2,119 Yes Stucco Yes 281,000 261,000 253,000 2, 189 No Brick No 173,000 161,000 164,000 2,316 C - N- No Wood No 207,000 193,000 189,000 2,220 No Wood Yes 199,000 186,000 188,000 1,901 No Wood Yes 209,000 196,000 194,000 2,624 Yes Wood Yes 252,000 237,000 249,000 1,938 No Stucco Yes 2.5 193,000 182,000 173,000 101 Yes Brick No 1 .5 209,000 197,000 205,000 2, 141 Yes Brick Yes 3 320,000 309,000 318,000 2, 198 No Brick Yes 1 . 173,000 164,000 159,000 1,912 Yes Wood No 187,000 178,000 2, 117 Yes Wood Yes N N 257,000 45,000 240,000 2.162 10 Yes Brick Yes 1 . 233,000 223,000 225,000 2,041 Yes Brick No 180,000 172,000 170,000 1,712 Yes Brick Yes 234,000 225,000 236,000 1,974 Brick Yes 207,000 199,000 189,000 2,438 Stucco Yes U NNNNNN 248,000 239,000 249,000 2,019 Stucco Yes 166,000 160,000 154.000 1.919 Brick Yes 177,000 171,000 176,000 2,023 Wood No 183,000 178,000 173,00 2,310 Stucco No 216,000 210,000 214,000 2.639 Brick Yes 2 .5 312,000 304,000 298,000 2,069 Brick Yes 200,000 195,000 197,000 2,182 Stucco Yes 73,000 267,000 264,000 2,090 No Brick No 1 .5 206,000 202,000 212,000 1,928 No Stucco Yes 1 .5 232,000 228,000 217,000 2,056 Stucco Yes 198,000 195,000 203,000 2,012 No Wood No 2 205,000 202,000 194,000 2,262 No Wood Yes 176,000 174,000 179,000 W N N 2,431 No Stucco Yes 308,000 305,000 296,000 2,217 Yes Brick Yes 269,000 267,000 272,000 2,157 Yes Stucco Yes 225,000 224,000 220,000 2.014 No No INN Wood 172,000 171,000 173,000 2,221 Yes Stucco Yes 217,000 217,000 215,000 2,236 Stucco No 193,000 193.000 203,000 U AA UW OUT A A C W C A W N A N A A W N O N N N W A A N W N N A W A W CI N N CI O A W W A CI CI W W J J W W W CI W A W A W W J W C A A N W U N N N W N N N CO A A C W CI A W N A U N A AW NUNNNWA 2, 189 Brick Yes 236,000 236,000 224,000 2,218 Yes Brick No 172,000 173,000 180,000 1.937 Yes Stucco Yes 251,000 253,000 243,000 2,296 D O - Yes Brick Yes 246,000 248,000 255,000 1,749 No Stucco No N N W I 147,000 148,000 144,000 ,230 10 Yes Stucco Yes 176,000 178,000 182,000 2,263 10 Yes Brick Yes 228,000 231,000 226,000 1.593 9 No Brick No 166,000 169.000 171.000 2,221 Stucco Yes 189,000 192,000 190,000 2,036 Brick Yes 290,000 296,000 281,000 2,170 No Wood Yes 2.5 270,000 276,000 287,000 2.007 Yes Stucco No 2 154,000 158,000 152.000 2,054 Yes Brick Yes 222,000 228,000 235,000 2,247 No Stucco Yes 210,000 216,000 210,000 2, 190 Brick 191,000 197,000 201,000 NNNNNN 2,495 No Brick Yes 254,000 263,000 258,000 2,080 No Stucco No 207,000 214,000 216,000 2.210 Stucco Yes 210,000 218,000 216,000 2, 133 Stucco Yes 2. 294,000 306,000 321,000 2,037 Brick No 176,000 183,000 174,000 1,900 Stucco Yes 2 224,000 234,000 225,000 ,871 Yes Wood No 1 . 125,000 131,000 135,000 2,584 No Brick Yes 237,000 249,000 242,000 2,312 Wood No 164,000 173,000 176,000 2,498 Yes Brick No 218,000 230,000 225,000 2,407 Yes Stucco No 2. 192,000 203,000 205,000 2,363 Yes Stucco No 1 .5 126,000 133,000 132,000 2,329 No Stucco Yes 221,000 234,000 246,000 2,732 Yes Brick Yes 295,000 13,000 297,000 2,343 Stucco Yes 2 .5 245,000 261,000 271,000 2,479 NW CO Stucco No 1.5 199,000 212,000 204,000 2,624 Wood Yes 2 240,000 256,00 264,000 2,319 Brick Yes 2 263,000 281,000 273,000 1,887 Yes Wood Yes 1 . 188,000 202.000 206,000 2,280 Yes Brick Yes 221,000 238,000 240,000 2,511 Yes Brick No 175,000 189,000 187,000 2,331 Yes Stucco Yes 253,000 273,000 287,000 2,408 No Brick No 155,000 168,000 160.000 2,548 Wood No 187,000 203,000 211,000 2,442 Wood Yes 179,000 194,000 186,000 2,091 Wood Yes UN N NA 188,000 205,000 211,000 2,886 Wood Yes 227,000 247,000 240,000 2, 132 Stucco Yes 174,000 190,000 194,000 1,311 Brick No 188,000 206,000 202,000 ONANNO 2,35 Brick 3 294,000 323,000 320,000 :442 Yes Yes Wood Yes 179,000 97,000 207.000 ,09 Stucco Yes 2 .5 188,000 207,000 197,000 Yes Brick No 1.5 227,000 251,000 261,000 Wood 2.311 Yes Yes 174,000 192,000 184,000 Brick 188,000 208,000 214,000