Excel Finance Class 14: Financial Statement Ratio Analysis - #1 Trick For Ratio Analysis

Download Excel workbook http://people.highline.edu/mgirvin/Ex...
Learn about how to complete financial statement Ratio analysis, create common sized Financial statements, why we use accounting information and problems with financial statement analysis. Learn an important trick that will make Ratio Analysis more easily understandable!
Highline Community College Busn 233 Slaying Excel Dragons Financial Management with Excel taught by Michael Girvin.

---

Closed Caption:

welcome to XL finance video number 14
if you want to download this PDF or the
excel file that will be using for
chapter 3 you can click on the link
below the video and go to the finance
section the file business-to-business 23
chapter 3 or the pdfs for chapter 3 now
let's start over in the PSA chapter
three financial statement analysis using
ratios now ratios are used everywhere in
almost all endeavors to compare numbers
using division and some of the things we
learned in this chapter are not only
awesome for finance accounting financial
statement analysis but also other fields
also our topics
why use financial statements as we
talked about in earlier videos
accountants do not necessarily use cash
flow numbers finance people are
interested in cash flow numbers and if
the financial statements have been
around for awhile
are they relevant so we'll talk about
why we use them will talk about problems
with financial statement analysis will
talk about the vast majority of what we
do we'll just be calculating financial
statement ratios not only common size
statements but ratios and then why do we
use ratios to analyze financial
statements
alright let's start off with ratios and
financial statement analysis financial
statements such as the balance sheet and
the income statement or accounting
information as we mentioned although
financial managers were prefer market
value information and there's lots of
market value information out there and
in fact there's all sorts of different
information
alright but sometimes you know we would
prefer this but accounting information
is often the best we can get right and
here's really what happens my accounting
information financial statements can be
so important here is inside the
corporation is outside
people are out here creditors suppliers
customers stockholders government's
managers are inside they control the
assets the accounting records all sorts
of things now presumably we have
independent auditors coming in and
making sure and in fact that's why
auditing exists auditors come in and
make sure that the managers are
following the regulation that the
government creates for financial
statements the regulation created by the
government is its sole intent is to make
sure that good information comes out of
here to all the outside all the people
outside good regulation will and good
incentives from various places will
force the managers to create good
accounting information but accounting is
the mechanism to get that information
out so financial statements convey
information from inside the corporation
controlled by managers to outside the
corporation problems so this is why we
use financial statements because a lot
of times that's the best axis we have to
getting information there's lots of
other ways to get information but that's
one particularly good one and that is
the mechanism in the market to get
information from outs inside to outside
right now problems with financial
statement analysis
hey accounting accounting rules right
that's problem enough there's so much
gray area and accounting but accounting
rules in the united states allow for
different firms to use different
accounting procedures for example in
inventory you can use the fifo lifo
weighted average or even other methods
same with depreciation so accounting
rules themselves are oftentimes gray and
you can move around a lot within those
rules so ultimately when you're looking
at financial statements you have to look
at them carefully and go to the back and
read the notes and see oh they're using
lifo for inventory and then when you
compared to other companies make sure
they're using life or if they're using
50 then you have to take that in
consideration number two international
rules can be very different
so when you're comparing financial
statement from different countries you
have to be aware of the rules from each
of those countries conglomerates like GE
right
they have all sorts of component
businesses so who you going to compare
ge2 there's no other business exactly
like them in that case you look at the
component business and then find a
parallel similar business outside and
you can compare that analysts may use
different techniques for calculating
ratios not not may its analysts do use
different techniques with race shows you
always have to know how the ratio is
being calculated you look at the
numerator and the denominator the top
the bottom and see what did they use to
get those numbers and number 5 not even
on this list kind of up here with
accounting but number five is accounting
is gray and accountants and finance
years do all sorts of tricks obfuscation
is the word in fact in the obfuscation
just means to gray out or make so
difficult to understand not only can you
do with accounting and even simple
things we have an example come up later
in this chapter with current ratio and
how we can change the numbers a little
bit to manipulate them so you have to
watch out for that
in addition of course finance ears can
do all sorts of tricks to move things
around
you've heard of off-balance sheet
financing all sorts of tricks to
actually high debt one trick that people
used in the recent financial crisis was
they had a bunch of debt and then they
bought insurance on the debt and then
that meant for some reason they could
not show it on the balance sheet many
other tricks that people do
so number five is here accounts and
finance trying to finance the years try
to trick you
now if you're doing this as a profession
your job is to figure out where they're
trying to trick you and then undo those
numbers and really analyze what you see
so those are problems but again
oftentimes you know that seems to be at
least this best starting point is that
information that comes out that
accounting information
all right ratios totally exciting and
what we're going to do here first is
talk about what is a ratio we have a
great example the current ratio now or
example down here but before what is a
ratio a ratio shows a relationship
between one number and unit and a second
number and unit using division example
fine ratio of current assets to current
liabilities now in chapter 2 we talked
about working capital right
current assets are all of the assets
that could be converted to cash within a
year
current liabilities are all the debts we
go within a year so really think about
it as current assets cash coming in
current liabilities cast going out so we
want to compare these working capital we
just took current assets and subtractive
current liability
ah but there's a ratio called the
current ratio now we'll talk a lot about
the current ratio later here we're just
going to talk about what is a ratio so
we're just going to take it
ok this would be current assets and
we're going to represent it as CA $200
current liabilities CL a hundred bucks
now before we do this Rachel let's look
at this ratio of something to something
else
every time you see this construction you
know the of part you come down here the
$DAY of $MONTH part always goes in the
numerator the number is 200 and the unit
you always got to think of the unit now
I'm going to show you here how to with
this simple one page guide to
understanding a ratio how you can
understand any ratio that's given to you
and the way you do that is you make sure
you put the right thing of into the
numerator and the two-part to current
liabilities in the denominator you
understand the number and the unit most
people do their racials and they leave
out the unit's this this book were using
doesn't talk about the unit's the
business math class accounting class I
teach they never talk about the units as
much but for us we're always going to
explicitly leave the unit's here
alright and so the same thing here to
goes in the denominator number and you
better list the units so all we have is
200 bucks of current assets and dollars
and hundred bucks of current liability
and dollars so when you do the division
since that sort of ratio is the trick is
you take out your Excel you say 200 / 1
most let's just go to our book goes to
but if you leave the units in the
numerator and the denominator you have
the meaning so really you don't have to
memorize all those meanings in the
textbook you just go oh this is two
dollars of current asset for every one
dollar of current liability now since
this is cash going out cash coming in
and this is cash going out that means we
have pretty well covered pretty well two
dollars of current assets for every one
dollar of current liability now i can't
stress this enough
the trick to ratios when we get to asset
turnover ratios and days payable and all
sorts of other ratios return on assets
return on equity keep that one here and
it will tell you what the meaning is
keep those units there i'll take you
with them tell you what the meaning is
every one dollar CL we have two dollars
of current asset now i want to go over
into an example we're going to go to our
Excel workbook so there is same thing
right here i'm going to do my
calculation here equals that what equals
that / this
it shows us to now what I'm really going
to do right here just since I'm we're
just learning this is I'm going to put
two dollars of current assets / one
dollar of current liabilities and
there's the meaning you just say you
could say they need the direction i have
two dollars of current assets for every
one dollar of current liability or you
can say it the other way for every one
dollar of current liability one dollar
of short-term debt i have i have two
dollars of current assets or two dollars
of these assets that can convert to cash
quickly right now this is silly here
look at this our next topic is common
size financial statements and actually
this whole video is just kind of a brief
introduction to our ratios the next
number of videos were going to great
detail about solvency liquidity profit
races etc but common size financial
statements is a good topic to talk about
when you're learning about ratios a
standardized financial statements
presents all items as in percentage
terms balance sheet and income statement
of the ones we're going to see most
commonly right balance sheet items are
shown as a percentage of total assets
that means you have to compare every
item on the balance sheet to total
assets total assets will be the
denominator income statement you show
every single item as a percentage of
sales or sales will always be the
denominator
now look at this is the most ridiculous
how we would do it by hand
here's our income statement net sales
cost of goods sold other expenses Abbott
earnings before income and tax interest
tax net income you take every one of
these numbers and you divide it by the
same number every time
ok when we get over to excel or absolute
cell references are going to help us
there
so here's all of our answers well that's
one right no because net sales by my net
sales anything divided by itself is 1
and then you get these percentages now
what is so beautiful about this for the
income statement
is that you can say for every one dollar
of net sales are we have it listed as
twenty-five percent let's go over here
it's easier here for every one dollar we
have of sales 25 cents is used up for
cost of goods sold 15 senses used up for
other expenses 60 senses are earnings
before interest and tax 7010 sense is
our interest 17 senses are tax and 33
senses are net income
ok so usually when you get used to it
you see these in percentages and you
automatically think that so as you see
this income statement percentage right
for every one dollar that we sell in our
store 33 cents is net income
let's go see how to do this in Excel
right so here's that same little
financial statement now remember every
one of these numbers / the same number
their equals 1 2 3 cells to my left /
benign now from chapter 00 remember that
benign we need to lock that so we hit
our f4 key and it puts the two dollar
signs and that means that's locked
this one is always going to be a
relative cell references copy now this
one's lakhs of control enter and then
i'm going to do two things here i
already have some formatting here so
when I copy this down on 2.2 my little
fill handle when I see that crosshair
click-and-drag now over here if I click
you can see it got wiped out the
formatting because when you copy a
formula copies the formula and the
formatting so I'm quickly going to point
to my smart tag and say Phil without
formatting now when i click over here
you can see I retained my formatting and
there you go that is just awesome not
only is this great for saying things
like 25 cents out of every dollars is
consumed for cost good soul but you can
also use these to make predictions right
for next year
now usually you don't base it on one
year you take an average of many years
but another great use
or common size financial statements
other uses and for common size financial
statements will look at going forward
let's go back to our pdfs why do we use
ratios to analyze financial statements
there's a bunch of reasons we have one
two three four five and then 50 will
talk about common size financial
statements the first one and the obvious
one is we can see relationships between
numbers so for example we have net sales
and that income right
oh we just did this calculation right
this was part of the common sized income
statement our profit margin
it's always going to be netting coming
dollars divided by net sales and dollar
so 200 keeping the units / 5,000 keeping
the units and what do we get for sense
of net income for every one dollar of
net sales that is another way we just
talked about it from a slightly
different . we said four cents of every
dollar we sell is goes to net income but
here we can see it from a slightly
different point of view right one dollar
of this yields for sense of that so net
income so that means what four percent
for every one dollar we sell we earn
four cents in profit again that's
looking here for every one dollar we
sell of net sales we have four cents
let's go over so and this is again
seeing the relationship between numbers
one of the two numbers every dollar that
comes into the cash register
what's the number up here every our net
income our accounting earnings
now i'm going to come over to excel
alright let's do the calculation equals
the 200 of net income divided by
$OPERAND or 5,000 . 04 what does it mean
yeah
too bad i can't type so for sense of net
income for every one dollar of net sales
all right number two
these are the why we use financial
ratios number two we can see trends over
time without distortions of different
numbers so here it is these are all and
ultimately wrote ratios for us
you can't really do a single ratio and
have it mean much you do a ratio and
then another ratio the next year and
another ratio the next hour and then you
compare them and see the change or you
do a ratio for your company and the
average for the industry or your company
in another company so here's an example
internally we see cash and 2001 is 200
total assets in 2001 r2000 but in 2008 a
bunch of years later we have cash of 600
and our assets are 5,000 now look at
this it's grown by 3 x 200 times three
is six hundred right so it looks like we
have three times as much cash but forget
it
that's not really the case if we look at
it in terms of all of our assets right
when we do the ratio 2001 cash / 2010
total assets we ended up doing that
means 200 / 2010 sense cast for every
one dollar assets or ten percent here
600 / 5,000 we got 12 sense of cash for
every one dollar of total asset or
twelve percent so what-what it looks if
we compare the naked numbers looks three
times bigger but forget it in terms of
the cash in relation to add assets total
assets ten percent compared to 12 so
although the cash is three times bigger
in 2008 we get a percentage change of
not very much so
although and you can see
I did spell it right i hope i spelled
right you know problem with these
handwritten notes is I don't see this
quickly headline so although the cash
amount of 2008's three times bigger than
2,000 one has a percentage of total
assets it's only gone from ten percent
to twelve percent there's not been much
change now let's go over to excel again
we're going to take two different ratios
2001 cash compared to total assets and
again here 600 / 5,000 and so the point
here is that we can understand
relationships between numbers better
when we use these ratios instead of
comparing just the same numbers let's
look at another great example along the
same exact lines we can compare small
and big companies without the
distortions of different numbers that's
basically the same thing here here's
microsoft cash this is 2006 data 23
billion Microsoft to last at 63 google
cache 11 billion google total assets 19
billion we can compare these two right
here looks like Microsoft has way more
cash now they do have more cast right
but what about as a percent of total
assets here we see Microsoft is at 3723
device by 23 / 63 is about thirty seven
and 11 / 19 so Google in 2006 had fifty
eight percent of their assets in cash
they were probably on the hunt they were
buying lots of new assets so they had a
lot of cash that's that's a huge
proportion
now we mentioned this earlier but cash
what can you do with cash if you put it
in the bank what can you earn em couple
percent or maybe have a lot you know
billions of dollars you can put it and
earn you know whatever the market rate
is more than just your savings but that
is so you're in five percent on 11
billion really what you want to do is
you want to go out and buy your
long-term assets those are the assets
that determine your business and
presumably earn a much higher return
than put it in the bank so when you see
this much cash there there could be a
couple reasons
back then it it presumably meant that
they were on the hunt to buy more assets
now during the financial crisis and
after 2008 2009 2010 you see people with
a lot of cash
the reason why is there still scared
there a out conserving cast because
during the financial crisis everybody
ran out of cast so they want to hold
enough cash in case they get into
trouble again and maybe during this
period right now they're not sure what
to go and buy so the point here is we
can compare different companies with
percentages let's go over here do that
same thing i'm going to go by the way up
click in this cell the number 23 is up
there if you you go to format cells
right click format cells are control one
you see there's a custom format there
anytime you put a 0 or if you want
decimals you can put 0.00 if you
actually put some text number this is
number down here in custody if you
actually put text the text will show up
in the cell but not there we should not
be too we shouldn't be too surprised by
that because here's a number format we
use all the time right that dollar sign
appears there but the numbers not there
alright so Microsoft equals 23 / 63 and
a google is 11 / 19
number five sound like David Letterman
right the top five reasons to use ratios
ratio analysis and common size financial
statements are a convenient way to look
at and compare financial statements for
different companies across time many
people use ratios and financial analysis
and common size financial statements
such as all of these people now just it
oops i skipped over number for this
example here is just a great example of
why ratios and common size financial
statements are so good because you can
compare different sized companies i
skipped over four but number five is
just everyone uses this kind of analysis
now remember font as in chapter 2 cash
flow analysis and this cat chapter 3
these are all were analyzing financial
statements so all of these numbers that
we get are just like the starting point
if it seems normal maybe we don't look
much further but anytime we see a
difference or an exception called
management by exception we see something
different like we better go investigate
but all these people use ratios
creditors ask the question should we
loan right there using current race or
something stockholders are managers
doing a good job maybe profitability
ratios auditors they use all sorts of
ratios where do we need to look closely
supplier should we extend credit
employees should we work for this
company stock brokers should we invest
investment banks
how am I going to cheat again oh no
investment banks should we underwrite
underwrite means should we help them
issue stock or issue debt that's one of
the big things investment banker banks
do research analyst is this a good
company now research analysts are the
ones that dig really deep but even
research analysts they can start with
the financial statement and some
financial statement analysis and then
they go and dig deep financial research
analyst
I mean they go and interview anyone
there are detectives just like auditor
should be detectives contract writers
every debt contract usually oh wait a
second not heavy debt contract during
the financial crisis they didn't really
spend much time writing the contracts
they just said ok I'll loan to you
ok i'll loan you remember they didn't
really check to see whether people had
enough funds to pay off but in normal
times debt contracts for example have
lots of covenants that means rules about
what you have to do so for example they
will have the current ratio and the debt
contract will say if the current ratio
gets below this then you're in default
and you have to pass it's not usually
just the current ratio its other things
but that is one of the things that they
can use now I skipped over number four
we can compare financial statements that
are in different currencies but be
careful about the counting method so if
we have company one equity is this
amount total assets is this amount of
equity and total assets here
equity is 500 total assets here a
thousand pounds right these are in
dollars season pounds and now I rigged
this one here so they come out the same
but look at this when we do the division
we get for the dollar 12 dollars of
total assets for every one dollar of
equity this is called a leverage ratio
because here's all of our assets
here's our equity this one has two
lasses debt plus equity so when you /
equities is for every one dollar of
equity how much assets do we have to
so there's the how you get the meaning
of it but when you do a straight
percentage of like two hundred percent
hear the same thing in pounds you get
the same ratio to $PERCENT & % all this
means is although the currencies are
different we can see both companies have
two units of assets
for every one unit of equity in other
words because of the use of debt total
assets equals 1 debt plus one equity
right if we broke it down simply write
it really could be in like two billion
and then 1 billion debt 1,000,000,000
equity both corporations have leveraged
up by two hundred percent so you can see
with our ratios here that we can compare
different currencies are let's see okay
let's go over to excel to one last 1i
better when we do these ratios total
assets divided by total equity and this
one but total assets divided by total
equity now I put some fancy formatting
there but that's just the the meaning of
it and then this doesn't have any
formatting somebody that same
calculation again should get to hear now
equals and the same thing for the pound
one now when we make formulas like this
it actually does suck the formatting
from the cell references right so if i
want to see this without just as a
number to which some ratio people like
to see just the number no formatting i'm
going to control one and then click
general there's the number tab format
cells number type general if they want
to c % control one number this is format
cells number and then percentage alright
that was our introduction to ratios when
we come back the next few videos were
going to go into great detail about
different types of ratios like liquidity
solvency profit etc alright see you next
video