Product Description
Financial Ratios Calculator in Excel
Find all the business ratios that bankers and professional analysts use evaluate companies.
Download our financial ratios calculator in excel and simply enter in the items from a year end financial statement. The calculator will handle up to 5 years of financial ratios.
Liquidity Ratios:
- Current Ratio
- Quick Ratio
Profitability Ratios:
- Net Margin
- Return on Assets
- Return on Equity
Capital Structure
- Debt Ratio
- Time Interest Earned
Asset Management
- Asset Turnover
- Average Collection Period
- Inventory Turnover
Performance Ratios
- Earnings per Share
- Price to Earnings
- Dividends per Share
- Dividend Payout Ratio
There is a lot to be said for valuing a company, it is no easy task. If you have yet to discover this goldmine, the satisfaction one gets from tearing apart a companies financial statements and analyzing it on a whole different level is great – especially if you make or save yourself money for your efforts.
In this template, we present 14 basic fundamental analysis ratios to help you get started. The ratios are presented in a manner to make them easier to understand. Sure some of the ratios have different varieties, but by the end you will understand the basic premise and reasons for fundamental analysis. The ratios in the template (and much more) are explained below.
The Ratios
Profitability Sustainability Ratios
How well is our business performing over a specific period, will your social enterprise have the financial resources to continue serving its constituents tomorrow as well as today?
| Ratio | What does it tell you? |
| Sales Growth = Current Period –Previous Period Sales Previous Period Sales |
Percentage increase (decrease) in sales between two time periods.If overall costs and inflation are increasing, then you should see a corresponding increase in sales. If not, then may need to adjust pricing policy to keep up with costs. |
| Reliance on Revenue Source =Revenue Source Total RevenueTotal Revenue |
Measures the composition of an organization’s revenue sources (examples are sales, contributions, grants).The nature and risk of each revenue source should be analyzed. Is it recurring, is your market share growing, is there a long term relationship or contract, is there a risk that certain grants or contracts will not be renewed, is there adequate diversity of revenue sources?Organizations can use this indicator to determine long and short-term trends in line with strategic funding goals (for example, move towards self-sufficiency and decreasing reliance on external funding). |
| Operating Self-Sufficiency = Business Revenue Total Expenses |
Measures the degree to which the organization’s expenses are covered by its core business and is able to function independent of grant support.For the purpose of this calculation, business revenue should exclude any non-operating revenues or contributions. Total expenses should include all expenses (operating and non-operating) including social costs.A ratio of 1 means you do not depend on grant revenue or other funding. |
| Gross Profit Margin = Gross Profit Total Sales Total Sales |
How much profit is earned on your products without considering indirect costs.Is your gross profit margin improving? Small changes in gross margin can significantly affect profitability. Is there enough gross profit to cover your indirect costs. Is there a positive gross margin on all products? |
| Net Profit Margin = Net Profit Sales |
How much money are you making per every $ of sales. This ratio measures your ability to cover all operating costs including indirect costs |
| SGA to Sales = Indirect Costs (sales, general, admin) Sales |
Percentage of indirect costs to sales.Look for a steady or decreasing ratio which means you are controlling overhead |
| Return on Assets = Net Profit Average Total Assets |
Measures your ability to turn assets into profit. This is a very useful measure of comparison within an industry.A low ratio compared to industry may mean that your competitors have found a way to operate more efficiently. After tax interest expense can be added back to numerator since ROA measures profitability on all assets whether or not they are financed by equity or debt |
| Return on Equity =Net Profit Average Shareholder Equity |
Rate of return on investment by shareholders.This is one of the most important ratios to investors. Are you making enough profit to compensate for the risk of being in business?How does this return compare to less risky investments like bonds? |
Operational Efficiency Ratios
How efficiently are you utilizing your assets and managing your liabilities? These ratios are used to compare performance over multiple periods.
Here are some commonly used ratios:
| Ratio | What does it tell you? |
| Operating Expense Ratio = Operating Expenses Total Revenue |
Compares expenses to revenue.A decreasing ratio is considered desirable since it generally indicates increased efficiency. |
| Accounts Receivable Turnover = Net Sales Average Accounts ReceivableDays in Accounts Receivable = Average Accounts Receivable Sales x 365 |
Number of times trade receivables turnover during the year.The higher the turnover, the shorter the time between sales and collecting cash.What are your customer payment habits compared to your payment terms. You may need to step up your collection practices or tighten your credit policies. These ratios are only useful if majority of sales are credit (not cash) sales. |
| Inventory Turnover = Cost of Sales Average InventoryDays in Inventory = Average Inventory Cost of Sales x 365 |
The number of times you turn inventory over into sales during the year or how many days it takes to sell inventory.This is a good indication of production and purchasing efficiency. A high ratio indicates inventory is selling quickly and that little unused inventory is being stored (or could also mean inventory shortage). If the ratio is low, it suggests overstocking, obsolete inventory or selling issues. |
| Accounts Payable Turnover = Cost of Sales Average Accounts PayableDays in Accounts Payable = Average Accounts Payable Cost of Sales x 365 |
The number of times trade payables turn over during the year.The higher the turnover, the shorter the period between purchases and payment. A high turnover may indicate unfavourable supplier repayment terms. A low turnover may be a sign of cash flow problems.Compare your days in accounts payable to supplier terms of repayment. |
| Total Asset Turnover = Revenue Average Total AssetsFixed Asset Turnover = Revenue Average Fixed Assets |
How efficiently your business generates sales on each dollar of assets.An increasing ratio indicates you are using your assets more productively. |
Liquidity Ratios
Does your enterprise have enough cash on an ongoing basis to meet its operational obligations? This is an important indication of financial health.
| Ratio | What does it tell you? |
| Current Ratio =Current Assets Current Liabilities(also known as Working Capital Ratio) |
Measures your ability to meet short term obligations with short term assets., a useful indicator of cash flow in the near future.A social enterprise needs to ensure that it can pay its salaries, bills and expenses on time. Failure to pay loans on time may limit your future access to credit and therefore your ability to leverage operations and growth.A ratio less that 1 may indicate liquidity issues. A very high current ratio may mean there is excess cash that should possibly be invested elsewhere in the business or that there is too much inventory. Most believe that a ratio between 1.2 and 2.0 is sufficient.The one problem with the current ratio is that it does not take into account the timing of cash flows. For example, you may have to pay most of your short term obligations in the next week though inventory on hand will not be sold for another three weeks or account receivable collections are slow. |
| Quick Ratio = Cash +AR + Marketable Securities Current Liabilities |
A more stringent liquidity test that indicates if a firm has enough short-term assets (without selling inventory) to cover its immediate liabilities.This is often referred to as the “acid test” because it only looks at the company’s most liquid assets only (excludes inventory) that can be quickly converted to cash).A ratio of 1:1 means that a social enterprise can pay its bills without having to sell inventory. |
| Working Capital = Current Assets – Current Liabilities | WC is a measure of cash flow and should always be a positive number. It measures the amount of capital invested in resources that are subject to quick turnover. Lenders often use this number to evaluate your ability to weather hard times. Many lenders will require that a certain level of WC be maintained. |
| Adequacy of Resources = Cash + Marketable Securities + Accounts Receivable Monthly Expenses |
Determines the number of months you could operate without further funds received (burn rate) |
Leverage Ratios
To what degree does an enterprise utilize borrowed money and what is its level of risk? Lenders often use this information to determine a business’s ability to repay debt.
| Ratio | What does it tell you? |
| Debt to Equity =Short Term Debt + Long Term Debt Total Equity (including grants) |
Compares capital invested by owners/funders (including grants) and funds provided by lenders.Lenders have priority over equity investors on an enterprise’s assets. Lenders want to see that there is some cushion to draw upon in case of financial difificulty. The more equity there is, the more likely a lender will be repaid. Most lenders impose limits on the debt/equity ratio, commonly 2:1 for small business loans.Too much debt can put your business at risk, but too little debt may limit your potential. Owners want to get some leverage on their investment to boost profits. This has to be balanced with the ability to service debt. |
| Interest Coverage =EBITDA Interest Expense |
Measures your ability to meet interest payment obligations with business income. Ratios close to 1 indicates company having difficulty generating enough cash flow to pay interest on its debt. Ideally, a ratio should be over 1.5 |
Other Ratios
You may want to develop your own customized ratios to communicate results that are specific and important to your organization. Here are some examples.
| Operating Self-Sufficiency = Sales Revenue Total Costs (Operating and Social Costs)% Staffing Costs spent on Target Group = Target Staff Costs Total Staffing CostsSocial Costs per Employee = Total Social Costs Number of Target Employees% Social Costs covered by Grants = Grant Income Total Social Costs |
Financial Ratios Calculator in Excel
admin – :
All ratios, easy to use, just enter from your financials