TEMPLATES & BUSINESS WORKFLOWS
Financial Ratios in Excel: Liquidity, Profitability, Leverage and Efficiency (Free Template)
Financial statements tell you what happened; ratios tell you what it means. Is there enough cash to pay the bills? Is the business earning enough on what is invested in it? Is debt under control? How long does cash stay tied up in stock and customers? In this guide you will build a financial ratio analysis in Excel from a balance sheet and an income statement: 16 ratios in four groups, annualized correctly for a part-year, each compared with a target that flags OK or Watch. The free template uses the same company as the balance sheet and cash flow statement templates, so every number ties.

The 16 ratios at a glance
| Group | Ratio | Formula | Sep 30, 2026 |
|---|---|---|---|
| Liquidity | Current ratio | Current assets / current liabilities | 2.79 |
| Liquidity | Quick ratio | (Cash + receivables) / current liabilities | 1.55 |
| Liquidity | Cash ratio | Cash / current liabilities | 0.62 |
| Profitability | Gross margin | Gross profit / revenue | 37.0% |
| Profitability | Operating margin | Operating income / revenue | 13.4% |
| Profitability | Net margin | Net income / revenue | 10.2% |
| Profitability | Return on assets | Annual net income / average total assets | 22.4% |
| Profitability | Return on equity | Annual net income / average equity | 36.1% |
| Leverage | Debt to equity | Total liabilities / equity | 0.55 |
| Leverage | Debt ratio | Total liabilities / total assets | 0.36 |
| Leverage | Interest coverage | Operating income / interest expense | 19.7 |
| Efficiency | Inventory turnover | Annual COGS / average inventory | 6.57 |
| Efficiency | Days inventory outstanding | 365 / inventory turnover | 55.5 |
| Efficiency | Days sales outstanding | Average receivables / annual revenue x 365 | 29.7 |
| Efficiency | Days payables outstanding | Average payables / annual COGS x 365 | 29.7 |
| Efficiency | Cash conversion cycle | Days inventory + days sales – days payables | 55.5 |
Step 1: Start from the balance sheet and income statement
The Statements sheet holds a condensed balance sheet for Dec 31, 2025 and Sep 30, 2026, taken from the balance sheet template, and the income statement for the nine months to Sep 30, 2026, which you would take from your profit and loss statement. Two more inputs matter: the number of months in the period (9) and the dividends paid (12,000.00).
Ratios are only as reliable as the numbers behind them, so the sheet checks them first: both balance sheets must balance, and opening equity + net income – dividends must equal closing equity:
=IF(ROUND(B16+B30-B32-C16,2)=0,"Ties","Check")

Step 2: Liquidity ratios
Liquidity ratios ask whether the business can pay what it owes in the next 12 months. On the Ratios sheet, B6 is the current ratio:
=Statements!C8/Statements!C13
The quick ratio leaves out inventory and prepaid expenses, which cannot be turned into cash quickly, and the cash ratio counts only cash:
=(Statements!C4+Statements!C5)/Statements!C13
A current ratio of 2.79 means 2.79 of current assets for every 1.00 of current liabilities. A very high ratio is not always good news: it can mean too much cash tied up in stock or slow-paying customers, which the efficiency ratios in Step 5 will show. Use the accounts receivable aging report to see how collectible the receivables really are.

Step 3: Profitability ratios
Margins divide each level of profit by revenue, for example net margin in B12: =Statements!B30/Statements!B21. Returns compare profit with what is invested in the business, and here the period matters. Net income covers nine months, so it is annualized before dividing by the average of the opening and closing balances. Return on equity in B14:
=Statements!B30*(12/Statements!$B$31)/AVERAGE(Statements!B16,Statements!C16)
31,990.23 x 12 / 9 = 42,653.64 of annual net income, divided by average equity of 117,995.12, gives 36.1%. Without annualizing, a nine-month return would look a quarter smaller than it really is; with a year-end balance only, a fast-growing business would look worse than it is.

Step 4: Leverage ratios
Leverage ratios show how much of the business is funded by lenders and suppliers rather than the owners, and whether profit comfortably covers the interest:
| Ratio | Formula in the template | Result |
|---|---|---|
| Debt to equity | =Statements!C15/Statements!C16 | 0.55 |
| Debt ratio | =Statements!C15/Statements!C10 | 0.36 |
| Interest coverage | =Statements!B26/Statements!B27 | 19.7 |
The template uses total liabilities for debt, the same definition as the balance sheet template. Lenders often use interest-bearing debt only (here the bank loan, 40,800.00), so check the definition in your loan agreement before comparing with a covenant. Interest coverage of 19.7 times means operating income could fall by 95% before it no longer covered the interest.

Step 5: Efficiency ratios and the cash conversion cycle
Efficiency ratios measure how long cash is tied up in the business. Inventory turnover in B20 divides annual cost of goods sold by average inventory, and days inventory outstanding in B21 is =365/B20. Days sales outstanding in B22:
=AVERAGE(Statements!B5,Statements!C5)/(Statements!B21*(12/Statements!$B$31))*365
Days payables outstanding works the same way with payables and cost of goods sold. Then the cash conversion cycle in B24 is =B21+B22-B23: 55.5 days of stock + 29.7 days to collect – 29.7 days of supplier credit = 55.5 days that the business has to fund itself. Shortening any part releases cash: tighter stock control (the FIFO inventory template values it), faster collection, or agreed longer terms with suppliers (see the accounts payable aging report). The cash flow statement shows the same effect as working capital changes.

Step 6: Compare every ratio with a target
A ratio on its own says little; it needs a benchmark. Each row has a target (column C) and a direction (column D, Higher or Lower from a drop-down). The status in E6:
=IF(D6="Higher",IF(B6>=C6,"OK","Watch"),IF(B6<=C6,"OK","Watch"))
Conditional formatting colors OK green and Watch red; see conditional formatting based on another cell for the technique. B26 counts the ratios on Watch with =COUNTIF(E6:E24,"Watch"). In the example only days payables outstanding (29.7 against a target of 30.0) is on Watch: suppliers are being paid slightly faster than planned.
Set targets from your industry, your lender's covenants or your own history. Comparing the same ratio over time is often more useful than any external benchmark.

Common mistakes with financial ratios
- Mixing periods: a nine-month income statement against a full-year balance sheet without annualizing.
- Using year-end balances only for returns and turnover, instead of the average of the opening and closing balances.
- Comparing with a benchmark that defines a ratio differently (total liabilities vs interest-bearing debt, for example).
- Reading one ratio alone: a high current ratio with a long cash conversion cycle can still mean a cash squeeze.
- Forgetting to check that the statements balance before calculating anything.
To track ratios month by month, put them next to your budget variance report, chart actual against target with a combo chart, or build a KPI dashboard.
Frequently asked questions
What are the most important financial ratios?
For a small business, start with the current ratio (can you pay your bills), net margin (how much of each sale is profit), debt to equity (how much is borrowed) and the cash conversion cycle (how long cash is tied up).
How do I calculate the current ratio in Excel?
Divide total current assets by total current liabilities, for example =C8/C13. A result above 1 means current assets cover current liabilities.
What is the difference between the current ratio and the quick ratio?
The quick ratio leaves out inventory and prepaid expenses, because they cannot be turned into cash quickly. It is a stricter test of liquidity.
How do I annualize a ratio for part of a year?
Multiply the income statement figure by 12 and divide by the number of months, then divide by the average balance. For nine months: net income x 12 / 9.
What is a good debt to equity ratio?
It depends on the industry. Many lenders are comfortable below 1 to 2 for small businesses, but check your own loan covenants and compare with similar companies.
Final checklist
- Both balance sheets balance and the equity roll-forward ties.
- Months in the period matches the income statement.
- Targets and directions are set for your business.
- Ratios on Watch have an explanation.
- Definitions match any lender covenants you report against.
Turn ratios into decisions
Browse the ready-to-use Excel templates, visit the OneXcel Tutorial Hub for step-by-step guides, or put your ratios on a dashboard with the OneXcel Dashboard Builder.
