Sage 100

Hi - I am posting in the Urgent Support due to the urgent time-frame for me to respond to a client Sage100 2014.... I need to confirm the proper procedure for a client to ""roll"" their A/R from month to month. Looking for something like: 1) Run the A/R Trial Balance for 3/31/16. 2) Run the Monthly Sales Report for 4/1/16 to 4/30/16. 3) Run the Cash Receipts Report for 4/1/16 to 4/30/16. 4) Run the A/R Trial Balance for 4/30/16, and you should be able to start with #1, add #2, subtract #3 and tie out to #4. Sounds simple .... BUT - A) the monthly sales report does not allow you to enter the date - only the current month ... so how do you do this ""after the fact"" when the month is already closed... B) the Cash receipts report does not seem to use the posting date as the selection criteria, so if a cash receipts batch was posted with a 3/31/16 deposit date, but posted on 4/3/16, it will not show up, and the process above with not tie out. Does anyone have a foolproof method to do this - what reports can be used to accomplish this ? Any help would be greatly appreciated ... new CFO at existing client...... Thanks in advance... Jeff

Seems a bit on the anal side. Can this really be done theoretically? I can think of some scenarios where this will not work. What about the miscellaneous cash receipts that post to a GL account, not to the AR as a PP? What about customers that pay deposits, the cash is recorded to a liability account (not as a PP in AR) and then the deposit is used on the invoice to reduce or pay it in total. This will not reconcile cleanly. What about the misc. items on the invoice (freight, sales tax, etc) that might not appear on the sales report but do appear on the AR TB? I equate ""foolproof"" with ""automatic"", very difficult to make it automatically foolproof. I certainly understand what is trying to be accomplished. Just don't see it happening on a regular basis.

Two Crystal reports? AR_CashReceiptsHistory has the posting date and the deposit date. AR_InvoiceHistoryHeader has the posting date and the invoice date.

While this sounds anal, I think it reflects lack of confidence in how Sage 100 handles this. I don't think this doubt is unusual, and I think it arises from not understanding how the TB can be unbalanced. Given the skepticism, I'm surprised he isn't also trying to reconcile sales tax. School him on how the system works, the relation between dates in subsidiary ledgers and GL. The things that are going cause problems are period straddles in invoicing and receipts, deposits, and GL entries made directly to AR, Help him develop a checklist to use with his Acctg Supv. Basically, when Sage processes are done correctly, it all works, so variances are people-driven. Second option is for him to buy some custom reports from you to help put his mind at ease. Don't make them cheap, and don't guarantee they will always work, because our experience is that customers manage to find new ways to screw up things when not incented to learn the right ways first.

Remember when there was no A/R Trial Balance? As a controller in those days, I had to rigorously enforce the date management for all of our employees who were doing data entry in Sage. That included A/P and A/R, all inventory transactions, etc. Over time, I began to understand that everything was going to the right places, however, I still have to prove the numbers to the upper management and to various auditors who kept wanting to find exceptions and make adjustments. More recently, I've discovered that there are ways to screw up the trial balance as well. As you mentioned, having an entry dated in one month but documents dated in a different month is a huge issue. Regardless, the information is there and you can dig it out with Crystal or Excel & ODBC.

I always tell customers to tie monthly. After-the-fact is not a simple process due to the say Sage 100 tracks info in the open invoice files. Then pray they don't ask why QuickBooks can but Sage 100 can't.

My rule of thumb for AR and AP control accounts - NEVER, EVER use that GLnumber to post ANYTHING to via a JE, cash receipt entry, AP invoice entry, SO invoice, etc to these control accounts. No misc charge codes, nothing...... I wish Sage put in a check box in set up options to prevent you from posting to these accounts. I have had people post AP accruals to the AP control account instead of setting up and AP accrual account. They then wonder why the TB's don't tie to the GL. Inventory is very similar.