Skip to main content

Notifications

Microsoft Dynamics GP (Archived)

AP Historical Aged Trial Balance vs. GL Balance

Posted on by 370

Hi,

We have been checking the month-end historical aged trial balance in our Purchasing vs. what's in the GL balance in the AP System account (the AP balance which appears on our Balance Sheet).  For some reason it is off every month, and by different amounts each month.  We are using GP 10.0 and the Historical Aged Trial Balance in the Purchasing module.  Are we running the report correctly, or is there a different report to do that?

Thank you.

 Here's a comparison of the AP vs. GL each month: 


Difference
12 ($55,350.69)
1 ($56,577.56)
2 ($103,259.05)
3 ($112,271.76)
4 ($56,412.64)
5 ($38,013.91)
6 ($8,598.44)
7 ($21,739.80)

 

*This post is locked for comments

  • Ibrahim Ghalghay Profile Picture
    Ibrahim Ghalghay 115 on at
    RE: AP Historical Aged Trial Balance vs. GL Balance

    Good Day dear Mahmoud;

    Please note that the provided link does not exist anymore...

    Do you have an active link for it? or you might saved this script somewhere...

    Would you please help?

    Best regards,

    Ibrahim Ghalghay

  • Verified answer
    Heather Roggeveen Profile Picture
    Heather Roggeveen 9,142 on at
    RE: AP Historical Aged Trial Balance vs. GL Balance

    Hi

    Just one other little thing to ensure you are doing - when comparing your AP to the GL and running the Historical Trial Balance, ensure you are choosing the correct radio button for "Select Transactions For Report Using" - when trying to reconcile to the GL - this should be set to GL posting date.

    That should always balance and if that is what isn't, then yes I am afraid it is one of the methods noted by Frank and Mahmoud.  You will probably find that once you isolate what is happening, there is a process change that needs to happen.

    Cheers

    Heather

  • Suggested answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: AP Historical Aged Trial Balance vs. GL Balance

    Meanwhile, you may consider Sivaumar script for reconciling AP versus GL, in case you have a technical knowledge. It is one legendary script of all times

    SQL Script for AP-GL Reconciliation

    Your feedback is highly appreciated,

  • Suggested answer
    Frank Hamelly | MVP, MCP, CSA Profile Picture
    Frank Hamelly | MVP... 4,029 Super User 2024 Season 2 on at
    RE: AP Historical Aged Trial Balance vs. GL Balance

    I think this is the article you were referring to:

    Reconcile Accounts Payable Account

    There are a number of reasons that control accounts don’t tie to their sub ledgers, including manual journal entries to the control account, transaction posting to incorrect accounts (should have posted to AP but posted to a different account), posting to incorrect periods, voided documents posted to the incorrect period, incorrect settings in the AP Trial Balance report definition, etc., etc.  (Make sure you're running the 'Historical Aged Trial Balance' and in the 'Exclude' section, that all boxes are checked EXCEPT for 'Credit Balance'.)

    The task of reconciling used to be a hunt and peck process before GP10. If you’re using GP10 or a later version, then you have the 'Reconcile to GL' tool available to you. This tool analyzes the transactions in the sub ledger (in this case, the AP Trial Balance) and compares those transactions to transactions in the GL. Where a definite match occurs, such is indicated on the Excel file produced by the Reconcile tool. Where a match doesn't exist, or the match is not 100% definite, those are indicated in the Excel file also.

    However, if you're on GP9 or a prior version, then the task is more difficult. You'll need to analyze your AP account transactions to determine the source of the transactions and whether they hit the correct period. SmartList is a great tool for analyzing individual transactions. Once you have the offending transactions isolated, fix what you can. Any remaining difference will likely require a journal entry to bring the AP account into balance with the APTB. Once you're reconciled, go to the AP Account Card and uncheck the 'Allow Account Entry' box to prevent manual journal entries posting to the account. In fact, you should do this for all your control accounts.

    I also suggest to my clients to reconcile the account more frequently than monthly. Biweekly or even weekly may make sense, depending on the transaction volume in your environment. It's a lot easier to find these little glitches and correct them when the volume of data to pore over is less.

    Oh, one other thing, check out The Closer from http://www.reporting-central.com/. This is a 'Reconcile to GL' tool on steroids that works with any version of GP. If I were responsible for a GP environment, this would definitely be in the toolbox.

  • Frank Hamelly | MVP, MCP, CSA Profile Picture
    Frank Hamelly | MVP... 4,029 Super User 2024 Season 2 on at
    RE: AP Historical Aged Trial Balance vs. GL Balance

    I'll have to look back through  my archives to find the article. It is no longer on the blog.  I'll post it here tomorrow.

  • Mrs.S Profile Picture
    Mrs.S 5 on at
    RE: AP Historical Aged Trial Balance vs. GL Balance

    IT seems that the blog does not exists.  Do you have an active link to this response?  Thank you in advance.

  • Frank Hamelly | MVP, MCP, CSA Profile Picture
    Frank Hamelly | MVP... 4,029 Super User 2024 Season 2 on at
    Re: AP Historical Aged Trial Balance vs. GL Balance

    Have a look at my blog article on this subject - http://gp2themax.blogspot.com/2010/03/gptip42day-reconcile-accounts-payable.html

    Hope it helps,

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,151 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,993 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans