Welcome to Excel 2013 statistical analysis video
number 32.
Hey, if you want to download the source book and follow along,
click on the link below the video.
Hey, last video we talked about discrete probability
distributions.
And we also talked about calculating an expected value
and variance and then taking the square root
to get standard deviation.
Now in this example, we have an accounting example.
So the accounting department, they've
looked at the past records, and they buy in lots of 100.
And here's the probability from the past
of on any particular month purchasing
that number of units.
So 800, there's a 0.2 probability.
900 units purchased, there's a 0.2.
So from this past data, our x units
demanded and our probability, our frequency distribution
with a relative frequency, we can calculate expected mean.
And this will be a number we can use in budgeting and planning.
Now from last video, we saw all the x's times
all the probability.
That means one whole column of x's times
another whole column of probabilities
multiplying and then adding, we use sum product.
Array 1, that's the first column to multiply comma
to get to the next rate and the second column.
This is a type of weighted average, a weighted mean.
Make sure that both of the dimensions for the two arrays
are the same and enter.
So on average, we ordered from the past 790 units,
so we could round that up to 800.
Now we also want to be able to calculate
the standard deviation.
This is our measure of variation.
So equals sum product, oh, last video,
we saw how to calculate this longhand,
and we extrapolated from our longhand
into a single cell formula.
If you didn't watch that, I'd go watch
that video to see what this whole formula means,
but what do we do?
Inside a formula, we go the whole column
x minus our expected value, and then square it.
That will be our first array.
So I'm going to highlight all of the x's.
From each individual x, I need to subtract the expected value.
That gives us our deviations, close parentheses,
and caret to.
That is our first array.
If we wanted to look, we could click on our screen tip
array 1, F9 to evaluate.
We could see that this, if we did it longhand,
would be our helper column, Control-Z.
But we don't want to use the helper column.
It's easy enough to do it in a single cell,
especially in examples like this where we do not
need the individual numbers.
We just need the answer.
Standard deviation comma array 2.
Well, the formula says once we do all
that, we have to multiply it by the whole column
of probabilities.
Close parentheses, Enter.
You gotta be kidding.
That is variance.
We want to go ahead and calculate standard deviation.
F2, so we come to the front and after the equals sign,
square root.
We could also raise it to the 1/2, but I like square root.
All right, Enter, and that is our variance.
So our expected value or weighted average
or budgeted average is 790 with the standard deviation
of 170 units.
Now we have our number that we're
going to use for budgeting.
So let's say we order 800.
Hey, and then we sell the product for 125,
the cost per unit was 60.
And we actually sold 500.
If we were doing this all cash, we
could easily calculate the amount of cash in.
That's the revenue.
The 500 times the 125, but remember,
we went ahead and ordered this 790.
And I'm actually going to round it to 800 using mround.
Now mround is not normal round because if I
click on this number. right here,
comma the multiple, I say round to the nearest whatever,
I'm going to put 100.
If this number was 749, it will go down to 700.
If it's 750 or above, it goes up to 100.
That's what mround does.
Now that's the units.
F2, and I'm going to multiply it times our costs per unit,
Enter.
So we could figure out if these were all cash transactions,
the cash implication.
I could say the cash in minus the cash out.
There's a disconnect between how many we sold
and how many we ordered.
Now be careful.
This is not accrual accounting.
Accrual accounting would match 500 units of revenue
to 500 units of cost.
All right, now we want to go look at another example.
This was an accounting example.
Let's look at a finance example.
Now here, we don't have a discrete variable over here,
but we do have some probabilities.
Now here's the situation.
We have a portfolio of stock A, B, and C.
In this example here, you will see
when you get to your finance classes,
this is a transfer class, so you have to take this class
and then go take your accounting sequence and finance sequence.
Every single textbook you have, you will see this.
And when you get out there, if you're doing finance,
you'll do this.
Now these probabilities, some economists
estimated the probability of the state
of the economy, the chance that there's a boom is 15.
The chance that it will be normal is 0.3.
And then the chance that we're going to have a bust economy
is 0.55.
So it looks like people are pretty gloomy about the future.
Now these are subjectively created,
but that's what we're going to use.
Now in order to calculate what's called the expected return,
we have to take our return for each one of the stocks
and multiply it by the probability.
Now these individual probabilities
mean that we expect for stock A to get 15% when
it's boom, 7% when it's normal, and minus 2% when it's bust.
So we can simply use the sum product
to multiply these, so equals sum product.
Now we've got to be careful here because I want to copy
this formula to the side.
Notice right now, the first column
is going to be relative as I copy it, comma, and then
our array 2.
Each one of these columns, boom, boom, boom,
have to be multiplied by the probabilities
for the state of the economy.
So I'm going to hit F4 to lock it, and that's it.
Expected return, expected value, our average or mean.
Click on the last cell, F2, and there we go.
So we have our returns.
Now notice once we'd calculate what's
called expected return, or weighted average,
even though stock B looked great if we were in a boom at 0.25.
The average here, given our probabilities,
is incredibly small compared to these other two.
Now we want to calculate our standard deviation,
standard deviation in finance as a proxy for risk.
Hey, we're going to use our formula that we just
did in our last example and last video.
Our first array, we're going to have
to take our particular x's, subtract our expected value,
close parentheses.
I can see I forgot that parentheses,
so I'm going to come back here.
When I type our parentheses there,
it turns orange at the end.
That's very helpful.
Square it, and that's deviation squared
for our first array, comma, and our second array,
the probabilities.
F4 to lock it, close parentheses, Control-Enter.
That gives us the variance.
We have to F2 and wrap the square root function
around that.
Square root of the variance, Control-Enter.
That gives us our standard deviation.
Now I can copy it over, 0.06, 0.15, 0.3.
So clearly, the most variation comes from stock B
Now in chapter 3, we talked about coefficient of variation.
We took standard deviation divided it by mean
and got standard deviation in terms of one unit of mean.
So let's go ahead and calculate that to see
our relative measure of variation.
So standard deviation divided by our expected return,
Control-Enter, and copy it over.
So clearly, the most standard deviation per one unit of mean
is stock B. Now in finance, you often
see the inverse of standard deviation divided by mean.
Hey, mean divided by standard deviation.
That says for every one unit of risk
because standard deviation is a proxy for risk,
for every one unit of risk, what is our returns?
So I'm going to do the inverse, and take
the mean expected return in the numerator divided
by our standard deviation, Control-Enter,
and copy it over.
So the biggest one will tell us the most expected
return for every one unit of standard deviation.
And that is stock C.
Hey, so in this video, we saw how
to do expected return and standard
deviation for a finance example and for an accounting example.
Next video, we're going to talk about the amazing binomial
distribution and calculate probabilities
using the binome.dysfunction.
All right, we'll see you next video.