Dynamics GP Account Structuring – Chart of Accounts

Dynamics GP Account Structuring – Chart of Accounts

Many companies grow and change over time and need to update their Chart of Accounts in Dynamics GP. During this recorded webinar, we discuss how to plan out a change to your Chart of Accounts and the tools and options available to assist you.



Topics discussed during this session include:

• Introduction of topics:
  • Chart of Accounts in GP – 1:00
  • Account types – 1:25
  • Segments – 1:50
  • How to plan out a change to your existing Chart of Accounts – Why, when, what else needs to be done – 2:20
• Changing Existing Chart of Accounts – 6:10
  • Issues to watch for
• GL Changer – 7:30
  • Single accounts vs. Multiple Accounts
• Demo of how to change your existing Chart of Accounts – 7:50
• Q&A – 22:50

The intended audience for this video is existing Dynamics GP users on versions of GP 2013 or higher.

We highly recommend you do a backup before doing this process, because if something goes wrong you must reload from a previous copy of Dynamics GP.

For future Dynamics GP Coffee Break sessions, please see our Events page.

Contact us if you have any questions about Account Structuring in Dynamics GP.

Transcript below:

Tess: Hello, everyone. And thank you for joining us today for our Dynamics GP Coffee Break about account structuring. My name is Tess, and I’m a facilitator. If you have any questions during the presentation, please type them into the questions area in the control panel on the right-hand side of your screen, and we’ll answer questions at the end. The session will be recorded and posted on our blog later this week. I’d like to introduce you to our presenter today, Don McNulty, who is a solutions architect on our Dynamics GP team at Encore. Don, over to you.

Don: Hi, there. Good morning, all. So, today, we’re going to talk a little bit about the general ledger and what happens to a GL over time. So, basically, the agenda for today is we’re going to talk about the chart of accounts in GP, the different account types, segments, and how to plan an existing chart of accounts. So, most of you already have a chart of accounts in place, some of you are going to be happy with yours and some of you are going to say it’s out of sync and out of whack. So, the chart of accounts in GP is segmented. In other words, we have a number of different segments based on how you’ve got your system set up. The system I’ll be showing today has three segments. It has a division at the front, or has a department at the front, followed by an account, followed by a sub-account. And that’s the types that we’re going to be talking about. There are three types of accounts inside your general ledger. Most people know about standard GL account. But we also have unit accounts, and we have allocation accounts. And inside the allocation accounts, we have two different types of allocation accounts. One is a fixed allocation and one is a variable allocation. I’ll talk about those near the end of the session.

On the segment side, based on how your original load of GP was done, you will have up to a maximum of 10 segments and a maximum of 66 characters depending on how it was loaded when you first loaded Great Plains. Some people who have had GP for a long time may have smaller structures than that because it was a disk saving at the time. Currently, we tend to start all new implementations with 10 segments. So, then we come up how to plan a change. And, first off, why we want to do it, when we want to do it, and what else needs to be done? So, why do we need to do it? So, over the course of time, GL codes get added to your system. And they may or may not be grouped together properly, you may run out of space between them, so you’ve moved into a different section or things like that. So when you’ve got a chart of accounts, or somebody’s entered the same account twice, and now you’ve got data against both of them in history years, and you’re going, “Well, my meals and entertainment here underneath the 6,500 account is equal to my meals and entertainment under my 7,200 account. And I really don’t want the one in the 6,500, and I want it in the 7200.” So, that would be why you want to do it. When do you want to do it? Well, usually you want to…it’s not that important about the when as it is about why you need to do it from the point of view of your financial statement.

If it’s making your financial reporting much more difficult, because now you’ve got to pick up 6,500, and 7,200, and 8,600 as one number and then you do it again and again. And then every time you add an account, you can’t do ranges where I want 6,500 to 6,600. You may decide that it’s time to change my chart so that I can get the reports that I want. It also allows you to break things into different segments because segments in reporting allows you to do some very good things with management reporter and with Jet Reports in order to produce your financial statements. You can go, okay, I have two accounts, I have meals and entertainment, Vancouver, meals and entertainment, Toronto. Because I didn’t put them in two different segments, I put them to 6,700 and 6,701, very difficult to pull out all the Toronto ones, all the Toronto accounts, and all the Vancouver accounts at the same time, on separate pages and then summarize them into the total. So, these are types of things that can be done. So, that’s why we need to do it.

What else needs to be done? GP provides a tool for this, it’s called the GP account changer. It allows you to modify and/or change an account. So, you can take an existing account and renumber to a new number. If that number is not in use already, it will just change all information inside Great Plains to that number. It changes your audit trails. It changes your chart of accounts. It changes every posted transaction that’s ever been done back to the beginning of when you first loaded GP or since the last time you did a change. Now, what else needs to be done? Once you’ve done the change, you have to reconcile. When you reconcile, it will rebuild your summary account so that you can then get your information that you need.

The second piece that happens is, is that you can do a combination. So, in my instance, I had 6,500 was meals and entertainment, and I had 7,200 is meals and entertainment. I can take 6500, and I can merge it into 7,200, then it will repopulate 7,200 as if it had all those transactions instead of just the separated two. And that way, when you look at that number, you get the combined number all together. There’s a really big warning on this. Once you combine accounts together, you cannot break them apart, at least not automatically. You can do it manually and move every transaction one by one, but that requires writing and adjusting journal entries. So, you have to be very careful about that.

So, changing the chart of accounts. So, issues to watch for. The challenge is if I’ve got a chart of accounts and say it’s four digits long, and I’m moving to another four-digit code, you have to be aware about the order with which you do it. So, if we take accounts A, B, C, and we want all of them to go to D. So, in other words, we want all of them to go to D, we could go A goes to B, B goes to C, and C goes to D, and that would work perfectly. However, if the intention was that you wanted A to become B, and B to become C, and C to become D, then if you went A to B, then B to C, you would now only have C left. And then when you went C to D, you would only D left. So, that you would wipe out the three accounts and you’d only have one account and D account left. If you do it in the other order, C to D first, then C moves out of the way to D, B moves to C, and A moves to B, and you’ve now got B, C, and D. So you have to be aware of the order of operation when you’re actually writing the information out. Okay.

We can do single accounts, or we can do multiple accounts. But I think the best way to do it is to do a very fast show of it. So, what I’m going to do now is I’m going to just go…just go, just leave that. So, inside your Great Plains, this is all done underneath what they call Professional Service Tools, which is PSTL. It will show up down at the bottom here on your list. Now, this is an 18.3 version, so not 18.4, but 18.3. Works the same 18.4, works the same all the way back to version 2013. So, this has not changed in its functionality since then. So, when we go like this, so we go PSTL main, we have a list of all these different Professional Service Tools. The one I’m talking about today is the Account Modifier and Combiner. So, when we click on the Account Modifier and Combiner, we can go Next. Now, in order to do this, you must be logged into the system as the SA. It requires global access to your database. You can be the only one in, it will tell you if somebody else is in GP. Everybody must act while you run this, it runs in the background as in SQL, and you need to run it as the SQL administrator.

So, we go Next. And we get a screen that looks like this. So, we can either select by Range. If I click on Range, I can go, what’s the current division and what’s the current value, what’s the new value, or I can do it by a single account. So, inside here, if I wanted to do it, I can grab my Cash Germany, and I’m going to make it…and if I go 1108, you’ll see that 1108 is not there. So, I’m going to move Germany to the bottom of the list, underneath Singapore. Like so. I can go Select. And then all I have to do is hit Convert. Verify that you have a backup. Obviously, you should do a backup. We highly recommend that you do a backup because if something goes wrong during this process, you must reload from a previous copy of GP. So, we go Continue. If you’re combining accounts using intercompany transactions, please log in to the other companies and post the intercompany batches. So, if you do have intercompany loaded, and you have any transactions, all batches should be posted, if you can, all GL batches. Intercompany on both sides, so you’ve posted it on your local side plus the intercompany. We click on Continue. Now, it shouldn’t take too long, because I’m only given a single account. And, of course, it’s not going to work this morning, that’s okay. So, it will process through and it will go through all your different accounts.

Now, it does this based upon a field called Account and MBR. So, if your third-party application has got account number and MBR as one of their numbers, then it will be changed accordingly. And it will go through and it will change all third-party tables as well, that it finds that matches the criteria that GP set out originally. If your third party uses a different name for their account number, it may or may not change. So, you have to be aware of what you’ve got so that you can make sure that you can validate those particular features inside your third party. If you need help with that, please let us know. We can help you through that. So, if you want to get it done, you say, “Well, I really want to do this, but I’ve got WennSoft, or I’ve got Accountable Solutions or something like that.” Let us know we can talk to them and say, whether or not there is any tables that we need to be concerned with. Okay? If there are, we just have to write additional scripts to do this, it’s not that it can’t be done, it just takes a little more effort. So, as you can see, it says that it’s complete, please reconcile your year. When I go Okay, it will then print a report, and it should print a report. Next. And when I close this, it will print the report. And it tells me that 1104 went to 1110, then it combined. So, it changed the account, that’s all. So, it’s moved it through. It’s changed it over to this and it’s changed everything that was in 1104 and moved it to 1110. Okay?

So, that then solves that, then you have to do a reconcile. The reconcile is very easy. You go to Financial, Reconcile, and you want to reconcile the year, and you want to do them all. Now, if you run 18.2, might be 18.3, 18.2, and onward, you have this All button. If the All button isn’t on there, it’s because you haven’t upgraded your GP system to the current version, which supports that, or originally what we used to have to do is hit History, and then we had to start at 2020, and do 2020, and then do ’21, and work your way down. Now, I can do All at one function, then we go Reconcile, then we go Yes. Now, a caveat to this reconcile, when you do the reconcile, which may make a decision as to whether you wish to do this or not. When you do the reconcile, if you have any years where you have purged the detail information from your general ledger, i.e., 7, 8, 9, 10, 11, 12 years ago, you purge the detail transactions. When you do the reconcile, it will wipe out the summary transactions and make them all go to zero. So, you will only have summary and detailed information for where you have details. It may be an issue for you and it may not be, it depends on your instance. Okay? Here we go.

So, that’s finishes how it actually works. Now, you say, “Well, I don’t want to do them one-by-one, I’ve got 5,000 accounts.” So what we can do quite easily inside GP is that we can create an Excel workbook that allows me to change multiples at the same time. So, I can go into here, I can grab my accounts. And we just click on the Accounts here, and we bring up the account number, the description, and things like that, and I’m just going to bring up a few of them. And then I’m just gonna go Excel, and I’m gonna go Stop, you hit Excel. I don’t need the whole chart, you could dump out your whole chart this way, that’s fine. It will then start Excel, and you can then use this to put in your new account numbers.

And then once you’ve done this, you can use this file or a portion of this file. There it is there. So, what we do is we click this and we go Insert here, New Account. And then I’m just gonna go like this. Actually, let me just do this. Equals… Oops. You go equals left of this. We’re going to take 3, 4, and we’re gonna take 8 characters, 9 characters, and we’re going to add 01. Oops. Yes, yes, yes. I hear that. Okay. So, there it is there. I’ve changed it from 1100 to 1101, all the way down. So, there it is there. So, this would be my, I want to move everything from 1100 to 1101. When I do that, this 1300, 01 is going to move into here, it’s going to stay the same, 02 is going to move into 01. And that will be all that will happen. That will happen incorrectly, or potentially incorrectly.

But let’s assume that this is the file, I’m not going to actually run it. What we then need to do is we then need to store this as a Table. So, we take a copy of this table. Like so. And we do two things. The first thing we do is, is we remove the headers. Second thing we do is we remove all the information except for the first two columns. So, now I’ve got original accounting, column one, new accounting, column two. And now I can save this. And the trick here is, is that Great Plains does not see… I’m gonna Save As, and I’m going to go…and let’s go File, so we go Save As. I just wanted to have a copy to come back to. When I go Save As, I’m going to put it into my…let’s go Browse, and I’m gonna put it into my Dyndata, and Import to the chart. Now, this is the trick, the trick is, is that you must use a tab-delimited format. That’s the one stipulation that Professional Service Tools says is that you must use the tab-delimited format. Like so. And we go Save. And when it says that, it’s gonna say only supports a single workbook, so it’s only going to save the active one, and that’s the one that I’m on right now. And then we go Okay. And now I can close that. And I can close this. And now I can go back to my Professional Service Tools, click on Account Modifier and go Next. And now I can click on the Import button. I get two new buttons, one called Validate and one called Modify. I have to validate first, so I go Validate. And this is where I get to pick my account, my GP chart, then it prints the screen. And it will list any rows or sections inside your file that don’t match to your current.

So, in other words, if the GL code does not exist in column A, it will report it here. Okay? The second segment, as long as it fits inside your current account framework, it will not list it here either. So, this account is actually valid, no problem. There’s no problem inside here, like that. So, if I go back to my Excel file. Come on. Don’t stop on me now. Okay. It’s a delimited file. Okay, let’s go close, File, Open. Oh, it’s in my temp. Okay. So, I will have to do it this way. My apologies. So, it’s delimited, then you go Next, and it’s tab and we go Finish. There it is, there. So, what I’m going to do here is I’m going to change this 1300 to 1104. Like so. Now we know 1104 does not exist because we just moved it out of the way. So, we can go File, Save. Let’s close this. And then when we go Validate, it tells me that the invalid account is 1104. So, it goes through each of the GL codes that it finds, checks to make sure that they actually exist inside GP, and then pops it up to that point there. And then at this point, if I wish to run all of those accounts at one time, I would then just hit the Modify button here. By hitting the Modify, it does exactly the same thing as a single one, except it does it one-by-one. It does the first one on the list and works its way down the page. So, then you have to control the order of it in order to do that. Okay?

All right. So, let’s go back to the slide. There it is. And that was really all I had to cover this morning, nice, and quick, and easy, but it’s something that you can do. Now, one item that you have to be aware of is if you’re changing segments, if you’re changing a major segment out of the way, do we need to do anything in things like Canadian Payroll in order for the departmental coach to overlap properly? Do we have to do anything to the segmentation of your sites, if you have sites driving in overlay, we have to be careful about those. So those are things that you can ask us about individually as to what you got. Most people don’t change those particular accounts, because they’re already working properly, so they tend to leave them. But it has been known to happen. So, if there are any questions, Tess.

Tess: Thanks, Don. Yes, there has been a question that’s come in. If anyone has questions, feel free to type them in now. Don, this question says, what’s the best use of the old currently not used accounts?

Don: Of the currently, what do you mean by the best? The best use of a currently not used. So, if you’re not using the accounts at all, and you want to get rid of hundreds and hundreds of accounts because you haven’t used them in say 10 years, then what a lot of people will do is they’ll group them together. And they’ll say, “Okay, I’m going to create a single not used account called Do Not Use, or Do Not Use Profit/Loss and Do Not Use Balance Sheet.” One thing I should mention, I should have said that was that when a GL code is created, it is created with either a profit/loss or a balance sheet type. You can only combine profit/loss accounts into profit/loss accounts. And you can only combine balance sheet accounts into balance sheet accounts. So, you would then, if you had 100, say a whole department is no longer with you or a whole division, and you want to take all the accounts for the division, and you could move them into an account called Do Not Use Old Division 5, merge them all into a single account. So, you’d have one account only on your chart of accounts instead of a group of say 100 or 200, or however many there were in the original division.

That’s especially good if you don’t want to get rid of all the detail because you can’t delete the accounts if they have any history against them. So, assuming that they’ve all been zeroed out over the years, then you kind of want to get rid of them, but at the same time, you don’t want to lose all your history from 8 or 10 years ago on the other ones, you can’t delete specific accounts. So, this way you can combine them together into a single account, and you’ll have a single account in place. So, when you go and look at that particular year, it will still balance. That seems to be the most common way of handling it. It does require two accounts, one for the balance sheet items and one for the P&L line items. But that’s a very good question.

Tess: Right. Thanks, Don. That’s the only question that has come in. So, if you could go to next slide, I think we have our upcoming Coffee Breaks in the new year, I’m going to add our events page link into the chat in case any of those topics interest anyone attending today.

Don: These are going to be a walkthrough of the ways to get data out of GP. Basically, in sequence of the way you’re probably doing one and two already, but you may not be using three, four, and five. So, each one builds on the one before, so. But you don’t need to go to each one of them, you can just look at each one will be self-contained as well, so.

Tess: Yeah, we’re looking forward to hosting those in the new year. So, thanks for the information you shared today, Don, and thank you, everyone, for joining us. And have a great rest of your day.

Don: Thank you.

Read Next: 18 Warning Signs Your Business Need the Cloud

This post first appeared first on Encore Business Solutions.