Financial ratios are categorize according to financial aspect of the business which the ratio measures. This financial ratio calculator in excel will help you calculate those important metrics. So, It should also help you to learn which accounts in balance sheet as well as profit and loss statement to generate those ratios. Moreover, You can customize this spreadsheet easily by typing row numbers next to respective account names.
Financial Ratio Categories
Thus, These are common categories you may find in many references which also you can find in this financial ratio calculator spreadsheet.
Liquidity Ratios
Measure cash availability to pay company’s debt. So, All numbers are taken from balance sheet statement.
Thus, This ratio is taken from balance sheet statement where total of company’s current assets is compared with total of company’s current liabilities
Cash Ratio = Current Assets (Cash & Obligation)/Current Liabilities
So, This ratio is similar with Current Ratio except it takes into account cash and obligation only from current assets
Quick ratio adds accounts receivable as part of current assets along with cash and obligation
Hence, Net Working Capital Ratio = [Current Assets – Current Liabilities (Net Working Capital)]/Total Assets
If you want to measure your net amount of all elements of working capital, you can use this ratio calculator.
Solvency Ratios
Measure company’s ability to repay long-term debt. Basically, it is the opposite of liquidity ratio where it sees financial performance from liabilities/debt side.
Debt Ratio = Total Liabilities/Total Assets
Reading this ratio should give you a quick measurement whether company’s assets can cover all of their liabilities.
Debt to Equity Ratio = Total Debts (Long Term Liabilities)/Total Equity
Debt to Equity ratio compares total debts with company’s equity.
Equity Ratio = Total Equity/Total Assets
The equity ratio is a financial ratio indicating the relative proportion of equity used to finance a company’s assets
Interest Coverage Ratio = Earning Before Interest and Taxes/Interest Payments
The interest coverage ratio is used to determine how easily a company can pay interest on its outstanding debt.
While the previous three ratios are taken from balance sheet statement, this Interest Coverage Ratio is taken from Profit and Loss Statement.
Activity Ratios
Measure capability of converting company’s non-cash assets to cash assets. This ratio relates to company’s operational activities. It takes values from both balance sheet and profit and loss statements.
Working Capital Turnover = Sales Revenue/Current Assets – Current Liabilities
This ratio is an indicator to measure company’s effectiveness in using their working capital
Inventory Turnover = CoGS/Inventory
Basically, this is an efficiency ratio to show how effective particular company’s inventory management.
Assets Turnover = Sales Revenue/Total Assets
Assets Turnover ratio is a key performance indicator to measure the value of company’s revenues relative to their assets’ value.
Receivable Turnover = Sales Revenue/Inventory
Average Collection Period = 360 or 365 days/Receivable Turnover
Days from this ratio are useful to manage company’s cash flow situation.
Profitability Ratios
Measure company’s use of its assets and control of its expenses to generate an acceptable rate of return. These are common metrics to measure it.
Net Profit Margin = Net Profit/Sales Revenue
Gross Profit Margin = Gross Profit/Sales Revenue
Operating Margin = Gross Profit after Expenses/Sales Revenue
Return on Assets (ROA) = Net Profit/Total Assets
Return on Equity (ROE) = Net Profit/Total Equity
Basic Earning Power Ratio = Gross Profit after Expenses/Total Assets
Hence, Financial ratios above might or might not suit with your company’s condition. So, There are still other financial ratios options you can choose if you fill some of ratios above are not suitable. So, Remember to define your own ratio references since it might be different between companies.
Furthermore, financial ratios will be useful if they are benchmarked against something else, like past performance or another company.
How to Use Financial Ratio Calculator Spreadsheet
There are only three worksheets in this spreadsheet. So, To use this financial ratio calculator correctly, you need to type row numbers from respective account names financial ratio worksheet. But before that, you need to copy your own balance sheet report and income statement into their respective worksheets. You don’t have to paste it in row by row, or try to put values in respective fields. You just need to type corresponding number at the left hand side of those reports in financial ratio worksheet. So, Don’t overwrite numbers at the left side of particular worksheets. They are all will be used in VLOOKUP formulas to get respective values. Thus, Dummy accounts and values are written in this financial ratio calculator spreadsheet to help you understanding this template well.
There are two background colors in cells where you need to put row numbers. So, Green color indicates that the values refer to Balance Sheet worksheet while orange color refers to values from Profit and Loss worksheet.
You can modify this spreadsheet to suit your own needs. You can add more ratios. Also, you can add more columns to cover calculation from other year period. You can generate KPI comparison chart and make it as your company’s default chart. But, you need to define your KPI values first.
