11. Real Estate Data Analysis The Excel file labeled REAL ESTATE contains data on 100 homes sold by a large real estate company in the past three years. You have been asked to provide some analysis of the data for the company's management team Copy the data into a new worksheet in your Excel file to complete the activities described below. Use Excel functions to arrange and analyze the data as required to produce the results described below. Make Excel do all of the work. I must be able to see your functions and formulas in the EXCEL file by foggling to show formulas Report the results in your Excel file close to the analysis that was used. Make sure the work and answers are well labelled and organized so the reader can follow it easily. Use complete sentences to report answers where appropriate. 1. Count of the number of houses that have a garage and the number that do not have a garage . Use the Excel COUNTIF function (watch the podcast and if you need additional help with this or any other function use the Excel Help) 2. Create a pie chart to display the results from #l. 3. Count of the number of houses in each of the five locations Use COUNTIF function (watch the podcast and if you need additional help with this or any other function use the Excel Help) 4. Create a bar or column chart to display the results from #3 5. Create the appropriate graph to show the relationship between Price and Size (copy just the Price and Size data into a new worksheet) Regression review and preview ---Think about which variable Price or Size, is likely to be the dependent variable (the one you want to predict) and which is likely to be the independent variable (the one we use to predict the other). We want the dependent variable to be on the vertical (y) axis and the independent variable to be on the horizontal (x) axis. Excel will automatically put the first column of data on the horizontal axis of a scatterplot and the second column on the vertical axis. Organize your data accordingly to make the graph. 6. Calculate the average size of house sold and the average price (use Excel AVERAGE function) 7. Use the results from 16 to calculate the average price per square foot. K M N O P Q R Price = selling price of house in $thousands Bedrooms = number of bedrooms Size = square feet Pool = binary variable 1 = house has a pool, O = no pool Distance = miles to center of city location = what neighborhood the house is located in 1 = Mayfair 2= Blueridge 3= Roseland 4 = Avondale 5 = Wildwood Garage = binary variable 1 = house has a garage, 0 = house has no garage Baths = number of bathrooms 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 B D E 1 House Price Bedrooms Size Pool 2 263.1 4 2300 3 2 192.4 4 2100 4 3 242.1 3 2300 5 4 213.6 2 2200 6 5 149.9 2 2100 7 6 245.4 2 2100 8 7 3272 6 2500 8 271.8 2 2100 10 9 225.2 3 2300 11 10 266.6 2400 12 11 292.4 4 2100 13 12 208.5 2. 1700 14 13 270.8 6 2500 15 14 246.1 4 2100 16 15 194.4 2 2300 17 16 281.3 3 2100 18 17 182.7 4 2200 19 18 207.5 5 2300 20 19 198.9 3 2200 21 20 209.3 6 1900 22 21 252.3 4 2600 22 192.9 4 1900 24 23 209.3 5 2100 24 345.3 8 2600 26 25 326.3 6 2100 27 26 173.1 2 2200 28 27 188.4 2 1900 29 28 257.2 2 2100 30 29 235.5 3 2200 31 30 180.4 2 2000 32 31 236.5 2. 1700 33 32 208.5 2. 2000 34 33 247.7 5 2400 35 34 166.2 3 2000 36 35 177.1 . 1900 37 36 1827 2000 F G H Distance Location Garage Baths 0 17 2 1 2 0 19 4 1 2 0 12 3 0 2 0 16 2 2.5 0 28 1.5 0 12 1 2 15 3 1 2 9 2 2.5 0 18 3 0 1.5 1 13 4 2 0 14 3 2 0 8 4 1.5 7 2 18 3 1 2: 0 11 3 0 21 16 2 1 2 0 16 3 0 2 0 21 4 2.5 0 10 4 2 0 15 4 2 8 4 2 0 14 2 2.5 20 5 0 1.5 0 9 4 1 2 11 5 3 0 21 2 1 1.5 1 26 4 0 9 1 1 0 14 3 1.5 0 11 5 0 19 3 1 11 5 1 16 0 16 1 1 10 1 2 0 14 1 2.5 1 1 1 4 1 25 1 1 1 1 1 1 1 C E F H 0 1 1 4 4 6 3 5 3 1 14 19 7 19 16 UNA 0 1 1 1 1 1 0 0 0 1 0 0 0 0 0 3 2 3 2 1 4 4 2 5 1 4 1 0 1 1 1 1 0 1 0 1 0 1 1 0 - 1 1 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 B 182.7 217.9 312.1 199.8 273.2 216.9 232.2 198.3 205.1 175.6 307.8 269.2 224.8 171.6 216.8 192.6 236.4 172.4 251.4 189.4 312.1 289.8 269.9 154.3 222.1 209.7 190.9 254.3 207.5 209.7 295.2 176.3 294.3 226.2 126.3 236.8 164 1 realestate WAWUNN AWWWWWW 2000 2300 2600 2100 2200 2100 1900 2100 2000 2300 2400 2200 2200 2000 2200 2200 2200 2200 1900 2200 2400 2000 2200 2000 2100 2200 2200 2500 2100 2200 2100 2000 2400 1900 1900 2600 2300 1 0 1 0 1 1 1 2.5 2 2.5 2: 31 1.51 1.5 1.5 2 2! 3 3 2.5 2 2 2 2 2 2 2 3 3 2.5 2 2 2 2 2 2 2 2.5 2 2 2 1.5 2. 2 16 19 20 24 21 8 17 16 15 14 20 23 12 24 13 21 11 13 9 13 18 15 10 19 13 17 8 6 18 17 19 1 0 1 1 1 1 0 1 1 3 3 2 1 3 3 4 2 5 2 3 3 2 2 2 3 4 1 4 5 4 1 0 0 0 0 0 1 1 0 4 1 1 1 0 0 4 2 2 7 3 2 4 1 1 0 0 1 0 1 1 4 0 ITI F G H 0 N 1 1 1 0 0 0 1 1 73 74 75 76 77 78 79 80 81 82 83 84 1 A 72 73 74 75 76 77 78 79 80 81 82 83 84 19 12 16 28 12 15 9 18 13 14 8 7 2: 21 2.5 1.5 2 2 2.5 1.5 2 1 1 1 1 0 0 1 1 1 1 1 1 1 1 1 1 85 18 2. 2 6 2 3 4 4 2 6 4 2 3 4 5 3 6 4 4 5 8 85 0 B 164.1 217.8 192.2 125.9 220.9 294.5 244.6 201.1 241.3 263.2 188.1 243.7 221.5 176.2 253.2 155.4 186.7 180.1 188.3 227.1 173.6 188.3 311.8 294.5 181.2 188.3 227.1 183.6 188.3 86 87 88 89 90 91 92 93 D 2300 2500 2400 2400 2300 2700 2300 2500 2600 2300 1900 2700 2300 2500 2300 2400 2500 2400 2100 2900 2100 2300 2900 2400 2400 2100 2900 2100 2300 1 0 0 0 0 0 0 4 3 2 1 2 3 2 1 4 3 4 2 3 3 2 3 4 4 2 4 2 5 4 5 4 2 5 4 5 1 1 1 1 1 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 1 1.5 2 2 2 2 2 2.5 2 2 2 2.5 1.5 2 3 2 2.5 1.5 2 11 16 16 21 10 15 8 14 20 9 11 8 14 20 9 11 0 1 94 0 1 95 96 97 1 1 1 1 1 1 98 0 0 99 0 100 101 102 1 1 1 1 11. Real Estate Data Analysis The Excel file labeled REAL ESTATE contains data on 100 homes sold by a large real estate company in the past three years. You have been asked to provide some analysis of the data for the company's management team Copy the data into a new worksheet in your Excel file to complete the activities described below. Use Excel functions to arrange and analyze the data as required to produce the results described below. Make Excel do all of the work. I must be able to see your functions and formulas in the EXCEL file by foggling to show formulas Report the results in your Excel file close to the analysis that was used. Make sure the work and answers are well labelled and organized so the reader can follow it easily. Use complete sentences to report answers where appropriate. 1. Count of the number of houses that have a garage and the number that do not have a garage . Use the Excel COUNTIF function (watch the podcast and if you need additional help with this or any other function use the Excel Help) 2. Create a pie chart to display the results from #l. 3. Count of the number of houses in each of the five locations Use COUNTIF function (watch the podcast and if you need additional help with this or any other function use the Excel Help) 4. Create a bar or column chart to display the results from #3 5. Create the appropriate graph to show the relationship between Price and Size (copy just the Price and Size data into a new worksheet) Regression review and preview ---Think about which variable Price or Size, is likely to be the dependent variable (the one you want to predict) and which is likely to be the independent variable (the one we use to predict the other). We want the dependent variable to be on the vertical (y) axis and the independent variable to be on the horizontal (x) axis. Excel will automatically put the first column of data on the horizontal axis of a scatterplot and the second column on the vertical axis. Organize your data accordingly to make the graph. 6. Calculate the average size of house sold and the average price (use Excel AVERAGE function) 7. Use the results from 16 to calculate the average price per square foot. K M N O P Q R Price = selling price of house in $thousands Bedrooms = number of bedrooms Size = square feet Pool = binary variable 1 = house has a pool, O = no pool Distance = miles to center of city location = what neighborhood the house is located in 1 = Mayfair 2= Blueridge 3= Roseland 4 = Avondale 5 = Wildwood Garage = binary variable 1 = house has a garage, 0 = house has no garage Baths = number of bathrooms 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 B D E 1 House Price Bedrooms Size Pool 2 263.1 4 2300 3 2 192.4 4 2100 4 3 242.1 3 2300 5 4 213.6 2 2200 6 5 149.9 2 2100 7 6 245.4 2 2100 8 7 3272 6 2500 8 271.8 2 2100 10 9 225.2 3 2300 11 10 266.6 2400 12 11 292.4 4 2100 13 12 208.5 2. 1700 14 13 270.8 6 2500 15 14 246.1 4 2100 16 15 194.4 2 2300 17 16 281.3 3 2100 18 17 182.7 4 2200 19 18 207.5 5 2300 20 19 198.9 3 2200 21 20 209.3 6 1900 22 21 252.3 4 2600 22 192.9 4 1900 24 23 209.3 5 2100 24 345.3 8 2600 26 25 326.3 6 2100 27 26 173.1 2 2200 28 27 188.4 2 1900 29 28 257.2 2 2100 30 29 235.5 3 2200 31 30 180.4 2 2000 32 31 236.5 2. 1700 33 32 208.5 2. 2000 34 33 247.7 5 2400 35 34 166.2 3 2000 36 35 177.1 . 1900 37 36 1827 2000 F G H Distance Location Garage Baths 0 17 2 1 2 0 19 4 1 2 0 12 3 0 2 0 16 2 2.5 0 28 1.5 0 12 1 2 15 3 1 2 9 2 2.5 0 18 3 0 1.5 1 13 4 2 0 14 3 2 0 8 4 1.5 7 2 18 3 1 2: 0 11 3 0 21 16 2 1 2 0 16 3 0 2 0 21 4 2.5 0 10 4 2 0 15 4 2 8 4 2 0 14 2 2.5 20 5 0 1.5 0 9 4 1 2 11 5 3 0 21 2 1 1.5 1 26 4 0 9 1 1 0 14 3 1.5 0 11 5 0 19 3 1 11 5 1 16 0 16 1 1 10 1 2 0 14 1 2.5 1 1 1 4 1 25 1 1 1 1 1 1 1 C E F H 0 1 1 4 4 6 3 5 3 1 14 19 7 19 16 UNA 0 1 1 1 1 1 0 0 0 1 0 0 0 0 0 3 2 3 2 1 4 4 2 5 1 4 1 0 1 1 1 1 0 1 0 1 0 1 1 0 - 1 1 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 B 182.7 217.9 312.1 199.8 273.2 216.9 232.2 198.3 205.1 175.6 307.8 269.2 224.8 171.6 216.8 192.6 236.4 172.4 251.4 189.4 312.1 289.8 269.9 154.3 222.1 209.7 190.9 254.3 207.5 209.7 295.2 176.3 294.3 226.2 126.3 236.8 164 1 realestate WAWUNN AWWWWWW 2000 2300 2600 2100 2200 2100 1900 2100 2000 2300 2400 2200 2200 2000 2200 2200 2200 2200 1900 2200 2400 2000 2200 2000 2100 2200 2200 2500 2100 2200 2100 2000 2400 1900 1900 2600 2300 1 0 1 0 1 1 1 2.5 2 2.5 2: 31 1.51 1.5 1.5 2 2! 3 3 2.5 2 2 2 2 2 2 2 3 3 2.5 2 2 2 2 2 2 2 2.5 2 2 2 1.5 2. 2 16 19 20 24 21 8 17 16 15 14 20 23 12 24 13 21 11 13 9 13 18 15 10 19 13 17 8 6 18 17 19 1 0 1 1 1 1 0 1 1 3 3 2 1 3 3 4 2 5 2 3 3 2 2 2 3 4 1 4 5 4 1 0 0 0 0 0 1 1 0 4 1 1 1 0 0 4 2 2 7 3 2 4 1 1 0 0 1 0 1 1 4 0 ITI F G H 0 N 1 1 1 0 0 0 1 1 73 74 75 76 77 78 79 80 81 82 83 84 1 A 72 73 74 75 76 77 78 79 80 81 82 83 84 19 12 16 28 12 15 9 18 13 14 8 7 2: 21 2.5 1.5 2 2 2.5 1.5 2 1 1 1 1 0 0 1 1 1 1 1 1 1 1 1 1 85 18 2. 2 6 2 3 4 4 2 6 4 2 3 4 5 3 6 4 4 5 8 85 0 B 164.1 217.8 192.2 125.9 220.9 294.5 244.6 201.1 241.3 263.2 188.1 243.7 221.5 176.2 253.2 155.4 186.7 180.1 188.3 227.1 173.6 188.3 311.8 294.5 181.2 188.3 227.1 183.6 188.3 86 87 88 89 90 91 92 93 D 2300 2500 2400 2400 2300 2700 2300 2500 2600 2300 1900 2700 2300 2500 2300 2400 2500 2400 2100 2900 2100 2300 2900 2400 2400 2100 2900 2100 2300 1 0 0 0 0 0 0 4 3 2 1 2 3 2 1 4 3 4 2 3 3 2 3 4 4 2 4 2 5 4 5 4 2 5 4 5 1 1 1 1 1 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 1 1.5 2 2 2 2 2 2.5 2 2 2 2.5 1.5 2 3 2 2.5 1.5 2 11 16 16 21 10 15 8 14 20 9 11 8 14 20 9 11 0 1 94 0 1 95 96 97 1 1 1 1 1 1 98 0 0 99 0 100 101 102 1 1 1 1