Accounts Receivable (AR) Aging Report

Problem Statement:

Currently, Pelcro does not provide an Accounts Receivable (AR) Aging Report. This limits finance teams’ ability to track outstanding balances by aging buckets (e.g., 0–30, 31–60, 61–90 days), which is a standard practice in accounting and critical for collections, cash flow forecasting, and financial audits.

💡 User Story

As an Accounts Receivable (AR) manager,

I want to generate an AR Aging Report from Pelcro with all relevant invoice, subscription, customer, and payment details,

So that I can track overdue balances, categorize them by aging buckets, and take appropriate collection actions.

🎯 Definition of Done (DoD)

A feature is done when:

1. Add the new report AR aging report under the reports page in Accounting.

2. The report must include a single “As of Date” filter that determines the cutoff date for aging calculations and which invoices are considered “open.”

   1. Industry Standards for AR Aging Report Time Filters

      1. Type: A single date selector (e.g., “As of 31 July 2025”).

      2. Why it’s standard:

      3. Aging is always calculated relative to a single reference date.

      4. Easy to reproduce accounting cutoffs and match financial statements.

      5. Reduces confusion vs. having “from–to” ranges.

      6. All open invoices as of that date are shown.

3. Aging buckets (0–30, 31–60, etc.) are calculated based on that date.

4. Only invoices with a balance due as of the selected date should appear in the report.

5. The report must include the following data points:

   1. Invoice ID

   2. Invoice Number

   3. Invoice Created At

   4. Invoice Status

   5. Invoice Total

   6. Subscription Created At

   7. Subscription Started At

   8. Subscription Period Start

   9. Subscription Period End

   10. Subscription Status

   11. Customer ID

   12. Customer First Name

   13. Customer Last Name

   14. Customer Email

   15. Product Name

   16. Plan ID

   17. Plan Name

   18. Plan Term

   19. Plan Amount

   20. Accounts Receivable

   21. Invoice Due Date

   22. Amount Not Yet Due

   23. Amount 1 to 30 Days

   24. Amount 31 to 60 Days

   25. Amount 61 to 90 Days

   26. Amount 91 to 120 Days

   27. Amount Over 120 Days

   28. Plan Auto Uncollectible Days - For each invoice, identify the Plan attached to the invoice and export the Plan level Auto Uncollectible Days value. This will allow computing the days (based on Invoice Due Date + Override Uncollectible Days) remaining before the invoice would become uncollectible.

   29. Sales rep name attached to the Subscription for which the Invoice is generated.

6. Aging buckets (Not Yet Due, 1–30, 31–60, 61–90, 91–120, Over 120) must calculate dynamically based on the selected As of Date.

7. Accounts Receivable per invoice equals the total of all aging buckets for that invoice.

8. Only invoices created on or before the As of Date and still open as of that date are displayed.