Yikes! My GL Payables/Receivables Total Do Not Balance To My PM/RM Trial Balance! What To Do?

Whenever I hear from marketing folk that Great Plains Dynamics is so dependable that you never need to worry about whether your general ledger (GL) balances to your accounts receivable and accounts payable (AR/AP) trial balances, I counter by saying YES, Great Plains Dynamics is one of the most dependable accounting packages on the market, and that is why you should check the balance of the general ledger to the AR/AP sub-ledgers every morning, so in the rare event that it does go out of balance, you have a much easier chance to find the difference, particularly when you have a small army of accountants feeding hundreds or thousands of transactions to the beast every day.

http://www.dilbert.com

If you want to save yourself a lot of grief and lower your blood pressure you should plan ahead and follow best practices.  If you want to follow best practices, on the day of each period end, and especially at year end, print to a PDF file the AR/AP trial balances and the period GL detail for the AR/AP control accounts.  (The main AR/AP control accounts for Fabrikam are 1200 and 2100.)  If you keep the books open for a few weeks for payables, then print an AP historical aged trial balance (HATB) and also the GL trial balances for the payables GL accounts to a PDF file, making sure they balance.  Although the HATB reports printed for prior periods are amazingly accurate, the longer you wait to print the AR/AP HATB report, the more likely you will find discrepancies.  Also, you should setup your general ledger so you post transactions in detail to the GL for the AR/AP GL control accounts.

Human error is more often to blame when the GL and AR/AP goes out of balance, although an aborted posting can cause a discrepancy.  For the AR trial balance, be sure you have checked the box to exclude UNPOSTED APPLIED CREDIT DOCUMENTS, this will cause the report to go out of balance with the GL.  When there is a discrepancy, first check for unposted GL batches, even if you think all batches posted through to the GL.  If the A/R trial balance is out of balance, run the Utilities -> Sales -> Reconcile utility to recalculate the Outstanding Document Amounts and the Current Customer Information.  Next, print a GL trial balance for the AR/AP GL control account for the out-of-balance period to screen and look for entries posted directly through a general ledger entry or from an odd posting source.  (Tip: you can search for any string in any report printed to screen.)

Did you notice that the AR Reconcile program gives you the option to print the report showing the proposed changes without processing?  Some good advice: never take unneeded risks, print the report first to see what will happen, THEN hit the PROCESS no return button.  Several years ago our GL and AR trial balances where thrown seriously out-of-balance when someone did not heed this advice.  They needed to un-apply a troublesome AR payment using the Professional Tools, which transfers AR transactions back to the open file so the payments/credit memos can be reapplied, and they did not print just the report first, and unfortunately the program seriously misbehaved.  That gave me a conundrum to figure that day, how to shift data between open and history and applied to bring the AR trial balance back into balance with the GL.

Starting with Great Plains Dynamics Version 10 there is a menu option, Reconcile to GL, with separate options for receivables and payables.  If you have multiple AR/AP GL control accounts, be sure to enter all of them.  This generates an Excel spreadsheet that shows the items that are possible discrepancies, you can disregard the rest of the content of the spreadsheet.  Usually these steps will resolve any discrepancies between the GL and AP/AR balances.

If you can’t find the cause of the discrepancy, the problem might solve itself when the mystery transaction causing the problem is processed and paid.  Keep in mind that when a transaction is paid the payment will relieve the same AR/AP GL control account that was used in the initial transaction.  Also, if the difference is small it could be due to a subtle timing difference perhaps related to discounts, which usually reverses as the transaction is paid.

Are you still out of balance?  To troubleshoot you need a deeper understanding of the problem.  Next step is to ask Dr Google to search for “Great Plains Dynamics 866570” and you will give you the nitty gritty details on “Information about differences when you reconcile General Ledger to Payables Management or to Receivables Management in Microsoft Dynamics GP.”  Many of these tips are repeated here, but some items are presented in greater detail, so it is worth studying.  Also keep in mind that when Great Plains posts AR/AP transactions, it posts them transaction by transaction, starting with the most important files first and the least important last, to minimize the file damage in case of a hardware related posting failure.

We learn in article 866570 that the “Reconcile to GL” spreadsheet is not a true reconciliation of GL to AR/AP, what it is really reconciling is the AR/AP drilldown GL files to the true GL files.  The AR drill-down files are the Receivables GL Distribution History table, RM30301; the RM Distribution Work table, RM10101; the PM Distribution History table, PM30600; and the PM Distribution WORK OPEN table, PM10100.  Since these tables are copies of what should post to the GL, any differences would be due to GL adjusting entries being edited or deleted, or perhaps the drill down tables themselves are not properly updated.

Before we implemented Dynamics 10 I had written some SQL scripts that compare the GL sub-ledger detail for the AR GL control account to the AR transactions in the AR trial balance and AR history files detail to find any discrepancies, which is a true reconciliation.  These scripts assume that the GL date matches the AR transaction date, or at least match within the week.  We have used these scripts for several years, and they run much quicker than the “Reconcile to GL” spreadsheet.  You could also write similar scripts to reconcile the GL transactions to the AP transactions. (See Below)

Sometimes when there is a small difference between GL and the AR/AP trial balances even when the individual GL and AR/AP transaction detail match to the penny.  How can this be?  Something other than the transactions themselves are affecting the AR/AP trial balance.  Logically it is probably due to a subtle timing differences or discrepancies caused by discounts and/or apply-to amounts.

What else can you check?  Run a SmartList or SQL query (below) against the AR/AP drilldown tables to see if the AR/AP control account was entered as an offset account there.  This error would also affect the real GL, as the drill down files are a copy of what is posted in the actual GL.  In this exhibit,  the A/R control account type should only be entered for RECV type, which is 3 in the table.  If it is entered for any other type, this will case the AR and GL to go out-of-balance.  If you see some exceptions, you can also run a SmartList or SQL query of customers/vendors and the various posting accounts entered, maybe someone entered the AR/AP GL control account as an offset account.  Also, for A/R you have a snapshot of the statement balances in the RM Reprint Statements Header table, RM30701, you can see if the AR customer balance for two successive statement runs matches the GL activity for that customer for that period.  This analysis is a more indirect test than my SQL query above and is probably redundant.

On rare occasion you will find a difference that can never be found and which never clears. In that case you have no choice but to make the GL entry to balance.  If you enter and track your balances following best practices this will be very rare, this happens for us every five years or so. There is not a bank auditor or external auditor that will get upset if you have to make one or two such GL entries a year to balance the AR/AP to the GL.

EXHIBIT 1
— Created by Bruce Strom for GPUG Magazine, June 2016
— Finding differences between AR and GL trial balance for a specified date range.
— Keep in mind there are lots of unposted batches in Fabrikam
— and since Fabrikam contains bogus test data, it has other differences.

USE [TWO]
GO

DECLARE @FromDate datetime,@ThruDate datetime
SELECT @FromDate = ’01/01/2003′,@ThruDate = ’01/01/2009′

DECLARE @GLSummary TABLE
([CUSTNMBR] nvarchar(15),
[Debit] money,[Credit] money)

INSERT @GLSummary
SELECT [ORMSTRID]
,Debit = sum([DEBITAMT])
,Credit = sum([CRDTAMNT])
FROM [dbo].[GL20000] gl
inner join [dbo].[GL00105] idx
ON gl.[ACTINDX] = idx.[ACTINDX]
where gl.[TRXDATE] BETWEEN @FromDate and @ThruDate
— replace with your main segment number and A/R accounts
and idx.[ACTNUMBR_2] in (‘1200′,’1271′,’1272′,’1273′,’1274′,’1275′,’1276′,’1277’)
— if your main account segment is valid, which it is not in Fabrikam,
— use idx.[MNACSGMNT], as the Main Account Segment field is indexed.
GROUP BY [ORMSTRID]
ORDER BY [ORMSTRID]

DECLARE @Aging TABLE
([CUSTNMBR] nvarchar(15),[DOCNUMBR] nvarchar(22),
[AMOUNT] money)

INSERT @Aging
SELECT [CUSTNMBR],[DOCNUMBR]
,case WHEN ([RMDTYPAL] = 7 or [RMDTYPAL] = 9) then -[ORTRXAMT] ELSE [ORTRXAMT] END ‘Amount’
FROM [dbo].[RM20101] — RM OPEN trial balance table
where [GLPOSTDT] BETWEEN @FromDate and @ThruDate

INSERT @Aging
SELECT [CUSTNMBR],[DOCNUMBR]
,case WHEN ([RMDTYPAL] = 7 or [RMDTYPAL] = 9) then -[ORTRXAMT] ELSE [ORTRXAMT] END ‘Amount’
FROM [dbo].[RM30101] — RM HISTORY table
where [GLPOSTDT] BETWEEN @FromDate and @ThruDate

— reverse any voided transactions
INSERT @Aging
SELECT [CUSTNMBR],[DOCNUMBR]+’*’
,case WHEN ([RMDTYPAL] = 7 or [RMDTYPAL] = 9) then [ORTRXAMT] ELSE -[ORTRXAMT] END ‘Amount’
FROM [dbo].[RM20101] — RM OPEN trial balance table
where [VOIDDATE] BETWEEN @FromDate and @ThruDate

INSERT @Aging
SELECT [CUSTNMBR],[DOCNUMBR]+’*’
,case WHEN ([RMDTYPAL] = 7 or [RMDTYPAL] = 9) then [ORTRXAMT] ELSE -[ORTRXAMT] END ‘Amount’
FROM [dbo].[RM30101] — RM HISTORY table
where [VOIDDATE] BETWEEN @FromDate and @ThruDate

DECLARE @AgingSummary TABLE
([CUSTNMBR] nvarchar(15),
[AMOUNT] money)

INSERT @AgingSummary
SELECT [CUSTNMBR]
,Total = sum([AMOUNT])
FROM @Aging
group by [CUSTNMBR]
order by custnmbr

— create link table for customer numbers
— in case customer is in GL and not AR, and vice-versa
DECLARE @CusnoLink TABLE
([CUSTNMBR] nvarchar(15))

INSERT @CusnoLink
SELECT [CUSTNMBR] FROM @AgingSummary

INSERT @CusnoLink
SELECT [CUSTNMBR] FROM @GLSummary
WHERE [CUSTNMBR] not in (select [CUSTNMBR] FROM @AgingSummary)

SELECT lk.[CUSTNMBR],cus.CUSTNAME,
‘Debit’=isnull(gl.debit,0),
‘Credit’=isnull(gl.Credit,0),
‘AR Amt’=isnull(ar.Amount,0),
‘DIFF’ = isnull(gl.debit,0) – isnull(gl.Credit,0) – isnull(ar.Amount,0)
FROM @CusnoLink lk
INNER JOIN [dbo].[RM00101] cus
ON lk.[CUSTNMBR] = cus.[CUSTNMBR]
LEFT JOIN @GLSummary gl
ON gl.CUSTNMBR = lk.CUSTNMBR
LEFT JOIN @AgingSummary ar
ON ar.CUSTNMBR = lk.CUSTNMBR
WHERE isnull(gl.debit,0) – isnull(gl.Credit,0) – isnull(ar.Amount,0) <> 0
— comment or uncomment the WHERE line

DECLARE @CustomerException TABLE
([CUSTNMBR] nvarchar(15))

INSERT @CustomerException
SELECT lk.[CUSTNMBR]
FROM @CusnoLink lk
LEFT JOIN @GLSummary gl
ON gl.CUSTNMBR = lk.CUSTNMBR
LEFT JOIN @AgingSummary ar
ON ar.CUSTNMBR = lk.CUSTNMBR
WHERE isnull(gl.debit,0) – isnull(gl.Credit,0) – isnull(ar.Amount,0) <> 0

SELECT * FROM @Aging
WHERE [CUSTNMBR] IN (
SELECT [CUSTNMBR] FROM @CustomerException )
ORDER BY [CUSTNMBR]

— The customer/vendor Master ID is not a key field in the GL20000 table,
— so a SQL query to search the GL20000 GL transaction table
— for specific master ID’s and GL Account segments can be slow for a large database.
— Of course, once you narrow down the difference to just a day,
— you can then run the RECONCILE TO GL program for just that day.