Great news for all Invest for Excel users – there are now 26 financial ratios that you can add to your calculation! Just select the most important ratios that you want to include in your “Key Financials” table and voilà – ratios are calculated automatically, there is even an option to classify them and provide a formula description to each selected ratio!
But first things first. We classified ratios into 6 main groups:
Just press the “%” button, select those ratios that you need, include category header or (and) formulas as comments and it’s done!
Current Ratio = Current Assets/Short-term Liabilities
Quick Ratio = (Current Assets – Inventories)/Short-term Liabilities
Absolute Liquidity Ratio = Bank and cash/(Short-term Liabilities – Receivables)
Cash Ratio = Bank and cash/Short-term Liabilities
Inventory Turnover Ratio *= (Inventories [previous year] + Variable costs – Inventories)/ Inventories [average]
Receivables Turnover Ratio = Net Sales/Accounts Receivable [average]
Capital Turnover Ratio = Net Sales/Shareholders’ Equity
Asset Turnover Ratio = (Net Sales + Other operating income)/Total Assets [average]
Net Working Capital Ratio = Net Sales/Net Working Capital
*Note! “Variable costs” should include only items related to inventory so the formula may need adjusting.
Gross Margin = Gross margin/Net Sales
EBITDA Margin = EBITDA/Net Sales
EBIT Margin = EBIT/Net Sales
Earnings Margin = Net profit/Net Sales
Return On Assets (ROA) = Net Profit/Total Assets [average]
Return on net assets (RONA), % = Operating profit before tax EBIT/Net assets Average balance
Return On Capital Employed (ROCE) = EBIT/(Total Assets – Short-term Liabilities)
Return On Average Capital Employed (ROACE) = EBIT/(Average Total Assets – Average Short-term Liabilities)
Return On Investment (ROI) = Net Profit/(Shareholders’ Equity + Interest-bearing Debt)
Return On Equity (ROE) = Net Profit/Shareholders’ Equity [average]
Operating Leverage = % change in EBIT/% change in Sales
Financial Leverage = % change in Net Profit/% change in EBIT
Total Leverage = % change in Net Profit/% change in Sales
Debt Ratio (Leverage) = Total Liabilities/Total Assets
Debt-to-Equity Ratio (Net Gearing) = Total Liabilities/Shareholders’ Equity
Interest Coverage Ratio * = EBITDA/Interest Expense
Debt Service Coverage Ratio ** = EBIT/Debt Service
* “Interest Expense” should include financing cost payments so the formula may need adjusting.
** “Debt service” should include financing cost payments and loan amortization payments so the formula may need adjusting.
Fixed Asset Ratio = Fixed assets/(Total assets – Short-term Liabilities)
Current Asset to Fixed Asset = Current Assets/ Fixed Assets
Proprietary Ratio (Equity Ratio) = Shareholders’ Equity/Total Assets
Haven’t tried Invest for Excel yet?
Download a free test version here!