Financial Ratio Calculator

• Version
• 17'428 Downloads
• 302 KB File Size
• January 4, 2022 Updated
• 19 Number of comments
• ★★★★★

  ★★★★★
  Rating

Financial ratio is a financial metric to evaluate the overall financial condition of a corporation. Big companies usually rely their financial performances by analyzing results of these financial ratios. This financial ratio is part of and the main Key Performance Indicator (KPI) for majority companies around the world.

Financial ratios are categorized according to the financial aspect of the business which the ratio measures. This financial ratio calculator in excel spreadsheet will help you calculate those important metrics. It should also help you to learn which accounts in balance sheet as well as profit and loss statement to generate those ratios. You can customize this spreadsheet easily by typing row numbers next to respective account names.

Financial Ratio Categories

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. All numbers are taken from balance sheet statement.

Current Ratio = Current Assets/Current Liabilities

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

This ratio is similar with Current Ratio except it takes into account cash and obligation only from current assets

Quick Ratio = Current Assets (Cash, Obligation and Accounts Receivable)/Current Liabilities

Quick ratio adds accounts receivable as part of current assets along with cash and obligation

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

Financial ratios above might or might not suit with your company's condition. There are still other financial ratios options you can choose if you fill some of ratios above are not suitable. 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. But, remember to make it apple to apple if you are benchmarking with other companies in term of company business and size.

How to Use Financial Ratio Calculator Spreadsheet

There are only three worksheets in this spreadsheet. 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 respective worksheets. You don't have to paste it row by row, or try to put values in respective fields. You just need to type corresponding number at the left side of those reports in financial ratio worksheet. Don't overwrite numbers at the left side of particular worksheets. They are all will be used in VLOOKUP formulas to get respective values. 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. Green color indicates that the values refer to Balance Sheet worksheet while orange color refers to values from Profit and Loss worksheet.

You may 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.