Your tracker says “paid.” The ledger says otherwise. The collections team remembers a promise, but nobody can find the promised date.
An accounts receivable tracker should make that situation easier to resolve. It is a working record of outstanding balances, account status, ownership and next actions. To support intelligent collections, it also needs to distinguish a commitment from a confirmed payment.
This guide provides a copyable starter and a set of operating rules. The tracker is a coordination aid. The accounting and payment systems remain authoritative for the states they own.
Decide what one row represents
Choose an account, invoice or installment as the unit of work. Do not mix those units without explicit identifiers and relationships.
For recurring invoices, an invoice-level row may be appropriate. For a member loan, the case may be managed at account level with scheduled amounts held elsewhere. If one payment covers several invoices, keep an allocation record rather than repeating the full receipt on every row.
Write the unit and reporting date above the table. That small step helps prevent double counting when the file is shared.
Use fields that support the next action
| Field | Purpose |
|---|---|
| Account and invoice identifiers | Connect the row to the source record |
| Due date and open balance | Describe the receivable at the refresh time |
| Source refreshed at | Show how current the balance is |
| Commitment amount and date | Record what was agreed |
| Payment state | Separate none, attempted, pending and confirmed states |
| Confirmation reference | Identify the evidence behind the state |
| Case issue | Record billing concern, status check or other unresolved issue |
| Owner and review date | Make the next action accountable |
| Contact handling | Preserve the applicable approved restriction or hold |
Avoid putting unnecessary personal information into a broadly shared file. Use identifiers and access-controlled systems for sensitive supporting detail.
Accounts receivable tracker starter
These fictional invoice-level examples show how to keep a payment commitment separate from payment confirmation. Amounts are illustrative currency units, not customer results. Dates use YYYY-MM-DD.
| Invoice | Open balance | Payment state |
|---|---|---|
| INV-001 | 200 | No payment confirmed |
| INV-002 | 150 | Pending confirmation |
| INV-003 | 90 | No payment confirmed |
INV-001: Commitment recorded. Account EX-001 has a commitment of 200 for 2026-11-16. Owner: AR team. Next review: 2026-11-17. The commitment does not reduce the open balance.
INV-002: Payment status check. Account EX-002 has a pending payment reference, TEST-PAY-002. Owner: Payments team. Next review: 2026-11-14. Pending does not mean posted or reconciled.
INV-003: Billing question. Account EX-003 needs a charge review. Owner: Account specialist. Next review: 2026-11-14. No payment commitment is recorded.
All three examples use a source refresh timestamp of 2026-11-13T08:00:00Z. When building your tracker, keep account and invoice identifiers, due dates, source timestamps, commitment details, confirmation references, owners and review dates in separate columns. Copy the field list above into Excel or Google Sheets and add the example records as separate rows.
Add deployment-specific contact handling and policy fields before operational use. This starter does not determine whether contact is allowed, a concession is available or a payment has legally satisfied an obligation.
Calculate age without changing the source balance
For a simple invoice model, days overdue is the greater of zero and the reporting date minus the due date. Keep the reporting date fixed for a snapshot so colleagues reviewing it later see the same aging.
For example, an invoice due November 1 is 12 days overdue on November 13 under this simple calendar-day convention. Define your own cutoffs, time zone and installment treatment before using aging buckets in performance reporting.
Use formulas for derived values and validation lists for statuses. Do not let a manually selected status overwrite a source-system balance. The intelligent collections operating guide explains why the evidence and decision should remain distinct.
Set up the sheet in five steps
- Put one invoice on each row and retain its account identifier.
- Format identifiers as text so leading zeros are preserved. Keep balances numeric and dates as dates.
- Keep commitment amount and date separate from payment state and confirmation reference.
- Add a named owner, next review date and deployment-specific contact handling.
- Fix the reporting date for the snapshot, then reconcile balances to the authoritative ledger before sharing results.
For example, if the fixed reporting date is in cell B1 and the invoice due date is in C2, =MAX(0,$B$1-C2) gives simple calendar days overdue. Apply it only where both cells contain valid dates. It does not handle business-day rules or installment schedules.
Keep commitments and payments in separate fields
When a customer agrees to pay on Monday, record the amount, date and source of that agreement. Leave payment confirmation unchanged until the relevant system supplies evidence.
If Monday passes without confirmation, label the case for verification under the approved workflow. Missing evidence does not automatically prove a broken promise. The payment file might be delayed, the reference might be wrong or an allocation might be outstanding.
Use the payment-outcomes guide to define which confirmation state counts in recovery reporting. A pending transaction and a reconciled receipt should never share an ambiguous “paid” label.
Give exceptions an owner
“Needs review” is not enough. Record what conflicts, who will investigate, what may happen while the case waits and when it should be reviewed again.
Keep operational comments factual. “Customer reports duplicate invoice; specialist review assigned” is more useful than an assumption about motivation. The scenario-based scripts show how a conversation can collect the information needed for that handoff.
Reconcile before presenting results
Compare the opening balance, new charges, adjustments, allocated payments and closing balance using a consistent period and unit. Where the tracker differs from the authoritative ledger, record the reason rather than silently forcing the totals to match.
When evaluating Agentic Payments, ask which system confirms each payment state and how allocations are reconciled. Do not subtract an unconfirmed commitment from open receivables. Do not count a single payment twice because it appears in both a bank file and a payment-provider file. Keep transaction identifiers where the underlying process permits them.
Recognize when a spreadsheet stops being enough
A tracker becomes fragile when multiple people overwrite statuses, source files arrive at different times or contact decisions depend on data the sheet cannot reliably refresh. At that point, evaluate controlled workflow and integration capabilities rather than adding more color-coded columns. Use the FinanceOps Dashboards overview to discuss the reporting fields and source checks your team needs.
The software evaluation guide provides questions about source ownership and writeback. The pilot checklist explains how to test delayed data and unresolved outcomes before expanding automation.
For organizations using an external provider, NCUA's third-party relationship guidance is a relevant US credit union reference for proportionate planning and controls. The template here is an editorial resource, not a regulatory reporting form.
FinanceOps Collectors Brain
Collectors Brain is designed to coordinate available account context and approved workflows. Use this field list during a FinanceOps discussion to identify which records can be connected, what remains manual and which confirmation source each outcome requires.
Explore the FinanceOps collections solution to connect these operating requirements with a discussion of supported workflows and implementation scope.
Include access, retention and audit requirements from your review of the compliance policy library.
