Implementing a new Enterprise Resource Planning (ERP) system can provide an ideal opportunity to clean up your Chart of Accounts. With Microsoft Dynamics NAV, you also have the opportunity to take advantage of the Dimension functionality it provides.
If you are making significant changes to your Chart of Accounts and you want to load any General Ledger (GL) history (in detail or in summary form) to the new ERP system, you must map all existing accounts with activity to the new accounts. Using a free entry tool (e.g. Excel) comes with a significant risk of entering invalid destination accounts.
Below, I outline how the creative use of existing Microsoft Dynamics NAV tools can provide a method of building the map from legacy GL accounts to new GL accounts – and appropriate Dimension values – that minimizes the risk of error.
A recent implementation using this approach resulted in no errors for invalid GL accounts or Dimension values when loading a year of history — on the first try! We were able to map hundreds of legacy accounts into new accounts and five different Dimensions, each with multiple choices of value.
Tool 1: Rapid Start Import
Microsoft Dynamics NAV provides great tools for importing data from Excel. The first step in building the GL mapping is to use the import tools to load a General Journal Batch with two pieces of information for each of the legacy GL accounts. After extracting the GL account number and description from the legacy system and importing this information into an Excel spreadsheet, you can use the import to load a General Journal Batch in Microsoft Dynamics NAV.
- Load the account number to the Description field on the journal lines.
- Load the account description to the Comment field on the journal lines.
Tool 2: Personalization
Microsoft Dynamics NAV provides the ability to personalize page views. In order to make the mapping exercise easier, users can arrange the columns in the General Journal page in a convenient order. The image below is an example of mapping with five different Dimensions included.
The Description and Comment fields contain the legacy GL account information. The remaining columns allow users to select Microsoft Dynamics NAV GL Accounts and Dimension values.
Obviously, this view of a journal entry page won’t work for actually entering journal entries. Once the mapping exercise is complete, simply restore the defaults on this page and return it to a format that is more conducive to its intended purpose of processing journal entries.
Tool 3: Validation Logic
The General Journal is subject to system validation logic and controls the user selections for GL Account and Dimension values. Users can only enter a valid selection. This prevents typographical errors, to which a freeform entry method (i.e., in Excel) would be subject.
Another benefit of this approach is that users have access to the lookup functionality that is part of normal journal entry logic. When you’re not sure of eligible values, the selection list helps you make your choice (see example below).
As the map is built, users gain access to filtering and sorting on the journal page to assist in isolating lines yet to be completed.
Tool 4: Export to Excel
Once mapping is complete, the “Send to Excel” action will create an Excel workbook containing a table with the mapping information. The columns in Excel follow the same order as they are displayed on the journal.
The resulting Excel file contains the mapping that translates legacy entries to the new accounting structure (combination of GL accounts and Dimensions). If you load the historic information using the Rapid Start tools, then the Excel Map combined with Excel’s VLOOKUP function allows the creation of an import worksheet containing the Microsoft Dynamics NAV GL Account numbers and Dimension values required for each line to be loaded.
In a system conversion to Microsoft Dynamics NAV that involves a significant change in GL structure, the process described above could significantly help prepare account mapping information. While it will not guarantee the correct destination accounts, the selection ability and validation it provides should significantly reduce the potential for incorrect mapping information.