In today’s post I am going to go over a very unused but very powerful feature in Dynamics 365, also available in Dynamics AX 2012 called ledger settlements. Let me start by laying out the common scenario I seen many times at multiple organizations.
Usually most companies following accrual accounting will have a miscellaneous expense accrual account, a liability account. Let’s call this account Miscellaneous Account Payable. Accountants will use this account to record expenses that will be payable in the short term but haven’t received an actual invoice yet. Another use will be to accrue for monthly expenses that will be paid in the near future at a lump sum. During month-end, quarter end or year end reconciliation, the same accountants will want to know the details of what transactions make up the ending balance of this Miscellaneous Accounts Payable account. What I have seen done many times is that the accountant will export all the transaction line details into an Excel spreadsheet, and then through sorting, filtering, coloring, etc… will try to tick and tie the transactions that were reversed, those that were paid and so on. The ultimate goal is to narrow down to the line transactions that make up the final balance of the account so they can provide support to the auditors of the final balance of the account.
Now let me show you how you can use Dynamics 365 or AX 2012 to perform this same process within the system:
First let’s take a look at the ending balance of our Miscellaneous Account Payable account as of 12/31/2017. We have to find out what makes up our liability of $10,000
- Head over to General ledger> Periodic Tasks> Ledger Settlements and then on this screen filter to the account that you want to reconcile. In our case, account 200101. Notice that it shows me all the activity booked on this account.
2. Then on this screen you can use various filtering techniques to tick and tie transactions that offset each other. I like to use the matches technique in order to find out transactions that offset each other. For example I will want to find out transactions that are positive 5,000 and negative 5,000 to mark them as settled. For that I will type 5,000, -5,000 to find them
Once I identify all the transactions that offset each other, I will mark them as reconciled. I will select the lines that offset each other and then click on include. Once I am done identifying those transactions in the top corner press “Accept”
I will repeat this process, until I am done identifying the transactions that offset each other.
3. Once you are done ticking and tying all the transactions, the accountant, accounting manager or anybody who is interested in what makes up the final balance of the account can head over to the trial balance and click on all transactions.
Notice how all transactions show up initially. When looking at this screen it is not easy to see what makes up the balance of the account.
But once you click on “Show unsettled transactions only” on the top corner, the system will automatically filter and show you only those lines that make up the final balance.
How great is this. I am sure your manager or controller will love this feature. What’s even better is that you can attach any supporting documentation to the lines. This would be great during audit time. This can be used to have all your account reconciliations and support all within Dynamics 365.
Hope you enjoyed this post, and keep being awesome.
Until next time.
Juan Sebastian Grijalba