Reconciliation Process Design Worksheet
Walk any reconciliation function through the seven-step design method: pick the failure mode from the 12-class taxonomy, trace the cause to one of the 6P categories (People, Policy, Process, Portal, Period, Partner), rate Severity, Occurrence and Detection on 1–10 scales anchored to Indian tax and audit consequences (Section 16(4) permanent ITC loss, Section 200A demand, DRC-01B, Section 40(a)(ia) disallowance), and let the built-in Severity-first lookup compute the Action Priority and flag the rows that have out-grown a manual control-plan fix. Twenty-plus failure mode rows per session, downloadable JSON output, no login.
Illustrative — anchor the Severity, Occurrence and Detection scales to your organisation's actual reconciliation consequences, its actual transaction volume, and its actual detection controls. The 1–10 anchors shown as tooltips are worked defaults calibrated to Indian statutory-reconciliation exposure (Section 16(4) ITC time bar, Section 200A demand, DRC-01B mismatch, Section 40(a)(ia) disallowance, CARO 2020 bank reconciliation qualification), not universal weightings. This worksheet is a design-defensibility artefact for the Internal Financial Controls over Financial Reporting walk-through under Section 143(3)(i) of the Companies Act 2013; it does not constitute tax, audit or legal advice. A downloadable Excel version of this worksheet is being finalised for the Assets Catalog Tier 1 release — the browser worksheet already supports JSON download for archival and audit sharing.
Failure mode ledger
Every failure mode with class, cause, effect, S / O / D scores, computed Action Priority (H / M / L) and any warning on detection-control depth. Remove any row entered in error.
| Function | Failure mode | Class | Cause (6P) | Effect | S | O | D | AP | Prevention | Detection | Owner | Cadence | Warnings | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| No failure modes added. Add a row above to start the design walk-through. | ||||||||||||||
12-class failure taxonomy — reference
| Class | Definition |
|---|---|
| Data extraction | Source data pulled incorrectly from an ERP, portal, statement or file (wrong period, wrong entity, wrong ledger, wrong field mapping). |
| Data classification | Correctly-extracted data assigned to the wrong statutory bucket (wrong TDS Section 393 code, wrong GST HSN, wrong nature of payment, wrong ledger head). |
| Data completeness | Missing rows or missing fields on either side of the reconciliation (unbooked invoices, missing UTRs, blank counterparty). |
| Matching logic | Correct data on both sides but the matching rule fails (rounding, prefix, case, whitespace, UTR versus reference number). |
| Timing and period | Correctly-extracted correctly-classified data assigned to the wrong period (cutoff drift, month-end lag, financial-year boundary). |
| Counterparty or partner | Counterparty master data drift (renamed vendor, PAN change, GSTIN change, bank change) that breaks the previously-working matching rule. |
| Precision and numeric | Rounding, currency conversion, tax-inclusive versus tax-exclusive, gross versus net, part-payment allocation error. |
| Policy and interpretation | Correctly-processed data whose treatment depends on a policy judgment that has drifted (revenue recognition, ITC eligibility, blocked-credit interpretation). |
| Aging and escalation | Exception surfaced but not aged, not escalated, not owned; sits open past the statute-anchored deadline (Section 16(4), Rule 37A, DRC-01B response window). |
| Cutoff and sign-off | Reconciliation prepared but not independently reviewed and signed off before the period-close deadline; ICFR walk-through has no evidenced sign-off. |
| Documentation and evidence | Reconciliation exists but the working paper, exception log, adjusting entry rationale or auditor-defensible trace is missing. |
| Portal and system | Failure originates in the external portal or system (GST portal, TRACES, bank net-banking, payment gateway, ERP module) rather than in the finance team's own workflow. |
6P cause taxonomy — reference
| Cause category | Scope |
|---|---|
| People | Preparer skill, reviewer bandwidth, handover gap, leave coverage, key-person dependency. |
| Policy | Accounting policy interpretation, tax position, ITC eligibility judgment, revenue recognition rule. |
| Process | Reconciliation SOP design, cutoff protocol, escalation matrix, sign-off cadence, aging protocol. |
| Portal | GST portal downtime, TRACES lag, bank portal format change, ERP master data drift, payment gateway API bump. |
| Period | Section 16(4) November 30 cutoff, GSTR-3B monthly cutoff, tax audit cutoff, GSTR-9 December 31 cutoff, financial-year boundary. |
| Partner | Customer, vendor, deductor, deductee, bank, payment gateway, marketplace, government portal counterparty. |
Action Priority lookup — Severity-first
| Rule | Action Priority |
|---|---|
| Severity 9–10 (Section 16(4) permanent ITC loss, Section 40(a)(ia) disallowance, Section 200A demand) — regardless of Occurrence and Detection | H |
| Severity 7–8 (DRC-01B, DRC-01C, material portal-mismatch exposure) with Occurrence 5+ (quarterly or more frequent) | H |
| Severity 7–8 with Occurrence 4 or less (annual or lower frequency) | M |
| Severity 5–6 (ledger exceptions caught at year-end) with Occurrence 5+ OR Detection 7+ (weak or absent detection) | M |
| Severity 5–6 otherwise (low frequency and good detection) | L |
| Severity 4 or less (immaterial rounding, cosmetic exceptions) — unless Occurrence = 10 (majority of transactions), then escalate one band | L |
Warning flags: a High Action Priority row whose Detection control text does not contain "multi-layer", "aging queue", or "system-enforced" is flagged as under-controlled — the failure mode has out-grown a manual preparer-plus-reviewer depth. A Medium Action Priority row whose Detection control text does not contain "ratio" or "reasonableness" is flagged for a numeric-detection gap. Both flags mean the same thing operationally: the reconciliation function needs continuous, system-enforced detection with automated aging and escalation, not a spreadsheet-and-reviewer workflow.
About this worksheet
This worksheet operationalises the reconciliation process design framework — a seven-step interactive method for structured design review over any reconciliation function an Indian finance team owns. Step one is to scope the reconciliation function and name its statutory consequence: an invoice-to-bank reconciliation whose failure lands as an unrecovered receivable is a different exposure profile from a GSTR-2B ITC reconciliation whose failure lands as a Section 16(4) permanent ITC loss on the November 30 deadline. Step two is to identify the failure modes across the fixed 12-class taxonomy: Data extraction, Data classification, Data completeness, Matching logic, Timing and period, Counterparty or partner, Precision and numeric, Policy and interpretation, Aging and escalation, Cutoff and sign-off, Documentation and evidence, and Portal and system. Step three is to trace each failure mode to a root cause across the 6P cause categories — People, Policy, Process, Portal, Period, Partner — because the corresponding prevention control follows from the cause category (a Policy cause is corrected by a written interpretation note; a Portal cause is corrected by a fallback data-capture protocol; a Period cause is corrected by a calendar-linked escalation trigger).
Steps four through six rate each failure mode on the classical Severity, Occurrence and Detection dimensions on a 1–10 scale — but the scales themselves are anchored to Indian statutory-reconciliation consequences, not to universal manufacturing scales. Severity 10 anchors on Section 16(4) permanent ITC loss or Section 40(a)(ia) expense disallowance in the assessment order. Severity 9 anchors on a Section 200A demand notice with interest under Section 201(1A) and late-filing fee under Section 234E. Severity 8 anchors on a DRC-01B or DRC-01C mismatch notice from the GST department. Severity 5–6 anchors on ledger exceptions caught at year-end. Occurrence anchors run from 10 (majority of transactions) through 8–9 (monthly) through 6–7 (quarterly) through 4–5 (annual) through 2–3 (policy-prevented) to 1 (impossible). Detection anchors run from 10 (no detection control) through 8–9 (manual preparer review) through 6–7 (independent peer review, sample-based) through 4–5 (peer review with ratio or reasonableness test) through 2–3 (multi-layer including aging queue with escalation triggers) to 1 (system-enforced rule that prevents the transaction from completing).
Step seven computes the Action Priority (H / M / L) via a Severity-first lookup: Severity 9–10 is always High regardless of Occurrence and Detection, Severity 7–8 is High when Occurrence is quarterly or more frequent and Medium otherwise, Severity 5–6 is Medium when Occurrence is high or Detection is weak and Low otherwise, and the residual Severity 4-and-under population is Low unless Occurrence is at the ceiling of 10. The Severity-first principle is intentional: Indian statutory reconciliation carries several failure modes whose statutory consequence is entirely disproportionate to their frequency, and a classical multiplicative product would demote the November-30 Section 16(4) exposure below a monthly cosmetic ledger exception with no detection. Step seven also flags rows whose detection depth is inadequate for the assigned Action Priority — the warning that the failure mode has out-grown a spreadsheet-and-reviewer workflow and now needs continuous, system-enforced detection with automated aging queues and escalation triggers. The worksheet is designed to be re-scored annually as part of the Section 143(3)(i) ICFR walk-through, quarterly for any function with a High row, and out-of-cycle on statute change, portal change, audit observation, ERP migration, or leadership change.
Related
Reconciliation failure mode analysis in India
The full seven-step design method, the 12-class failure taxonomy, the 6P cause categories and the Severity-first Action Priority lookup.
GSTR-2B ITC reconciliation failure modes
The design-review walk-through applied to the Section 16(4) November 30 exposure, Rule 37/37A reversals and IMS.
TDS Form 26AS reconciliation failure modes
The design-review walk-through applied to Section 393 payment codes, cross-era 194x codes, Form 168 and Section 200A exposure.
Reconciliation control plan template
The audit-defensible control-plan output that follows from a completed worksheet: prevention, detection, owner, cadence, ICFR trace.
Operationalise the reconciliation function
If the worksheet is surfacing High rows without the multi-layer detection to match, talk to us about production reconciliation infrastructure.
Frequently Asked Questions
What is the reconciliation process design framework, and what problem does it solve for an Indian finance team? +
The reconciliation process design framework is a seven-step interactive method for a finance team to run a structured design review over any reconciliation function it owns: invoice-to-bank, TDS Form 26AS to Form 168, GSTR-1 versus GSTR-3B outward liability, GSTR-2B input tax credit, vendor ledger, customer ledger, intercompany, cash-flow, fixed-asset register, or any custom reconciliation. The seven steps are: (1) scope the reconciliation function and its statutory consequence, (2) identify failure modes across a fixed 12-class taxonomy, (3) trace each failure mode to a root cause across the 6P cause categories, (4) rate Severity on a 1-10 scale anchored to Indian tax and audit consequences (Section 16(4) permanent ITC loss at 10, Section 200A demand at 9, DRC-01B mismatch notice at 8, ledger exceptions caught at year-end at 5-6), (5) rate Occurrence on a 1-10 scale anchored to frequency (majority of transactions at 10, monthly at 8-9, quarterly at 6-7, annual at 4-5, policy-prevented at 2-3, impossible at 1), (6) rate Detection on a 1-10 scale anchored to control depth (no detection control at 10, manual preparer review at 8-9, independent peer review sample-based at 6-7, peer review with ratio or reasonableness test at 4-5, multi-layer including aging queue with escalation at 2-3, system-enforced rule at 1), and (7) compute the Action Priority (H / M / L) via a Severity-first lookup and produce a control plan naming the prevention control, the detection control, the owner and the cadence. The problem the framework solves is that most Indian finance teams live inside a reconciliation function whose failure modes have never been enumerated, whose statutory consequence of failure has never been quantified, and whose detection controls have never been rated against the true frequency of occurrence. When the auditor asks under Section 143(3)(i) of the Companies Act 2013 how the Internal Financial Controls over Financial Reporting design has been tested for the bank reconciliation, or for the GSTR-2B reconciliation, or for the TDS reconciliation, this worksheet is the design-defensibility artefact that answers the question.
Why is Severity dominant in the Action Priority lookup, rather than the classical Severity times Occurrence times Detection product? +
In an Indian statutory reconciliation context, some failure modes have consequences that are entirely disproportionate to their frequency or to the ease of detecting them once they have occurred. A single missed Rule 37A ITC reversal past the Section 16(4) November 30 time bar permanently loses the Input Tax Credit for the entire financial year even if it is a first-time occurrence and even if a manual reviewer would in principle have caught it in a full year-end walk-through. A single Form 168 TDS deposit against the wrong Section 393 payment code (post-April-2026) triggers a Section 200A demand notice with interest under Section 201(1A) and late-filing fee under Section 234E even if the vendor is a first-time payee. A single wrong classification of a payment as under Section 194J when it is a Section 194C works-contract payment can drive a Section 40(a)(ia) expense disallowance in the assessment order. Multiplying Severity by Occurrence by Detection dilutes these consequences: a Severity 10 failure that occurs once a year (Occurrence 4) and would be caught by an independent peer review (Detection 5) produces a product score of 200 that ranks below a Severity 5 failure that occurs monthly (Occurrence 8) with no detection control (Detection 10) at product 400 — even though the Severity 10 failure irreversibly loses ITC while the Severity 5 failure is a working-capital exception recoverable in the next cycle. The Severity-first lookup fixes this: Severity 9-10 is High Action Priority regardless of Occurrence and Detection, Severity 7-8 with monthly-or-more Occurrence is High, Severity 5-6 with material Occurrence or weak Detection is Medium, and the residual population defaults to Low. The Action Priority column therefore ranks the reconciliation function's failure modes by the operational reality of Indian tax and audit exposure, not by an arithmetic average.
How is the 6P cause taxonomy different from the manufacturing 4M, and why was it built for reconciliation specifically? +
The manufacturing 4M cause taxonomy (Man, Machine, Method, Material) is the traditional fishbone-diagram categorisation of root causes on a factory shop-floor and is designed for physical-process defects: a misaligned tool, a wrong operator training, a raw-material batch out of specification. Reconciliation is a paperwork-process activity where the causes are structurally different. The 6P cause taxonomy (People, Policy, Process, Portal, Period, Partner) reflects the six actual cause categories that appear in reconciliation post-mortems across Indian finance functions: People (preparer skill, reviewer bandwidth, handover gaps, leave coverage), Policy (accounting policy interpretation, tax position, revenue recognition rule, ITC eligibility judgment), Process (reconciliation SOP design, cutoff protocol, escalation matrix, sign-off cadence), Portal (GST portal downtime, TRACES portal lag, bank portal statement format change, ERP master data drift), Period (financial year cutoff, quarter-close cutoff, GSTR-3B monthly cutoff, tax audit cutoff, Section 16(4) November 30 cutoff, GSTR-9 December 31 cutoff, Section 194-IA TDS deadline), and Partner (customer, vendor, deductor, deductee, bank, payment gateway, marketplace, government portal counterparty). Portal and Period in particular have no analogue in the manufacturing 4M — Portal captures the exogenous dependency on regulatory and banking IT systems, and Period captures the calendar-driven statute-of-limitation dimension that dominates Indian reconciliation. Naming the cause category correctly is what drives the corresponding prevention control: a People cause is corrected by training or by role redesign, a Policy cause is corrected by written interpretation notes, a Process cause is corrected by SOP rewrite, a Portal cause is corrected by fallback data-capture protocol, a Period cause is corrected by a calendar-linked escalation trigger, and a Partner cause is corrected by counterparty onboarding and periodic master-data confirmation.
How often should the reconciliation process design worksheet be re-scored, and what triggers a re-run outside the regular cadence? +
The worksheet should be re-scored once a year as part of the year-end Internal Financial Controls over Financial Reporting design walk-through, and additionally once a quarter for any reconciliation function whose Action Priority column contains one or more High-rated failure modes. Beyond the regular cadence, five events trigger an out-of-cycle re-run: (a) a statute change — Section 393 TDS payment codes replaced the legacy 194x codes from April 2026 and every TDS reconciliation function required a full re-score, the Income Tax Act 2025 replaced the 1961 Act and every direct-tax reconciliation function required a full re-score, and periodic GST rule amendments (Rule 36(4) ITC ceiling revisions, Rule 37 and Rule 37A ITC reversal timings, Section 17(5) blocked-ITC list updates) trigger a re-score of the affected GST reconciliation function, (b) a portal change — GSTR-2B format changes, the Invoice Management System (IMS) rollout, TRACES Form 168 replacing Form 26AS for post-April-2026 periods, bank statement format changes at ICICI, HDFC, SBI, Axis, or a payment gateway API version bump, (c) an audit observation — a statutory audit qualification, an internal audit finding, a tax audit Section 143(3)(i) ICFR design deficiency, a CARO 2020 Clause 3(ii)(b) bank reconciliation qualification, or a DRC-01B / DRC-01C mismatch notice from the GST department, (d) an ERP or system migration — SAP S/4HANA cutover, Oracle Fusion upgrade, Tally Prime version change, Zoho Books configuration change, or a new bank integration, and (e) a leadership change — new CFO, new Head of Finance, new statutory auditor, or new tax advisor whose interpretation of a Policy-category cause may differ from the incumbent's. The worksheet is meant to be a living design artefact, not a one-time exercise.
When does the reconciliation process design worksheet surface the need for dedicated reconciliation infrastructure rather than a manual control-plan fix? +
The worksheet surfaces the infrastructure need when a High Action Priority failure mode carries a warning flag from the built-in lookup: warning one flags a High-priority row whose detection control is not multi-layer, does not include an aging queue with escalation, and is not a system-enforced rule; warning two flags a Medium-priority row whose detection control has no ratio or reasonableness test. Both warnings mean the same thing operationally — the reconciliation function has out-grown a manual preparer-plus-reviewer detection depth and a spreadsheet-based aging tracker will not catch the failure mode at the frequency at which it now occurs. Three concrete triggers appear repeatedly in customer walk-throughs: (i) the GSTR-2B ITC reconciliation for a mid-size business processing tens of thousands of invoices per month cannot be manually walked to Section 16(4) November 30 defensibility — the at-risk ITC queue needs to be a first-class output with an automated aging queue and escalation triggers, (ii) the invoice-to-bank reconciliation for a business processing thousands of bank credits per month cannot be manually walked to Rule 37A or to advance-versus-receipt classification — an automated, continuously-refreshed detection layer is required, and (iii) the Form 168 TDS reconciliation across post-April-2026 Section 393 codes plus legacy 194x codes for pre-April-2026 periods carries a cross-era statute-code mapping burden that is impractical to hold in a manual preparer's head. TransactIG is the production reconciliation platform that operationalises the at-risk ITC queue, the automated aging queue with escalation triggers, and the continuously-refreshed detection layer as customer-benefit outputs. ISO 27001:2022, AWS Mumbai, implementation two to four weeks. The worksheet is the diagnostic; the platform is the treatment.
From design walk-through to production reconciliation infrastructure
TransactIG operationalises the at-risk ITC queue as a first-class output, the automated aging queue with escalation triggers, and the continuously-refreshed detection layer that the worksheet flags as inadequate for a manual preparer-plus-reviewer workflow. ISO 27001:2022, AWS Mumbai, implementation two to four weeks.