Streamline Your Invoice Reconciliation Template Excel for Banking

Invoice reconciliation template excel for Banking

in this video we're going to talk about how to reconcile a bank statement or a credit card statement in QuickBooks online so I'm in QuickBooks Online then I'm going to click on the gear menu on the top right of the screen and then under Tools I'm going to click on reconcile then on the drop down menu I'm going to pick which bank account or credit card I want to reconcile and let's say I want to reconcile this Chase bank 8145 now I'm going to bring a bank statement here on the screen and every Bank looks different and this is just a sample bank statement that I created and essentially what I want to do is I want to make sure that the beginning balance that's already in QuickBooks which in this case is 17,43 uh 5051 matches the bank statement right here uh which is right here so okay perfect so that beginning balance matches we're good to go next step is I'm going to take the ending balance of the bank statement and and again depending on your bank statement you will have your own type of number in there so we're going to put ending balance and we're going to put it in the Box here it says ending balance and then the ending date that's always going to be the last date of the current uh bank statement that you're reconciling so in this case I'm reconciling January 2024 and you see right here where QuickBooks tells me that the last one that was reconciled was the one right before which is December 2023 so we're going to put here 0131 2024 and as long as I have my correct ending date the correct ending Ballance and the correct be beginning balance I can now click on start reconciling and go to the reconciliation screen now this is not as simple as coming in here and clicking on select all yes and it just working it doesn't work like that now sometimes when you connect QuickBooks to the banks and the only transactions you put in QuickBooks are the bank downloaded transactions these will sometimes Auto reconcile and I'll create a different set of videos that have to do with uh downloading transactions from the bank and the reconciliation process however in this case let's just assume that we entered all these transactions by hand so now what we do to make sure that information essentially is complete and accurate is we are going to check the box on every single one of these transactions that show up on the bank statement so I'm going to bring QuickBooks here to the right hand side a little bit I'm going to collapse the left navigation bar so we can have a little bit more real State and I'm going to bring the bank statement kind of like next to it so you could try to follow along as much as as much as possible so what I'm going to do is I'm going to grab the actual uh transaction if I have it printed I'm going to put an actual physical check mark on the paper uh I'm just going to highlight it uh in here using um using the highlighter tool in in my PDF uh software okay and I'm going to check it in here so essentially that's the process I'm going to check every deposit and every uh expense and every check and I'm going to come in here in my PDS software and Market uh highlight each one so I'm going to go through the process and we're just going to go uh and kind of fast forward to this but this is the process you would take with your own bank [Music] statement [Music] perfect and then once you put check boxes or in this case I put I highlighted them on the PDF once you mark everything off and then you can double check and triple check that everything that was on your bank statement matches QuickBooks you should see a difference of zero now what happens if you have transactions missing what if the bank statement says you have a transaction for $300 That You Don't See in QuickBooks then then you got to go find the transaction in QuickBook see if you categorized it to the wrong account or uh if if you never entered it for whatever reason then you that's the opportunity to enter it right there and then that's if you're missing transactions if you have duplicate transactions that don't belong there delete the duplicates go into the actual duplicates and delete them and if you see any transactions that are not necessarily duplicated but you don't see them on the bank statement well it's possible that if there an outstanding check maybe they'll clear on the next month so you kind of have to wait until the next month happens to see if those outstanding checks show up in the next month and then on the next month exercise when you do February you will be marking those together in that month as a matter of fact I'm going to scroll all the way up notice that there's one check here from previous periods that literally um just clear this period so uh that could happen too where you have something from last period that just clears in this period so at the end of the day that's that's the the idea is that you get down to the difference of zero and click on finish now and as as soon as you're done with that you can click on view Reconciliation Report and you can look at the Reconciliation Report and you can print it uh save it as a PDF whatever you want for your records for that and essentially that's it it it does get a little bit more tricky I mean um in the in the real world you know there's always going to be missing transactions duplicate transactions transactions with the dollar amounts that don't match uh sometimes you'll have like deposits in QuickBooks that you have to match with mult multiple invoices and multiple payments I mean that stuff gets really really tricky I'm going to do a separate video uh for online banking where I talk about matching transactions existing transactions to downloaded transactions and how all that stuff flows through into uh the reconciliation at the end but that's it in a nutshell uh reconciling is matching your QuickBooks to your bank statements or your credit card statements to make sure that all the debits all the credits beginning balances and ending balances match those statements anyway I hope you like this video and it will make or should make your QuickBooks experience more enjoyable and I'll see you on the next one