Unanswered: AR Aging - When Payments are Applied to Account vs to Invoices
I am working on a database for a client in MS Access 2003 - the way they handle their accounting is as follows:
Invoices are Generated
Debit May be Generated - Interest - Service Charge, Etc
Payments are Rceived - Payments are not applied to any particular Invoice, - just to the Account
Credits may be Generated Against the Account - refund, adjustment
Invoice terms are 30 days - from Invoice date
Interest is Applied to Balances over 30 days
I am trying to show Aging Balances on a Form
Current - 30 Days - 60 days - 90+ Days
How do I create a query, or set of queries that will allow me to do this?
If a payment is applied to the account, I would like it to credit the oldest invoice - is this FIFO???????
If I add the debits between now() and now()-30 minus the credits between now() and now()-30
I get balances for 30 days when there were payments made... I also have to consider any debits/credits generated in the periods??
Is this FIFO????? Yes
First, let me define the terms and assumptions I'm going to use here. First, just use credits to calculate the current balance, then ignore them afterwards. That way, all credits are applied without any date consideration (FIFO) and the charges are the only things that will be considered in this aging process.
Because all money received is against the account only, what you need first is the balance due. Then subtract the most recent charges that are within the previous 30 days. If the amount due is more than the charges, then the charges total is the amount in the <=30day column. Do the same with the remainder of the current balance with the charges in the period of >30 and <=60days. That is the amount due for this column, etc...
Hope this helps,