Create Your Proforma Invoice Template in Google Docs for Accounting

Proforma invoice template google docs for Accounting

if you are overwhelmed with all the different bookkeeping softwares that exist out there and you're looking for just a super simple method to keep things organized this video is for you now it's no secret that I talk a lot about software on here like QuickBooks Online to help you with your finances however what if you're just starting out and you don't need all those bells and whistles you just want a way to keep things organized at a low or no cost to you well thankfully if that's you you can probably just use a simple spreadsheet especially towards the beginning of your business in order to keep yourself organized and I'm going to show you in this video how you can set up a Google sheet for free that helps you actually create a true profit and loss for your business so make sure to stick around because by the end of this video not only will you have your own Google sheet or Excel template to be able to track your business finances and do your bookkeeping in but you're going to have some really important tips and tricks that are going to help you do this efficiently and effectively hi everyone Jamie troll here your trusted CPA bringing you all the tools and resources you need to keep your business informed organized and profitable now like I said in this video I'm going to show you how to create your own free Google Sheets bookkeeping system and that is going to help you to be able to run an actual profit and loss and be prepared for tax time but if you'd rather shortcut that a little bit and you don't want to get into the spreadsheets yourself and you'd rather have one that's already created for you and has exactly what you need then definitely check out jr.com profit loss and grab my already created pnl template and dashboard and you can use that to follow along with what we're going to do here today so with that let's jump on into Google Sheets so obviously the very first thing we're going to do is open up a blank Google sheet if you prefer Excel that will work as well so what you're going to do first is on this first tab this is going to be the chart of accounts okay so we're going to use a few different tabs in here to keep things organized but this is going to be where you come up with what the accounts you're going to use I.E how you're going to categorize your income and your expenses and believe it or not you actually have quite a bit of leeway to do this how you want to so I'll walk you through how I determine what my chart of accounts is going to look like and how you can customize yours so the first thing I've done here is I've made this ourchart of accounts Tab and I have put in sections for income expenses and for other non pnl things we'll talk about a little bit about what that means here in just a minute but ultimately we're going to come up with accounts that fit these different areas now if you have something like inventory or significant like big fixed assets like Machinery you might want to go with something that is a little bit more robust from an accounting perspective this is really meant for simple income and expenses and those transactions from your bank and credit cards and keeping them organized in a really simple manner but if you have a more complex business you might want to invest in a software like QuickBooks online and do things a little bit more uh the long way but in this case if you have a more simple business this is going to be perfect for you so I'm going to start by coming up with what I want the accounts to be here and if you're not really sure right then one great way to start is to go look at the schedule C which is what you will file for tax purposes assuming that you aren't a corporation right if you are a a single member LLC or you are just a soulle proprietor just getting started then what you're going to file for taxes is with your personal tax return and it's called the schedule C and by looking at the schedule C you can determine what you're going to need to report for taxes now again it doesn't have to match exactly but just knowing the level of detail at which you need to be able to report for taxes can help you as a starting point for coming up with your own chart of accounts so here I've pulled up a schedule C I am going to link this down in the comments too so that you can see what this looks like but this is the tax form that will end up getting filed with your personal tax return once you start a business so what you're going to do is you're going to have to report on income right and that's just going to be your sales income and you're going to be able to show your returns and allowances if it's applicable to you that is shown separately and cost of goods sold this is largely more for a product based business again this might be better suited to using an accounting software if you have significant cost of goods sold this may not apply to you otherwise and that's pretty much what you're going to have to be putting in here and some other income if there is any a lot of people aren't going to have anything additional their income is mostly going to be sales income and then down here you can see the expenses so again it's broken out by advertising car and truck you're going to have separate for interest legal and Professional Services office expenses wages etc etc so you don't necessarily have all of these a lot of businesses will not have um many of these so if you know you're not going to have it then you don't need to put that on your p&l because it's just an account you're never going to use and again you can go more granular than this so if you actually want to have multiple different accounts for office expenses that maybe one is for you know office expenses like paper supplies one is more for software right type expenses you can break that out further on your p&l now you don't want to go crazy you probably don't want a three-page p&l because it might be overwhelming but think about the level of detail you would want for making business decisions it may be more detail than what is required for taxes and then come tax time you just add up a couple of those different accounts that you have and Report them um but you do want to know what you're going to have to report on separately so that you don't accidentally intermingle it with a whole bunch of other stuff and then you're you know it tax time really scrambling to figure out okay um I put all of my you know travel together um and I need to be able to pull that out so for example maybe you um put all your travel and meals together and didn't realize you were going to have to split those into two line items that one is important because those are treated differently for taxes so you want to make sure that from the get-go maybe you have a travel account and that's where you put hotel and transportation but then your meals are on a separate line item so that's why it's a good idea to start with the schedule C to make sure you're going to have the detail necessary when it does come tax time so what I'm going to do is just start kind of putting in some accounts that I think are the ones that I want to use for categorizing my income and expenses so this is where I'm going to start and I generally recommend that you um put in a numbering system and the numbering system you use doesn't really matter it's just to help keep things organized within your chart of accounts and make it easy to find the things that you're looking for okay so otherwise it's always going to default to organizing things alphabetically which is super annoying and it's much easier to have your income come first then your expenses then any non pnl stuff when you're looking to categorize this which you'll see when I get to the next tab so you can see now I put in some starting accounts that we can use as we go and categorize this and what I've done is I've made the income account starting with 100 expense account starting with 200 and I just did the other non pnl things that I want to track starting with 300 now again I used essentially what I saw on the schedule see and I picked out the ones that were most interesting to me but let's say actually I want to be able to break this out a little bit further right I can come up with my own so maybe I want to have uh oh I don't know maybe you want to have a different software expenses line which you'll probably put together maybe with office expenses or depending on what the software is maybe it ends up being with advertising when you file your taxes but you can make that line item so that you can separately track things that are important to you so that's why I've left a little bit of room here because sometimes you don't need you know that you need another account until you go to categorize your transactions and realize nothing really fits now you don't want to create a new account for everything it doesn't have to be a perfect match um but I think it is a good idea if it's something that you want to be tracking separately and it's going to help you to be able to see the total of those expenses separately to be able to make a separate account for it again don't go crazy but think about what's most useful for making business decisions what do you want to be able to see so in this case for me right I could just have a sales income line but I decided to have a sales income and affiliate income those are my two main sources of income so I'd prefer to track them separately I don't have to because for taxes I'm just going to add these two lines together but for my own business purposes I prefer to see them separately so I'm going to track them and categorize them separately also returns things like that I'm going to separately show that because that is something that shows up on on the tax return at the end of the day and if you're wondering about these other non pnl these are things that maybe you spend money on but they aren't actually income or expenses or maybe it's money coming in that isn't actually income or expenses so for example maybe you're making an owner contribution you just set up your business you set up a new business bank account which I highly recommend that you have and you contribute money into that business bank account so that you can start paying your business expenses from that account so that Capital contribution into that business bank account maybe from your personal funds right that's not going to be something that you want to categorize as income because it's not really income right that's just money you're transferring from yourself to your business and so in that case that's considered an owner's contribution you don't want that to end up in income because if it does you're going to end up accidentally paying taxes on that as if it was income from sales right so not all money coming in is income not all money going out is expenses okay that's important to know because that's something that can mess up your whole p&l and can impact you come tax time so you want to make sure that those types of things are not getting captured in your profit and loss that you're going to use for taxes so you're going to have maybe an account for owner contribution maybe an account for owner's draws right again if you're not a corporation you're just taking money out of your business to pay yourself as a draw that is not an expense that shows up on your income statement on your p&l okay that is just a draw from your profit all right I have more videos that go into more depth about this if you want to understand this a little bit more but for the sake of this just make sure that those don't get categorized as income or expenses when you are moving money personally from yourself to your business and vice versa and similarly transfers between accounts let's say you're paying your credit card bill right so you're paying your credit card bill with your business bank account those things should offset um we want to make sure that they offset in general transfers should always net to zero but we don't want those transfers to show up as income and expense and inadvertently impact our taxes right so that's why those are the things that I put down there there may be a few other things again if you're buying um Machinery or you have debt payments that could be non pnl but that gets a little bit more complicated and we're not going to get into all that here again this is a simple method for simple businesses okay so next step after we have made this we are going to make a new tab so you're just going to hit the little plus sign down here that's going to create a new tab for you and this is where the magic is going to happen Okay so this is where we are going to come on and this is where we're going to be putting our transactions now what you want to categorize for your transactions is going to be coming from things like your bank account and your credit card statements and I will use this time as a little caveat get on my soap box for a minute to say to make this much easier on yourself right you're going to want to separate your business and personal so it makes it much easier on yourself and if you're an LLC that's actually required to maintain that personal and business separation so even if you haven't made dollar one in your business if you are starting to have expenses already it's a good time to go ahead and grab a business bank account I do have a few that I recommend relay and Novo are my favorites you can go see which is right for you if you go to jr.com banking they are both completely free because please do not pay fees for business banking lots of banks will charge you fees but there are lots of options these days for great business banking that is free so go check out that page if you don't have a business bank account yet please use my links cuz they can also give you some extra benefits and freebies and money back so definitely use those affiliate links thank you thank you so first first things first what do we want to even put in this this spreadsheet what do you want to make well I'm going to have you put in your account so what bank account is this from is this your credit card account is this your bank account we're going to have you list that then I'm going to have you put in the date okay now this is usually going to be the transaction date the date that you spent the money and even if you are spending that money on a credit card and that credit card you're paying later it's going to be the date you spent the money and charge the expense on the credit card not the date you paid the credit card okay um so you're going to have account date you're going to have description so that's what that actually was you're going to have of course the amount and then you are going to come over here and you are going to put the category okay so this is going back to your chart of accounts so what are we going to do first once we've set up what we want these columns to be well we're going to go to category and I'm going to show you how instead of having to type in 100 sales you know every single time we are going to be able to pull make a drop down to pull the account that we want from here so since we already have these listed what we can do is we can hit here and in fact I'm going to have you just go ahead and go pretty far down and just select a whole bunch of cells because again as we add transactions we don't want these formulas to break so I'm just going to go down like 500 cells you may not need that many if you don't have that many transactions but it's not going to hurt to have that go farther down than it needs to and then you're going to go to data and data validation and once you're there you're going to see this add Rule and that is exactly what I want to do I'm going to add a rule and it already knows what range because I've already selected that range to apply it to and I can either list out all the options I want in the drop- down but since I already have the list over here I don't want to have to repeat that so I'm just going to say drop down under criteria go to drop down from a range okay and then you can hit select data range and you can go ahead and navigate to this other sheet and you can go ahead and pick again I tend to go a little bit further past in case I add accounts I don't want them to be left off and I'm going to press okay all right and you can see here that all of a sudden now we can see all of these accounts showing up and even if you wanted to change the colors like maybe um just if we want to be fun with it maybe we put all of our income accounts as blue and maybe our expense accounts are all purple right like this is not necessary but this is the fun that we can have when we use Google Sheets um and so you can kind of change it that way just to make it easier to find where something starts and ends and we're going to hit done and then we're going to navigate back to this tab and all of a sudden when we hit category woohoo check this out we can see all of these different categories that we created and they should be easy for us to select so we'll just just select one um I would usually just go ahead and make this bigger so that you can see depending on what it is right and what you're going to do then is you're going to put in the information from your bank and credit card accounts and then select the category so that's the next thing that we're going to do you could do this as often as you like the key is making sure you know where you started and stopped so that you don't double count anything and so that you don't uh forget anything right so maybe it's monthly you pull um all your January activity in so you can key it all in if you want to or I'm going to show you how to pull it from a CSV in this case let's start with my credit card I have a business credit card so we'll just put Chase card here so that we know that that's what I'm pulling this from so you can see on the screen now I just pulled in the CSV file that I ran from my credit card so if you go into any credit card or bank account usually they're going to give you an option to download a CSV so sometimes you can put in the date range um that you want to download it for sometimes it can just be statement by statement but usually there's going to be a way to put it into spreadsheet form okay so once you have that in spreadsheet form then you can just copy and paste and like I said you can pull this for a month at a time if you wanted to um and so let's say I'm pulling for the month of January and in this case instead of having to key all of this in you know by itself one by one I'm going to look look and just align the columns so in this case the date I'm going to pull the uh transaction date usually that's what you're going to want to go by even if the posting date usually maybe a day or so off I typically am going to go by the transaction date because that was the date I actually made the purchase I'm going to go ahead and just put that right here and the columns that you're going to see on here may differ in this case I used the description and I copied that in but I'm not going to use these categories so these are categories that my credit card company came up with these don't align with my chart of accounts or what I need for accounting purposes so I'm going to kind of ignore them they also don't really understand my expenses considering the fact that they put Eno insurance as office and shipping so if you have a category listed don't think that you need to keep to that that's just your credit card or your bank account trying to be helpful um but that's not its job so we're going to just pull the account tab as well into here and then once we're done with that again we can come in here and that's where we can go ahead and categorize so in this case software expenses for this one right and I'm just going to go through and categorize all of these different things again if you're doing this and you realize you don't have an account that you need right or that you'd like to see you can always go over and add it and it should let's see we'll test it real time but once we do that it should show up right here I didn't change the color but you're going to see it showing up on there immediately once you create the account yay and then what I'm going to do is just pull down because these are all Chase card so now I've gone ahead and populated some information from uh a business bank account as well to show that and one thing I added in that wasn't in the original is I wanted to show you an example of what happens if you see like a credit card payment coming through from your credit card account or through from your bank account right and that's where we're going to use these fun uh accounts down here the 300s right so in this case it's a transfer between accounts you're essentially paying your credit card with your bank account and in that case you should see right if you're pulling in both accounts you should see both sides of that so I do they net to zero and I want to put them into these same accounts so ultimately we don't want that to show up as income or expenses we're just moving money around um we don't want to double count anything and so therefore you just want to make sure those transfers are categorized correctly cuz that can mess things up the other thing I did was put a note up here because depending on your bank the way that they pull in information may look different okay so what you're going to want to check for the purpose of this is that everything is consistent in the way that it is going for example outgoing cash should be negative right expenses should be negative and incoming cash should be positive this can mess everything up if maybe your actual bank statement um has two columns and they put everything positive and then you're going to have to go in and add those positives and negatives so that can be kind of frustrating and annoying depending on how the bank shows your statements but the most common is to show expenses money going out as negative money coming in as positive okay so then we're just going to go ahead and um we're going to put in the rest of this and we are going to um categorize all of that fun stuff so now we have them all categorized so what happens now so here's where the magic happens in Google Sheets I'll show you the formula that you need so first of all I want to go ahead and change these tab names just to make this a little bit easier to manage and know what we're doing I don't love sheet one and Sheet two so now we got short of accounts and transactions name them whatever you want so we're going to come over here to right next to sales income so we're going to do a sum if and what you can do when you look at this is it'll tell you exactly what it wants it to be so a sum if you're going to first pick the range that you want it to um look at to determine if there is a match then the criteria for the match and then the sum range the cells you actually want it to sum so just to make this a little bit clearer what it says range we're going to come over and say we're essentially going to be looking up everything that is 100 sales income that's what we want to know that's what we want to sum so we're going to come over here and the range is going to be these now again I tend to go all the way down because um as we add transactions we don't want to be in a situation where things are missing because it didn't go all the way down so I would take that all the way down to wherever you ended that drop- down menu and then we're going to do this and then we're going to say criteria well the criteria is we only want to sum things that are the 100 sales incoming account right here so we're going to say before and then and we just click on it and then another comma and you're going to want to pick the sum range so if it matches in this column right we're going to want it to uh sum this column this is the column that has all the transaction amounts in it so we're going to sum it again we're going to go all all the way down to wherever we ended those drop- down menus just so that we have some room for the future and then we're going to close parentheses and you'll see it pulled in 2400 which just happens to be the one sales income it pulled that in it recognized it and it pulled it directly in here now we can pull this down and it will work to a degree but there is a watch out here with this that I want to tell you about that often is a reason that some of these Su ifs and the these formulas don't work and if this has happened to you this might be why okay here's your little accountant trick anytime you're pulling in a range and you don't want that range to change right so we want it to stay E5 to e539 because we want it to look from the top right and same thing with the transactions we want it to sum from the top we don't want it to miss something okay now you'll notice if you just pull this down all of a sudden it went to E6 to E5 40 it also pulled the formula down we don't want that we want to lock those ranges so what you're going to do is you're going to come in here and you're going to go old school and you're going to add these dollar signs and the dollar sign goes right before the column letter and then right before the row so that means you're locking the column and you're locking the row you want to do that on both of these you don't want to do this for B4 because we don't want each of these to stay looking at sales income we want that formula to carry down so that one I'm not going to lock but these ranges that it is looking at I want to lock so we're going to go ahead and add those now you'll see when it goes like this it keeps those same ranges but it did update this so now we're looking at affiliate income for B5 okay so you can kind of carry this on down however you want to you can you can X out any of these if you don't want them there um but again it's going to pull in all of that so now this is essentially your p&l and what you may want to do is at the end of this have a profit loss now make sure you don't override the formula here but what you can do is you can um sum these really easily and this is the total profit so we can always again probably have to do a little bit of formatting we have not formatted within here so if you want to go ahead and add your formatting and do it how you want to um you can bold this you can do all the things you want but this is your let's say year to date P and L and there you have it um so you just created yourself a year-to date p&l um again ignore the formatting because this was quick but it basically said you are profitable right usually it's going to be profit or if you were in a loss position that would be negative so this will automatically update as you add new things in here right if you add another transaction in here let's just put it we'll just put the numbers in for right now but let's say you had um a big hit you made a lot of income so we're going to put some additional sales income in there it is going to pull that right on in and update your p&l so again you'll want to do a new one each year you may even want to do a new one each quarter or each month but this is a way to track it and actually be able to see what's going on in your business you can see what's happening with your expenses you can go drill into it if you're wondering what this is you can go search for wages on the next tab if something looks off right but this is the way that you're going to want to do this and again your transactions between accounts yay it Nets to zero that's what we want if this doesn't net to zero it means we're missing one side or the other of the transaction so how simple was that a really easy and quick way to get a pnl up and running and to be able to get away from just having a laundry list of expenses and actually have something you can use and make decisions with now if you're like you know what it's a little basic for me or maybe you don't want to spend the time to put that all together and mess around with formulas and you'd rather have the deluxe version that already exists well definitely check out my pnl dashboard and template it is for one low cost you don't have a monthly recurring subscription to be worrying about and I've already built it out you can have a p&l that you can see by month all the tabs are already created all of the formulas already included in there and summarizing correctly it gives you a fully customizable chart of accounts and it also is going to give you a dashboard with lots of different metrics that you can see really easily with really fun charts and graphs so if you'd rather not spend the time and do all the things and just use the one that's already created go to jam troll.com proofit loss and check it out or maybe you're sitting here thinking you know what I think a spread shet might be a little too simple for the complexity that I have within my business and the things that I need to track well then a software solution may be best for you so if you want to check out QuickBooks Online a little bit further then definitely check out this video I did comparing the different options for QuickBooks online and what it can do plus if you're feeling in the mood to set some financial goals for your business and you want some direction on how to do that check out this video that I did recently that talks all about it thanks so much for watching definitely make sure to subscribe if you liked this content and let me know down below if you enjoyed this more step-by-step tutorial I don't often do these but this one was actually kind of fun so I'll do more of them if you found this interesting if you enjoyed this and you want to know what other resources I recommend for small business Finance Management I have lots of them I have a favorite things page with a list of resources that I have specially curated for you including the best deals on certain softwares to help you manage things like banking payroll and accounting and thanks so much for joining me I will see you next time