Accounts receivable automation: review ageing from supplied records

Accounts receivable automation can prepare a repeatable ageing view from authorised invoice, payment and credit records. Fix the reporting date, calculate each remaining balance from supplied allocations, then group outstanding amounts by currency and days past the supplied due date. Preserve disputed and incomplete records for review.

The account receivable automation task here is a deterministic report, with no collections messages or ledger updates. The supplied allocations determine which payments and credits belong to each invoice; this workflow does not discover those relationships.

Capability status for this design: documented. Load CSV, Python Interpreter and Update Range are documented in the catalogue; not production-tested by Ai Flowi for receivable ageing.

Copyable ageing input contract

Use one row per invoice in a frozen export. Keep its source revision, currency, due date, supplied invoice amount and separately allocated payment and credit amounts. Retain allocation references outside or beside the row so a reviewer can trace how those totals were produced.

invoice_ref,currency,due_date,invoice_amount,allocated_payments,allocated_credits,disputed,source_revision
AR-A,MYR,2030-04-30,1000.00,250.00,50.00,false,SYNTHETIC-1
AR-B,MYR,2030-03-31,400.00,0.00,0.00,false,SYNTHETIC-1
AR-C,MYR,2030-03-30,300.00,100.00,0.00,false,SYNTHETIC-1
AR-D,USD,2030-03-01,200.00,0.00,20.00,false,SYNTHETIC-1
AR-E,MYR,2030-02-28,150.00,0.00,0.00,true,SYNTHETIC-1
AR-F,MYR,,90.00,0.00,0.00,false,SYNTHETIC-1

All dates and amounts in this table are hypothetical. The synthetic reporting date is 2030-04-30; it is an exercise input, not this page’s publication date or a live balance date. Include only allocations effective on or before the reporting cutoff according to your source system’s date policy.

Use allocated credits as positive deductions in this contract. An unapplied credit or payment stays separate until the source process assigns it. Never subtract a customer-level payment from every invoice, and never subtract credits again if the supplied starting amount is already net of those credits.

Define ageing buckets before calculating

Remaining balance equals supplied invoice amount minus allocated payments minus allocated credits. Age is the reporting date minus the supplied due date, measured in whole calendar days for this example. A due date equal to the reporting date has zero days overdue and belongs in the not-yet-due bucket.

A dispute is a separate flag rather than an alternative arithmetic rule. Show its balance in its age bucket and also in a disputed subset, without adding that subset a second time to the grand balance. Missing or invalid dates require an exception, not an assumed payment term.

Worked synthetic ageing view

The MYR starting amounts total 1,940.00, allocated payments total 350.00 and allocated credits total 50.00. Their residual total is 1,540.00: dated buckets sum to 1,450.00 and the unaged exception adds 90.00. The disputed 150.00 is already included in the dated buckets.

The USD record reconciles separately: 200.00 minus 20.00 equals 180.00. Do not add MYR and USD into a single money total or convert them using an unstated rate. These are expected results from the authored dataset, not observed customer balances.

Runnable decimal and boundary check

This small standard-library example preserves exact decimal amounts and tests the 30/31/60/61-day boundaries. It expects reviewed decimal strings and ISO date strings. Validate blank, non-finite, negative allocation and malformed date inputs before a broader calculation; the input contract must be satisfied before this helper is used.

from datetime import date
from decimal import Decimal

def age_bucket(days):
    if days <= 0:
        return "not_yet_due"
    if days <= 30:
        return "1_30"
    if days <= 60:
        return "31_60"
    return "61_plus"

as_of = date.fromisoformat("2030-04-30")
balance = Decimal("1000.00") - Decimal("250.00") - Decimal("50.00")
assert balance == Decimal("700.00")
assert [age_bucket(n) for n in (0, 30, 31, 60, 61)] == [
    "not_yet_due", "1_30", "31_60", "31_60", "61_plus"
]
assert age_bucket((as_of - date.fromisoformat("2030-03-31")).days) == "1_30"
print(balance, age_bucket(0))

A negative residual must survive as an over-allocation exception instead of being clamped to zero. A fully settled invoice has zero residual and can be listed separately from outstanding balances. Reconcile all source rows, including those exceptions, before accepting the report.

Configure preparation and a controlled report write

example configuration, not a recorded test

Prepare AI Flowi Workflow, the authorised export, your own Google account and a connection that can edit a dedicated report range. No model or model-provider key is needed for this calculation. Any separate API you add needs your provider account and credentials, with provider charges outside the plan.

Follow the Load CSV source controls for ingestion; this report requires the invoice-level allocations and explicit dispute flags in the input contract above.

1. Load the frozen export with Load CSV. Reject a missing allocation column rather than treating its absence as zero, and retain the exact source revision for every accepted invoice row.
2. Set Python Interpreter’s global_imports for csv, datetime, decimal and json; place your reviewed validation, ageing and reconciliation code in python_code. The small snippet above checks the rule; it is not a complete CSV processor.
3. Keep the input handoff explicit. Python Interpreter has a Message-compatible code input, not a JSON data port. For a bounded trial, supply the inspected synthetic records as fixed data in reviewer-authored code; do not turn untrusted CSV cells or generated text into executable instructions.
4. Inspect its results JSON. Prepare a rectangular header-and-row array from the accepted calculation; the export does not specify the results envelope or an automatic array mapping.
5. Use the shared Sheets writing setup for Update Range; the ageing-specific destination is the column mapping below.

Map the ageing output to a dated report range

For the six synthetic invoices, set range_name to AgeingReview!A1:M7: one header row and six data rows. In values, place the explicit header array first and then one equally wide array per invoice; set include_object_headers false so this design supplies its own header exactly once. Choose the exported value_input_option "RAW" to preserve the prepared text.

reporting_date,source_revision,currency,invoice_ref,due_date,days_overdue,invoice_amount,allocated_payments,allocated_credits,remaining_balance,age_bucket,disputed,exception_reason
2030-04-30,SYNTHETIC-1,MYR,AR-A,2030-04-30,0,1000.00,250.00,50.00,700.00,not_yet_due,false,
2030-04-30,SYNTHETIC-1,MYR,AR-F,,,90.00,0.00,0.00,90.00,unaged,false,missing_due_date

Those two CSV rows illustrate the first and last invoice outputs; the full destination also needs AR-B through AR-E. Keep currency summaries in a separate labelled area so a reader cannot mistake a summary for another invoice. Match the six destination invoice references back to the six inputs and compare MYR 1,540.00 and USD 180.00 separately. If a later snapshot has fewer invoices, use a fresh review range and identify the current snapshot explicitly; old rows must not enter the new totals.

Why reconcile before anyone acts on an accounts receivable report?

Keep the reporting date, source revision, currency subtotals, dated buckets, unaged records and disputed subset visible in the output. Test a future due date, a missing due date, a partial payment, an allocated credit and a credit greater than the invoice balance. Duplicate invoice rows must be resolved before summing.

The report automation guide owns the broader report lifecycle; JSON-to-CSV conversion covers export shape, and the Google Sheets reference covers destination operations. This report does not send collections messages, match bank payments or post to a ledger. Use your accounting system’s existing ageing report when it already preserves the required allocations and cutoff.

Preparation usage

Plans: Go from USD8/month billed yearly (USD10 monthly), Plus from USD16 (USD20 monthly), Pro 100 from USD80 (USD100 monthly), Pro 200 from USD160 (USD200 monthly) and Pro 500 from USD400 (USD500 monthly), with 5,000 to 250,000 Flowi Tokens/month and top-ups; 1 token per node run; third-party API and model usage is not included.

Inspect loader, calculation and range-write executions in your own run history. The arithmetic check has no recorded Workflow charge, and rerunning a report adds executions without changing the allocation evidence.

By Ai Flowi · Published 10 October 2026.