Welcome to Highline BI348 class video number 23.
If you want to download this workbook, BI348Chapter02Start
or the finished file, click on the link below the video.
In this video, we've got to talk about variation.
Now, what's variation?
Well, it's going to be closely related
to the mean calculation.
And here's our example.
We have two companies, Banderson's and Plywood
and Floor.
Our company makes boomerangs and we order custom aircraft birch
plywood from each one of these companies.
Here's the last 20 orders.
And this is a column of number of days it took for Banderson's
to get the shipment to us.
Here's a column, number of days for each one of the orders
that Plywood and Floor took to get the custom order to us.
But look at this.
The average is 9 for Banderson's and for Plywood and Floor.
How in the world do we decide which
supplier we might like to choose going forward?
Well, which one's more reliable?
In order to answer that, we have to figure out
which mean more fairly represents its data points.
Now, here's a great visual over here.
The red one, that's the mean for Plywood and Floor.
And this is the mean for Banderson's.
That means on this axis here-- this is number of days.
Here's the mean.
Here's all the data points.
Here's the mean.
Here's all the data points.
It definitely it looks like for Banderson
that the data points are more spread out, more dispersed.
Now you can imagine from a planning point of view,
here's one shipment that came within 4 days.
Here's a shipment over here that came within 16 days.
Well, if we were going off the average
and we had planned for this to come in 9 days
and it comes 4 days early, our manufacturing facility
might not be ready to receive that,
whereas here if we were planning on getting it at day 9
and it comes on day 16, well, that's
long past when we were planning to use that wood.
So we would like a measure that could tell us
the dispersion in data sets.
Now, the calculation we're going to use
is called standard deviation.
And the essence of a standard deviation
is going to look at the deviation between the mean
and each one of its data points.
So you could imagine if I took 4,
the particular value in this case, minus 9,
I would get minus 5.
That means a deviation for this particular value
from the mean of minus 5.
Up here, this would be 16 minus 9,
so it would be a deviation of plus 7.
So if we could take all of those deviations
and use them in a calculation to measure
variation or dispersion, that's what we want to do.
And our calculation is standard deviation.
And we're going to go over to the sheet SD
and calculate standard deviation for Banderson's days
for shipment and Plywood and Floor.
Now, the base calculation is the deviation,
so we're going to take the particular value minus--
and I've already calculated mean, the count,
and count minus 1 up here.
I have to lock this with F4, Control-Enter,
and copy it down.
Now, what we'd like to do is simply just
add them up and divide by the count to get a type of average
for our deviations.
But we can't do that, and here's why.
If I add up all the deviations, Alt-equals,
I'm always going to get 0.
And that always will be the case.
If we come over here to Plywood and Floor
equals that first particular value minus the mean of 9,
F4 to lock it, Control-Enter, and copy it down,
come down to the bottom, this is a completely different set
of deviations.
Alt-equals, it's still 0.
Anytime you have a set of data points, you calculate the mean,
subtract it from each one to get your deviations and add them,
you're always going to get 0.
So there's actually a couple ways we could deal with this.
We could actually, as in our standard deviation formula,
we could square them to get rid of the minuses
and then do the rest of this calculation here.
Or there's a separate calculation where
we take the absolute value.
Standard deviation is the most common calculation
and so that's the one we're going to do.
Notice we're going to take each deviation,
square it, add them all up, divide by n minus 1.
And the inside part under the square root side, that's called
variance.
And that's a measure of variation also.
But square root, that will give us our standard deviation.
And we're using a little s here, because this is sample data,
so little s represents sample standard deviation.
All right.
So we're going to take our deviation
and square it, Control-Enter.
Now remember that we squared all these.
That's in essence why at the end we
have to take the square root, to undo the square.
So our mechanism for getting around that 0 was squaring,
but in the end we take the square root to undo it.
Now we've squared all of the deviations.
That's that part.
The summation sign means we need to Alt-equals and add them
all up.
So 182, that's the numerator here.
Now let's do this for Plywood and Floor
equals deviation caret 2, Control-Enter, copy it down.
Now we can add this up.
Alt-equals, Control-Enter, and that 20 for Plywood and Floor,
that's the numerator there.
Now I'm going to scroll down a little bit.
Sample variance is represented with s squared,
so I'm going to take the sum of all the deviations squared
and divide it by our n minus 1.
Control-Enter.
That's sample variance.
Now we went ahead and went through longhand
how to calculate this so we could sort of see,
hey, there's a 0.
We had to do something about it.
We got around it by squaring it.
But in essence, we did it longhand
so we could see if it's a type of average
of all of these deviations.
But of course there is a built-in function.
So for variance equals VAR and P is for population.
Dot s is for sample.
I'm simply going to tab and highlight the actual values.
Control-Enter gives me the exact same number.
Now here, we squared it to get around that 0,
so we're going to do the square root now.
Equals SQRT for square root, and I'm
going to take the square root of the variance.
And boom, there's our standard deviation.
Of course, there's a built-in function STDEV.
And there's a P for population, an s for sample.
We go to our source data, and that's
how we're going to do it moving forward in the class.
We simply use the standard deviation for the sample
function and we get 3.61.
Now what that means is that Banderson's has
on average a deviation of 3.61 days.
Now let's do our calculations over here
for Plywood and Floor.
The sum of all the deviations squared divided by n minus 1,
there's our variation.
We can use VAR.S, source data, and Enter.
We get exactly the same.
We take our variance and we use the square root function.
It's perfectly all right to caret and take
the exponent of 1 divided by 2, which
is the same as taking the square root.
But you've got to be sure and put
1 divided by 2 in parentheses to force the division
before the exponent.
Boom.
I'm going to get 1.2.
If we use our STDEV.S function, we take our source data
and Enter, we get 1.2.
And boom, there we go.
The more reliable supplier is Plywood and Floor.
On average they have a deviation of 1.2 days.
On average Banderson has a deviation of 3.61.
We are using the numerical measure standard deviation
of the sample to measure variation
to make a business decision.
All right.
Now we want to go look at one last example on the sheet
variability.
Now actually, this data set here,
it goes from row 20 down to 755.
And this is 10-year US government bond yields.
And there's a huge range of values in here,
you can imagine, if you go from 1953 to 2014.
And we want to calculate a few measures of variation.
Now, we can simply say, hey, what's the max in here?
And I'm betting it somewhere in the late '70s or '80s.
It was terribly high back then.
15.32% was the return on a 10-year US government bond.
Now, the min, that's probably somewhere
in the more modern era.
In particular I bet it's right about the financial crisis
era, 2008 to '10.
And let's see.
1.53.
Now, we can't see those.
You could actually highlight all these
and right-click, Unhide, and try and find them.
But wow, that is a huge spread.
So using the max and the min, we can get an idea of variation,
right?
But it's only using two numbers, as we saw just
in our last example.
The standard deviation, however, uses all of the data points.
Now, our sample size equals count,
because we're counting numbers.
And notice I have those rows hidden.
But when I go from 2 to 755, it's
also highlighting all those hidden numbers in cells.
Enter.
So we had 736 equals n minus 1.
And we can calculate our average.
And wow, when I highlight from 20 all the way down to row 755,
that's spanning the years '53 to 2014.
So the average is 6.07.
And as we mentioned, the range is simply
whatever the max is minus the min.
That is a blunt measure of dispersion.
Now, sample variance, we VAR.S. And I'm
going to highlight-- broop.
And so remember, that is before we do the square root.
And actually, because we squared our units,
we actually have a problem with squared units
for this particular measure here.
A lot of times we use standard deviation instead.
But we will see some important calculations later
in this class where variation is the preferred
measure for variation.
Our standard deviation equals STDEV.S, highlight.
And there we have 2.7, so the standard deviation
for this huge period is on average the deviation is
2.71 percentage points.
Now, standard deviation, variance, and range
are all measures of variations.
There's another measure of variation
that's pretty important, coefficient of variance.
And it's important because we'll take standard deviation
and divide it by mean, which will
mean it gives us standard deviation per one unit of mean.
And this is good when you're comparing variances
from different distributions, particularly when
the means are really far apart or the units are different.
So I could in this case take the standard deviation
and I want to express it in terms of units of mean.
That means in our case for every 1 percentage point,
the variance would be 0.45.
Still another measure for variation is z-score.
And we did a lot with z-scores in the pre-req class,
Business 210.
But here's a particular value from this government bond
data set, 10.37.
If I were to take the deviation, which is the particular value,
and subtract the mean, right there
that gives me the deviation.
So this particular one was 4.29 above the mean.
But now I want to take that particular deviation
and describe it in terms of number of standard deviations.
So when I divide it by number of standard deviations,
the z will always tell us number of standard deviations
a particular value is away from the mean.
And we remember from our pre-req that we use this all the time,
especially with our normal curve.
Here, 15.32, that was the max value.
I want to figure out how many standard deviations this
is above the mean, so equals in parentheses particular value.
We subtract the mean and we divide it
by our standard deviation.
And boom, 3.3 standard deviations above,
because it's positive.
Now, we remember from our study one way
to measure outliers, extreme values, is the 3 z-score rule.
If this were a normally distributed distribution--
and we'd have to plot it and look-- finding a value that
is 3 standard deviations away has a probability
that is incredibly small.
So that's why the z-score of 3 z's is used sometimes
to identify outliers.
Now, just a couple other things that
are not paramount to this video, but if we needed
to calculate the standard deviation
longhand, we don't have to do it in two columns.
I can simply say equals whatever the deviation is
particular value, subtract the mean, F4 and then caret 2.
I copy that down.
This is just deviation squared.
That total right there can be then used.
Remember, the variance is the inside here.
So I take, boom, the total all of the deviations
squared divided by n minus 1.
That would give me variation.
F2, and then I could take the square root
to get my standard deviation.
Another thing is last chapter we learned about the INDEX
and MATCH functions.
Well, notice we have a huge column here.
And we calculated the min and max.
Well, notice we have some dates here,
so we could use INDEX and MATCH functions to look up
15.32, find the relative position amongst the rates,
and then return the date.
Now, lookup functions in Excel cannot handle duplicates,
so if there is a duplicate, it'll only find the first one.
But let's go ahead and remind ourselves from last chapter.
INDEX, the array, those are the values
you want to go and get and bring back to the cell.
I'm going to hit F4 to lock this,
because I am going to copy this down, comma,
and then I need the relative position or row number.
1, 2, 3, 4, 5, all the way down to 736 it looks like there are.
So I use MATCH.
MATCH is a lookup function that can look up a value, comma,
amongst a bunch of values.
So we're looking up 15.32 and MATCH
is going to look through here and tell us
what position it is.
And I need to lock that, F4, comma,
and we're doing exact match, because those values are not
sorted.
Close parentheses, close parentheses.
Again, lookup functions only find the first match.
If there are duplicates, it will only tell us
the first date it found.
And sure enough, luckily, we're Excel pros
and we know that's a serial number.
So we simply have to format it as a date.
And there it is.
9/1/1981.
When I copy this down, boom.
7/1/2012.
So that was a few years after the real heart
of the financial crisis.
But in the '70s there was a lot of inflation, which led
to really high interest rates.
And then after the financial crisis,
the Fed brought the rates down to 0
and all the other related rates were very low.
All right.
So in this video, we had a little fun at the end
with INDEX and MATCH, but the main point was variation.
On this sheet, we saw z-score is a very important measure
of variation.
We saw coefficient of variance, if you have especially means
that are far apart or different units.
We saw standard deviation, variation, and range.
Back on SD, we saw the two suppliers
and we did standard deviation longhand.
And then of course, this awesome chart
to introduce the idea of spread in the data
and using the standard deviation as a measure of how reliable
a particular mean is.
All right.
Next video, we'll actually talk about mean, standard deviation,
z, and the bell curve and the empirical rule.
All right.
We'll see you next video.