AR Aging Report Design Issue

[AutoMagic]

I receive an AR report showing statements billed and payments made by
customers for the last 12 months. It also shows the current balance.
I have been asked to create an aging report that determines if any of
the current balance is 30, 60 or 90 days overdue. Statements and
Payments are on separate rows and can vary widely. For example one
report I saw showed 5 months unpaid and when the customer finally did
pay they only part of one bill. Accounting always pays the older bill
first. I understand how to create the days overdue formulas but to
track unpaid amounts from over many rows is causing me a headache. I
can also write a VBA program to “look-back” several rows to determine
which bill this partial payment should be applied to but that started
to get way too complex. There must be a simpler way to determine
aging on the current balance. Any help will be appreciated.

 

[AutoMagic]

I receive an AR report showing statements billed and payments made by
customers for the last 12 months.  It also shows the current balance.
I have been asked to create an aging report that determines if any of
the current balance is 30, 60 or 90 days overdue.  Statements and
Payments are on separate rows and can vary widely.  For example one
report I saw showed 5 months unpaid and when the customer finally did
pay they only part of one bill.  Accounting always pays the older bill
first.  I understand how to create the days overdue formulas but to
track unpaid amounts from over many rows is causing me a headache.  I
can also write a VBA program to “look-back” several rows to determine
which bill this partial payment should be applied to but that started
to get way too complex.  There must be a simpler way to determine
aging on the current balance.  Any help will be appreciated.

As an example the Aging Report should end up looking like this if 5
months of payemts are not paid. Then
only a $50 payment is made by client (assumes 30, 60, 90 days pass Due
Date):

Trans Due Invoice Paymt
Client Date Date Amount Recvd Balance Overdue > 30 > 60 > 90
-----------.-----------------.----------------.---------------.----------------.---------------.---------------.---------------.---------------.------------
Client 1 1/1/08 1/31/08 $100.00 $0.00 $100.00 $0.00 $0.00 $0.00 $0.00
Client 1 2/1/08 3/2/08 $100.00 $0.00 $200.00 $100.00 $0.00 $0.00 $0.00
Client 1 3/1/08 3/31/08 $100.00 $0.00 $300.00 $200.00 $100.00 $0.00
$0.00
Client 1 4/1/08 5/1/08 $100.00 $0.00 $400.00 $300.00 $100.00 $100.00
$0.00
Client 1 5/1/08 5/31/08 $100.00 $0.00 $500.00 $300.00 $100.00 $100.00
$100.00
Client 1 5/1/08 $0.00 $50.00 $450.00 $250.00 $100.00 $100.00 $50.00

I'm having trouble determining what formula or VBA code is needed to
do this automatically.

 

[AutoMagic]

As an example the Aging Report should end up looking like this if 5
months of payemts are not paid.  Then
only a $50 payment is made by client (assumes 30, 60, 90 days pass Due
Date):

        Trans   Due     Invoice Paymt
Client  Date    Date    Amount  Recvd   Balance Overdue > 30 > 60 > 90
-----------.-----------------.----------------.---------------.------------­----.---------------.---------------.---------------.---------------.------­------
Client 1        1/1/08  1/31/08 $100.00 $0.00   $100.00 $0.00  $0.00   $0.00   $0.00
Client 1        2/1/08  3/2/08  $100.00 $0.00   $200.00 $100.00 $0.00   $0.00   $0.00
Client 1        3/1/08  3/31/08 $100.00 $0.00   $300.00 $200.00 $100.00 $0.00
$0.00
Client 1        4/1/08  5/1/08  $100.00 $0.00   $400.00 $300.00 $100.00 $100.00
$0.00
Client 1        5/1/08  5/31/08 $100.00 $0.00   $500.00 $300.00 $100.00 $100.00
$100.00
Client 1        5/1/08          $0.00   $50.00  $450.00 $250.00 $100.00 $100.00 $50.00

I'm having trouble determining what formula or VBA code is needed to
do this automatically.

Ballpark Calculations for 30, 60 90 Days Overdue. Assumes monthly
invoices with 30 days to pay bill.

Total Overdue = Current Balance - (Average Invoice X 2)

Buckets (Overdue categories affected - 30, 60, 90) = Total Overdue /
Average Invoice

If Buckets < 1 then

30 = Total Overdue

If Buckets > 1 and < 2 then

30 = Average Invoice
60 = Total Overdue - Average Invoice

If Buckets > 2 then

30 = Average Invoice
60 = Average Invoice
90 = Total Overdue - (Average Invoice X 2)

Total Buckets should always equal Total Overdue.