Dynamics GP – AP and AR Close

The closing processes for AP and AR are mirror images of each other. During this recorded session, we explain the process to close Accounts Payable (AP) and Accounts Receivable (AR) monthly. We also share some tips and tricks throughout the closing steps. The intended audience is Dynamics GP users of all product versions.

Transcript below:

Melissa: Hello everyone, thank you for joining us for our Dynamics GP Coffee Break, AP and AR Close. My name’s Melissa and I’m the facilitator today. So 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. This session will be recorded and posted on our blog as well as shared with you later this week. Now, I would like to introduce our presenter today, Don McNulty, who is a solutions architect on our Dynamics GP team at Encore. Don, over to you.

Don: Well, welcome everyone. Hopefully, everyone has a good morning. So, today we’re gonna talk a little bit about AP and AR Close, something that the accounting departments have to do on a monthly basis and what do we balance and how do we balance and things like that. So, lemme get started, introductions. We’re dealing with fiscal periods, so we’re talking about what happens at the end of a month. So, it’s not going to be May 1st in a lot of cases, in a lot of cases it’s gonna be like May 5th or May 6th that you’re gonna do April except for like and things like that. So that is…it is a fiscal period that we can control. And we can soft cloth that, and I’ll quickly show you that. What reports are you required to do? You’ve got the age trial balance, historical age trial balance, and you’ve got some smart lists and things like that that I’m sure you run today in order to figure out whether or not you’re out. There’s also some Reconciles to GL.

So what do we balance? We’re balancing our sub-ledger to the general ledger. So the general ledger says we’ve got X dollars in it, we should have exactly that same amount in our sub-ledger, okay? We have a Reconcile to GL tool that’s built into GP, which I’ll be showing today and it works for both AR and AP. It also works for inventory, it works for bank rec as well. So those are other areas which we’ll be dealing with in other seminars. And then lastly, there is a little brief description about a third-party tool that does a lot of this for you, automates some of it. It does a very good job and shows you where some of your differences can be.

All right, so what are we…what reports and what do we need to do to close GP? So, at the end of each period, we need to run either an age trial balance with options or an age historical trial balance. Now, the difference between the two is where it gets its information. So, the age trial balance with options is a much faster report to run than the historical one because it only looks at outstanding transactions. The historical one goes back to the beginning of time and recalculates what was outstanding as of a specific date. So, if you run your age trial balance, before you let anyone post into the next fiscal period, you can use the age trial balance with options for speed. As soon as you’ve done a transaction in the new period, you can no longer rely on the age trial balance with options because now that transaction has either gone to history or is no longer in the open file.

So there’s no date range limitations with options, and there is a date range limitations on the historical age trial balance. Most accounting departments end up running the historical one because we have to open up our next period before we finish the period before. Just from an accountant’s point of view, that tends to take 5 to 10 days depending on the different people. We got the GL summary report and then the GL summary report and then the currency issues that you may have if you run multicurrency in your system as well. What can we get in Excel reports? Well, there’s a couple of these that we can actually get out to Excel in a reasonable format as long as you’ve got your SQL reporting services running. So we can show you…I’ll show you that as well. And then lastly, I’ll show the Reconcile to GL.

There’s a Reconcile to GL function inside GP, which allows you to create a spreadsheet which tells you what transactions it thinks it’s matched up, which ones it thinks it potentially has matched up, and it tells you which ones have not matched up. So you can take a look and it’ll find a lot of your problems for you without having to do too much work. It’s kinda like the third-party product, except that it is built in. The difference is that the Closer, which is the third-party product, goes a little deeper into the data than the actual reference out to the GL. Okay.

So, the last piece is the third-party assistance, there is a piece called the Closer, it’s by Reporting Central. It runs 1500 bucks a module. So AR, AP, bank rec would be three modules. You buy three, you get ’em for four grand, if you buy all…if you buy all of them including inventory and receive note invoice for the POs and things like that, you get the whole set for 5,000. So, it is a nice product. And we will post a link with this in the blog that can take you out there. Just have to talk to your account manager about that if you’re interested in doing it. You’re having problems closing and spend a lot of time each month then having to close because of issues on inventory and sales orders and purchase orders and things like that, this is actually a very, very good tool. Okay. All right. So I won’t waste too much time. I’m gonna show my screen…I’m gonna show GP. So, there we are. Melissa, we’re okay on the screen?

Melissa: Yeah, it looks great.

Don: Perfect. All right. So, this is regular GP, nothing fancy about this. What I’ve done in this one here, this is fabric cam, this is the sample company. The sample company’s data is out of balance, intentionally out of balance so that when you show this, it actually can show some transactions. Most of the time you’ll be in…hopefully, you’ll be invented balance in this one. The big thing about this is the date. So, I’ve got the date set at the bottom here, and I’ll just bring it up, as 05/01/2027. So, my date is all set in 2027, and my database has got 04, 20/04 is the active month.

So I’m going to the next month as if I was gonna close my books for the month of April. So, what I’ve done is I’ve moved the date to 05/01, so I’m in May, which means that when I’m looking at reports, I want everything up to and including last month. I do not wanna see…I do not wanna see stuff that happened in May. So, once I’ve got my date set…and this date would automatically roll for most accounting departments, it’s May 2nd, May 3rd, or whatever it happens to be. So, the first thing we need to do since we go into smart list, and what we need to know is…we need to know a couple of things.

First off, what is the AP control account? Most of you already know this. By the way, to find it is you can create a smart list with vendors with control accounts. I’ve just added the accounts payable account number and the accrued accounts payable to give you both the AP and the…AP and the accrued AP number. And as you can see, mine happens to be set to 2100.

So, when I look at my general ledger, that’s the account number that I want to balance too. So I’m gonna balance all of my transactions backing to 0002100. You may have multiple control accounts, one for Canadian, one for U.S. or one for…international one for, one for U.S. or whatever it happens to be. You would need to know that you’ve got multiple control accounts, and the accounting departments know that. So when I go into here, I now have to…run my report is what I need to do. Now I’m gonna deal with purchasing today. And although I labeled this seminar AR and AP, everything I say about vendors also applies to customers. So, exactly the same format of reports, the trial balance with options. And the historical trial balance work exactly the same way between sales and purchasing. So, all my vendors and my customers work exactly the same way.

So if I wanted to get a customer list here instead of with control accounts, what you would do is you would go into your sales, you would grab your customers, like so, we would go to columns and we would just go into here and go add, and we want the accounts receivable account number. And we go Okay. And we go Okay. And that will add that to here. I now know that my account’s receivable account number is 1200. There it is there. And we hope that all of your customers have an accounts receivable account number. If you are using your main posting accounts as your control account…as setting your control account, you would need to look it up underneath posting and posting accounts.

I use [inaudible 00:09:15] classes so, therefore, is sitting here, which is the recommended method of doing it. So, I know it’s 1200, and I know it’s 2100. So having…so I now know that. So then the next question becomes, what reports do I need to run? So the first thing I need to do is I need to run a trial balance. There are two types. So inside here I’ve got an age trial balance. The age trial balance inside the closing here will print every single vendor that you have, whether or not they owe you money. There’s no filtering on this for people that you don’t owe money to. So if I got 500 customers, I’m gonna get a report with 500 sections. Probably don’t need it because I probably only owe 30 or 40 people, or maybe 100 people. And so what I wanna use is either the options or the trial balance.

So when I go into my options one here, I’ve got a close today one, and I’m gonna modify it and I’ll show you what I’ve done. Let’s go see it. Under my close today, I said print as of the current date. The reason I want the current date is that this is an open file, so I know that this is going to be accurate as of today’s date. Down here there’s no date range that we can…that will do anything down here because it keeps it only to the thing. It does have a date range at the bottom, but that date range only looks at unpaid transactions.

So, if I’ve written a check on the 1st of May and I posted it and I pay off an April transaction, it will remove it from this report yet it was still open at the end of 4:30. The historical trial balance will not. And that’s the difference between the two reports.

When I run this, because I’ve got nothing posted in May, I’ve got my destination set to screen for all my reports so I don’t need to worry about it. And I’m going to hit the details so I can see all the detail, and I’m gonna hit print. And when I hit print, I’m going to get a report that is…I think it’s about 21 pages or something like that. There it is there. And on this list here, I now have…at the very bottom of this list, I have a total, I have a balance current period, 30, 31, 60, etc. So this here totals up $1,339,000. So that would be the number I would expect to find in my general ledger as of today. And it’s also the number I should find on my historical age trial balance when I run it for April because I know I’ve done no transactions in May. And I did that intentionally for today so you can see it.

This tells me who owes me the money and things like that. I can run it in detail and in summary. If the detail and the summary give you different responses, very rare but is possible, you need to run a Reconcile against your vendors or against your customers accordingly, okay? So I’m gonna close this one and I’m gonna run, and then… Because I’ve got multicurrency turned on, I can see that I’ve got some in blank currency and some in a regular currency giving me the same 1337 is in U.S. dollars, the rest is in other currencies.

The currencies are usually listed, this is the bad sample data that Microsoft provides me, so I can fix some of these things, so… Now, when I move out to here, hang on, say we save on that one. If I go to my age trial balance with…historical age trial balance, when I go to my month and close, I now have a few more options in the middle of the screen. I can select my transactions for report using the posting date or the document date.

Ordinarily, you wanna use the GL posting date because the GL posting date is what’s gonna affect your general ledger, and this is what you’re trying to balance too. If I get a transaction in April, but it’s for a January transaction, I’ve already closed January financially and I’ve issued my financial statements, I no longer want to include that transaction in the January information yet the document date is still January. If I select a document date, it would push it back into January and it may or may not be outstanding and you need it to be because of dates and things like that. So ordinarily we use the GL posting date.

I’ve entered in here, enter end of the previous month, and down here I’ve got date from 1st to the end of the previous month. And the way I did the end of the previous month was inside my date, I just leave this as blank and then underneath here, I can go end of the previous month or previous period. If your month…if you don’t use periods, that’s what we’ve got there. So I’m gonna leave that there. This is looking like so. Again, I’ve got it set to the screen. When I hit print, it will print my report and this is the same 21 pages, and will give me, I’ll check this, same number, 133,916,411. That’s the same number. So that’s the number that I would want to find in my general ledger. And this is as of April the 30th.

So I go, okay, so that’s what I should have in my general ledger. So then what I would do is, and there’s my…this is showing Australian, Canadian, and U.S. So this is the…those are miscellaneous currencies. They’re sitting here in the posted file, so there they are. I know how much in each of these that I got. I got about the same number, there we are. So once I’ve done that, I’ve now got a list of this is what it should be, I now need to go out and I need to check to find out whether that’s what I’ve got. So I would go in to my financial and I would go into summary, the standard summary report, accounts payable, and period four, which is April, I have $1,316,923.

Well, that number is not the same as what I had in my general ledger or that I had on my sub-ledger. It should be the same, it should be identical. It needs to be, and you need to balance this and figure out what the differences are because if your sub-ledger says you owe $1,311,000, then that’s how much money you owe. You’re not paying somebody the extra $4,000 or $5,000, in this case, to somebody who you don’t know. So we need to adjust the general ledger in order to make it work. We also need to make sure that we understand why it’s out of balance.

So now we’re gonna go and take a look at what we do in order to show those differences. Now, the biggest difference is usually because somebody has posted to the wrong fiscal period, okay? Now you can control the fiscal periods, you probably all know this, but underneath admin and under set up and company, you have fiscal periods. And inside your fiscal periods, you have the ability to shut down and open up different periods.

Now, most people, what I would do with them is we would do this, we would close off and then we would open up the period that we’re dealing with, right? So this would stop people from posting anything into May and also stop them to posting into March. And this is my March transactions. I brought my March financial statements, I don’t want anyone backdating anything. I don’t want anybody putting stuff into the future, so I close down 5, 6, 7, and 8. And then the next month as I close purchasing, I closed this and I reopen for the next period. I just walk my way down this table.

This is a simple open and close button, you just click the buttons and it opens up the two periods and there you go. So this is how we can control what goes in. You can also do it for inventory. Payroll, that is U.S. payroll only, it does not affect Canadian payroll at all. And then project accounting, of course, is what’s on outside [inaudible 00:16:50]

I’m going to reopen all these just so that I’ve got them for my demos. So there we have [inaudible 00:17:00] remember. So, there we are. So, that gives me the information that I require in order to look at this. And well, I know that I’m out, so now what I wanna do is I wanna run this and I wanna reconcile it to the GF. So underneath routines, does it Reconcile to GL? When I bring up the Reconcile to GL, I have a choice of payables, receivables, inventory, indictment.

Now in my case, I’m gonna do the payables. I would then type in my 2100 in my account. If I have multiple GL codes, I must include all GL codes that are possible inside my sub-ledger. The Reconcile to GL does all the sub-ledger at once, so it needs all the financial transactions at once. So it might be that you need both U.S. payables and the Canadian payables on the same sheet. Reconciliation date is going to be today’s date, matches up automatically. And then inside here, I would pick the period that I’m looking at.

So I’m out of balance in April, or I believe I’m out of balance in April. So I’m gonna look at this, I give it an output file. You can put it anywhere you want. My suggestion is on these outputs, that you put them to the…either into the dying data folder, which is a shared folder across all GP users. Yours might be called Dyn-data, it might be called Dynamics data, it might be called Dynamics depending on your installer back when you first loaded GP, way back whenever it was. And then it will automatically put in the file name for you there. And if you wanna change it, just push the yellow one to change the direction that it goes.

I’m gonna leave it where it is and I’m gonna hit the process. And when I hit process, it’s gonna do the calculation down inside here. And I can move this to the side. And you can see inside here, I’ve got general ledger and then it provides me this list. Now, I don’t know why my particular report is doing this, usually it formats it quite nicely, but I’m gonna just double click on the with the [inaudible 00:18:54] so you can read them. There we are. So now…whoops, let’s move on for now. So now I have a list of unmatched transactions, potentially matched transactions, and matched transactions. So let’s talk about each of these. These are the transactions that went through the sub-ledger and through the general ledger in the last month.

You’ll notice here that the dollar values match, payment numbers match, everything matches going across. So, the match transactions lists off all the transactions that there is a transaction in… Let’s go to the top. So this is your general ledger and this is your account…this is your account’s payable. So, this is the vendor of the transaction source and the voucher number, over here, this is your general ledger information. So, we’ve got everything that we need inside there, but we go, “Okay, why do I have these unmatched transactions?”

Well, we look at this one here, we say, well, this particular receiving transaction is in my sub-ledger, but it is not in my general ledger. So, the first thing I would check is to check the posting. So when I go into my batches inside here, and I do a lookup for my batches at my GL, I go, “Oh, I’ve got a receiving transaction.” And if I open this fee receiving transaction up, then I go with transactions, and I look at this transaction, let’s see if I can find it, receiving transaction, there’s the $45, and that’s the same $45 that you see right here.

So what I would have to do is that…what I need to do is I need to go into here, and let me just close some of these down through the batch entry, bring up the receiving transaction and go post, and close that. So now that it’s been posted, let me make sure everything’s closed, payment and summary.

So now, when I come back to my Reconcile to GL, I can run this again, just have to hit save, it’s ready to go. It’s got a five here, so the reconciliation number becomes part of the file name. So I can run this again, I can hit process. And what it will do now is it will run us another spreadsheet and that particular one will be broad. Oops, sorry. And you’ll notice that I have no unmatched transactions. So, I found that transaction. You have to look at why is it not in the GL but it is in the AP?

So then we look at these. Now, this is a potentially matched transaction, and by looking at it, it looks perfect. This one looks perfect, this one looks perfect. As we move down, these all look good until I get down over here. Okay. At this point here, I’ve got a $27,000 invoice, journal entry number 3338, and I got a $29,000 check. So, how is it that I’ve got $29,000 coming out of my sub-ledger, but I’m putting it to 27,000 in there? So if I wanna see this transaction, I can do one or two things. If I wanna see the actual voucher, the document number, I can just click on here, and it will ask me to open up. You must be in GP in the right company. And there’s my payment.

And so now this is looked up into GP, it’s gone in and it said this is the 29,265.50. Now when I look at my distribution, my account’s payables only got 27,996.94. And that’s because I’m using what they call discounts available in the GL. So, I would have to include that account, the 2105, in order for these to match up properly because that would be an issue. We would have that 1,286 is in the GL, but the sub-ledger would be [inaudible 00:23:00]. I’d have to add the 2105 because these were posted with discounts available.

Now, I’m gonna say most, 95% to 99% of the customers out there have their settings in GP under purchasing. And that particular function comes from right there, track discounts available in GL, I think. So, the first thing I do when I set up a demo is I turn that off so that all my transactions balance properly. And it’s the first thing I do with almost all my new clients and all clients who ask me to look at their cable system, I go, you know, very few people track discounts available, like very, very few. So, that provides me the information. So now I know why this 2796. So now I know that this is…they’re both correct. So now I have an offsetting entry. So I would then need to go back to my Reconcile to GL. [inaudible 00:24:02].

You can add 2105 to it, and now I can hit process. And now you’ll see that the match transactions has now dropped significantly. Okay. And as a result of that, because I’ve now got two GL codes, it’s adding the GL codes together so that we end up with net numbers. So in the long run, this will actually balance through. If I scroll to the bottom here, you’ll see there’s that tell set for $45, went to the very bottom, last transaction. And up top here I’ve got no unmatched, and these ones here, 102, 103, 326.

Now this one here, these two together, when I add those two together, let me just move my Excel sheet up a bit, is 197. So that balances 31, 36, 300, 330. So we should be in balance. Now, we’re probably not because my historical balances in my balance sheet are not valid in Fabricam simply because of the way that the sample data was provided to us, and we don’t…I don’t actually clean it up. But that is how you do it.

Now this works also for accounts receivable. There is no difference on that. All right? Now, for those of you using multicurrency, you have two numbers that you need to balance. You need to balance GL like this. This is only in functional currency. So this would be in Canadian dollars if you’re a Canadian company, U.S. dollars if you’re a U.S. company.

If you’ve got foreign currency transactions, you need to balance, not only do you need to balance this by virtual currency, you also need to balance your AR and your AP based upon your currencies as well. So there we are. All right. So that was…

Oh, so the next question people ask me all the time is, how do I get my age trial balance into Excel so I can store it as an Excel file and I can read it? So there is a way of doing it. And what you need to do is that you have to use reporting services. This is the reporting services reports from SQL reporting services and SQL reporting services can be loaded from GP. Now there are both historical, and options. All of the activity reports are available inside there in all the different areas, including…so purchasing, payables, etc. And we can hit that and it will come up with a list.

Come on, today would be good. Let me go this way. Why is it taking so long? We were flying about 20 minutes ago. All good demos have issues, right?

Melissa: We do have a couple of questions here, Don, if you wanted to tackle those.

Don: Yep.

Melissa: Okay, great. I’ll read the first one. So, if someone posted to an erroneous payables control account, how would we know what account to include, i.e they overrode the account number accidentally when recording a voucher?

Don: Well, what would happen would be…is that the voucher would show up on your Reconcile to GL, and it would be unmatched, there’d be nothing on the other side to match it with. You would drill down on that transaction and it would…you’d look at the distribution and you would see the one that says PAY, that’s the one that you care about. So, when I look at these… So I’ll show…let me show that in about two seconds. So inside here, we’ve got a historical age trial balance and an age trial balance like so. If I click on the historical age trial balance, it comes up with this, we’ve got vendors, we’ve got a selective value, I’m gonna go GL posting date. I’ve got 1900 to…lemme take that date, I gotta put in your date, 04/30/2027. There we are there, and we go with that, give you the report.

The advantage of doing… This is exactly the same report that we just saw, the only difference is it’s been run in SQL Reporting Services. But the big difference is that underneath here we have the ability to save it to Excel. So that’s the reason that we want to use the SQL reporting services. And then, as you can see through here, this is this thing, the same number. If I go to the very end, let’s go to the last page, 21 pages. When I scroll down here, I have exactly the same information that I need. Oh, I’m missing a bucket. So, we’ve got the information that we need.

So that’s the SSRS reports. And that’s available in both sales and in purchasing, so both are available. Now you asked about that. So, if I was to go into…so for instance, I go cancel and… Let me go into my Reconcile to GL again. And I’m gonna take 2105 out here. Click Save. Or actually lemme just go back to this transaction. So inside here, what would happen is that you would get a transaction like this one here, and it would be underneath unmatched, and you’d have nothing on the GL side because it wouldn’t match your control account. So you go, “Well, how come I got one?” I found it because it was unposted. But if it was not…if the GL code was not valid, what you would do is you could drill down on the voucher here, bring up the voucher, and then when you hit the distribution, you look for the word pay.

The pay is what is showing in 2100. So that’s 27,996. In this case, that GL code could be something else. So if I was to go out to my payables, I can actually create that instance, but I don’t have time today. But that’s how you would find it. You drill down on the voucher and then look at the actual distribution on the voucher that’s not matching, and it would tell you this is the GL code. Then, at that point, you could then say okay, now I need to do…either I have to avoid this transaction in AP and reenter it so that it posts correctly through the GL, or I can do a journal entry and know that that’s gonna be a differential…it’s still gonna be out on this sheet, but it won’t be out in the GL, you’ll have another one underneath GL side, which is a GL entry only. So that’s how you would do that.

Melissa: Okay, we’ve got one more question here.

Don: Sure.

Melissa: The Reconcile to GL for AR takes forever to run, what can I do to make it run faster?

Don: Date range is the biggest one. Are you running it…? If you’re running it for a single month, how many…? It’s a matter of transactions, however big the transactions are. So when I go into receivables inside here and I put in my 1200… Is it 1200? I think so, right? When we type in a date… If we type in… I can make this go back in time. I can go back to 01/01/27 like so, and when I select here…where am I here? Save the same spot and I hit process, this will take longer because it’s got more transactions.

So to limit the date range would be the one…would be the first comment. And that is one of the reasons that people move to something like the Closer. So there’s this. So you see this one here, I’ve got a standard invoice that has not… I don’t know what this one is. You asked me how to find it, I would click on it here, I’d go, yes. Oh, it happens to be when it’s [inaudible 00:33:00] but the history’s been removed, my demo system is not great.

So inside here, you would just drill down on these particular one and you can see which ones there. These ones are all matched. We’ve only got the one that’s out of balance, okay? But to speed it up, date range is the only thing you can really control. Other than that, it takes as long as it takes. Having Excel running, launching Excel, if you have third-party add-ins for Excel like Jet or something like that, make sure you launch Excel before you run the Reconcile. If it’s already open, it uses the same instance, it doesn’t have to restart it, that saves a little bit of time. But mostly it’s date range. And the Closer would be something that you could potentially look at. It is a lot faster because it does it at the SQL level rather than trying to extract it through the work solution. Okay? Any other questions on the list?

Melissa: No, that was it. Looks like we’re running a little late here, so if we’re able to wrap it up.

Don: So, yes. So let me just pop back to my… Here we’ve asked the questions and… So, we’ve got a couple more of these coming up. One in December for fixed assets…on running fixed assets on a month-end basis like what do I do for the…? How do I generate depreciation, what do I balance to, and things like that. Figure January was a good month to show the GL closed. That’s the once-annual piece that people do at the end of each fiscal year. And then on the 14th of February, I’ll be doing one on bank recs. So, hope to see you all there and thank you.

Melissa: Great, thanks. Yeah. So the 2023 webinars are not yet up on our site to register, but they will be up there very soon. And yes, thank you Don for all the information today, and thanks everyone for joining us, and have a great rest of your day.

Don: Thanks very much.