Excel Bookkeeping Walkthrough | Incite Tax

Let's hop into the template that Insight has created that you can use if you so desire. So if you are going to use Insight's template, the way that you have to do it is you have to make a copy. So you come up here, make a copy, and then you can, you can save it to your drive. This is a Google Sheet, which is very similar to Excel. It has a little less functionality, but it's a free service, which is great. And then from there, when you make a copy, you can actually download your copy and do it within Excel. So you can go to download and Excel, and then you will have this file that you can use in Excel, or you can just use it in Google Sheets. But again, you have to make a copy because if you just try to use Insight's direct template, it's going to say you don't have access to edit it because it's the template. We don't— we can't have everyone accessing how to edit it. Okay, so there are 3 different tabs here that we're going to go over. This first one is the financial statements tab. This tab is built to list all of the different categories that you can use to categorize your transactions. But then it's also built to sum up all the revenue that you have, sum up all the expenses that you have, and then show you your net income or loss up to date according to the transactions that you have. All of these are formulas that you don't want to edit because they are pulling from this transaction list. They're pulling from this column right here. And they're matching to the category based off of right here. Okay? So again, these are formulas. We put a little note in here, don't edit. And it's going to just automatically take in all of the information that you put in the transaction list. So there's the income statement accounts, the revenue and the expenses, and it's going to show you the net income and loss. And then we have our balance sheet accounts. The owner's draws, those are actually, again, if you remember, the balance sheet is assets equals liabilities plus equity. These owner's accounts are equity accounts, so they show up on the balance sheet. And we want to have the total— we want to have the net amount of either did you contribute, and if so, how much, or did you have a net distribution, and if so, how much? Okay. The other things is at the end of the year, when you're getting ready to file your tax return and giving your information, or maybe it's not at the end of the year, if you're getting a loan or something like that and you're having to provide financial statements, Then whatever you need, then you'll have your income statement up to date. You just need to make sure you have all the transactions for the period that they're asking for. And this will be your income statement from here to here. But then for the balance sheet, you just need to have this distribution contribution amount. And then you want to put the balances of the bank accounts, credit cards, and any other loans and stuff like that. Now you might have inventory or equipment or some bigger assets like an office building and things like that. If you're getting into that territory, I recommend either taking some basic bookkeeping or accounting classes or hiring a bookkeeper at that point to make sure that things are correct. This is only going to be good for you to do on your own if things are pretty simple and you don't have as much of that stuff to take care of. So that's what this tab is. These ones have to be manually entered, and then the rest of them are all formulas. So now let's hop into the transaction list. So we have some important notes on here just to make sure that if you're using the template that it's used correctly and to your best advantage. So the first one, when copying transaction data, you want to only insert the data into these columns, the date, description, and amount columns. The columns in red are formulas, and we put a little note here as well. You don't want to edit these because they're, they're changing things so that the template works correctly. Okay, so you wanna avoid changing those. But you do, if you have your transaction list, then you wanna just copy the dates, paste into this column, copy the descriptions, paste into this column, and copy the amounts, paste into this column, okay? If you ever accidentally paste over one of the other columns that you shouldn't have, Ctrl+Z or this button, undo, it's the best thing in the world to be able to just be like, oh, I messed up, let's undo that. Okay, so another thing to be really important to be very aware of is you are not going to categorize if you're using this template or your own template in Excel. You do not need to categorize transfers between different accounts like credit card payments or transfers between bank accounts. The reason being, the purpose of this categorization is to take transactions and assign it something on the income statement or an owner's contribution or distribution. Things like transfers between accounts or credit card payments, unless they are interest payments on a credit card, the actual transfer is not hitting the income statement. It's just taking cash from one spot and moving it to another. That's not going to show up on your income statement. That's just moving cash around. So you don't categorize those. You can just delete those transactions or you can leave the category blank in this template. For this template specifically, expenses and all transactions should be listed as a positive sign. And so again, this column is taking all these numbers and just doing the absolute value just to make the financial statement make more sense. Instead of having all of the expenses show up as negative numbers, it's showing as positive numbers so that you can see total expenses you had $1,900 in expenses. So again, that's specific to this template. It doesn't matter if in other templates if you're using negatives or such. If you need to add a different category than what is listed on here, that is completely fine. All you have to do is add a row. It's easier if you do it in the middle of different— of the different categories. And you can right-click. There's different ways to add this, but you can insert row above. The great thing about this is if you do it in the middle, then the new thing that, the new row that you've added will automatically be included in the summary. And you could add new account categorization to add different things. Now we do want to caution business owners to be careful not to add way too many different accounts. Now this is already quite a few accounts. So there are a lot of different things that can fall into these 40 different expenses. It gets overwhelming if you get too detailed with your bookkeeping, and then you and then business owners stop doing it because it's overwhelming. So it's cleaner if you just use the the ones listed here. But again, if you ever need to add more, that's okay. Just add it to the financial statement tab, and then it will work in here. Okay, this one is a weird one. But if you have business expenses that you paid using personal funds, then you want to add the transaction twice. The reason for this is that we kind of went over this earlier in the video, but— and we'll have a specific example here that we'll go over in the future in just a couple minutes. But you need to have one of the lines that's marked as an owner's contribution like it says here. And then in the second row, you'll apply the correct expense. So like in the example we talked about earlier, if you bought tools using your personal card, but it was a business expense. You'd have $50 that was marked as an owner's contribution because you paid for those tools using personal funds. You contributed those funds to the business. But then you'd also want $50 to small equipment or tools as an expense. So you'd have to have both of those lines. So if you're going through and adding transactions from your personal card or your personal bank account that were business expenses, then you need to add those twice. And the first time in each of those instances, you have the category as owner's contribution to make sure that you're capturing the contribution part, which is going to be beneficial to you, and also the expense, which is also going to be beneficial to you. Okay? So now let's hop into some examples. So these were transactions pulled from a bank feed. You can see that we copied in the date, we copied in the description, we copied in the amount. And then over here, Honestly, as you're doing it for the first time, if it was Walmart Supercenter, then you could come over to here, copy this, and just paste it over. Now we created this formula over here that says, let's say that you're typing in and you just put supply. What this formula is going to do is it's going to say no if you put some sort of category in here that does not match an account on the financial statement tab. And the reason why that's important is you can see right here, this supplies is marking— is marked as 0. But when I change this to match, then it pulls in the amount. And that's what makes your financial statement work so that you don't have to manually add up things. It will just do it for you. So if you've added a category and this says no, then there's an issue and you need to make sure that this matches one of the accounts on here. Okay. So Del Taco, this is meals. R&R, automotive repairs. We have an auto expense repairs category. Gas, auto expense fuel. Now we get to this. This is a payment for a credit card. Now I have 2 options at this point. I can leave this and just leave the category as blank because that will not pull into the financial statements, which is just fine. The other option is just to take the transaction and delete the whole row. So if you're like me and you don't like the blanks because it looks— it doesn't look as clean and as tidy and it makes it seem like you have something that you left undone, you can delete it. But again, you can just leave it blank if that makes you happier, whatever tickles your fancy. So then we have a deposit from Stripe, that's income or sales. Now we have this one on here, this is Corner Canyon Academy, this is a A preschool that has gone through the business account. It is set up as an ACH through the business account. Now, daycare, as important as it is, is not a business expense 99% of the time. And so what we're going to do is we are going to have this marked— sorry, I didn't need to add that row— as an owner's expense. Distribution/draw, I think, is the account, okay? So this is an example of an account that, of a transaction that was personal in nature, but we paid for it using business expenses. So that is something that will show up as an owner's distribution. And instead of showing up as an expense here, it's gonna show up here in the owner's distribution area on the balance sheet, okay? The rest of these are pretty self-explanatory. Now let's say that I was adding a transaction that I paid for using the business funds— using personal funds, I'm sorry, that was business. So let's say that this one was one that I added myself from my personal credit card statement. Then I would want— I'm just going to delete that so it looks good. I need to add this twice. Because this was paying for the internet for the business, but I did it on my own card. Then this first line, I'm going to mark it as an owner's contribution. And then the next line, I'd come in here and I believe we have on our template, it's office expense/internet. And so I'm just going to pull that over. And that is an example of having a transaction that you're adding manually from your personal ones. In order to get credit for both the contribution and the expense, you need to have it on there twice. So you can see that now we have the $85 as an expense, but then we also have the $85 contribution. That is so important because that's going to give you the best tax result. Okay. So those are some examples of transactions. Now, again, Everything can be a little overwhelming as you're doing this. We have created this category guide tab that is simply to list all the accounts that are listed on the financial statement tab. And it goes through, gives a description of the accounts, and some— on some of the more common ones, then it's going to give an example of vendors that you can see when you're doing transactions. Because lots of business owners will be like, okay, well, this makes sense, but How do I categorize this transaction? How do I categorize this one? Well, this will help you to kind of know where to categorize what. The good news is, at the end of the day, many of the transactions that you're categorizing, it's not going to matter whether it's coded to janitorial or let's say office expense. Like if it's if you're coding that because it's something for your office, it really is not going to matter on your tax return. If you do office expense versus janitorial. It just won't. So the accounts are there for the most part to keep you organized, to help you know where your money is going as a business. But don't kill yourself over the individual account, individual transactions and where a $25 transaction should be coded. At the end of the day, most of the time, it's not going to make a huge difference because— As long as it's a real business expense, then it will be accounted for. And that being said, you do want to make sure that you're paying attention to these ones that we've called out specifically. Loan payments, that you're only doing the interest and that you're accounting for contributions and distributions and meals and travel correctly. As long as you're doing that, then it's going to be okay. And if you have questions, then contact a bookkeeping or an accountant professional. Okay, so that is what we have for you. Hopefully this is something helpful. This is just here as a tool if you choose to use it. But good luck if you're doing your bookkeeping in Excel, then it's a great tool if you don't have a ton of transactions going on. It's a, it's a free way for you to do your own bookkeeping without having to pay for others or pay for software or learn a new software. So that's it from Insight Tax. Thank you so much.