The 3 Steps to Bookkeeping in Excel | Incite Tax
Small-business owners can set up a two-tab Excel workbook to track income and expenses: a summary tab by category and a transactions tab where each entry gets categorized. Export your bank and credit card statements as CSV files, then manually categorize each transaction. Watch for common mistakes like mixing loan principal with interest expense, confusing owner contributions with distributions, and misclassifying meals as 100% deductible when they're only 50%.
Chapters
If you have chosen that you're gonna do your own books and you're gonna do an Excel, okay, this is where we're gonna hop in and go through some steps that you should be aware of, of how to do it. Okay, so step 1, you need to create some sort of template in Excel. Um, a template should include, uh, 2 different tabs. Um, the first one, a tab for summing income and expenses by category, so you want some sort of— it's basically going to be like a financial statement or like, this is all of the expenses that I've had that are categorized to this. Okay? So you need some sort of tab that's going to sum things up. Otherwise, you're going to have to have all the transactions. And then when your tax accountant asks, well, how much did you spend on office expenses for the year? Then you would have to individually add up all the things. So you're going to want to have some sort of tab that automatically does that for you. It will save you time. And then you want a tab for categories. Categorizing all your transactions. Okay, so on the tab for transactions, we recommend having a formula that ensures that your category that you've listed next to the transaction matches one of the categories listed on your summary tab, just so that you're making sure that you're not adding a bunch of different random miscellaneous categories, but that you're sticking within the categories that are available to business owners to use. Okay, it will just keep things cleaner. So one thing that we do have is we do— if you are watching this video and you want to do bookkeeping in Excel, we have made a template for you that we're going to dig into a little later. So you can click— there should be a link in the description of this video for this template that we'll, we'll go through later that's actually made for you. All you have to do is add the transactions and, and do the categorization, but everything else is done. Okay, but the first thing is to Track down a template. There are a bunch of templates made out there, so you can Google one if you don't want to use ours. But find some sort of template or make it yourself so that you can say, okay, this is what I'm going to use for bookkeeping. Okay, the second thing, when you have a template, when you have a workbook that you're going to use in Excel, you are going to be in charge of downloading the transactions from your bank. So you need to do this for all of your business accounts. This includes the bank accounts and also the credit cards. You need all of the transactions for the year. To make sure that you're capturing everything. So we recommend, because you're going to be doing your bookkeeping in Excel, most financial institutions are going to have an option to download your transactions straight into an Excel or CSV file. There are too many to show how to do that, but most of the time you can either do a statement in Excel or you can do a transaction history. And if you hit download, then it will give you an option of what type of file format you want and just choose the Excel option or CSV option. And that will be great. Google is your best friend for almost everything. I guess ChatGPT is, is going to be the real best friend coming up, but Google or ChatGPT to see if you can't figure out in your financial institution portal. And then if that doesn't work, then go ahead and you can contact the bank and see how you can download the transactions in Excel. So that's step 2. So Step 1, create the template. Number 2, download all the transactions into Excel. So step 3 is just going to be categorizing those transactions within the template. So you're going to copy the downloaded transactions into your bookkeeping workbook. And then you need to have some sort of column that's for the category. So you're going to go one by one through each transaction and you're going to assign a category. And we're going to go through an example of how to do this, but like if you have a transaction $55 to Walmart because you were buying supplies for the office, like plastic cups or something like that. Then you could have Walmart, the amount, and then have a column for the category, and you would say office expense. So as easy as that. And you would just go down the list. Again, you want to make sure that you're using the same categories for different things. You don't want to have 150 different categories with slightly different names. You want to make sure that you're using similar name, well, the same names for the different categories so that things stay clean and well organized. One thing is that you want to make sure when you're getting the data that when you have withdrawals from the bank account, it's going to show as a negative number. And when you spend money on the credit card and you download it, lots of times those are going to show up as negative numbers as well. It just keeps things cleaner and easier to sum up safely if you switch the signs of the transactions to be positive so that you can categorize and then have things listed out nice and clean. Okay? So before we go into the actual template, we have a lot of questions from business owners that are doing their own books with regards to like very specific categorization questions. And so we're going to go over just a few of those questions that we see quite a bit and some issues that we see when customers do their own bookkeeping. Okay? So the first one, loan payments. If you have an auto loan for your business and you're making payments out of that, let's say the payment is $200 a month, just to keep things simple. Many business owners want to call that whole $200 some sort of expense. But the reality is that only a small portion of that $200 is an expense. The rest gets ignored on the income statement. Because let's say $180 is reducing the principal of the auto loan and $20 is the interest expense. So that $20 is the only part that you mark as an expense. The rest is just reducing the loan. And so you want to be very careful with regards to that, that when you're making loan payments, you only get to categorize on your income statement the $20 or whatever portion of the interest expense that is applicable for that payment. Okay. These next 2, these get really confusing and it causes a lot of frustration with clients as we get into it. But it's owner's contributions and owner's distributions. So owner's contributions, this basically is money that you are putting as the business owner into the business. This includes any transactions that you are paying for using your personal funds. So let's say that you forgot your business card at home because you're really good and you have a business card, but you accidentally forgot it. And you are buying, let's say, some tools from Office Depot for a job. You use your personal credit card to buy those tools. That is okay. You can still deduct that as a business expense. That's going to be 2 different things. You're going to have an owner's contribution. Let's say the tools cost $50. You're going to have that owner's contribution of $50, and you're also going to have some sort of expense, like let's say tools or small equipment for the actual cost of the tools. So you want to make sure that you're tracking down all of those types of things that you're using on your personal card or personal bank account, because all of those transactions are owner's contributions. Now the flip side is the owner's distributions. So this is any transaction that anytime that you are pulling money out of the business, Then that's a distribution. So this includes any personal transaction that you are paying for using business funds. Lots of customers have a hard time with this. We'll see them like, let's say that a gym owner is buying their dog's food on their business card. Then there's an assumption that, well, I paid for it using my business credit card. That makes it a business expense. like we should categorize that. But the reality is, just because you're using business funds to pay for a transaction like dog food, that doesn't actually make it a business expense that's deductible on your tax return. That is an owner's distribution because you're paying for a personal expense using business funds. So those things just get categorized as a distribution, and they don't show up on your income statement because It's not actually a deduction on your tax return. And so just be aware that, again, just using business funds does not make something a business expense. If you're paying for anything personal, that is going to be a distribution. Now, that being said, there's some confusion around payroll versus owner's distributions versus the payment of contractors or 1099s, 1099 contractors. So owners can be receiving a wage depending upon the different entity type that their business is. So if you receive a W-2 from your business, and you should be receiving a W-2, talk to your tax accountant if you have questions about whether or not you should be receiving a W-2 as an owner from your business, then you're going to have times that you're getting paid as a payroll expense from the company, and that is separate from an owner's distribution. So if you are on the payroll and you, and you have to receive a salary from your business, again, that depends upon your entity type, then there are going to be times that you're taking money out of the business, but it's for a payroll, not for owner's distributions. In those situations, you want to have an account on your, um, on your category list that's owner's wages so that you're making clear this amount I got for owner's wages, and then these, these amounts were for distributions that were outside of the wage. Okay, and then if you have other employees that you're paying, then those types of expenses and costs would be under normal payroll. And then you have contractors Contractors are not official employees of the business. They don't receive W-2s, they receive 1099s. You want to make sure that you keep those things separate. You want to have payroll for actual employees, including yourself. If— and then you want to have an account for contractors that receive a 1099. Okay. So I know that can get confusing. Again, this is why if you ever have questions or concerns or this is getting complex, Google, use ChatGPT. If it's causing way too much frustration, then reach out to a tax pro or anyone that you know that has a connection to bookkeeping because there are bookkeepers out there that are trained to help you do this. Okay? So the last 2 common categorizing issues that we want to cover are meals and travel. This just gets a little messy and we have lots of business owners that think, well, again, if I'm buying a meal on my business card, then that That should be—that's a business expense. That's a meal. But we do want everyone to know and be aware that meals are meant to have a business purpose if you're trying to deduct them on your tax return. So some common situations where the meals are included as a deduction: you have meals for meetings, meals while traveling for work, or snacks or drinks or things like that that you purchase for employees. Like let's say you buy Pringles for the office or something like that, or donuts for the office. So all of those types of things. Things are meals that you can deduct on your tax return. Most of the time you get 50%. There were a couple years in the last several years that you got the full deduction, but most of the time you're going to get 50% of the meals on your tax return as a deduction. But you want to put the full amount and then your tax pro will take care of the rest. The next thing is travel. We want you to, to be able to have fun and take advantage of traveling. And be able to write off as much as you can. So just be aware, in order to write off travel as a business expense, the trip has to be mostly for business, and that is measured in days. Um, and so if you're going somewhere for a conference, let's say you're going to Hawaii— my father-in-law just went to a conference in Hawaii for his work, and, um, he, he was there at the conference for 3 days And then him and his wife stayed an extra 2 days because they were in Hawaii. Why wouldn't they? Because they spent 3 days actually going to the conference, attending the courses and stuff like that, then that trip is a business trip and they can write off those, a lot of those expenses because they were traveling for business. You just have to make sure that most of the time, again, measured in days, Not— we don't have to get as, as minute as minutes or hours or anything like that, but most of the days have to be for a business purpose, and then that's going to be something that you can write off. Okay, this is a lot of information, but those are some of the most common categorizing issues that we see.
