Streamline Your Invoice Reconciliation Template Excel for Engineering

Invoice reconciliation template excel for Engineering

bankr consulations can be really complex and timec consuming but with the help of this template I'm going to show you how you can automate that process and speed up your bank racks so here I've got a simple layout for a bank in the book tab where we've got date description debit and credit amount so this is the bank side this is the book tab the rest of these are formulas we have an option to manually override but I'm going to show you how how you can set up automatic matching rules on this template as you can see some of these transactions have already cleared and so if I go in the setup tab I've created a space where you can set up rules to help you match your transactions to categorize them to say okay this is what a what a check looks like this is what a wire transfer looks like and so on so so here we enter in a category checks we say okay for our checks regardless you talk about the bank of the book side it's going to look on both if we see CK pound then we know that that's a check is going to be classified as a check and then if we want to get the check number I'm calling this the the key so this is the going to be a component of knowing whether we've got a match or not cuz otherwise you know if we're just seeing a check on the same date we're going to say okay that's a match which obviously we don't want to do we want to make sure that the check numbers actually match before we say okay these are a match so this is where we can specify that key so we've got options like use alpha numeric string if you've got numbers and letters use just the numbers or you if you just want to use just the letters or if you just want to use the date so depending on your transaction depending on the category you may use any number of these options so if it's CK pound we're going to say okay use the numbers that come after this and then we can specify the length of it so we're going to grab the first four numbers after it and then we have a space for Gap to say okay we want to jump over the next one or two um things that come after it and then we have an option for a one: one match which means we exactly have to have one exact match or we can have a one to many match so if we have let's say in the case of deposits right we've got multiple deposits on the book side could link up to one deposit on the bank side or vice versa depending on how our our our setup is so a bit of flexibility on how these matching rules are set up so if I go back to the bank tab you know we've got that CK pound1 15 4 6 so we got that identifier grabbing the those four first four numbers afterwards and plopping them here into this key section and then we've identified it as a check amount and we got the amount so if these items are all a match on both sides on the bank in the book tab then we've got a match so this is designed to minimize false positives to make sure we're not accidentally matching things that aren't a match so in this case we say okay we need the check number to be a match so we look on the book side you know we've got 1546 as well we've got that same identifier and so on now let's say we we've had we want to utilize that Gap so we don't want to grab the first four numbers afterwards because you know there's going to be a zero first so now it's not a match we've got 01 54 right and same thing on here we've got let's say in this case we've got a a seven in front whatever the case may be we can go back here and let's set that Gap to one so basically it's going to skip over the first thing that comes after the identifier and then grab the first four numbers so now if I go back now it's matched it so it's skipped over that seven still pulled in that 1546 same thing on the book side so now it's able to match so that that's how you can customize your rules uh to determine how you want these transactions to be automatically matching to one another if you go back here we've got the wire transfers and deposits in which case we're just using the dates cuz typically if you're dealing with wire transfers or deposits the there's often not going to be an identifier on on the bank or the book side again depending on how you've got your uh GL setup and how your how your bank statements look this this may vary but if we're using the date it's just going to pull in the date so so for the case of wire transfer we've got February 9th 2024 we've got 02924 so you've got that date in there as that being the key on the book side we've got that wire TR transfer as well 0209 24 so now let's take a look at an example where we've got multiple uh values matching up to one so we've got this deposit here on February 4th for $1,511 February 4th on the bank tab we've got multiple deposits on February 4th if we add these up they add up to 10,500 111 but the problem is because on my rules here I've got a one to one match I'm looking for an exact match so on this system here it's not going to automatically flag that as saying okay this is a match but if I change this to say Okay I want one to many and I go here now it's highlighted these values and said okay now it's a match so if the one to many is set up then it's going to look at all of the transactions across the bank on the book it's going to look on the previous outstanding items as well just to see if everything across those tabs that meets this criteria that's a deposit has the same key if those are a match then we're going to say okay yes it is a match and it's going to clear those off on the bank and the book tab so that's how that's designed to work for for the rules so so you can you can create different rules depending on how you want your transactions to match automatically and um and clear off because on the previous outstanding items these are carried straight forward you know these are also checked onto here so if we've got dates here that um are a match to what we've got on our book and our bank tabs obviously those can clear off as as well but now what about those situations where you don't have an exact mention you actually need to do some side by side matching that part is also going to be um easier as well so I've got this reconciler uh button here so on the Home tab I've added to the ribbon there's a section for this bank reconcilation so we've got an option for uh reconciler to to launch a new month to clear the data and to reconcile the month and create a a report so I'll start with the reconciler here so if I open this up we've got an option to do side by side matching so I'll start with just selecting anything on on this side here so I was on the bank side so it's was going to pull these these transactions from here it's also going to pull the values from the previous um previous sub standing tab so if I select these values so I selected a wire transfer I'm going to see other wire transfers that I can potentially match up to so you know I select a transaction that's 37,000 37,000 that's a match right so I can click this and say match the selections now if I selected other ones now I cannot match these because again I've got 7 21,000 on this side 37,000 on this side so that's not a match so again the goal again is to make sure we minimize the uh potential for uh false matches here so if I uncheck this and I've just got 3737 there's no variance everything's good to match up I can click on this button to match the selections you can see it's highlighted in green so now updates on here now if I want to flip this right now I've got the bank view if I want to Swip the SW Swap this you know now I'll see the book tab now I'm on the book tab and I've got that perspective on it so again I select the deposit I see the deposits that I could potentially match to so I can make my selections and you know if I add up to that total then I can clear it off and say okay that's that's a match right so the key thing is making sure that these totals match up and if they do we have match we're good to go so I've matched up a few and now the one thing that that the reconciler does is if you've manually matched this it actually stamps okay so we match this transaction on the book tab this deposit of 20577 it was matched up to the previous tab previous Bank row 7 so we look at the previous tab we got Bank row seven right here so we've got that matched up and this one matches up to book row four I've got the got the headers and columns off here but this this tells you what row number what tab and so you can cross reference that way to say okay row four and that one was row seven so it gives you a bit of an an audit Trail to say okay these ones were manually matched um through the through that record cile you could also manually override just by entering anything in here but if it's automatically matching then that'll clear out through this formula if it isn't manually matching you can override it yourself or you can go through that reconciler and go through and do your bank reconciliation process that way so it's a lot easier to do side by side you don't have to open a second uh uh screen Excel Window to do this you can do that right within that reconcile check those items off and knowing that you've got that auto Trail and if there are multiple items it's going to list each each and every row each and every item that you've matched to to say okay this is the complete list of items that that have been a match and so if you've done your bank reconciliation process you're done with it you you've gone as far as you can then you know you can create the the report for your reconciliation so here we've got it so we've got a bank bounce so this is just a static uh bank balance you just enter in your your balances here so these are not linked into anything so you just update these ingly you've got our variance here we've got a Reconciliation date so if we want to actually reconcile everything based on our categories we click this button to reconcile the month and once we do that we can see we have a breakdown based on the checks you know so we've got this is the the bank side this is the book side so these are all of our outstanding items based on what we've got now obviously I don't have the balances um entered in here I've made some changes in here but this is how we would sort of match it up to say okay this is a breakdown of the wire transfers the deposits all our outstanding items and all that so once you update your balances and everything's accounted for you would see that that's um a breakdown of your report so a clean summary to see what makes up your your outstanding items presumably if you've got everything everything posted in here so it generates that that report for you to to help summarize that for you and then you know if you're if you're done you want to start a new month then that's where you click the new month button what it's going to do it says okay it's going to clear the bank and book data and copy the outstanding items to the previous outstanding items continue I'm going to click no for now because I'm just going to show you what it's going to do is going to push these items to this outstanding items the previous outstanding items Tab and push those into there so if I go there and click on new month it's going to do that and so so it's cleared off my reconciliation my bank and book are clear my previous outstanding now has these values from 2024 so it's got these values that I did not match so it's added to uh my old list and pushed over the items that I did not have uh a match for previously so it's going to build off and so you can start fresh with a new month put in your values and start with your reconciliation process for the new month so your previous outstanding items are are going to get added to whatever was was not reconciled so this template it can hopefully help you you know improve your your your bank reconciliation process just again because you've got those automatic matching rules you can set up in the setup tab you've got the reconciler which makes it really easy to do side by side comparisons and check off the items as they match up and again giving you that audit Trail to see okay this is exactly what you match to what so if you need to look back and reference back you can see um that's what happened that matched up to to what so I'll leave a link in the description for this video where you can um access uh the post related to this to this template and you can download it and try it out so hope you like this video and this template please leave a like if you did and make sure to subscribe for more thanks for watching