Ratio Analysis – Excel Spread Sheet

Updated on

To assist you in comparing companies within the same industry. You will find notes to assist in calculations.

Get Our Activist Investing Case Study!

Get The Full Activist Investing Study In PDF

Also read:

  • Hedge Fund of funds Business Keeps Dying Every Year
  • Emerging Hedge Funds: Can They Outperform?
  • Baupost Letter Points To Concern Over Risk Parity, Systematic Strategies During Crisis
  • AI Hedge Fund Robots Beating Their Human Masters

Profitability Ratios

Ratio Method of Calculation Significance of each ratio
Return on Assets profit / average total assets Measures return earned through total assets provided by both creditors and owners
Return on equity profit - preference dividends / average ordinary equity Measures return earned on assets provided by owners
Net profit margin Net profit / Revenue Measures net profitability of each dollar of sales
Earnings per share Net profit - preference dividends /  weighted average number of ordinary shares issued Measures profit earned on each ordinary share
Price-earnings ratio Market price per ordinary share / earnings per share measures the amount investors are paying for a dollar of earnings
Earnings yield Earnings per ordinary share / market price per ordinary share Measures the return to an investors purchasing shares at the current market price
Dividend Yield Annual divdiend per ordinary share / market price per ordinary share Measures the return to an shareholders based on current market price
Dividend Payout Total dividends to ordinary shareholders / profit - preference dividends Measures the percentage of profits paid out to ordinary shareholders

Liquidity Ratios

Ratio Method of Calculation Significance of each ratio
Current Ratio Current Assets / Current Liabilites A measure of short term liquidity. Indicates the ability of an entity to meet its short-term debts from currrent assets.
Quick Ratio Cash assets + Receivables / Current Liabilities A more rigous measure of short term liquidity. Indices the ability of the etity to meet unexpected demands from liquid current assets
Receivables Turnover Net Sales Revenue / Average Receivables Balance Measures the effectiveness of collections; used to evaulate whether receivables balance is excessive
Average Collection Period Average Receivables Balance X 356 / Net Sales Revenue Measures the average number of days taken by the entity to collect its receivables
Inventory Turnover Cost of Sales / Average Inventory Balalnce Indicates the liquidity of inventory. Measures the number of times invenotru was sold on average during the period

Financial Stability

Ratio Method of Calculation Significance of each ratio
Debt ratio Total liabilites / total assets Measures percentage of assets provided by creditors and the extent of using gearing
Equity Ratio Total equity / total assets Measures percentage of assets provided by shareholders and the extent of using gearing
Capitalisation ratio Total assets / total equity Revenue / Average total assets
Times interest earned Profit before tax + Net finance costs / Net finance costs Measures the ability of the entity to meet its interest payments on borrowings out of current profits
Asset turnover ratio Revenue / Average total assets Measures the effectiveness of an enitty in using its assets during the period

Cash Sufficiency Ratio

Ratio Method of Calculation Significance of each ratio
Cash flow adequacy Cash flow from operating activities / repayment of long term borrowings + asseets acquired +dividends paid Measures the entity's ability to cover its main cash requirements
Repayment of long term borrowings Repayment of long term borrowings / cash flows from operating activities Measures the entity's ability to cover its long term debt out of cash from operating activities
Dividend payment Dividends paid / cash flows from operating activities Measures  the entity;s ability to cover its dividend payments
Reinvestment Purchase of property, plant and equipement / cash flows from operating activities Measures the entity's ability to pay for it's non current asets out of cash from operating activities. And provides you insight as to how much is required to reinvest back into the business just to maintain current revenues.
Debt coverage Non current liabilites / cash flows from operating activities Measures the payback period for coverage of long term debt

Cash Flow Efficiency Ratios

Ratio Method of Calculation Significance of each ratio
Cash flow to revenue Cash flows from operating activities / revenue Measures ability to convert revenue into cash flows
Operations Index Cash flows from operating activities / profit An index measuring the relationship between profit and operating cash flows
Cash flow return on assets Cash flow from operating activities / average total assets Measures the operating cash flow return on assets before interest and tax

Article by Searching For Value

Leave a Comment