Aged Receivable/Payable Spill PRO
- On this Page:
- Total Age Rec./Payable
- Drill Down
- Age Rec./Payable
Using AgeDebtTotalSpill() and AgeContactDebtSpill() functions hot link age debt analysis for cashflow predictions.
You will get Excel error "#SPILL!" if the function has not got enough space to write to adjacent cells.
The Wizard "Age Debt/Forc. Payment Wizard" shows how to use the functions below. It's best to create a sheet using the wizard and then customise it.
Aged Customer Receivable/Vendor Payable (Pro)
The following functions get Aged Customer Receivable/Vendor Payable Aged Analysis. This function will also list the value of transactions in Draft/Not Authorised (Xero 'Awaiting Approval' Status)/Committed status used for Cash Flow analyisis (Xero Approved Status).
Example:
if AgeDays=7, PrePeriods is 2 and NoPeriods=6 then we get:
First line is Age Debt aged in 7 day Periods, with 2 periods shown before due date
The second is a Forecast based on the either Contact Average Pay days from History
Or overall company average
Performance Rated:A+
For a tutorial on this function, click here
Example
| Get Age Debt Summary for COMP_A: | |
| age by 30 days, in 6 cols: | =CXL.AgeDebtTotalSpill( "COMP_A", "Receivable", 30, 6 ) |
| age by 10 days, in 8 cols: |
=CXL.AgeDebtTotalSpill( "COMP_A", "Receivable", 10, 8, 2 )
And included 2 columns before due date aging |
| For only Xero Draft trans: |
=CXL.AgeDebtTotalSpill( "COMP_A", "Receivable", 10, 8, 2, "Draft" )
And included 2 columns before due date aging |
| Get Agee Payable Summary for COMP_A: | |
| age by 30 days, in 6 cols: | =CXL.AgeDebtTotalSpill( "COMP_A", "Payable", 30, 6 ) |
Wizard: "Age Debt/Forecast Payment Wizard" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.
Parameters:
| Parameter | Description |
|---|---|
| CompCode | Short code assigned by Consolidated XL, or by you to identify your company |
| Type |
Optional Defaults to get Receivable "Payable" returns Payable "Receivable" returns Receivable |
| [AgeDays] |
Optional Defaults to 30 Sets the age in days of each column (NoPeriods and PrePeriods) |
| [NoPeriods] |
Return x Periods, the Period duration is set in company settings Hence if 7 days, MaxPeriods=6, gives break down of the next 6 weeks, with the last period being every thing >= 6 weeks Click here for an explaintion of these periods. max value 42. |
| [Options] |
Optional - Comma delimited list "NoHeading" removes headings "NoTitle" removes Title in first row "Submitted" lists Total for Submitted(Xero status) Transactions "Draft" lists Total for Draft(Xero status) Transactions "Committed" lists Total Outstanding Transactions "All" lists Totals for Committed,Submitted,Draft "HideForecast" removes forecast lines |
| [PrePeriods] |
Optional 1..6 break down of periods before due date so 1 gives week before, 2 gives 2 weeks before... |
Age Debt Total Drill Down (Pro)
Selecting CXL figures in the sheet and then selecting "Chart" menu shows CXL Quick Charts:
Selecting CXL figures in the sheet and then selecting "Chart" menu shows CXL Quick Charts
showing the Debt and the forecast Debt payments in a chart.
The "Trans" menu shows all outstanding transactions for the current selected period.
Clicking on the chart columns wil drill down to the transactions in each period.
Aged Customer Receivable/Vendor Payable (Pro)
The following functions get Committed Aged Customer Receivable/Vendor Payable by Customer/Vendor table broken down for a single company.
Contact Age Debt aged in 7 day Periods, with 2 periods shown before due date
If type is set to "ForecastReceivable" then gives forecast pay dates based on Contact average
Or overall company average
Performance Rated:A+
For a tutorial on this function, click here
Example
| Aged Debtors for Customers for COMP_A: | |
| age by 30 days, in 6 cols: | =CXL.AgeContactDebtSpill( "COMP_A", "Receivable", 30, 6 ) |
| age by 10 days, in 8 cols: |
=CXL.AgeContactDebtSpill( "COMP_A", "Receivable", 10, 8, "", 2 )
And included 2 columns before due date aging |
| et for QuickBooks Vendor transactions with display name "Vendor1", Xero Contact "Vendor1": | |
| age by 30 days, in 6 cols: | =CXL.AgeContactDebtSpill( "COMP_A", "Payable", 30, 6, "",0,"Vendor1" ) |
Wizard: "Age Debt/Forecast Payment Wizard" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.
Parameters:
| Parameter | Description |
|---|---|
| CompCode | Short code assigned by Consolidated XL, or by you to identify your company Or a list to get a list of all GL codes from multiple companies. |
| [Type] |
Optional Defaults to get Receivable "Payable" returns Payable "Receivable" returns Receivable "ForecastReceivable" returns Forecast Receivable "ForecastPayable" returns Forecast Payable |
| [AgeDays] |
Optional Defaults to 30 Sets the age in days of each column (NoPeriods and PrePeriods) |
| [NoPeriods] |
Return x Periods, the Period duration is set in company settings Hence if 7 days, MaxPeriods=6, gives break down of the next 6 weeks, with the last period being every thing >= 6 weeks Click here for an explaintion of these periods. max value 42. |
| [Options] |
Optional - Comma delimited list "NoHeading" removes headings "NoTitle" removes Title in first row "SortByCode" Sort By Code "SortByName" Sort By Name "SortByOldest" Sort By Oldest Debt "SortBySlowest" Sort By Slowest payer based on avg. days "FilterContact" Filter by Contact code in Filter "ShowInCurrency" Show all Contacts in Currency "FilterCurrency" Filter by Contacts to Currency entries only |
| [PrePeriods] |
Optional 1..6 break down of periods before due date so 1 gives week before, 2 gives 2 weeks before... |
| [Filter] |
Optional if Options contains "FilterContact" then Filter = Contact Code it will then return both All contact data. |
OutStanding "Receivable" or "Payable" Transactions (Pro)
The following function will get OutStanding "Receivable" or "Payable" Transactions for a single company.
Syntax: =CXL.ContactTransSpill( "COMP_A", "Payable" ) Performance Rated:A
Example
| Get for QuickBooks Vendor transactions with display name "Vendor1", Xero Contact "Vendor1": | |
| Outstanding Transaction list: | =CXL.ContactTransSpill( "COMP_A", "Vendor1", 0 ) |
| Transaction list from year 2026: |
=CXL.ContactTransSpill( "COMP_A", "Vendor1", 2026 )
By default returns 5 years of data from financial year 2026 |
| only 24 periods back: | =CXL.ContactTransSpill( "COMP_A", "Vendor1", 2026, 0, 24 ) |
| restrict to last 50 transactions: | =CXL.ContactTransSpill( "COMP_A", "Vendor1", 2026, 0, 0, 50 ) |
| All Outstanding Transactions | |
| All Debts/Receivables: | =CXL.ContactTransSpill( "COMP_A", "Receivable" ) |
| All Creditors/Payable: | =CXL.ContactTransSpill( "COMP_A", "Payable" ) |
Wizard: "Age Debt/Forecast Payment Wizard" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.