excel exercise and need the explanation and answer to help me learn.
Part 2) Data Cleaning
Your data should be on a worksheet titled “data”.
Create a duplicate of your data which you will clean:
Fix any issues with your observations. Our purpose is to make the spreadsheet ready for analysis. For example: School could be listed as Montgomery City Schools or just Montgomery. These would filter as different data points, so they should be corrected to the be the same value.
Create a new variable called age which shows the age of the house in years.
Split the address variable so you have street number/prefix, street, city, state, and zip all separate columns.
Create a new variable called city = 1 if the house is located within the City limits, 0
therwise.
Part 3) Assignment Tasks
Create a Word document named housing_report_5880_City.docx (where City is the city you are assigned) with the following information in a report format. This document should be considered a professional document that would be provided to your boss or your colleagues. It should be a minimum of 4 content pages. It should also be double-spaced, with appropriate headers and footers as you see fit.
Create a professional report that you would give your boss at your Real Estate Firm with the following contents.
Cover page with the team name (City) and team members. List what each team member was responsible for and what each worked contributed to the project.
Problem Statement: explain the purpose behind this project, why you would even want to examine these variables and this market.
Create a footer to number each page.
General Requirements:
Each section should have a heading.
The graphics / charts should be clear and flowing in the document (as well as aligned and sized correctly).
Each graphic should be readable. No tiny fonts. Do not use links.
Explanations should be consistent, clear, and complete. Do not abbreviate or use slang. Professional writing only.
Report contents:
Describe the process of exploring variables and complete the following:
Categorize and describe each variable.
Produce MIN, MAX, MEAN of each variable.
Produce boxplots of price, square feet variables. Interpret and explain your findings.
Produce scatter plots of price and square feet variables. Interpret and explain your findings.
Create a histogram of prices with 10 bins. Show the histogram. Explain what it shows.
Are there outliers in the data collected? Explain.
What are the clusters or groups that appear from the price variable?
Group each property appropriately.
What method do you use to calculate groups?
What are the general parameters of your groups (show range of price, sq feet)?
Show each group’s school zone (or zones). Does each member of each group have the same school zone?
Which property has the largest sales price?
Find the average price per square foot of homes in each of your groups and produce a table with this information.
Requirements: as above