Financial Ratios Analysis in Excel
Financial ratios analysis are calculations that are derived from the financial statements and other financial data to measure various aspects of a company. They can be compared with other companies or to industry standards. This tutorial demonstrates how to calculate several financial ratios. (See image below)
Liquidity ratios is a financial ratios analysis that measures a company’s ability to pay its bills in the short term. Poor liquidity ratios may indicate that the company has a high cost of financing or is on the verge of bankruptcy.
Net working capital is computed by subtracting current liabilities from current assets:
Current assets are turned into cash within one accounting period (usually one year). Current liabilities are debts that will be paid within one period. A positive number here indicates that the company has enough assets to pay for its short-term liabilities.
The current ratio is a similar measure that divides current assets by current liabilities:
When this ratio is greater than 1:1, it’s the same as when net working capital is positive.
The final liquidity ratio is the quick ratio. Although the current ratio includes assets, such as inventory and accounts receivable that will be converted into cash in a short time, the quick ratio includes only cash and assets that can be converted into cash immediately.
A quick ratio greater than 1:1 indicates that the company can pay all its short-term liabilities right now.
Asset use Ratios
Asset use ratios measure how efficiently a company is using its assets: that is, how quickly the company is turning its assets back into cash. The accounts receivable turnover ratio divides sales by average accounts receivable:
Accounts receivable turnover is then used to compute the average collection period:
The average collection period is generally compared against the company’s credit terms. If the company allows 30 days for its customers to pay and the average collection period is greater than 30 days, it can indicate a problem with the company’s credit policies or collection efforts.
The efficiency with which the company uses its inventory can be similarly computed. Inventory turnover divides the cost of sales by average inventory:
The average age of inventory tells how many days’ inventory is in stock before it is sold:
By adding the average collection period to the average age of inventory, the total days to convert inventory into cash can be computed. This is the operating cycle and is computed as follows:
Whereas liquidity ratios compute a company’s ability to pay a short-term debt, solvency ratios compute its ability to pay long-term debt. The debt ratio compares total assets with total liabilities:
The debt-to-equity ratio divides total liabilities by total equity. It’s used to determine whether a company is primarily equity financed or debt-financed:
The time’s interest earned ratio computes how many times a company’s profit would cover its interest expense:
As you might guess, profitability ratios measure how much profit a company makes. Gross profit margin and net profit margin can be seen on the earlier common size financial statements because they are both ratios computed relative to sales. The formulas for gross profit margin and net profit margin follow:
The return on assets computes how well a company uses its assets to produce profits:
The return on equity computes how well the owners’ investments are performing: