Essential Ratios for Financial Analysis in Excel

A balance sheet full of numbers tells you almost nothing on its own. A current ratio of 2.1, though, tells you the business can comfortably pay its short-term bills. That is the entire point of ratio analysis: turning raw figures from the Trading Account, Profit & Loss Statement, and Balance Sheet into a handful of numbers that reveal whether a business is healthy, overleveraged, or quietly running out of cash. Once you know the formulas, Excel does the heavy lifting, recalculating every ratio the moment a single number in your spreadsheet changes.

What ratio analysis actually measures 🔗

Ratio analysis compares two related figures from a company’s financial statements to produce a single, comparable number. A current ratio, a debt-equity ratio, or a net profit ratio each answers a different question: Can the firm pay its short-term debts? How much of the business is funded by borrowing? How much of every rupee of sales actually becomes profit? None of these ratios mean much when read in isolation, and industry context matters a great deal, since acceptable current ratios and margins vary widely depending on the sector a business operates in, as noted in Lumen Learning’s introduction to business finance.

Every ratio you calculate draws from one of three statements: the Trading Account (which gives you sales and cost of goods sold), the Profit & Loss Statement (which gives you operating expenses and net profit), or the Balance Sheet (which gives you assets, liabilities, and equity). Building a spreadsheet that links all three statements to a separate “Ratios” sheet means you calculate each figure once and every ratio updates automatically.

Liquidity ratios: can the business pay its bills 🔗

Liquidity ratios check whether a firm has enough short-term assets to cover its short-term obligations. These are usually the first ratios a bank or supplier looks at before extending credit.

Current ratio 🔗

The current ratio divides current assets by current liabilities. A ratio above 1 generally means the company can cover its short-term liabilities without financial strain, and a reading close to 2 is often seen as a comfortable position, though the exact benchmark depends on the industry, as CFI’s explanation of liquidity ratios points out.

In Excel: =Current_Assets/Current_Liabilities, referencing the totals pulled from your Balance Sheet tab.

Quick ratio (acid-test ratio) 🔗

The quick ratio is a stricter version of the current ratio. It excludes inventory and prepaid expenses, since these cannot always be converted into cash quickly, leaving only cash, marketable securities, and receivables against current liabilities. This makes it especially useful for businesses that carry large inventories that take time to sell, since a business could look solvent by the current ratio alone while the quick ratio tells a different story.

In Excel: =(Current_Assets-Inventory-Prepaid_Expenses)/Current_Liabilities.

Solvency ratio: how much of the business runs on debt 🔗

Debt-equity ratio 🔗

The debt-equity ratio compares total debt, meaning everything a business owes to outsiders, against shareholders’ equity. Unless a specific ratio calls for “long-term debt” alone, debt is generally treated as the sum of current and non-current liabilities, as explained by AccountingCoach’s breakdown of leverage ratios. A lower ratio generally signals a business relies less on borrowed money and carries less financial risk, while a high ratio means the company depends heavily on creditors to fund operations.

In Excel: =Total_Debt/Shareholders_Equity.

Profitability ratios: is the business actually making money 🔗

These ratios come from the Trading Account and Profit & Loss Statement and answer a simple question: out of every rupee of sales, how much survives as profit at each stage?

Gross profit ratio 🔗

Gross profit ratio expresses gross profit (sales minus cost of goods sold) as a percentage of net sales. It tells you how efficiently a business is managing its direct production or purchase costs, before overheads like rent, salaries, and marketing enter the picture.

In Excel: =(Gross_Profit/Net_Sales)*100.

Net profit ratio 🔗

Net profit ratio takes this a step further, expressing net profit, after all operating expenses, interest, and tax, as a percentage of net sales. A shrinking net profit ratio even while gross profit ratio holds steady usually points to overheads growing faster than revenue.

In Excel: =(Net_Profit/Net_Sales)*100.

Operating cost ratio 🔗

The operating ratio measures the total operating cost, cost of goods sold plus administrative and selling expenses, as a percentage of net sales. A worked example from ICAI’s own study material shows a firm with sales of roughly ₹1.97 crore and an operating cost of about ₹1.80 crore arriving at an operating ratio of 91.75 percent, meaning only around 8 percent of sales was left to cover interest, tax, and profit. The lower this ratio, the more efficiently the business is controlling its costs.

In Excel: =((COGS+Operating_Expenses)/Net_Sales)*100.

Efficiency ratios: how well is the business using its resources 🔗

Inventory turnover ratio 🔗

This ratio shows how many times a business sells and replaces its stock during a period. It is calculated by dividing cost of goods sold by average inventory, and a higher turnover generally signals efficient stock management, as summarised in the ICAI’s reference table of financial ratios. A low turnover often means capital is tied up in slow-moving stock.

In Excel: =COGS/AVERAGE(Opening_Inventory,Closing_Inventory). The AVERAGE function is useful here because opening and closing stock figures usually sit in different cells.

Working capital turnover ratio 🔗

Working capital turnover ratio links net sales to average working capital, where working capital itself equals current assets minus current liabilities. It shows how efficiently a firm converts its working capital into sales revenue. A manufacturing firm generating ₹60 lakh in annual sales against average working capital of ₹12 lakh, for instance, would post a ratio of 5 times, meaning every rupee of working capital supports ₹5 of sales, an example worked out in detail by ClearTax’s guide to the ratio. A very high ratio can also be a warning sign of overtrading, where a business is stretching too thin on too little working capital.

In Excel: =Net_Sales/AVERAGE(Opening_Working_Capital,Closing_Working_Capital).

Return ratios: measuring what the business earns on what it employs 🔗

Return on working capital 🔗

Return on working capital compares net profit (often taken before interest and tax) to the net working capital employed in the business. It tells you how productively the funds tied up in day-to-day operations, stock, receivables, and cash, are being converted into profit, complementing the working capital turnover ratio, which measures sales rather than profit.

In Excel: =Net_Profit/Net_Working_Capital*100.

Return on capital employed (ROCE) 🔗

ROCE divides earnings before interest and tax (EBIT) by capital employed, where capital employed is usually calculated as total assets minus current liabilities. This makes ROCE one of the more complete profitability measures, since it accounts for both the equity shareholders have invested and the long-term debt the business is using, as explained in CFI’s guide to return on capital employed. Investors and lenders often use ROCE to judge whether a company is generating enough return to justify the capital tied up in it.

In Excel: =EBIT/(Total_Assets-Current_Liabilities)*100.

Setting up a ratio analysis sheet in Excel 🔗

The formulas above are simple, but a few Excel habits make the difference between a spreadsheet that works once and one you can reuse every year.

Link, don’t retype 🔗

Pull every figure a ratio needs directly from your Trading Account, P&L, or Balance Sheet tabs using cell references, rather than typing numbers again into the ratio sheet. This way, updating one figure automatically recalculates every ratio built on it.

Use named ranges for clarity 🔗

Instead of formulas full of cell addresses like =B12/B18, name your cells Current_Assets, Current_Liabilities, and so on through Excel’s Name Box. A formula like =Current_Assets/Current_Liabilities is far easier to check for errors and to explain in a project report, an approach recommended in Zebra BI’s guide to calculating financial ratios.

Guard against errors 🔗

Wrap ratio formulas in IFERROR so that a missing figure or a zero in the denominator does not break your sheet: =IFERROR(Current_Assets/Current_Liabilities,”N/A”). This is especially useful in a working sheet where some figures might not be filled in yet.

Format for readability 🔗

Ratios expressed as a proportion, like the current ratio, work best as numbers with two decimal places. Ratios expressed as percentages, like the gross profit ratio, should be formatted as percentages directly in Excel rather than manually multiplied by 100 in every formula where it can be avoided. Turnover ratios, like inventory turnover, are usually shown as “times” (for example, 6.5 times) to reflect how often the underlying asset cycles through the business in a year.

Build a simple trend chart 🔗

Once ratios are calculated for two or three years side by side, a basic line chart in Excel makes it immediately visible whether liquidity is improving, whether debt is climbing, or whether profitability is under pressure. A single year’s ratio tells you where a business stands; a trend across years tells you where it is heading.

What do you think? 🔗

What do you think? If you had to judge a small retail business using only two ratios from this list, which two would you pick, and why? And once you build a ratio sheet like this in Excel, would you trust a single year’s numbers, or would you want at least three years of data before drawing any conclusions?

References

1. https://courses.lumenlearning.com/suny-hccc-introbusiness/chapter/financial-ratio-analysis/
2. https://corporatefinanceinstitute.com/resources/accounting/current-ratio-vs-quick-ratio/
3. https://www.accountingcoach.com/financial-ratios/explanation
4. https://www.vknow.in/images/uploaded/CA%20Inter%20FM%20Revision%20Chapter%20-1.pdf
5. https://live.icai.org/bos/vcc-2nd-batch-recorded-lectures/pdf/Ratios%20ICAI%20Format.pdf
6. https://www.clear.in/s/working-capital-turnover-ratio
7. https://corporatefinanceinstitute.com/learn/resources/accounting/return-on-capital-employed-roce
8. https://zebrabi.com/how-to-calculate-and-interpret-financial-ratios-in-excel/