Training - Age Debt + Forecast Payments PRO

Using AgeDebtTotalSpill() and AgeContactDebtSpill() function hot link age debt analysis for cashflow predictions.

You will get the Excel error "#SPILL!" if the function does not have enough space to write to adjacent cells.

   The Wizard "Age Debt/Forc. Payment Wizard" automatically created what we are going to cover on this page.

3.1 Get Age Debt in one function

Next we are going to get Age Debt and Forecast Debt payments based one Average Contact or Average Company Payment days if no Customer History.

   The Wizard "Age Debt/Forc. Payment Wizard" shows how to use the functions we are about to create manually.

   Start a new spreadsheet
   Copy the top 3 rows of the previous sheet across
   C2 add formula =CXLComp to point to the Company code on the previous sheet
   Enter the following in the same cells:

Parameters needed for the Age Debt function

   Next a formula in cell C12 =CXL.AgeDebtTotalSpill( CXLComp,C5,C6,C8,"",C9 ) to give:

Age Debt over 8 Periods in 7 Days Periods

   Reference Guide CXL.AgeDebtTotalSpill( CompCode, [Type], [AgeDays], [NoPeriods], [Options], [PrePeriods] )

   Next we can change the parameters to show the effect:

6 Periods in 10 Days Periods, with 2 periods before Due Date

3.2 Age Debt Drill Down

Selecting CXL figures in the sheet and then selecting the "Chart" menu shows CXL Quick Charts:

Selecting CXL figures in the sheet and then selecting the "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 will drill down to the transactions in each period.

3.3 Age Debt List of Customers

   Next a formula in cell C18 =CXL.AgeContactDebtSpill( CXLComp,C5,C6,C8,"",C9 ) to give:

   Reference Guide CXL.AgeContactDebtSpill( CompCode, [Type], [AgeDays], [NoPeriods], [Options],[PrePeriods], [Filter] )

8 Periods in 7 Days Periods

   And now if we change C5 to "Payable" we see what we own, and when we are likely to pay it:

8 Periods in 7 Days Periods

Other Reading

Now that you have completed this training, you should have enough knowledge to use other functions. We recommend using the Wizards to create the sheets, and then customise those.

   Reference Guide Customer/Vendor Spill PRO

   Reference Guide Stock Spill PRO

   Reference Guide Get Journals, Invoice and Stock Movements PRO

Consolidated XL Functions: "Your Secret Wand for Financial Wizardry! Enjoy...