Welcome to Finance in Excel video number 107.
Hey, if you want to download this workbook for Chapter 11
or the PowerPoints for Chapter 11,
click on the link directly below the video,
and scroll all the way down to Excel Finance class section.
[? Hey, ?] this video we've got to talk
about how to calculate beta.
Last video we talked about announcements that
reveal unexpected information.
That is the true risk of holding a stock.
And that is what makes a stock price move.
We also talked about, [? hey, ?] unexpected information
comes out, it could be of two different kinds.
It could be systematic, which means GDP jumps unexpectedly.
That's going to affect a lot of stocks.
That's called systematic risk or market risk.
And we talked about on systematic risk
that's like when Boeing announces
a new, unexpected contract.
Hey, that's not going to affect all the stocks
in the market, just that one stock or a few stocks.
So, ultimately, market risk or systematic risk
is what is rewarded in the markets.
And beta is the measure of that.
So, now, how do you calculate beta?
In our textbook, they don't show you how to calculate it.
But it's not too hard.
So I'm going to show you how to calculate it.
It reveals the meaning a bit.
Now, again, it's a measure for a particular stock,
the systematic risk.
In a later video, we'll see how to calculate for a beta.
All it involves is picking a proxy for the market.
And I'm going to pick S&P weekly returns for the last four
years.
I'm shooting this in 2010, so this is going back.
And we definitely have the credit crisis recession stock
returns in here too.
And I'm going to get all these numbers for each week.
And I'm also going to get, for the same exact dates, the stock
price for Whole Foods Market International.
So I want to calculate Whole Foods Market International
beta, the systematic risk for this stock.
Now, what we'll do is we'll plot it.
Returns, and we'll actually calculate
the returns for the S&P, and then
the returns for Whole Food Markets,
not the actual stock value, but the returns.
And this is a scatter diagram.
A scatter diagram plots the relationship
between two variables.
And we'll be able to see what happens as this one moves, what
does this one do?
Well, we can see from this finished product that--
we're going to do this in this video--
you can see that it looks like there's
a direct relationship here.
So as the market moves up, the Whole Foods Market
tends to move up also.
And as the market goes down, Whole Food Markets
tends to go down.
And what we're going to do is we're going to plot this,
and then we'll do with Excel--
makes it so simple--
least squared linear regression to try and get
at the slope of this line.
And there it is, 1.1601.
And that'll tell us for every one unit of market change,
it looks like this will go up by 1.106.
So it's a little bit more than the market.
That means it has a little bit more systematic risk
than the market.
But when it goes down, when we move minus unit here,
this will go down by minus 1.1061.
All right, let's go over to Excel.
And all I did is I went to Yahoo,
and I wanted the weekly data for the S&P and Whole Foods Market.
And I got the close adjusted.
Close adjusted.
This adjusted will account for stock splits and dividends.
Now, all we need to do before we plot it,
and then calculate the slope of the line for our beta,
is to calculate the returns.
Well, here we have weekly data.
So for the S&P I'm simply going to say, hey,
the end value divided by R, [? begin ?] minus 1.
That'll give us our weekly return.
Notice these are relative cell references.
So when I copy it over here, it'll move.
And when I copy it down, it'll work just fine.
So I'm going to copy it over.
And then, I'm going to double click this fill handle
and send it all the way down.
Now, we need to highlight these two labels up here.
Control-Shift and down arrow to highlight all the way down.
Now, let's go to Insert, Scatter.
There's our scatter diagram.
Shows the relationship between two numbers.
Now, this is way down at row 150.
So I'm going to point and click on the edge
and Control-x to cut.
Control-Home key to jump to the top.
Control-v to paste.
All right, so let's look at this chart.
Right off the bat, any time you do
an x-y scatter you gotta put labels to let people
know what the heck's going on here.
I'm going to click there and hit the Delete key.
What variable is here?
And what variable is here?
I'm going to come up to the Chart Tools Context Sensitive
Ribbon Layout.
I'm going to go over to Access Titles.
How about horizontal first?
Title below.
Now, actually, we need to move this.
I've already typed out some titles here.
Now, when you click on this and you see the outline box,
you can then immediately come up to the Formula Bar,
click Type Equals, and then click on our label.
This is the S&P weekly returns.
Enter.
So we have our label right there.
Let's do the same thing Layout, Axis, Vertical Rotated.
It's highlighted.
Click in the Formula Bar, Equal, click Enter.
And I'm going to highlight this and come up
here and type, beta for Whole Foods Market International.
So you can see, for labels you can either type or link them.
Enter.
All right, now, we can see the relationship looks direct.
As the market goes up, Whole Foods Market tends to go up.
And if it goes down, it tends to go down.
But I want a line.
And it's really easy in Excel.
You just click on the lines and right Click Add Trend Line.
There's also a Layout Add Line right here.
I'm going to say Linear.
I definitely want to display the equation, and show R squared.
R squared just tells you what influence
the x variable has on the y.
Not causation, just the influence.
All right, and then, I have this.
I'm going to click on the edge.
And then when I see my Move Cursor,
four-way pointing black arrows that I click and drag.
And there it is.
There's our slope.
That's our measure of beta.
Now, let's see how to do it in the cells with a formula,
because a lot of times you want to use this in calculations.
Drag this over here.
And we're going to see how to do it two ways.
I like the slope way, linear regression.
We want to figure out the m from our y equals mx
plus b, lots of other variations on that,
but it's just the slope.
And guess what?
They named the function smartly, SLOPE.
There we go.
SLOPE.
Now, it's asking for known y's and known x's.
So our y's are our Whole Foods Markets.
Remember, because this is our predicted variable.
This is the predictor variable.
So as the predictor variable moves,
what does the predicted variable do?
So I'm going to take my y's first.
Control-Shift, down arrow.
F4 to lock it.
That's just to jump back up.
We're not going to copy it anywhere.
So we don't need to lock it.
Comma, known x's.
Control-Shift, down arrow.
F4 to jump it back up.
And close parentheses.
So there we have it.
That's the amount of our systematic risk
based on weekly data from the past four years.
However, that's one way to do it.
There's another way to do this.
And this isn't a statistics class,
where we learn how to calculate correlation and what it means,
but it is kind of a cool measure-- correlation.
And standard deviation we already learned
how to calculate in this class.
But it's correlation times standard deviation
of Whole Foods Market divided by standard deviation
of the market.
Now, correlation just tells us the strength and the direction
of a data set.
The relationship between two data sets.
And it's always between negative 1 and 1.
If it's a positive number, it means that it's
a direct relationship.
If it's a negative number, it has an indirect relationship.
Indirect just means as this one increases,
the other predictive variable decreases.
The closer they all are to the line,
the closer they get to a one.
All right, so correlation will tell us the strength
and direction, but notice this [? format. ?] Correlation,
strength, and direction times standard deviation
of the asset divided by standard deviation of the market.
Well, what is this part do right here?
When you take standard deviation of the asset
divide it by standard deviation of the market,
you get per one unit of standard deviation, which is
the total risk of the market.
What's your standard deviation or total risk for Whole Foods?
So it's kind of clever.
Correlation, strength, and direction
times the amount of total risk per unit of market risk.
All right, let's see how to calculate this.
Well, luckily, there's a CORREL function.
CORREL.
And we can array 1 and 2.
So we'll get our y's.
Control-Shift, down arrow, F4, comma, and then our x's.
Control-Shift, down arrow, F4, times, and we'll
do standard deviation.
Now, this is a sample, so we use STDEV.
I'm using 2010 so that these new functions here, but the one we
want down here, these little symbols mean compatibility.
Just means you're allowed to use the old functions.
So I'm going to use that.
Oh, standard deviation of Whole Foods Market.
So I'm going to click there.
Control-Shift, down arrow, F4, divided
by-- because we want total risk per unit of the market risk--
so I'm going to do another STDEV.
And this will be of our market, which
is the S&P, Control-Shift, down arrow, F4.
And that ought to do it.
You get the same answer both ways.
Both of these are methods of calculating
the amount of systematic risk or our beta
for a particular stock.
And we also have our chart, which shows this visually,
where all the data points lie.
And we can also add our trend line and show our equation.
All right, we'll see you next video.