Reconciliation

Dynamics GP Vs Dynamics 365 Business Central – Reconciling AP and AR to the General Ledger

Gavin WhittakerLeave a comment

Introduction

Its good practice to periodically reconcile the General Ledger control accounts to the sub ledgers. In this post I’ll highlight the reports and enquiries that can be used in Dynamics GP versus the reports and enquiries that can be used in Dynamics 365 Business Central to achieve this task.

Dynamics GP

On the General ledger side you can obtain a balance on a GL code at any given period via the “Enquiry > Financial > Summary” window shown below:

This window provides information on the debits, credits, net change and period balances for an individual account for the year selected.

I love this view as it gives you a lot of information in a handy window that’s also easy to consume. You can drill down by clicking on the blue hyperlink headings to get to the detailed information.

** Incidentally Dynamics GP stores the information displayed in the summary window in a separate table from the detail information. Its not summed data based on detailed transactions. This provides performance efficiencies if there’s lots of data however on rare occasions it might not accurately reflect the period balance. (seasoned GP users will be well aware of this headache).

Unfortunately you can’t get the General Ledger balance at a date that’s in the middle of the period using this window. If you want a General Ledger position at a given date I’d recommend using the “Reports > Financial > Trial Balance > Summary” report.

With the restrictions I’ve added above I can see the General Ledger balance at the 25/04/27. If I wanted a detailed breakdown I’d use the “Reports > Financial > Trial Balance > Detail” using similar restrictions.

Once you have the control account balance you can obtain the AP or AR balance via the relevant Historical Aged Trial Balance report. You can access this via “Reports > Sales (or Purchasing) > Trial Balance > Historical Aged Trial Balance”. The options for the report are shown below:

Key fields on this window are:

Print/Age as of:- Using this report you get a retrospective position on the ledger at any given time. For example if I wanted to reconcile back to January 2020, I’d simply change this date to 31/01/2020 and the report would disregard all activity after this point.

Select Transactions for Report Using:- You can select either Document Date or Posting Date. When reconciling to the General Ledger its recommended to use Posting Date.

Detail:- You can either tick or untick this option if you want a summary per Debtor or Creditor or the detail information.

Using the information from this report you can reconcile back to the GL to ensure the ledgers balance.

Its also worth noting that there is a “Reconcile to GL” feature you can use via “Tools > Routines > Financial > Reconcile to GL”. This tool is very useful as it provides an insight into potential differences between the GL and a given subledger. i.e. it would highlight manual journals that could be causing issues.

Dynamics 365 Business Central

If I want to see a quick balance of a General Ledger code in Dynamics 365 Business Central I’d start by going to the Chart of Accounts page as per below:

In this page I can filter for the General Ledger codes in question and filter the totals using the “Filter totals by:” option.

When I enter a “Filter totals by:” restriction the values shown in the “Balance at Date” and “Net Change” columns change automatically based on the filter (however the “Balance” column always shows the current balance). Therefore in the screen shot above the “Net Change” is showing the change in April and the “Balance at Date” is showing the balance at the end of April.

This is a great feature as I can simply change this filter to any date in the year. I can then drill down on the detail transactions by clicking the amounts.

** Incidentally, the “Balance at Date” and “Net Change” values are dynamic as the data displayed on the page is based on detailed data. (they also don’t exist as fields in a table – they are a special type of field called flow fields). The “Filter list by:” filter is a special filter called a “Flow Filter” that work with those flow fields and performance is maintained by SIFT indexes (SQL Indexed Views).

Its also worth noting I can get a simpler view showing all period amounts for a given General Ledger code by highlighting the General Ledger code and selecting “Navigate > Balance > G/L Account Balance” from the navigation bar.

Again I can drill down on the amounts to see detailed information. This also offers a more familiar way of viewing the balance for a Dynamics GP user.

To obtain a balance on the AP and AR side I’d use the relevant Aged Accounts reports as shown below. (just search for “Aged Accounts” to see these)

The key fields are

Aged as of:- this is similar to the GP option “Age/As of” and will give you the position “as at” a given date enabling you to reconcile to a point in time.

Print Details:- With this option checked the report is printed with transaction detail however left unchecked you are presented with a summary per debtor.

Using the information from this report you can reconcile back to the General Ledger to ensure the ledgers balance

Conclusion

As expected both products offer great and simple options for reconciling your sub ledger back to the General Ledger control accounts.

As I stated above I really like the GP summary enquiry screen however I also love the flexibility that the flow filters provide in Dynamics 365 Business Central. (FYI, this functionality is offered throughout the system).

Also, in Business Central, as the summary values are provided based on detailed data in the ledger entries table, I can guarantee the balances are providing accurate information every time.

Thanks for reading!

Dynamics GP – 4 Steps to help reconcile General Ledger to Receivables or Payables

Gavin Whittaker2 Comments

Its month end and when you come to reconcile the General Ledger control accounts to Receivables or Payables there’s a difference! You check all the usual things like unposted General Ledger batches or manual journals that may have been inadvertently posted to a control account, but this proves fruitless. So where do you start to track down the difference? There’s quite a lot of information out there on this however one technique I’ve found that gives me the most success is to narrow down the difference to a single day using the Historical Aged Trial Balance and the General Ledger Trial Balance and then use smartlists to focus on the transactions on that day. So without further ado here’s the full process.

Step 1: Find the date the ledgers last balanced.

This is very important. For this to work you need a point in time when the Receivables (or Payables) Historical Aged Trial Balance matches the control account. In reality the ledgers should be reconciled at least monthly so this shouldn’t be a problem

Step 2: Run the Historical Aged Trial Balance periodically and compare this to the General Ledger Trial Balance of the control account.

Now you have a starting point when the ledgers balance run the historical aged trial balance report periodically using the “Print/Age as of” field and compare this to the General Ledger trial balance for the same date range until you find the difference.  

For example, let’s say the General Ledger and Receivables last balanced at the end of February but don’t balance at the end of March 2019. In this scenario you’d run the historical aged trial balance with a “Print/Age as of” date of the 7th of February as per below:

You’d then run the General Ledger Trial Balance for the control account up to the 7th of February as per below:

If the Historical Aged Trial Balance matches the General Ledger Trial Balance you can safely assume the difference occurred later in the month and therefore you repeat the process running the same reports but advance the date. For example, you’d run the Historical Aged Trial Balance report with a “Print/Age as of” of the 14th of February and the General Ledger Trial Balance report with a date of 14th of February. Once you find a point where the reports no longer match you alter the date working backwards until ultimately you have one day when the reports match and the following day when the reports don’t match. You have now found the date the ledger goes out of balance!

Step 3: Run a smartlist of the General Ledger and Receivables transactions for the day the ledgers went out of balance

Now you have the exact date the ledgers went out of balance you can run a smartlist of the General Ledger and Receivables transactions and compare them looking for differences.

To do this use the “Account Transactions” smartlist for the General Ledger using the date the ledgers go out of balance.

Therefore, assuming the date we have identified when the ledgers went out of balance is the 10th of February, you’d run the smartlist using the following search criteria:

Next run the “Receivables Transactions” smartlist for the Sales Ledger using the same date as per below:

If you output both smartlists to Excel you can compare the two datasets and hopefully this will highlight the transaction(s) causing the difference. However if you still can’t find the difference Step 4 may help.

Step 4: Check the Apply records

Normally by Step 3 I’ve identified the difference and can take corrective action. However, on the rare occasion this doesn’t help I’ve had to examine the apply tables in SQL as I’ve found a journal hasn’t posted for a multi-currency exchange gain or loss.

To explain in more detail when one or more foreign currency transactions are applied with difference exchange rates a journal is created in the General Ledger to adjust the control account. This is also recorded on the RZGANLOS field in the Receivables apply tables namely RM20201\RM30201.

To see if this is causing an issue you need to have access to SQL to run a query. The query I use is below assuming the date the ledgers went out of balance is the 10th of February:

set dateformat dmy
select * from RM20201 where DATE1 = ’10 Feb 2019′ AND RLGANLOS<>0
select * from RM30201 where DATE1 = ’10 Feb 2019′ AND RLGANLOS<>0

In my experience you should have at least one journal for each record produced by the query. In the General Ledger smartlist they would have a source document of “RMAPY”. (apply journal). If not a manual journal may have to be posted for any missing journals.

After following these steps I can usually find the difference.

Thanks for reading.