Bank Reconciliations in Microsoft Dynamics NAV 2016         

What we want to go over today is bank reconciliations. These are always very important in accounting and can be tricky sometimes. But if you always follow procedures pretty well then it shouldn’t be a big deal.

However in NAV now we have several options, especially in NAV 2016 and it’s really important that you pick the right things when you’re setting up.

So we just started a new bank, Chase Bank. This is a brand new bank and we have some transactions already hitting this bank.

Setting up a Bank
I just want to go over how I set up this bank; I know I did that in a different video but I think it’s important to just touch base on that again and how we posted to this bank, then we’ll explain how we reconcile as well.

So if I go into this bank account here I have set up all the standard information for the bank.

The important part here is that we need to set up the last statement number. I’ve set that to be 0000 so when it actually creates a new Statement it will pick 0001, so that’s a good running number.

You might put more zeros if you intend having more than 9999 reconciliations.  That would be quite a few years but it’s important to fill out that field.

Also, another thing that you always have to do when you set up an entity that it books into the GL, you need to set up a Posting Group.

And in this case I’ve created a special posting group for this bank called Chase and if I look at that posting group that is being mapped to posting GL account 11550 in the Chart of Accounts.

This is important. It’s usually good or almost always if you want the bank reconciliations to work for you to have one bank account relate to one GL account so one to one relationship between the two. This is because NAV will reconcile the bank towards GL and if there are more than one bank hitting the same GL then your bank recs are going to be off. So this is important.

If I go ahead and take a look at this, I go into Advanced here.

And I will drop into the Chart of Accounts.

Just to show you how now when I look at my Liquid Assets I have something called Chase Bank this is just for that bank account.

And ideally we should turn off the direct posting here and I’ll talk about that in a separate video.

So now we have that, now we already have several postings to this account and I’m going to take a look at those right now so I go into the Ledger Entries.

I can see that I have about 8 postings. We have some deposits here all from customers and we have payments to lenders and then some more deposits.

Now the important part here is that this Open field here actually interacts with the bank rec. It shows Open if it hasn’t been reconciled. If it’s closed or ‘Open’ is not checked then it has been reconciled. So you can see not reconciled items on here.

Now if you want to see the actual deposits or checks you can actually go here for example in  Check Ledger Entries.

And you can see these are the checks that were written off, the check number etc. So not in Ledger Entries but in Check Ledger Entries.  And you can do similar for deposits.

Bank Reconciliation
I’m going to go ahead and jump right away into Bank Account Reconciliation, let’s see, here are the deposits.

Just quickly show you that.

And if I take a look at those, those are actually consolidated deposits where you have built a deposit slip with the bank and grouped together many payments onto one deposit.

Alright, so I’m going to go back here, close out.

Cash Management and Reconciliation
I’ll go into Cash Management.

And start my bank account reconciliation.

So I just go in here, hit New.

Choose the Bank you want to Reconcile
And I pick the bank that I want which is Chase.

And now it initializes the bank reconciliation.

Now we are working in the future in the Coffee Mug Tutorials, we are in the year 2017 so our fictional company operates a year in the future.

So my statement date is going to be 03/10/17. This is the statement ending date from the bank.

And as I put in the date it automatically updates the GL balance. Let’s see I’ll update that (date) to the 31^st. So I have $21,905 in my GL.

It’s looking straight into the GL here so this is why it’s so important to have a one to one relationship on the GLs. And then it asks me ok, what the balance on the statement is? 

My statement balance is actually $26,705.11. So there is already a difference between the GL and the statement and there could be many reasons for that and we are going to find that out in a minute.

I wouldn’t worry about these two fields here too much, they are good indicators; you can type them in from the statement but they do not reflect clearing out entries on the statement.

And the goal if you look at this as a little game, the goal is to get this difference number here down to zero.

If you get that down to zero and you’ve gone over all your transactions you can post; if this is not zero you cannot post the statement.

Add Checks, Deposits and Transfers
So now we’ve got all our header information in here but we are missing our checks and deposits and transfers. How we get them in?

What we do is we go up to Actions: Suggest Lines.

Here it brings up this window and what we want to get is ‘Both’ - that means both checks and deposits, we want them to come in here.

And the posting date here I can specify a range and I’m just going to do everything up until 03/31/17 which is the end of the statement.

I could get anything from whenever that’s also acceptable. Let’s see if we have any future items, I don’t want to put in there beyond March 31^st and I can just delimit those by doing ’..’ which means ‘until’ March 31^st 2017. Ok I just hit Ok and it brings in my entries.

Now here in the Checks, these are actual checks written out the system, we have $5000 written out to The Berry Farm and $20,000 written out to Lots of Oranges.

On my statement I can see the $20,000 has cleared so I check ‘Clear’.

However I don’t see the $5000 has cleared – so we’ve mailed it to them but they haven’t cleared it ok so I just leave that alone.

Then in my Deposits I have The Cannon Group.

It’s a Collapsed Deposit – that means there were multiple checks that we got but we deposited on one slip so there is one number that comes into the bank and I see this one number on there, so I check that off.

There is also Payments in this, so this screen can be a little bit confusing as it contains both Deposits and Payments that are made with Wire Transfers in one screen.

So these are wire transfer payments not check payments and they have all cleared; wires are great they clear immediately that’s better than checks, so that’s all good.

Refresh
Now let me now go up into the Header and just refresh it. Now what I just did was hit F5 to refresh because this doesn’t automatically refresh as you check off; you just have to click into the header and hit F5 and it refreshes the numbers.

Now I see that the difference has gone down to $200 bucks. $200 short.

Making Adjustments
Where is that $200? So I go on my statement and take a look at what’s going on there and I see a $200 fee to the bank. Ok, that’s one of those usual things. What we can do is put that into Adjustment.

So what we’re going to do right now is make an adjustment for $200.

That’s going to be out of Chase and it’s going to be into, let me see, let me just pick a GL account for that, something appropriate – Interest on Bank Balances, let’s say that ok.

So it needed to be an account that we can directly post to and so we have $200 coming out of the bank account, going to Interest Expense and I’ll have to put the posting date as proper 03/01/2017 obviously. So the description is Interest Expense. 

Now this is a transaction that hasn’t been posted yet.

I’m actually posting this transaction along with the bank reconciliation as an adjustment. Let me just take a look at what I have here, now I got zero, yes, I beat the game.

Posting
So we’re done with this bank reconciliation, all I have to do now is post it and then I can move onto the next reconciliation if I have one, so I go ahead and hit Post here.

Click Yes.

And it’s successfully posted.

Now I’m done. I can go into Cash Management again here.

And take a look at my bank accounts.

Go into the Ledger Entries.

Notice all of the Opens have been cleared except this one, as it’s an outstanding check that hasn’t been cashed.

Review
We can actually go into Bank Account Reconciliations.

And see that it has cleared and they’ve moved it into Posted already.

Go into Cash Management > Bank Accounts.

Navigate > Posted Reconciliations.

I see that as Posted.

Click on View.

I can see what was reconciled on that so I have full history of course.

Right here with what I just did.

So that was the first bank account reconciliation for Chase bank in the Coffee Mug Company. It went pretty well and I hope you have no issues going onwards with your bank account reconciliations.