 Zero Accounting Software 2023. Bank Reconciliation Month Number One Deposits. Get ready to become an Accounting Hero with Zero 2023. First, a word from our sponsor. Well, actually these are just items that we picked from the YouTube Shopping Affiliate Program, but that's actually good for you because these aren't things that we're just given to us and some large corporation which we don't even use in exchange for us selling them to you. These are things that we actually researched, purchased, and used ourselves. Focusrite Scarlett Solo Third Gen USB Interface with Software Suite. I've been using a Focusrite for years for my audio needs, before which time I had a USB microphone which plugged directly into the computer, and I think you'll find, as I have found, if you want to increase the quality of your microphone, you will need an interface and the Focusrite is the go-to interface as far as I'm concerned. I've been using this for years now. It works well, it's easy to use, it seems quite durably built. Because I only do the screen recordings, I only need the one solo interface. However, if you have multiple microphones you need to plug in, or if you have other instruments you need to plug in, you can look at a similar model that has more input ports. If you would like a commercial free experience, consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com where we have many different courses. You can purchase one at a time or have a subscription model, or even you access to all the courses, courses which are well organized, have other resources like Excel files and PDF files to download and no commercials. Here we are in our Custom Zero homepage. Going into the company file, we set up in a prior presentation. Get great guitars. We're going to duplicate some tabs to put reports in like we do every time. Right-click in the tab up top so we can duplicate it. Right-click in the tab up top again so we can duplicate it again. Back to the tab to the middle, accounting drop-down. We want the balance sheet. This is a slightly modified balance sheet, but if you just have a normal one, that will work. Tab to the right. We're going to go to the accounting drop-down. We want the income statement. We're going to look at the comparative income statement here, but if you just have a normal income statement, that will work. Back to the tab to the left. Let's make a change now to the date. Just looking for January as of the end of January. Let's do a customization down below taking it to January, the end of January 31st, and update. I'm also going to open up another report which is going to be the bank reconciliation because that's what we're working on now, noting that we can actually look at the bank reconciliation report before we reconcile and see how it changes as we go through the reconciliation, which is really neat, I like. I have not seen that in other software such as QuickBooks Online, so it's a little bit different. Let's duplicate this thing again. Open up another item, accounting drop-down. Let's go into the reports. I'm going to type in bank reconciliation, bank reconciliation, and then let's change our date range up top. Let's bring this on a custom range, custom home on the range, where the antelopes and deers run. I don't know how the song goes. I haven't sung that in a while. Okay, focus. January to January. I'm going to say this is going to be our checking account, and then this is going to be 6124185, 6124185 bank statement ending balance, and we can update it. So we saw a quick look at this last time, just noting that these outstanding items now will no longer be outstanding as we check them off as having been cleared, and we'll see how the report will change as we do that. These summary numbers will change, and the balance numbers down here will change, which is pretty neat. I'm impressed by a different layout. Back to the first tab. We're going to then go to the accounting dropdown, go into our bank accounts, and we're going to go into the manage accounts, and then account transactions. Let's go into the account transactions here, and we want to go through our bank reconciliation process. We have our three tabs down below, the reconcile, then the bank statement. Nothing's in there on the bank statement side and the account transactions. Now, notice that Xero is really designed as most online accounting softwares to be connected to the bank, so it's kind of wanting us to be pulling in the transactions directly from the bank. Now, if you're not connected to the bank and you want to do the manual transactions, we can mark these off as being reconciled, which we'll take a look at in a second, or we can manually upload a statement, which is basically uploading the... So if I go into this manually upload a statement, we're going to then basically be uploading like the bank feed information from the bank manually. So you can download bank statement from your online banking as an OFX, that's the preferred method, an OFX, I-Q-I-F, I-Q-B-O, which is like a bank feed type of format that's often there, which is designed for QuickBooks Online, but it's really just a format that the bank feed data is in, which is quite common, and then you have a CSV, which is basically just a data file which usually people open up with a spreadsheet, which is really just like a comma-deliminated data file, I believe. So if you look at their file here, just to take a look at it, if you were to import your information from the bank, this is the data fields that you can have. So you might actually try that, you can go into your bank, even if you don't have your bank feeds turned on, and so this is like an example of a Wells Fargo, for example. There's usually a section where you can download account activity, and then when you download the activity, you can choose the date range that you want to be downloading it for. So in this case, if we were doing this live in our bank, we could do it just for the month of January, if that's what we're working on, and then usually you have different formats, like Quicken is different than QuickBooks, so make sure that you, but the QuickBooks is that Q-B-O file, which is quite common, this is an actual QuickBooks kind of file, and that's the bank feeds that you can upload to Xero as well, and then this is the comma deliminated, which is kind of the default, usually if you don't have anything else, you can download it that way, and then you can populate your information with these headers. Now one kind of neat thing, if you did download the data and then upload it, is that you can then add the date fields, the payee and the description, the payee is something that you might be able to add from the description from the bank feeds, so that's just something to note, so if you were to populate this, it would look something like this, right, you can actually populate something like this in Excel, notice when I opened up their file, it was actually a CSV file, not an Excel file, this is an Excel file, they look the same because both are opened in Excel, so if I save this file as a CSV file, when I open it back up, it'll strip all the Excel formatting, making it easy to upload to like a software like Xero, so in other words, here's my data that you would get from a bank feed kind of transaction, right, you'd have the date, and then check numbers, and if there was any information, then you would have the information in the description line over here, and so then you've got the balance, and one column increases being deposits, being positive number, decreases being negative numbers, and note that this is just showing you activity, it's not giving you then the beginning balance as of the beginning of the period, which is going to be an issue when you first do that first bank reconciliation, so just note that the data that you would pull is just going to be the data from one time period to another, it's not going to include the beginning balances, which are before that point in time, you've got to deal with that cutoff kind of issue. Now if I saved this file, if I go to file, save as, and I save it as, I can save it as an Excel file, or I can hit the dropdown and save it as a CSV, comma, deliminated file, so that allows you to use Excel to kind of format your file and then save it as a comma, deliminated file if you so choose, and just so you can see what that kind of looks like, if I go over here, we can see that we have the January, this is the comma deliminated file, this is the bank transactions file, and if I right-click on the comma deliminated file, and I go into my properties for it, I can see that it's not an Excel file, but a .csv file, and if I open it back up, then it looks, it'll strip the formatting of, I can't open the two at the same time, hold on a second, I'll close these out, and then open this one up, it'll strip the formatting that it had, see, and then it just makes it like just the data, okay, that's the idea, so if you needed to upload it, that's one method that you can do, we're gonna try to just record it as reconciled as we go, so let's go back into here again, I'm gonna go accounting bank accounts, and then I'm gonna go back into the reconciliation, I'm gonna say, let's go into the account transactions, okay, so now I'm on the account transactions, what I'm gonna do is just go through and manually reconcile them as we go, now the thing that's kind of an issue here is they don't have the reconcile button up top, I think they're, I've been looking hearing that there's a setting that you can turn on the button, but I haven't been able to find it, so what you can do is I can go into these items, and I can see the options up top, and there's the mark as reconciled item, meaning you cleared it off to the bank, so it's a bit tedious of a process, but that's what we're gonna use for the manual reconciliation, which will change it to unreconciled to reconcile, and then when we get to the bank feeds portion of the course, which is really what zero is really, you know, geared towards doing, connecting to the bank feeds, we'll do it using the bank feeds, or you can upload your information even if you're doing it manually like we talked, that's why I pointed that out as well. Now you can sort your data here by the date, right, we might want to sort it by the date, and and so now I can see like the older stuff first, you might sort by the description, because that will put together like items next to each other, which could make it easier sometimes when you're adding the information, same with the reference, and then you might have it sorted by the spent money, and the received money, here we're focusing in on the deposits, so we might put, you know, the received money or the date item here. Okay, so now we've got the date and then so let's keep it at this. So now if I go to my financial statement, this is my actual bank statement, we're imagining it's from the bank itself, so I have the beginning balance up top, we'll talk more about that later, we're going to have to deal with that in and of itself, that'll be that beginning balance issue. Right now we're looking at this number right here, which represents the additions, the increases. Now just note that if everything is set up well within your accounting system, the reconciliation should be really easy. However, it's quite likely or common for people to kind of not have things set up well sometimes on the deposit side, what that would mean is that maybe you have your deposits kind of grouped in a way that is not exactly how they're going to be grouped on the bank statement. So if I go back onto my flow chart, this is the QuickBooks desktop flow chart, but I'm just looking at it to look at the flow of transactions, we're looking at the revenue cycle because we're looking at deposits, most deposits being of course from customers, hopefully and the process when they come from when we get the revenue will differ depending on the type of industry we're in. If we're in that gig work industry, for example we just get paid by YouTube or something then we're going to get the deposit and we might just record the deposit as it clears the bank. That will be very easy we won't have any issues with that. However, if we have a situation where we're going to have a register we're collecting money at the register, cash or some other kinds of transactions credit cards are involved then if I deposit these directly into the bank I could have issues because one I could have fees that make the deposits different in amount and two, I might be grouping the cash together and the credit cards together in such a way that when they actually physically get deposited into the bank will be different than individual sales. In other words, if I made $5 sales all day long for cash and then I collected the cash I'm going to deposit physically into the date the bank one lump sum of like $100 not a bunch of different $5 amounts and therefore when I reconcile if I deposit everything directly into the bank I'm going to have the one lump sum on my bank statement but a bunch of individual deposits in my books and that's going to be difficult to reconcile. What do you do to clear that or fix that? You usually have that clearing account that we talked about when we did the data input you can have a similar issue with the invoices you have a similar issue if you have payment processors PayPal, Stripe and what not you want to make sure that you're when they actually hit the bank account you're grouping them in the same way in your books as we'll be hitting the actual bank account okay and if that's the case it should be fairly easy to then do the deposits and check them off as having been reconciled. Remember what's on the bank statement you're going to have is the date of the deposit which is usually quite relevant because the date of a deposit if it's an electronic deposit or even if you physically deposited it into the bank it'll be easy for it to clear the date will be pretty close meaning you might put it in your books like three days before it actually clears the bank so you might still have some outstanding deposits you might still have timing differences but it should be pretty close unlike checks for example where the timing difference could be quite large and then you have the dollar amount of course which can help you out to reconcile now note that if you make physical deposits into the bank with just cash then you're not going to have anything else like descriptions but most of the time these days people do electronic transfers so if you get the money from an electronic transfer then you might have more detail if you have a payment processor like Stripe or something then it might at least tell you that it came from Stripe and it might be batching things together if it's a credit card company it might at least tell you it came from the credit card company if you're getting deposits directly from someone else someone else's bank transfer then it might give you the information from who gave it to you which would help you to determine if you're building your books from the bank feeds who gave you the money so the electronic transfers help with the bank feeds and at the minimum you'll have the deposit and the date which should help us to reconcile so note that if it's on the bank statement and not on our books then likely we're going to have to add it to our books because likely our books are wrong unless the bank is wrong which isn't usually the case if it's on our books but not on the bank then it's not necessarily the case that anybody is wrong because we might have an outstanding item we might know about something a transaction that happened which has not yet cleared the bank that's what we're looking for ideally when you get used to the bank reconciliation you always want to go from the bank to the books because it's on the bank side it has to be on the book side you don't want to go from the book to the bank because if it's on the book side it may not be on the bank side so if you always go this way you will get less confused like it should be easy to do but sometimes you get kind of mixed up alright so we're going to go over here and say that's 65,000 where if I go back on over here it is so what I'm going to try to do to make this easy is I'm going to right click on these instead of opening them in here and I'm going to open them in a new tab so that I can go into my transaction over here and go into my options and I want to say Mark is reconciled and then it is recommended that you only mark as reconciled in cases when the original bank transaction data cannot be imported and that's we're not importing the bank data so it really wants us to connect to the bank but I'm going to say yeah I know we're going to we're going to do it anyways we're doing it this way so then if I I have to refresh my data over here which is a bit tedious you can see it's been reconciled right so now it's been reconciled if I go into my bank statement information it actually recorded it on the bank statement now note it didn't record anything new it didn't like change the balance sheet or income statement it just now is saying hey look we're recording this on the bank statement side which is usually input when you import the data from the system into the bank statement and we're matching that to what you had on your books and if I go into the reconcile area it says you've reconciled all the transactions for this account bank statement $65,000 so we're not quite there yet at this point of time alright let's go back to the transactions tab and then I'm going to go back on over and say okay let's make that one green and so this is green and tying it off we're going to say the next one is $50,000 on $15,000 so here's the $50,000 notice we did it on $12,000 but because there's a slight timing difference but it's pretty close the date should be pretty close so I'm going to right click on this and open a new tab and then I'm going to say that it's going to go from options we're going to mark as reconciled and so we're going to say okay we've forced it to reconcile and then let's update this this one so we can see it here so those two items are now reconciled now if I go to this report to the right the reconciliation report and I update the reconciliation report notice it's it's basically changing because the outstanding the outstanding receipts are going down because the outstanding receipts here have now been reconciled the reconciled items are being pulled out of these outstanding receipts the 87 143 35 so here's the 87 143 35 so we're removing those items let's go back to the first tab and we're going to say alright we found that one make it green and then 757085 so 757085 here it is on 123 and we have it here in 119 okay so let's open that in a new tab and I'm going to say let's go ahead and hit the drop down and mark as reconcile mark as reconciled and then I'm going to say okay mark as reconciled and then I'm going to close this back out back over here let's update it so now that one's been reconciled I'll go back on over let's make that one green and then we've got the 20,500 20,000 on 124 I don't think we have that one here 20,000 oh we do 20,500 20,500 on so let's mark that one off let's check that one off so we're going to go from here check it off and over here 20,500 let's open that one up right click open it and then we're going to options and reconcile so mark as reconcile and next time I'm thinking maybe we'll upload bank transactions to see it that way as well so I might actually un reconcile these and upload the bank transactions just to check that method out as well but there we have it now note that you can see that we might have other deposits for example I have this deposit this deposit happened in January it's in our books but it's not on the bank statement so does that mean that there's an error not necessarily because this happened at the end of the year or close to it it should have cleared you would think if it happened on the 25th but it's pretty close so I can check my bank books to see if it cleared in the next month of February and if it did then I should be okay right because then it cleared in February and this will simply be a timing difference so if I go into my reports over here and I update it so then now I've got the plus the out this one less the outstanding receipts at the $59,000 if I go down to the outstanding receipts we've got this $25,000 the beginning balance we have to deal with as well and there's that $34,000 $72,000 that we'll have to deal with that will clear but as of the end of January that's the difference that's what's making up the difference between the bank and the books so we're creating our bank reconciliation report by kind of backing into and ticking tying out what's on the books and what's on the bank so if I go back on over here you can see we've tied we've ticked these off so we should have this number basically tied out we're still going to have to add that beginning balance in place and then we're going to go into the decreases next time as well we might try next time as well to upload the Excel or CSV file that we made just to take a look at the reconciliation process that way as well so we'll test that out in future presentations