Transcription
SPEAKER 1: Hi, class. In Module 2 assignment, you've been given a scenario that involves real estate data. The scenario basically is that you have been recently hired as a junior analyst at a real estate company. The sales team has tasked you with preparing a report that examines relationship between the selling price of properties and their size and square feet area. You've been provided with a Real Estate County Data document that includes properties sold nationwide. The team has asked you to select a region, complete an initial analysis, and provide the report to the team.
So essentially, you've been given a document that has real estate data, including selling price of properties and square foot area. And the idea is that you're going to pick a region and then work off of the data of that region and answer a bunch of questions here. So let's go over the questions on a very high level.
First, you will generate a representative sample of the data. OK, so once you pick your region, you will generate a simple random sample of size 30 of that region. Then you will report some basic statistics, including the mean, median, and the standard deviation of listing price and square foot area. Then you will analyze that sample. So you will analyze the sample data with that of the national market. And we will see whether there's a difference or whether the sample data is fairly reflective of the national market.
Then you will create a scatterplot, OK. So you're going to take the two variables of interest, listing price and the square foot area, and you will create a scatterplot to see whether there is some kind of a relationship between the two variables. And then you will answer some of these questions here. Basically we're going to study that relationship between the two variables and then see if we can create a regression equation and then answer some of these questions.
So let's start with selecting the random sample of the region that you're going to pick. Now the data set for this scenario is included in this Real Estate County Data document. And when you click this, this will open up this Excel workbook that includes the data that we need. So it includes median listing price here in column D, median square feet area in column F. And also includes a column for the region. And if you scroll down, you will see that this region here-- we have East North Central here. You have East South Central, Mid Atlantic, Mountain, and so on. So all of the regions here are already sorted.
Now remember the first step was to pick one of these regions to start with. So for this lecture, I will just pick East North Central region. And then I can start the assignment. So how do we do that? So if you note, this is the last row of East North Central region data. So I can just click on this cell here. And if I just highlight all of the remaining cells and hit Delete on the keyboard, this will delete data for all of the other regions and leave me with the East North Central region that I want to work with. And so, what I can do now is I don't need this data here, right. So I can just get rid of this data before I generate my random sample from this region. So how can we get rid of this? Well, if I just click on row four here and then scroll up so that the first four rows are selected and then right-click and delete, this will get rid of the information that I don't need. And so here is the data set that I'm going to start working with. OK.
So now let's go ahead and pick a random sample of size 30. Now remember that we have to pick our representative sample randomly. So somehow we have to figure out a way to include randomness in this data. And then maybe we can sort randomly and then pick the first 30 observations so that I will have my random sample of size 30. So how do we include randomness in the data? Well, we can use Excel's Rand function to generate random numbers that we can then use to sort the data randomly. So let's start by first typing Random in this cell here, so that I know column G will represent something that will include randomness in the data set. All right. Now, I can use Excel's Rand function, which I can just type here as equal rand parentheses. And what this does, it generates a random number in this cell, right. Now I can do the same in the next cell to generate another random number. And I can keep doing that until the very last row. Now clearly, that's not entirely feasible. It will take forever to type this everywhere. So naturally, what we can do is we can use-- we can either just drag this cell down so that it copies the Rand function, OK, in all of these cells, or if I just hover over the lower right corner of the cell and I double-click on it, what this does is it copies the Rand formula in all of these cells until the very end. OK, so that's exactly what we wanted to do. And you must have noticed each time I perform some kind of a task-- so even if I just, say I type equal here and I type one enter, you'll see that the numbers will keep getting updated. And that's fine. All of the numbers that are generated again are indeed random, OK.
So what this column now represents is a bunch of random numbers, OK. And what I can do is if I select all of these columns here, then I go to Data, Sort-- make sure you have this My data has header checked, because we have headers. Then click on this drag down here Sort By. And then sort by Random. Smallest to largest is OK. If you hit OK, what this does is it will sort by this column. But because this column was just a bunch of random numbers, the data set has now been sorted randomly, OK. And how do I pick a random sample of size 30? I can just pick the first 30 rows. I know the data set is randomly sorted right now. So if I just pick the first 30 rows of data, which will be until row 31-- remember, the first row is the header row. So I don't need any of this data here. All right. So I can just, again, highlight all of this data, scroll down all the way, and just hit Delete on the keyboard. And so here it is. This is my representative sample, which is random, and it's of size 30.
The next question is about analyzing the sample data that you just picked with that of the national market. And the idea here is to compare and contrast your sample with the population using this National Statistics and Graphs document. So if you click on this link here, this will open up this document that includes summary statistics for the national market. And as you can see, it includes median listing price, median square foot area. That's what we are working with. And we have the mean, standard deviation, minimum median and the maximum, and so on. So we can use some of these statistics to see if we can compare them to the same stats of the sample that we just picked.
So what I've done here in this document is that I've copied over the national market stats here. OK, so for the mean price. This is the mean price, this is the median price, and then this is the standard deviation of the price. And then the same stats for the square foot area. And again, I've taken these stats from this document here. They're all mentioned here. And now the idea is I can calculate the same stats for this sample data that I've just generated. So let's calculate the mean price. So I can use equal average function to calculate the average. And the price here is in column D. So if I just hit-- if I enter parentheses and then I click on column D, so if I just click here and then parentheses again-- here we go. So this is my sample mean price. OK, so this is the mean price of this random sample of size 30. And as you can see, that in the sample mean is fairly smaller than the national mean here.
I can use the median function here. Calculate the median price of the sample. So the median list price of this sample of size 30. And again, I will click on column D. So that's my median, 175. And again, it's much lower than the median price of the national market, right. To calculate the standard deviation, I can use equal stdev.s. .s is for the sample standard deviation, because we're calculating that for the sample size 30. Parentheses. And then again, I can click on column B. So here is 81,455. That is my standard deviation of the price. Compared to the national market, obviously it's lower.
Now I can do the same calculations for the square foot area for this sample. So I can say equal average parentheses. And this time, I'm going to click on column F. The median I can use. The median function-- parentheses. Click on column F. And then do that again for the standard deviation-- stdev.s. Parentheses. Click on column F. All right. So I can just get rid of these decimal points here. So my mean square foot area is 1,760. Compare that to 1,944. Again, the sample mean is lower. Sample median is 1,686. Compare that to the national market, that's 1,901. So again, it's lower. Standard deviation is also lower, all right.
So clearly in this scenario, my random sample from the region East North Central is showing statistics that are lower than the national market. So obviously, it does not really compare to the national market. It seems that the prices are lower in general than the national market. And possibly, that's explained by the lower square foot area in this region, at least in this sample, compared to the national market.
The next step is to create a scatterplot of listing price and square feet area. But before we do that, it is very important to answer the question of which one of these two variables is the dependent variable. And then which one is the independent variable. This is very important before we create any scatterplot. Now generally speaking, square feet area will determine the price of the home. We expect that if the square feet area is lower, then the listing price will be lower. And if the square feet area is larger, then clearly the listing price we expect would be higher. So what we're saying here is that the listing price depends on the square feet area. So the dependent variable is listing price, and square feet area is the independent variable. OK.
Now, in a scatterplot, the independent variable will go on the x-axis. OK. So here, in this case, median square feet area will go on the x-axis. And the dependent variable will go on the y-axis. So listing price will go on the y-axis. So how can we create this scatterplot? So let's just click on any of the cells here. So I'll just click on this cell. Go to Insert. Click on this Scatter X, Y or Bubble Chart. So if I click on this dropdown, I can pick this scatterplot here. If I just click on this, this will open up a blank scatterplot in which we will add data. OK, so I'll just move this here for now. Let's just move it here.
Now if I right-click on this and I click on Select Data, this will open up this. In Legend Entries, if I click on Add and if I click on Series X values, for x values, I can pick the square feet area. So if I click on this cell where the data starts for square feet area and then just drag this down, this will select all of the median square feet area values here. OK. Then for Series y values, the first thing I need to do is I need to delete this here. So I can just delete that. And again, because it's y values now-- so I'm going to start by selecting from here, from this cell, for the listing price. So if I just click on this cell and then drag this down, this will select all of the values of the median listing price. And as you can see, as you do that, it will actually generate the scatterplot in the back. Now I can just click OK. And OK. OK, so here's the plot. OK.
As you can see, we don't have any data from zero till almost 1,200, right. So what if I just start the x-axis not at zero, but at 1,000? OK, so I can do that. If I just double-click on the X-axis here-- so I can just double-click on this 1,000. This will open up this panel here. And as you can see for the bounds, the minimum is set to zero. I can just set this to 1,000. OK. And if I hit Enter, so now the x-axis starts at 1,000. So this is what the trend looks like. So clearly, you can see that there is some kind of a trend. It's not an exactly linear trend, but the trend is positive, right. As the square feet area increases in this direction, we can see that the prices are going up, all right. For example, if you hover over this data point here, you can see this is where the square feet area is 1,405, and the price is $97,735. Compare this to this data point here. Square feet area increases to 2,131, and the price increases to $332,796. So clearly there's a positive trend. It's not perfectly linear.
Now that we have this, what we can do is we can create the simple linear regression line here. OK, so it's also called the best fit line. We can create that on this chart. And how do we do that? First, let me just close this panel here. So I'll just hit on this x. OK, now if I click on this plus sign here and I scroll down and just checkmark Trendline, this will open up this, OK. So this is the best fit line or the trendline. Also called the regression line. If I double-click on this, this opens up this panel here. So we want to make sure we pick linear line here. So that is what was picked by default. And then I can scroll down here. As you can see, we have this display equation on chart options. So if I checkmark this, this actually displays the regression equation on the chart. This is the regression equation here. And I can just drag this up to somewhere. Maybe here so that it's visible. I'll close the panel. So here is the best fit line. And in this simple linear regression line equation, x is the independent variable. So it's square foot area. And y is the listing price.
Now that we have the scatterplot and the regression equation, we can answer some of these questions. So the first question is, define x and y. So we know what those are. Is there an association between x and y? So the scatterplot clearly showed that there was an association between square foot area and the listing price. As square foot area increased, the listing price increased as well. What do you see as the shape? Well, the shape of the scatterplot was definitely not non-linear. It was also not perfectly linear. But the general trend was linear. As the square foot area increased, the price increased as well. And for the most part, the relationship was a positive correlation between the two variables. So there was somewhat of a linear relationship.
If you had a 1,200 square foot house based on the regression equation in the graph, what price would you choose to list at? If we go back to this scatterplot here and this equation, we can use this equation to figure out the listing price if we input the square foot area in place of x here. So let's do that in this cell. So I'm going to type equal 268.44 times 1,200 square foot, which is given in the question, minus 283,188. The predicted listing price is what we would pick, and that would be 38,940. OK, so somewhat lower than most of these other values. But you can also see that 1,200 square foot area is here. And it's somewhat outside of most of this data that's shown here. So we are outside the bounds of the data that we have in the sample. So we should be careful in actually using this predicted value. But based on the equation, this would be the listing price that we would pick, OK.
And then the last question is, do you see any potential outliers? Why do you think the outliers appeared, and what do they represent? So as you can see for the most part here, this data is fairly well contained. Especially if you use the trendline as somewhat of a base. Most of the data points are well contained around it. But there's one data point that's way up here that clearly seems like it's an outlying case. Why is it an outlier? Well, you can see that it's not near any of these other data points. Now there is no other data point that is closer to it. So this is a candidate for being an outlying case. Now why do we have this outlier? Well, one of the reasons-- potential reasons-- could be that in East North Central region, remember that we picked a random sample. So the random sample is a representative sample of that East North Central region. And potentially in that region, most houses have square foot area within this range here, OK. So about 1,400 to 2,200 say. This is most of the houses in that region. But potentially, there are very few homes that are larger in their square foot area, OK. And so because there are only a few homes that have that high of an area, the likelihood of those homes getting picked in the random sample is somewhat lower. And so, every now and then, when you pick a sample, they will get picked. And so you might end up having these somewhat of an outlier, outlier cases, OK. And then also note that because this value here has very high square foot area compared to the other ones-- so it's about 2,529-- the price for that home is also higher than these other homes here.
Lastly, I want to discuss Module 2 assignment template. So if you click on this document, this will open up this Word document here. And you should use this document to create your report. So be sure to include your name here. And then the template actually gives you a basic structure of how your report should look like. So start with the introduction-- a brief overview and the purpose of the report. Clearly specify what region you picked and how did you select the random sample of size 30. Include the data analysis. So be sure to identify your mean, median, standard deviation of the sample and how do they compare to the same stats of the national market. And discuss whether they compare well or whether they are different. Then include the scatterplot that we just created. So be sure to include your own scatterplot that has the trendline on it and has the regression equation on it, and then specify that regression equation. And then discuss a little bit of what the scatterplot shows.