AR register upload → six-bucket aging + three DSO methods — accounts receivable aging report in under 30 seconds per register.
| Invoice No. | Customer | Due Date | Outstanding (Rs) | Terms | Bucket |
|---|---|---|---|---|---|
| INV-2026-0142 | Acme Corp Ltd | 27 Jun 2026 | 4,50,000 | Net 30 | Current |
| INV-2026-0098 | Acme Corp Ltd | 08 Apr 2026 | 2,85,000 | Net 45 | 61–90 days |
| INV-2026-0156 | Acme Corp Ltd | 14 May 2026 | 2,50,000* | Net 30 | ⚠ Mismatch |
| INV-2025-0871 | Acme Corp Pvt Ltd | 04 Nov 2025 | 1,75,000 | Net 30 | 180+ Risk |
Mid-market finance teams carrying 50–500 open customer invoices spend two to four hours at every month-end close on what should be arithmetic: computing Days Sales Outstanding, placing every invoice into an aging bucket, and checking that outstanding balances are not silently misstated. The workflow that replaces this is not glamorous — it is the unglamorous kind that prevents a statutory audit adjustment.
Exporting the AR subledger, writing TODAY()−due_date formulas, repairing broken date references, and building the bucket pivot table from scratch every cycle is pure spreadsheet maintenance. A 300-row register in Tally Prime or Zoho Books can take a senior analyst three hours to age correctly when terms are heterogeneous (Net 30, Net 45, milestone-based) and prior-month formulas carry over stale cell references.
Partial payments posted incorrectly in the ERP mean outstanding ≠ invoice_amount − amount_paid. A single Rs 30,000 overstatement on one invoice inflates the total AR balance by the same amount — enough to push simple DSO across a covenant threshold or distort the Ind AS 109 Expected Credit Loss provision matrix before the number reaches the CFO or statutory auditor.
Collection-risk invoices sit in the AR register for months without a formal flag when the aging exercise is manual and exception thresholds are not enforced consistently. Balances beyond 180 days past due have a materially lower recovery probability and, if left unactioned, produce bad-debt write-off surprises at statutory audit — exactly the scenario that generates an ICAI SA 505 External Confirmations request and a customer-balance dispute that was avoidable.
Simple DSO (AR balance ÷ daily revenue) inflates the metric during low-revenue months and suppresses it during peak months. For seasonal manufacturing or FMCG distribution businesses, count-back DSO — which consumes the most recent revenue tranches until the outstanding balance is exhausted — is more operationally accurate. Without a system enforcing method consistency, the DSO number changes character between periods depending on which formula an analyst happened to reach for.
The typical controller time consumed by a manual AR aging bucket report at month-end on a mid-market register of 100–500 invoices — before accounting for the re-work when a late payment posting invalidates the export. The same computation runs in under 30 seconds on Cadel, with deterministic bucket assignment, three DSO methods, and an exception log every auditor can trace back to specific invoice numbers under ICAI SA 230.
An aging bucket report is not a management convenience — it is a direct input to statutory disclosures, ECL provisioning, covenant reporting, and income-tax compliance. Four overlapping standards mandate it, each from a different angle.
Under Ind AS 107 (Financial Instruments: Disclosures), entities must disclose an aging analysis of financial assets that are past due but not impaired and those that are impaired, broken into the same aging bands the workflow produces. The six-bucket structure — Current, 1–30, 31–60, 61–90, 91–180, 180+ — maps directly to what the statutory auditor expects in the notes to financial statements.
The MCA Schedule III amendment effective FY 2021–22 made it mandatory for Indian companies to disclose outstanding dues from trade debtors in bucket-wise format in the notes to financial statements. Companies that present a single “Total AR” without aging banding now face a qualified observation from their statutory auditor under ICAI SA 700 (Forming an Opinion).
Under Ind AS 109, the Expected Credit Loss provision for trade receivables is calibrated using a provision matrix keyed to the age distribution of the AR book. The probability-of-default assumption changes materially across the six buckets: a Current invoice carries near-zero ECL while a 180+ invoice in a distressed sector may require a 50–100% provision. Incorrect bucket assignment directly understates the ECL charge and the associated provision balance.
Under US GAAP ASC 310-10-50, entities are required to disclose an aging analysis of past-due financing receivables by class. For companies reporting under US GAAP — including Indian subsidiaries of US parents or cross-listed entities — the same six-bucket aging structure satisfies the PCAOB and SEC disclosure expectations, and the workflow output drops directly into the receivables footnote without reformatting.
Seven deterministic passes from AR open-items XLSX to a bucket-aged, DSO-computed, exception-flagged report — in under 30 seconds per register upload.
Reads the uploaded ar_open_items.xlsx, identifies the tabular sheet, and confirms the presence of all required columns: invoice_no, customer_name, customer_code, invoice_date, due_date, invoice_amount, amount_paid, outstanding, and payment_terms. Registers with missing customer name or code are flagged immediately with a Customer Required FAIL before any computation runs.
For every row, computes invoice_amount − amount_paid and compares the result to the filed outstanding value within a ±Rs 1 tolerance. Rows where the discrepancy exceeds Rs 1 — such as INV-2026-0156 (filed Rs 2,50,000 vs. computed Rs 2,20,000) — are routed to the exception queue with the correct value displayed alongside the filed value. This catches partial-payment posting errors that inflate or deflate the AR balance before they reach the DSO formula.
Checks that due_date ≥ invoice_date for every row. INV-2026-0201 (Acme Corp Ltd, invoice_date 15 Apr 2026, due_date 16 Mar 2026) fails this check and is quarantined from all bucket assignment and DSO computation until the data error is corrected in the source ERP. This prevents impossible negative-days-past-due values from distorting the aging schedule.
For rows that pass both validations, evaluates today − due_date in calendar days and assigns each invoice to one of six aging buckets: Current (not yet due), 1–30, 31–60, 61–90, 91–180, or 180+ days past due. Bucket logic uses due_date — not invoice_date — to honour Net 30, Net 45, and milestone-based payment terms correctly, consistent with the MCA Schedule III aging disclosure requirement.
Any invoice placed in the 180+ bucket receives a collection-risk flag regardless of outstanding balance size, and the entry is surfaced at the top of the exception queue with a recommended escalation note (write-off review or legal referral). This directly supports the ICAI SA 540 auditor assessment of bad-debt provision adequacy and the early-warning trigger for MSME Samadhaan filings or civil recovery before the statute of limitations narrows.
Calculates Simple DSO (total outstanding ÷ (trailing-period revenue ÷ period days)), Count-back DSO by consuming the most recent monthly revenue tranches against the AR balance until exhausted, and Weighted Average Days Outstanding per customer by weighting each open invoice’s days past due by its Rs amount. All three methods are surfaced side by side so the controller can select the metric that fits their covenant or ECL provisioning requirement.
Produces a structured Excel file containing: the aging summary by customer (outstanding per bucket, DSO figures, exception count), a metadata tab with the run date and bucket boundary logic, and the full exception log recording every validation check name, result (pass or FAIL), and computed values. This output is designed to attach directly to the audit file as supporting documentation under ICAI SA 230 (Audit Documentation) without a separate memo or reformatting step.
A battery of synthetic test scenarios covering every failure mode seen in real-world AR registers. Each produces a deterministic outcome that an auditor or controller can verify in under a minute.
invoice_amount = Rs 3,20,000, amount_paid = Rs 1,00,000, but outstanding is recorded as Rs 2,50,000 instead of the correct Rs 2,20,000 — a Rs 30,000 discrepancy exceeding the ±Rs 1 tolerance.invoice_date of 15 Apr 2026 but a due_date of 16 Mar 2026 — the due date precedes the invoice date by 30 days, an impossible payment-terms sequence caused by an ERP data-entry error.customer_name and customer_code fields — a common data-quality gap when AR is exported from Tally without the customer master populated.Five seeded AR open-items registers — each engineered to exercise a different validation path. Together they cover all five exception classes the workflow enforces.
Demonstrates that all five validation checks pass for a well-formed Net 30 invoice and that the workflow correctly places a not-yet-due invoice in the Current bucket rather than a past-due bucket.
Confirms that Net 45 terms are respected: bucket assignment uses due_date, not invoice_date, so a Net 45 invoice dated 31 Dec 2025 ages correctly regardless of when it was raised.
A Rs 30,000 partial-payment posting error that would inflate simple DSO by the same amount if left undetected — potentially crossing a working-capital credit facility covenant threshold before the next draw.
This balance had no partial payment and no recovery action recorded for 230 days — the exact scenario that generates a bad-debt write-off surprise in a statutory audit and a potential ICAI SA 505 external confirmation dispute.
A manual DSO and aging exercise on a 200-invoice AR register takes a finance analyst two to three hours. Cadel completes the same computation in under 30 seconds, enforces five validation checks deterministically, and produces an exception log that drops directly into the audit file — without spreadsheet version-control risk or formula overwrite accidents.
today − due_date, not invoice_dateComputing invoice_amount − amount_paid for every row and comparing it to the filed outstanding within a ±Rs 1 tolerance surfaces partial-payment posting errors that a formula-based spreadsheet will not catch unless the formula was explicitly authored — and they rarely are. A single Rs 30,000 overstatement silently inflates the AR balance, DSO, and the Ind AS 109 ECL provision base before the close package is signed off.
The 180+ bucket flag and the escalation note require zero analyst judgment to generate — they are deterministic outputs of the bucket-assignment step. Eliminating the “someone will look at the old invoices next week” deferral means collection action is initiated before the MSME Samadhaan window closes and before the statutory auditor raises a bad-debt provision gap in the management letter.
The timestamped exception log — recording each validation check name, result, and the computed values used — is designed to attach to the audit workpaper as supporting documentation under ICAI SA 230 without reformatting. The metadata tab in the Excel export gives auditors a reproducible trail from the run date through the bucket logic to each invoice-level assignment, satisfying the workpaper-sufficiency standard.
Questions finance controllers and statutory auditors ask before deploying AR aging automation.
The workflow calculates three DSO metrics simultaneously. Simple DSO divides total accounts-receivable outstanding by average daily sales over the period (AR ÷ (revenue ÷ days)). Count-back DSO (also called the Iterative or Percent-of-Sales method) walks back through prior months’ revenue until the outstanding balance is exhausted, producing a figure less sensitive to revenue seasonality — the preferred method for seasonal manufacturing or FMCG distribution companies. Weighted average days outstanding is computed per customer by weighting each open invoice’s days past due by its Rs amount, giving a customer-level collection-efficiency view useful for prioritising dunning actions.
Bucket assignment is calculated as today − due_date in calendar days — using due_date, not invoice_date, to correctly honour Net 30, Net 45, and milestone-based payment terms. Invoices are placed in: Current (due date today or in the future), 1–30 days past due, 31–60 days past due, 61–90 days past due, 91–180 days past due, and 180+ days past due. Any invoice in the 180+ bucket is automatically flagged as a collection risk. Invoices that fail the due-date-after-invoice-date validation are quarantined and excluded from bucket assignment until the data error is resolved in the source ERP.
The workflow accepts any XLSX export containing the required columns: invoice number, customer name, customer code, invoice date, due date, invoice amount, amount paid, outstanding balance, and payment terms. Most mid-market systems — including Tally Prime, Zoho Books, Oracle NetSuite, and SAP Business One — can export the AR open-items ledger in this format directly from the AR subledger report. No API connector is required; the file is uploaded and processed without manual column mapping when header names match the expected schema.
Four overlapping standards apply. Under Ind AS 107 Para 36B, entities must disclose an aging analysis of financial assets that are past due but not impaired. The MCA Schedule III amendment (FY 2021–22) mandates bucket-wise outstanding disclosure for Indian companies in the notes to financial statements — making the six-bucket structure a compliance output, not just a management tool. Ind AS 109 Para 5.5 requires an ECL provision matrix calibrated to aging bands. Under US GAAP ASC 310-10-50, similar aging disclosures are required for financing receivables, covering cross-listed entities and Indian subsidiaries of US parents.
The current workflow is designed for single-currency INR AR registers, consistent with the expected column schema (Invoice Amt (Rs), Amt Paid (Rs), Outstanding (Rs)). For multi-currency registers, each foreign-currency invoice must be translated to the functional currency before upload, using the closing exchange rate or the transaction-date rate as required by Ind AS 21 or ASC 830. A dedicated multi-currency AR aging workflow that applies RBI reference rates or user-supplied spot rates is on the Cadel roadmap.
Every run produces a timestamped exception log recording each of the five validation checks (Outstanding Arithmetic, Due Date After Invoice Date, Collection Risk 180+, Customer Details Present, Entries List Not Empty), the result (pass or FAIL), and the computed values used. The Excel export includes a metadata tab with the run date, bucket boundary parameters, and DSO formula inputs, giving auditors a reproducible trail consistent with ICAI SA 230 (Audit Documentation) requirements for workpaper sufficiency — without a separate supporting memo.
Simple DSO divides the AR balance by (revenue ÷ period days) — fast to compute but inflated during low-revenue months and compressed during peaks. Count-back DSO walks back through the most recent completed months, consuming each month’s revenue against the AR balance until exhausted, producing a result in days that reflects actual payment behaviour rather than a snapshot average. For seasonal businesses — manufacturing, FMCG distribution, agri-processing — the count-back method is more meaningful for covenant reporting and Ind AS 109 ECL provisioning, where the underlying payment pattern matters more than the period average. Cadel computes both so the controller can select or reconcile the two figures without recomputing manually.
The workflow currently uses the due_date column from the AR register for all bucket assignments regardless of whether the counterparty is an MSME. A separate MSME-flag column is on the roadmap; once added, any MSME receivable beyond 45 days will surface as a distinct exception consistent with the payment obligations under the MSMED Act, 2006. Note that while the Section 43B(h) income-tax disallowance applies to payables to MSMEs (a buyer-side obligation), the symmetric risk for companies holding MSME receivables is that their own buyers may invoke similar penalties — making the 31–60 day bucket the first actionable early-warning threshold for MSME AR positions.