/n Housing Sale Data Analysis and Dashboard: Excel-Based Data Exploration, Analysis, and Dashboard Development Summary House prices and other house features for 50 cities/towns with universities are contained in the provided CSV file. Your task as an analyst/developer is to do an analysis of the data, as well as develop an interactive dashboard that allows someone to study the data in a user-friendly way. You will briefly summarize insights from your data analysis (i.e., what you learn about the housing market). Specifically, your role is as a consultant to a real estate investment firm. You have been hired with two purposes: a) to create a meaningful, interactive dashboard for the firm so they can use it as a decision tool to help them determine potential new investment areas; and b) to identify key insights about the real estate market in 2016 that will be relevant to the investment firm in their decisions. Data The data file is HousePriceData.csv. It contains sales transaction and house feature information for sales occurring in 2016. There are 50 universities/towns covered, and the file contains information on over 10,000 transactions. Fields in the file are record number (unique), sale amount, sale date, number of bedrooms, number of bathrooms, home square footage, lot square footage, type of building, year built, town (city, state), and university. The cleaning phase has essentially already been done for you, and you should be able to load the file into Excel. As noted in the Tasks section, you will be enhancing the data with one or more calculated columns. Tasks (these should all be done using native Excel tools; not Query Editor and not Power BI; portability of one's work is often important so for this homework we are sticking with native Excel tools). All sheets need meaningful name; a user generally familiar with the case/data should be able to navigate easily through your workbook and understand what you've done. 1. Download the data. Make a copy of the raw data sheet. (Always keep a copy of your original source data!) 2. Data Exploration/EDA Sheet: Exploratory Data Analysis (EDA). Explore the data by converting it into a table (give the table a meaningful name), creating slicers to make filtering the data easy, creating appropriate summary measures (overall, and based on the filtered results), and charts. Part of this is a requirement to create at least two meaningful (to the housing market) calculated columns, which essentially becomes part of your data set (use your “domain knowledge" of the housing market to know what would be pertinent information; this is intended to illustrate that as a data analyst, you are never doing analysis in a "vacuum," but always need to stay connected with the real problem situation). Pro tip: Calculated columns are often critical to developing a better understanding of the data and in formal predictive models, and you should not stop at one or two columns. Example: In a baseball data set, you might have total hits by a player and total at-bats. You would certainly want to calculate the batting average, hits/at-bats. Often, ratios are the most important calculated columns (examples: return on assets, return on investment, other financial ratios, inventory turnover, etc.); in fact, ratios are often the most important Key Performance Indicators (KPI) that businesses track. So don't skimp on this step as it may impact the quality of your insights! Like the Newmarket 2004-2015 EDA example, create slicers and summary statistics on your data sheet so the user can drill into the data according to multiple fields. You should have one Housing Sale Data Analysis and Dashboard Page 1 of 2 column of statistics that calculate over all the data, and one column that calculates based only on the filtered (showing) rows (use AGGREGATE function). As you explore the data, identify anomalies and/or what seem to be outliers, and take note of them. These are often important in the interpretation phase of analytics. 3. Multiple Pivot Tables/Charts (Separate Sheets, meaningful titles): Continue exploring the data by creating several pivot tables and associated charts. You will need to iterate as you discover interesting things about the data. Make note of interesting things you discover that you may want to note in your brief writeup. Note: There may be some desirable charts that are not possible to do with Pivot Tables. For example, you cannot do a scatter plot (XY chart) with Pivot Tables, but they are a key chart type when looking at relationships between two variables. You can create a scatter chart based directly on the data in the Data/EDA sheet and filter it using the slicers on that sheet. 4. Sketch out a dashboard ON PAPER. Think of useful charts and KPIs (key performance indicators, numeric values to help summarize a key metric). Use this as a guide in your real dashboard development. As you do the analysis, you might deviate a bit from this sketch, but this gives you a reference point as to what you thought would be important. 5. Dashboard. Create and customize the needed Pivot Tables/Charts for your dashboard (clean up/customize ones from Step 4, and create new ones as needed). Implement the dashboard in Excel. It must contain at least four charts (at least three should be interactive), three KPIs, and at least three slicers and/or other user interface items (e.g., drop-down boxes) to allow the user to interact with the dashboard. 6. In a textbox next to your dashboard, identify and briefly discuss (bulleted format) at least three key insights and why someone interested in the housing market should care about these things. Also briefly discuss two improvements you would like to make to the dashboard if you had additional time. Submission Excel file containing (1) raw data; (2) Data/EDA sheet: data with enhancements (e.g., additional columns, slicers, summary measures), in table structure; (3) pivot table sheet(s) containing pivot tables and associated charts; (4) your actual dashboard sheet; and (5) a textbox next to your dashboard with your insights. Housing Sale Data Analysis and Dashboard Page 2 of 2

Solved: /n Housing Sale Data Analysis and Dashboard: Excel-Based Data Explorat

Home
questions and answers
n housing sale data analysis and dashboard excel based data exploratio

Question

/n Housing Sale Data Analysis and Dashboard: Excel-Based Data Exploration, Analysis, and Dashboard Development Summary House prices and other house features for 50 cities/towns with universities are contained in the provided CSV file. Your task as an analyst/developer is to do an analysis of the data, as well as develo