I’m trying to put together a 30, 60, 90 Day aging report from an
accounting spreadsheet. The spreadsheet shows the last 12 months for
each customer and also shows – Billing Amount, Billing Sent Date,
Billing Due Date and also when the Payment was received and the
Payment amount. Billing statements and Payments are on separate
rows. It also shows the current balance on the account. I can create
an accurate running balance for all 12 months based on this current
balance by using Goal Seek or other simple calculations. If I receive
a spreadsheet, for example, that covers January 2008 to December 2008
it will show one row that will state the current balance as of
December 31, 2008 is $XXXX.XX. I then need to determine if any of
this amount is 30, 60 or 90 days overdue. Now I could start
determining what was the balance in January and work my way down to
December, determining the overdue breakdown for each month. The
problem is Billing statements and Payments vary and sometimes the
customer may not pay anything for months and when they do pay they may
only pay part of the amount owned. This makes it a nightmare trying
to track this from month to month so I can arrive at the current
month’s (December) over breakdown if any. One workaround I’ve thought
of is to determine the average month statement and divide that into
the balance. For example if the average statement is $100 and the
currents balance is $250 I would come up with 2.5. I can assume that
1.0 is the current month and that is not over 30 days so this leaves
1.5. This leaves 1.0 or $100 is over 30 days and the .5 or $50 is
over 60 days. I don’t have to be accurate to the penny for this
report but it should be close. Does this work? Is there an easier
way to do this?