Calculate aging over time

Where can I find the information I need to calculate customer aging over time? Usually when I get stuck I can copy something one of my predecessors did, but no one knows of any report like this previously. 

I want to be able to show a sales person, for instance, that their receivables for an account are increasing over time. Eg., when viewing the report in June, they'd be able to see in January that their net30 for a customer was $1000, February it was $1500, March.... etc etc. But, I'm stuck as to what would be my starting point to establish initial balance etc. I would assume it's something like transaction history, where to find quantity at a point in time I would calculate the flowing river after Jan 1 of each year... But again, stuck as to where to start... Maybe I need to establish that beginning yearly balance in a table and calculate from there to avoid the queries being too monstrous. Hope I'm not over thinking this. 

Parents
  • 0

    Perhaps print the Aged Invoice Report to paperless office every month.

  • 0 in reply to BShockley

    I'm actually looking to show the information in an external application I'm building, so when they start to write an invoice they'll get a quick graph summary of some key customer data - including a trend line of account balance over time. 

  • 0 in reply to wishingforsql

    The raw data is not in a form that is easy to interpret "as of" a certain date... and if you have DSD multi-currency, don't even try.  Be aware the "open" data is not permanent (subject to the "days to retain" setting, instead of the years of history setting).

    There is one row in AR_OpenInvoice for each first transaction (top grid on the Invoices tab in Customer Maintenance), with transactions "applied to" that first transaction (payment, credit, adjustment...) having subsequent details linked under that transaction with the data stored in AR_TransactionPaymentHistory (lower grid on the Invoices tab).  The dates for the various transaction types can be found in different places... making custom reports very difficult to write.  Starting with the raw history tables would be much more complicated.

    With multi-currency it's impossible to deal with mixed currency transactions, due to the ways the program determines when an invoice is paid in full.  Don't even try.

    A better idea would be to schedule the stock Aging report (which refreshes the Aging buckets in AR_Customer), then capture those values daily in a UDT.  Base your report off the UDT.

  • 0 in reply to Kevin M
    A better idea would be to schedule the stock Aging report (which refreshes the Aging buckets in AR_Customer), then capture those values daily in a UDT.  Base your report off the UDT.

    That's an interesting idea. Probably the easiest, but I don't mind building my own summary. I don't presently have any scheduled tasks, but I'm generally pretty good at building my own data aggregations and firing them from store procedures or functions etc. I did the same thing for transaction history, so I could look back and see how much material we had on hand on any given date. We're all single currency. But, in this case, yeah, I should probably schedule the report to run at, lets say 10PM, and then have a SQL scheduled task run at 2AM or whatever to load everything the aging category buckets from AR_Customer in to my own tables.... Trouble is, it'll take 3 months to get the starting point for the data I want to show! 

    If I go to at least the aggregated data, it sounds like I can use the first entry in OpenInvoice as my starting point, then calculate invoice totals by bucket, and PaymentHistory to calculate the payment history by bucket, and show the change over time? 

Reply
  • 0 in reply to Kevin M
    A better idea would be to schedule the stock Aging report (which refreshes the Aging buckets in AR_Customer), then capture those values daily in a UDT.  Base your report off the UDT.

    That's an interesting idea. Probably the easiest, but I don't mind building my own summary. I don't presently have any scheduled tasks, but I'm generally pretty good at building my own data aggregations and firing them from store procedures or functions etc. I did the same thing for transaction history, so I could look back and see how much material we had on hand on any given date. We're all single currency. But, in this case, yeah, I should probably schedule the report to run at, lets say 10PM, and then have a SQL scheduled task run at 2AM or whatever to load everything the aging category buckets from AR_Customer in to my own tables.... Trouble is, it'll take 3 months to get the starting point for the data I want to show! 

    If I go to at least the aggregated data, it sounds like I can use the first entry in OpenInvoice as my starting point, then calculate invoice totals by bucket, and PaymentHistory to calculate the payment history by bucket, and show the change over time? 

Children
  • 0 in reply to wishingforsql

    This sounds like a neat report idea.  I'd love to collaborate with you on it.

    Another way to look at this:  measure average days to pay or average daily balance.

    You could also create a new metric - DDO - or DollarDaysOutstanding and graph that.

  • 0 in reply to wishingforsql

    IM history is all in one table, so calculating point in time values is not all that hard (just add everything from the beginning of time up to your date). 

    AR data is not so simple, being located in multiple tables, and much more complicated with different transaction types looking very different.  Look at the actual data in those tables and you'll quickly see what I mean (invoices, payments, credits, adjustments, applied discounts, finance charges...).  To calculate aging buckets, you can't just add everything up from the beginning of time, you need to see what each invoice balance is at that point in time, with aging dates based on the original entry.  Throw in "fun" possibilities like how payments can technically be dated before the original invoice... and it becomes a real headache to do well.

  • 0 in reply to Kevin M

    True that! Which is a big part of why I got stuck. You're helping me see through the trees, though. That said AR_InvoiceHistoryPayment is blank, but I should be able to correlate OpenInvoice to CashReceipts. I cte's or temp tables in my future... 

  • 0 in reply to wishingforsql

    That is the wrong table (stores details of posted CC payments).

  • 0 in reply to Kevin M

    CC = credit card? It appears to store all types of data, from check to cash to CC. The CheckNo column either has a check number or some sort of prefix, VISA, CASH, etc. 

  • 0 in reply to Kevin M

    All good points.  I have been building these reports for years now and understand the complexities.

    It seems like the user wants to see a trend of a customer's payment performance.  We should be achieve thet, even if it glosses over a few nuances.  items that skew that trend can be addressed (may be with some difficulty and by accepting a margin of error)

  • 0 in reply to wishingforsql

    Ignore that table entirely.  It's not what you want.  Cash Receipts history is stored in AR_CashReceiptsHistory.

    Edit: wait.... you might need that data for CC payments processed during invoice data entry.

    Seriously, going to the raw transaction history tables is making your life much more complicated.  Sage 100 is a complex system, with bits of data scattered in multiple places.  There is a very good reason why Sage programmed the AR_OpenInvoice and AR_TransactionPaymentHistory tables to manage invoice balances. 

  • 0 in reply to Kevin M

    AR_TransactionPaymentHistory was not part of my replication schema. Doh! Just had a look at the actual production table and now I see. A lot of relevant info. I'll add it to the replication model. I was initially looking at AR_InvoicePaymentHistory, which was blank. 

    AR_CashReceiptHistory seems to include all payment types. I'm still not sure what is wrong is wrong with this table other than it is volumunous, like trying to use AR_InvoiceHeaderHistory (or detail, etc). 

  • 0 in reply to wishingforsql

    The problem is understanding every data source fully, every field, every nuance... odd transaction types, weird dates... table data you didn't think to look at until months later when a user asks about a number not matching what they see inside the system.  Then joining it all together, to perfectly reproduce invoice balances at any given date... when even the Sage Aging reports aren't perfect at this.

    I've been burned by underestimating the complexity of data too many times (including with AR / AP Aging specifically), when simpler approaches would have achieved the business goals with much less effort / cost. 

    If you want to go dive into the complexity, good luck! Slight smile

    The possibilities are not limited like simple IM +/- calculations.

  • 0 in reply to Kevin M

    I'm game. What's next?