A useful accounts receivable spreadsheet should do more than show an aging bucket. It should connect each open invoice to the person responsible for follow-up, the latest contact, any promise or dispute, and the next action.
A promise is not a payment: see the actual workbook
Watch the 45-second A/R workbook demonstration on YouTube.
Follow a fictional $12,500 invoice, a $5,000 payment and the remaining $7,500 balance. See why a promise to pay is recorded separately from received cash, then review the assigned follow-up.
This silent demonstration uses views rendered from the actual demo workbook. English and Spanish captions are available; the downloadable workbooks are in English. For small worksheet text, open the video full-screen and select 1080p if available.
Fields to track
- Customer or payer
- Invoice number and invoice date
- Due date and remaining balance
- Aging bucket
- Last contact date
- Promise-to-pay or dispute status
- Assigned owner
- Next action and due date
A simple weekly review
- Start with overdue balances.
- Confirm whether a payment, promise or dispute changed the status.
- Assign the next follow-up and owner.
- Separate expected cash from cash actually received.
- Escalate balances with no action or missed commitments.
For businesses that want this workflow in a reusable workbook, review the MetricForgeHQ Accounts Receivable Tracker Excel. It includes a fictional demo, blank workbook, aging views and a collections follow-up workflow.
This guide is for general business organization and does not replace accounting, legal or financial advice.
Worked example: an aging balance becomes a collection action
Use a fixed review date so the same invoice does not change buckets during the review. In this fictional example, an invoice for $1,200 was due on October 1. By the October 31 review, $300 has been applied and no credit has been issued.
- Remaining balance: $1,200 − $300 = $900.
- Days past due: October 31 − October 1 = 30 days.
- Bucket: 1–30 days past due under this example's convention.
- Next action: verify the payment allocation, confirm the unpaid amount and assign an owner and follow-up date.
For a simple Excel exercise, place the review date in B1, the due date in C2, invoice amount in D2 and applied payments in E2. Use =MAX(0,$B$1-C2) for days past due and =D2-E2 for the remaining balance. These formulas assume valid dates and no credits or adjustments; investigate negative balances rather than hiding them.
A promise to pay does not reduce the balance. Record it separately until cash is received and applied. Review disputes separately from routine follow-up.