Excel Finance Class 20: Growth Ratios and Market Value Ratios

Download Excel workbook http://people.highline.edu/mgirvin/ExcelIsFun.htm
Learn how to calculate the ratios: Earnings per Share EPS, Price To Earnings PE, Dividend Per Share, Internal Growth and Sustainable Growth.
Highline Community College Busn 233 Slaying Excel Dragons Financial Management with Excel taught by Michael Girvin.

---

Closed Caption:

welcome to excel in finance video number
twenty
hey if you wanna download this workbook
or the PDF's
just click on the link below to video
this is
chapter 3 singer download the chapter 3
workbook NPF hey
we have just a few more ratios to talk
about
wanna talk about the internal growth
rate I'll the sustainable growth rate
and then a few other a market value
ratios no internal growth rate here's
a maximum that the corp Canada to the
corporation can achieve
in growth with no external
financing no issue in debt our equity we
take our way
times be let's be all be
we saw this when we did are income
statements last chapter chapter 2
addition to retain earnings that just
means
we have net income we pay some out as
dividends and the rest we plow back into
that
the company so this is in essence
internal financing
so the way we calculate this be is
addition to retain earning divided by
net
income right if it's all going back
all net income no dividends are being
paid out then this is
wanna I so do that this is for
internal if you're not going to get any
external financing the sustainable
growth rate
this means you're not gonna get
miscalculation you not gonna get
any external financing except
to maintain a constant debt to equity
ratio now
if you have you know good ideas in your
expanding a lot then of course you want
to go out and get
debt or equity are fine they were gonna
look at the price to earnings ratio
simply the price per share and you go
and you can see that in the
for publicly traded companies and you
divide it by
earnings per share earnings per share
just as a all the net income available
available for
earn for this particular period divided
by the number of shares
earnings per share says if
we gave all the profit for this period
out to the stockholders how much would
each one get so make this calculation
and this is called PE ratio it's a
surrogate for growth
people interpret a high PE ratio
as the markets think in that the stock
has a lot of growth potential
finally we have market to book ratio you
take the price per share that out on the
market right price per share price per
share
and you divided by akk with T per share
to actually look on the balance sheet a
figure out the total equity divided by
the number of shares and get
what to book value per share would be so
this is for one share the book value in
this is the market value per one share
when we do that division if we get a
number greater
then won it mean for financial markets
think the corp is worth more than the
book value and that's just mean
mostly the case most often the case now
huh not always but
I am and less than one mean for
financial markets think the corp is
worth
lasts the corporation's work lasts than
the book
value all
let's go over to excel right here's
are a workbook and more on the growth
market a ratio so growth and market
ratios sheet I'm here we have our net
income per 2006 we have a few numbers
that we've been working with throughout
this whole chapter
dividends paid in 2006 book value
%uh equity in 2000 and 6
here's overturn for on assets we
calculated that last video
return on equity and there's are
be this is simply
I'll Africa over here and look
mmm
this is 25 2005 calculations are right
here
here's our net income here's the
dividend so the calculation for
amount we get to plow back into the
company hehehe our net income minus or
dividends
that certain cell C seventeen so I went
ahead and
calculate this that amount
you can see the blue box right there
divided by the income
now this is the internal growth waited
for not getting it issue
it any debt or equity so in parentheses
were gonna say
actually don't have to equals the Roha
return on assets times are
be and then we're going to divide it by
lawn now mine s that same thing
return on assets times be
4.52 now if or going to
are keep a constant debt to equity ratio
which
some firms like to do it's the same
calculation except for we're going to be
doing
it said a return on assets return on
equity
equals I'll return on equity times that
be
provided by in parentheses one minus
return on equity
times the be close parentheses
so but also the intro growth rate
sustainable growth rate
we now look at some those market price
now here we went out this is all for
2006
our financial statements were ending on
September 26 or something like that
I'll September 24th in in 2006 0
I went out got the number shares from
Yahoo Finance sir
Google Finance for one of those and we
got our market value of stock now this
is 2006 and she in the studio 2010
I stock prices come down quite a bit
and idk you gotta expect that right what
does Whole Foods do they sell
high-end goods I at least according to
the perception
of customers their high and good they
actually do a pretty good job of keeping
prices low
for organic and natural items and they
do it because they
have the economies of scale they are the
biggest
chain that sells that kind of food
alright so let's do our earnings per
share
okay we got this from you know the
market track
to go up early cat equals arnett income
for 2006
divided by earnings per share so this is
if we're gonna pay out all
love Tiger is net income Corp each
stockholder we get one dollar in 46
cents now this circuit for growth are
p easy ratio we say equals
whatever the price is divided by
this earnings per share wow that's huge
40 so in 2006
the markets absolutely had a I'll
bull they believed that Whole Foods was
expanding and if you look at the
financial statements read their
I am the notes to the financial
statements read their blogs at their
website read their manager
statement saying that they were and they
they were expanded a lot
when the financial crisis hit now they
had to stop something that
on and people bought less at the high
end item so this was
this is high back in 2006 but maybe
somewhat justified
they work spanning no dividends per
share that's just a straight calculation
we need to know what the dividends are
so dividends
to fight it by are number of
outstanding shares at tellers at the
cash
that each stockholder got the shared
to dollars and 56 cents
probably our last ratio we're gonna look
at this market to poke
and if we know the market value a
of the stock and the book value out the
stock
we do this ratio and if it's bigger
that means the markets are valuing the
company more than the actual
company is on the books so let's go
ahead and do it
there's are stock price that the
a stock price for one stock
divided by and now we're gonna have to
do calculations we don't have book value
per stock but no problem we take our
equity divided by
in parentheses are I
shares outstanding
until five point a 9 that means
that for every one dollar a book value
there's for high colors and 89 cents
I love a market value
to let's look at some these numbers in
comparison
this sheet over here there's industry
averages and it more
industry looks like it industry average
again this is back in 2006
price to earning for grocery stores in
this entry industry were
17 wow look at that
again if price for earnings is a circuit
for growth
fee markets absolutely shot that Whole
Foods was gonna
expand a lot faster than Sabourin it was
there
in the of its 2000 to 2006
the demand for organic and natural foods
was
increasing dramatically in fact Safeway
an
Walmart and Fred Meyers and QFC and many
other
I'm food chains were try to catch up
right cuz they saw that the demand was
increasing cell
I M A T a price to book not much
difference
profit margin you expect this I have a
percentage format here if I
click here and I'll and then increase
the decimal
you can see in percentage right but
that's to be expected cuz they had sell
high-end
that grocery chains are usually low
profit margin high turnover they go
through
sell a lot sell a lot but Tom you expect
that this high and
whole foods that have a higher profit
margin and they do
return on equity just a little bit
higher and look it
yes so remember when we looked at the
DuPont analysis
you can %ah increase
return on equity when you have more debt
are if you can meet your
debt payments of course so this is lower
then
for the industry return on equity is
lower than it is for Whole Foods but in
part
they increase this so this would be even
lower
if they had the same debt/equity ratio
asshole fu to Whole Foods
is about getting a good return on equity
with not so much debt they will the last
debt you have
the less chance you have of choline on
defaulting on debt and getting into
trouble
right now these are averages and
the that is very useful but sometimes
you you don't want to look at averages
you want to look at I'll a little bit
more data
now an average says what's in the middle
this is the Middle High
you look at all the numbers for the
industry Adam up divide by the count
but we also sometimes can look at what
are called cork tiles
what's going over here and there are
textbook talks about this
co-op median is the one in the middle so
PE ratio 17
on point for so I got some to import
file old little bit different rounding
here
but that's the one in the middle median
means
you line I'm all out and actually the
one that is positionally in the middle
it's a type of Albert court I'll well
what is a
median mean it means that 50 percent in
2000 below and 50 percent by Zara but I
tip
I did that motion wrong this is below
this is about but this one when you say
low score talk means
25 percent of the values are below and
75
a buff the highest quartile the third
says 25 percent above
75 percent below so it's in essence were
getting our range
our values we can look at right so the
lowest quartile
that's 15 .3 them one in the middle is
17 and the highest
amongst all the their grocers and people
sell food is twenty
you can see for PE we are warning
air-popped the highest quartile
here return on equity were in between
the
middle and the highest court also that
means there are some
people in this industry that are earning
you know more than are fourteen point
five percent
or decimal long-term equity our way down
here one the lowest or near the lowest
quartile
which you know when you leverage op
you can buy more assets and if you have
a good idea that can be good but
at the more debt you have the more risks
you have parted the reason
no Whole Foods could really had big
trouble in the
when the financial crisis hit because
they're selling high-end items and what
do people do when they're
in a recession they don't buy high-end
items but
they didn't have all a lot of a data at
least not in 2006 which probably helped
them
a lot you know member what happen in the
financial crisis so
some of the up some big retailers went
bankrupt
and it was because they had too much
debt alright so when people stop buying
things you have a lot of debt
you can't meet your interest payments
and you're in big trouble price to
earnings
I'll be our way up of that's similar to
this right
price targets for the market is valuing
them very highly and then finally
net profit margin I look at this were
whole
Whole Foods is way above and you would
expect that cuz they're a high
and I seller
alright town that's it for chapter 3
chapter 4&5 comes up next and then we'll
stop
their start talking about cash flows and
actually making a
in investment like into a bank account
with an interest rate
and figure out what the value of that
investment will be
alright see you next chapter