Workflow People|Docs
Log in

Tutorial: Accounts Receivable Aging Report

1. Overview

In this tutorial you will build a workflow that takes a list of open invoices and a list of payments received, matches payments to invoices, calculates how many days each unpaid invoice is overdue, and produces an aging report grouped by the standard aging buckets: Current, 1-30 days, 31-60 days, 61-90 days, and 90+ days.

This is one of the most common reports in any accounting office — and one of the most tedious to build manually.

What you'll end up with:

  • A workflow that calculates aging for all open invoices
  • An aging summary showing totals by bucket and by customer
  • A detailed HTML report you can download or preview
  • A CSV export of the aging data for import into other systems

Time: About 10 minutes.

Skip to result: Download the finished invoice_aging.wfp and open it in the app to see the completed workflow without building it yourself.

2. Background

Why this matters

At the end of every month, the AR team needs to know: which invoices are still unpaid, how old are they, and which customers owe the most? The aging report drives collection calls, credit decisions, and cash flow forecasting.

In a spreadsheet, this means calculating days outstanding for each invoice, writing IF formulas to assign aging buckets, building pivot tables by customer, and formatting the whole thing for management review.

What the workflow does instead

You give the app two data files: open invoices and payments received. The workflow matches payments to invoices (partial payments included), calculates the outstanding balance on each invoice, determines how many days overdue it is based on today's date, assigns it to an aging bucket, and produces a report with summary totals and customer breakdowns.

About the sample data

The sample data represents accounts receivable for a small professional services firm. There are 20 open invoices across 8 customers, with payment terms ranging from Net 15 to Net 45. Some invoices are fully paid, some partially paid, and some completely unpaid. The dates span from January to March 2025, creating a realistic spread across aging buckets.

3. Sample Data Files

Add the following files to User Data.

open_invoices — Add this to User Data

invoice_id,customer,invoice_date,due_date,amount,terms,description
AR-1001,Meridian Consulting,2025-01-10,2025-01-25,4500.00,Net 15,Strategy consulting - January
AR-1002,Bluewater Holdings,2025-01-15,2025-02-14,12000.00,Net 30,Quarterly audit services
AR-1003,Apex Manufacturing,2025-01-20,2025-03-06,8500.00,Net 45,ERP implementation phase 1
AR-1004,Meridian Consulting,2025-01-28,2025-02-12,3200.00,Net 15,Tax preparation services
AR-1005,Summit Financial,2025-02-01,2025-03-03,6700.00,Net 30,Financial modeling project
AR-1006,Greenfield Properties,2025-02-05,2025-02-20,2800.00,Net 15,Property tax review
AR-1007,Bluewater Holdings,2025-02-10,2025-03-12,9500.00,Net 30,Compliance review Q1
AR-1008,Cedar Point Labs,2025-02-14,2025-02-28,5200.00,Net 14,Lab equipment audit
AR-1009,Apex Manufacturing,2025-02-18,2025-04-04,11000.00,Net 45,ERP implementation phase 2
AR-1010,Northstar Logistics,2025-02-22,2025-03-24,3800.00,Net 30,Freight audit services
AR-1011,Meridian Consulting,2025-02-25,2025-03-12,4100.00,Net 15,Monthly bookkeeping - Feb
AR-1012,Summit Financial,2025-03-01,2025-03-31,7500.00,Net 30,Annual tax filing
AR-1013,Greenfield Properties,2025-03-05,2025-03-20,3400.00,Net 15,Lease review services
AR-1014,Bluewater Holdings,2025-03-08,2025-04-07,6200.00,Net 30,Internal controls assessment
AR-1015,Cedar Point Labs,2025-03-10,2025-03-24,4800.00,Net 14,Quarterly compliance check
AR-1016,Apex Manufacturing,2025-03-12,2025-04-26,15000.00,Net 45,ERP implementation phase 3
AR-1017,Northstar Logistics,2025-03-15,2025-04-14,2900.00,Net 30,Warehouse inventory audit
AR-1018,Meridian Consulting,2025-03-18,2025-04-02,3600.00,Net 15,Monthly bookkeeping - Mar
AR-1019,Summit Financial,2025-03-22,2025-04-21,8200.00,Net 30,Merger due diligence
AR-1020,Greenfield Properties,2025-03-25,2025-04-09,5100.00,Net 15,Property appraisal review

payments_received — Add this to User Data

payment_id,invoice_id,amount_received,payment_date,method
PMT-001,AR-1001,4500.00,2025-01-22,Wire
PMT-002,AR-1002,5000.00,2025-02-10,Check
PMT-003,AR-1004,3200.00,2025-02-15,ACH
PMT-004,AR-1003,4000.00,2025-02-20,Wire
PMT-005,AR-1006,2800.00,2025-02-22,ACH
PMT-006,AR-1005,6700.00,2025-03-01,Wire
PMT-007,AR-1008,5200.00,2025-03-05,Check
PMT-008,AR-1007,3000.00,2025-03-10,ACH
PMT-009,AR-1010,3800.00,2025-03-20,Wire
PMT-010,AR-1011,4100.00,2025-03-15,ACH
PMT-011,AR-1013,1500.00,2025-03-25,Check

4. How the Aging Works

For each invoice, the workflow calculates:

  1. Total payments received — sum of all payments matching this invoice_id
  2. Outstanding balance — invoice amount minus total payments
  3. Days overdue — number of days between the due_date and today (negative = not yet due)
  4. Aging bucket — based on days overdue:
Bucket Days Overdue
Current Not yet due (days ≤ 0)
1-30 1 to 30 days past due
31-60 31 to 60 days past due
61-90 61 to 90 days past due
90+ More than 90 days past due

Fully paid invoices (outstanding balance = $0) are excluded from the aging report.

Expected results

Given the sample data (assuming today is April 10, 2025):

  • AR-1001: Fully paid ($4,500) — excluded
  • AR-1002: $7,000 outstanding (partial payment of $5,000), due Feb 14 — 55 days overdue → 31-60 bucket
  • AR-1003: $4,500 outstanding (partial payment of $4,000), due Mar 6 — 35 days overdue → 31-60 bucket
  • AR-1009: $11,000 outstanding (unpaid), due Apr 4 — 6 days overdue → 1-30 bucket
  • AR-1012: $7,500 outstanding (unpaid), due Mar 31 — 10 days overdue → 1-30 bucket
  • AR-1016: $15,000 outstanding (unpaid), due Apr 26 — not yet due → Current

5. Building the Workflow

Once your two data files are saved, go to the Dashboard and type:

I have open_invoices and payments_received in my workspace. Build me a workflow that calculates accounts receivable aging. Match payments to invoices, calculate the outstanding balance on each, determine how many days overdue based on today's date, assign aging buckets (Current, 1-30, 31-60, 61-90, 90+), and produce an aging report with summary totals by bucket and by customer. Also give me a CSV export of the aging data.

6. Checking Your Results

Aging summary

The report should show:

  • Total AR outstanding — sum of all unpaid balances
  • Bucket totals — how much is in each aging bucket
  • Customer breakdown — outstanding balance per customer, sorted by largest balance first

Detail table

Each row should show:

  • Invoice ID, customer, invoice date, due date, original amount
  • Payments received, outstanding balance
  • Days overdue, aging bucket

What to verify

  • Fully paid invoices (AR-1001, AR-1004, AR-1005, AR-1006, AR-1008, AR-1010, AR-1011) should NOT appear in the aging report
  • Partially paid invoices (AR-1002, AR-1003, AR-1013) should show the correct remaining balance
  • The aging buckets should be calculated from the due date, not the invoice date
  • Customer totals should add up to the total AR outstanding

7. Saving Your Work

When you're happy with the results:

  1. Go to Workspace → User Data
  2. Click Export .wfp to save everything
  3. At month-end, update the open_invoices and payments_received files with fresh data and run the same workflow again

What's Next

  • Add a collection priority column — flag invoices over 60 days or over $10,000 for immediate follow-up
  • Add customer credit scoring — use api.llm.summarize() to generate a narrative assessment of each customer's payment history
  • Connect to GL — post an allowance for doubtful accounts entry based on the aging percentages
  • Trend reporting — save each month's aging data and build a workflow that shows how AR aging is trending over time