Breaking news from around the world
Get the Bing + MSN extension
Now Available in Community - MBAS 2019 Presentation Videos
Catch the most popular sessions on demand and learn how Dynamics 365, Power BI, PowerApps, Microsoft Flow, and Excel are powering major transformations around the globe. | View Gallery
2019 release wave 2 Discover the latest updates to Dynamics 365Release overview guides and videos Release Plan | Early Access Availability
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Talent TechTalks | Upcoming TechTalks
Over the years of Supporting NAV, I’ve found a topic that frequently pops up in regards to customers trying to reconcile their Aged Accounts Payables or Receivables Reports to the GL. There can be many different reasons why they may not reconcile, but one reason is that if you have foreign currency transactions, the Aged AP or AR report may not reconcile. Even though there is mention of this topic in KB 858035 and brief mention in the blog below, I will walk through a detailed example to show how the Aged Accounts Payable Report may not tie to the Payables GL Account with foreign currency.
NOTE - This article is not saying you cannot use the Aged Accounts Payable (or Receivable) report to reconcile. If you follow the steps carefully and understand them, you should get a feel for how the report works. There is some timing that is involved. Also, it is important to note, whether you have foreign currency vendors/customers or not, there are other reasons the report may not tie out to the GL (for example, if you post directly to the GL Account).
KB 858035 - "Aged Accounts Receivable and Aged Accounts Payable Do Not Tie Out to the General Ledger Receivables and Payables Balance or the Customer or Vendor Balance" https://mbs2.microsoft.com/knowledgebase/KbDisplay.aspx?WTNTZSMNWUKNTMMYMXTYYKSWTLKQNNOXTSXUSRWYXVYQYPRP
http://community.dynamics.com/nav/b/erpsoftwareblog/archive/2012/10/31/microsoft-dynamics-nav-navision-year-end-closing-procedures see #5 North America Database: US Company SETUP:
CAD VENDOR with CAD Currency and its own Vendor Posting Group to GL 22350 account.
1. Post $1,000 CAD Purchase Invoice ($900 US or local) on 1/26/2015…
2. Print Aged Accounts Payable Report as of 01/26/2015..
3. Add new currency exchange rate on 01/29/2015…
4. If I print Aged Accounts Payable Report as of 1/30/2015, report pulls latest exchange rate within (or up to) the Aged As of Date..
At this point, The GL Account would be $900, but the Aged Accounts Payable is $960 if I compare on 1/30/15. We have a difference.
5. Run Adjust Exchange Rate batch job for an Ending Date of 1/30/15. This records a -$60 Unrealized Loss which increases the Vendor Ledger Value to $960.
6. Aged Accounts Payables as of 1/30/2015 will still show $960, and now so does the GL Account.. 7. If I add another Exchange rate on 1/31/2015.. 8. Again, if I print the Aged Accounts Payable as of 1/31/2015, it will use the new Exchange Rate, while the GL is still only valued at $960. Again, here we’d have a difference.
9. If I skip running Adjust Exchange Rate as of 1/31/2015…. And now add a new exchange rate on 02/02/2015..
10. Now run Adjust Exchange Rate Batch Job, but with an ‘Ending Date’ of 02/02/2015…
11. The invoice is now valued at $990. An Unrealized Loss was posted on 2/2/2015 for -$30. Note - We did not run the Adjust Exchange Rate on 1/31/2015. Remember this was the 2nd time we ran the batch job where currency exchange rate was 99/100 on 02/02/2015, whereas the first time we ran it, the exchange rate was 96/100, thus the -$30 adjustment on 02/02/2015. 12. If we run the Aged Accounts Payable as of 01/31/2015… the report uses the Exchange Rate that was set for 01/31/2015 (the latest Exchange Rate up to the ‘Aged As of Date’, which is 97/100.
13. But if we review the GL Account as of 01/31/2015, the Value is only $960, because the only time we ran the Adjust Exchange Rate batch Job on or prior to 01/31/2015, was step 5 on 1/30/2015 when Exchange Rate was 96/100. (Showing the GL Ledger Entries below…) If we would have run the Adjust Exchange Rate batch Job on 01/31/2015, prior to Step 10, when we ran it on 02/02/15, an additional -$10 Unrealized Loss would have posted and updated the GL Account with a value of $970 as of 01/31/2015.
Unless you run the Adjust Exchange Rate Batch Job on the day you print the Aged Accounts Payable report for, it is recommended to print the Vendor – Trial Balance Report to reconcile with the GL…
Awesome article Bryan; clearly explained and very helpful.
Business Applications communities