Jaclyn Anku, ProAdvisor Training & Certification Leader: On today's episode of In the Know, we'll be going back to basics and exploring how to reconcile a bank account in QuickBooks Online with ProAdvisor trainer Carla Caldwell. The training she'll present is an excerpt from the banking course found in our newly reimagined QuickBooks Online Certification. In this training, Carla will walk you through the principles of reconciliation, viewing the account statement, reconciling an account, resolving reconciliation issues, and reviewing the reconciliation report. So with that, let me welcome Carla. Carla, welcome and thanks for leading us through another training.
Carla Caldwell: It is great to be with you, Jaclyn. Thank you so much. And I know that anybody that's watching this is super excited, because who doesn't love a good bank reconciliation? So, let's dive in and get started on what that is going to be all about. What we're going to be talking about today are just some principles of reconciliation. We're going to talk about how we can view the account statement right inside of QuickBooks. Of course, we're going to reconcile it, and because not everything goes smoothly all the time, we're going to talk about how to handle issues with reconciliation and make sure we understand that reconciliation report and know how to access it.
Let's start out with those principles. How often should we be doing that? When should we be doing it, and what makes it successful?
We should be doing a reconciliation at the end of each month. That is what we would recommend. That's going to include checking accounts, savings accounts, and credit cards. Now, I say “every month,” but that doesn't necessarily mean “end of month,” because we know those dates can be a little weird sometimes. But it should be on a regular basis. And while that seems pretty obvious, we don't want to do this on a daily basis. That could be a lot.
What we are going to do is manage those bank feeds—discussed on a previous In the Know on managing bank feeds— on a daily basis. Bank feed management on a daily basis, but reconciliation typically at the end of a period.
Now, that reconciliation should usually happen a couple days after the beginning of the month, because it covers the last month. Usually on the first, second, third, or fourth of the month is when we're able t o get those statements and be able to actually do that reconciliation. So hopefully you're going to see as we go through this that you won't have to wait for it to come in the mail. You'll be able to see it right inside of QuickBooks.
One other thing about reconciliation that's important is, How do we know it's been successful? It's not just that we reconcile. Or that it says the difference between the bank statement and what's in QuickBooks is zero. Yes, we want that zero to be there. We don't want to force it to be, you know, adjusted or whatever. But we need to make sure that that zero difference makes sense. Did we clear the right transactions? Are there any transactions in QuickBooks that did not clear the bank that should have? We are going to make sure that we are able to know that we did the reconciliation. We didn't have to force an adjustment to happen; instead, we really trust that reconciliation.
Think of being a detective as you go through this part. We're really wanting to dig in and make sure we understand what's going on and are looking at the things there with a critical eye to make sure that things are accurate in the books that we're working in.
Alright, how do we go in and take a look at this workflow? How do we see our account statement? Before we can actually do that reconciliation process, we need to have the beginning balances. Typically what we're most concerned about is that ending balance from the bank or credit card statement. So we need to have those. We also need the activity that has happened throughout the month. Now, the good news—and I mean, really good news—is that if the account (bank account, credit card account) is connected to QuickBooks, then what you're going to see is those statements may automatically be sitting inside of QuickBooks waiting for you. What does that mean? It means that we don't have to go log in and download that from our client's bank statement or wait for them to send it to us. It's already sitting there, ready for us to go. So the moment it's released by the bank, it's inside of QuickBooks, and we don't have to worry about going and finding it. It's super easy, and we don’t have to worry about paper, which is really helpful for me (I lose paper, and we don't want to do that).
So, where do we see this? Inside of QuickBooks, you're going to see that next to the account that we're selecting to reconcile—just to the right of that, you'll see outlined in pink—it says “view statements.” That is where we would actually view the account statement. Now, if you go into your QuickBooks and you don't see “view statements,” but above where it says “Which account do you want to reconcile?,” it says “enroll” to download those, just select “enroll.” You don't have to log in again. It should just start pulling those in for you. It might take a few minutes, but if you're able to, those will come in automatically if the bank allows it, which is wonderful news.
So, if we do see “view statements,” what do we do? We select “view statements,” and a little side drawer, if you will, pops up over on the right side, and that slides open, and you can see any statements that are there.
If you happen to have been using QuickBooks for a while—maybe you hadn't reconciled in a few months, or your client hadn't, but they had already been pulling in the statements—there could be multiple months there. It shows you any unreconciled statements. Little trick, the other tab says “reconciled.” Yes. That means you can go back to ones that were already reconciled previously and look at those statements. They are now stored inside of QuickBooks. Love it.
Okay, so that's where we're able to see the statements. So again, we don't have to go download those somewhere else. We can select that, and QuickBooks will open it up in another window for us. And if you're like me, and you have a big, wide monitor, or you have multiple monitors, you can put it on another screen while you do your reconciliation inside of QuickBooks. You don't even have to print it if you don't want to.
All right, so we did that. We also have the ability to manually upload statements as well. So if we have a client whose bank is not able to do this or doesn't allow it or whatever the situation is, we're still able to add those in.
Now we're able to go to the main reconciliation screen where we selected “view statements” a moment ago. In the top right corner, it says “history by account.” Typically if a statement is already there, you can see that there's an attachment, and the statement would be there. If you do your reconciliation, having manually downloaded it from the bank (or the client sent it to you or you scanned it or whatever the situation), you can do it at the end of the reconciliation. QuickBooks knows that you didn't have a statement inside the system already, and it will ask if you want to upload that. And so, I highly recommend that. Now, if your firm is like mine, you probably store those in some other file storage place as well, but it's so handy to have it right inside of QuickBooks. It's wonderful for us to be able to review those and peek over our team's shoulder every once in a while and do spot checks to have it right there. Super easy. I highly recommend it.
I am going to go over to our sample company and show you how we do the reconciliation. You should now see QuickBooks Online, and you'll notice that I am in the sample company. This is the same sample company that you have access to. Remember, when you're at your main QuickBooks Online Accountant screen, in the top right corner there's a little gear icon in the top right. If you select that, you're able to access the sample company, which is Craig's Landscape and Design. So, this is where you are if you want to do that. You'll notice that I'm over in the transactions area, and then I can go over to where it says “reconcile.” Now, this is the first time I've done this in the sample company, so it wants to show me how it works. You don't have to watch that video if you don't want to, because you're watching this video. So if you want to watch it again, feel free. But we're just going to go ahead and get started.
Now, we're in a sample company, and the sample company is going to be a little different than what you experience. For example, you would have a “view statements” button here or, as I mentioned, an “enroll” button up here that would allow you to get those statements brought into QuickBooks. But we're going to pretend that I have a paper copy, or I have my bank statement on another screen, and I'm just going to walk you through the process.
I'm going to go ahead and put in an amount, and I'm going to put in a date (did you know that if you put a T, it puts in today's date?). Before I go ahead and start reconciling, one really quick thing—did you notice this orange box up here? This is really great, because it's letting you know there are transactions in the bank feed—in the transactions area—that have not been managed. They're still sitting there. When I go to do my reconciliation, it's very possible that I'm going to need those, so it lets you know that you might want to go categorize those first. I think that's pretty awesome.
Now I'm going to go ahead and start reconciling, completely ignoring the warning (not that you would ever do that). You're going to get a few little warnings up here. I'm going to close those down and make it where they don't pop back up again as I'm working through here.
What you're going to see is that this is where we do the reconciliation. Now remember, we're matching the bank statement balance to the QuickBooks book balance. So we have the statement ending balance. I typed that number in there. It had a beginning balance of $5,000. It shows any payments or deposits. I haven't checked anything off yet, so you don't see those numbers. And the difference is this right here; we want that number to be zero. But remember, we want to make sure that number at zero makes sense. In other words, we’ve checked off all the right things. There's no transactions that didn't clear that probably should have, that were old, that type of thing. Now, a couple things to be thinking about here: as we think about this cleared balance, we need to make sure that it's taking the beginning balance minus the payments plus the deposits. That's how it comes up with that. We obviously have a difference and need to do that, because the difference between what QuickBooks cleared and the statement ending balance is this.
If, at any time, you realize you put in the wrong date, feel free to click “edit info.” That allows you to update the amount and/or the date. Not that I've ever transposed numbers, but I'm sure somebody might have … okay, I have many, many times.
If I want to narrow down the list of transactions by just looking at payments or deposits, I can do that. Or in my case, I'm looking at them all and in date order. You'll also notice that there's a filter over here. You're going to see that there is a filter that is automatically filled out based on the statement ending date. If you're really digging in and finding out that there are some issues, sometimes changing that filter date could be very helpful.
Now, in our scenario here, as I look through the list of all the transactions, I would typically expect something; if this bank account or credit card account had been connected to the bank feed, meaning those transactions were coming in automatically, and I was matching those two transactions in QuickBooks or adding them into QuickBooks from the bank feed, there would be a clear date here.
In this column that looks like two rectangles on top of each other next to the Payment and Memo fields, there would also be something telling me that those are connected through the bank feed.
This information is really great. I can go in here, take a look at the transactions, understand what's going on, and even see where they were coded to many times. That gives me confidence that the transactions I'm marking that have cleared are the correct ones. If I'm looking at this transaction and—let’s look at Tanya's Nursery—I'm thinking, Why in the world did they put that into Job Expenses? I can take a look at that transaction by simply selecting it and choosing “edit.” I can even add a memo here that explains things and so on. So there are some options here to do some editing kind of on the fly, if you will. And that can be very, very helpful as well.
For our sample company, it's going to go really smoothly—which isn't always reality, but we're going to say that it is for now. I'm going to use the option here at the very top. You can see this is the top of this grid, and I'm going to select that option. And QuickBooks is going to say, Hey, are you sure you want to select all the transactions? I mean, let's be honest, that's not always the case that it's going to happen that way, but today it is and I'm going to say, “Yes, select all.” Now, when I do that, immediately my difference is zero. But remember, put on your detective hat and double check that that really does make sense. I would always recommend that you kind of look through it. If it had been connected to the bank account, you would see the icons here and a clear date here. That’s really helpful, because if you have a transaction that cleared in February and you're doing the January bank statement, you want to make sure it makes sense that it would have cleared in the future. So sometimes we just want to double check those things. Usually, you're okay. Trust but verify.
So, we're going to do the same thing here, but in our case, it looks pretty awesome. I'm going to go ahead and hit “finish.” Now we're going to have this marked as “reconciled,” and when I select “Done,” what you're going to see is that that reconciliation is now as of the date that I put in. Everything looks great. That ending balance has now become my beginning balance, and I'm ready to go for the next period that I need to reconcile for.
I want to just quickly show you a couple things. I don't know if I'm supposed to or not, but I just get excited talking about it. So, if I go to Summary, you can see a list of all the accounts that have been reconciled; what the ending date was; and the actual date it was reconciled. Super helpful. But now let's look at history by account. So that was a summary of all accounts. This is the summary of the specific account that I'm doing the reconciliation for. You can look at it for just the past year, or for all dates, and it'll group it based on the year. You can see that was the ending balance. Any changes that have been made since the reconciliation—that's what would show up there. If it was an auto adjustment, meaning somebody forced the reconciliation (they didn't get to the zero, and they went ahead and reconciled it anyway), that would show there. And if I wanted to, I could attach a statement here, even if it wasn't already pulled into QuickBooks for me. At any time, I could go back and look at that reconciliation report as well. So just some great stuff there to share with you on a reconciliation.
Let's go back over to our presentation and continue on. So, we did a straightforward reconciliation, but as I said, they don't always go that smoothly. What do we do to resolve reconciliation issues? If we go in to do a reconciliation and you see this red box here that says
“Your account isn't ready to reconcile yet,” and it tells you that the beginning balance is off by some amount. You can see where it says, “We can help you fix it” in blue. We all know that typically, if there is something hyperlinked, we can click on it. Same thing here.
So, we would want you to click on “We can help you fix it,” and it will tell you a bit about what is going on with this and yes, even help you fix the issue, which is fantastic. What I'm going to do is click on that, and when I do a reconciliation, “Discrepancy Report” comes up. Keep in mind, this is the only way to get this. Don't go to the Reports menu and search for this. It's not there. The only way to get to the reconciliation Discrepancy Report is if there is an issue and you clicked “We can help you fix it.”
Now it's going to tell you exactly what's going on and what happened here. It will list out all the various transactions, and then you can view the history, because remember, QuickBooks keeps that audit trail forever, so you're going to be able to see exactly what happened and who did it. Remember your detective hat. In this case, a transaction was entered, reconciled, and then—low and behold—it was deleted, right here on this date, by Winter Dixon. Mystery solved.
Now, we don't know exactly why it was deleted, but we know that it was and, unfortunately, in this one situation where it's deleted, QuickBooks is not going to re-add that for you. You would need to re-enter it. But fortunately, QuickBooks shows you exactly how it looked when it was reconciled, so you're able to put that back in and continue on with your reconciliation.
What are some of the likely causes of those discrepancies? Well, there’s the first one we just saw—a reconciled transaction was deleted. Sometimes people will go into the register and manually unreconcile or reconcile a transaction, and that's a no-no. I tell my clients all the time, “If you go into the register, you can look, but don't touch.” And that's typically what's happened.
Sometimes people will go into a transaction and change it, even though it has been reconciled already. Obviously that's a problem. Another likely cause is that somebody could have voided the transaction; so not a full delete, they just voided it. But it still was a reconciled item. So they’ve changed the amount basically to zero.
Then sometimes people will go in and change the source account. So, say you have a transaction—maybe it was a credit card transaction—and it was off of account A, and they've changed it to come out of account B. Well, that basically moved it out of that account, so the reconciliation is messed up. Those are the typical, likely causes.
Sometimes it's helpful to understand why people legitimately needed to do that. Often, they didn't know of a better way to do whatever it is that they were trying to do to fix it. We can help them out with that. Sometimes what I find is that we will have parameters around what we have our clients do and not do. We say, “If you have this issue, this is how you fix it. But if it's not that, please call us; let us do it.” Sometimes it's easier for us to do it than to try to figure out what they did and then do the actual correction.
So, this is the process that we would recommend you use for fixing those errors: Review that beginning balance, check the ending balance you entered. If you're not able to reconcile, double-check that beginning balance one more time, just to be on the safe side. (I've tried to reconcile the wrong account, where the beginning balance was wrong, and I was like, “How is it not telling me?” It was the wrong account.) Check the ending balance. Again, check for transposition of numbers; look at all the payments. See what's going on there. Sometimes we have payments from customers, and we put them straight into the bank account instead of using undeposited funds and putting it into one single amount. That can cause some issues. And just in case something happened there, that can be an issue.
Do the easy ones first. Clear the transactions you know are correct, then deal with those other ones—the transactions that aren't in QuickBooks (that’s always something we have to double-check as well). Is there anything missing? Sometimes we can't see that as easily. We also need to think about what transactions have been marked that cleared or maybe got matched, that haven’t actually cleared the bank statement. Double-check transactions that could be slightly off—there are times when, as we all know, checks can clear for different amounts. So just kind of watching for all of that.
One really important call-out here: You have a workbook as part of the QuickBooks certification course, and there's a ton of content in there that I highly recommend you check out as well. There are additional resources available to you; that’s something to consider looking into when you have a really tough reconciliation.
Speaking of really tough reconciliations, sometimes we just need a do-over, and that's what undoing a reconciliation is all about. There could be times when somebody has made adjustments that they shouldn't have, or they cleared the wrong thing, or it's just so messy that we need to start over. What we’ll often do is called “undoing a reconciliation.” This is a function that, as accounting professionals, we have but our clients do not have. It's really important to understand that we have this power—and it’s another reason to log into your clients’ books as the accountant user, not as a regular user. You won't have this option if you are just a regular user.
Now, when should you do this? (Before you do, please go ahead and download that statement—the reconciliation report. Download that and hang on to it. You never know when you want to come back to it.)
This is crazy easy. You're going to go to that report that had summaries by account. Remember where we could go ahead and add the attachments and we could see when they did it or where there was an adjustment? There's an option there where we can actually undo a reconciliation. It’s pretty incredible to be able to do that. If you decide to unreconcile—say, January, and you've already done February and March—just keep in mind that it's going to undo February and March as well. So, remember, don't forget to download those reconciliation reports before you undo the reconciliation.
This is the report I was talking about—the Reconciliation Summary Report. This is the one at the end that we really want to make sure we have. This report is the one that's going to show us what happened in that reconciliation. It is a “point in time” report, meaning changes or things that happen after this are not reflected here. It doesn't update when you run that report again. It is what happened at that date and time when the report was reconciled. But it is super helpful to be able to review this.
So, let's go through this real quick. First of all, at the very top, it tells you the company's name, which account it was, and what the period ending date was. It tells you when it was reconciled and who did it. So really great information right off the bat. But first and foremost, if something has changed since the reconciliation happened, that’s front and center at the top of the report. Any reconciliation changes would show up here. So, anything that’s happened since the reconciliation—that's the one thing that does update on the report. The actual report itself, as I said, is going to show who did it, what the date is, and then it has the summary of this reconciliation.
It's going to show you, from the bank side and from the QuickBooks side, what it is, what the statement’s beginning balance was, what cleared, any adjustments, and an ending balance; and then it's going to show you any other kinds of transactions that happened as of that date and after that date, so you can see everything that’s going on. In the details area, you're able to see each transaction that cleared. The cool thing about this report is that I can click on this and actually go look at them too. So it's still live and connected to what happened in QuickBooks. It's not a static report. I can click on things, and in my deposits down below, you can see additional information—like what has not cleared the bank as of the date of the reconciliation. Or what has not cleared and has actually been dated in the future.
So if you were doing an entire year's worth of reconciliation, your January report would show you if there happened to be anything uncleared. Below, it could have months of transactions that are dated afterward, because it's a snapshot of time when that actually occurred. It shows you all of that information.
I love reconciliation reports. I go back to them often. I review them with my team. I will tell you that this is kind of the bow at the end of the month for me—when we do our reconciliations. Afterward, we feel like our bookkeeping is completely done. We've reconciled things, and it just makes us feel like we know what's going on.
A couple little tips I'll tell you really quickly: Always watch those uncleared checks and payments. They shouldn't be getting old—we should be dealing with those. Also, if you have a lot of deposits or payments that have happened and those have not cleared, that's really important to follow up on, because they should be clearing pretty quickly. Deposits don't usually stay out for a long time. Make sure you're watching those as well. And also remember, you don't have to download this and store it anywhere extra. It's going to stay in QuickBooks forever.
That concludes our time going through reconciliations. Thank you all so much for being with me today. Jaclyn, back over to you.
Jaclyn Anku: Carla, thank you so much for leading us through that training. It's really meaningful to get to show the community an excerpt from our newly reimagined QuickBooks online certification.
And thank you for watching. If you like what you saw, be sure to like, comment, and subscribe so that you don't miss a single episode. We'll catch you next time.
