Excel Assignment – QMB 210

zozrqb
timer Asked: May 24th, 2018

Question Description

All the Details and steps are in the Excel assignment QMB 210 Summer 2018 PDF all step must be followed as to whats stated in the QMB 210 PDF attached. Assignment is In EXCEL FORMAT!

Unformatted Attachment Preview

Excel Assignment – QMB 210 – Summer 2018 Due: No later than June 3, 2018 at midnight (NOTE: Assignments submitted after the due date will NOT be graded and will receive a zero). You have been contracted to analyze a data set and write a report to the client describing the data. Your assignment consists of three parts: In the first part, you must prepare and clean a sample of real estate data from Zillow.com for analysis. In the second part, you are to complete an Excel‐based analysis of the data set. The data file can be found on the course Blackboard site. When you create tables and graphs, be sure to follow the guidelines for developing proper graphs and tables as discussed in class and in the textbook. All tables and graphs should be properly labeled and have suitable titles. In the final part you need to write a report where you interpret the results of the analysis. Save the report as a Microsoft Word document and turn it in together with the Excel file with your analysis. Part 1: Data Preparation and Cleaning (10 points) 1. Open Zillow Real Estate Data file in Microsoft Excel from the course BlackBoard site and save it with the following naming convention: “last name, first name”. Failure to save the file with the appropriate naming convention will result in a deduction of points. 2. There is always a chance of having duplicate rows so check for duplicates and remove duplicate rows 3. Rename this tab containing your data set as “1-Data” and color the tab Yellow. Part 2: Data Analysis (50 points) 1. Calculate the price per square foot for each home in your data set.  Insert a new column between the “Sqft” and “Lot Size” columns, be sure to label the new column with “Price/Sqft”  For each home in the data set, compute the price per square foot by dividing the price by the square footage (sqft). You must use Excel formulas to calculate these values, do not type the numbers by hand. 2. Compute the descriptive statistics (mean, median, mode, variance, standard deviation and kurtosis, skewness, range, min, max, sum count) for Price, Beds, Baths, Sqft and Price/Sqft  The results should appear on a new worksheet called “2-Descriptives” with a Red tab.  Organize the results in the worksheet such that the name of the statistics (mean, median, mode, etc.) appear in column A and the values of the statistic’s for each variable appear in subsequent columns.  The values of all statistics must be in numeric format with two decimal places. 3. Pick any three tables and /or charts (such as a pie chart, bar chart, frequency table, etc.) from Chapter 2 in your text book that effectively illustrate /summarize dissimilar aspects of the data. In other words, create three different types of tables or charts for different variables in the data. Be sure to follow the guidance for developing good graphs and tables with proper titles and labels as discussed in class.  Your tables and charts will appear on a worksheet called “3-Charts” with a Green tab. 4. Analyze the normality (presence of outliers) of each numerical / quantitative variable in the data set. This can be done by evaluating the proximity of the variables mean versus median and mode.  Rename the worksheet to “4-Normality” with a Brown tab. 5. Create a Pivot table on a new worksheet for variables Price, Lot size and Fireplace, where you use Price as the report variable, Lot Size as row label and Pool as value. Do not filter the data.  Name the worksheet to “5-Pivot” with a tab colored Black. 6. Construct a 95% confidence interval estimate for the population characteristics for the variables Price and Sqft.  The confidence interval results will appear on a new worksheet named “6‐ Confidence Intervals” with the tab colored Orange. 7. Provide two hypothesis tests: one for the Price variable where you use a two tail test with H0 being 250,000 and level of significance 0.05 and one for the Sqft variable where you use a two tail test with H0 being 1,500 and level of significance 0.05  Hypothesis tests appear on a worksheet named “7‐Hypotheses” with a Blue tab. 8. Conduct a simple linear regression where you check whether price of the homes is affected by having a fireplace or not. Provide a scatterplot with the regression line.     We need to create dummy variables for the fireplace variable. Add one column to the right of fireplace and create a dummy variable in a new column where 0=no and 1=yes You must use the Excel function “=IF()” to create dummy variables Be sure to label the column with “Fireplace dummy” The regression equation and scatterplot will appear on a new worksheet named “8‐ Regression & Scatterplot”. Color the tab Purple. ➢ Reorder the worksheets in your Excel spreadsheet to appear in chronological order from 1 to 8. Part 3: Summary (40 points) You will now create a formal report to the client (approximately 500 words but not more than 1,000) which must be written in Microsoft Word with the following naming convention: “last name, first name”. The report should be professional in appearance and have proper spelling and correct grammar. It will contain and answer the following: First, describe the dataset in detail for the client. Next, summarize the type of information (i.e. the variables) contained in the data (i.e. what is the dataset capturing?) Use vocabulary and concepts found in Chapter 1 in your text book. Describe and include the three charts you created in step 3 as well as a simple caption for each table and chart that summarizes what is being illustrated. Finally, integrate and fully describe and interpret the results of analysis in step 5, 6, 7, and 8. From your developed regression equation in step 8, interpret the coefficients and the meaning of r-squared. Compose a summary paragraph where you indicate your recommendations to the client for properties in the area. This should be in your own words and share your recommendation with the client. ➢ This is an individual assignment – all students must work independently. ➢ Academic integrity rules as per the syllabus will apply! ➢ Before seeking help from your instructor, use the Excel Help functions, Excel reference guides, Atomic Learning videos posted to Blackboard, or Excel references on the internet. ➢ In class we also went over how to use Excel functions for each item. ➢ One purpose of the assignment is to teach you how to find help for Excel on your own so please review these sources before seeking assistance from your instructor. Excel Assignment – QMB 210 – Summer 2018 Due: No later than June 3, 2018 at midnight (NOTE: Assignments submitted after the due date will NOT be graded and will receive a zero). You have been contracted to analyze a data set and write a report to the client describing the data. Your assignment consists of three parts: In the first part, you must prepare and clean a sample of real estate data from Zillow.com for analysis. In the second part, you are to complete an Excel‐based analysis of the data set. The data file can be found on the course Blackboard site. When you create tables and graphs, be sure to follow the guidelines for developing proper graphs and tables as discussed in class and in the textbook. All tables and graphs should be properly labeled and have suitable titles. In the final part you need to write a report where you interpret the results of the analysis. Save the report as a Microsoft Word document and turn it in together with the Excel file with your analysis. Part 1: Data Preparation and Cleaning (10 points) 1. Open Zillow Real Estate Data file in Microsoft Excel from the course BlackBoard site and save it with the following naming convention: “last name, first name”. Failure to save the file with the appropriate naming convention will result in a deduction of points. 2. There is always a chance of having duplicate rows so check for duplicates and remove duplicate rows 3. Rename this tab containing your data set as “1-Data” and color the tab Yellow. Part 2: Data Analysis (50 points) 1. Calculate the price per square foot for each home in your data set.  Insert a new column between the “Sqft” and “Lot Size” columns, be sure to label the new column with “Price/Sqft”  For each home in the data set, compute the price per square foot by dividing the price by the square footage (sqft). You must use Excel formulas to calculate these values, do not type the numbers by hand. 2. Compute the descriptive statistics (mean, median, mode, variance, standard deviation and kurtosis, skewness, range, min, max, sum count) for Price, Beds, Baths, Sqft and Price/Sqft  The results should appear on a new worksheet called “2-Descriptives” with a Red tab.  Organize the results in the worksheet such that the name of the statistics (mean, median, mode, etc.) appear in column A and the values of the statistic’s for each variable appear in subsequent columns.  The values of all statistics must be in numeric format with two decimal places. 3. Pick any three tables and /or charts (such as a pie chart, bar chart, frequency table, etc.) from Chapter 2 in your text book that effectively illustrate /summarize dissimilar aspects of the data. In other words, create three different types of tables or charts for different variables in the data. Be sure to follow the guidance for developing good graphs and tables with proper titles and labels as discussed in class.  Your tables and charts will appear on a worksheet called “3-Charts” with a Green tab. 4. Analyze the normality (presence of outliers) of each numerical / quantitative variable in the data set. This can be done by evaluating the proximity of the variables mean versus median and mode.  Rename the worksheet to “4-Normality” with a Brown tab. 5. Create a Pivot table on a new worksheet for variables Price, Lot size and Fireplace, where you use Price as the report variable, Lot Size as row label and Pool as value. Do not filter the data.  Name the worksheet to “5-Pivot” with a tab colored Black. 6. Construct a 95% confidence interval estimate for the population characteristics for the variables Price and Sqft.  The confidence interval results will appear on a new worksheet named “6‐ Confidence Intervals” with the tab colored Orange. 7. Provide two hypothesis tests: one for the Price variable where you use a two tail test with H0 being 250,000 and level of significance 0.05 and one for the Sqft variable where you use a two tail test with H0 being 1,500 and level of significance 0.05  Hypothesis tests appear on a worksheet named “7‐Hypotheses” with a Blue tab. 8. Conduct a simple linear regression where you check whether price of the homes is affected by having a fireplace or not. Provide a scatterplot with the regression line.     We need to create dummy variables for the fireplace variable. Add one column to the right of fireplace and create a dummy variable in a new column where 0=no and 1=yes You must use the Excel function “=IF()” to create dummy variables Be sure to label the column with “Fireplace dummy” The regression equation and scatterplot will appear on a new worksheet named “8‐ Regression & Scatterplot”. Color the tab Purple. ➢ Reorder the worksheets in your Excel spreadsheet to appear in chronological order from 1 to 8. Part 3: Summary (40 points) You will now create a formal report to the client (approximately 500 words but not more than 1,000) which must be written in Microsoft Word with the following naming convention: “last name, first name”. The report should be professional in appearance and have proper spelling and correct grammar. It will contain and answer the following: First, describe the dataset in detail for the client. Next, summarize the type of information (i.e. the variables) contained in the data (i.e. what is the dataset capturing?) Use vocabulary and concepts found in Chapter 1 in your text book. Describe and include the three charts you created in step 3 as well as a simple caption for each table and chart that summarizes what is being illustrated. Finally, integrate and fully describe and interpret the results of analysis in step 5, 6, 7, and 8. From your developed regression equation in step 8, interpret the coefficients and the meaning of r-squared. Compose a summary paragraph where you indicate your recommendations to the client for properties in the area. This should be in your own words and share your recommendation with the client. ➢ This is an individual assignment – all students must work independently. ➢ Academic integrity rules as per the syllabus will apply! ➢ Before seeking help from your instructor, use the Excel Help functions, Excel reference guides, Atomic Learning videos posted to Blackboard, or Excel references on the internet. ➢ In class we also went over how to use Excel functions for each item. ➢ One purpose of the assignment is to teach you how to find help for Excel on your own so please review these sources before seeking assistance from your instructor. Excel Assignment – QMB 210 – Summer 2018 Due: No later than June 3, 2018 at midnight (NOTE: Assignments submitted after the due date will NOT be graded and will receive a zero). You have been contracted to analyze a data set and write a report to the client describing the data. Your assignment consists of three parts: In the first part, you must prepare and clean a sample of real estate data from Zillow.com for analysis. In the second part, you are to complete an Excel‐based analysis of the data set. The data file can be found on the course Blackboard site. When you create tables and graphs, be sure to follow the guidelines for developing proper graphs and tables as discussed in class and in the textbook. All tables and graphs should be properly labeled and have suitable titles. In the final part you need to write a report where you interpret the results of the analysis. Save the report as a Microsoft Word document and turn it in together with the Excel file with your analysis. Part 1: Data Preparation and Cleaning (10 points) 1. Open Zillow Real Estate Data file in Microsoft Excel from the course BlackBoard site and save it with the following naming convention: “last name, first name”. Failure to save the file with the appropriate naming convention will result in a deduction of points. 2. There is always a chance of having duplicate rows so check for duplicates and remove duplicate rows 3. Rename this tab containing your data set as “1-Data” and color the tab Yellow. Part 2: Data Analysis (50 points) 1. Calculate the price per square foot for each home in your data set.  Insert a new column between the “Sqft” and “Lot Size” columns, be sure to label the new column with “Price/Sqft”  For each home in the data set, compute the price per square foot by dividing the price by the square footage (sqft). You must use Excel formulas to calculate these values, do not type the numbers by hand. 2. Compute the descriptive statistics (mean, median, mode, variance, standard deviation and kurtosis, skewness, range, min, max, sum count) for Price, Beds, Baths, Sqft and Price/Sqft  The results should appear on a new worksheet called “2-Descriptives” with a Red tab.  Organize the results in the worksheet such that the name of the statistics (mean, median, mode, etc.) appear in column A and the values of the statistic’s for each variable appear in subsequent columns.  The values of all statistics must be in numeric format with two decimal places. 3. Pick any three tables and /or charts (such as a pie chart, bar chart, frequency table, etc.) from Chapter 2 in your text book that effectively illustrate /summarize dissimilar aspects of the data. In other words, create three different types of tables or charts for different variables in the data. Be sure to follow the guidance for developing good graphs and tables with proper titles and labels as discussed in class.  Your tables and charts will appear on a worksheet called “3-Charts” with a Green tab. 4. Analyze the normality (presence of outliers) of each numerical / quantitative variable in the data set. This can be done by evaluating the proximity of the variables mean versus median and mode.  Rename the worksheet to “4-Normality” with a Brown tab. 5. Create a Pivot table on a new worksheet for variables Price, Lot size and Fireplace, where you use Price as the report variable, Lot Size as row label and Pool as value. Do not filter the data.  Name the worksheet to “5-Pivot” with a tab colored Black. 6. Construct a 95% confidence interval estimate for the population characteristics for the variables Price and Sqft.  The confidence interval results will appear on a new worksheet named “6‐ Confidence Intervals” with the tab colored Orange. 7. Provide two hypothesis tests: one for the Price variable where you use a two tail test with H0 being 250,000 and level of significance 0.05 and one for the Sqft variable where you use a two tail test with H0 being 1,500 and level of significance 0.05  Hypothesis tests appear on a worksheet named “7‐Hypotheses” with a Blue tab. 8. Conduct a simple linear regression where you check whether price of the homes is affected by having a fireplace or not. Provide a scatterplot with the regression line.     We need to create dummy variables for the fireplace variable. Add one column to the right of fireplace and create a dummy variable in a new column where 0=no and 1=yes You must use the Excel function “=IF()” to create dummy variables Be sure to label the column with “Fireplace dummy” The regression equation and scatterplot will appear on a new worksheet named “8‐ Regression & Scatterplot”. Color the tab Purple. ➢ Reorder the worksheets in your Excel spreadsheet to appear in chronological order from 1 to 8. Part 3: Summary (40 points) You will now create a formal report to the client (approximately 500 words but not more than 1,000) which must be written in Microsoft Word with the following naming convention: “last name, first name”. The report should be professional in appearance and have proper spelling and correct grammar. It will contain and answer the following: First, describe the dataset in detail for the client. Next, summarize the type of information (i.e. the variables) contained in the data (i.e. what is the dataset capturing?) Use vocabulary and concepts found in Chapter 1 in your text book. Describe and include the three charts you created in step 3 as well as a simple caption for each table and chart that summarizes what is being illustrated. Finally, integrate and fully describe and interpret the results of analysis in step 5, 6, 7, and 8. From your developed regression equation in step 8, interpret the coefficients and the meaning of r-squared. Compose a summary paragraph where you indicate your recommendations to the client for properties in the area. This should be in your own words and share your recommendation with the client. ➢ This is an individual assignment – all students must work independently. ➢ Academic integrity rules as per the syllabus will apply! ➢ Before seeking help from your instructor, use the Excel Help functions, Excel reference guides, Atomic Learning videos posted to Blackboard, or Excel references on the internet. ➢ In class we also went over how to use Excel functions for each item. ➢ One purpose of the assignment is to teach you how to find help for Excel on your own so please review these sources before seeking assistance from your instructor. Address 11 Sunview Blvd 119 Andre Mar Dr 120 Gulf Island Dr 121 Falkirk St 12208 Siesta Dr 126 Madison Ct 137 Madison Ct 165 Flamingo St 17500 Canal Cove Ct 17550 Bryan Ct 18195 Cutlass Dr 18196 Cutlass Dr 19 Sunview Blvd 190 Primo Dr 2 Sunview Blvd 21083 Saint Peters Dr 211 Redfish Rd 21540 Indian Bayou Dr 227 Mango St 239 Driftwood Ln 240 Pearl St 241 Randy Ln 248 Dundee Rd 251 Albatross St 2518 Estero Blvd 253 Palermo Cir 261 Randy Ln 263 Palermo Cir 282 Ohio Ave 30 Bayview Blvd 301 Lenell Rd # 9A 3051 Shell Mound Blvd 3081 Shell Mound Blvd 3190 Shell Mound Blvd 331 Seminole Way 340 Randy Ln 405 Washington Ct 450 Lazy Way 4841 Coral Rd 490 Randy Ln 5 Glenview Manor Dr 5175 Williams Dr 518 Carlos Cir 521 Estero Blvd 521 Estero Blvd City Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa State Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Date Sold 5/21/2017 6/27/2017 7/9/2017 5/28/2017 7/31/2017 5/9/2017 6/17/2017 5/20/2017 8/18/2017 8/1/2017 7/24/2017 7/24/2017 5/16/2017 7/7/2017 5/7/2017 6/18/2017 6/25/2017 5/30/2017 5/28/2017 7/18/2017 6/30/2017 8/7/2017 5/9/2017 6/19/2017 5/6/2017 5/21/2017 6/5/2017 8/20/2017 5/30/2017 7/30/2017 6/20/2017 8/19/2017 6/26/2017 6/5/2017 5/28/2017 8/7/2017 8/25/2017 5/6/2017 5/6/2017 8/7/2017 7/30/2017 5/20/2017 7/2/2017 6/17/2017 6/17/2017 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Price 470,000 390,000 497,000 340,000 390,000 275,000 280,000 324,000 340,000 300,000 400,000 400,000 655,000 800,000 412,000 429,000 365,000 510,000 500,000 529,000 314,500 535,000 455,000 435,000 295,000 588,000 485,000 425,000 415,000 775,000 450,000 290,000 360,000 300,000 450,000 610,000 380,000 349,000 328,000 610,000 537,500 600,000 420,000 365,000 365,000 Zestimate $ 482,000 $ 363,000 $ 428,000 $ 365,000 $ 423,000 $ 279,000 $ 342,000 $ 393,000 $ 337,000 $ 350,000 $ 596,000 $ 377,000 $ 644,000 $ 720,000 $ 416,000 $ 420,000 $ 388,000 $ 657,000 $ 235,000 $ 605,000 $ 319,000 $ 532,000 $ 452,000 $ 417,000 $ 388,000 $ 488,000 $ 495,000 $ 484,000 $ 424,000 $ 707,000 $ 460,000 $ 375,000 $ 365,000 $ 303,000 $ 472,000 $ 606,000 $ 392,000 $ 366,000 $ 335,000 $ 641,000 $ 510,000 $ 652,000 $ 441,000 $ 262,000 $ 262,000 5340 Williams Dr 5700 Lauder St 574 Estero Blvd 6500 Estero Blvd # F116 7390 Estero Blvd APT 802 8155 Lagoon Rd Tampa Tampa Tampa Tampa Tampa Tampa Florida Florida Florida Florida Florida Florida 6/3/2017 5/28/2017 7/3/2017 7/1/2017 5/29/2017 6/2/2017 $ $ $ $ $ $ 523,000 492,400 940,000 330,000 370,000 645,000 $ $ $ $ $ $ 534,000 424,000 920,000 305,000 396,000 599,000 Beds Baths 3 3 3 2 3 2 3 4 3 3 2 2 3 4 3 3 3 4 3 3 3 2 2 3 3 4 3 3 4 3 3 2 2 3 3 3 3 2 3 3 3 3 2 2 2 3.0 2.0 2.0 2.0 3.0 2.0 2.5 3.0 2.0 2.5 2.0 2.0 2.0 3.0 2.0 3.0 2.0 4.0 2.0 2.5 2.5 2.0 2.0 2.0 2.0 2.0 2.0 2.0 3.0 2.0 2.0 2.0 2.0 2.0 2.0 3.0 2.0 2.0 2.0 2.0 2.0 3.0 2.0 2.0 2.0 Sqft 1421 1600 1416 960 1625 1172 1930 2160 1549 2005 1960 1536 1458 2082 1323 1582 1584 2190 1357 1900 800 1317 1659 1808 1056 1612 1623 1788 840 1632 1623 1321 1444 1778 1592 1832 1395 1050 1260 2407 1584 2079 1200 784 784 Lot Size 8276 8286 7405 8276 7318 5227 10018 7086 7405 6534 9374 9583 9147 6150 8276 7700 17860 16988 5662 15246 3920 11761 6969 6098 4356 6098 14810 12197 7020 7840 6177 8276 7362 6534 11761 10454 7840 8712 5662 11326 9583 9147 7405 1742 1742 Fireplace Yes Yes Yes Yes No No No No Yes Yes No No Yes Yes Yes No Yes Yes No Yes Yes No Yes Yes Yes Yes Yes Yes Yes No Yes Yes Yes No No No Yes No No No Yes No No No Yes Stories 1 1 1 1 2 1 1 1 1 1 1 2 1 1 1 1 1 1 2 2 1 1 1 2 1 1 2 1 1 1 1 1 2 2 2 1 1 1 2 1 1 2 2 1 1 4 4 2 2 2 3 2.0 3.0 1.5 2.0 2.0 2.0 2171 1746 768 1055 1061 2200 12197 4904 14375 8537 8137 9147 Yes Yes Yes No No No 1 1 2 2 1 2 Excel Assignment – QMB 210 – Summer 2018 Due: No later than June 3, 2018 at midnight (NOTE: Assignments submitted after the due date will NOT be graded and will receive a zero). You have been contracted to analyze a data set and write a report to the client describing the data. Your assignment consists of three parts: In the first part, you must prepare and clean a sample of real estate data from Zillow.com for analysis. In the second part, you are to complete an Excel‐based analysis of the data set. The data file can be found on the course Blackboard site. When you create tables and graphs, be sure to follow the guidelines for developing proper graphs and tables as discussed in class and in the textbook. All tables and graphs should be properly labeled and have suitable titles. In the final part you need to write a report where you interpret the results of the analysis. Save the report as a Microsoft Word document and turn it in together with the Excel file with your analysis. Part 1: Data Preparation and Cleaning (10 points) 1. Open Zillow Real Estate Data file in Microsoft Excel from the course BlackBoard site and save it with the following naming convention: “last name, first name”. Failure to save the file with the appropriate naming convention will result in a deduction of points. 2. There is always a chance of having duplicate rows so check for duplicates and remove duplicate rows 3. Rename this tab containing your data set as “1-Data” and color the tab Yellow. Part 2: Data Analysis (50 points) 1. Calculate the price per square foot for each home in your data set.  Insert a new column between the “Sqft” and “Lot Size” columns, be sure to label the new column with “Price/Sqft”  For each home in the data set, compute the price per square foot by dividing the price by the square footage (sqft). You must use Excel formulas to calculate these values, do not type the numbers by hand. 2. Compute the descriptive statistics (mean, median, mode, variance, standard deviation and kurtosis, skewness, range, min, max, sum count) for Price, Beds, Baths, Sqft and Price/Sqft  The results should appear on a new worksheet called “2-Descriptives” with a Red tab.  Organize the results in the worksheet such that the name of the statistics (mean, median, mode, etc.) appear in column A and the values of the statistic’s for each variable appear in subsequent columns.  The values of all statistics must be in numeric format with two decimal places. 3. Pick any three tables and /or charts (such as a pie chart, bar chart, frequency table, etc.) from Chapter 2 in your text book that effectively illustrate /summarize dissimilar aspects of the data. In other words, create three different types of tables or charts for different variables in the data. Be sure to follow the guidance for developing good graphs and tables with proper titles and labels as discussed in class.  Your tables and charts will appear on a worksheet called “3-Charts” with a Green tab. 4. Analyze the normality (presence of outliers) of each numerical / quantitative variable in the data set. This can be done by evaluating the proximity of the variables mean versus median and mode.  Rename the worksheet to “4-Normality” with a Brown tab. 5. Create a Pivot table on a new worksheet for variables Price, Lot size and Fireplace, where you use Price as the report variable, Lot Size as row label and Pool as value. Do not filter the data.  Name the worksheet to “5-Pivot” with a tab colored Black. 6. Construct a 95% confidence interval estimate for the population characteristics for the variables Price and Sqft.  The confidence interval results will appear on a new worksheet named “6‐ Confidence Intervals” with the tab colored Orange. 7. Provide two hypothesis tests: one for the Price variable where you use a two tail test with H0 being 250,000 and level of significance 0.05 and one for the Sqft variable where you use a two tail test with H0 being 1,500 and level of significance 0.05  Hypothesis tests appear on a worksheet named “7‐Hypotheses” with a Blue tab. 8. Conduct a simple linear regression where you check whether price of the homes is affected by having a fireplace or not. Provide a scatterplot with the regression line.     We need to create dummy variables for the fireplace variable. Add one column to the right of fireplace and create a dummy variable in a new column where 0=no and 1=yes You must use the Excel function “=IF()” to create dummy variables Be sure to label the column with “Fireplace dummy” The regression equation and scatterplot will appear on a new worksheet named “8‐ Regression & Scatterplot”. Color the tab Purple. ➢ Reorder the worksheets in your Excel spreadsheet to appear in chronological order from 1 to 8. Part 3: Summary (40 points) You will now create a formal report to the client (approximately 500 words but not more than 1,000) which must be written in Microsoft Word with the following naming convention: “last name, first name”. The report should be professional in appearance and have proper spelling and correct grammar. It will contain and answer the following: First, describe the dataset in detail for the client. Next, summarize the type of information (i.e. the variables) contained in the data (i.e. what is the dataset capturing?) Use vocabulary and concepts found in Chapter 1 in your text book. Describe and include the three charts you created in step 3 as well as a simple caption for each table and chart that summarizes what is being illustrated. Finally, integrate and fully describe and interpret the results of analysis in step 5, 6, 7, and 8. From your developed regression equation in step 8, interpret the coefficients and the meaning of r-squared. Compose a summary paragraph where you indicate your recommendations to the client for properties in the area. This should be in your own words and share your recommendation with the client. ➢ This is an individual assignment – all students must work independently. ➢ Academic integrity rules as per the syllabus will apply! ➢ Before seeking help from your instructor, use the Excel Help functions, Excel reference guides, Atomic Learning videos posted to Blackboard, or Excel references on the internet. ➢ In class we also went over how to use Excel functions for each item. ➢ One purpose of the assignment is to teach you how to find help for Excel on your own so please review these sources before seeking assistance from your instructor. Address 11 Sunview Blvd 119 Andre Mar Dr 120 Gulf Island Dr 121 Falkirk St 12208 Siesta Dr 126 Madison Ct 137 Madison Ct 165 Flamingo St 17500 Canal Cove Ct 17550 Bryan Ct 18195 Cutlass Dr 18196 Cutlass Dr 19 Sunview Blvd 190 Primo Dr 2 Sunview Blvd 21083 Saint Peters Dr 211 Redfish Rd 21540 Indian Bayou Dr 227 Mango St 239 Driftwood Ln 240 Pearl St 241 Randy Ln 248 Dundee Rd 251 Albatross St 2518 Estero Blvd 253 Palermo Cir 261 Randy Ln 263 Palermo Cir 282 Ohio Ave 30 Bayview Blvd 301 Lenell Rd # 9A 3051 Shell Mound Blvd 3081 Shell Mound Blvd 3190 Shell Mound Blvd 331 Seminole Way 340 Randy Ln 405 Washington Ct 450 Lazy Way 4841 Coral Rd 490 Randy Ln 5 Glenview Manor Dr 5175 Williams Dr 518 Carlos Cir 521 Estero Blvd 521 Estero Blvd City Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa Tampa State Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Florida Date Sold 5/21/2017 6/27/2017 7/9/2017 5/28/2017 7/31/2017 5/9/2017 6/17/2017 5/20/2017 8/18/2017 8/1/2017 7/24/2017 7/24/2017 5/16/2017 7/7/2017 5/7/2017 6/18/2017 6/25/2017 5/30/2017 5/28/2017 7/18/2017 6/30/2017 8/7/2017 5/9/2017 6/19/2017 5/6/2017 5/21/2017 6/5/2017 8/20/2017 5/30/2017 7/30/2017 6/20/2017 8/19/2017 6/26/2017 6/5/2017 5/28/2017 8/7/2017 8/25/2017 5/6/2017 5/6/2017 8/7/2017 7/30/2017 5/20/2017 7/2/2017 6/17/2017 6/17/2017 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Price 470,000 390,000 497,000 340,000 390,000 275,000 280,000 324,000 340,000 300,000 400,000 400,000 655,000 800,000 412,000 429,000 365,000 510,000 500,000 529,000 314,500 535,000 455,000 435,000 295,000 588,000 485,000 425,000 415,000 775,000 450,000 290,000 360,000 300,000 450,000 610,000 380,000 349,000 328,000 610,000 537,500 600,000 420,000 365,000 365,000 Zestimate $ 482,000 $ 363,000 $ 428,000 $ 365,000 $ 423,000 $ 279,000 $ 342,000 $ 393,000 $ 337,000 $ 350,000 $ 596,000 $ 377,000 $ 644,000 $ 720,000 $ 416,000 $ 420,000 $ 388,000 $ 657,000 $ 235,000 $ 605,000 $ 319,000 $ 532,000 $ 452,000 $ 417,000 $ 388,000 $ 488,000 $ 495,000 $ 484,000 $ 424,000 $ 707,000 $ 460,000 $ 375,000 $ 365,000 $ 303,000 $ 472,000 $ 606,000 $ 392,000 $ 366,000 $ 335,000 $ 641,000 $ 510,000 $ 652,000 $ 441,000 $ 262,000 $ 262,000 5340 Williams Dr 5700 Lauder St 574 Estero Blvd 6500 Estero Blvd # F116 7390 Estero Blvd APT 802 8155 Lagoon Rd Tampa Tampa Tampa Tampa Tampa Tampa Florida Florida Florida Florida Florida Florida 6/3/2017 5/28/2017 7/3/2017 7/1/2017 5/29/2017 6/2/2017 $ $ $ $ $ $ 523,000 492,400 940,000 330,000 370,000 645,000 $ $ $ $ $ $ 534,000 424,000 920,000 305,000 396,000 599,000 Beds Baths 3 3 3 2 3 2 3 4 3 3 2 2 3 4 3 3 3 4 3 3 3 2 2 3 3 4 3 3 4 3 3 2 2 3 3 3 3 2 3 3 3 3 2 2 2 3.0 2.0 2.0 2.0 3.0 2.0 2.5 3.0 2.0 2.5 2.0 2.0 2.0 3.0 2.0 3.0 2.0 4.0 2.0 2.5 2.5 2.0 2.0 2.0 2.0 2.0 2.0 2.0 3.0 2.0 2.0 2.0 2.0 2.0 2.0 3.0 2.0 2.0 2.0 2.0 2.0 3.0 2.0 2.0 2.0 Sqft 1421 1600 1416 960 1625 1172 1930 2160 1549 2005 1960 1536 1458 2082 1323 1582 1584 2190 1357 1900 800 1317 1659 1808 1056 1612 1623 1788 840 1632 1623 1321 1444 1778 1592 1832 1395 1050 1260 2407 1584 2079 1200 784 784 Lot Size 8276 8286 7405 8276 7318 5227 10018 7086 7405 6534 9374 9583 9147 6150 8276 7700 17860 16988 5662 15246 3920 11761 6969 6098 4356 6098 14810 12197 7020 7840 6177 8276 7362 6534 11761 10454 7840 8712 5662 11326 9583 9147 7405 1742 1742 Fireplace Yes Yes Yes Yes No No No No Yes Yes No No Yes Yes Yes No Yes Yes No Yes Yes No Yes Yes Yes Yes Yes Yes Yes No Yes Yes Yes No No No Yes No No No Yes No No No Yes Stories 1 1 1 1 2 1 1 1 1 1 1 2 1 1 1 1 1 1 2 2 1 1 1 2 1 1 2 1 1 1 1 1 2 2 2 1 1 1 2 1 1 2 2 1 1 4 4 2 2 2 3 2.0 3.0 1.5 2.0 2.0 2.0 2171 1746 768 1055 1061 2200 12197 4904 14375 8537 8137 9147 Yes Yes Yes No No No 1 1 2 2 1 2
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors