Financial reporting and MIS in Excel

In short

Financial reporting is the preparation of the statutory financial statements - balance sheet, statement of profit and loss, cash flow statement, statement of changes in equity and notes - in the form the law prescribes, mainly Schedule III of the Companies Act, 2013 read with Ind AS or AS. MIS (Management Information System) reporting is the internal cousin of the same data: monthly packs, budget-versus-actual variances, ratios, ageing and KPI dashboards that managers use to run the business, with no prescribed format. Excel is still the default tool in Indian finance teams for both - it is where the trial balance exported from Tally, SAP or Zoho Books is mapped, grouped, analysed and presented. B.Com and M.Com papers (Corporate Accounting, Financial Statement Analysis, Management Accounting), CA/CMA Inter and almost every finance placement test examine the formats, the ratios and the variance logic; the Excel build is tested in the practical round of MIS executive, financial analyst and accounts executive interviews.

Explained

Statutory financial reporting: what must be reported and in what form

• Governing law: Section 129 of the Companies Act, 2013 requires financial statements to give a true and fair view and to follow Schedule III; Section 133 notifies the accounting standards.
• Schedule III divisions: Division I for companies applying AS, Division II for companies applying Ind AS, Division III for NBFCs applying Ind AS. A student should be able to reproduce the Division I/II skeleton.
• Components (Section 2(40)): balance sheet, statement of profit and loss, cash flow statement, statement of changes in equity (Ind AS companies), notes. One Person Companies, small companies and dormant companies need not give a cash flow statement.
• Ind AS applicability (broadly): all listed companies and unlisted companies with net worth of ₹250 crore or more, plus holding/subsidiary/associate companies of such companies; others follow AS. Thresholds have been amended over time - check the current notification.
• Balance sheet grouping: Equity and liabilities (equity share capital, other equity; non-current liabilities - borrowings, provisions, deferred tax liabilities; current liabilities - borrowings, trade payables, other current liabilities, provisions) and Assets (non-current - PPE, CWIP, intangibles, investments, loans; current - inventories, trade receivables, cash and cash equivalents, other current assets). Classification is current / non-current, decided by the 12-month operating cycle test.
• Statement of profit and loss order: Revenue from operations → Other income → Total income → Expenses (cost of materials consumed, purchases of stock-in-trade, changes in inventories, employee benefits expense, finance costs, depreciation and amortisation, other expenses) → PBT → tax expense (current + deferred) → PAT → other comprehensive income (Ind AS) → EPS.
• 2021 Schedule III amendments (examined and asked): rounding off is now mandatory and uniform, and the band is set by total income (the 2021 amendment replaced "turnover" with "total income") - below ₹100 crore: hundreds/thousands/lakhs/millions; ₹100 crore and above: lakhs/millions/crores; ageing schedules for trade receivables, trade payables, CWIP and intangible assets under development; disclosure of 11 prescribed ratios with variance explanation where the change exceeds 25%; plus disclosures on benami property, struck-off companies, title deeds, crypto and loans to promoters.
• Other reporting layers: CARO 2020 (auditor's report on order matters), internal financial controls reporting under Section 143(3)(i), CSR reporting under Section 135, AOC-4 / AOC-4 XBRL filing with the MCA, and for listed companies SEBI LODR Regulation 33 quarterly results (filed within 45 days of quarter end; annual results within 60 days).

MIS reporting: purpose, types and good design

• Definition: a structured, recurring flow of financial and operating information to management for planning, control and decision making. Not governed by any Act - governed by what the management needs.
• Statutory reporting versus MIS: statutory is backward-looking, standard-format, audited, annual/quarterly and public; MIS is decision-oriented, free-format, unaudited, daily to monthly and confidential.
• Common MIS reports: P&L versus budget; flash report (quick top-line within 1-2 days of month end); cash flow and fund-flow forecast; debtors and creditors ageing; inventory ageing and stock movement; branch/product/customer/segment profitability; sales MIS (price, volume, mix); cost per unit; headcount and productivity; working-capital and CCC trend; expense analysis against last year.
• Frequency tiers: daily (collections, sales, cash balance), weekly (production, order book, overdues), monthly (full pack), quarterly (results, board pack), annual (budget, statutory).
• Characteristics of good MIS: relevant, accurate, timely (an approximate report on the 3rd beats a perfect one on the 25th), consistent period to period, exception-focused, comparable (actual versus budget versus last year), and traceable to the ledger.
• Management accounting techniques carried into MIS: responsibility accounting (cost, profit and investment centres), marginal costing and contribution, flexible budgets, standard costing variances, common-size and trend analysis.

Building the reporting model in Excel: a four-layer design

1. Data layer: raw trial balance / ledger dumps exported from Tally Prime, SAP, Oracle, Zoho or Busy - imported, never typed, and never edited in place. One sheet per source, kept as an Excel Table so ranges grow automatically.
2. Mapping layer: a master grouping sheet linking every ledger code to a reporting line (ledger → group → Schedule III head → MIS head). This is the heart of the model: one mapping table drives every report, so a new ledger only has to be classified once.
3. Calculation layer: aggregation by SUMIFS on the mapped columns, or a pivot / data model; all comparatives (budget, last year, last month) pulled by the same keys - entity, cost centre, period, line code.
4. Presentation layer: clean report sheets and dashboards that contain only references and formatting, no data entry and no hard-coded numbers.
5. Power Query (Get & Transform) is the right tool for repeat imports: append twelve monthly files, unpivot a wide budget sheet, trim and clean codes, then refresh next month with one click.
6. Power Pivot / data model with relationships and DAX measures handles multi-lakh-row ledgers that would choke worksheet formulas.
7. Dynamic references - named ranges, structured table references, OFFSET/INDEX or dynamic array spills - keep the pack alive when rows are added.
8. Golden rule: inputs, workings and outputs are separated and colour-coded (for example blue for inputs, black for formulas, green for links to other sheets).

Analysis built into the pack

• Variance analysis: actual versus budget and versus last year, in rupees and in per cent, each flagged favourable or adverse; revenue variances split into price and volume (and mix where there are multiple products).
• Flexible budget: re-state the budget at actual volume before judging cost performance, otherwise a volume fall looks like cost control.
• Common-size (vertical) analysis: every P&L line as a per cent of revenue; every balance sheet line as a per cent of total assets - makes periods and companies comparable.
• Horizontal / trend analysis: index each line to a base year, or show 12-month moving averages and growth rates.
• Ratio analysis: liquidity, solvency, activity/turnover and profitability ratios, plus the DuPont split of ROE into margin × asset turnover × leverage.
• Working capital analysis: DSO, DIO, DPO and the cash conversion cycle; ageing buckets (0-30, 31-60, 61-90, 91-180, above 180 days) built with SUMIFS on date differences.
• Exception reporting: conditional formatting or filters that surface only the items breaching a threshold - overdues above 90 days, variance above 10%, negative margins.
• Charts for management: waterfall for a profit bridge, stacked column for revenue mix, line for trend, combo (bar + line) for value and margin together, sparklines inside tables, and KPI cards. Keep one message per chart; avoid 3-D and dual axes that mislead.

Controls, accuracy and governance

• Tie-outs (the non-negotiable check): total debits = total credits in the imported TB; mapped total = TB total; MIS PAT = ledger PAT; closing cash in the cash flow statement = balance sheet cash. Build each as a visible check cell showing OK or a difference.
• Unmapped ledger check: a count of ledger codes not present in the mapping table - must be zero before the pack is issued.
• Data validation drop-downs for period, entity and cost centre; sheet and workbook protection to lock formulas; IFERROR only after the cause of the error is understood, never to hide it.
• No hard-coded plugs: a figure typed over a formula to make a total agree is the classic audit finding. Every number is either an input in a seeded input sheet or a derived value.
• Avoid volatile and fragile constructs: whole-column array formulas, INDIRECT chains, merged cells, manual calculation left on, and external links to files that move.
• Version control and audit trail: dated file names or a revisions sheet, a stated "as on" date and basis of preparation, and sign-off by preparer and reviewer. Note the legal limit: Rule 3(1) of the Companies (Accounts) Rules requires accounting software to maintain an edit log / audit trail (mandatory from 1 April 2023), which a plain Excel workbook does not provide - so Excel is a reporting and analysis layer, not the books of account.
• Rounding discipline: present in ₹ lakh or ₹ crore using number formatting, not by dividing and retyping; disclose the unit in the report header.

Automation, delivery and when to move beyond Excel

• One-click refresh: Power Query + pivot refresh, or a short macro that refreshes, updates the period parameter, re-paginates and exports to PDF.
• Distribution: PDF for the board pack, protected workbook for reviewers, a shared OneDrive/SharePoint link for live viewing; schedule reminders rather than emailing stale copies.
• Excel's limits: row limit of 1,048,576 per sheet, single-user editing, no row-level security, no enforced audit trail, and version sprawl.
• When to graduate: Power BI or Tableau for refreshable visual dashboards and row-level security; an ERP's report writer for statutory-grade output; SQL/Power Query staging for large volumes. Many Indian SMEs run Tally for books plus Excel for MIS; mid-size companies run SAP/Oracle plus Power BI, with Excel still used for reconciliation and ad-hoc analysis.

Key points

• Financial reporting - statutory, standard-format, audited reporting to outsiders; MIS - internal, flexible, decision-focused reporting to management.
• Schedule III, Companies Act 2013 - prescribes the balance sheet and P&L format; Division I for AS, Division II for Ind AS, Division III for Ind AS NBFCs.
• Current/non-current classification - decided by the 12-month operating cycle test, not by the nature of the item alone.
• Ratio disclosure - Schedule III now requires 11 named ratios with explanation when the change from the previous year exceeds 25%.
• Ageing schedules - trade receivables, trade payables, CWIP and intangibles under development must be shown in prescribed age buckets.
• Rounding off - mandatory and uniform; the band depends on whether total income (not turnover, after the 2021 amendment) is below or at least ₹100 crore.
• Four-layer model - raw data, mapping, calculation, then presentation; data is imported, never typed.
• Mapping table - one ledger-to-reporting-line master drives the entire pack; a new ledger is classified once.
• Power Query - repeatable import, clean and append; Power Pivot/DAX - large volumes and reusable measures.
• Variance - actual minus budget; for income a positive variance is favourable, for expenses a negative (lower actual) is favourable.
• Flexible budget - restate budget at actual volume before judging cost efficiency.
• Common-size statement - lines as a per cent of revenue (P&L) or of total assets (balance sheet).
• Tie-out checks - TB balances, mapped total equals TB total, cash flow closing cash equals balance sheet cash; shown on the face of the pack.
• Unmapped ledgers must be zero - the single most common cause of a wrong MIS total.
• Flash report - indicative top-line within a day or two of month end; accuracy traded for speed, and labelled as such.
• Cash conversion cycle - DSO + DIO − DPO; the working-capital headline of any MIS pack.
• No hard-coding - never type over a formula to force a total to agree.
• Excel is not the books of account - the audit-trail requirement under the Companies (Accounts) Rules applies to accounting software.

Formulas and examples

Typical formula patterns in the calculation layer:

Reporting line value   =SUMIFS(TB[ClosingBal], TB[MISLine], $B5, TB[Period], $D$2)
Unmapped ledger count  =SUMPRODUCT(--(COUNTIF(Map[Ledger], TB[Ledger])=0))
TB tie-out check       =IF(ROUND(SUM(TB[Debit])-SUM(TB[Credit]),2)=0,"OK","DIFF")
Variance %             =IFERROR((Actual-Budget)/ABS(Budget),"n.a.")
Ageing bucket (61-90)  =SUMIFS(AR[Amt], AR[Days],">60", AR[Days],"<=90")

Example 1 - Trial balance to a Schedule III statement of profit and loss (₹ lakh)

• Material cost consumed net of inventory change = 640 − 20 = 620; Gross profit = 1,200 − 620 = 580, GP% = 580/1,200 = 48.33%.
• Operating EBITDA = 1,200 − 620 − 180 − 190 = 210; EBITDA margin = 210/1,200 = 17.5% (including other income it is 225, so state the basis).
• EBIT = 210 + 15 − 60 = 165; PBT = 165 − 35 = 130; PAT = 130 − 33 = 97; net profit ratio = 97/1,200 = 8.08%; effective tax rate = 33/130 = 25.4%.
• Check: total expenses 620 + 180 + 190 + 60 + 35 = 1,085, and 1,215 − 1,085 = 130 - the tie-out agrees.

Example 2 - Budget versus actual MIS (₹ lakh)

• Material cost as % of revenue: budget 650/1,250 = 52.0%, actual 620/1,200 = 51.67% - so the ₹30 lakh saving is partly volume-driven, with a genuine 0.33 percentage-point efficiency gain.
• EBITDA margin fell from 240/1,250 = 19.2% to 210/1,200 = 17.5%, mainly because fixed-nature employee and other costs did not fall with revenue - the comment an MIS pack must carry.

Example 3 - Price and volume split of the revenue shortfall Budget: 50,000 units at ₹2,500 = ₹1,250 lakh. Actual: 48,000 units at ₹2,450 = ₹1,176 lakh.

• Volume variance = (48,000 − 50,000) × 2,500 = −₹50,00,000 = (₹50 lakh)
• Price variance = (2,450 − 2,500) × 48,000 = −₹24,00,000 = (₹24 lakh)
• Total = (₹74 lakh), and 1,250 − 1,176 = 74 - the bridge reconciles.

Example 4 - Ratio block from the same company (balance sheet, ₹ lakh) Inventories 150, trade receivables 200, cash 40, other current assets 10 (current assets 400); trade payables 160, other current liabilities 40 (current liabilities 200); equity 500, non-current borrowings 300. Revenue 1,200 (all credit), COGS 620, EBIT 165, finance costs 35.

Example 5 - Cash flow statement, indirect method (AS 3 / Ind AS 7), ₹ lakh

• PBT 130; add back depreciation 60 and finance costs 35 → operating profit before working capital changes = 225.
• Working capital: receivables up 25 (−25), inventories up 20 (−20), payables up 15 (+15) → 195; less income tax paid 30 → CFO = 165.
• Investing: purchase of PPE (90) → CFI = (90).
• Financing: repayment of borrowings (40), interest paid (35), dividend paid (10) → CFF = (85).
• Net decrease in cash = 165 − 90 − 85 = (10); opening cash 50 → closing cash 40, which equals the balance sheet figure - the mandatory tie-out.

Where it is used

• Industry finance and FP&A teams: monthly MIS packs, budget versus actual commentary and board decks in manufacturing (Tata, Mahindra, Asian Paints), FMCG, pharma and services; branch and product P&Ls built in Excel from ERP extracts.
• Banks, NBFCs and insurers: branch profitability, deposit and advance mix, NPA ageing and regulatory return workings, built on data-model-driven Excel before ADF/XBRL submission.
• CA firms, consulting and shared-service centres: Schedule III financial statement preparation, consolidation, XBRL and AOC-4 workings, agreed-procedure reports for clients (Big 4 and mid-size firms hire heavily for exactly this skill).
• Start-ups and SMEs: Tally Prime or Zoho Books for books plus an Excel MIS - runway, burn, unit economics, collections and investor reporting.
• Retail, e-commerce and hospitality: daily sales and cash reports, store-wise contribution, stock ageing dashboards refreshed from POS extracts.
• Job roles: MIS executive, financial analyst, FP&A analyst, accounts executive, audit associate, business analyst - the Excel reporting test is usually the screening round.

Interview questions

• Q: How does MIS reporting differ from statutory financial reporting? A: Statutory reporting follows a prescribed format under Schedule III and the accounting standards, is audited and is meant for outsiders; MIS is internal, flexibly formatted, unaudited and built to support decisions.
• Q: What does Schedule III of the Companies Act, 2013 govern, and what are its divisions? A: It prescribes the format and disclosures for the balance sheet and statement of profit and loss - Division I for AS companies, Division II for Ind AS companies and Division III for Ind AS NBFCs.
• Q: How do you decide whether an asset is current or non-current? A: By the 12-month / operating cycle test - an asset expected to be realised, sold or consumed within the operating cycle (or 12 months if the cycle is not identifiable) is current.
• Q: How would you build a monthly P&L in Excel from a Tally trial balance? A: Export the TB, keep it as a table, map every ledger to a reporting line in a master mapping sheet, aggregate with SUMIFS or a pivot, and add tie-out checks that the mapped total equals the TB total and that debits equal credits.
• Q: Your MIS P&L does not match the books. How do you find the error? A: Check the TB tie-out, then the count of unmapped ledgers, then double-mapped ledgers, then the period filter and sign convention of income versus expense ledgers - in that order.
• Q: Is a favourable variance always good news? A: No - a favourable cost variance may simply reflect lower volume, deferred spending or an unbooked expense, so costs must be compared against a flexible budget and against percentage-of-revenue norms.
• Q: What is a flexible budget and why does MIS need it? A: A budget restated at actual activity level, so that variable costs are compared on a like-for-like basis and volume effects are separated from efficiency effects.
• Q: Which Excel feature would you use for a 12-month MIS built from twelve monthly files? A: Power Query to append and clean the files into one table, with a pivot or Power Pivot measures on top so next month's pack needs only a refresh.
• Q: How do you split a revenue shortfall into price and volume? A: Volume variance = (actual quantity − budget quantity) × budget price; price variance = (actual price − budget price) × actual quantity; with multiple products add a mix variance, and the three must sum to the total gap.
• Q: Which checks do you put on the face of an MIS pack? A: TB debit-credit equality, mapped total versus TB total, zero unmapped ledgers, PAT agreeing with the ledger, cash flow closing cash agreeing with the balance sheet, and the "as on" date with the unit of rounding.
• Q: Can a company keep its books of account in Excel? A: It is not appropriate - accounting software must maintain an edit log / audit trail under the Companies (Accounts) Rules (mandatory from 1 April 2023); Excel should sit on top of accounting software as a reporting and analysis layer.
• Q: When would you move the MIS out of Excel? A: When data volume approaches Excel's row limit, when multiple users need concurrent access or row-level security, when a refreshable visual dashboard is required, or when an audit trail of changes is needed - then Power BI or the ERP report writer.

Common mistakes

• Hard-coding or typing a plug figure over a formula to force a total to agree, instead of finding the mapping error.
• Issuing the pack without tie-out checks - no TB balance check, no unmapped ledger count, cash flow closing cash not reconciled to the balance sheet.
• Mixing up the variance sign convention, so lower actual expenditure is reported as adverse and a cost overrun as favourable.
• Comparing actuals to a fixed budget when volumes have changed, and calling the resulting cost variance efficiency.
• Dividing figures by 100,000 and retyping them "in lakh" instead of using number formatting - which breaks drill-down and rounding consistency.
• Presenting a balance sheet or P&L in a self-invented order and heads rather than Schedule III sequence, and omitting comparatives for the previous year.
• Burying errors under IFERROR, leaving the workbook on manual calculation, or building on merged cells, volatile INDIRECT chains and broken external links.

Related topics

Excel formulas: lookup, logic and pivot tables · Month-end close and reconciliations · Tally Prime basics · Accounts payable and receivable · Financial statement and ratio analysis · Cost and management accounting: budgeting and variance analysis