Tips and Tricks for AR and AP reconciliation

Posted by

Accounts receivable (AR) and Accounts payable (AP) reconciliation is apparently a vital step in the month\year end closing process. Auditors may trace transactions from a sub ledge to the general ledger and from there to the financial statements, to ensure that transactions are being recorded properly in the accounting system.

This blog provides the tips and tricks to handle the AR and AP reconciliation during the month end process in Microsoft Dynamics 365 for finance and operations,

TIP #1: Analyze and troubleshoot discrepancies through reconciliation reports

If you see any discrepancy or difference between Trail balance and Aging reports, there are out of box reports in AR and AP module to provide the insight of variances between sub-ledger and ledger balance.

These reports provide both summary and detail view of the customers\vendors transactions with differences. You can further drill down to the journal and voucher level to troubleshoot the root cause of the discrepancy.

From Microsoft Dynamics 365 for finance and operations, the reports are moved to below path,

Accounts receivable > Inquiries and reports > Customer > Customer to ledger reconciliation report

&

Accounts payable > Inquiries and reports > Vendors > Vendor to ledger reconciliation report

ARAP_Recon_01.PNG

Mark “Include details” and “Difference only” check box so the report will extract voucher and journal wise details for only differences. In this way it is easy to identify and troubleshoot the transaction which has caused the differences

From the below example screenshot you would notice the journals which are directly posted to ledger causing the difference of 13,999 between customer balance and ledger balance

ARAP_Recon_02

TIP #2: Reconcile sub-ledger accounts and make use of balance list reports.

In some scenario’s reconciliation reports may not help as the problem could be within sub-ledger. In such cases, generate customer/vendor balance list report and customer/vendor aging report to verify the balance within the sub-ledger accounts.

The aging reports can be accessed from the path below,

Vendors:

Accounts payable > Inquiries and reports > Vendor aging report

Customers:

Credit and collections > Inquiries and reports > Customers > Customer aging report

The balance list reports can be accessed from the path below,

Vendors:

Accounts payable > Inquiries and reports > Vendor reports > Vendor balance list

Customers:

Credit and collections > Inquiries and reports > Customers > Customer balance list report

Note: For the demo purpose customer reports are used.

From the below screenshot as you observe comparing the customer aging report with the customer balance report and trying to reconcile each customer account balance in summary.

ARAP_Recon_03

Identify the account which is causing the major difference and re-generate the customer aging report with the “Details” check box marked by filtering on a specific customer.

The detailed report provides the transaction wise data for the filtered customer which will help to identify the actual transaction impacting the difference.

ARAP_Recon_04

On the other hand customer balance list report can be used to generate the report by main account and this is when “Include details” check box is marked in report dialog.

This helps to identify the all ledger account used to post transactions for the particular customer account, if in case the summary accounts are changed for the customer in the posting profile, this report will give the clear view of all the GL accounts used to arrive at the customer balance.

ARAP_Recon_05

TIP #3: Understanding of Batch transfer rules

Make sure there no transactions pending to be transferred from Sub ledger to general ledger and this can be observed from the ‘Sub ledger journal entries not yet transferred’ form in general ledger.

General ledger > Periodic > ‘Sub ledger journal entries not yet transferred’

ARAP_Recon_06

If the records exists, the transaction can be transferred individually or through the batch from the above form. However, the batch transfer rules setup determines when the sub-ledger transactions needs to be transferred when sub-ledger postings occurs.

It is recommended to use the Synchronous mode of transfer so the general ledger postings happen real time, however when volume of transactions is more it is recommended to setup Asynchronous (Processed when server resources are available) or Scheduled batch (Processed based on the recurrence of the batch schedule) for performance enhancement. It is necessary to monitor the sub ledger journal entries not yet transferred form and transfer balances before reconciling accounts.

ARAP_Recon_07

To avoid certain reconciliation issues in Microsoft Dynamics 365 for finance and operations follow the tricks below,

Trick #1: Do not allow manual entry

Make sure the control accounts of AR and AP (assigned to posting profile) are marked with “Do not allow manual entry” in the main account form

This helps to prevent the postings to the control accounts directly and avoid differences between sub-ledger and general ledger balance.

ARAP_Recon_08

Trick #2: Do not settle transactions with multiple posting profiles.

Make sure when you are changing the summary account from posting profile there are no open transactions for particular customer or vendor.

Let’s say, the customer US-002 is associated to group ‘30’ and posting profile is setup to post GL entries to account “130100”. At some point, business decided to track all GL entries separately for customer account ‘US-002’

ARAP_Recon_09

In this case, make sure there are no open transactions for the customer US-002 and it’s recommended to change posting profile at the start of new fiscal period and not in mid period as it might cause havoc during the month end closing process.

ARAP_Recon_10

I hope this helps who are struggling in fag end of every month closing process.

Cheers,

Namith Hosmane

Disclaimer:
This is a personal blog. Any views or opinions represented in this blog are personal and belong solely to the blog owner and do not represent those of people, institutions or organizations that the owner may or may not be associated with in professional or personal capacity, unless explicitly stated. All content provided on this blog is for informational purposes only. This blog makes no representations as to the accuracy or completeness of any information on this site.

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s