Effortlessly Manage Your Invoice Reconciliation Template Excel for Customer Support

Invoice reconciliation template excel for Customer Support

hello my name is Ken Wilson I'm going to be showing you an Excel worksheet I programmed for bank reconciliations just to let you know this video isn't a tutorial if you don't already know how to compete am I correct the video is more for persons who perform bank reconciliations regularly but of course anyone can watch so the bank reconciliation is often a shredded and avoided task because most process approach is a rather tedious one it would take the bank stupid entries on line for line they were compared with their general ledger or their own transactions so if they take one entry they look at the value and when they find the corresponding value there / just highlight both entries on each statement saying that they agree and they will continue to do this and said till they get to the bottom of the statement on tell everything almost everything is highlighted the ones that you can highlight I don't agree with then put them on a bank reconciliation form to see if the transaction balance or if the difference is zero oftentimes this doesn't actually happen and anyone who has done bike reconciliations often knows this doesn't happen the first try and I can be very frustrating sometimes that I see that they want so we see 67 instead of 76 that's actually there and our fingers are no better we type 1 instead of 2 because the keys are just too close and finding these little tiny errors can be quite needles in a haystack but you don't have to be frustrated if you're using this particular sheet let me show you how it works so after I placed it in my new entries into the bank sleeping and the GL tabs I can actually right click and just click reconcile so excels actually going to go three centuries from me and it's going to use its Lodge to decide which match isn't which doesn't let me show you how it works so I'm going to click reconcile it's going to ask me if I want to clear my previous transactions for the first time you usually click yes now Excel has stopped and it's saying that ok it's found a problem that I can't solve silence alone so it's actually for help so it's saying Bank staple 913 cannot be reconciled because the appearances of $162 ninety-seven cents from the bank reconciliation form and the bank statement are unequal reconcile is andrey manually and then retry the automatic reconciliation actually we refer got to do is let me go to google i'm gonna start a timer this exact equipped i could do this so reset i'm gonna start it yet you can add admit it to it and i already started so but i press ok so what it has done is has filtered it by this particular vial that I mentioned before and if I look on my bank rec form you see that that values that twice so basically the exhales asking me well what is this particular line for by looking at this I'm going to say that this is probably an arrow because I don't think this value is gonna be there twice on the same day so what I want to do is I want to convert this to a jail at justment so I can actually right click any one of these and click convert and you see my form is still 0 I just moved it from the deposit in transit section to the GL adjustment section so I'm going back to my bank statement and I'm going to change the status to deposit in transit I'm going to change this to true because I'm going to adjust my bank rec and when I change the true you'll see we now have a difference so I need to delete this particular line here ok so I'm gonna right click again and choose reconcile so we want to Claire know this time cuz I already started and this is the same Bank right so no so Excel is doing all this for me I just looping through all the transactions another problem I can't solve so this time GL line 38 may be matched with bike lines 224 and 225 X own of both of these transactions mileage record cell line 38 and then we try the reconciliation so you can see that the bank seems have made an error here this particular check numbers here twice so the bank has reversed it so we use the word reconcile to say that the entries of the bank le GL agree I use the word offset to show that something has been reversed so I'm just gonna set these two to offset right click reconcile again nope okay so exhales tennis first leap and if I go to a reconciliation summary it's saying that it's 88% done so most of the lines actually have a status and has been reflected on the bike rack form this was the original balance I've had and this is the changes to it so I can see the progress that has been made and you can see that this is blank before but now all the adjustments I need to meet already here it's removed all those standing checks that's what on the current bank statement it's also removed the deposited transit that it could match so now I need to match the ones that are left manually so I need to find all the ones that don't have a status here actually I can use my match for so right click let's do that first open on march 4 and what the match form does is it will pop up all the transactions on the bank statement and the GL that don't already have a status so now I can just highlight between the two and set which ones that much so let's look at photo offset ones first so you can see this one here says credit reversal so because of this keyword reversal this will be an entry that's the positive value of this year so it's from smallest to Lewis so it should be to the top and that's it there so because these particular two are highlighted it will show you the sum of everything's highlighted here or sorry here this is the bank so now that I know these two balanced or offset I can choose the status of offset and I click match so what I click match is going to change the status for this line one to seven a line one to five let me out let me show you what I mean so if I filter by line 1 2 5 and 1 2 7 you see that I don't have a status so when I do this in my match form it's going to set the status for me it's gonna take it off the list so again I'm going to highlight these and choose offset and it's going to disappear so now I could do the same thing on my G outside now our particular general or GL Club knows to use the word offset when at when it's exactly that so it makes it bit easy for me so I just highlight all the ones that are off that and they should have differences zero offset that was another one another one okay that looks like all them and you see the difference is zero so I click match and this is a reversal so should be another entry here as well 166 that is that's also offset ok so this first one on the bank statement lender 805 wire so Excel could not match this now because I do this regular I know that the invoice charges and the pie charts are separate so I just looked over and 805 so there's the invoice and there's the wire charges now there's actual difference here so there's probably a mistake made in one of these so this one is now a difference so I clues I choose much better 5 5 8 I don't see that let's list so this looks to be omitted payroll enough of things parallel 15 there's a difference one match vendor 199 and the 109 wire invoice this bounces so this one is reconciled hold mail feet don't see that omitted refund so all the ones while POS ones would be credit card stuff so there is a MasterCard a refund has to be with a payment you have to pay first for a refund so I look for someone that's MasterCard payment oh that's me so that one so the difference is 26:46 and that's that so that balances match refund will discover payment for discover that's on the same side so this is offset refund Pahlavi so three five three fifty that's that reconciled taxes don't see that fees no fees interest that matches the difference match deposit returns the particles if that it's probably didn't enter it cause it's returned to pause it okay so that seems to be everything that I can do now that matches okay so I can exit this I'm going to filter again of the things that auto status so this deposit here September first I'm guessing this is a deposit and chance about there's a difference why excel didn't pick it up so two two seven four five six slight difference there so going to go in my difference column and the difference would be the bank statement amount minus the fall amount Center okay so $10.50 just going to paste that there to make sure doesn't change and this is the puzzle guys with the difference okay so this one here that's a very big deposit 18th let's look at the match form again I missed that one so of this and that there's a difference so much those get close that again also once with no status okay so this is omitted that was a minute so all these I can set to admitted just copy that okay oh my G outside now filter with blank status okay all these are checks I didn't clear so these are both stunning checks of wires and these would be deposits in transit I can check my reconciliation summary to make sure that everything okay so everything the status now but the bank for was not completely updated so right click reconcile no so just going through to see the ones that have changed and says done so these are in blue so you can just actually go to see what matches so I can see this one here is match with line 66 so if I click it's just gonna go to that line and I can do the reverse as well if necessary okay so if I go to the summary but now a hundred percent everywhere so we're done so I checked the time now ten minutes it's not too bad okay so when I can actually check to make sure it makes sense so it's saying the difference between the old balance and new balance is this here one hundred and two thousand four fifteen how do I check that's correct a good buying statement and you just highlight all the entries so 102 401 575 the same thing check a GL to make sure everything is captured nine seven eight nine eight seven nine and that's the same like that so we're done so these will be all the changes you have to make this particular month these are just ins these are differences or minute transactions etc these are the checks that didn't clear that will probably a later and the deposits for next one okay see something here are these two charges that were from July but didn't clever bike so this is probably an error to some learn reverse those and this one as well and that's it so we did a bike rack in less than 10 minutes if you like this bright correctly like a copy my men should be below thanks for watching