Financial Modelling Basics for Interns: Spreading Statements, Ratios and Cash Flows

What a financial model actually is in equity research

Many students think financial modelling means memorizing keyboard shortcuts or building complicated thirty-tab workbooks full of circular references. In practical equity research, a financial model is simply a structured numerical representation of how a business operates—built so that an analyst can test how changes in volume, pricing, costs, and working capital affect earnings, cash flows, and balance-sheet health.

A concise, well-organized five-tab workbook that accurately reflects a company's unit economics is far more useful than a sprawling template you do not fully understand.

Step 1: Setting up the historical three-statement spread

Every reliable model starts with at least five years of audited historical data entered directly from the company's annual reports (typically in ₹ Crore or ₹ Million, kept consistent throughout the workbook). Never rely solely on automated exports without checking them against the actual PDF filing, because automated databases frequently lump operating expenses together or misclassify one-time items.

Keep your layout clean: place historical fiscal years (e.g., FY22 through FY26) in consecutive columns from left to right. Separate the Income Statement, Balance Sheet, and Cash Flow Statement clearly, and include an explicit balance-sheet check row (Total Assets minus Total Equity and Liabilities = 0) so any data-entry error is caught immediately.

Step 2: Building common-size and ratio schedules

Directly below or alongside your historical statements, build a common-size schedule expressing every income statement line item as a percentage of Revenue from Operations. Seeing raw-material cost move from 54.2% of revenue to 58.7% over three years immediately highlights gross-margin compression that raw rupee numbers can obscure during periods of high top-line growth.

Next, construct your core analytical ratio block across four categories:

1. Profitability & Capital Efficiency: Gross Margin %, EBITDA Margin %, EBIT Margin %, Net Profit Margin %, Return on Equity (ROE), and Return on Capital Employed (ROCE).

2. Working Capital & Activity: Inventory Days, Receivable Days, Payable Days, Net Cash Conversion Cycle, and Fixed Asset Turnover.

3. Solvency & Liquidity: Total Debt-to-Equity, Net Debt-to-EBITDA, Current Ratio, and Interest Coverage Ratio (EBIT / Finance Costs).

4. Cash Conversion Quality: Operating Cash Flow (CFO) divided by EBITDA, and Free Cash Flow (CFO minus Capital Expenditure).

Step 3: Revenue and cost driver decomposition

Rather than projecting revenue as a flat percentage growth number, break revenue down into its underlying physical or operational drivers whenever disclosures permit. For a cement or steel manufacturer, model installed capacity (in million tonnes), capacity utilization %, sales volume, and average realization per tonne. For a retail or restaurant chain, model store count, new store additions, and same-store sales growth (SSSG).

Do the same on the cost side by separating variable costs (raw materials, freight, power and fuel linked to production volume) from semi-fixed costs (employee salaries, administrative overhead, lease rentals).

Step 4: Working capital, capex and depreciation schedules

To connect projections across the three statements, build supporting schedules for working capital and fixed assets. Use historical Receivable Days, Inventory Days, and Payable Days to estimate how much incremental working capital will be absorbed as revenue changes. In the fixed-asset schedule, link opening Gross Block, annual capital expenditure additions, and depreciation rates to compute closing Net Block and annual depreciation expense.

Step 5: Valuation sensitivity tables (process over point targets)

In the final tab of your workbook, summarize peer valuation multiples (P/E, P/B, EV/EBITDA) and discounted cash flow (DCF) sensitivity tables. Instead of presenting a single point estimate as a 'true value', build a two-way sensitivity matrix showing how implied enterprise value varies across different discount rates (WACC) and terminal growth or operating margin assumptions. This keeps uncertainty visible and grounds your written research report in transparent math.