 Hello! In this presentation, we will prepare a bank reconciliation for the month of February into our bookkeeping problem in Excel, keeping in mind how that same information might be input into accounting software, such as QuickBooks. We will first take a quick look at QuickBooks and then move to Excel and enter the bank reconciliation there. When considering QuickBooks, we would be going through the process of first looking at the bank statement. This will be the bank statement that we're going to be looking at both in terms of this short QuickBooks presentation and then going over to Excel. That's going to have the beginning balance, our detail, and our ending balance. We then have the detail of the additions and the subtractions in terms of deposits and checks. We're going to use that to compare to our information in the books. In order to do that within QuickBooks, we're just going to say we want to know what account we're talking about, the checking account. It's going to have the beginning balance, this from the prior year's ending balance, and then in the prior month, and then we're going to have the ending balance here from the bank statement. Given that, we'll then see a screen like this. Our goal here is just to check off everything that's in our books. This is our books side of the system to what it's on the bank, the bank statement. This is the bank statement side of the system. All we're doing is ticking and tying everything out. The things that do not tick and tie out then are going to be the reconciling items. When going through this process, we have some other tick and tie outs of the deposits. These are going to be the reconciling items, the ones that don't match up. Then we have a few items if they're on the bank reconciliation and not on our books. Those are things we're going to have to fix on the book. We'll actually enter an adjustment in this case for the withdrawals and the bank service charges. That'll look something like this. We're in the register here, and we're just going to make an entry within the cash register, decreasing cash for the 100, and put in the other side for the draws. We'll see this as we work this in Excel, and then we can go back to the bank reconciliation and check that off. We'll do the same thing for the service charges. We're just entering this into the bank reconciliation or into the register so that we can then enter it in the bank reconciliation. Again, this will make more sense as we work through this in Excel. Then we can check that off and then create the bank reconciliation. The QuickBooks system has two reconciliation types, a summary and a detail. We recommend both of them, but the summary looks something like this. We'll get into the format of this as we move into this, but basically we have the cleared items. Then this is really the reconciling items here, and then we have the get into the register balance. We're really looking at the difference between the bank balance and the register balance, which at the end of the day should basically be outstanding checks and deposits after we make any adjustments needing to be made. Let's see that in Excel. Within Excel, we are clearly going to be working on this account here. This is the checking account, and we're going to verify that that checking account is correct. By verifying it, it's correct. We will also be checking many of the other processes because as we can see, the checking account is one of the most used accounts in terms of detail and also is a major component in every cycle. So if we check all the checking account information, huge check to be checking everything else as well. We're checking it against a third party, which is a great way to be able to check in a very reliable third party. That is the bank. Let's do that. We're going to go to the right to where we have our bank reconciliation process. So we're going to go all the way to the right until we see our bank reconciliation. We're taking the scroll to the right. And here is the bank reconciliation we had for January. So this is the prior month bank reconciliation that we have made. The ending balance here is going to be used as our beginning information for, of course, February. So now we're going to go to the right a bit more. And we see our bank reconciliation for the second month, the month of February. So we're in columns EM through FE. And what we have here is we're going to have our bank statements. So we're saying that this is coming from the bank. So this is not our document. This is the bank document about our checking accounts. And it, of course, has the beginning balance, February 1, which is the ending balance as of last month, as of January 31. And then we have the additions, the subtractions and the total, then the detail of those additions and subtractions that of the checks on the left hand side, I mean, the deposits on the left hand side, and the checks and other types of deductions on the right hand side. What we will do now is take this information and compare it to our books. What you see here is going to be our general ledger. So this is our GL from January and our GL from February. So you'll remember that we had January here, and then we closed it out. And then we started in February. So we have the beginning balance that comes from our GL in February, being picked up from our ending balance in January. The reason we need both of these in here and not just the February information is because we need to know where we left off in January, not just in terms of the date of when we entered information, but the information that has not yet cleared the bank, some of the information that we entered into January had not yet gotten to the bank. And we need to know those because those are the items that were reconciling items in the first month. In other words, if we scroll back, I'm going to go back to the left to the January bank reconciliation. We went through this bank reconciliation tick and tide everything out. And these items, these light green items are the ones that were the reconciling items in our bank reconciliation here and here. In the month of February, we hope that those have now cleared and are no longer reconciling items scrolling back to February. In other words, these items that had not cleared, which are now in yellow in January had not cleared the bank, we expect to see them over here in the bank. And we need to check them off at this point to clear them out there. Then we have the current month of activity. This is all stuff that we put in the checking account as of February. And we're going to check and see which of that information will take and tie out here as well. Just a word of note here, this information at the bottom because they were checks, and we combined the checks when recording them into the general ledger is in a bit different breakout because we broke out the individual checks, both in terms of writing the checks to the government for payments and the payroll checks. So that could be a bit confusing. I apologize for that, but the ending number is the same. We just broke out the amounts to be per check rather than per journal entry in the general ledger so that we can match them out to the bank statement. That's going to be one of the common type of problems that we do face within the bank reconciliation. We want to make sure that we are inputting the same grouping into our books as the bank reconciliation, the bank statement, so that it is easier for us to just take and tie them out and see which amounts are tied out to the banks. If we don't do that, we can still reconcile, but it's a little bit more difficult because then we have to combine amounts and find which ones match out. I'd rather just have it be checking, ticking a time for an example like this. What we're going to do now is just tick and tie everything out. I start from going from the bank statement to our books, meaning I'm going to check everything off on the bank statement and see if it has cleared the books and then go the other way go from the books back to the bank statement to double check everything. Before we start that, we're going to have our two components of the bank reconciliation. One's going to be the bank side and one's going to be the book side. We'll start with the beginning balances. In terms of the beginning balance, it's going to be the ending balance here, but it's the beginning balance for our reconciliation. This is where we are at before we put any reconciling items in. This is the balance in other words as February 28th on the bank statement. That's going to be 99296. Then we have the book balance. This is the book balance at the end of the month, but the beginning of our bank reconciliation and that's going to be 99116. At the end of the day, we're going to get to this ending balance down here and hopefully they will then match. If we tick and tie everything out, we think that they should have to match. Just to note where we have started here, just to get an idea of this, remember that all of this blue stuff, that's the stuff we checked off already, that should match out to the beginning balance. Now, that's all the cleared stuff that has happened thus far within the business. So, if we highlight that this plus this plus this minus this minus this minus this plus this plus this minus this minus this. I'm holding, I'm letting go. I'm holding down control and clicking on the blue items, blue item, blue item, blue item, and then just looking down here and let an excel sum that up. It sums it up to 109415. The blue items that we checked off in other words are our cleared balance. Those are our beginning balance. So, that matches our beginning balance that has already cleared. The blue items are here. Then, we're going to record our activity and check off all the activity that happened in the bank statement as of February in our books as of January and February depending on when we wrote the check. So, we wrote some of these in January, some of them in February, which have been cleared in February here on the bank statement. Let's take a look at that. We'll go through the deposits first and we're just going to tick and tie this information out. We have the 956 on the deposit and the only thing we have other than the deposit really is the date and that date is not going to match our date because we wrote the the check date as of the point in time we made the deposit and it takes a little bit of time for it to clear the bank. So, note the date will always be closed but sometime after the the deposit date in our books and that's why it's important to get the deposits matched up correctly so that we can match this stuff out as easy as possible. I'm just going to highlight these. I'm going to make them green. Say, we found that and we found that. So, we're good there. Here's the 12, uh, 250. We're just going to do the deposits first. That's going to be something that we wrote in February. So, we found that. I'm going to highlight that here. We're going to highlight that there and say we found that. Here's the 825. I see it on both sides. So, we're going to say here it is there and here it is there. So, those are going to be the deposits and yes, we have this other one but we're going from here to here now. So, I'm not going to worry about this other deposit yet. What we're going to do now is go through the checks and here's the check. We usually have a little bit more detail. We still have the date but we also have the check number. Now, our general ledger isn't showing the dates and the check numbers because it would get to, um, long and messy if we had the problem with that there. So, we tried to shorten it out but, uh, we typically would have a little bit more information in terms of check number when marking the stuff off in a computer system and that can give us a little more help. Note the dates will be even further off however because we wrote the check and the date at the point in time we wrote the check and it takes a lot longer for a check to clear the bank. So, it the dates here are going to be much later. So, we'll go ahead and check these off. So, here's the eleven. We see it there. I'm just going to make it green. There's the eleven. There's the five hundred and I know this is a tedious task but, uh, it is this is how it's done and then there's the five hundred and there's the three hundred and sixty. Here's going to be the three hundred and sixty. There's the three five three nine. Here's the three five three nine. There's the six thirty. Here's the six thirty. So, we've tied all those out and that's all information that we actually wrote in January and didn't clear the bank until February. Then we have the one thousand uh three fifty nine. That's going to be up here so I'm going to highlight that and that's going to be this item. Then we have the one sixty eight. So, we had to skip them. Notice they're not quite in the same order. That's okay. That could happen on the bank statement. That's fine. That's the one through just because of when it depends on how the bank statement's being ordered. If it's ordered by date, then that's likely to happen because it's it's likely that different people deposited their checks at different rates. So, we're going to say this is going to be this item. Here it is here. We have the one twenty three. There it is there. Here it is here. We've got the three eighty or the eight thirty here and here we've got the six forty eight. That's going to be here and it's a skipped a few so we didn't see a couple of those. That's okay. It's not in order. We're going to just check them all off and then we have the three five one three here and here. So, now we've we've tied everything out that are the checks. Then we have these two items the withdrawals and the bank service charges. Those are going to be items we don't see over here. I don't see the withdrawals and we don't see the bank service charges and so those are going to be items that we're going to have to adjust for. So, I'm going to I'm going to make these yellow and say that I don't see these over here and I'm going to have to do something about that and and see what happens or see what we can do about it and then we'll go from the other side. We found all these highlighted ones. Let's see double check the ones that have not been highlighted. We have this deposit here. It's not over here. So, I'm going to make that yellow too and say we got to do something about that. These two are here. I don't see them over here. So, we're going to make that yellow. These two are over here. I don't see them over here on the bank statement. We'll make that yellow and this too of course is here and I don't see them on the bank statement. So, what we have now is is that we checked everything off. Everything that we checked off means that the blue items add up to that 109, 415 and if we check these everything off that adds up to this 14 and this 24, 149 then we have to reconcile because we've basically just matched everything out. So, that's what we're looking to do. In order to do that we need to first add these or adjust for these. These two amounts are going to be amounts that are on the bank statement which we need to adjust for. We need to just fix on our side. What we will do is we'll put them first into this book balance adjustment and then we'll actually make an adjustment making our GL change by those amounts. So, what we're going to say here's our book balance now at this point in time and we're going to say that the withdrawals are going to have to decrease it. So, I'm going to say negative 100 for that 100 here and then the bank service charges are going to decrease it. These are amounts that we in essence have to pay that aren't in our books. So, those are I'm going to make these a light green and say we've kind of found a home for those. Those are going to be a part of our reconciling process and then we got to do the same for these yellow items here. These yellow items this is a deposit in this case that is not on the bank statement. Why would that be? It's possible we made this deposit as of the end of the month as of the end of February has not yet cleared the bank and so it should then clear the bank in March. If we're concerned about that then what we can do is call the bank or go online and look at March and say hey did that clear in March? If it did then we're okay it's fine it hit the bank we're okay. What we just need to do now is put it in the reconciling item a temporary item a timing difference showing the difference between the bank balance as of the end of the month February 28th and our balance as of the end of the month February 28th. So, we're going to put that in outstanding deposits here on the bank side. Here's the beginning bank balance and we're going to say that this deposit isn't included in that number and it should be it's just a timing difference they haven't yet got it not really their fault but their number is wrong because it should be increased by this 1641 and so we're going to say we found a home for that and then I'll make that light green. All these are going to be the same these are checks that once again we wrote in February they haven't cleared yet the bank in February we expect them to clear in March then the next month the following month. So, we're going to say all these all these checks here we're going to say we expect them to clear in March they're okay the bank balance here is not quite right by those checks but that's okay because we think it's going to clear next month so we're just going to note that as a timing different as of this point in time. So, we're going to put our cursor in EF or FE5 and I'm just going to point and click and add these up so in essence I'm adding this plus this and then I'm holding down control these two and that so all the yellow accounts if I just click on those it'll sum up to 1,942. Let's do that with a formula and we'll point to all those so we are in FE5 I'm going to select equals and I'm just going to point and add so I'm going to click this 154 plus this 614 plus this 180 plus this 365 plus this 629 and enter. So, once again those all we're doing is adding up these items there all the yellow items all right and that's going to be a negative number so if we add this up then now we're going to say that we accounted for everything we've we've got everything on the books and checked everything off that we need to check off and we should then reconcile so we're going to add these up I'm going to say this minus this plus this I'm going to do that with the sum function so we're going to say equals the sum which will take this positive number and then subtract the negative number plus the positive number let's bring it all the way down that gives us the 98995 we'll do the same thing here I'm going to say the sum function will take this minus the 100 minus the 20 so we're down here in fe 16 we're going to say equals SUM double click the sum function highlight the 99 us 116 down and enter and there we have it now we're off by a dollar that's rounding that's okay I'm going to say that's okay and so we're tied out there so that's what we have at this point and note in order for this to work all we did is basically say all the blue accounts are the beginning balance and we're going to take and tie everything else out meaning we're going to find exactly what the differences are in terms of additions and and subtractions and just take and tie all those out and then show the reconciling items the items that need to be adjusted the items that are either on the bank statement and not on the books or on the books and not on the bank statement now next time what's going to happen is we have these two amounts that were on the bank statement not on the books we showed them as a reconciling item here but really they're not they're not something that we can just leave there there's something that we got to fix we and we need to make this balance decrease by those two items and that's what we'll do now and once we will do next time and once we do that then will be left with this as really just our bank reconciliation because it'll reconcile to our actual account so this is often used in in a book problem to show the book side of it but in reality of course we're just going to fix these items and give our true balance to be the book balance of 98996 and therefore our reconciling items should really just be the outstanding check and deposit in most cases in simulation and the simulation