Dynamic Communities Magazine

This article offers a step-by-step guide for cleaning up your chart of accounts before upgrading Microsoft Dynamics GP.

Originally published in H1 2018 GPUG Magazine

It’s that time of year again: You’ve completed your budget, wrapped up your end-of-year activities,
and are diving into your 2018 goals. If one of them is upgrading Microsoft Dynamics GP, you probably want to clean up your chart of accounts before you move forward.

If you are like most organizations, the chart of accounts is ever-evolving and sometimes takes on a life of its own. In many situations, you have run out of numbers in a sequence or your numbering is out of order. While the task seems daunting, it’s quite easy given the way Microsoft Dynamics GP is structured and the Account Modifier/Changer in Professional Services Tools Library (or PSTL for short). PSTL allows us to change accounts one by one, a range of reports, or to simply import our changes. (Bonus: Starting in 2012, Microsoft started providing the PSTL for free!)

Lucky for us, Microsoft Dynamics GP doesn’t store the actual GL account number with each transaction. Instead, it stores an “account index”. In short, the GL account number you enter is actually a label or description for the account index. Therefore, when we talk about changing the chart of accounts, we are not affecting the actual transactions. We are only changing the “label” for that account index. It works very similar to what you have on the vendor card, for example. You cannot change the vendor ID, but you CAN change the vendor name.

The difficult part, and the part Microsoft Dynamics GP can’t help you with, is restructuring. However, once you’ve laid that out in a simple Excel spreadsheet, you are ready to go.

STEP 1
To start, I recommend pulling a very simple SmartList into Excel. The account description column isn’t needed, but it does make it easier, so I suggest adding it for now.

STEP 2
Populate the Excel template with the new account numbers.

STEP 3
Remove the Account Description column and the header row. Then save your file as a tab delimited file. The end result should be Column A with the old account number and Column B with the new account number.

STEP 4
Take a backup of your company database and print (to file or hard copy) a copy of your trial balance. (Both actions are not required but STRONGLY recommended.) Log into Microsoft Dynamics GP as “sa” with no other Users in the database.

STEP 5
Access PSTL and check Acct Modifier/Combiner. Hit Next.

STEP 6
Select Import and Validate.

STEP 7
Navigate to your text file and select Open.

STEP 8
Print and review the Invalid Accounts Report. You will need to correct any errors before proceeding. However, once you get a “clean” report, you are ready to move on.

STEP 9
Once the process is complete, you will get a series of windows. You will want to continue through each and review the report listing all the modified accounts. Note: When prompted to combine accounts, select Yes. It is a good idea to print the resulting report to file or hard copy for audit or historical reasons.

STEP 10
After exiting PSTL, reconcile your years starting with the oldest historical year first and ending with the current open year (Financial > Utilities > Reconcile).

That’s it! If you run a new SmartList, you will note the old account numbers are replaced with the new account numbers.

A Few Things to Keep in Mind When Doing This

• It is ALWAYS a good idea to run this in a test company prior to doing it in your live company database.
• Once the change is made to combine accounts, it cannot be undone later. For example, if you currently have one account for travel expenses and one account for entertainment and have decided to combine all the historical and future transactions into one account for travel and entertainment, you cannot separate them out again in a few years. Once they are combined, there is no indicator that the transactions were originally on a different account index.

• After the change is made, you may need to modify your reporting tool (i.e. Management Reporter) to pull the new accounts. The method I have found as most efficient is to make the changes to a test company. Then rewrite the MR reports while pointing to the test company. Compare the results between the live/current reports to the revised test/modified reports for accuracy. Make the change in live and point the modified reports to the live company.
• If you are making major changes to the account structure, the account descriptions, posting type, account category, and/or typical balance may need to be updated as well. Since the PSTL tool will simply pull these fields from the original account to the new account, you will need to use another tool to make these corrections. In the past, I’ve used a macro when the number of changes is significant enough to warrant it; otherwise, manually updating them is an option as well.
• If you are looking to expand or decrease your account segments or segment length, there are some additional steps, so look online for the additional steps or contact me for recommendations.
• Before considering expanding the number of segments or the length of the segment, you will need to verify what was set as the maximum at the time Microsoft Dynamics GP was initially configured. This can be checked at Administration > Setup > Company > Account Format. In the example below, Microsoft Dynamics GP was configured at the max allowed, with a total account length of up to 66 digits and up to 10 account segments. At present, the account length is nine digits with three segments. We could, however, add up to seven more segments or expand any of our three existing segments up to six digits.

Good luck and happy upgrading!