Payment Reconciliation in Dynamics NAV

Recently, Encore hosted an event: “What’s New in Microsoft Dynamics NAV 2017”. We presented the best new Features in Dynamics NAV 2017, including payment reconciliation. enjoy!


Video will start playing at the correct point in the presentation.

The next new feature I’m going to show is the Payment Reconciliation. So how does your Payment Reconciliation differ from your bank reconciliation? I’m going to start with a demo on how the auto bank reconciliation works in NAV 2015, and then I’ll lead to on how the new Payment Reconciliation functions works in NAV2017….

So in NAV 2015, Microsoft has introduced this new bank reconciliation function called Auto Bank Reconciliation…Function is very to use. Basically, we start with the bank reconciliation screen. Click on New to create a new bank rec, and then you have to select which bank account you want to use there…And then you go online to download your bank statement from your bank.

Your bank statement can be either in the SEPA format, a Single Euro Payments Area format. Or it can be in any type of formats. So in this particular case, I have text file for my bank statements that is in a comma-delimited text file. You get to setup the mapping in the data exchange definition table. So based on those two files, I will do an import function. So I’ll import the bank statements…Click to choose the file…And once the system read it through, it will populate the bank statements line.

And then from there on, I have couple options there. I can click on Match Automatically where the system will pop up a prompt screen asking me what kind of tolerance date are you looking at. Do you want to make sure the transaction match if it’s within a three-day time difference or five-day time period? I put five there. Tell me that up to 246 lines that you have imported, five of them is a match.

So the way it’s matching is based on amount. So I’ll work through the rest of the line here to match up to my bank ledger entry. So for those ones, as I say, a miscellaneous charge, bank charges, I can click on the line and send it to my General Journal, Transfer to General Journal for me to record the miscellaneous charges that does not get post to my account until I actually see the bank statements there. So that’s how the bank reconciliation works.

So now in this particular case, I have a long statement and then only five line matches to my account and the main reason could be that my customer simply deposit dollar amount into my bank without telling me too much detail about sending it or sending me a paper copy of what are the invoices that they apply to. Or that my vendor simply draws the amount because we have a auto payment agreement with them that I don’t normally record in my system until I see the transaction.

So that’s when the Payment Reconciliation function comes in handy…So I see this being particularly useful in terms of a Visa card, a purchase card kind of reconciliation now you do on a periodic basis there. So I click on New to create a new payment journal. I’m going to select a different account just because this one there has a little bit less entry for me to reconcile than the other one. I’ll click on the Import Bank Transaction. So it’s the same file that I use to upload to my bank statement. I can do that over here on my Payment Reconciliation Suite. Click on Choose to pick my file…And click OK to import.

So over here I have a bunch of line imported into the system there. And I can see that the bank actually given me some detail based on the transaction. So a customer has sent me a transaction for \$4,000 and it was applied to invoice this, this and that. So I click on the Apply Automatically function. It gives me a pop-up message saying that six payments line up out of the nine lines that you imported has been applied.

So the system couple is doing of couple of things here. So for the entry that has the descriptions and there’s invoice here. It’s trying to match the descriptions up and match the invoice number up here. So over here, this \$4,000 actually applies to three different invoices within the system from the same customer.

So upon posting this payment reconciliation, the system is doing three things. Well, first, reconcile the transaction. Second, it creates the payment entries to the customer. So it creates the entry on the customer side. It applies the entries against the invoice, and it does the bank reconciliation for you. Because the transactions imported from banks, so as soon as I match it up, it’s doing the bank reconciliation for me.

Another different type of transaction here is…I actually did a deposit to my bank a couple of days ago, and it was from three different entries here, so two checks and then a petty cash deposit. The system also try to match it up because it’s the same amount for \$3,500 here is trying to match it up, and doing the reconciliation there. In this particular case, it’s not creating additional entry but just to reconcile the bank entries there.

I can also do some mapping here to tell the system that consistently the service fee for a copy machine always shows up on my bank statement and I actually want it to go to a specific G/L account. So when I receive the payment, all I need to do is tell the system that, “Hey, this particular \$50 transaction should be posting to an expense account that I have there because it’s recurring or it shows up all the time.” So things like Starbucks transaction, the Visa card or the fuel Petro-Canada or on Chevron transactions on the Visa, these are all easy to recognize text that you can map to specific G/L accounts.

So over here for this particular transaction, the system is automatically matching it up to my Office Supplies because it’s copy machine, see there. For those ones that is not matched, I can click on Show Non-Applied Lines there. So there’s three lines that’s not matched.

Over here I get to decide what I want to do with it. So if I know that this is actually a invoice, a payment from customer that I haven’t created the invoice for, I can actually click on Transfer Difference To Account. So I can say, “Transfer this particular amount to one of my customers there because I know the customer made the payment there.” And I can do the same here. I can transfer this to a vendor or G/L account or to bank account here.


So now all my lines has been matched up. I can click on the Test Report…You see, kind of like your Bank Reconciliation Report, it shows you the lines that has been matched and the lines that’s not matched. Once I’m happy with it, I can go post. So, post the payments to the account, and post to reconciliations to the bank there.

So the benefit we get there is very efficient payment reconciliations. There’s no double work. We don’t need you to post the transaction once and then go into the bank and do the reconciliations again, kinda avoid error from happening there. If you have Yodlee setup, you can actually just download the statement from there and create your bank payment reconciliation there without having to import the statements here.

Which Dynamics Product Is Best for You?

Answer some basic questions about your company and your requirements, and find out what products would fit your business.

Take Our Quiz

Which Dynamics Product Is Best for You?

Take Our Quiz