Sales Tax Invoice Format in Excel for Financial Services Made Easy

Sales tax invoice format in excel for Financial Services

hey guys a few years ago I started creating a course to help people kick-start their business but before we could finalize it life just got in the way so instead I've decided to just release this free for all of you to watch and use this video is an introduction to using Excel for accounting now on to the video now it's essential to find a suitable accounting system to track your income track your expenses customers tax and Reporting starting your business without an accounting system is like jumping on a plane without a pilot your public crash and burn the accounting system can be something simple like using spreadsheets to track income and expenses however we strongly recommend investing in accounting software early on even if you're a sole Trader if you have a sound accounting system it will save you time help you make better decisions and assist you in running your business so let's get into it so if you don't want to use accounting software and you just want to use the spreadsheet or just record your data manually you can also do that as well the main thing is that you need a business bank account first so once you've got your business bank account um and you've recorded your invoices and you send your invoices out to your clients then the next step is that you need to record your expenses and your income on a monthly basis at least I would not recommend you doing it every six months or three miles I actually think it's really important for you to you know do your bookkeeping and record your monthly expenses and income so you can track how much money you're actually physically making so how you do that is it's quite easy let me just go and create the spreadsheet that you would use so I'll open up Excel open up a new book um and this is what you need to do you need to create two spreadsheets um two sheets one that's going to be income period and always separate your periods from which financial year so you're gonna go July 2020 to 30 June 2021 okay so that's easy and then what you need to do is because we're doing invoicing uh with the income we need to record the income data and what's the most important for the income data the income data you need to put is the date that you that you receive the income or you influenced um the amount that with the description actually understand start with the client first then the description there the net amount it's a GST amount and the question amount and then hide and Method that's the most important things that you would need in your situation when you do your bookkeeping so this this is just if you guys don't want to use a canning software you can actually create your own that's a good thing about this and what you need to do now is create a table so you go press Ctrl t and it creates this my table has headers already and then this is what it creates cool let's open that out okay so now this is where you go and put in the information of what money you've received and then we do the exact same thing for expenses so let's just create this one and call it income for now and we do the exact same thing for expenses why I separated into two spreadsheets it's just easier to track how much expenses you receive for a particular period and how much expenses you paid for a particular period and how much income you've received for a particular period so all right so all I need to do is just go to this expenses and then that's for that period that amount um if you want to have client you'll have uh and you would have um Sapphire or you could have Biller cool cool and then you can put in paid and payment method so this is Method means like you know how it was received or how it was paid so did we pay for our credit card did we create it through our personal bank account we would pay through the business bank account same thing with income to receive it personally or to receive in cash so we paid it did we receive it in our business bank account that's basically what this column is for all right so the next step is that how do I get this data where do I get it from same thing all you need to do is get that data from the bank account um so what you do is log on to your internet banking I've already done that and what I've done is I've exported the CSV file they call it a comma separated values file and that's just basically your bank statements in a spreadsheet format and it looks like this this is what it looks like so uni every bank has something like this you can do this for every single Bank um so what I would recommend is you go and log on to the internet banking um and then export your CSV file if you're not sure on how to do that Google it so CBA shows you how to do that as well so you can easily Google CBA CSV export and then you choose the dates that you want and you can export basically order transactions into a spreadsheet format and why I want you to do that is because this here will tell you what your income expenses will be so what your income and expenses will be is basically all the transactions you have in your bank account so the next step after that is now you go control C and you change that then I've reordered this into deposits and income and highest to smallest so now therefore it's just recording all the income that I have received in this back business banknote so now you copy all these expenses hey this this income into that spreadsheet that we're talking about so anyway I've also got the dates so just go and copy here this is for income and this is the gross amount and then here is all the invoices and that's the description and then here this is all the view yeah cool all right so that's in there and these are all the expenses so and then you go and put in here the description and that's a gross amount so now what you need to do is put in the dates as well put that in right now and then do the same okay this is not it you're not done yet what your next thing you need to do now is actually update this spreadsheet and reorder it as well because the order is incorrect though orders is highest to lowest so what you need to do now is you need to put in who this person is so who this company is that you're working for obviously this was X Y and Z so easy and this is cancer cats Council and then ma Sarah whoever that person is and then this is Brent and this is for ABC PTY limited for the whoever my landlord is and then go all the way down he insurance premiums so basically what I'm trying to do is you record all the data and insurance payments let's just say you know NRMA or something like that okay the next step after that is um you need to create one more column for your for your expenses which is type so this is important why are we this is basically like rounded where we classify what type of expense it is so this is software obviously and we can bring this down all the way down to here and this is cancer Council um so this is donations or charity and this is marketing and it's rent then it's important that when you do your bank transfers you make sure that you put into the description properly in your bank transfer so if you don't do that this is going to be hell when you do your bookkeeping so it's really important that you do that so just make sure that you have a good habit okay so now that um I've copied all the time transactions into this spreadsheet I've realized that some of these transactions are actually personal and it shouldn't be in here so we should delete it that's what we just need to do so I'm going to get rid of this spreadsheet it's not important anymore because I've copied all the data into our bookkeeping spreadsheet okay the next step now is to delete this delete all your personal transactions in there you don't need to have this got the gross them out the next step is to take out the GST so work out which of these expenses have GST on it luckily for us I might cancel and I can easily work out which ones have GSC on this one here does not have GST on it so just remember it's divided by 11 on the gross amount so if whatever money that you spent and that I'm a map that you spent on is the total amount including GC you have to divide it by 11 to work out what the GST is so it's not divided by 10 just know that so all right cool I know that cancer Council doesn't have GSD and we know that Sarah does not have GST as well because we've checked the invoices so therefore these ones are the ones that have GC on it and we just got done and the net amount will obviously be then this minus this and that is it guys so that's how you work out your how you do your bookkeeping for your expenses and you do the exact same thing for your uh income so obviously there might be some deposits that are not related to um income so I had to delete this for example this is payback this is a transfer contributions are also not invoices so we know that these are all invoices you've got to put in the client startup name um and then you also divide this by 11 and then you got the same thing and then you got the net amount is this minus this cool all right so that's how you do your bookkeeping if you don't use a Canon software and this is how you get all your data in and when it comes to doing your basses now you have to reorder this oldest in yours and then reorder this all this in yours okay so let's try to do our business activity statement and work out you know what is our fast gonna be for April to June 2020 all right so we know that our expenses for April to June is going to be this one here this one here this one here because we've already worked out what the totals are so we can do a something we want to but we can just work it out like it so that's our GST so this is what we call 1B which is also GST Onyx purchases this is what we call G11 but it doesn't show up anymore so you don't actually need to put this on there but I just this is your total purchases I just record that anyway for the quarter okay and then we've got to do the same thing for our income so unfortunately we only actually invoiced once so that's easy so your your total sales is basically this and then your GST is this amount here because that's the only payment that was invoice that you've received for main don't forget you have to fill this party out I'm not going to fill that out because that's that's obviously like we know that it's all paid because that came from the bank account yep so that's your total sales and that's what your we call it one uh J1 and then you won eight and this is how you fill out the business business I give you a statement now so going to business like this statement click on the period so this was April to June and because this has been knowledge I'm just going to show you how it how we fill that out if you always revised so that we'll put in one word and share it and then a GRC is one thousand dollars that's it and then disabled and that's how you do your business security statement and you got this amount only so that's how you do it manually if you don't want to use the accounting software you don't need to you can just use a spreadsheet to record your income expenses and do your best now you just need to save this save this as a file and save it all and then you do the exact same thing for either the next quarter or you do it for the full you add on to this um this spreadsheet so you can record all your incoming expenses for the whole year cool all right guys that's how you do it [Music]