Ratio Analysis, Financial Ratio Analysis in Excel

For details, visit: http://www.financewalk.com

Ratio Analysis, Financial Ratio Analysis in Excel

Financial Ratio Analysis

Meaning-

" The process of calculating the relationships between various pairs of financial statement values for the purpose of assessing a company's financial condition or performance is called ratio analysis."

Users of Financial Analysis

Financial Analysis can be undertaken by management of the firm, or by parties outside the firm like owners, creditors, investors and others. The nature of analysis will differ depending on the purpose of the analyst.

• Trade creditors- are interested in firm's ability to meet their claims over a very short period of time. Their analysis will, therefore, confine to the evaluation of the firm's liquidity position.
• Suppliers of long term debt- on the other hand, are concerned with the firm's long-term solvency and survival. They analyse the firm's profitability over time, its ability to generate cash to be able to pay interest and repay principal and the relationship between various sources of funds i.e. capital structure relationships. Long-term creditors do analyse the historical financial statements, but they place more emphasis on the firm's projected, or pro forma, financial statements to make analysis about its future solvency and profitability.
• Investors -- who have invested their money in the firm's shares, are most concerned about the firm's earnings. They restore more confidence in those firms that show steady growth in earnings. As such, they concentrate on the analysis of the firm's present and future profitability. They are also interested in the firm's financial structure to the extent it influences the firm's earnings ability and risk.
• Management - of the firm would be interested in every aspect of the financial analysis. It is their overall responsibility to see that the resources of the firm are used most effectively and efficiently, and that the firm's financial condition is sound.

---

Closed Caption:

hello and welcome to finance work in
this video we'll cover case on ratio
analysis
hello and welcome to finance work in
this video we'll cover case on ratio
analysis we have seen a different
calculations of ratio analysis and how
ratio analysis is important in equity
research as a financial and list there
are different tools available to check
whether the company is really strong one
or investable opportunity or not in that
ratio analysis is one of the important
tools in financial analysis and as an
equity research analyst or as a
financial analyst
you should know how to calculate
different ratios
ok all right we'll start with this case
we have taken this case in manufacturing
industry
ok so in this sheet will see the will
read out the case first
ok the ex manufacturing company is a
leading producer and exporter of
engineering items such as steel pipes in
God's be late extra
it has also recently added a chemical
plant and a paper plant as a part of its
diversification strategy
the company started with the share
capital offers 25 lakhs in the early
sixties which has now increased to peace
- 25 lakh the number of cheryl shares
outstanding is 22.4 p lack the average
market price or MP of the company's
share during 2010-2012 has been replaced
26 . 38 in 2010 purpose 34 . 50 in 2011
and replace 29 . 25 in 2012
the financial data for the company are
given in next week so next steps will
click on the P&L sheet
ok so this is profit and loss account
you can see net says cost of goods sold
then gross profit and you can see this
is a bitch to profit and loss account we
can see it for 2010 there now 2011-2012
also and here it's even if you look at
this column a in two thousand will they
open it says then here
gross profit is given then now different
figures like profit before tax
this is profit after tax effective tax
which is thirty-two percent and even
distributed and retained earnings
okay with this let's move to the next
sheet that is a cost of goods sold
so in cost of goods sold is a statement
basically about what goes in a while
manufacturing the product and we can see
here
raw material the indirect labor
depreciation other manufacturing
expenses then now opening stock in now
probate process ok
closing stock in process likewise so
essentially this is cost of goods sold
for 2012
how will move to next week that is
balance sheet
one of the important financial
statements ok here it is given Network
then the borings capital employed
current assets current assets here
ok this is current as a total current
asset figure liabilities
this is current liabilities net current
assets and then next assess which is
edition of fixed assets and current
assets
ok so we have this data which is a total
number of lashes are sending then the
company but the market price also market
price in the market
ok and share capital T then next we have
P&L profit and loss account
we have cost of goods sold a statement
and we have balance sheet
so with this data let's check different
ratios for this company and see how
medical company is an investable
opportunity or not
ok so here in this sheet ratio analysis
we can see the different issues like a
liquidity ratios financial
liberalization issues profitability
issues and dividend policy issues or
certain other issues we are going to
cover in the second column we can see
formulas for different ratios will
calculate treasures here and we'll also
interpret the issues
ok so here first we'll start with
current issue
ok so we'll see first what are the
liquidity issues
ok liquidity they shows measure the
ability of the form to meet its current
obligations a farm should ensure that it
does not suffer from lack of liquidity
and also that it does not have excess
liquidity the failure of the company to
meet its obligations due to a lack of
sufficient incredible results in a poor
credit worthiness or loss of critters
confidence and even legal tangles
resulting in the closure of the company
a very high degree of liquidity is also
bad that is adolescents are nothing
the forms funds will be unnecessarily
tied up in current assets
therefore it is necessary to strike a
proper balance between high liquidity
and lack of liquidity
ok so we'll start with current ratio as
we have seen in this formula current
ratio is calculated by dividing current
assets by current liabilities
ok so current a sex
ok we'll go here equal to sign then we
go to balance sheet because current
assets automation in balance sheet
ok so current assets total current
assets it deviates current assets then
go here Division sign and current
liabilities current liabilities where do
we find current liabilities
ok so current liabilities here
you can go down and see current
liabilities
so current liabilities is how much it is
715
ok so if you look at the total current
liabilities
it is 715 . 88 prison but we are
skeptical creating it only for 2012
ok so for enter and then you have here
too . 61 of current ratio as 261 if you
want you can now make it only two digits
like this
ok ok so it is now to stew . stephen so
current ratio for this company is too .
61
ok so how to interpret this current
ratio
ok as a conventional rules a current
ratio of 2 is to 1 or more is considered
satisfactory
ok so in this so we can say it is more
than satisfactory so interpretation we
can put is good
ok we can see more
here the this company has a current
ratio of two points 2 1 is to 1 so it is
it is always mention in case of two
parts to 1 is to 1 so i would write it
here
this way so too . 60 1 is to 1 which is
good
so the ratio is good ok
therefore it may be interpreted to be
sufficiently liquid
ok then now see this rule is based on
the logic that in a worse situation even
if the value of current assets becomes
half the form will be able to meet its
obligation the current ratio represents
a margin of safety for creditors
ok so the higher the current ratio the
greater the margin of safety or the
larger the safety for creators
the higher the current ratio the greater
the margin of safety
ok the larger the amount of current
assets in relation to current
liabilities
the more the firm's ability to make its
current obligations
ok so here one thing is clear this
company is very good in current ratio
ok so of that with current ratio looks
more to next issue that is quickly issue
ok what is quickly issue quick ratio
establishes a relationship between quick
or liquid assets and current liabilities
see inventors are considered to be less
liquid because it winter is normally
require some time for realizing into
cash
so their value also has a tendency to
fly to it
the quick ratio is found out by dividing
creek assets by current liabilities so
now what is it what is the quick ratio
current assets - inventory divided by
current liabilities
ok so we'll cut look here let's go here
for equal sign and then go to balance
sheet
why bullshit because we'll find current
sx
the inventor is there so here it is go
put my equal to sign and then our
current assets from it is current assets
here
- inventor is so inventory's this is
inventory
ok in winters now you can put a
parenthesis here
ok
and this divided by current liabilities
ok so go current liabilities balance
sheet
current liabilities this is current
liability and enter
ok so here it is
so 1.41 ok so 1.0 1 is to 1 is quickly
show which i think is a good one
ok so that's if this company's inventors
do not sell and it has to pay all its
current liabilities
it can it is easy for this company to
meet its obligations ok because it's
quick ourselves our 1.01 times of
current liabilities
so how it is it can be written as 1
point 0 1 is to 1 which i think is again
good okay with this
let's move to our next ratio that is
cache issue
ok so what is cash an issue since cash
is the most liquid asset of financial
analyst may examine catch tissue and
it's equivalent to current liabilities
trade investment or marketable
securities are equivalent of cash
therefore they may be included in the
computation of cash the issue
ok so here we can see cash it shows
formula is cash plus bank balance
this place marketable securities divided
by current liabilities
ok so for this company - let's let's
calculate cash plus bank balances go to
balance sheet
ok so go to current assets here it is
got cash and bank balances for 2012 it
is contagious . 08
ok and divided by current liabilities so
this divided by current liabilities
current liabilities is this
so according to this formula
this is point zero three or if you want
to put in percentage terms
go here see this is four percent ok so
for person
ok so for example the company in our
example carries a small amount of cash
for this purpose and so there's nothing
to be worried about the lack of cash if
the company has a result boring power
so in India forms help create limit
sanction from banks and can easily draw
cash so i would say i would say this is
again a good major
so we can put this interpretation as
good
ok so after calculating a liquid a shows
later we can move to financial leverage
ratio so what our financial leverage
so we need to see we need to check how
much company is a company has taken
loans and its capital structure also so
we'll go to we will see first issue in
this that is dedicated issue
the formula is a day / equity
ok so total date / network is the
formula for the dedicated issue or will
calculate here
let's see total date
we need to go to balance sheet
see ok so total borings is well
29 . 06 / total network which is 67 2.81
then press ENTER 1.8 it - okay so let's
see in this format so one . 83 so what
does it indicate 1.83 ok so again 1.8
the of indicates that it is clear that
from the total day to show that this
company complete lenders have
contributed more funds than owners are
so Linda's contribution is one . 18
times of honest contribution
so this is called that accreditation i
would suggest for manufacturing
companies
yes sometimes the Mounties invested or
invested in my basically fixed assets or
grass block
so in that case the company is leverage
- so this
I would porto in ok not great or a good
so one . 83 or we can put it as one
point eighty three to 11 . it
three two one spoken
ok interpretation is ok so after that
let's move to interest coverage ratio so
interests what is interest coverage
ratio SI interest courage in the in
interest coverage ratio we need to check
how many times the particular interest
or the company is running in relation to
interest so abdi tea or we can call it
to operating margin is how many times of
interest
ok
so in this interest coverage ratio that
is abdi t / interest
so the interest coverage ratio shows the
number of times the interest charges are
covered by funds that are ordinarily
available for their payment so since
taxes are compound completed after
impressed interest coverage is
calculated in relation to before tax
earnings
ok so the position is a non-cash item
there for fun sequel to the position are
also available to pay interest charges
so we can calculate the interest
our coverage ratio as earnings before
depreciation interest and taxes or
implement a media t / interest
so here immediately for immunity
let's go to P&L to put equal sign and
then go to P&L
ok ebit that is earnings before interest
and tax 242 . 61 / interest so here
interest figure is 142 . forces and now
put enter
so this is so too . the 39 times or 2.4
times
this is interest so that this ratio the
interest coverage issue all the times
interest on is used to taste difference
debt servicing capacity the interest
coverage ratio is computed by dividing
and things before interest and tax by
interest charges
ok so here uh i would say it is good day
the
a company is able to generate the amount
or profit or onions in short that it can
serve with interest easily
ok so i would say this is a good ratio
in this particular companies case
ok so with this will move to the next
area that is profitable . issues see
profitability ratios are most important
issues for an analyst Dennis
shareholders
so you need to see how this ratio is
calculated
ok it is gross profit
my first we'll look at gross profit
margin issue so gross profit / next says
is a desiccator gross profit margin and
if you want it in person
percentage terms then you need to
multiply it by a hundred
ok so this is gross profit margin also
we can calculate the net profit margin
which is same way that is profit after
tax / nexus and that is x hundred again
ok so first we'll start with gross
profit margin the first profitability
show in relation to says is the gross
profit margin or simply gross margin
issue it is calculated by dividing the
gross profit by cells
ok so here gross profit margin is
nothing but says - cause trouble sword /
said / cells so let's see for this
let's calculate for this company gross
profit margin they show
so for gross profit margin go to a P&L
and check gross profit my grass property
is so gross profit is 66 3.57 / says
okay so nexus is 37 17.3 ok and then
press enter this unit
this in percentage terms so check HERE
percentage so eighty percent is cost
profit margin
ok so this gross profit margin reflects
the efficiency with which management
produces each unit of product so this
issue indicates the average speed
between the cost of wood sword andy says
the venue
i would say eighteen percent is good but
you need to see this
in comparison way to other companies or
other place in the same industry i would
say good here
ok then go to net profit margin they
show net profit modulation profit after
tax / nexus in 200 so profit after tax
12.1 to the fore . it 6 / nexus nexus is
this a 117 . two three
ok yes I was correct and you wanted in
percentage terms so go here
check percentage so net profit margin is
four percent
ok so if the non-operating income figure
is substantially it may be excluded from
pad to see profitability arising
directly from cells for this company
operating profit after taxes too serious
issue is four percent so what does net
profit margin shoulders it establishes a
relationship between net profit tenses
and indicates management's inefficiency
in manufacturing administering and
selling the products this ratio is the
over on major all for the difference
ability to turn
HUP cells into net profit so if the net
profit net profit margin is inadequate
the form will fail to achieve
satisfactory written on shareholders
funds
ok so i would say again
poke him ok of pressure this this is ok
shouldn't profit margin in this company
is the case will dig deeper because we
need to check this issue in comparison
with at the players in the same industry
ok so with this looks more to next issue
that is written on capital employed
ok so return on capital employed is one
of the important ratios so return on
capital employed the formula is a bi T
into 1 minus T divided by net assets in
200 ok so here we can see in what is
immediately we have calculated in dat
that is go to P&L in dat is
342 . 61 x 1 -
so this is x 1 minus T tax great that is
pointed to
ok divided by net assets so net assets
were to balance sheet check net assets
so these are net assets
ok so return on capital employed
you can check in percentage terms is
twelve percent
ok return on capital employed seems good
to me so I'll put good reading here
ok written on with this
let's move to return on equity so what's
the difference between the turn on
capital employed and return on equity
sittin on capital employed is a major
which show majors the return for date
holders and equation for this boat
whereas return on equity measures the
earnings or it done for protection
orders only
so if you look at the formula
it says profit after tests / network and
it was net assets
here it is network in 200 so let's
calculate to turn on a quit it
ok so return on equity is profit after
taxes so profit after taxes but for
profit of texts go to P&L it is 124 . h
6 / network for network you need to go
to balance sheet and here is a network
figure that is 67 2.81 press ENTER and
this is the figure we wanted in
presentation so it is 20-person return
on equity 20 person i would say of i
would say very good doe return on equity
is twenty percent so anything above
fifteen percent is considered to be
really good because Indian companies
average rate is fifteen percent so if
you check if you get any company which
is consistently giving out on equity
higher than 15-percent consider it is
really good to know
a company in this case it is giving
twenty percent so i would say
good good measure ok after this we'll
look at earnings per share you might
have heard this GPS jargon
you know in financial annual reports or
of financial dailies like economic times
business standard
so what is a PSA pieces calculated as
profit of protest / number of common
shares outstanding
ok so here we will check the prophet of
protects which is given in TN
ok so profit after tax he is 134 . at
six divided by number of common shares
outstanding so number of common shares
outstanding is for that will go to case
here it is being given total number of
shares outstanding is 22 . 50 lakh
ok so here we go - okay so 22.2 22 . 50
lakhs
ok press ENTER and since we want it in
this format
ok so 5.99 if you want to remove these
decimals then six solo p 6 is earnings
per share
so our onions per share is
6 that is a partial the company is
giving you six rupees
I would not say it is good or bad unless
so I check the track record and other of
comparative become companies comparable
companies in this industry
so you need to see the trained in this
so you need to see without the trend
that it's a boo trained or it's in
growth phase and onion / sure it's going
by at least five percent ten percent or
fifteen percent so in that case we need
to change so i would say need more
analysis
ok then check price
our price earnings ratio ok for p by ear
issue so p by e to show me the issue is
also we have seen in a presentation how
to calculate it and what's the
importance of the price and initial
surprise and innovation is good . as
market price per share / earnings per
shift
ok so this is this PE gives you a very
good major or for how much show of God
how many times the particular earnings
per share is measured in terms of market
a price or what's multiple the market is
giving to its eps that is earnings per
share
so market value of the price pressure
so for that go to this for shit that is
in 2012 it is on average price average
market price has been 29 .
25 so go to issue an assistant it is 20
9.25 then divided by earnings per share
so this we have can just covered on this
pressure that is 6 so let's see price an
insatiable price earnings ratio is a
five times so it's good i would say it's
good because the price and is a show is
widely used by the security at least two
value deforms performance as expected by
investors
this indicates this issue indicates
investors judgment or expectations about
the forms performance management is also
interested in this market appraisal of
difference performance and would like to
find causes the . issue declines so i
would say prices prices on this issue is
good
of course it's good because now if you
see it normally companies having less
than 12 or 15
he is considered to be good but again
need it needs more analysis because we
need to check other financial we need to
check out the financial variables for
this for me and the company's profitable
the net profit margin is good
the one more thing we need to look at is
that cash flow so we'll check that in
some time but again price on this issue
looks good to me okay with this
we have four completed profitability
ratios
then we'll move to dividend policy
ratios
ok so dividend policy issues one of the
important variable for investors
ok so first we'll check dividend yield
they show their dividend yield is
calculated formulas difference per share
and / market price pressure so
dividend yield so evident for sure
so where do we find dividend per share
ok they wouldn't partial let's go to P&L
and check whether it's been there
yes David and distributor is 45
so if you can click that
or let's check with a division is given
an affair
apart from P&L ok
so David and distributed is 45
ok it is 45 lakh so let's see let's say
dividend per share is a two piece ok for
our calculation will take a dividend
distributed as two piece
ok short here to that is dividends per
share / market price so market price we
know it is 29 pointer to five
ok so we'll check dividend yield
dividend yield in percentage terms it is
seven percent i would say very good
company in case of dividend yield
because if you
it says that if you invest 29 . 25
rupees which is market price of the
shared you are going to get two rupees
so which is seven percent so this is
also a consistent running for a pretty
sure let's do seven percent i would say
it's really good to see you
was you invest in shares basically for
two reasons one is getting evidence and
second for capital appreciation of the
companies which are giving dividend
are they willing to share holders
consistently are awarded highly okays or
are - hi Lee
so seven percent according to me is
really good
let's check it lists go to next to show
that is dividend payout tissue dividend
payout ratio is calculated as difference
per share / earnings per share
x hundred ok so difference per share
is we have calculated difference
pressure is too / onions per share
what is uninspired share it is this
figure 6
ok so press ENTER and we wanted in
percentage terms so go here 33 person
different pair failed to show is
starting three percent
ok what does this mean earnings are
earning is not distributed to
shareholders are written in the business
so payout ratio is thirty-three percent
so i would say a hundred - 33 so that
ratios is called retention ratio
ok so it's it's good because the more
than well you know one third of the
total earnings is distributed as
dividends and to third is retained for
further investment
so I would say good ok so as as long as
the company are keeps this track record
then the in that case company is really
managing its finances well and keeping
the investors happy also
ok so with this will will more to
turnover ratio so what our turnover
ratios
there are no issues are the ratios which
of you know which is always quoted in
multiples
ok so inventory turnover ratio is
calculated as cost of goods sold divided
by average inventory
ok so a cost stop invent a turnover
ratio indicates the efficiency of the
form in producing and selling its
products
ok it is calculated by dividing the cost
of goods sold by the average inventory
okay in a manufacturing company inventor
of finished goods is used to calculate
inventory don't know what okay so like
okay let's let's start a calculating
invent a turnover issue
okay so we need average inventor so
let's calculate average inventory first
ok so average 23 go to balance sheet
calculate here
average inventory so a legend when
you're finished of work so this is 244 .
28 plus 4 60 1.81 this / -
now this will give us average inventory
53.0 45 is average inventory
ok now with this let's go to a ratio
analysis sheet and calculate cost of
course also cost of goods sold is Israel
53.6 26 this /
Elementary ok so it . 64
okay we'll put into decimals so eight
points 25 times in 20
another issue which is i would say good
so this is turning its inventor of
finished goods into sales
eight point six five times in a year
in other words it was a virgin when 3 of
4 1 point 4 months
that is if you do when they were to 12
months x 8.6 it will give you one point
four months
the reciprocal of invent a turnover
gives average in winter holdings in
percentage terms
ok so i will say good see this ratio is
always in times as i said it is multiple
citizen times
so eight point is 25 times
so the ratio is good now let's see the
total assets turnover this is again
important because as the company invest
highly in total assets we need to see
the impact of this investment in sales
or revenue also so higher the better
hire this issue is the better
ok so we'll calculate the now 2 plus its
turnover which is net assets divided by
average total assets
ok so first we calculate average total
assets to balance sheet average total
assets for extra sets calculation should
be
next 6-plus current assets so 26 17.75
is the total figure for a total of six
ok so now we'll go to the issue analysis
sheet and nexus 4nexus we need to go to
the end and nexus is a 3 c1 17.3 dad /
total assets total assets we have
completed in balance sheet that is 26
17.75 presenter that it was 21 . 42
times so it is one . 42 times so it says
the total access to an order of 1.42
implies that this company generates a
sale of rupees one . 40 to 41 of
investment in fixed n contestants
together
ok so i would say i would say it is ok
not great
so since it is only 1.4 times every time
the company in west one rupee in total
Isis it will only generate one point for
the tool nexus which is again not good
ok so through this issue analysis we can
see the financial position of your
company and this financial health care
especially for the company is ok because
the current ratio is good then that
quick ratio again i would say good
- - oh four percent then all you know
about four percent is held in cash
which is good the equity issue is one .
83
it should be ideally less than 1 but
since it is manufacturing company the
company is highly leveraged so companies
should look at this ratio once again
interest coverage ratio too . 3139 is
good again
gross profit margin eighteen percent
would net profit margin is four percent
I think the we need to IBM's I'm i make
sense okay because we need to dig deeper
and check why it is only four percent if
it is industry standard then we have to
accept it
return on capital employed is twelve
percent good
what as surely should be interested in
return on equity which is twenty percent
so as I mentioned earlier more than
indian companies averages fifteen
percent more than ar-15 person is better
and in this case it is twenty percent so
i would say it's really good earnings
per share is to be six it
we cannot comment on standalone figures
so we need to dig deeper and check the
train so we need more analysis for this
price earnings ratio five is ph 5 is
again good
so anything any company as p is less
than well i would say good
so is since i've mentioned herein good
and then it more honest and this is we
need to dig deeper next a ratio of
issues are different policy issues they
wouldn't yield is the one person which
is in good dividend payout ratio to
three percent that means company is
distributing thirty-three percent of it
so onions and remaining sixty-seven
percent is returning for companies of
future plans okay
don't nobody shows invent a turnover a
show
so eight . 65 x is good
in this clip for this company and the
total assets turnover one . 42 times i
would say it is just ok
our but this this issue is just ok for
this company because i would expect
anything between three and six times for
this company bus driver says i have
mentioned here it's ok so with this I
would say we need to check with the
company is really good or not
in my case the company is good but in
some cases company can do much better
but again this as i said this ratio and
this is these issues we have to compare
it with different players in the same
industry