Effortlessly Manage Your Billing Excel Sheet for Accounting and Tax

Billing excel sheet for Accounting and Tax

does bookkeeping have to be expensive or complicated in order for you to have a great system for your bookkeeping it doesn't today i'm going to teach you specifically how you can use something as simple and as free as google sheets or words xl in order for you to have a good bookkeeping record system if you're a small business owner you can do this if this is what you want to put your time and efforts into and if you are a bookkeeper then you can also use this for your small business clients in order to keep track of their bookkeeping needs here's the thing things like quickbooks and xero they are great programs to buy into and i'm not by any means telling you to not maybe explore those routes but if you're just starting out or if you feel like you just kind of want to get a taste of what bookkeeping is going to look like and what the needs are going to be something free like google sheets is a great tool to get your feet wet into what recording transactions really is like the thing about bookkeeping is that it doesn't need to be fancy in order to be effective the irs states that any business needs a bookkeeping system but they don't create mandates on what that bookkeeping system has to look like you need to keep a record of transactions and try to categorize that to the best of your ability but it doesn't have to be fancy so today's tutorial is going to be specifically related to what this system could look like and how easy and simple it is to use formulas within google sheets in excel to calculate totals for you to take some of the calculator pen and pencil old system of bookkeeping out of the way so if you've never been on my channel before this is realistic bookkeeping i'm rachel brown and before i get too wordy let's get into it by the way i will have a link for the excel file that i'm going to be using down below please make sure to subscribe to this channel if you find that this is helpful or leave me a comment if you need more videos like this one okay let's get into it okay you guys so as we are opened up in a new google sheet and that's what i'm going to use for the purposes of this video however you can convert a google sheet to an excel file but for this google sheet there's absolutely nothing in it and just in case you don't know any basics of google sheets it's really good to just play around in it for a little bit so you'll notice something that's important when you're doing a file like this for a bookkeeping client in my opinion is understanding that you can add new sheets to a google sheet file so you'll see down here it says sheet number one and if i come over here to this add sheet button it's just a plus then it'll give me as many sheets as i might need and something that might be useful for this for yours for your bookkeeping is you could create a sheet for each month of transactions if that's how you prefer to do it you can also just have one sheet with all transactions included in it and make sure that it is categorized and sorted by date chronologically so you don't have to have 12 different sheets in order to make sure that your transactions are easy to track by month however you might want one sheet for your transactions and for your banking needs and i could double click here and make this transactions and then the second sheet might be nice to be a profit and loss statement and what we can do is in between these two sheets we can actually have them talk to each other through formulas that will pull information from one sheet into a next okay so when you're in google sheets you can obviously type um words as well as numbers and you can um format these things to be as beautiful or as simple as you need them to be give myself kind of a little bit of formatting so that not this this whole blank thing looks a little bit more usable okay so all i did was maybe spend 30 seconds formatting so that this looks a little bit nicer to me this is usable and friendly to look at visually with your eyes which although that might not seem like a big deal it does matter when you're starting to look at these um files for usable information aesthetically you need it to be pleasing to your eye enough so so that you can actually look through it and it doesn't look too tiny or you can't read what it says okay so all i've done was i made four basic columns here i made a transaction description a date amount and a transaction type and this column is going to be really important because the transactions you're going to fill in given receipts or maybe bank statements or maybe a check register or a check ledger but you as their bookkeeper or you as your business owner you need to focus on the transaction type and categorizing them by irs regulations this doesn't have to be scary it really is more about you being comfortable and understanding the types of categories that a transaction could fall into this is something that i'm going to start to teach you some excel and google sheets formatting and formulas is that you can have google sheets be really helpful to you so that you don't have to constantly go back to the irs guidelines and check what type of categories exist so here's what we're going to learn we're going to learn how to do something called data validation and what this means is that each of these boxes below transaction type are going to have a drop down menu for you to select what a transaction might fall into so i'm going to show you that really quickly what we're going to do is we are going to i'm just going to click on one cell here and i'll show you how we apply this formula to one cell and when you click on that now you're going to go up to where it says data and you're going to go down to data validation what this is saying the cell range that you've clicked on is in our transactions sheet so this transactions with an exclamation mark means it's pulling from the transaction sheet in google sheets that we're in in cell d2 that's the one that i clicked on and then it says criteria list from a range i don't want a list from a range i want a list of items and then here is where i'm going to type in the list of categories of tax categories that i want are as options for transactions as i go through and classify them as a bookkeeper so what i'm going to do is i'm going to paste that in here but but don't worry i will have a list of all of these options later on in this video for you to copy for your own use so i'm going to go ahead and paste that in here so i have entered all of the types of transaction categories that i would like as options as i go through and describe transactions and categorize them and then i'm going to make sure that it's clicked to show the drop down list in the cell and hit save and all i've done is for one cell i have made this option just wanted you to see where we're using this so now let's say that i automatically had a transaction that was put in here maybe i was given a receipt by the business owner so now what i need to do in order to make sure that this mcdonald's receipt is categorized so i'm going to come over here and i'm going to click on the drop down menu and say well this transaction is most likely meals and entertainment and now i have what i've spent categorized to a specific category and that really is one of the biggest things for bookkeeping is to make sure that not only are your transactions there for record but they are also calculated in the right way so that a business can deduct the right expenses that they've made throughout them through the fiscal year so now what i'm going to do is i'm actually going to make this not just a drop down menu or option for one cell but i want all of the cells in this column to have that i can do i can go back to this one specific cell that i've given this option for i can go back to data and data validation and instead of just having it be d2 you can put a little this little colon symbol right next to here i'm going to show you again d2 right next to it and i can make it go to d d100 and so now what that is going to do is i hit save is it's going to give all the ones from d2 all the way down to d100 which i don't even think my yep right here d100 has this drop down menu option and you'll have the same options over and over again you could for the next transaction it could be bank charges and interest and for the fourth one down maybe it's meals and entertainment again and so you'll have those options at your fingertips to use for all of them now why this is going to be important is because you are now going to be able to pull information from this sheet over to the profit and loss statement by getting google sheets to sum up all of the expenses within a certain category and that's really going to save you a lot of time and effort now before i do that i'm going to kind of go back here for a second i want to teach you some very very very basics of math in google sheets so what i'm going to do is over here to the side i'm just going to teach you a couple of things and i'm going to give myself a list of examples i'm actually going to fill in some transactions so that i can use them as examples over to the side so all i have here are some literally made up transactions and just as a blurb here really quickly mcdonald's and subway would not actually be tax-deductible for a business unless they were specified to be a business lunch where you were meeting a client and generally not 100 of that expense is tax-deductible but generally it's about 50 so just want to give you a quick quick blurb on that so you're not trying to take off hundreds and hundreds of dollars every time you go to subway or mcdonald's as a business owner now to learn some of these basics of formulas in excel and google sheets what you need to understand is that in order for google sheets to pull information and do something with it you always have to start with an equal sign so you'll see i've typed in the equal sign and now google sheets knows that i'm about to tell it to do something instead of me just typing in an amount or typing in a word so the one that's most useful for basic accounting is sum so that's literally the word s-u-m and then you'll see that because google sheets knows i'm doing a formula it's actually going to give me a list of all of these things and i don't need it to do that right now i just need it to sum or add together some values and so this might give me a total of several different transactions or maybe the whole list of transactions and so what i want to do next is i want to enter into parentheses what i want it to sum there's a really easy way to do this once you've done that parentheses you can actually just go click on whatever i'm just dragging that across whatever cells that i want it to total so if i wanted it to total all of those i'm also then going to close the parentheses google sheets will do this for you if you forget and then hit enter and now what you'll see is that this number this total that they've given me maybe i'll even put total beside it make this bold that total is made up of specifically the numbers that are over here in the list right now what if i said oh that rent bill was wrong it wasn't 150 it was 145. i don't know if you've noticed but this total changed it changed by the five dollars that i changed that amount for okay now you have to make sure that whatever you're summing whatever range that you've put in there is correct so if i went and i forgot that i'd already set that to be just those four cells now when i enter a new one for travel travel website and let's see this was on the 1st of august and that was 15 and that was travel this didn't change at all 231.51 cents didn't change even though i put a new total underneath well that's because if i double click on this cell it is not adding in that cell there i only did c2 through c5 and just in case you don't know what that's referencing c is the column and 2 3 4 5 are the row numbers if i wanted it to add in that 15 dollars that i added i'm gonna need to go and make this go to c6 or you could make it go to c100 and now when i put in a new one here for another mcdonald's on the same 8 1 20 21 for 20 it's now going to add that to that total because i've made sure that that sum is the entire column or at least through c100 so that every time i don't have to keep going back and changing it so some is really important and useful for bookkeeping you can do the same thing with um you can subtract things so let's say that i wanted this total to now be a sub total and i wanted to take out i wanted it to be this exact same amount i want it to be the 266 and 51 cents from above but i wanted to take out i wanted to subtract the professional fees here well now it is the total of all of the things in that column but minus that one professional fee so you can also subtract things what i would say is that it's really good to play around with this and kind of see not a client's books or not in your own business books but make a spreadsheet specifically for you to play around in and make sure that you can get your feet wet and you know what you could even go up to the spreadsheet title and put playground this is where you're literally playground bookkeeping and that way you know that this is where you can go and test things out to see if you feel like they're going to work for you or not the next thing that i'm going to teach you is about how to pull information from this sheet to a profit and loss statement what i've done in my profit and loss statement sheet is i've really just made this super basic in order for me to categorize all the totals from my transactions sheet and in a profit and loss statement essentially the basic idea if you've never had a one made for your business before if you've never made one as a bookkeeper is literally to show what a business has made what they've spent and whether they have profited or they have loss in that month or in within that given time range so what you want to be able to do here is you want to make nice totals so that you can look at have you made something have you lost money and if you've made or lost money where have you made or lost that money did you spend way more in advertising than you should have did you spend more on office expenses than you should have or maybe you're really proud of yourself because your repairs and maintenance are low within that given time frame so what i'm going to show you is going to sound really complicated at first but what this is meant to do is it's meant to give you a formula to pull from the transactions all the nitty gritty through receipts or through your bank statement that you filled out in the transaction sheet and pull it over here to make a nice pretty picture for you without you going back and forth over and over and over again since you've already made a category for each transaction now we can put the totals where they need to go in the expenses so what i'm going to do before i actually make any of the numbers appear in the columns over here to the left i'm just going to paste here in a cell for you so that you can see really quickly what the formula is going to look like and i'm going to make this print a little bit bigger so you can see it really easily here so the formula and you'll notice that this isn't pulling any information because i didn't put the equal sign at the beginning that's really important the equal sign at the beginning is going to make this really do something but right now i just want you to see the words and understand what this means and at the beginning of this it says sum if so this is very similar to the formula that we used before where we're adding things together but it's only going to add things if something else happens now let me explain the rest of the formula it's going to add things together if they are in the transactions sheet so see how that transactions matches the sheet name down here it has to exactly and the exclamation mark behind it lets google sheets know that it is talking about a sheet and not a cell or something like that so it's going to go to the transaction sheet and it's going to look through d2 through d100 let me show you where that was d2 through d100 are all of these cells so that formula is going to come over to this sheet and look in those cells and see if they say this word so there's a comma and then in quotes it says advertising so this specific formula i'm showing you would go to those columns go to those cells and see if they match advertising which is one of our expense categories if it matches advertising what this formula says to do next is take all of the things from that transaction sheet from cell c2 to c100 and add them together let me show you where c2 through c100 is that is this column so i clicked c2 through c100 you could make the c1000 if you wanted it to be that big but it's just going through all of those things i don't have that many transactions in here so i didn't need the column to go from c2 to c1000 100 would survive would suffice c8 would be fine as of right now but so let me explain this formula one more time it's going to pull in that other sheet it's going to come back to this one called transactions and it's going to look in this column and if that column says the word advertising then it is going to go here and pull that amount and add them together so i would know how much i spent in advertising so let me show you what that does when i put an equal sign in front of it and put it in my advertising category if i put this up equals sign and then paste that formula and hit enter it now gives me a zero dollars why does it give me a zero dollar total well if i go back to my transactions i have nothing here that's in advertising but what if i said that mcdonald's was advertising we obviously know it's not but just for the sake of the argument let's see what i now have in my profit and loss statement well i have twenty dollars now because i have a twenty dollar expense of advertising so let me show you how you would change this if you were now doing bank charges and interest well you're still going to put the equal sign in this in this column or in this cell enter in our formula but now instead of advertising in quotes i'm going to be looking for things that say bank charges and interest end my quotes enter but i still see a zero that's because if i go to my transactions i don't have anything that our bank charges and fees or interest what about insurance same thing i don't have any insurance right now let's go to meals in entertainment though because we know we have some expenses within that category equal sign for my formula and then go up and insert this formula that we've created but again i don't want advertising in quotes instead i'm looking for anything that was meals and intertain mint and quotes enter and look we don't need to do the autofill i don't want it to do that right now but now it says 27.76 let's see if that's true let's go back to my transactions sheet what i'm going to do is i'm going to have an equal sign over here in a random cell and i'm going to total in this is a meals and entertainment cell plus here's the other one that's meals and entertainment let's just see if that total matches it does 27.76 is exactly what the other sheet said with the formula pulling that information together but see if you were going to make a statement a profit and loss statement within this sheet that did that you'd have to go through and select all the ones that were meals in entertainment whereas this one pulls it nice and pretty from the previous sheet for you what i'm also going to do is i'm just going to go ahead and format this entire column to be currency so that it looks nice and neat with my money symbols and then i'm going to go down to this total here and i'm going to make sure that that total is equal to the sum of this entire column so that once i fill them all out it's going to total them for me now let's just say i'm going to enter some numbers here that my income for the time range i'm looking at was 500 in cash and let's say that in checks i had 14 1420. i could again make a total here to be the sum of my cash and checks in term and now what i think is pretty important is that this profit and loss box that i've put over here i want that to show me whether i made something or i lost something so this cell is going to be equal to what i made that total minus what i spent and is going to tell me that look at that i have profited 18 and twenty 072.24 cents now this isn't fleshed out because i didn't fill in all these other categories but this is really just to give you a perspective of what you could do and how this could round out something that's gonna categorize things really quickly for you and that now once you have this set up you can come back to the transactions and as you enter things here it's gonna change that other sheet without you going back to it at all okay you guys i really hope that this has been helpful it's been a lot of information crammed into one i am planning on making more videos similar to this style in the future but to be honest for excel this is all that you need you guys please go click down below and find this template hopefully you can play around with it and make it the way that you want it for your business hit subscribe i'm rachel brown this is realistic bookkeeping and we'll see you next time