(mellow music)
- Hello, I am Professor Rick Jerz,
and I welcome you back
to my week five reinforcement session
of business analytics.
In this session, I want to show you how to solve
some more problems using our Excel model
for normally distributed population data.
Remember, that there will be times in this video,
where I encourage you to pause my video,
try to solve one of these problems on your own,
and then restart the video,
and watch how I solve the problem.
I will often explore these problems,
in a little more detail.
So, you can get a even better understanding
of calculating probabilities for this type of data.
Let's get started.
My objectives for this reinforcement session
are as follows, to review a few administrative items,
to briefly review the topic
of continuous probability distributions
to solve some additional
normal probability distribution problems
using our Excel model to provide a few comments,
about my Excel models and modeling techniques,
and to provide a few comments
about our next topics.
Administratively, I do want to mention
that exam one in now now showing in Moodle.
Above exam one, I am providing a study guide,
showing the topics that will be covered.
This exam will contain only problems,
no concept questions.
To help you review for this exam,
I will be turning back on,
all of he self assessments leading up to this first exam.
Your grades for the assignments are locked
based upon the due date of the assignment.
So, your grades cannot change,
but you can return to the self assessments,
and practice solving problems.
The problems that I plan to put on the exam
will contain problems very similar
to those we have done in our self assessments,
my lecture videos, and my reinforcement videos.
I am not planning to surprise you with anything.
If the scheduled date for the exam
is going to give you scheduling problems,
you can let me know,
and I am willing to move your exam,
oh, maybe plus or minus two days
from the scheduled date,
just drop me an email,
and let me know what would work for you.
Please remember that you must take this exam
on your own, with no other student near by.
On Moodle you will see a special form topic
called how's it going in this online course?
I'm trying to get some intermediate feedback from you
about how is it going.
Are you liking the course?
Are you learning?
Have you been able to apply some of what you are learning?
What is it that you think can be done better?
Are you having any particular difficulties?
I'd like to do this in my online courses,
just to make sure that everything is going well.
This is an assignment, and you are welcome
to say whatever you'd like,
and if you just wanna be brief, with words like fine,
terrible, okay.
This is fine.
This assignment is worth just a few points,
but I do require it.
Maybe your comments will cause other students
to think about suggestions.
You might find that the MBA program
will also be collecting their own progress data from you.
I do want you to know,
that I'm really trying hard to make this course
be effective for you.
So, I will appreciate any feedback that you can provide.
The topic for this week
was continuous probability distributions.
We started by looking at the uniform distribution,
just to get you comfortable
with the concept of calculating probabilities
by finding the area under the curve.
I'd like to do one more
uniform probability distribution problem,
just to review this concept of calculating probabilities
as the area under a curve.
This problem is from another author.
Suppose the actual flight times for U.S Airways
nonstop flights between Philadelphia
and Orlando follows the uniform probability distribution.
Assume the minimum flight time
between these two cities is 140 minutes,
and the maximum time is 170 minutes,
we will do question b,
determine the probability,
that the next flight will take between
148
and
165
minutes.
By hand I can quickly sketch,
what this uniform distribution should look like.
Of course, it is a rectangle,
and the minimum value, I represent as
140,
the maximum as
170.
The total area under this curve is equal to 1,
and this are is equal to width times height.
I can solve for the height by taking
1
over
170
minus 140,
and the height
is
1 over 30.
Question b is interested in the probability that
the flights are between 148
and
165
minutes.
So, I need to calculate the area under
this curve.
The probability is equal to height times width.
We know that the height is 1 over 30,
and the width of this area
is
165
minus
148.
The result is that the probability is equal to 17 over 30,
or
.5
6
7.
This illustrates that the probability
of the event of interest can be calculated
as the area under the curve.
I should mention to you,
that the uniform distribution is being used here
just to illustrate this concept.
On the exam,
we will not have
any uniform probability distribution problems.
In this chapter, we want to focus
on population data,
that when collected and plotted,
looks like a normal distribution.
for this kind of data, we then want to ask questions
about the likelihood, of certain events happening.
Let's give this a try with some data,
that we have seen earlier in this course,
our M&M weight data.
When we plotted this data we did notice
that the distribution of weights
looks somewhat normally distributed.
It may not be perfect, but it looks close
to a normal distribution,
and let's assume that these M&M's represent all of our
M&M's of interest.
In other words, our entire population of M&M's.
Now, let's ask a question.
What is the likelihood that an M&M would weigh between
.9
and
.95
grams?
I encourage you to pause this video,
and try to solve this problem on your own,
before seeing how I solve this problem.
I will use my Excel model
for normal probability distributions,
the first thing I need to do though,
in order to use this model is
to calculate the population mean and standard deviation.
I can do this by using my descriptive statistics model,
and feeding this the
M&M weight data
by editing the cell reference values
for our defined name,
my
data.
I do this by clicking on the formula ribbon, define names,
picking my data,
erasing what is there.
Then I point to some of the cells for M&M weights,
and I just edit the ending cell reference
to make it something big, and I am done.
Next, I go to my normal probability model,
and I enter the
.9
and
.95
as the low and high values of the range of interest,
and again, I'm going to reference the mean,
and population standard deviation
from what I just calculated
on the description statistics model.
I calculate that
48
.73%
of the
M&M's should have their weights between .9 and .95.
You can scroll down to see the graph for this scenario.
The area of interest is shown in red,
and this represents our
48
.
73%.
How good is this prediction?
Well, if we want to,
we could go back to the actual population data
for M&M weights, and stroke through the values from
.9
to .95,
and at the bottom of Excel, we see
that there are 49 actual values.
Our model calculated
48
.73%,
and the actual percent is
49
over 100
or
49%.
This difference between,
what we calculate, and the actual value
is the result of the data, not being
exactly normally distributed,
with this M&M weight data, we are pretty close.
After we have calculated the mean and standard deviation
for any population data, that is normally distributed,
we can use the normal probability model
to calculate all kinds of probabilities of interest.
For example, I might want to know
the beginning and ending values of 50%
of this M&M weight data.
I encourage you to pause this video,
and try to solve this problem on your own,
before seeing how I solve this problem,
in my model, I entered 50% as the probability,
I carry forward the mean and standard deviation,
and my model calculates
.8
9 to
.94,
when I go back to the actual M&M weight data,
and select the inner 50 values, I get
.8
91
to
.934,
so our calculated values are pretty close to the actual.
Let's try solving a couple problems from the textbook.
The weights of canned hams processed
at Henline Ham Company follow the normal distribution,
with a mean of
9
.20 pounds
and a standard deviation of 0.25 pounds.
The label weight is given as 9 pounds.
Let's do problem A.
What proportion of the hams
actually weigh less than the amount claimed on the label.
I encourage you to pause this video,
and try to solve this problem on your own,
before seeing how I solve this problem.
In my model, I can solve this problem pretty quickly.
I enter
9.2
pounds as the mean
0.25 pounds as the standard deviation.
I enter the upper value as 9 pounds,
and for the lower value,
oh, I'm gonna enter something small, like 0 pounds.
The model calculates that
21
.1
9%
of the hams
will weigh less than 9 pounds.
We can see this area of interest
shown on the graph below.
Now, let's do the second part of this problem.
The owner Glen Henline, is considering
two proposals to reduce the proportion of hams
below the label weight.
He can increase the mean weight to 9.25
and leave the standard deviation the same,
or he can leave the mean weight at
9.20, and reduce the standard deviation
from
0.25
pounds to
0.15,
which change would you recommend?
I encourage you to pause this video,
and try to solve this problem on your own,
before seeing how I solve this problem.
Well, we would recommend that change,
that produces a smaller percent of hams below 9 pounds.
If in my model, I change the mean to
9.
25 pounds, I calculate that
15
.8%
of the hams
will be below 9 pounds.
If I change the standard deviation to
0.15,
the model calculates that
9
.1%
will be
below 9 pounds.
So, to answer this question,
it is better to change the standard deviation
than to change the mean.
In a business scenario, one might ask,
how would you change the standard deviation.
The standard deviation represents the variation
in the process.
What I would do is to explore ways
to modify the equipment,
maybe maintain it a little bit better
to reduce the process variation.
Reducing variation in processes
is always a good thing to do
to improve the quality of products.
Let's do another problem.
Most four-year automobile leases allow
up to 60,000 miles.
If the lessee goes beyond this amount,
a penalty of about 20 cents per mile
is added the the lease cost.
Suppose the distribution of miles driven on four-year leases
follows the normal distribution.
The mean is 52,000 miles
and the standard deviation is 5,000 miles.
Question a asks, what percent of the leases
will yield a penalty because of excess mileage?
I encourage you to pause this video,
and try to solve this problem on your own,
before seeing how I solve this problem.
I do like this problem,
because it does represent a real world situation.
To solve this problem,
I'm going to use my model, column A.
Since we are being asked for more than 60,000 miles.
I enter the mean of 52,000,
the standard deviation of 5,000.
Then
I enter
60,000
as the
lower limit,
and something big as the upper limit.
Oh, maybe something like a 100,000 miles.
Actually, any value
more than three standard deviations away
from the mean, meaning more than 15,000 miles
would be adequate.
So,
100,000 miles works.
The model calculates that 5.48% of the vehicles
will exceed this 60,000 miles.
We can see this area, represented in our graphic below.
Let's look at question b,
if the automobile company wanted
to change the terms of the lease,
so, that 25% of the leases
went over the limit.
Where should the new upper limit be set?
I encourage you to pause this video,
and try to solve this problem your own,
before seeing how I solve this problem.
To solve this problem, I'm going to use column C,
in my model.
Because we are give the percent at 25,
I changed the plot what condition to C,
I entered the
25%,
I copy and paste the mean
and the standard deviations as provided,
and I think that I might be there,
but when I look at the graphic below,
I see this area being centered around the mean,
and I want
this red area, to be pushed to the right side.
This where you can use the switch
to change from centered
to
right.
Once I do this, everything is now looking good,
and we would use
fifty-five thousand three hundred and seventy-two
as the new lease limit.
Now, realistically, we would probably
never use a number like this.
I would either round it up to oh, maybe
fifty-five thousand five hundred
or round down to exactly
55,000
miles.
Let's do question c.
One definition of a low-mileage car
is one that is four years old,
and has been driven less than
45,000
miles.
What percent of the car is returned
are considered low-mileage.
I encourage you to pause this video,
and try to solve this problem on your own,
before seeing how I solve this problem.
To solve this problem,
I go back to column A of my model,
and I'm going to use
45,000
miles
as the upper limit,
and for the lower limit,
I will use something small like 0,
we can see that a little over 8% of the vehicles
will be returned with less than 45,000 miles.
Let's do one more problem.
Refer to the Real Estate data,
which report information on homes sold in the Goodyear,
Arizona area during the last year.
The mean selling price, in thousands of dollars,
of the homes was computed earlier
to be 221,000 thousand dollars
with a standard deviation of 47.11.
Use the normal distribution to estimate
the percentage of homes selling for more than
280,000 dollars, compare this to the actual results.
Does the normal distribution
yield a good approximation to the actual results.
I encourage you to pause this video,
and try to solve this problem on your own,
before seeing how I solve this problem.
This problem is similar to what we did
with our M&M weight data.
I will download the data set from this webpage.
I go to my Excel model,
and select that I am in condition A,
since, we are dealing with greater than 280,
I put the
280
as my lower limit,
oh, I could leave this 45,000 here as an upper,
I just have to have something big for the upper.
Then I enter the mean and the standard deviation,
and the model calculates that
10
.56%
of the homes would be more than
280,000 dollars, I can take a quick look at the graph,
just to verify that this area seems correct, and it does.
Now, I switch over to the data file.
I sort this list by price,
I scroll down
and select those
home values of 280,000
or greater.
I notice in the status line that there are 14 homes
greater than 280,000 dollars.
I could do a quick calculation Excel,
14
divided by
105
is
13
.3%,
so, this actual percent of
13
.3
is not that different than our calculated percent of
10
.5
6,
the difference again is due to the actual home values
not being perfectly normally distributed.
One thing that you should notice about these three problems
that we have just done
is that they are all high numbered problems,
63,
65,
and
74,
the author considers these
to be among the hardest problems
that they could give a student to do,
and we have done them relatively quickly
using our Excel model.
This,
once again, is the power of
using Microsoft Excel
to do statistical calculations.
Remember, that my Excel models are completely open,
and you can always see
how I am performing these calculations.
For example, if you are wondering,
how I calculate the probability in column A,
I would double click on the probability cell, and see
that I am using
the normal distribution function
within Microsoft Excel.
The normal distribution function implements
the equations that we saw in the textbook,
okay, enough for this week.
Next week, we are going to shift our focus
to sample data,
dealing with sample data
is another common data scenario in business.
Thanks for spending some of your time with me.
I hope you have enjoyed what I have shown shared with you.
If you ever have any questions, or need my help, please
let me know.
(mellow music)