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.

Financial ratios in Excel at Sep 30, 2026: days inventory 55.5, days receivables 29.7 and days payables 29.7, giving a cash conversion cycle of 55.5 days.
Days inventory + days receivables – days payables = the cash conversion cycle.

The 16 ratios at a glance

GroupRatioFormulaSep 30, 2026
LiquidityCurrent ratioCurrent assets / current liabilities2.79
LiquidityQuick ratio(Cash + receivables) / current liabilities1.55
LiquidityCash ratioCash / current liabilities0.62
ProfitabilityGross marginGross profit / revenue37.0%
ProfitabilityOperating marginOperating income / revenue13.4%
ProfitabilityNet marginNet income / revenue10.2%
ProfitabilityReturn on assetsAnnual net income / average total assets22.4%
ProfitabilityReturn on equityAnnual net income / average equity36.1%
LeverageDebt to equityTotal liabilities / equity0.55
LeverageDebt ratioTotal liabilities / total assets0.36
LeverageInterest coverageOperating income / interest expense19.7
EfficiencyInventory turnoverAnnual COGS / average inventory6.57
EfficiencyDays inventory outstanding365 / inventory turnover55.5
EfficiencyDays sales outstandingAverage receivables / annual revenue x 36529.7
EfficiencyDays payables outstandingAverage payables / annual COGS x 36529.7
EfficiencyCash conversion cycleDays inventory + days sales – days payables55.5
Free download: Get the financial ratios template (.xlsx). It has a Statements sheet with two balance sheets and an income statement, a Ratios sheet with targets and status flags, and a How It Works sheet. Works in Excel 2010 and later, Microsoft 365 and Excel for the web.

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")
Excel financial statements with a balance sheet that shows Balanced for both dates and an income statement for nine months with net income of 31,990.23.
Checked statements first, ratios second.
Checkpoint: Both dates show Balanced, net income is 31,990.23 and the equity roll-forward shows Ties: 108,000.00 + 31,990.23 – 12,000.00 = 127,990.23.

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.

Excel liquidity ratios at Sep 30, 2026: current ratio 2.79, quick ratio 1.55 and cash ratio 0.62, all OK against their targets.
Current, quick and cash ratios.

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.

Excel profitability ratios: gross margin 37.0%, operating margin 13.4%, net margin 10.2%, annualized return on assets 22.4% and return on equity 36.1%.
Margins and annualized returns.
Checkpoint: Gross margin 115,588.00 / 312,400.00 = 37.0%. Return on assets 42,653.64 / 190,315.34 = 22.4%.

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:

RatioFormula in the templateResult
Debt to equity=Statements!C15/Statements!C160.55
Debt ratio=Statements!C15/Statements!C100.36
Interest coverage=Statements!B26/Statements!B2719.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.

Excel leverage ratios: debt to equity 0.55, debt ratio 0.36 and interest coverage of 19.7 times.
Debt to equity, debt ratio and interest coverage.

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.

Excel efficiency ratios: inventory turnover 6.57, days inventory 55.5, days sales outstanding 29.7, days payables 29.7 and a cash conversion cycle of 55.5 days.
How long cash is tied up in stock and customers.

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.

Excel ratio status column comparing each ratio with its target: one ratio on Watch, days payables outstanding of 29.7 against a target of 30.0.
OK or Watch for every ratio, and a count of what needs attention.
Try it: On the Statements sheet change Sep 30 inventory (C6) from 38,640.00 to 58,640.00 and other current liabilities (C12) from 16,937.40 to 36,937.40, so the balance sheet still balances. The current ratio falls to 2.19, the quick ratio to 1.03 and the cash ratio to 0.41; cash ratio, inventory turnover and days inventory outstanding show Watch, and Ratios on Watch becomes 4. Change both back.

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.
Practice file: Download the financial ratios template. Replace the yellow statement figures and targets with your own; every ratio and status updates automatically.

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.