 Hello in this presentation we will enter a bank reconciliation into our bookkeeping problem in Excel. Keep in mind how that same information could be input into accounting software such as QuickBooks. We will first take a quick look at QuickBooks and then jump back over to Excel and enter the data there. When considering QuickBooks we have a feature to allow us to conduct the bank reconciliation that starts with choosing the account we are going to choose the checking account and then we're going to put in the date we're first going to reconcile the first month. So remember we have two months worth of data now we're going to go back and reconcile the first month and take a look at some of the problems that can happen in making adjustments in that case if there's a problem and we need to make some adjustments for the prior month and then we're going to have the beginning balance and the ending balance and enter that information. Once we do that we could say continue within the QuickBooks software and it really just gives us a detail of our bank banking information which of course is similar to our general ledger listing out the date of the transaction the checks for the transaction and the amounts. We can then compare those to our actual bank statement and all we're going to do is just check and check off these information we're going to check off everything that's on the bank statement to what is on the books here and everything that is similar we're going to say has cleared by checking that information off everything that has not cleared then will be unchecked and that's how QuickBooks will generate the bank reconciliation so we'll talk more about what that is when we put this into Excel but just note that we'll do the same for the deposit side we're just going to say here's the QuickBooks data and we're just checking everything off and we're going to match that over here this is going to be our bank statement and we'll just tick and tie this out this will make more sense as we do this in Excel that's just a quick example of what the QuickBooks looks like as you're doing this bank reconciliation and once you have done that we're going to identify anything that's going to be on the bank statement that is not on the books in this case we have these two items that were on the bank statement not on the books when working with QuickBooks then we would just make the adjustment meaning we would go into the account and actually in the check register make an adjustment enter the data for anything that's on the bank statement that's not on our books and we would enter that data and then go in and check that item off we'll do the same for any other item the bank service charges will actually go to the check register and enter that data and then go in and check that off this will again make more sense as we work this in Excel and then once we're done it'll indicate that we have been reconciled in that we'll have a zero in the differences column down here in the bottom and in essence we will just generate it'll generate a bank reconciliation based on that information so in order to get a better understanding of what is actually happening what is a bank reconciliation and how did that generate a bank reconciliation and why do we need to do that we'll put this information into Excel here we are back in Excel we are going to do the bank reconciliation for January so remember we're at the end of February and we didn't reconcile the account as of the end of January and now we're going to do the two bank reconcilations back to back in February and talk about the pros and cons of doing something like that what we're doing is we're going to be reconciling the bank account and that means we're looking at the bank statement and just tying out what the bank statement has to what we have and if you would think that if we were going to look at the same date as of in this case the end of January you would think that the balance at the end of January would match on the bank statement as to our books if everything was done correctly but that's not going to be the case because of timing differences meaning checks that we wrote that haven't cleared the bank and also deposits that haven't cleared the bank therefore we want to reconcile those items and we want to do that for a big reason one to check our cash account obviously because the cash account is very important but also by checking the cash account because the cash account is involved in the accounts receivable cycle with in the payable cycle within just about every cycle in the accounting process we're really given a good check about on every cycle within the process so and the fact that it's being checked against a bank which has very good bookkeeping services and is often very accurate means that it gives us a lot more verification to all accounts so it's really our second best line of defense in terms of catching any errors within the double entry accounting system other than simply the debits and credits the double entry accounting the balancing system itself so we're going to go ahead and match this what we're going to do this now we're going to go to the bank reconciliation is going to be all the way to the right we'll see a bank reconciliation so we'll take a scroll all the way to the right looking for the bank reconciliation area and we will see a bank statement that we're going to then compare to our books so it's all the way over here in the columns dw through ek so dw through ek that's what we will be working with here and what we have is a bank statement on this side and then we're going to have this is the gl account so first bank statement what is it we've all probably seen a bank statement we're going to get it at the end of the month it's going to come directly from the bank this isn't something that is from our books it's from the bank so this is what the bank says that we have and it will generally have a beginning balance it's going to have the additions in summary and then any subtractions in summary and then the ending balance this for the month of january the first month of operations and then it usually gives the detail meaning we've got deposits by date here's the date of the deposits and then we have the checks here by date and we also typically have check number and then the amounts there as well any other withdrawals or any other activity in this case we have withdrawals and bank service charges to get to our total in terms of the deductions and our total additions which are these items here so what we want to do is look at all this information and be able to tie it out to what we have if we can double check everything if we know that all these items for example are on our books then we can be pretty confident that those are correct because they're also on the bank and that gives us assurance that they are okay in order to do that we're going to take a look at the general ledger the activity for the banking for january so note that we are looking for the month of january rather than where we are in currently now the month of february and therefore where do we get this information went back to the first tab so this tab over here went all the way back to this tab the trial balance first tab and we picked up the this is the 94 437 at the end of january and we're picking up the gl all this activity the gl that made up that 94 amount right there so that 94 is being generated from here and all the detail is this so this is the activity we have going back to our trial balance for february here we have that so all we did was copy and paste this over here and we're just looking at the detail now and so all we need to do then is check off everything that happened here to here and see if it all ties out how do we want to do that it's makes a big deal that we want it's important to know whether we're going from the bank statement to the books or the books to the bank statement i would start off going from the bank statement to the books and then go the other way because when thinking about this if it's on the bank statement and it's not on our books it's most likely that there's an error in our books and we're going to have to fix whatever the problem is if on the other hand it's on our books and it's not on the bank statement then it's most likely the fact that there's an outstanding item meaning an outstanding deposit or an outstanding check so what we'll do is we'll just go through here and check everything off and see what happens first going from the bank statement to the books as we do it just going to highlight everything as we go so here we have the deposit here of 50 000 i see that 50 000 here in the books so i'm going to i'm going to highlight that i'm going to right click that i'm going to make it let's make it green we've made we found that there's the 50 000 there i'm going to highlight this there's the 50 000 there note it might not always be in order we see the 65 before that 50 here that's because it might have taken different times to deposit in the bank meaning we may have deposited one electronically while we've deposited the other one by going to the bank and putting it into the ATM machine or something like that and if that's the case it might take the bank a different amount of time to process the checks so the dates that the bank have will always be later than the bank than the book dates because we recorded it at the time that we made the deposit the bank is going to have to take a little bit of time in order to process the deposit then we have the 65 000 here we see the 65 000 there so we're just going to check that off or highlight it and say yep we found that there we found that here looks good and then we have the 628 here this is a deposit so i'm just going to highlight that i see that here i see that here the 628 looks good then we see the 20 000 here and the 20 000 there looks good note we also see a thing that's a little bit tricky is we have a zero in the beginning balance here and really the beginning balance is that 25 000 because this was our first month of operations so that's can be tricky in the first bank reconciliation to get that beginning balance right we're going to have to enter it in the data in some way but here's the beginning balance on the books and we're basically just going to tie that out as well so here's our beginning balance there we have it there and we have it here so when we go to to record our our reconciliation we will we'll be using the 25 000 as the beginning balance looking through the checks we have this check and and uh if we had more detail on the books we would also have the check number and the date on our books here we don't have as much detail because we are limited on space and don't want to make the thing look too uh intimidating so remove some of the detail but just remember that these dates on the bank statement aren't something that we can tie out exactly to our books our books will always be the date prior to when they cleared the bank but the check number will always be the same or should be and so should the amounts those are the two things we can use to to tie these items out so here's the 12 000 there we got that here it is on our books here's the 16 check 1002 here it is there here it is on our books and just right clicking and highlighting these as we go and i know this is tedious this is actually you know the way this stuff is done here and then here's the 7 000 we're going to say there's the 7 000 here it is on our books we're just checking these off just highlighting these there's the 400 uh check here's the 400 on our books check that off here's the 5 98 on the bank statement here's the 5 98 on the books and then we have the 6 20 now once again it would skipped a little bit so we're going to say okay but i still see that 6 20 here there's the 6 20 there and here's the 15 000 here and here's the 15 000 there so i had to skip around a little bit no uh to get these and we didn't see them all line up perfectly there's some numbers in between haven't found and then we're looking for this 80 000 withdraw and we don't see that here so so this and this 15 i don't see that on the books so these two items are on the bank statement not on the books i'm going to make those yellow say we're going to have those are the differing items these are something we're going to have to deal with we're going to have to reconcile and then on our books if we look on our books and go the other way just to double check everything i say hmm i didn't check off this green item do i see that over here we're going to say no it's not over there it's not in the deposits so i'm going to highlight that and say that's a reconciling item i'm going to have to do something with that same with this 11 000 i don't see it in any of the checks so i'm going to have to do something with that uh this 500 i don't see it over here at all so we'll have to do something with that as well this 320 360 we don't see over here so we'll have to do something with that and then same with these two here they're not they're not over here on the bank statement so that's what we have here now when we look at these we might say hmm these must all be errors that happen here but note that they're not going to typically be errors on one on this side because we're saying that this deposit happened probably at the end of the month and therefore it may not have cleared the bank yet and the same with these checks these checks could be outstanding for a pretty good amount of time because the check has to go to the mail and then the other person has to get it and then they have to deposit it and then the bank has to talk to them to the other bank in order to know the deposit was cleared before we get it so even if we wrote the check sometime in january it's very uh clear it's very likely that it doesn't clear the bank till sometime in february so that those are going to be our reconciling items then we're going to say that we expect that there's that there's going to be some outstanding checks here and we're just going if we can reconcile exactly what those are then we can double check that all other items that we have recorded here are correct and have been verified by an outside third party and a very dependable one the bank so what we're going to do is we're going to do a bank reconciliation so here's the bank reconciliation and typically in a textbook we have two sides to it we're going to have the bank balance and the book balance and we will adjust them in the bank reconciliation on quick books we didn't really have two sides and the most softwares you would and you really only have kind of the the bank reconciliation which is the one side and the reason is because everything that we're going to adjust on the books we're just going to make an adjustment for it we're actually going to fix so after we fixed it we will only be left with the bank balance side of the bank reconciliation so that'll make more set more sense in a second let's go through the bank reconciliation process what we'll first do is we'll start off with the bank balance and so as of the end of the month not the beginning of the month the end of the month here's the bank balance 109 415 so we're going to put that here this is the 109 415 and we're going to compare that to our books the ending balance on our books is way down here the 94 47 437 so that's going to be our beginning book balance so we're going to say that that's 94 437 our beginning book balance and obviously these are both as of the same date and they don't match and they're now they're never going to match because there's almost always going to be some kind of outstanding item typically so what we're going to do is just go through these items and say is it the banks problem fault or is it the books fault and put the reconciliation item in the correct area so if we do that we're going to say if we look at the bank statement first and say this 80 and this 15 these are amounts on the bank statement not on our books so this probably happened maybe we made a withdrawal from an ATM or something like that and we didn't record it and the bank service charges of course we just don't see those until we get the bank statement and then we have the bank service charges these are items that we're going to say the bank is not wrong on this or at least you know those are things we're going to have to pay for our books are the things that are going to have to be adjusted so these two items would go on the book side we're going to say okay we're going to have to fix our books our books are going to have to be adjusted for the fact that we're going to have to pay 80,000 or 80 we already took out 80 and we got to pay 15 for the service charges which they were nice enough to take out of our account already so we're going to do that by saying it's going to decrease the book balance i'm going to say the withdrawals are going to be 80 i'll put a negative 80 and then the bank service charges 15 negative 15 i'm going to go over here and highlight these and make these a lighter green saying that we have found those and put those in a particular place we put those on our book balance to make that adjustment now we're going to adjust the rest the rest of the yellow items so we have this 956 that's going to be a deposit it's a deposit on our books not on the bank statement why probably we made it at the end of the month and it has not yet cleared if we're concerned about it then we could check with the bank meaning this is actually being done for the time period into January 31st but clearly it's some period after that when we're doing the bank reconciliation sometime after February in this case and therefore we could call the bank and say hey on the bank statement as of the end of February or sometime after February did this 956 or we can check online of course clear and if it has cleared sometime in February then that's okay then we're saying okay that's good all we need to do then for the January bank reconciliation is to reconcile the fact that it's going to be a timing difference to do that we're going to go up to the bank balance up here and we're going to say that the bank balance needs to be adjusted meaning that 109 415 is not entirely accurate it's not that the bank really got it wrong it's the fact that it's just a timing difference they don't know about the fact that this deposit has happened and just hasn't cleared yet and so they're kind of wrong just from lack of knowledge and therefore we're going to make the adjustment here so this is a deposit that is included in our balance not included in the bank balance and therefore it needs to go up so we're going to put an increase here i'm going to call it an outstanding deposit and that's going to be a 956 it might be called a deposit and transit as well so i'm going to go ahead and highlight that and say we found a home for that and then a similar process is going to happen for all of these items these are going to be the checks that were written on our books that have not yet cleared the bank so these are items that we wrote a check for probably closer to the end of the month that have not yet cleared the bank once again if we're concerned with them we can call the bank and say hey did they clear after the bank closed and so then we're okay and all we have to do is really reconcile them same item here the same idea is that this account the bank balance account the bank balance account here is not really right it's it's wrong in that it doesn't include these items that we know about that the bank does not know about it's just a timing difference they will clear the bank once the bank is aware of them but they're not there yet so what we need to do is just add these up we're going to say all these items are going to be the items that are going to be outstanding checks checks we wrote that haven't cleared the bank and those are going to be items here that are included in our balance bringing our balance down not including the bank balance and therefore to reconcile we will have to decrease the bank balance so if we were to take the calculator out and just add these up then of course it would just be let's do that again the 11,000 plus the 500 plus the 360 plus the 3539 and the 3630 so it's the 1629 if I did that correctly overly and we're just we're going to do that of course with a formula though so we're just going to point and click to them so we're going to go back up here we are in cell ek6 ek6 and we're going to select equal and just point to that 11 and it's a it's a negative so it's going to be decreasing here so that's fine and then we'll say plus this 500 so it's plus a negative so it's going to and then I'm going to say plus this number plus this number plus this number and enter so it's going to look like this if I double click on it that's the formula once again I'll do it one more time I'm deleted it I'm on ek6 I'm going to select equals point to the 11,000 plus the 500 plus the 360 plus scrolling down a tad the 3539 plus the 630 and enter so there's what we have there so we're going to say then of course that we have an adjusted balance then of 109 415 minus the 16029 plus the 956 and that's going to be our balance if I did that correctly we'll do that with the sum function here if I sum this up it'll take this plus the negative or subtract that plus this so let's use the sum function we are in ek10 we're going to say equals sum double click the sum function and highlight of this column that gives us this number here and by the way we've found a home for all these so I'm going to make these a light green so we've found everything now should be no more red so or yellow so there we have that and we're going to do the same thing over here and hopefully if we say this minus this minus this it will be the same number it'll be that 94342 let's see if that's the case we're going to use the sum function by saying equals sum double click the sum function and highlight this column enter so there we have it and they tie out so now we've matched these out so we've reconciled the items we said here are the items that need to be on the bank balance that aren't included these are the items that are correct on our books that have not yet been included to the bank and need to be in order to reconcile the bank to the books these are the items on our books where they were on the bank but not on our books and they're things that we are wrong on we need to fix on our books side in order to reconcile and then we have the reconciling items I'm going to go ahead and underline these just format these just a bit we'll say double underline here double underline there and we'll have a single underline here and a single underline here and there we have that now what we're going to need to do at this point then is to adjust these amounts these two amounts are things that need to be fixed because they're not on our books and they're not a timing difference they're things that actually are going to be out of our checking account permanently so what we're going to do is actually make a journal entry related to these two items and then our ending balance will be in actuality this 94 342 we're actually going to add them in essence to the gl down here