Help! I did my bank reconciliation and it balanced, but now it does not match my G/L balance.
Help! When I run last month’s statements again I get different numbers.
Help! I ran my month-end foreign exchange revaluation, but now when I multiply my USD A/R amount by the month-end exchange rate it does not match my G/L control account in CAD.
Help! Why do my balance at date amounts not match my net change amounts for my income statement accounts?
What do all these have in common? They are all examples of what can happen when entries are backdated after a reporting activity is completed for a period.
Timely enforcement of posting ranges should prevent the backdating of transactions, but there may be times when things can sneak through between the time you are ready to reset the posting ranges and when you actually get them reset. (Note that the Adjust Cost Item Entries functionality can be the source of unanticipated backdated entries as it processes its costing entries.)
So, what can we do if faced with this type of situation? The first thing I would do is identify what changed. It will be easier to determine the best resolution when armed with this information. Microsoft Dynamics NAV, with its filtering ability, can make it relatively easy to find the information we need. Let’s look at how.
Understanding Ledger Entries
When Microsoft Dynamics NAV posts ledger entries one of the fields of data it creates is an entry number. This number is sequentially assigned as the entries are posted, regardless of the actual posting date.
At the General Ledger (G/L) level, allowed date ranges can be controlled by a combination of the General Ledger Setup and specific User Setup. When entries are posted, the “Posting Date” can be controlled by the user, as long as it falls within the defined range allowed for said user.
When inventory transactions provide new cost information, the Adjust Cost Item Entries routine (which may be run manually or set to trigger automatically) will try to make updated costing entries on a date that matches the original transaction. These entries will not be dated earlier than the “Allow Posting From” date in the General Ledger Setup, but they can carry a posting date that is back in time without a user specifically selecting a posting date that is back that far.
Logic to Find Backdated Transactions
In order to find late entries posted to a date that falls into a “reported” period, we want to find entries with the posting date <= to the report ending period that were posted after reporting was finalized.
Finding the Entry Number that Represents the Reporting Point
In order to know which entries were posted after reporting was finalized, we need to find the ledger entry number that represents the last entry made before reporting.
Although there are other possible ways to find this, the G/L Register provides the typical method. By going to the G/L Register list you can see:
- When the entries were posted (Creation Date)
- The user who posted the entries (User ID)
- The source of the entries (Source Code and Journal Batch)
- The range of entry numbers (From Entry No. and To Entry No.).
If we know the date on which reporting was finalized, we can use the information on this list to identify the last ledger entry number that would have been included.
For example, if reporting was run at the end of day on December 31, 2016, the entry number highlighted above represents the cut-off point we need.
Using NAV Filtering
Microsoft Dynamics NAV provides lists and the ability to filter these lists. This provides a very effective way of isolating information that we need. In the above example, we can make use of the General Ledger Entries list. This lists all transactions for all G/L Accounts by default. However, by applying filters we can use it to list exactly what we need.
In this situation, we want all entries (for all G/L accounts) with a posting date on or before December 31 that were posted after the reporting point (cut-off entry number 3225). Setting the filters as shown below accomplishes this for us:
- Posting Date <= 12/31/16 and
- Entry No. > 3225.
Once we have the resulting list of entries, we have identified what has changed in the reporting period after the point of reporting. Now we can determine how to handle it. My objective here is not to determine what action is necessary, it’s simply to provide a means of gathering the information required to make that decision.
Since Microsoft Dynamics NAV integrates well with Microsoft Office, it is easy to export it to Excel to further manipulate the data once we have a filtered list.
I have focused on G/L-based entries; I can apply the same logic to other ledgers. This includes Bank, Customer (A/R), Vendor (A/P), Item (Inventory), Jobs, etc. Most of these sub-ledgers do not have a register to use in identifying the cut-off entry number. However, there may be other ways of finding it based on locating a specifically posted transaction that defines the cut-off point for what you need to find.
Hopefully, you will never find yourself facing this exact challenge. However, I do hope this will provide you with an example as to how you can creatively combine filters on Microsoft Dynamics NAV lists to let the computer do your work in finding the specific information you need.