馃攳
Excel Finance Class 107: Calculating Beta (2 Methods) & Chart Beta, SLOPE CORREL & STDEV functions - YouTube
Channel: unknown
[0]
welcome to finance an excel video number
[1]
107 hey if you want to download this
[4]
workbook for chapter 11 or the
[5]
powerpoints for chapter 11 click on the
[7]
link directly below the video and scroll
[9]
all the way down to Excel finance class
[11]
section a this video we got to talk
[13]
about how to calculate beta last video
[15]
we talked about announcements that
[19]
reveal unexpected information that is
[23]
the true risk of holding a stock and
[26]
that is what makes a stock price move we
[29]
also talked about a unexpected
[31]
information comes out it could be of two
[33]
different kinds it could be systemic
[36]
which means GDP jumps unexpectedly
[39]
that's going to affect a lot of stocks
[41]
that's called systemic risk or market
[43]
risk and we talked about unsystematic
[46]
risk that's like when Boeing announces a
[48]
new unexpected contract hey that's not
[51]
going to affect all the stocks in the
[53]
market just that one stock or a few
[55]
stocks so ultimately market risk or
[59]
systemic risk is what is rewarded in the
[62]
markets and beta is the measure of that
[64]
so now how do you calculate beta in our
[67]
textbook they don't show you how to
[68]
calculate it but it's not too hard so
[70]
I'm going to show you how to calculate
[72]
it reveals the meaning a bit now again
[76]
it's a measure for a particular stock
[78]
the systematic risk later we'll see in a
[82]
later video we'll see how to calculate
[83]
for a beta all it involves is picking a
[88]
proxy for the market and I'm going to
[91]
pick S&P weekly returns for the last
[93]
four years I'm shooting this in 2010 so
[96]
this is going back and we definitely
[97]
have the credit crisis recession stock
[102]
returns in here too and I'm going to
[105]
pull I'm going to get all these numbers
[107]
for each week and I'm also going to get
[109]
the set for the same exact dates this
[111]
stock price for Whole Foods Market
[114]
international so I want to calculate
[115]
Whole Foods Market international beta
[118]
the systematic risk for this stock now
[121]
what we'll do is we'll plot it
[124]
data returns and will actually calculate
[126]
the returns for the SP and then the
[128]
returns for whole food markets not the
[130]
actual stock value but the returns and
[133]
when we plot them you we can clearly see
[136]
that and this is a scatter diagram a
[138]
scatter diagram plots the relationship
[140]
between two variables and we'll be able
[142]
to see whether what happens as this one
[145]
moves what does this one do well we can
[147]
see from this finished product here
[149]
we're going to do this in this video you
[151]
can see that it looks like there's a
[153]
direct relationship here so as the
[156]
market moves up the Whole Foods Market
[160]
tends to move up also and as the market
[162]
goes down whole food markets tends to go
[164]
down and we're going to do is we're
[167]
going to do plot this and then we'll do
[170]
with Excel makes it so simple least
[174]
squared linear regression to try and get
[177]
at the slope of this line and there it
[180]
is one point one six zero one and
[183]
that'll tell us for every one unit of
[187]
market change it looks like this will go
[191]
up by one point one zero six so it's a
[193]
little bit more than the market that
[195]
means it has a little bit more
[196]
systematic risk than the market but when
[198]
it goes down by one when we move minus
[201]
one unit here this will go down by minus
[203]
one point one zero six one all right
[206]
let's go over to excel and all I did is
[210]
I went to Yahoo and I calculate I wanted
[213]
the weekly data for the SP and Whole
[215]
Foods Market now I got the clothes
[218]
adjusted clothes adjusted this adjusted
[221]
will account for stock splits and
[224]
dividends now all we need to do before
[227]
we plot it and then calculate the slope
[229]
of the line for our beta is to calculate
[231]
the returns well here we have weekly
[235]
data so for the S&P I'm super we're
[236]
going to say hey the end value minus our
[240]
begin I'm sorry divided by our begin
[243]
minus one that will give us our weekly
[245]
return notice these are relative cell
[248]
references so when I copy it over here
[250]
it'll move and when I copy it down it'll
[252]
work just fine
[254]
so I'm going to copy it over and then
[257]
I'm going to double-click this Phil head
[259]
and send it all the way down now we need
[262]
to highlight these two labels up here
[264]
ctrl shift and down arrow to highlight
[267]
all the way down now let's go to insert
[269]
scatter
[271]
there's our scatter diagram show us the
[273]
relationship between two numbers now
[276]
this is way down at row 150 so I'm going
[279]
to point click on the edge and ctrl X to
[282]
cut control home key to jump to the top
[286]
ctrl V to paste all right so let's look
[291]
at this chart right off the bat anytime
[294]
you do with XY scatter you got to put
[296]
labels to let people know what the
[297]
heck's going on here I'm going to click
[299]
there and hit the Delete key what
[302]
variable is here and what variable is
[304]
here I'm going to come up to the chart
[306]
tools context-sensitive ribbon layout
[308]
I'm going to go over to axis titles how
[312]
about horizontal first title below now
[317]
actually we need to move this I want to
[319]
get to I've already typed out some
[321]
titles here now when you click on this
[323]
and you see the outline box you can then
[326]
immediately come up to the formula bar
[327]
click type equals and then click on our
[332]
label this is the SP weekly returns
[334]
enter so we have our label right there
[337]
let's do the same thing layout axis
[339]
vertical rotated it's highlighted click
[344]
in the formula bar equal click enter
[351]
and I'm going to highlight this and come
[353]
up here and type beta for Whole Foods
[358]
Market International so you can see for
[362]
labels you can either type or link them
[364]
enter alright now we can see the
[369]
relationship looks direct as this one as
[371]
the market goes up Whole Foods Market
[373]
tends to go up and if it goes down it
[376]
tends to go down but I want to line and
[378]
it's really easy in Excel you just click
[380]
on the lines and right click add
[384]
trendline there's also a layout add
[386]
trendline right here I'm going to say
[389]
linear I definitely want to display the
[393]
equation and show r-squared our score
[395]
just tells you what influence the X
[398]
variable has on the Y not causation just
[402]
the influence all right and then I have
[404]
this I'm going to click on the edge and
[406]
then when I see my move cursor four way
[410]
pointing black arrows and I click and
[412]
drag and there it is there's our slope
[413]
that's our measure of beta now let's see
[417]
how to do it in the cells with the
[419]
formula because a lot of times you want
[421]
to use this in calculations drag this
[424]
over here we're going to see how to do
[425]
it two ways I like the slope way linear
[430]
regression we want to figure out the M
[433]
from our y equals MX plus B lots of
[439]
other variations on that but it's just
[440]
the slope and guess what they name the
[442]
function smartly slope not there we go
[447]
slope now it's asking for known Y's and
[450]
known X's so our Y's are our Whole Foods
[455]
Markets remember because we're R this is
[458]
our predicted variable this is the
[460]
predictor variable so as the predictor
[463]
variable moves what does the predicted
[465]
variable do so I'm going to take my Y's
[467]
first control shift down arrow f4 to
[471]
lock it that's just to jump back up we
[473]
know we're not going to copy it anywhere
[474]
so we don't need to lock it
[476]
comma known X's control shift down Oh f4
[480]
to jump it back up
[483]
and close parentheses so there we have
[488]
it that's the amount of our systematic
[489]
risk based on weekly data from the past
[492]
four years however that's one way to do
[495]
it there's another way to do this and
[497]
this isn't a statistics class where we
[500]
learn what how to calculate correlation
[502]
and what it means but it is kind of a
[504]
cool measure correlation and standard
[507]
deviation we already calculate in this
[511]
class but it's correlation times
[513]
standard deviation of Whole Foods Market
[515]
divided by standard deviation of the
[517]
market now correlation just tells us the
[519]
strength and the direction of a data set
[524]
the relationship between two data sets
[526]
if it's always between negative one and
[530]
one if it's a positive number it means
[532]
it has a direct relationship if it's a
[534]
negative number it has an indirect
[538]
relationship indirect just means as this
[540]
one increases the other predictive
[542]
variable decreases the closer they all
[545]
are to the line the closer they get to a
[547]
one all right now so correlation will
[551]
tell us the strength and direction but
[553]
notice this formula correlation strength
[555]
and direction times standard deviation
[557]
of the asset divided by standard
[558]
deviation of the market well what is
[560]
this part to right here when you take
[562]
standard deviation of the asset divided
[564]
by standard deviation of the market you
[565]
get per one unit of standard deviation
[569]
which is the total risk of the market
[570]
what's your standard deviation or total
[573]
risk for Whole Foods right so it's kind
[576]
of clever correlation strength and
[577]
direction times the amount of total risk
[581]
per unit of market risk all right let's
[584]
see how to calculate this luckily
[587]
there's a Correll function Correll and
[590]
we can array one and two so we'll get
[594]
our wise control shift down on f4 comma
[598]
and then our X's control shift down
[600]
arrow f4 x and we'll do standard
[604]
deviation now this is a sample so we use
[606]
esti de vie I'm using 2002 10 so these
[611]
new functions here but when we
[614]
down here these little symbols mean
[615]
compatibility this means you're allowed
[618]
to use the old functions so I'm gonna
[620]
use that Oh standard deviation of Whole
[622]
Foods Market so I'm going to click there
[623]
ctrl shift down arrow f4 divided by
[627]
because we want total risk per unit of
[631]
the market risk so I'm going to do
[632]
another stdev and this one will be of
[639]
our market which is the SP control shift
[643]
down f4 and now how to do it get the
[647]
same answer both ways both of these are
[649]
methods of calculating the amount of
[652]
systematic risk or our beta for a
[655]
particular stock and we also have our
[658]
chart which shows us visually visually
[665]
where all the data points lie and we can
[669]
also add our trendline and show our
[671]
equation all right we'll see you next
[674]
video
Most Recent Videos:
You can go back to the homepage right here: Homepage