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:

   For a tutorial on this function, click here

Parameters:

Parameter	Description
CompCode	Short code assigned by Consolidated XL, or by you to identify your company
Type	Optional Defaults to get Recievable "Payable" returns Payable "Recievable" returns Recievable
[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 "Commited" lists Total Outstanding Transactions "All" lists Totals for Commited,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:

Aged Customer Receivable/Vendor Payable (Pro)

The following functions get Commited 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

   For a tutorial on this function, click here

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 Recievable "Payable" returns Payable "Recievable" returns Recievable "ForecastReceivable" returns Forecast Recievable "ForecastPayable" returns Forecast Payable
	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 Optins contains "FilterContact" then Filter = Contact Code it will then return both All contact data.