Streamline Your Finances with a Payment Template Excel for Accounting

Payment template excel for Accounting

hey everybody paul from avalon here today i'm going to take you through step by step how to use our free bookkeeping template in google sheets or excel you'll learn everything that you need to know about doing your bookkeeping for your small business so let's dive it so here we are we're at the bookkeeping template just click the link and it'll take you to avalon's free bookkeeping template in google sheets and we'll want to actually either make a copy of this sheet or download it to excel if we tried to go ahead and edit this for our own purposes it's locked in a view only state so that's just to keep people from making changes to the actual template version once we download it or make a copy we can do whatever we want with it so here's the example i'm typing into the cell and nothing's showing up if i go ahead and click file make a copy i can rename this to whatever i want so i'm just going to call it my bookkeeping template and i'm going to save it to my drive this is good if you have a gmail account or a g suite account you can have your own copy of it in google sheets and you can edit it really from anywhere you don't need a desktop version of the program or anything like that so now this is the copied my bookkeeping template and i can type into the cells so i can actually edit this one whereas the original template the one i'm looking at here was view only and i can't do anything to it so that's how we copy it as a google sheet if you wanted to use this as an excel file which is totally fine too you can click file and click download or just mouse over download and it'll bring you to the list of things you can download it as i'd recommend microsoft excel.xlsx and it will prompt you to download it to your desktop and you can save it as an excel file and open it up in excel just the same way as you might use it as a google sheet our first step is we're going to check out this bookkeeping tab and we're going to go through what these instructions say so copy the sheet just like i mentioned you're going to want to enter information into these blue cells you can enter info into the other ones but it doesn't really help anything so we want to use those shaded blue cells for your information so that's this section here that's going to be step one so i'm going to type in my name i'm going to type in the year and then it's going to ask us if we charge gschst if you're registered for gst or hst very very similar just different sales taxes and different provinces in canada you can choose either yes or no and it's going to give you some instructions on what to do down below so if we are registered for gst hsc we're going to want to enter both the gross amount and the gst hst amount so we'll talk a little bit more about that when we get into recording transactions here's what it looks like if we chose no it says enter the gross amount only so then we would only enter amounts in this column and not this column so we'll leave it as yes for the demonstration purposes the second question here is did you work from a home office during the year and if you did and if you use that predominantly for your business we can claim home office expenses so we can see that there's a home office tab and we've said yes and now it says please fill out the home office tab and that purple one down below if you choose no it's going to just say please skip the home office tab so we'll leave it as yes for this demonstration too so that's your information you can see we've entered paul 2022 down there it now shows up in this cell it'll show up in the other ones as well and then these are really just to help you understand what to do when it comes to entering transactions if you have gst hst and whether or not you worked from your home office during the year whether you're going to use that home office tab so next up we can actually just start recording transactions and that's all down here in the bookkeeping section we have columns for transaction description we have a column for the date we have gross amount so that's going to be the total transaction including any gst hst or provincial sales tax that you paid on the transaction we have the gst hst column and that's going to be any gst hst that you paid on transactions again this column is only if you are registered for gst so you put any gst agency that you paid in this column or any that you've collected on revenue so we're going to record expenses and asset purchases as well as any revenue transactions in these cells here so we've got a whole bunch uh lots that we can use because we're going to be doing a full year of bookkeeping in this bookkeeping sheet the net amount here it's grayed out it's a calculated cell so if we paid 105 dollars for something and we also paid gst hst of five dollars then our formula just calculates out what the actual expense portion would be and then we also break out the gst hsc paid the last column here in column f is transaction type and we're going to just use this drop down menu to choose what type of transaction that we had here so if it was sales revenue we could choose that account if it was some sort of other expense like rent we would choose that type of expense and we can add a description here so we can say rent for the month or whatever it is and then we're going to add a date as well so let's say it was rent for the month of august i'm going to enter the date in this form i just couldn't make it and now we have august we paid rent of 105 including five dollars of gst and it's being recorded as a rent expense so that's generally how this sheet works there's a way faster way that we can use this to record all of our transactions and i'll show you that next so the way that we get transactions into the bookkeeping sheet in a more efficient manner is to download our transactions from either our online banking or our online credit card account so in this example here i've downloaded a pretend set of transactions for the month of august from my online credit card login so i've got some expenses and you can see there shown as negative numbers that may or may not be the case for your transactions that you download but i do want to show this to explain a concept for the bookkeeping sheet and then we also have this positive one for a credit card payment so we can get these numbers into our bookkeeping sheet in a much faster way by copying and pasting these directly in there before we record what types of transactions they are so as i mentioned we do need to have these all as positive amounts and to do that i can use a formula that's just like this i could say equals this times negative one that's one way to do it and i can drag that all the way down and that's going to get a new column with positive numbers this one's a negative number because i've just reversed the sign here i'm actually not going to be recording credit card payments meaning this was money that came from my bank account and is now recorded on my credit card i don't need that in here so i'm going to go ahead and delete that row and the rest of these i'm going to copy and then paste into the bookie big sheet and so i might want to do it so that it's in the same order as the sheet so i'm going to cut this column i'm going to insert it into here and then now i can copy and paste actually i'm going to need to hide these as well and then i can copy and paste all of these directly into that bookkeeping sheet so i've copied it and now i'll head over to the sheet and paste them in so here we are back at the sheet where we want to enter our transactions and i've got that one transaction in here i'm going to delete it because we don't actually need it that was just for demonstration purposes and what we want to do is paste those transactions in but we want to right click and use paste special and choose values only so as soon as i do that we can see i've just copied and now pasted all of those transactions in here and so i'll show you why i like values only if i go ahead and just paste those as normal it's going to replace all the blue and sometimes it might mess up some of the formulas if we do it wrong or it might change how the date is formatted in here and we just want to keep it in one format so what we do is we right click and we go paste values only so we're most of the way there now for doing our bookkeeping for the month of august you can see it's hyperlinked this one i'm gonna get rid of that and we have just we have descriptions we have dates we have gross amounts we have net amount that's a calculated cell and all we have to do is enter in the gst that we paid on these transactions so to do that typically you're going to have to either have a receipt or you could use a formula to calculate it but for this example i'm just going to say that each of these had gst on them really it's important to keep track of that properly so i'll pause this and record all that gst so there we go i've added in the gst hst that we paid on each of these transactions and it might not be accurate for this demonstration purpose but really you're going to want to hang on to receipts anyway and there's a great program called dext that you can use to upload receipts and it'll keep track of those or you can just take photos of them and keep them in your photo album and they'll be organized by date which helps you a lot you can really just grab the photo and enter in the amounts from that if you don't collect gst hst again we're going to choose no here and it's going to say enter the gross amounts only so we would only enter those gross amounts and leave this column blank our example is yes so we're going to go with that so now we have gross amount gst hst paid and net amount and next all we really do is record the transaction type and it's a drop down menu these are general transaction types and it really doesn't matter too much which you choose i would say choose the transaction type that makes the most sense to you so the drake eatery is my favorite pub in town here so we're going to go with meals and entertainment expense for that one best buy we're going to say that this was actually an asset purchase we'll say that it was a monitor that i purchased for my business and i'm going to scroll all the way down and choose computer equipment under capital asset if you're not sure what the difference is between an expense and a capital asset i've linked a blog article that we wrote about it in the description below generally the difference is that capital assets are going to last longer than a year whereas expenses are typically used up before that year is over or instantly in the case of having a drink at the drink so capital asset computer equipment for this one and i'll go ahead and just record all these transactions you can skip ahead if you like i'm going to talk us through categorizing all of these transactions and it's really a matter of preference what makes the most sense to you when you're looking at your income statement how do you want these numbers to appear on here whether something is office expenses or software and subscriptions really is up to you and your preference so we'll go through these we'll say robin's parking this one could be vehicle or travel i like it as travel i'm going to say vehicles more like fuel and that kind of thing zapier is a software subscription faxage is an electronic fax so we'll call that software subscription it could also be telephone and utilities if you want but either one works fine amazon this was a book for a business reading it's an office expense google we've got that as software subscriptions that's our g suite account hub dock that is another software subscription lots of those going on same with practice ignition microsoft i don't know i'm using that too i guess they do use that cali lots of software subscriptions more parking so that's travel again we'll do that burger crush great restaurant in victoria we're going to call that meals and entertainment momentum dashboard another software subscription purchase interest that was i didn't pay my credit card ahead of time so we're going to call that bank charges and interest bow is another excellent restaurant i guess i'm going out for lots of business meetings apparently amazon another office expense we'll call that carbon that's an excellent software that we use for our internal task management london drugs we're going to call that office expenses again and air canada that's going to be travel so the idea really is to choose the one that makes the most sense to you here and at the very end of this video i'll show you how to customize these so that you can rename transaction types so that you can have custom ones on your income statement so we have all of our transactions our expense and asset purchase transactions recorded for the month next we're going to record our revenue and so there are a couple of ways we can do this we can go into our online banking and do the same thing where we export the transactions to csv and then copy and paste them in here and we're gonna probably have something that looks like this deposit for sales deposit and we're gonna have a date on that and what we would do is we would just record the gross amount here copy and paste it again makes a lot more sense and we can record it that way or one thing that can help if you're doing your bookkeeping all at once for a whole year probably one of the more efficient ways to do it actually is that way as long as you don't want monthly financial statements but to record all your transactions for one year just take a look at your invoicing system and grab a summary of the revenue for the whole year and you can type it in and you could say summary of revenue for entire year and we'll say it was for the year 2022 and let's say we earned thousand dollars in gross revenue and we collected four thousand dollars in gst this was all based on that report from our invoicing system all we then do is we just use the sales account here to record that as revenue this is the only revenue account the rest are either expenses or asset purchases so now that we have this let's pretend that we've recorded all of our expenses for the entire year now that we have all of our transactions recorded we could check out our income statement with our total sales amount no cost of sales if i had maybe a t-shirt business or something where there really were cost of sales i would choose that as a category and record a cost of sales amount we just had operating expenses you saw me record our credit card interest some meals entertainment lots of software subscriptions and some travel and that leads us to 1136.94 in operating expenses with a net income of 78 thousand so we've recorded all of our transactions the next step is to go in and fill out the home office tab so here on the home office tab we also have some instructions it says if we've used a portion of our home for our office fail in the blue shaded cells so just like the bookkeeping template we're going to use these blue cells to record our home office expenses so we want to record first of all the information about our home office how large is your home in square feet let's say we live in an 800 square foot condo and then the second question is approximately how large is your home office area in square feet so you can estimate this or maybe when you bought your home you had a plan that showed the square footage of each room so let's say my home office is 150 square feet out of my 800 square foot condo we can now see that the estimated percentage of my home used as an office is 18.75 and what that's going to do is it's going to take our total yearly home expenses multiply it by 18.75 and get us our eligible home office expenses so next step is to actually go in and record our annual home office expenses and that includes things like heat electricity insurance maintenance all these things you can enter in here these are total annual amounts so what we can do is we can add up our bills or get a summary of our bills from our utilities providers and we can record those here so for example if i had paid 60 dollars in heat a month i would have 720 of heat expenses let's say i paid 100 electricity a month i'd have 1200 in electricity my home insurance was 450 maintenance i didn't really have any my condos pretty new so i didn't have much in terms of maintenance costs mortgage this would be if you own your home you can include your mortgage interest and that's not your total mortgage payments that's just the interest portion of your mortgage so you'll usually get an annual statement that shows you exactly how much you paid in interest for the year and that's a great place to look for this amount so let's say i pay eight thousand dollars in interest my mortgage this year i also paid property taxes so i paid 2 600 in property taxes and in this case i didn't rent a house i owned it so i have nothing in the red row there strata fees yes i did have strata it was let's say it was 350 a month and then if there was anything else that you had this could be internet any other kinds of expenses that directly relate to your home office you could record those here so let's say i had interest expenses of 80 a month i can record that and then i'll just add a quick internet cost description in that blue cell there that'll just help you remember what this is if you go ahead and use this sheet next year you can pretty much reuse it just copy it and then edit the numbers and it makes recording your bookkeeping really quick so you can see 18 000 and total home expenses for the year multiplied by 18.75 is our percentage of home use as an office equals about thirty four hundred dollars in home office expenses eligible to be deducted so come back over to our income statement all those numbers are still the same but now we have a home office expense number and a net income number something else to note on this income statement is really something that shouldn't be on an income statement it's your capital assets that you purchased so when we came back over to this bookkeeping tab we said this was a monitor and it was a capital asset purchase check out the description if you're not sure the difference between capital assets and expenses but we've recorded it and it doesn't show up in our income statement as an expense it shows up in this list so that we can record it on our tax return as a capital asset purchase that's going to be actually deducted as a thing called capital cost allowance on your tax return and if you're using a tax program like wealth simple tax or turbotax they'll have a wizard that'll take you through the process of recording your asset purchases so at least you'll have them summarized nicely on this sheet to enter into your tax software so we're pretty much done for the bookkeeping for the year we have our transactions recorded in the bookkeeping sheet we have our home office expenses recorded in the office sheet and we have our nice income statement showing up here and the last thing that you might have to do is file a gst hst return and you can see there's a tab for that as well this is populated from that bookkeeping sheet so we can see we had eighty thousand dollars in sales so because it was categorized as sales that's going to pop into that cell there we had four thousand dollars of gst collected that came from this and it knows the sheet knows because you've recorded it as sales and you've recorded it as gst hst so it shows up in the gst collected row and we also had some gst paid so there was gst paid on all these expenses that we would have gotten from our receipts and entered into that column and it totals up all the ones that are expenses or asset purchases and records those as gst paid and those are also called itc's or input tax credits and this is pretty much everything that you'll need to file your gst hst return for the year it's going to ask you for sales gst collected gst paid and then you're going to have a net amount to either pay or if you paid this isn't what you should do but just to demonstrate if you paid more gst than you collected you're going to have a refund as this negative number there one thing that you might have to do before filing your gst hst return is make an adjustment so that you're only claiming 50 of gst hst that was paid on those meals and entertainment expenses specifically so we've got one we've got two and we've got three and we could just add up these total numbers and we can multiply it by fifty percent we're gonna get an adjustment to make sure that we only claim half of gst hsc paid on meals entertainment there's a link in the description as well that talks about how to file your gst return and it explains how that adjustment works for meals and entertainment so really all you'd have to do is something like this copy this here and then subtract 50 of gst paid on meals and entertainment from this number let's say we paid five dollars in total it would be equals five over two two dollars and fifty cents so we could subtract two dollars and fifty cents from 73.65 and we're going to get an updated amount of gst to claim as an info tax credit or gst aid so this is net of that 50 adjustment and then we have a new gst amount owing that's slightly more than it was before so that's one way to do it there's lots of info of their own gst returns and lots on avalon's blog as well so have a peek there if you're not sure where to go on gst filing so that is about it for how to use this bookkeeping template the next thing i'm going to dive into is a little bit of customization so you can make some changes in how these transactions look it can be a little bit tricky so we're going to do this as a demonstration just to show how to change some names of transaction types and what can happen if the formulas break so i don't recommend this but if you're really set on changing some names this is how you do it if you weren't happy with some of the transaction type names we can make some changes on there there's just a few important things to keep in mind so if we look at our income statement we have a list of the expense categories and they have to be identical to the list that's over here the list in the transaction types is what populates the drop-down list and then it flows into the income statement using a sum if function so this is the key is that if we make any changes to the transaction types for example if we change telephone and utilities to just utilities we would have telephone utilities still sitting here but the utilities account now flows through here so we've got utilities and let's say we paid bc hydro for some utilities for our office for 2022 as let's say it was thirteen hundred dollars we've thought we've recorded utilities but because our income statement says telephoned and utilities nothing is pulling through this sum if is using this heading to pull amounts from here and not the utilities heading it's really easy to fix all we then need to do is go to the income statement and rename this to be exactly what it says on the bookkeeping sheet so you can see already that 1300 was pulled through so step one change the transaction type name here step two change the transaction type name on the income statement step three do your bookkeeping using the new name from the drop down menu i hope you found that helpful and now you can use our bookkeeping template and add a google sheets or excel if you get stuck or if you need some guidance on things please do reach out in the comments below we're always happy to jump in and help if you have questions and if you found this useful and if you like our content please do subscribe and like the videos it gives us motivation to keep doing these things and we really want to help business owners so thank you very much and we'll see in the next video