Transcription
SPEAKER: Hi class. So in module 5 assignment, we will be doing a hypothesis test for the population mean. So the scenario here is provided to you. So let's go through the scenario and see what kind of hypothesis test we will be doing.
So in the scenario here, you've been hired by the Regional Real Estate Company to help them analyze real estate data. One of company's Pacific region sales people just returned to the office with a newly designed advertisement. The average cost per square foot of home sales based on this advertisement is $280. The salesperson here claims that the average cost per square foot in the Pacific region is less than $280.
So essentially, this salesperson here has come up with an advertisement that is giving an average cost per square foot of home sales being that of $280 and the claim that the salesperson is making is that the actual sales in the Pacific region today are less than $280. So in other words, the newly designed advertisement would result in higher average cost per square foot in the Pacific region. Cost areas taken as a positive context, and so the idea here is to carry out a hypothesis test to test this claim.
So remember that the claim here is that the average cost per square foot of home sales in the Pacific region is less than $280. So this salesperson, he wants you to make sure that he can make that statement before approving the use of the advertisement. In order to test this claim you will generate a random sample of size 750 using data for the Pacific region. You will generate a random sample from the data for Pacific region that's given to you. And then you will work off of that sample to carry out the hypothesis test. So let's walk through those steps and carry out the test and see what kind of results we get.
To carry out this hypothesis test, the first step is to generate a random sample of size 750 for the Pacific region. So because this is a random sample that you are going to be generating, the results of the hypothesis test will be unique to you. So each student will have their own set of results. And the data set here is contained in an Excel workbook that's linked here, House Listing Price by Region. If you click this, this will open up this workbook, and if you scroll over to the tab called Pacific, this will have the data for the Pacific region and we can start working with this data. If you remember, the claim was for the cost per square foot. So that's column E. So this is primarily the column that we're going to be working with.
To select the random sample, we can start working in this tab, but my recommendation is to keep this original tab as is because you may want to go back to this data set at some point, and what I would suggest is to create a copy of this tab and you can work in that copy. So how do we do that? So if we just scroll over to this tab name, Pacific, if we right click on it and click on Move or Copy, then select the Pacific sheet here, and then checkmark Create a Copy and say OK. So what this will do is this will create a copy of the original Pacific spreadsheet here, and this is the copy that you can work with and keep the original as is. And so if you click on this tab here Pacific 2, double click on this, I can just rename this to, say, student. And then now I know that this is the spreadsheet I'm working with. OK.
So let's start by cleaning this up a little bit. We can get rid of these four rows here. So if I just click on row 4 and then just drag it up, I can just delete all these four rows. And how do we do that? We can right click and delete. And so this is the data set now that we're working with. Now, because this sample has to be random, we have to figure out a way to randomly sort this data set so that we can pick the sample from it. Now, remember that we have to pick 750 records in this random sample. So the two steps of this process will be first, we will randomly sort the data set, and then the second step will be to pick the first 750 rows. And because the data set was randomly sorted, those 750 rows will be a random sample.
So we can use this function that Excel has called the Rand function, and this function generates a random value. And so what we can do is go in front of the first record, this first row and column G here in this cell, we will type equal RAND and then parentheses. And if you hit Enter, what this function does is it generates a value between 0 and 1, and it's a random value. So the next step, we will take this Rand function and we will copy this in all of the rows here of this data. So we can either just drag this down like that all the way down, or there's a neat little trick here. We can hover over this bottom right corner of the cell and you will see that the cursor will change to that plus sign right there, and then double click on it. So if you do that, it will just copy over the Rand function as you can see here, it will copy this all the way down to the last row. So column G now has random numbers that are randomly generated for each of these rows, and I can call this random. Let's just call this random. And each time you do some kind of an operation, the random function will run again and it will generate another random value. So these numbers will keep getting updated, and that's OK.
Now that we have a random column, we now have to sort the data randomly. So because this column was generated randomly, if I just sort by column G, the rows will be sorted randomly. So that's the idea of using the Rand function to randomly sort the data set. So how can we do that? So we can just highlight all of these columns. So just select them all, go to Data, and then go to Sort. And when you hit Sort, Excel will know that you have the first row as the header row, and that's why this my data has headers is checked. And it's asking you basically what column do we want to sort this by. And obviously, we want to sort this by the random column, and smallest to largest is OK. So now we hit OK. And so there you go. So now, this data set has been randomly sorted.
So we want to pick the first 750 records. So what we can do is we can scroll down. And so remember the first row was the header row. So we're starting with row 2. So we want to go to 750 first row, which is right here. So we want to keep that row and then delete everything else below it. So I can just click here and then drag this down, so select all rows all the way to the bottom here. And then I can right click and then delete. So there you go. This is your random sample. So remember we had to have 750 records and this is how-- that's how many records we have here. And this is a random sample that we will now use to carry out the hypothesis test.
We need to specify a few things so that we can set the right context for carrying out the test. So the first thing is that the population parameter that we're testing is the mean cost per square foot in the Pacific region. So average is being tested in the hypothesis test. Let mu represent the mean cost per square foot in the Pacific region. So we will use mu to represent the population mean cost per square foot in the Pacific region. The mean cost per square foot based on the new advertisement is 280 and the claim that the salesperson made is that the mean cost per square foot in the Pacific region today is less than 280. This statement here is key because this will help you pick the correct type of test to do.
So remember we have three types of tests here-- two tailed test, a left tailed test, and any right tailed test. And based on this claim here, you will pick one of the three tests to do. Now, I can tell you right now that a two tailed test is not the correct answer for this scenario, and that's because in a two tailed test, the claim would have been that the mean cost per square foot in the Pacific region is not equal to 280. So that's not the correct answer. And so now, I will let you pick between the left and the right tailed test. So you'll pick the correct answer here. But I will walk you through the calculations for all three tests. So that is clear how to get to the p value. So you can carry out the test.
So let's start the calculations. The first thing is to calculate the sample mean. And we can calculate that using the average function, so I'm going to type equal average parentheses and hit column E here, which is the cost per square foot. So that will give me the sample mean of cost per square foot, that's 264. The target specified in the problem is 280.
Next, we have to calculate the standard error. The standard error is shown in the formula here. This is the general formula not the Excel formula. So the general formula is sample standard deviation divided by square root of n. So we can calculate that very easily using Excel functions. So I will type equal STDEV.S, this is the function for sample standard deviation, parentheses, and then I will again click on column E here. So that's the first part, the sample standard deviation, and then divided by square roots, which is the SQRT function of 750, which is the sample size, that was the sample size we selected. And so here we go. This is our standard error.
Next, we have to calculate the test statistic. And the nice thing is that the test statistic is the same for all three tests. It's the P value that's different. So the test statistic can be calculated as equal. We're going to calculate the sample mean. We already calculated that. So I can just click on this cell, this is the sample mean, minus the target, parentheses closed, divided by the standard error, which is here. So here we go. This is the test statistic, minus 2.786. A lot of times, students will get confused with what is a test statistic. And you can see the easiest way to remember what this is note that 264 is less than 280. The sample mean is less than the target. So the first thing you'll see is the negative test statistic means that your sample mean is less than target, which clearly we can see here. And then the magnitude 2.786, this represents how many standard errors the sample mean is below the target. So because the standard error is 5.81, we can say that the sample mean is 2.78 standard errors below the target. So if I subtract 5.81 from 280 2.78 times, I would get to 64. So this is just like saying how many test standard errors below the target our sample mean is. That's the easiest way to remember this. And then the degrees of freedom is n minus 1. So n is 750 for us, so degrees of freedom are 750 minus 1, at 749.
Now, I already mentioned that the two tailed test is not the correct answer in this scenario. But let's carry out the test. So let's see if we can calculate the P value. So the P value we can use the T.DIST.2T function, and the first argument it takes is the test statistic. And the important thing here is that we have to enter the positive value of the test statistic, at the absolute value of the test statistic. So the absolute value of minus 2.7863 is 2.7863. So I'm going to enter the positive value here. And the degrees of freedom are 749 because I just click here. And so this is the two tailed P value for this test.
Now, suppose this was the correct test to carry out in this scenario, we're just assuming it was. And if it was, then we can now reach a conclusion based on this P value. So since the p value is less than a level of significance of say 5%, so 5% is usually the level of significance used, if the level of significance is 1%, the P value is still lower than 1%. So the conclusion would be the same. So since the P value is less than 5% or 0.05, we will reject the null hypothesis. If the P value is less than the level of significance, we always reject the null hypothesis. And there's evidence to support the alternative hypothesis. Now, this is not the complete conclusion. To establish a complete conclusion, we need to make another statement and say we conclude that average cost per square foot in Pacific region is not equal to 280. So this is the complete conclusion of the test. And note that this is exactly what the alternative hypothesis here says. All right.
Now, not that this is not the correct answer for this scenario, for this claim here. So you have to pick between this test or this test. But I will do the calculation for both. So let's now do the P value calculation for the left tailed test. In the left tailed test, the null hypothesis is the same, mu equals 280. But the alternative hypothesis, because it's a left tailed test, is that the mu is less than 280. Let's calculate the P value here. So the P value will be we will use T.DIST function. And now, it's asking for the test statistic. So we will just click on this, we will use this value as is. Next argument is degrees of freedom, that 749. So I can click here. And then the next is whether we should use a cumulative distribution, and that will be set to true, or I can just enter 1 which is interpreted as true, and parentheses. And there you go. So this is the P value based on it left tailed test, and then you can reach the conclusion in a similar way here. Just be careful when you are making the conclusion about the average cost per square foot, basically the second line, you have to go with the alternative hypothesis shown here.
Now, suppose you think that the right tailed test is the right answer. So now, we can calculate the P value for that. Null hypothesis again is the same that mu equals 280. Alternative hypothesis is that mu is greater than 280. Hence, this is the right tailed test. So the P value here we can calculate with T.DIST.RT for the right tailed test. The first argument is the test statistic as is, the second argument is the degrees of freedom, 749, and the P value we get is 0.997, it's a very high P value. And again, we can compare this P value to the level of significance. And your response or your conclusion of the test will always be based on this question-- is the P value less than the level of significance? So whatever level of significance is specified in the question. This is the question you need to ask yourself. If yes, then reject the null hypothesis, not, then do not reject the null hypothesis. And then you will finally specify the actual conclusion of the test like I've done here. So again between these two tests, this claim here is the key for you to pick the correct answer. So I hope this helps you carry out this test.
When you do your report, be sure to answer all of the questions. Specify how you picked your random sample. So you'll just have to explain in a few sentences how we were able to pick a random sample of size 750 and then explain the steps that we've done here. The template does a very good job of telling you exactly what you need to specify in your responses. Good luck.