In this lecture, we will learn how to calculate different financial ratios when you have balance sheet
and PNL statement of a company with you.
You can find balance sheets and PNL for any company from its annual report.
Or you can visit any of financial websites such as Yahoo!
Finance or Money Control, etc., to find balance sheets and BNN the statements of any publicly traded
companies.
We have already imported Off-balance-sheet and PNL, the statement of a company, so in the first step
you can find balance sheet and in the second tab you can find BNL statement of that company.
Using these two taps will find different financial ratios that we have discussed in the three lecture.
First, we will discuss the profitability issues, the first issue we are going to find is gross margin,
the formula for gross margin as gross profit divided by net sales.
You can find gross profit and sales in the Pinelli statement.
So if we go to PNL statement.
Formalized gross profit by total revenue, so the gross profit is this gross margin and the total revenues,
total sales revenue that we have in the first sell.
If you see, we have calculated the gross margin from this BNL statement.
You can also notice in the formula bar that if you select any cell from any different tab, you will
find the name of that tab in the formula bar.
So just rate equal to while using formula and then you can navigate to any tab and you can select any
cell from any tab.
Excel will automatically modify the address accordingly.
So the gross margin for this company is around point thirty eight or thirty nine percent.
Next will find the net profit margin.
Net profit margin is net profit divided by net sales?
Again, we can find this information in the PNL statement.
So net profit or net income, is this divided by the revenue, which is this?
So the net profit margin for this company is zero point zero for nine.
If you change it to percentage, it will be around four point nine percent or five percent.
Next, we have a return on asset, which is net profit plus interest multiplied by one minus tax rate
divided by average total assets, you can find net profit.
And interest and tax rate from your personal statement, and you can find total assets from your balance
sheet.
So let's first add net profit plus interest.
Is the net profit plus interest?
Now we have to multiply this term with one minus tax rate and tax rate varies from company to company,
but in our case we are taking tax rate as 25 percent or zero point Dufay.
So we'll multiply it with one minus zero point to five.
This is the numerator and the denominator we have to put total average assets.
You can find total assets from your balance sheet.
So this is the return on assets at zero point zero zero eight, or if you convert it to percentage,
it will be at one point eight percent or one percent.
Next, we have an return on equity here, we have to divide net profit by total equity.
You can find net profit from your personal statement.
And you can find equally from balance sheet, so the net profit divided by.
Total equity.
So this is the total equity total or not, secretly, you can divide this.
So are we in our cases, zero point zero nine or nine percent.
Now, let's move on to liquidity ratios.
The first issue is current ratio, which is current assets divided by current liabilities.
You can find both of these items in the balance sheet.
So this is the total current asset and.
On the right, we have the total current liabilities for divide, these two will get to current ratio
to the current ratio in our case is zero point six five or six to five percent.
Non-current ratio is one of the ratios which lenders evaluate before lending money to any company,
and they usually put a filter on this ratio for analyzing different companies.
So let's filter.
If the current ratio is more than 80 percent, we will return path.
And if current ratio is less than 80 percent, we will return feel.
So we can use if a statement.
To get an output, according to this 80 percent threshold limit.
If we hit enter, we'll get feed, so I'm showing you this only because you can use these ratios value
to create a summary checklist on different ratios values according to your business requirement.
Now, the next is cash ratio, which is cash plus marketable securities divided by current liabilities,
you can find these values in your balance sheet.
So assets we have cash and marketable security, we will add these two values.
And divided by current liabilities.
So the cash ratio in our case is zero point three five or 35 percent.
Now, the next is quick ratio.
Again, this is a very important ratio, which is the sum of cash plus marketable securities, plus
receivables divided by the current liabilities.
So, again, you can find these values in your balance sheet.
So in the asset, you will find cash.
Marketable securities.
And then the receivables.
And we have to divide this whole term by current liabilities.
So the quicker issue.
For this company is point five zero eight or 51 percent.
Now let's move on to debt management ratios.
The frustration issue we have is debt to capital ratios, which is BEP divided by debt plus equity.
Again, you can find this terms.
In your balance sheet.
So the total up here is this total current liabilities plus total long term liabilities.
Divided by debt plus equity, which is this number total liabilities and on an equity.
So the debt to capital ratio in the case is zero point nine three or ninety three percent.
The next issue is debt to equity ratio, which is divided by equity.
Again, you can find this DOMS in your balance sheet total up is this.
Current liabilities plus long term liabilities.
This is our total debt divided by equity, which is total total owners equity.
So the depth to equity ratio in this company is fourteen point five it.
Now, the next is.
Interest coverage ratio, which is about earnings before interest and tax.
Delighted by the interest amount.
Now you can find these items in PNL Stutman.
So here we have earnings before tax.
Now we want earnings before tax and interest, so we will earn interest to find a bit.
And then we have to divide it by the total interest amount, which is this.
So the interest coverage ratio for this company is four point five five.
Next, we have asset management ratios.
The first issue is a sex turnover, which is revenue divided by average, and that aside here, the
average term means the average net asset during the whole year.
So, for example, if you have some value in the start of January and you have some value of net asset
in December, and you have to average it out to find the average net asset.
But for now, I'm only using a single value that we have in the balance sheet.
So first.
For revenue, we will go to PNL statement.
This is our revenue and for a site we will go to.
Balance sheet, which is this.
So the asset turnover is zero point one win for this company.
The next issue we have is inventory turnover, which is cost of goods sold divided by average inventory
balance.
Again, here also average means the average inventory level for the whole year.
So you can either take an average of your start of the year inventory balance and end of the year inventory
balance or any other metric to calculate average.
But in this case, we will take the value which we have in the balance sheet.
So the cost of goods sold for that, we have to go to PNL a statement and you can find it here.
Divided by average inventory level, so we will select this inventory value from our balance sheet.
You can see that inventory turnover in our case is one point six nine.
The next issue is inventory days, which is number of days in a period divided by.
Inventory turnover.
So supposing they are generally we have 365 days, so we will write 365 divided by this inventory turnover.
So the inventory days is equal to two one six for this company.
So that's all we calculate financial ratios when we have balance sheet and biennales statements.
Generally, you'll need to calculate these ratios for multiple companies, and it is better to create
a format like this.
So if you get balance sheet for some new company, you can just update this balance sheet and update
the PNL statement and all of your ratios will get automatically updated and the steps along with finding
the ratios.
You can also create text like this if you have any threshold or standards for particular financial ratios.
This makes Decision-Making much more easy and you will be able to create some reports in just a few
minutes.