Financial Ratio Analysis in Excel: Complete Guide with Indian Company Examples

The Financial Ratio Analysis Framework

Financial ratios distill complex financial statements into single numbers that reveal a company's operational health, financial stability, and value creation capacity. For Indian companies reporting under Ind AS, ratio analysis requires understanding both the standard ratio formulas and the India-specific nuances that affect their interpretation -- from the impact of promoter holdings and pledged shares to the implications of related party transactions and the distinct capital structures common in Indian business groups.

A systematic ratio analysis covers four dimensions. Liquidity ratios measure the company's ability to meet short-term obligations as they come due. Profitability ratios assess how effectively the company converts revenue into profit at various levels. Efficiency ratios evaluate how productively the company uses its assets and manages its working capital. Solvency ratios examine the company's long-term financial stability and its ability to service debt obligations. Together, these four dimensions provide a comprehensive financial health assessment.

The power of ratio analysis lies not in individual numbers but in comparisons -- across time (trend analysis), across companies (peer comparison), and against benchmarks (industry standards). A current ratio of 1.5 means nothing in isolation. It becomes meaningful when you know the same company had a current ratio of 2.1 two years ago (deteriorating trend), the industry average is 1.8 (below average), and the company's lender requires a minimum of 1.25 (still compliant but narrowing buffer).

Setting Up the Data Foundation in Excel

Before calculating ratios, structure your financial data properly. Create a Financial Data sheet with rows for each financial statement line item and columns for each year (typically 5 years of historical data). Pull data directly from annual reports filed on BSE or NSE, ensuring consistency in the accounting standard used. Key line items to capture from the balance sheet include total current assets, inventories, trade receivables, cash and cash equivalents, total current liabilities, trade payables, total debt (short-term and long-term borrowings), total assets, shareholders' equity, and total liabilities. From the income statement, capture revenue from operations, cost of materials consumed, employee benefit expenses, other expenses, EBITDA, depreciation, EBIT, finance costs, profit before tax, tax expense, and profit after tax.

Liquidity Ratios: Measuring Short-Term Financial Health

Liquidity ratios are the first line of defense in financial analysis. A company may be profitable on paper but still face a liquidity crisis if it cannot convert assets to cash quickly enough to meet obligations. This is particularly relevant in the Indian context where extended payment cycles, seasonal cash flow patterns, and the cash-intensive nature of many Indian businesses create significant liquidity management challenges.

Current Ratio

The current ratio measures the company's ability to pay short-term obligations with short-term assets. The Excel formula is =Current_Assets/Current_Liabilities. For an Indian IT services company, a current ratio of 2.5-3.5 is typical, reflecting high cash balances and receivables with relatively low current liabilities. Manufacturing companies typically show 1.2-1.8, with higher inventory and payable levels. A ratio below 1.0 indicates the company has more short-term obligations than short-term assets, which is a red flag unless the business model inherently generates negative working capital (like some retail businesses that collect cash before paying suppliers).

When analyzing Indian companies, examine the composition of current assets. A current ratio of 2.0 where current assets are primarily cash and receivables is qualitatively different from 2.0 where current assets are dominated by slow-moving inventory. The SEBI-required disclosure of aging schedules for receivables (under Ind AS revisions effective 2024) provides valuable data for assessing receivable quality.

Quick Ratio (Acid-Test Ratio)

The quick ratio strips out inventory and prepaid expenses, focusing on the most liquid assets. The Excel formula is =(Current_Assets - Inventories - Prepaid_Expenses)/Current_Liabilities. This ratio is more conservative and particularly important for manufacturing companies where inventory may take months to convert to cash. Indian FMCG companies typically maintain quick ratios of 0.8-1.5, while capital goods manufacturers may show 0.5-0.9. A quick ratio below 0.5 warrants investigation into the company's cash management and receivable collection efficiency.

Cash Ratio

The most conservative liquidity measure, the cash ratio considers only cash and cash equivalents plus current investments divided by current liabilities. The Excel formula is =(Cash_and_Equivalents + Current_Investments)/Current_Liabilities. While a high cash ratio provides safety, excessively high ratios (above 1.0) may indicate inefficient capital allocation -- the company is holding too much cash that could be deployed for growth. Indian IT services companies often show cash ratios exceeding 1.0, which has led to investor pressure for share buybacks and higher dividend payouts.

Profitability Ratios: Measuring Value Creation

Profitability ratios reveal how effectively a company converts revenue into profit at various stages. These ratios are central to equity valuation, management performance assessment, and competitive benchmarking.

Gross Profit Margin

Gross margin measures the percentage of revenue remaining after direct costs. The Excel formula is =(Revenue - COGS)/Revenue or equivalently =Gross_Profit/Revenue. For Indian companies, interpret gross margin in the context of the industry. IT services companies show gross margins of 28-40 percent. FMCG companies typically achieve 45-65 percent. Manufacturing companies range widely from 15-35 percent depending on the product. Steel and cement companies may show 20-30 percent gross margins that are highly sensitive to commodity price cycles.

Track gross margin trends year over year. A declining gross margin despite revenue growth signals pricing pressure, input cost inflation without pass-through ability, or product mix shifting toward lower-margin offerings. In the Indian context, GST rate rationalizations and changes in customs duty (as announced in Union Budgets) can create step-changes in gross margin that should be understood as regulatory impacts rather than operational deterioration.

Operating Profit Margin (EBITDA Margin)

EBITDA margin removes the impact of capital structure and tax planning, revealing core operational profitability. The Excel formula is =EBITDA/Revenue. This is the most commonly used profitability metric in Indian corporate analysis, private equity evaluation, and credit assessment. Indian IT services companies show EBITDA margins of 20-30 percent, with the top performers like TCS and Infosys consistently achieving the higher end. Pharmaceutical companies range from 18-28 percent. Real estate developers show highly variable margins of 15-35 percent depending on the project mix and delivery cycle.

Return on Equity (ROE)

ROE measures the return generated on shareholders' investment and is the single most important profitability metric for equity investors. The Excel formula is =Net_Income/Average_Shareholders_Equity. Use the average of opening and closing equity to account for capital changes during the year. Indian Nifty 50 companies show a median ROE of approximately 14-16 percent. Companies consistently delivering ROE above 20 percent -- such as Asian Paints, Bajaj Finance, and TCS -- command premium valuations in the Indian market.

Return on Capital Employed (ROCE)

ROCE is particularly important in the Indian context because it measures the return on all capital -- both equity and debt. The Excel formula is =EBIT/(Total_Assets - Current_Liabilities). ROCE is more informative than ROE for capital-intensive businesses and for comparing companies with different capital structures. Lenders use ROCE to assess whether the business generates sufficient returns to service debt. An ROCE consistently below the company's weighted average cost of capital indicates value destruction.

Efficiency Ratios: Measuring Operational Productivity

Efficiency ratios reveal how effectively a company manages its assets, collects receivables, and pays suppliers. For Indian businesses dealing with extended payment cycles and seasonal inventory patterns, these ratios provide critical operational insights.

Receivable Turnover and Days Sales Outstanding (DSO)

DSO measures how many days it takes to collect payment after a sale. The Excel formula is =Average_Trade_Receivables/(Revenue/365). Indian B2B companies typically show DSO of 45-90 days, with government-facing businesses often exceeding 120 days. PSU companies dealing with government clients may show DSO above 180 days. Tracking DSO trends is crucial -- a steadily increasing DSO signals deteriorating collection efficiency or potential bad debt issues, even if absolute revenue is growing.

Inventory Turnover and Days Inventory Outstanding (DIO)

DIO measures how many days inventory sits before being sold. The formula is =Average_Inventory/(COGS/365). FMCG companies aim for DIO of 30-60 days. Automobile manufacturers may show 45-75 days. Infrastructure and real estate companies have inherently high DIO due to project timelines. For Indian companies, compare DIO against the industry average and track the trend. Increasing DIO may indicate slowing demand, production inefficiency, or quality issues leading to unsold stock.

Payable Turnover and Days Payable Outstanding (DPO)

DPO shows how long the company takes to pay its suppliers. The formula is =Average_Trade_Payables/(COGS/365). While a higher DPO means better cash management from the company's perspective, excessively high DPO may strain supplier relationships or indicate cash flow stress. Under MSMED Act provisions in India, companies must pay MSME suppliers within 45 days, making DPO analysis particularly relevant for compliance monitoring.

Cash Conversion Cycle (CCC)

The CCC combines DSO, DIO, and DPO into a single metric showing the total time cash is tied up in operations. The formula is =DSO + DIO - DPO. A shorter CCC means faster cash generation from operations. Negative CCC (seen in some retail and subscription businesses) indicates the company collects from customers before paying suppliers. For Indian manufacturing companies, CCC typically ranges from 60-120 days. Reducing CCC by even 10-15 days can release significant working capital -- for a company with INR 100 crore revenue, a 15-day CCC reduction frees approximately INR 4 crore of cash.

Solvency Ratios: Measuring Long-Term Stability

Solvency ratios assess a company's ability to meet long-term obligations and survive through business cycles. These ratios are critical for credit analysis, bond evaluation, and assessing financial risk.

Debt-to-Equity Ratio

The debt-to-equity ratio is the primary leverage metric. The Excel formula is =Total_Borrowings/Shareholders_Equity. Indian regulatory frameworks set industry-specific limits -- NBFCs typically operate with D/E of 4-7x (regulated by RBI), while manufacturing companies generally target 0.5-1.5x. The SEBI-mandated reduction in promoter pledging has been a significant solvency improvement driver for Indian companies, as high promoter pledging previously amplified downside risk during market corrections.

Interest Coverage Ratio (ICR)

ICR measures how comfortably earnings cover interest payments. The formula is =EBIT/Finance_Costs. Banks typically require ICR above 2.0-2.5 for term loan approvals. An ICR below 1.0 means the company cannot cover its interest from operating profits, signaling severe financial distress. Among Indian listed companies, the median ICR has improved from 3.2 in 2020 to approximately 5.5 in 2025-26, reflecting the deleveraging trend driven by strong earnings growth and lower interest rates compared to the pre-2019 era.

Debt Service Coverage Ratio (DSCR)

DSCR is the ratio most closely watched by Indian banks and financial institutions. It considers both interest and principal repayments. The formula is =(PAT + Depreciation + Interest)/(Interest + Principal_Repayment). A DSCR above 1.5 is typically required by Indian banks for project finance, and above 1.25 for working capital term loans. DSCR below 1.0 indicates the company cannot meet its debt obligations from operating cash flows, which is an early warning of potential NPA classification.

DuPont Analysis: Decomposing Return on Equity

DuPont analysis is one of the most powerful analytical frameworks in finance, decomposing ROE into its constituent drivers to identify the source of a company's returns. The three-factor DuPont model breaks ROE into Net Profit Margin (profitability), Asset Turnover (efficiency), and Equity Multiplier (leverage). The formula is ROE = (Net Income/Revenue) x (Revenue/Total Assets) x (Total Assets/Equity).

In Excel, create separate cells for each component. Verify that multiplying the three components yields the same ROE as direct calculation (Net Income/Equity). Any small difference is a rounding indicator and validates model accuracy. Build a visual decomposition tree using shapes and connectors in Excel, showing ROE at the top branching into its three components, with trend arrows showing year-over-year direction for each component.

DuPont analysis reveals fundamentally different business models even among companies with identical ROE. An IT services company might achieve 25 percent ROE through high margins (20 percent), moderate asset turnover (1.1x), and low leverage (1.15x equity multiplier). A retail company might achieve the same 25 percent ROE through low margins (3 percent), high asset turnover (3.0x), and moderate leverage (2.8x equity multiplier). The risk profiles of these two businesses are vastly different despite identical ROE, and DuPont analysis makes this visible.

Extended Five-Factor DuPont Model

The five-factor model further decomposes margin into operating efficiency and tax burden, and separates the impact of interest expense. The formula becomes ROE = (EBIT/Revenue) x (Revenue/Assets) x (Assets/Equity) x (EBT/EBIT) x (Net Income/EBT). The fourth factor (EBT/EBIT) captures the interest burden -- how much of operating profit is consumed by debt service. The fifth factor (Net Income/EBT) captures tax efficiency. For Indian companies, the tax factor became particularly interesting after the September 2019 corporate tax rate reduction, which boosted ROE for companies that opted for the lower 25.17 percent effective rate while forgoing certain exemptions.

Building the Complete Ratio Analysis Template

The ideal ratio analysis template in Excel contains four integrated sheets. The Data Input sheet has a structured layout for entering 5 years of financial statement data with clear labels and INR formatting. The Ratio Calculation sheet computes all ratios with formulas referencing the data sheet, organized by category (liquidity, profitability, efficiency, solvency). The Dashboard sheet displays key ratios visually with trend charts, traffic light indicators, and benchmark comparisons. The Peer Comparison sheet allows side-by-side ratio comparison with 3-5 peer companies.

Use conditional formatting extensively. Highlight ratios that are better than the industry median in green, worse in red, and within 10 percent of the median in amber. Apply data bars to show relative magnitude across years. Use icon sets (arrows) to indicate year-over-year improvement or deterioration. These visual cues transform a dense ratio table into an intuitive assessment tool.

Wrap all ratio formulas in IFERROR to handle division-by-zero scenarios gracefully. For example, =IFERROR(EBIT/Finance_Costs, "N/A") prevents unsightly error displays when a company has zero finance costs. Add cell comments explaining each ratio's interpretation and typical Indian industry ranges for reference.

Frequently Asked Questions