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