Sage 100

Before I go re-inventing the wheel, I have a customer requesting their AP and AR aging to be based on posting date, rather than invoice date, the explanation as to why is rather long, and they don't want to budge. I am thinking either SI or BIExplore... but they are requesting Crystal. Any words of wisdom?

I believe the AR and AP Trial Balance reports are based on the transaction date which should equal posting date. Perhaps you can modify this report to the format they want.

If they are an accrual basis company, the posting/transaction date and the invoice date should be the same.

Grab a 4.00 version of the AR aging report under the custom reports menu (Sage used to include a Crystal version of the reports) and modify the formula to use the transaction date as the aging date. You can use the Set Location option in Crystal to point to the new tables.

@BillPfahnl good call! I'll give it a shot. @DougHiggs SHOULD... Agreed.

@MichelleNichols You can do the same as @BillPfahnl mentioned in 2014 and 2015. The transaction date is in the AR Aging report. You would just need to replace the standard aging categories with your own formula. Click Save in upper right corner, then ""Save As"" and you can create your own version of the aging.

I modified a copy of the Trial Balance report to look like an Aging report so that it always ties to GL. Took awhile but client (and the auditors) love it. No matter how many times I tried to explain, they could never understand why they were unable to print an Aging that tied to GL months after the fact.

Printing an aging after the fact is highly requested by my customers too.

Good tips everyone! I will poke around and see what I can come up with. Thanks so much!!

Correct me if I'm wrong, but isn't the aging report dependent on the number of days to retain paid invoices? Due to this you're limited on back dating based on this setting.

AR and AP Open are affected by days to retain but most have this set to the max. The problem is payments/cash receipts effect on these files. That's why modifying the Trial Balance to mimic the Aging is the only way to go.

Thanks @KathrynScofield I see the wisdom of that method.

Wow! A lot happens when you are on-site with no access to email! Thanks everyone!

One of our own in-house smarty-pants figured this out pretty quick. He added his own parameter to the report for the ""age as of"" date, then generated formulas to calculate the age in days based on the transaction date, then a few more formulas for aging buckets with some simple suppression and viola! He's testing, but looks good so far. Hopefully we can get him to the MotM!

Anyone have a TB report that ages the invoices based on transaction date that is shareable? Perhaps there is one in the Dropbox site? Auditors just don't get the concept of why there are differences between the Aging and the TB report. They WANT their aging to tie to the GL....

You might also see if the check on Print Transaction Date option on AR Aging would be helpful to the auditors.

A client of mine asked for this too. So for AP, instead of modifying the Aged Invoice Report to match the Trial Balance, I modified the AP Trial Balance to look like an aging report. Attached is the report. It's in summary by Vendor for a client with Job Cost but can easily be modified to show invoice detail. It's written for Sage 2014. Just create a new report setting in AP Trial Balance, click designer so the system creates a folder and copies in the default report, then replace the existing .rpt file with this one. You'll need to rename the file if they don't have JC.