Vendor Aging Report

[sjhussain]

Hello,

Hope all is well with everyone.

We are working on an vendor aging report (from ACCPAC - Sage 300) and would like some help in figuring out aging in terms of 1-30, 30-60, 60-90, 90+ days over due.

Please find attached the image showing the invoices and vendor balances.

* In this example the amount Due is 355,686.50

* Not Due yet - if due date is less than or equal to 0 then we can show it in another column - this we can achieve

          * 0 Days overdue - 4,262.15

          * -18 days overdue - 177,230.87

          * Total Not Due yet is 181,493.02

* 355,686.40 - 181,493.02 = 174,193.38 invoices due

* The only invoice from the table remaining or close to 174,193.38 is 167,845.27 which is 1-30 days over due.

* We should just show this amount in the 1-30 days column

* 1-30, 30-60, 60-90 and 90+ we can achieve through interval match

The question is that how we can just show the 1-30 days over due column and the amount 167,845.27 in this column for this example?

How can we just show the invoice which is due as we have the list of all the invoices?

Will appreciate if someone can assist us.

Thanks.

Message was edited by: Syed Hussain

[settu_periasamy]

Hi,

Try something like

Amount Not Due : sum({<APIBH_INVOICES.nDays={"<0"}>}APIBH_INVOICES.AMTTOTDIST)

Invoice Due : [Amount Due] - [Amount Not Due]

1-30 Days : sum({<APIBH_INVOICES.nDays={">0<=30"}>}APIBH_INVOICES.AMTTOTDIST)

[sjhussain]

Settu,

Thanks for the response.  What about the case when there are multiple invoices in 1-30 days and only a few would be added as they are outstanding from the remaining balance?

Thanks.

[Mark_Little]

Hi,

I have done this kind of report a couple of times, each time I have marked the ageing group in script. Something like below.

If([DueAmount] <> 0, If(Ceil(date(Now(),'DD/MM/YYYY') - [Due Date])>90,[DueAmount])) as '>90D',

If([DueAmount] <> 0, If(Ceil(date(Now(),'DD/MM/YYYY') - [Due Date])>60 and Ceil(date(Now(),'DD/MM/YYYY') - [Due Date])<=90 ,[DueAmount] )) as '61-90D',

So on..

this will give you the monthly buckets you can then just sum(>90D) for example.

Mark

[gfisch13]

Hi Mark - I'm trying to adapt the code you show above.  Where do you load this to create the buckets?  Is this in the script, or possibly individual expressions?? 

This is very helpful as I'm new to this syntax, any guidance would be appreciated!

Thanks,

George

[settu_periasamy]

Hi gfisch13‌,

that is script based ..

If([DueAmount] <> 0, If(Ceil(date(Now(),'DD/MM/YYYY') - [Due Date])>90,[DueAmount])) as '>90D',

If([DueAmount] <> 0, If(Ceil(date(Now(),'DD/MM/YYYY') - [Due Date])>60 and Ceil(date(Now(),'DD/MM/YYYY') - [Due Date])<=90 ,[DueAmount] )) as '61-90D',