Collaborate on Invoice Tracker Template for Small Businesses with Ease Using airSlate SignNow

Invoice tracker template for small businesses

hi and welcome to this dcp web tutorial in today's tutorial we're going to focus on a system for small businesses which are vag registered so we're going to make a bookkeeping tutorial which is for small businesses which are vat registered so if you've been following my tutorials on my youtube page you'll see that i created previously a small business bookkeeping tutorial and these were for businesses which are not vh registered so if you're not a vat registered business then you can go ahead and follow these tutorials people seem to find them quite useful and it's a good system especially for a small startup business it's a pretty much a free way to keep track of all your bookkeeping there's no expensive software involved everything's free tools to use so you can go ahead and follow these free tutorials here but we're going to focus today on small businesses which are vat registered so i'm going to go to google and we need to download openoffice just type in openoffice and go to openoffice.org click on the link and then say i want to download apache openoffice and then choose the relevant operating system the right one that's for you i'm using windows and i'll click this download and it will tick down to five seconds and then you see this pop-up up here save the file as you save any other um application file it's 134 makes it take a bit of time to download but once you've downloaded it you should be good to go so just it as you any other software so i'm going to close that and if you are a small business or not obviously registered you can just go to youtube.com uh you should be able to just type in small business just type in small business bookkeeping you'll see my tutorial right here as well that's an easy way to find my small business bookkeeping tutorial if you're not fat registered so let's focus on the vhc registration uh bookkeeping tutorial so i'm going to close this and we've downloaded we'll assume that you've downloaded and installed openoffice so here you can see all my software and i'm going to click on open office here we're going to load up a blank spreadsheet and we're going to go to file save as and i've already created a folder on my computer called small business vit bookkeeping i've created this file called invoice tracker and i've given it today's date just type in invoice tracker and make sure you give it today's date the date there's a there's a purpose for this date because every so often we're going to make an archive of this fold of this particular file so we'll keep a backup of that file as we go along i'm just going to save this overwrite the file so we've got a blank spreadsheet here and the objective of this first spreadsheet let's just try and understand what this spreadsheet is supposed to do so in your business every day or every few days you're going to be sending out invoices to customers for work that you've done for them and you want to be able to create a spreadsheet where you can keep track of who's paid you when they've paid you how to pay you what the work was for various information you want to keep track of in this particular spreadsheet and it's important to have that information because you want to be able to chase customers that haven't paid you you want to be able to see who's paid you how much you've generated in a month how much you've generated in six months the end of the year all this information is quite important to your business and you know this is a nice system that i've created in this open office spreadsheet which will help you to do this so in this first column in this letter here this is cell a1 you can see a1 written here this will be cell a1 this will be b1 c1 this will be c5 cell c5 so we're going to click on cell a1 and in this cell we're going to type in invoice date and then if we press the tab key that will move us to the next cell let's just save this so now we're in b1 and we're going to type in invoice number and then we want if you notice like this invoice number is over overlapping this this letter c here this column c so just move in between cell b and c and double click here you can double click and it will set the size of the cell for you automatically so you can see what's in there and then in this cell we want payment type we just double click here set the width we want reference i'm going to explain all these columns what they're for and then we want description and we also want company name we want the price excluding that we want the price we want the v80 on its own and then we want the including that price and in the last column we want paid so we've missed one thing here let's just save this so we've got invoice date invoice number payment type so we're going to click on this c cell payment type and we're going to right click here and we're going to insert a column it's going to insert a blank column here because we want the date paid here which is very important okay so we're going to click on this letter a that's going to highlight this whole column and we're going to hold down the control key on our keyboard and also click on date page these two so we'll highlight these two because they're both dates and we're going to right click on them and format cell and we're going to say we want it to be a date format and i'm using uk date format but you may want to select a different date format from here in theory openoffice should be able to see what what your language settings are and your location and set the format for you automatically but i'm just going to use this default date format here uk day format pick a different date format if you're in american format or european using the date format days months and years you can see the days the months and the years here i'm going to click ok nothing's really happening but it all makes sense in a moment and then in here the exfat v80 and including that we need to highlight these three columns so left click on the g hold the mouse down and drag across to highlight these three we're going to right click format cell i'm going to set this to currency currency format again select the relevant settings for your currency but i'm using uk british pounds and click and you must make sure it's a to the decimal place so you've got the pennies in there as well that's important click ok let's just save this so we need to start filling in some data so let's just assume today is the 26th of the 5th 2015 the same date as what this is written up here in the invoice tracker and this is our let's just say this is our very first day of business and we've done some work for our customer we need to put some information in here so we need an invoice date we need to say uh when did we send this invoice to the customer what you have to understand is um this spreadsheet is not the invoice itself we're gonna in our second tutorial we're gonna make a separate document which will actually be the invoice that you send to the customer this is just a tracker this is to track all the invoices that go out this is not the invoice itself that will be a separate tutorial i'll do afterwards so really this is tracking all the money coming into your business the second tutorial will be an actual invoice that you'll send to the customer as a pdf file or you'll print it out and send it in the post the third tutorial will create is all of your expenditure all the money that you're spending in your business so you need to keep track of that as well so in here we need to invoice date so we're going to put today's date 26th of the 5th 2015 and we're going to click out and one thing we want to do here is click on this little box in the left hand corner this little box and we're going to left align everything for now everything left aligned and we need an invoice number so this invoice number is always a unique number but if you're running you know at some point in your in your business you may be running more than one company so i normally use an acronym so what i do is i put in dcp that's that's an abbreviation of my company name and then i'll put in the year 2015 so i know what year this invoice was sent out and then i put in the number zero zero one so this will be dcp company my company the year 2015 today this year and this is the very first invoice and it will go up to 999 and then you loop back to zero zero one again then we need to put in a date here so this is the date it's paid we're gonna assume it hasn't been paid yet the payment type hasn't been sorted out yet so these things here these three we're to leave blank for the moment i'll explain them as those in a moment but a description what job did we do for this customer so we did a logo design just a simple logo design that's what we did one thing one one other thing we can do is we can click on this number one right here and it will highlight this whole row and we're going to make it bold and that will just make all of these headings bold here just so that we can see them a bit clearer i'm just going to reduce the size of these for a moment okay i've zoomed in here a bit so normally the spreadsheet will look more like this i'm just zooming in so it's a bit easier for you to see you can zoom in and out by holding down the control key and using your mouse wheel so moving up and down on your mouse while we're zooming it out it's a company so i'm just going to make this up i'm going to call it xyz company this is the name of the company that we did this work for and now we need to do our calculations here so whenever i quote a customer for a job i always quote them excluding that so i'll say the job cost 100 pound excluding v80 and we're going to calculate the v80 here in a moment and we need to calculate the including v80 price here so what we do is um we're gonna say let's just make sure we do this right so be h1 times 1.2 so we do h it'll be h2 this cell equals h2 times 1.2 and then this i cell we do equals j2 minus h2 enter so whatever figure we put in here is going to calculate the v80 and excluding that price so if i put in 25 pounds here 25 plus 5 pound is the v80 equals 30 pound total including that so this formula will calculate the v80 so whenever i put a customer price i always say it's 25 pound plus v80 i put the 25 in here it works like the v80 and including that in the uk we're charging 20 facts this is why it's times 1.2 so if you're charging 40 v80 you'll be 1.4 and hit enter and then you'll get 40 v80 in there but we're going to make it 1.2 because we're in the uk so you can modify the formula depending on what v80 you want to charge we're charging 20 percent here so let's look at that one more time the excluding v80 you just put in the price here the including vat will be the h2 so this is cell h325 this 25 pound is cell h2 you can see that here h2 so h2 times 1.2 will give us the v80 so let's just try one more figure in here let's try something a bit more random like 145 145 plus that is 174 pounds so let's go and double check this formula we'll go to a browser and we'll do v80 spot that wrong with the ac calculator so some online tools that you can use to check your v80 so we did 145 145 the v80 is 29 the gross is 174 145 29 174 so let's just start again and we'll do something a bit more one thousand one hundred and one thousand two hundred and thirty four add the v80 at 20 and we'll just check these figures just make sure our formula is working quickly so put 1234. you can see this hashing is happening here you need to make the the column wider you need to make it wider when the column is too narrow it puts these hashes in here if you see the hashes you need to make the column wider so you can see the values inside so 246 80 1480 spot eight zero there you can see so the formula is working correctly i'm just checking on this website to make sure the formula is working quickly but it's fine okay so let's just make this a sensible figure let's say it was 125. okay let's just save this document so obviously this is only one invoice that we've sent out we're going to assume we're going to move into the future and we're going to say we sent out let's say let's do 20 invoices for now so you know realistically you're going to be sending your invoices on different dates you may send one today you may not send one for another three or four days even another week until you finish that next job but to make my life a little bit easier i'm just going to move my mouse cursor over this bottom right hand corner i'm going to drag down and that's going to generate the dates for me so you can see the 26 27 28 29 so i'm using one day and normally it wouldn't be like this normally in your spreadsheet you would have different dates in here you may have the same date you may do three jobs on the same day and you may have to 26 26 and 26 for three different companies here the same day and you send out three different invoices to three different companies but in this case i'm just showing the dates here quickly because i want to type them all in now the invoice number you can do the same thing here so it the software recognizes that it ends with a zero one so in theory if we drag all the way down you're gonna see zero two zero three zero four zero five six seven eight nine ten all the way up to twenty zero 20 here that's a quick way of generating your invoice numbers i didn't have to type it in i typed in the first one i made sure it ended with a zero zero one and i could generate all my invoice numbers very very quickly you can see this becomes very useful when we create our actual invoice in the next tutorial because we can just cut and paste this information in very quickly so the date these three i'm going to leave them blank we're going to come back to those in a moment and we need to generate some more information in here in in these four columns so i'm going to type some stuff in i'm going to say website design and this is for abc company a different company and we charge 1000 and you'll see that these values haven't been generated here the quick way to do this is just click on this cell above and then just drag it down this is a little black box in the corners maybe a little bit hard to see i'm going to try and zoom in as much as possible this little black box here in the corner you click on it and you drag down one and you see it's showing some error here but you need to click and drag down on this one as well and it's going to generate the values for you let's just zoom back out a bit so in theory you can drag these all the way down but it's going to show all zeros and it's going to show all zero values in here because there's no value in the here so normally i only drag down once i've generated that particular line of information so once i've done a row of data then i just drag these two down so i don't see all zeros in here it just looks a bit messy let's just save this um business card design and one two three so i'm just making up some company names here and we charged 95 pounds to do this work and we just click here and drag down and drag down and we'll get our values in here you can see that we can't really see all the information in this column we don't really want this column to be too wide because we're going to see a lot of information missing off the screen so normally i'll keep this quite narrow about to about here if i can't see all the information in the cell i click on the cell click on this cell and it'll tell me right here what is in there it's a business card design website design i can read it up here so don't worry if you can't see all the information here you can click on the cells to see exactly what's written in there it's more important that you can see all the rows of data without having to scroll left and right that's more important let's just save this um so we said we did some work it was search engine optimization work and it's month one for this particular company and we call it red that's the name of the red carpets limited that's what the company's called and we're charging them i don't know let's just say 295 you can also move up to this cell press ctrl c to copy and press control v to paste and you can move to this cell control c and control v to paste so you can copy and paste these down as well and it will change the value can you see in here it says h4 and if i move up one it will say h3 and h2 you can use the arrow keys to move around the spreadsheet so that's another way to generate these quite quickly is just to copy and paste them down i'll show you some more quicker ways in a moment let's just save this so we've got four invoices in here at the moment and i'm going to do a few more i'm just going to replicate them so let's just say um we did some banner designs i'm animated banners for a customer it really should be a bit more descriptive in here because banner design's a bit generic so we could say banner design gif animation times 12 so we did a banner design job it was gift animation banners and we did 12 in total so that's a bit more descriptive and it was for the one two three company so it's the same company here and let's say we charge them 10 a banner there's 12 in total so it's 120 and this time i'm going to select these two cells with my mouse copy them both so i'm going to select them both press ctrl c to copy edit copy ctrl c it's very easy ctrl c and then i'm going to press ctrl v to paste so i'm going to move down to here and press ctrl v it's going to generate the values for me as well so that's another way to to um get those formulas pasted you can just select the ones above copy them and paste them below or you can drag down you can click here and drag down if you want that's another way to do it there's a few different ways i'm going to save this so to save me a bit of time because i want to keep typing them in here we're going to be here for a very long time i'm going to copy these ones and paste them below so it's for the same similar companies we can see and the values i'm going to make a bit more random so i'm going to type in 145. these are just random figures i'm going to put in here i'm just making them up i'll just put some random numbers in i wanted to make sure they're not all exactly the same but these descriptions and these company names would have probably been different you wouldn't invoice your customers in this particular sequence these would be a bit more random and the jobs wouldn't be exactly the same but i've done that for a reason just to make this process a bit quicker but the values in here i'll put them a bit random i want to make them different there's a reason for that so um what we can do now is select both these cells with our mouse so we'll highlight both of them we're going to press ctrl z ctrl c to copy and we're going to highlight all of these here we're going to press ctrl v to paste and it's going to generate all the values for us the the price excluding that we typed in the including vat and the sorry the vat amount and including that is done for us so that's that's how this particular formula is going to work and you can see we've got some missing information in here so let's fill out some more of this information so let's let's just make an assumption today is today's date and we're looking into the future really here and um we sent this invoice today and let's just assume 14 days later the customer pays us so this particular customer has sent us we looked we logged on onto our internet banking and we can see that the customers paid us and when we look at our internet banking or a bank statement the customer always puts a payment reference a payment reference here in your bank statement and that's what the customer sends when they send a payment they're going to make a reference some sort of reference and they're going to say something like it could be anything they'll probably put something like logo design and they may put your invoice number like vcp 201501 just so that you can recognize it so you know it's come from that company because it'll be marked on your bank statement from xyz company they may even put that in the reference as well but normally references are um you know it's a short short amount of space that what you can type in there so when i looked at my bank statement in this example i went online and logged in i saw logo design i told his one payment logo design dcp 201501 so that's the invoice number there and also on that bank statement it would have said xyz company so easily i can tally this up now i know that this payment of 150 pounds that i can see in my account belongs to this particular invoice that i sent out to the customer so in here let's work out 14 days so uh one two three four five six seven eight nine so around the ninth for this six that would have paid me 14 days later so in here on my bank statement it says on the 9th 46th 2015. that's the date that the customer paid me this is when i sent them the invoice and that's the date that they paid me and how did they pay me they paid me by a bank transfer i'm just going to zoom out a little bit so this one this one row you can say has now been completed kind of complete kind of complete there's still a few bits of missing information so in this column we've got paid and i'm going to type in yes here but we're going to assume all these other ones haven't been paid so when i generated these uh these rows of data really i want to put no in all of these columns none of these have been paid only this first one right now and what i do is i highlight row number two so i'm going to highlight row two click on number two here and then from these colors i'm going to choose green and green tells me it's been paid so really all these other ones would have been a different color i'm going to make them all blue so when i look at this spreadsheet let's just say that we're closed for business today now tomorrow i open up this spreadsheet this spreadsheet will tell me this first one has been paid because i can see the data in here but it's also green so i can almost ignore this one i know it's been paid i can forget about it for now but all these other ones haven't been paid so we're going to look at these other ones in a moment so let's assume we log in tomorrow and we look at our bank statement or we log into our business account and we can see that four of these other invoices have been paid one two three let's say these next four have been paid and all have different reference types so let's just make them a bit different so this will be abc dcp 2015 so they in this case they've put their company name abc and they'll put their invoice number and the amount was 12 at 1 200. so what i will do here is i'll mark this as yes it's been paid so i'm gonna mark it as yes and it was paid via bank transfer as well and if you notice as i'm typing because the seller above already has bank transfer written in there it's gonna recognize that below and i can just hit the enter key and this got paid on the 10th got paid on the next day so now we've got two invoices shown as paid and let's mark this one as green that's two invoices paid on this next one this invoice got paid but in this case the customer sent us a check so i'm gonna write in here check payment and normally when someone sends you a check you have to put that into your bank account it takes a bit of time for that check to clear it doesn't clear straight away it takes a few days maybe seven days to clear the check payment but the reference won't be from the customer the reference will be from your check pane in slip so when you when you uh you have normally have you have two types of books you have your checkbook and your check pane in slip book so in that pane in slip you'll have a reference number and it will be something like zero zero zero dash something like this may not necessarily be that it will be different in every different type of banking system but each one will be a unique number so you'll have zero one and you have zero two zero three each check slip will have a unique number so we put that reference number in here this is your check reference pain and slip number and we put that into our bank account and then let's just say seven days from this date 28th one two three four five six seven on the fourth of the six you logged into your bank account and you looked at it and you you'll see that the payment has cleared so on the fourth of the six you logged in and you can see that this check payment has now hit your account and you can see the money in your account so we're going to mark this as yes as well and we're going to make it green so now we can see free payments have gone through and let's just assume that you logged in a few days later and all these payments have started to come through so we start to mark some of these down we're going to leave a few of them unpaid at the bottom but let's get most of them marked in so this one here on the 29th got paid on the fifth the fifth of the sixth it was a back transfer and it was from red carpet so let's call it the reference was red carpets limited and it had your zero zero four that's what they put in as the reference they put the end of the invoice number not the full invoice number remember these can be quite random what they type in here but they're just trying to identify what the payment was for they might even put invoice zero zero four so it's a bit clearer for you this mark this is yes and this was sent on the 30th so let's just say they paid it back transfer and they paid it this was one two three company it's a one two three company and it was 2015.005 that's what they put in for the reference this end part is identifying your invoice number this is really relating to this part here that's what they put as a reference let's mark this one as green and normally i try and choose colors down here at the bottom so they're easy to find so this green and this blue these two i use i don't try and pick colors from here because they're quick quite hard to find just these two at the bottom are very easy to find these two here so this one here on the 31st to the 5th 2015 this customer came and paid you and they paid you on the following day they paid you on the 1st of the 6th 2015. and they happened to pay you using cash they came and gave you cash in your hand so here you can just put in cash payment and you took that money on the same day and you paid it into your bank account so in your bank statement you'll see that on the first of the 6 2015 they paid you cash and it was in your bank account straight into your account i put cash payment here but you may put a different reference you might put something like xyz cash payment so you know that it came from there so i'm going to speed this up a little bit and we're going to say that all of these were done by bank transfer and we're going to set the date here to the 5th of the 6th 2015. this is the first of the six so this invoice was sent on the first of the six so it can never be a date before this the date paid always has to be a date in the future we're going to drag down and generate the dates that have been paid and then the reference numbers um all i'm going to do is copy these and paste them here these are what the companies that send the payment these are the references that they used just to speed things up a little bit here hopefully this all makes sense and we can mark all of these as yes so as your business goes along you're going to keep adding all of your dates in here you're going to generate all your invoice numbers and you're going to end up with a whole year's worth of data we happen to have pretty much 20 days worth of data in here and we're going to use this tool to do some some other things now so most important you know when you sent the invoice what the invoice number was the unit your unique invoice number the date the customer paid you how they paid you what sort of work you did well the reference their company reference so later when you look at your bank statement if you had many payments from xyz company each one of these references will normally be unique not always but sometimes they're unique so you can look back at your back state bank statement and quickly find that bank statement and see when they've paid you if you need to reference it for any reason what the job was done the company that you sent the invoice to the price excluding that how much vat and including that and this is going to be important information when we create the actual invoice template in our second tutorial in the next tutorial we'll make a real invoice that you can send to the customer so we can do some we've got some tools in open office that will help us do a few things here so we can click on this number one here we can go to data filter or type filter and we can say we can go to this page column and we can click yes here click on this yes and then it will only show us invoices that have been paid and we can say well we can click on this bottom cell here and we can use the sum tool here we can click on that and we can click the green arrow and we can do the same with the v80 here the sun tool and click the green arrow and this one here sun tool and then click the green arrow and it will give us the grand totals so we'll say during this period you have been paid exactly this amount in your bank account this is how much vat was and this is how much was it was excluding v80 so we can delete these three i'm just showing you the sum tour here and we can go to no and this is a quick way for you to reference who hasn't paid you so in this paid column this is why we've got a yes or no in here the null will tell us how many people haven't paid us and we can see when we sent out the invoices and if it's been more than 14 days or 30 days depending on your payment terms you can quickly check to see who hasn't paid you and you can chase them up on these payments quite quickly this is a good way to see who hasn't paid you and who has paid you we can click on all and when we click on all or just before i do that you'll notice these these drop down menus are black you'll see they're black but the ones that we're using are in blue you can see right here they're blue so blue tells us that it's currently active so when we click on here and click all you see it's gone back to black because it's not being used at the moment and we may want to see a grand total so we click on this cell here use the sum tool click the green arrow here and we can see how much we generated during that period as a grand total you can do the same with these columns as well here you can click on here some tool you can hit the enter key as well it's a bit quicker click here click on the sum tool and hit enter this is the sum tool you can see it's hashed here because we need to make the column a bit wider to see the value there's a quick way to to get all the information you know about your grand totals so you can quite easily see how much you've generated in total how much is outstanding and how much you actually have been paid in your account those three figures are quite important we can also go to the name column here and we can select one two three company and we can see how much we build to one two three company so we click here and do a sum tour we can say one two three company we're building 2532 pounds and from that amount we can click yes and do a sum tour and we can say 1806 has been paid from that particular company and we click no and we can do another sum tour and we can say 726 is currently outstanding i'm always deleting this formula afterwards once i've used it this sum tool click all and we can go back here we can choose a different company abc we can do the same and xyz we can see the same we'll click all here we can see our grand totals again we can also see how many bank transfers have been cut i've come through who's paid by bank transfer and what's the grand total that's not really useful information really but you can do that if you want how many cash payments how many check payments and everything together so these filters become very very useful especially when you want to find out who owes you money because one of these ones in the middle for example let's change it back to blue and we'll set this to no and we'll delete this data here as we're looking down this spreadsheet we can see all of these green ones have been paid but this one in the middle here hasn't been paid so we can go and chase this company normally you won't you'll chase for payment after 14 days or 30 days it depends on what your payment terms are most companies will give you 14 or 30 days you know one working month to pay an invoice so you send it on the 26th but you wouldn't expect this payment for 14 days later or 30 days later but this one happens to not been paid yet and they may have gone on holiday and you can chase them up for payment when they get back and when they do pay you can simply market as paid and you can mark it as green and you can mark it as yes paid here i hope that all makes sense this is quite a simple spreadsheet but it's got some really important information in here and um this should really help you to generate your v18 values very quickly so you don't really have to use a calculator anymore or an online tool you can just generate this directly in the spreadsheet and this will become a very very useful spreadsheet for you as your business grows when you've got hundreds of invoices in here for the whole year and you want to see who hasn't paid and who has paid and your grand totals for the end of the year this spreadsheet will help you to do that so this spreadsheet i normally keep up to date every day i go online i check my bank account and i see who's paid me i send out quite a lot of invoices i need to see who's paid me and when they've paid and who hasn't paid and this spreadsheet will help you to keep track of all that information that's the end of this tutorial in the second tutorial we're going to create an invoice an actual invoice and we're going to use this first example here and we'll create a real invoice that you can send to your customer as a pdf file but we're going to use openoffice to create that actual spreadsheet so i look forward to seeing you on the next tutorial i hope you found this one useful and if you have any questions i'm going to upload this to youtube and uh feel free to ask me any questions in youtube commenting and i'll try and help you if you have any questions okay i look forward to seeing you on the next tutorial you