Aging Calculation

[melman03]

I am trying to use Access to create an A/R aging report but I am stuck. So far, I have debits and credits listed by account number and transaction date. I have created a column called 'Age' using datediff which calculates the age of the transaction, and I used the IIf function to create a field called 'Bucket', which sets the field as '0-30', '31-60', etc for each record.

At this point, I would like to subtract the total credit amount per account number from each line of debit transactions, so as to only age the
debit transactions left outstanding after the all the credits have been applied, using the First-In, First Out method.

Logically I'd like to sort the records by account number and then by age
(descending order), and use the following logic:

reference and join the following query tables:
debit transactions by account
credit totals by account

for each account number,
if debit <= total credit,
set debit = 0
set total credit = credit - debit
if total credit > 0, loop
else stop

then i would take my remaining records where debit <> 0 and use a pivot
table to classify by account number and Age bucket.

I assume I will need to write SQL code for this. Can this even be performed in Access? Since I have no SQL experience, do you have any tips on how to write this? Any help would be appreciated.

Thanks,
Melody

 

[Parker]

seems an awful long winded way!

Why not just run a query to find all unpaid accounts older than say 30 days from date of issue?

If you need to move these to a different table, (though I don't know why you would do that because if they then become paid you will need to move them again),use the query to append a table created speacially for them and delete the record from the origonal table.

Also I don't know about the legalities of "first in first out" methods when applied to credit accounts.------------- I know my accountant would have 40 fit's!

A credit has to be applied to the bill that was issued and the bill your account holder has authorised payment for.

Hence it is possible for a credit account holder to have outstanding invoices older than ones allready paid.

If you are able to use this method then, surly, all you need is a running total and again run a query to find outstang amounts by older than theory.

If you record the date of payment you could even run a "historical" transaction query to determine consistantly bad payer's

 

[melman03]

Thanks for addresssing my issue. I understand this method seems kind of crazy but these are for old accounts, in which payments were made not for any debits in particular -- I know, messy.

You mentioned a running total and a query to find outstang amounts by older than theory. How exactly can I do this? What functions can I use, and will I need to do this in the SQL view? I'm particularly confused about how to apply credits to debits on account age order and how to create Loop funtions to run through records by account. Again, thanks for your help, if you can help me further I would appreciate it.

Melody

 

[Parker]

Thanks for addresssing my issue................ if you can help me further I would appreciate it.

Melody

I will help if I can but this sounds first and formost like an accounting problem rather than an Access one.

Access can handle this sort of thing easily. But first you need to analyse the exact problem.

In order for me to do this it will be necessary for me too understand more about the processes that you have been using historically. I.e. how do (or did) you manage the receipt of payments?

How are these outstanding accounts made up? I.e. are they made up of a number of invoices totalled into a client account, is it loans, credit agreements or what?

How this is dealt with as an accounting problem really depends on the terms of the outstanding account, also the way the law says you must deal with this and record this for tax reasons.

Thhis will differ from country to country and possiblay from state to state.

I have no indication of even which country you are from? I have a little knowledge of accounting law in the UK but outside of UK the only knowledge I have is the certanty that the systems and the law is different.

This dose need to be determined first as to deal with what is, potentially, an Inland Revenue problem in the wrong way could open up a whole can of worms.