(upbeat music)
- Hello, I am Professor Rick Jerz
and I welcome you to this session
titled Test of Hypothesis Using Excel.
In this session I'm going to show you
how to use my Excel model to do test of hypothesis.
Usually these kinds of calculations
are very difficult to do,
because you have to look
at the normal curve in different ways.
And the typical textbook steps
are not easy to follow either.
I think you will see that my Excel model
makes the calculations rather simple.
The other thing that my model does
is give you a good visual view of what is going on,
which helps you understand the data a little bit better
and this decision making process.
I think you are going to enjoy using my model.
Let's get started.
Before we begin problem solving
let's review our approach.
We state the claim and the alternative claim,
we decide how much risk you want to take.
How often do you wanna be wrong?
This is called the level of significance, or alpha,
and is typically 5% or less.
We collect the data,
using our Excel model, either the normal or T-distribution,
we calculate the p-value.
We see if the data suggests that you will be wrong
less than 5% by comparing the p-value to alpha.
If the p-value is less than alpha, you will reject the null,
meaning that you will be wrong
less than your willingness of being wrong.
Even though I have given you a new Excel model,
it is really nothing new.
The only difference is that both the Z and T
distribution models I have uncovered an area
of the model that allows us to do hypothesis testing.
We have to enter the null hypothesis claim point
and whether or not the null hypothesis a equal to,
less than, or greater than value claim.
You will put the letters E, L, or G in this cell
and the model will then decide whether or not our test
is a one-tail or two-tail test.
My model calculates the p-value
and based upon the comparison of the p-value
with the level of significance a decision is made.
Remember that our decision is to either reject the null
or to not reject the null.
We will use the normal distribution model
when we know the population standard deviation
and we will use the T-distribution model
when we do not know the population standard deviation.
If the claim is in the area under the curve
representing the sample data,
meaning that it's in the red region,
we do not reject the null.
Here is how my model represents a null hypothesis
whose claim is equal to.
My model draws a black line representing this claim point.
If this black line is in the red
we do not reject the null.
If the black line is outside of the red region
we do reject the null.
When the null hypothesis is a greater than or equal to
or less than or equal to claim
my model represents the beginning point of this claim
as a solid black line
and uses a shaded green area to represent the less than
or greater than area.
If the black line is in the red region
we do not reject the null.
One can visually see the overlap between this greater than
or less than claim as a brown region.
Basically the mixture of red and green.
Let's solve some problems presented in this chapter
using my Excel models.
Here we go.
Jamestown Steel Company manufactures and assembles desks
and other office equipment at several plants
in western New York State.
The weekly production of Model A325 desk
at the Fredonia Plant follows a normal distribution
with a mean of 200 and a standard deviation of 16.
Recently, because of market expansion,
new production methods have been introduced,
and new employees hired.
The vice president of manufacturing
would like to investigate whether there has been a change
in the weekly production of the Model A325 desk.
Is the mean number of desk produced at the Fredonia Plant
different from 200 at the 1% significance level?
The vice president thinks
that there has not been a change in the mean.
In step five we are told that we do take a sample
once a week for 50 weeks
and we calculate the sample mean to be 203.5.
So is this 203.5 unusual?
In other words, if we construct the 99% confidence level
for this sample data
would the mean of 200 be inside
or outside this confidence interval?
We must first decide whether to use the normal distribution
or T-distribution model.
In this case we are provided
the population standard deviation,
which means we will use the normal distribution model.
For test of hypothesis
we are always in the confidence interval column.
I will enter an F in the Plot which Condition cell.
I will enter the sample size of 50.
The mean that we need to enter is always that of the sample,
which in this case is 203.5,
the standard deviation of the population of 16,
and the 99% confidence interval.
Said a little bit differently, a 1% level of significance.
Notice that this is calculated down below.
We are testing for equality,
so this cell has to be equal to E.
The claim point is 200
and the model tells us
that we should not reject the null hypothesis.
Why?
You are willing to be wrong 1% of the time,
but the data suggests that you would be wrong
12.19% of the time.
This is more than you want to be wrong,
so you do not reject the null.
You can get a better sense of what's going on
if you scroll down and look at the graph
for this normal distribution.
The area in the red represents
the 99% confidence interval for the sample.
The claim point for an equal to null hypothesis
is shown as a black line.
As long as this black line
is somewhere within the confidence interval,
meaning in the red, you do not reject the null.
For our data this black line is well within the red area,
which is why we do not reject the null.
In step six you will see that our conclusion
matches that of the author's.
However, with my Excel model
we've reached this conclusion much faster.
Let's do another problem, the self-review problem 10.1.
Heinz, a manufacturer of ketchup,
uses a particular machine to dispense 16 ounces
of its ketchup into containers.
From many years of experience
with this particular dispensing machine
Heinz knows the amount of product in each container
follows a normal distribution with a mean of 16 ounces
and a standard deviation of .15 ounces.
A sample of 50 containers filled last hour
revealed that the mean amount per container
was 16.017 ounces.
Does this evidence suggest that the mean amount dispensed
is different from 16 ounces?
Use the 5% significance level.
Once again, as we read this problem very carefully
it says that we do know something about the historical
standard deviation, that it is .05 ounce.
Therefore, once again,
we will use the normal distribution model in Excel.
The level of significance is 5%
and the claim is an equal to claim.
We have to prove that it is not equal to 16.
Using our model we enter the sample size,
the mean of the sample,
the standard deviation for the population,
and the 95% confidence interval,
which gives us a level of significance of 5%.
This is an equal to test and the claim point is 16.
Our model says we cannot reject the null hypothesis.
In other words, there is not enough evidence
to suggest that the mean is different than 16.
We can see this graphically by scrolling down
and looking at the chart for the normal distribution.
We see that the black line is well within the red region.
If we look in the book for the answer to this problem
we would see that our answer matches that of the author's.
This Excel model makes this test
of hypothesis procedure easy.
Let's do another problem.
A sample of 36 observations
is selected from a normal population.
The sample mean is 21
and the population standard deviation is five.
Conduct the following test of hypothesis
using the 5% significance level.
The mean is less than or equal to 20,
the mean is greater than 20.
You should recognize that the only difference
between this problem and the previous two
is that we now have a less than or equal to condition.
We are still using the population standard deviation,
so we must use our normal distribution model.
Let's put this data into our model.
A sample size of 36, the mean of the sample of 21,
the standard deviation from the population of five,
a 95% confidence interval,
which gives us a significance level of 5%.
The condition that we're testing
is a less than or equal to condition,
so we have to make this be an L.
You might notice that when you change the claim condition
to L for less than or equal to
the model immediately shows that this is a one-tail test.
The claim point is 20
and we end up with not rejecting the null.
We not have sufficient evidence from the sample
that the boss's claim that the mean
is less than or equal to 20 is incorrect.
Let's scroll down and take a look at the graph
for this situation.
When our null hypothesis is a less than or equal to
or a greater than or equal to condition
my chart will show this as a green area
somewhere on this graph.
If any of this green region is on top of the red region
I add these two colors to give us brown.
We do not reject the null hypothesis.
And this is the case for this particular problem.
If we look in the book for the answer to this problem
you will see that our answer matches that of the author's.
Let's do another problem from this section.
The manufacturer of the X-15 steel-belted radial truck tire
claims that the mean mileage the tire can be driven
before the tread wears out is 60,000 miles.
The population standard deviation
of the mileage is 5,000 miles.
Crosset Truck Company bought 48 tires
and found that the mean mileage
of its trucks is 59,500 miles.
Is Crosset's experience different from that claimed
by the manufacturer at the 5% significance level?
Once again, we have the standard deviation
of the population,
so we must use the normal distribution model.
We have to carefully interpret
the wording in this paragraph.
The words different than implies an equal to
versus not equal to condition.
We will use this in our model.
The manufacturer is making the claim
and therefore the manufacturer is the null hypothesis.
The truck company which bought 48 tires
is representing a sample
and is therefore the alternative hypothesis.
Does the sample support the manufacturer's claim?
We once again enter this data into the model,
making sure that the test condition is set to E for equal.
The claim point is 60,000 miles
and we see that we do not reject the claim.
We do not have enough evidence
at the 5% level of significance to challenge
the manufacturer's claim that the mean is 60,000 miles.
If we were to reject this claim
we would be wrong 48% of the time,
but we only want to be wrong 5% of the time.
This is why we do not reject the null.
You are not required to always look at the chart
for every situation,
but I like to do this just to verify
that the null hypothesis and the data being represented
are visually correct.
We can compare our result to that of the author's
to verify that our answer is correct.
Any slight difference in the results
is usually due to our model being more accurate.
All right, let's do another problem.
The McFarland Insurance Company Claims Department
reports the mean cost to process a claim is $60.
An industry comparison showed that this amount
to be larger than most other insurance companies,
so the company instituted cost-cutting measures.
To evaluate the effect of the cost-cutting measures,
the supervisor of the Claims Department
selected a random sample of 26 claims processed last month.
The sample information is reported below.
At the 1% significance level,
is it reasonable to conclude that the mean cost
to process a claim is now less than $60?
I like this problem for several reasons.
It is more realistic that somebody would collect the data
and give it to us for analysis
than do some pre-analysis on it and give us the mean.
It is up to us to calculate the mean
and standard deviation from the sample.
Another thing that we see going on here
is that we're not told anything
about the historical standard deviation of the population.
This means that we are going to have to use
our T-distribution when we analyze this problem.
To identify the null hypothesis
and the alternative hypothesis
we have to see who is making the claim
and who is collecting the data.
It appears to me that McFarland Company is making the claim
and that we are acting as a supervisor
of the Claims Department collecting the sample.
When the question asks, is it reasonable to conclude
that the mean cost to process a claim is now less than $60,
I think that this question is asking whether or not
the data from the sample suggests that the mean cost
to process a claim is now less than $60.
So the alternative hypothesis
is always from the perspective of the collected sample data.
Therefore, the alternative hypothesis is that the mean cost
is less than $60.
The null hypothesis is always the opposite
and the null hypothesis always includes the equal.
Therefore, the null hypothesis
is that the claim is greater than or equal to $60.
Figuring out the null hypothesis
and the alternative hypothesis
is sometimes the challenge in these problems.
We begin this problem by putting the data
into our descriptive statistics calculator,
so that we can get the mean
and standard deviation of this sample.
I see that the mean is calculated to be 56.423
and the standard deviation for the sample is 10.041.
Now I could write these numbers down
on a piece of scratch paper
and then go to my T-distribution model,
but instead I will go to my T-distribution model
and then enter some equations to reference these values
back on this descriptive statistics model.
I like using this technique
just in case I had entered some data incorrectly
on the descriptive statistics model.
If I make any changes to the input data
the T-distribution model will immediately be updated.
I enter the 99% confidence interval.
Down below I make sure that the test condition
is greater than or equal to,
meaning a G, and the test point is 60.
The results of our analysis
is that we cannot reject the null hypothesis.
We would be wrong rejecting the null 4% of the time
and we only wanna be wrong 1% of the time.
The chart below shows an overlap
between the calculated P value
and our level of significance.
Whenever there is an overlap we do not reject the null.
If you look at step six, in the second paragraph
the author comes to the same conclusion.
You might notice that it took the author
a lot more work to get there.
In fact, it is no harder for us
to use the T-distribution model
than the normal distribution model.
Let's do this self-review problem.
The mean life of a battery used in a digital clock
is 305 days.
The lives of the batteries follow a normal distribution.
The battery was recently modified to last longer.
A sample of 20 of the modified batteries
had a mean life of 311 days
with a standard deviation of 12 days.
Did the modification increase the life of the battery?
We are told to use the 5% level of significance.
There are several things to recognize in this problem.
The first is that the standard deviation of 12 days
is from the sample,
so we must use the T-distribution.
The question, did the modification increase
the mean life of the battery,
is asking whether the sample suggests that the mean life
has been increased.
The sample takes on the role of the alternative hypothesis
and it is a greater than claim,
therefore the null hypothesis is always the opposite,
that the battery life is less than
or equal to the claim of 305 days.
I enter the data into the T-distribution model.
I have to make sure that the null hypothesis claim
is a less than or equal to setting.
Our results are to reject the claim.
We can safely say that the modification
increased the battery life to more than 305 days.
Notice that the p-value is 1.87%.
Assuming that we are the data collector
we would be wrong not rejecting the null 1.8% of the time,
but we're willing to be wrong up to 5% of the time.
So it is relatively safe to reject the null.
Rejecting the null suggests that the modification
did increase battery life.
If we look at the chart below
we see that the green region does not overlap
any of the red region.
Remember that no overlap means that we reject the null.
If we compare our results to those of the author
we would see that our results are the same.
Let's do one more problem.
A machine is set to fill a small bottle
with nine grams of medicine.
A sample of eight bottles
revealed the following amounts in grams in each bottle.
At the 1% significance level
can we conclude that the mean weight
is less than nine grams?
Once again, I like this kind of problem
because we are given the raw data
and we must put this
into our descriptive statistics calculator
and calculate the sample mean
and the sample standard deviation.
Also, once again, since we are not being given
the historical standard deviation for the population
we must use our T-distribution model.
I will enter equations into this model
just like I did earlier
to reference the necessary statistics
from the descriptive statistics model.
I enter a probability of .99 for my 1% level of significance
and I have to decide what to enter
as the null hypothesis claim.
The question asks, can we conclude that the mean weight
is less than nine grams, suggests to me
that we have been acting as the data collector
and have collected this sample data.
Therefore, we take on the role of the alternative hypothesis
and this means that the null hypothesis is the opposite,
meaning a greater than equal to claim.
I enter a G for greater than or equal to
into our null hypothesis statement condition,
the test point is nine,
and we see that we cannot reject the null.
In other words, the data has not shown that the mean
is less than nine grams.
If we look at the graph below we will notice
that we are very close to rejecting the null.
Had we used a 5% level of significance
we would've ended up rejecting the null.
The beauty of looking at the graph in this Excel model
is that it does give you an indication of how close you are
to either rejecting or not rejecting the null.
We would not be getting this kind of feel
for the data had we been using
either the Z or T statistic approach.
By now you realize that it is no surprise
that our answer matches that of the author's.
Wow, this is amazing.
These models really do make test of hypothesis
for means quite simple.
Thanks for taking time to spend some moments with me.
I hope you have enjoyed what I have shared with you.
If you ever have any questions
or need my help please let me know.