The Aging Report is a custom report that can be run for you and uploaded into Dashboard>Financial Reports. Clients typically request that the report be run for them daily. There is currently no way for the user to generate an Aging Report from the UI.
The Aging Report is a downloadable spreadsheet file which can be opened in Excel, with the following tabs:
- Report Index
- Raw Data
- one tab each for the last three complete calendar months
- one tab for the month in progress ("[MONTH] MTD")
- Unlinked Invoice Items
The Report Index contains a simple table of contents of all the other tabs.
The Raw Data tab contains a list of all transactions which have an outstanding balance during the time span of the report (last three complete calendar months and current month to date). All transactions in the current month are as of midnight of the day of export.
The transactions can be filtered and sorted by the following fields:
- Invoice Number: note that only transactions related to an invoice are included in the report. If for example a prepayment was logged but not used to pay off any invoice, it does not appear here.
- Client Alayacare ID
- Client Item Total
- Invoicing Model: this is the invoicing model of the funder of the invoice, dependent on their type.
- Posting Date: the date assigned by the user to the transaction.
- Type: invoice, credit note, payment, etc.
- Payer Name and Provided To Name: of the funder of the invoice. In the case of client individual funders, this is the billing contact of the invoice.
- Funder Code: of the funder profile.
- Client First Name
- Client Last Name
- Client Phone Number
- Billing Period Start Date/End Date: only if the invoice is not ad hoc.
Additional notes about content:
- No "Draft" invoices are included
- No "Deleted" invoices are included
- If "Invoice Total Amount" = "Payment Total Amount" and the “Payment Maximum Date” = "Current Date minus 3 months", those invoices are excluded as the balance would be 0
Aging Month over Month and Current Month
The next four tabs detail, for the previous three calendar months and the month in progress, the Aging for each client and funder combination. Therefore, if a given client has services funded from several different sources, Aging will be detailed for each funding source on their own line.
These tabs can be sorted and filtered by the following fields:
- Alayacare Client ID
- Client Full Name
- Funder Name
The next four columns total the outstanding balances of all invoices aged in the following increments:
- 0-30 days
- 30-60 days
- 60-90 days
- 90 days and over
Aging is calculated from the invoice Posting Date to the day of export of the Aging Report.
An invoice with an outstanding balance of $50 is dated April 15th and the Aging Report is exported on the 17th of April. The $50 will be added to the column 0-30 in the tab April MTD for that client and funder.
An invoice with an outstanding balance of $50 is dated April 15th and the Aging Report is exported on the 25th of May. The $50 will be added to the column 30-60 in the tab April for that client and funder.
The invoice numbers associated with each balance are listed in the comments within the applicable cell in the worksheet. Hover over the cell to make the comment appear, or use the options Show/Hide Comment or Show All Comments in the Excel Review tab.
Unlinked Invoice Items
If an entry in the raw data cannot be linked to a Client, we input AC000000000 as the AlayaCare ID and add it to the "Unlinked Invoice Items" worksheet. This can happen if the invoice line item/visit was deleted.
The Invoice Posting Date is the "Invoice Date" set on the Billing Period. If this value is null, the "Invoice Date" will reflect the date of the transaction.
If a balance is between -0.001 and 0.001, we round to 0.
There are cases where certain transactions (i.e. credit notes, payments, write-offs) happen before the Invoice Posting Date. Since we use the Invoice Posting Date to calculate the age, in these cases we compute everything and add it as a negative amount (i.e. overpay).
Calculation of Balances
The calculation of balances will differ based on the invoicing model (see Raw Data).
Client Individual/Funder Individual Invoicing Models
Both of these models create a separate invoice per client.
Payments therefore apply for a single client and funder, whether they are used for a single invoice or pay multiple invoices.
Funder-batched Invoicing Model
A single invoice is created for multiple clients.
Since all clients are housed within the same invoice, the payments are not applied to a specific client but rather to the invoice.
To age this invoice per client, the payment sum is pro-rated across the clients according to the value of items in the invoice which concern them.
- This percentage is computed using “Invoice Item Amount” / “Invoice Total Amount”, see example below
A single invoice is sent for multiple clients.
A single payment is received for the invoice, however the RA file response to the billing file specifies the quantities payable for each payable invoice item.
An invoice is sent for 3 clients, A,B, and C, each with a visit of $50.
An RA file is received stating that a payment of 90$ will be made.
The RA file specifies that client A's visit is rejected, but the visits for B and C are payable, though only for an amount of $45 each.
Client A will therefore have a balance of $50 owing and clients B and C will each have a balance of $5 owing - until the user chooses to perform some action to eradicate the balance, such as writing off or crediting it.