Streamline Billing in Excel for Accounting with airSlate SignNow

Billing in excel for Accounting

[Music] hi and welcome to the ccp web excel bookkeeping for small business a tutorial so in the first part of this tutorial we're going to focus on invoice tracking so I've been running my business for quite a few years and I used three simple spreadsheets to keep track of my books in terms of invoice payments all my expenditures and even creating invoices right so have these three simple spreadsheets that I use so in the first part of this tutorial we're going to focus on invoice tracking so what does that actually mean it means that we're going to create a spreadsheet and that spreadsheet will allow us to document all the invoices that we sent to all of our customers for a whole year and in that spreadsheet as well we'll be able to work out which clients have paid us and which ones haven't the method that they paid and also the date that they've paid and a few other bits of information so let's go ahead and open up Microsoft Excel and we'll start the first part of this tutorial so quite XO opening I'm using the latest version I'm going to go to file save as and we'll go to my let's go to my desktop let's fly my desktop here and we'll find this tutorial here so inside this folder on my desktop you see that in the top right hand corner in a moment I'm just going to call this spreadsheet let's just call it in fact let's just call it invoice tracker in fact what we call it is 0 0 - to put the 0 0 space - space invoice track up and then put today's date so today is the 26th of the 7th 2019 and we'll click Save we've got a blank spreadsheet here I'm going to use the ctrl key and the mouse wheel to zoom in a little bit and normally I will name the sheet down here let's just call that invoice tracker for 2019 and then you can label this 2020 2021 and so forth as you're running your business but this one spreadsheet will keep it and then we'll give that to our accountant and be very settled clean books and also every every week or every couple of days or every month you can go back to the spreadsheet and find out which customers have paid you which ones haven't and so forth right let's click the Save button and in this first column here this first row the row number 1 is going to be our titles so we're gonna put all our header titles in here and the first one we're going to have is invoice date and in column B we're going to have invoice number and we'll have date paid I'm also going to have payments type let's just fix that so you can see as I'm typing this information it's not quite clear so all we do is left click up here in the top left-hand corner that will highlight the whole spreadsheet and then just move the mouse cursor in between columns a and B or a and B and C or any of them it and just double click with your left mouse button that will just resize all of the columns you can see the tile is clearer I will continue writing some more tiles in here the next one will be reference and the one after that will be description and then we'll have pumping them and we'll have company location and then finally we'll have cost and then paid column so this is a small business bookkeeping tutorial I may do another one later which would deal with v80 or maybe I'll make a full tutorial so I'll do the first 3 parts and maybe at the end I'll show you how to transform these spreadsheets into a v80 applicable methodology or documentation let's say yeah so we've got invoice date invoice number the date that was paid for this payment for this particular invoice the payment type the reference the description company name company location we can probably put just location in here we don't really need company just location cost paid so let's click on number one and make them bold we could select a letter B here to make them all bolt and if you're not seeing these quickly just remember left click here and you just double click inside here and we'll sort them all out and the column width and the first thing we want to do is sort out these column formatting so we're gonna click on the letter A and this is invoice date so the software is expecting this to be a date but if you all knows you've got date paid here you've got a day pay column so we're going to hold down the shift key and left-click on that one in fact we will click on letter A and we'll hold down the control key and click on the letter C that will highlight or select only these two columns here and we're going to right click and go to format cells and we'll go to date and you can pick a relevant date depending on your location you can select other locations and you drop down but typically when you Microsoft Windows you're going to select the look your location during the installation and then software will also automatically pick that up and give you the right date format so in the UK we have day month and year I know in other countries you have the month than the day in the year but you pick the relevant format that's applicable to you I'm going to keep it nice and simple and use the top one here and click OK nothing really seems to happen here but until you start doing some data entry you won't really see the formatting being applied so invoice number that can just be a default value so when we go to format cells we can see it's just a label is general we might change that I think that should be fine payment title can be general reference in description and company name and location these can all be left at general and cost we need to make sure we set that to a currency so let's right-click or let's left-click on the letter I right-click on it and go to format cells inside here we're going to select currency and again you should see different currencies based on your particular location if you don't see the right one you can select this drop-down and select the relevant country will about two decimal places select this first value up here and click OK so now that we have a formatted spreadsheet we can think about the type of data that we want to enter so this way we're going to make this some fictitional info Meishan in here and then i'm going to show you how to format this spreadsheet so that you can track the information so let's put in a date for argument's sake I'm going to start this from the the first of the first twenty nineteen right at the beginning of this year so let's just say that we're starting a business and on the first day of the year we want to put in a date here right we'll go first in the first twenty nineteen and you can see Excel will format that out in this particular format style so if I if I left click here then right click and then go to format so I can pick other types of cell formatting in here maybe we'll do this one here like fourteen for the third twenty-seven in fact I like the top one but let's pick another one just so you can see so I'll select this second version and click OK and now it's firstly January 2019 but I don't like to read dates like that I like to select here right click format cells and set them to this top value and then I think that's kind of the best formatting I want to left the line all of my content so let's enter some data then we'll left the line up afterwards invoice number this is this is a number that you can make up yourself right and if you want to stretch out the columns you can just move between a and B and C and just stretch them out a little bit more so you've got more space to see stuff I'll zoom in here a little bit more for you and invoice number this is something that you can make up for yourself so my company is called DCT what's called DCP web designers but DCP can be an acronym right and then we want to give it an invoice number so normally the invoice number I start off with in fact it's all one one set of characters and normally I'll do this lowercase work so it's easier to type in C it would just be DCP the year 2019 and then I follow it by four digits so be zero zero zero one so that will allow us to create up to 9999 invoices for that giving year or you know you it'll be a very very long time before you run out of invoice numbers you're gonna have to do you know close to ten thousand invoices before you loop back around to zero zero zero one again you couldn't add another digit in there and you'll have even more but I think this should be more than sufficient this will probably last you a good ten fifteen years so they paid we're gonna leave this empty for a reason I'll come back to that and same with payment type and reference description so in this description normally when I'm doing this in my own business I'll put a brief description about the job that I've done for this particular client and in this description I'm going to just going to type in logo design times 3 because I did 3 logo designs for this particular client and now I need to put in a company name it's going to make this up so I'm just gonna call it Bob Joe Company Limited right so I just make this up and then the location will be UK so the client is based in the UK sometimes that's worth knowing for your own reference where your customers are based I've got customers all over the planet so I like to know that this particular company is based in the UK or America or Germany or whether there may be then we need to put a cost in here so we just we don't need to put a pound so I'm just going to type the value in so let's just say hypothetically I charge these custom 145 and then on the paid element here I'm gonna type in capitals and all no well at the moment as I'm doing this data entry I'm putting this information in here for tracking purposes so in the second part of this tutorial so not in this particular video the next video I'm going to show you how to create a custom invoice in Excel and this information this line piece of information will take that information and put it into this invoice that we design in Excel and then we'll be able to save that invoice as a PDF file and email it directly to the customer so the purpose of this spreadsheet is only to track what invoices have we sent out and which customers have paid us and which ones haven't so right now you can see a lot of the contour or some of the content right aligned than some of its left aligned so it looks a little bit messy so let's click on number two in fact let's click on the top left here to highlight the whole spreadsheet and click here to left align everything now any data that we enter into this ratio will always be left aligned now let's say that's the only invoice that we sent out on the first of the first but we did another job on the second at the first and we want to create a new line of information so you could type in the second to the first 2019 or you click here and I'm gonna zoom quite close in here hopefully you better see this in the bottom right-hand corner there's a little square dot if you roll your mouse over it the mouse icon will change color if you left click and hold down the mouse button using the left mouse button hold it down and drag down one it's going to generate the next day for you so sometimes I'm invoicing maybe you know three or four clients all on the same day so let's just just let's say let's imagine is the second of the first twenty nineteen and you've got three invoices to send out this is exactly how I would do it first left click on this in this particular cell I then hold down my left mouse button and drag down one so now I've got the third the second of the first 20 19 but I don't drag down two or three well i only drag down one then i'll click on here press control-c to copy and paste it down twice now I've got my date sorted out so I've got three invoices to send out on this particular day so that just saves me doing typing right if I undo that quickly if I do that a little bit quicker normally I just drag down one copy and I'll select both of these cells and then press ctrl V that's even a quicker way to do it right the reason why I say you copy and paste because if you click here and drag down lots of them you're going to generate the second the third the fourth and the fifth and you don't want that well you may want that but for me I'm doing this work on a particular day right so I want to copy this down to represent three different invoices there could be the same customer but three different invoices for three different jobs or three different invoices with three different customers right same with the invoice number we don't want to type this in so we just drag it down like we did with the date this one we can drag all the way down because we want to generate a unique invoice number for each one of these invoices right we send them out all in the same day but each invoice will have a unique number that way when the customer calls you or you call your customer and you say you haven't paid invoice DCP 2019 zero zero three they'll be able to find that invoice and pay you quickly or they may make a query on invoice you just ask them for the invoice number you can check and maybe make some data entry problem and for every reason the invoice is incorrect so it's easy for them to reference them for you in this description we're going to put some more descriptions in here quickly so I'm just going to write a website design and in here I'm going to do business card design and finally in this column here let's do leaflet alright so with this we did three jobs it just so happens that we did this job for gold I'm just making this up great go go limited yeah so we did that job for that company we also did the same trip or a different job but for the same company again I just cut and pasted it down and one other thing as this spreadsheet starts to fill out with lots of lots of information you want to be typing the minimum of that as possible right so if I type in the letter G Excel will look down this column magic if you've got hundreds of rows of data in here EXO is gonna look down the column it's going to find the letter G it's gonna find what did you use last time or what what other letter G's are in this this particular column that start with G these companies and it's gonna give it to you straight away so all I need to do is type in the letter G and hit enter if I'm billing the same company again and I'm gonna build this bozo company again so I'm gonna type in shift and B to make a capital B and then just hit enter so all I have to do is press shift and the capital letter the first letter and just hit Enter key obviously when you've got more companies in here and it's going to give you more selection you have to be a bit careful which one you're selecting this column here I normally just copy it and paste it down but let's say Gold Grove is based in basically Germany right I suppose the different cut was is a company but it's based in Germany and we need to put in our costs here so for website design let's just make a price up let's just say we subtract 1450 and then for business class let's say we get 75 pound and you know for the leaflet let's say we also did 75 pound I'll click here and then drag down so I'll click here press ctrl C to copy I'll lift click here and drag down to highlight these cells and press ctrl beats a paste and then I press ctrl s to save so obviously I'm doing this in my own business I'm doing this a lot lot quicker it's because I'm explaining it to you it's taking a little bit longer for me to do but what I'll do is add up to row ten now I'm not really going to explain too much what I will explain but I'm going to do it is pretty quick way so let's say that we want to send the invoice on the on the on the fourth Y so we're in the second right now and we're gonna send one on the fourth so I drag down one I'll double left-click in here it's a double let's click and change that to a four so we're not sending in the third we're gonna send in the fourth but the invoice number will be the zero five number and for this we did let's say we did the SEO some SEO work might even describe it a bit better like SEO one day work something like this in this column you don't need to write really big descriptions I normally just use them for basic reference and then in the invoice or the email I send to the client I'll have some more detailed information especially for that website design I have a whole specification right maybe a four or five page document in a PDF file but I don't need to put that much information in here I know the company I know I did a website I know the day there's pretty obvious what I did in that particular point in time SEO work let's say that we did this for ABC limited and they're based in the UK and we charge them 195 pounds no you can see I'm typing in the information a little bit quicker now so I'm gonna do a few more let's say that we did three of these on the fall I'll click here and drag down and then we did some more work for this Fojo company and let's call it newsletter design and then normally a lot of times and in how many newsletters I did for the company the business cars will have to do that but sometimes I do many newsletters for the same company and then we have put that times 1 or x 2 and in fact let's do x 2 right so let's do x 2 so in this particular invoice I did two newsletters that was costed out let's say a hundred pounds so this grand total all be 200 so when I look at this spreadsheet I'll see two invoices I see 200 so I just divide that in my head I know that I charge 100 pound for each invoice it might be different in my being 125 for one in what newsletter and 175 for the other one because the one was complex and one was less complex for example so even got 0 0 7 that's putting some more data here and we'll do something like let's do banner ad versus well write banner ad design and normally when I do banner design I'll put something like this size that I've done so 250 by 300 it's worth putting in a little bit of information so you know that you did this particular type of banner and normally I would say it's like a gif animation so when I look at this spreadsheet later I can see this is actually what the job was I might not remember what the details about it but at least this would told me enough to go and reference it later so that company was based in the UK as well and that's that company was Goldberg his Goldwell company so old world in fact let's delete his UK so we type in G for gold world we hit enter and then we type in G for Germany and hit enter and we say we tried 45 pounds and we copy and paste this down that one's done I need to do a few more I have to do these but I just want to show you how this might work let's bounce let's say that we went a little holiday and we came back home to 12 so I'll type this time I could drag down sometimes I just typed the dates into 12 for the first 20 19 19 is pretty easy to type in as well we'll drag this one down and let's say that we did another website it's called lead generation lead gen website project and this is for new company that's called them.we leads the limited make this up and they're based in the UK and we choice them 795 pounds and know that no one's paid at the moment right when you start your business normally you have a payment term so normal you say what we on your actual invoice the invoice that you send to the customer you need to pass within 30 days or 14 days or 60 days depends on your own business preferences right but normally you would explain to the customer if I send you this invoice it needs to be paid in 14 days or whatever your terms are those are your own business terms right so forth on the talk we did a job let's do 13 and we'll actually go down to 13 here on the 13 for the first we'll drag this down one we're gonna do battle sometimes let me just expand this column right make it a bit bigger we'll squeeze this down for a minute not feeling anything in them so sometimes I need to type in a bit of information and it's very similar to a description that would be done before so let's say that I want to do another banner advert or I've done a ban out of a job and I want to invoice for it so sometimes I just look down your spreadsheet or a type in banner ba N and then exhale again will look up look up in this spreadsheet column and find me something that's similar to that right you may have different variations and permutations it is if I hit enter then I press the up arrow and then I press the f2 on my keyboard f2 the f2 allows you to edit a spreadsheet cell without having to click on it then I press f2 then I can turn this end part through JPEG banner and I'll change the size to 7 to 8 by 90 that's quite a common size right 7 to 8 490 and then we did this for let's say we did it for the Bojo company right so we type in hold down the shift-key typing a letter B that's done then we can type in hold down the shift-key typing a letter U that's done you can hold down your Shift key and type in N and that's done and then we should need to charge the fee and the fee will be 35 pounds for example so we've got actually nine rows down not ten so I'm going to do one more to make it 10 so let's do let's do the full team so I'm typing in I really need to type in I can just click here drag down one let's fall in drag this one down and you see that we now on invoice 10 and we want to put some data in here and I'm going to do a newsletter design that's actually just do the newsletter so type in Shift + n that's news that I'm gonna press let me do this a bit slow for you right so hold down your Shift key and press the letter n that will allow me to select newsletter design I hit the enter key that moves the selection down one so you can see this cell below has been selected I'll hit that up arrow key and I'll press f2 and then I'll type number one in here so I did one newsletter and we did this for a new company called X Y limited and we type in the letter I hold on let's hold down the shift key typing you for UK we'll put in the prices and newsletter design it cost 100 pounds and we'll hold down the shift key and type in n let's do one more I'm gonna do this one very quickly now so drag this down drag this down it was for website shape W website design is for this let's do we grow shift W shift you who charged 895 and then shift then n that's it done so we've got a lot of date ring we haven't got a lot of day up in my own business normally this spreadsheet can be anything up to five six hundred maybe even more some some month some years I may send that file to 600 plus invoices right some of them unfold lots of small jobs like just basic jobs but some of them could be quite you know bigger jobs as well cuz it's a variation I'm gonna hold down the control key using my mouse wheel to zoom out a little bit and then I'll click here and then double click here just to spread out the sheet a little bit more I'll try and zoom in as close as possible maybe let's assume do 180 is probably good for you right so we're gonna have to use our imagination here a little bit because the second part of this tutorial is actually about designing the invoice but I'm pretty sure you've seen invoices before I'm pretty sure you've received invoices to your business or in your previous businesses you've had a customer that sent or supply that sent you an invoice so you know what an invoice looks like right as an invoice number as the company name that logo the address it has a section where explain what you're paying for normally it's like a line by line item and then you have a subtotal and then a grant all I'm going to show you this in the second part of this tutorial but let's say let's use our imaginations a little bit and let's say that we created this invoice we actually have the invoice and then we sent it to the customer so when I do this data entry each time I do the data entry each time I finished one of these lines I'm market blue color blue like this so really this whole spreadsheet so far will all be blue blue tells me it so it's like a visual signal for me I don't like choose red red represent it's kind of danger and I don't like to use red color I like to use blue blue myse a neutral me just means the customer hasn't paid me alright so we've sent this invoice on the first of the first 2019 so I wrote this email to this customer I said I've done your logo design please find attached the invoice and you need to an Indian voice it says please pay within 14 days so look we're on the 15th now right so we're going to check our bank account maybe on our app on our phone or we log in online and we see that this customers paid us and normally when a customer pays you they'll put this reference number in the payment they don't always do that but sometimes they do sometimes I don't sometimes I just you know don't bother at all or they may put their company name in the reference on your bank statement you'll see that their company name or you see your invoice number so let's say that this I'm looking at my bank statement right now and the cost that I looked at my bank statement on the 15th but the customer actually paid me on the 14th so all I do is type in the 14 for the first 20 19 and then I need to decide how did they pay me and because I'm looking at my bank statement and I'm going to just say for argument's sake they sent me a bank transfer it's worth knowing how your customers paid you some people will send you check payments some people may give you cash payment and some people will send you a bank transfer that's pretty much the only two three types Peyman Chev we're going to receive write a check payment which is quite rare I still have customers sending me checks but that's probably one in a hundred payments will be revised check the other one is a bank transfer some customers may pay you via PayPal or we strongly advise that you try and avoid getting paid web wire PayPal because there's transaction fees and stuff like this it's better to get your customer to bank transfer the money to you it saves their money and it saves you money so bank transfers in the UK are pretty much free overseas payments you need you know the the the sender of the payment will get charges a fee but you for you to receive you shouldn't really get you know charged anything maybe a currency conversion charge possibly so reference number what I'm looking at my bank statement this particular client put their company name and then they were kind enough to put their company name and also my invoice number so really old always advise if you ever making payments to your suppliers you should put your company name or just an acronym away like DCP there and then the invoice number followed following it because that way when I look at my spreadsheet I'll see that invoice number not my spaceship when I look at my bank statement I'll see that particular invoice number and I'll see the company name and automatically I know what that payment is for it's very easy because there's only a zero one here it can only be for this particular in this particular invoice that we sent right so let me zoom out a little bit here there's got a bit more information hopefully you're following me so far and let's just make this a bit wider so what happens now this this was we know that this has been paid so we need to turn the the no to a yes so let's type in our first yes and it's still blue so like I said to you I like to use a lot of visual signals in my spreadsheet so I'll click on the number two and I'll click it and make it like a light green color like this so green tells me it's been paid I know it's been paid I've got all the information there about the payment so whenever I look at this spreadsheet again I may have like a hundred different invoices in here and all the ones that agreeing automatically I ignore them because I know the customers been paid I'm only focused on the blue right and let's say if we sent this invoice out and it's now like the 20 of his five more days off to this day and these customers have paid us give the customer a call maybe he's on holidays come back and you need to make payment so this particular customers actually paid me in a different way and let's say pay me on the 20th right so it's a little bit late the payment but he's paid me or she paid me and I said it to the 20 for the first 20 19 the customer eventually paid but they paid by check they paid me a check payment and when when I receive a check payment from a customer I need to fill out a little paying in slip that I give to the bank so I normally sound checks in the post but if you were to go physically to the bank and give them the check they'll ask you for your check painting book and that check painting book will have a reference you know it'll be like a painting slip right so normally I try to slip you don't even have to tie today normally I just type in the slip number and normally that spots with a lot of zeros and it will be like a zero zero zero zero one that'll be the first payment slip I'm gonna hit the enter key and you see there's a problem now I typed in zero zero zero zero one and Excel automatically assumes that that represents the number one which is factored you're not correct so I'm gonna click on this little right click then go to format cells and I'm going to set it a text value only text here and click OK now Excel will just treat this column as text values only so if I type in 0 0 0 0 1 and hit enter now I can see that reference there then when I go look at my bank statement once the check is cleared and the money's gone into my account I'll see that same reference on my bank statement and I will also see it in my painting book my check the painting book way that the payment slip book I'll see that same reference there it'll be on the little slip that I give to the bank and it will be on the bank statement so we can track it in those ways so it's really important that you document the way that you've been paid you may think this is quite complex or quite difficult but trust me when I started my business I wasn't doing this and I learned my lesson after about 100 invoices trying to work out who's paid you how do I pay you when they paid you it's a real headache so if you do this on a regular basis maybe once a week you go back to your bank statement maybe it's the first thing you do on a Monday or the last thing you do on a Friday or people every day you like isn't on your choice but do it on a regular basis and that way your books will be like super cleaning up and you're doing another thing as well let's just click here and let's make it green and let's copy this or we can just click here and type in shift Y and then hit enter and that'll add I say yes so this spreadsheet actually serves two purposes one it's really important for you to understand your business what invoices have you sent who has paid you where does companies are located which is not that relevant but it's still one of them and how did they pay you what date have they paid you and what was the reference and what job did you actually do more importantly right and a unique reference number and what date did you send in voice of the customer you've got all that accurate information in here and what you'll find is let's say that the business started on the 1st of the 1st 2019 on the 1st of the first 20 20 exactly one year you're going to need to give your your accounts to your accountant you need to give your final end of your accounts so imagine you go to your accountant you give him this spreadsheet everything in Green's being paid everything in blue hasn't been paid yet I'm chasing those customers your client is going to be your best friend he's gonna say to you you've given me literally everything that I need to know rather than giving him lows and bits of paper in a file and he has to go through one by one sometimes accountants like that because they can charge you a lot of money they can say to you I've got all of these accounts in my hand I've got five hundred invoices they're all paper-based nothing's been digitally documented now can I get extra money for me to go and do that work for you who's gonna have to sit there and fill all this information into a spreadsheet it's better you do it yourself you'll cut your accountant will charge you less and you know more about what's going on in your business so that's really important I think that's really important it's this spreadsheet served me very well for 15 years so it should serve you very well for 15 years as well no client has ever not paid me because I always know which ones have and which ones haven't all right maybe in a rare circumstance of christ' disappeared and never pay me but it's always been a small amount in my business what I always do is if the project is small like 25 pound I'll just do the work and invoice the customer afterwards if it's the first time I ever done business with a client I'll make sure I invoice them first I'll make sure they pay me first and then I do the work if it's a big project I'll make sure the customer pays out 50% deposit and then 50% on completion so like for example here this particular invoice would really have been 50% of the total value so let's fix that a little bit we've got this lead generation here we've got this website design let's just say that you know this sometimes it's all about trust isn't it so some customs I've been working for we've 10 you know over 10 years with those customers they've never not paid me they've always paid me on time so some customers you can just invoice them at the end of your job or you may say to the client no I need to invoice you 50% cause I need money to pay my wages and my staff and so forth right cover some overheads and they'll always pay that anyway but let's just say for example this customer here we've never done never done business with them they want to lead generation website and you're gonna charge them a total of seven hundred ninety five pounds but really you want to send them a 50 percent the positive voice right I just like 50% deposit what I do is underneath this spreadsheet I create another little bit of tracking information so what I do is copy this one here in fact I double click on it select the text and paste it here just like this and in this column here for example let's take seven nine five and divide that by two so let's do the real math seven seven nine five divided by two is three hundred and ninety seven and fifty pence so I'll paste that into a three hundred ninety seven fifty and then I'll paste that into here three hundred ninety seven fifty and it's a little bit saying deposit I'll say final invoice yeah in fact I will put the word invoice at the end right invoice so when this customer paid me the deposit I would have said to them on the top of the first who's your fifty percent deposit invoice will agreed everything please make payment they may pay the same next day but pretty much I don't start a job unless I've been working with him for many for a very long time it was a new customer I don't start until they pay me that 50% deposit if they want me to put my time in any Jin then I expect them to give me something in return and sometimes you've got to show a little bit of work before that happens or maybe a part of this project was a logo design maybe I'll start doing a basic logo design for them send that to them as example that you know they can trust me as well it's a bit about trust going backwards and forwards isn't it so I'll put that down here and we'll do the same with this project here this one here right actually it's for the same companies in Italy generation website website design let's change this one to this XYZ company you know you know it's highly unlike you're gonna do two websites in the same month for the same client you may do one might be a corporate site and one may be a just allegiance that it's all possible but let's say that this one was also 50% deposit invoice right so I'm showing you how I really do in my own business this is how I do it I'm actually VAT registered so I'll have extra columns in here for v80 calculations like I said this is like a small business book people.you tutorial what I'll do is import for of this tutorial once I've been through all of the main logic I will create maybe a part four and I'll show you how to add the v80 and how to automatically calculate the DAT and do some you know some other good stuff so let's just do 8 9 5 divided by 2 8 9 5 divided by 2 is 4 4 750 will copy this and we'll paste it here then we'll double click here select it with my mouse copy it paste it here and there's really one bit of information we're missing from this cut and paste down here so really what we'll do I'll fix this in a moment let's just copy this and paste it here so look at these two look at this bit of information what is missing there's something quite critical missing from here right we'll actually add that information into here it's the company name what you see is having this information in here if you don't know who got a bill afterwards right so let's get this correct here so here you can see the company name that you've gotta be billing the amount and you will already prepared the description already you've changed it from deposit to final invoice so that's what I do I just change the apology to final cut and paste it in when the invoice is ready to go I know which company is I know how much I've got to send very easy right and some in fact most of the time I don't even cut and paste this information in I'll use what I've already done the data entry for right so imagine if I started is lead gen project and now it's the 17th for the first and I need to send this invoice so let's say that this customer paid us some invoices don't get paid or later these people hang in a pestle 14 days for example but let's say that this legion project I said to the customer you've got a paid his invoice before I start your project so he paid on the same day so what I would do is highlight this column there's a few different ways you can do this way you can highlight it first and then do the data entry that's what I normally do I highlight it green so I can see what I'm typing into and then he's paid on the same day so I just click here ctrl C to copy ctrl V to paste he did a bank transfer I'll press shift and B that's a bank transfer done and the reference was just my invoice number so it was only the invoice number normally what I do is I log into my online banking system and whatever the reference is actually on the screen on my online banking I cut and paste that into this spreadsheet right I don't manually type these ones in I've cut and paste it directly from the online screen on my logins for my banking account so that's really where you should be doing it so imagine there's a bank account somewhere you're gonna copy just save that those references you'll copy it from the banking website and paste it into here because then you've got an accurate description of what that reference is on the actual banking system so we have if you have to search for it later on the banking system move very easy for you to find if that makes sense hopefully it does let's just type in shift why and we're typing yes here so we this is quite let's just say this was a small project a short one we invoice on this date he made the payment on this date now it's the 17th row there's not this 16 so we drive it down one to make the sixteen with double click here and type type 17 we're gonna create a new invoice with them in was twelve you can see some of these will highlight blue someone so we'll fix that in a moment and it's for lead gen project it's the second invoice for this we finished a project now and we need to pay get this invoice paid you've got two options you can have a cut and paste this across or you can cut and paste this down and edit it let me show you both versions will cut will copy this one copy and paste it down and we'll just double click and then double click on the deposit and just type it type in final here so that's normally how I do it or let's undo that we've got a reference down here so we can just select this press ctrl C to copy and ctrl V to paste that's one of the same job entire your choice which way you do it and then we need to copy the value right so really all we need to do is select all of these three here or all of these four select them ctrl C to copy them ctrl V to paste them and then just type change the Y to a no change the yes to an all and then all of this is going to be blank because we're invoicing today he hasn't paid today you may pay fourteen days from today so let's click on number 13 and we'll go up to here and make it blue and that invoice is done now we just gone create the actual invoice and send it to the customer that's what we're gonna do in part two it is tutorial actually that's actually create invoices so the one thing that I normally do is click here right click on it on to the number nineteen and delete and then I know that I want mistakenly invoice the same thing twice alright that's quite important you only invoice a customer twice they pay you twice and you'll do a refund and go forward a headache and you know she's not worth the effort so what's the conclusion the conclusion here is that this spreadsheet may have one hundred two hundred three hundred thousand injuries inside of it and but this one spreadsheet will only ever represent a particular year so we've made probably one mistake here right I'm gonna show you the one mistake that we've made I'm gonna close this spreadsheet and I'm gonna open up the folder on my desktop and it's called zero zero English tracker and we put a date here so that they shouldn't be 2019 well or the or a particularly date it should be 29 in spaceship space of v1 this is version one of our spreadsheet the reason why I create version one is I create a new folder and we'll call that one zero zero archive and this zero zero archive folder every so often maybe every every every time I open up the spreadsheet instead I'll do a new data entry on it I will right-click before I open it you drag it to here so I'll write hold down the right mouse button drag it to here and make a copy and then I just edit this one and make it version two so to rename this this file here just undo that to rename it you can just click on it once and then click on it once again and then it will allow you to edit it or preferably just click on it and press f2 and then just go and change it to version two so when I open up this spreadsheet is identical to the one in the archive except for any new data entry that I do I like to kind of make a little backup copy as I'm going along if anything ever crazy goes anything crazy happens you know to my spreadsheet and I've done thousands of lines of information in here it's going to be a problem right if I do three lines of data entry I save it and something goes wrong I can just close this close this down go to my archive and I've got the original copy here let me just copy that back I'll just make a copy of this move it back to this root directory rename it again and then do my work again has that ever happened to me no to be fair it's never happened it's never happened that spreadsheet it's crashed or something Crazy's happen but you never know you could be sitting in front of your computer you get a power cut and your whole computer could turn off I'm using the desktop computer there's no laptop of a battery attached to it for some strange reason you might have a power cut and your computer turns off while you're doing your work so that could be a problem right so let's just do a quick overview and then we're going to end this tutorial and then I'll put a link in the usually description for the second part we will create an actual invoice that will create a custom invoice template in Excel so rather than just using a downloaded invoice template I want actually show you how to make your own invoice that's quite important if you've got control over that invoice you can customize it is actually the way that you want it rather than using the default template in Excel and nine times that ten is in the wrong sort of formatting with some problem with it let's create our own custom invoice but we do that in the second tutorial just wanna do a quick summary of this particular video or this spreadsheet so let's start from the beginning in the beginning we need to create a folder preferably you don't create on your desktop right you're creating your My Documents you created somewhere else don't really slow stuff on your desktop while you put this here because it's a demonstration tutorial I hardly ever store stuff on my desktop if I do it's very temporary and then move it to a proper location afterwards let's just say that we say this in My Documents folder on our computer when we open up this this folder we create a blank spreadsheet and then we name that spreadsheet we should have quickly named it to invoice tracker 2019 this is a version 1 v1 and we put zeros all right at the end because when we create our other spreadsheet we love 0 1 0 2 master templates right then inside of there we created quite a special we edit our column headers we did some formatting when we did the column formatting for the type of data entry and simply we put some dummy data in here to demonstrate how to do the data entry and then I'll show you an example of why we color things blue blue tells us that it's not being paid and green tells us that it has been paid so there's a couple of things I want to show you before we finish there's something called auto fillers so I'm going to click on number 1 here click on the number 1 that highlights these all of these headers here and we'll go to data and we'll go to filter here filter and when we click on that we're going to see these little these little arrow boxes here and these little arrow boxes allow us to filter this spreadsheet now so I can click on the pay column this is what I normally do sometimes I'll click on paid and then I will untick select all and I'll select yes only and then click OK now I can see only this information is related to all the invoices that have been paid so if I click here and then tick no and Sun tick yes and click ok now I can see all the invoices that haven't been paid so this is a quick way for me to reference all the companies that I need to chase up for payment some of them I won't be chasing yet but if it's like the 17th and it's a 14 day invoice payment term these ones here I should be chasing the client up and saying you haven't paid it's 14 days payment time please make payment right when you click data here you're looking at these particular tools if you click home here you'll look at the basic tools on the home this is where I normally leave it when I'm working so how do we get back to our original spreadsheet will click on paid here and then select all click this option and click ok now you can see everything had if I ask you a question right now how much money have we generated or how much invoices what's the total value of all the invoices that we generate regardless if they've been paid or not paid we can do that by clicking down here on this blank cell and use the autosum tour so we'll click that and it will hit the enter key and we can see during this period of the first almost two weeks of business with all three weeks of business we've generated three thousand five hundred and sixty two pounds of fifty pence so that's how much we've invoice to our customers that doesn't mean that's how much we've been paid if we click again on this paid column and we go to select all tick it off and then click on yes and then click ok now we can click here and then do a novel Auto sum and then hit enter and that didn't work so let's delete that let's left-click here and drag up let's try it this way and then click autosum that didn't work even normally she should like give us the grand tour here spring enough it doesn't want to do that so let's see let's try and fix that I wanna show you how to do that right so let's click here and do Auto sum and then select these cells and then hit enter ok so that's how we do it so what we do is we select the blanks so here if you've got anything in there at the moment just hit the Delete key to delete it click on the autosum tall and then highlight the ones that you want to calculate and then hit enter and it will tell us we've been paid 1992 pounds 50 P normally what I do is click on this cell and then delete it that's just to know how much we will pay and we'll click yes and then go to no and then click okay and now let's find out how much is outstanding so let's click autosum we will click on this first cell hold down the shift key on our keyboard and click on the last cell that highlights one of them hit enter that's a quicker way of selecting all the cells and we owed one thousand five hundred and seventy pounds that's how much we rode and that's how much we've paid when off you've done all so I mean you should really delete this data go back to here click select select all of them and click OK now we're back to our original spreadsheet you can kind of leave this one here normally what I do is I will click here right click and then insert a new column insert a new row and then I'll right click and insert another new row what if you wanted to insert 10 rows all at the same time you could right click here insert row if you want it's insert 20 rows you could sit here and right click and insert one row at a time and you have to do that operation 20 times right so I'll show you a quicker way of doing that let's just say we want it insert 10 rows so I'm going to right click it insert one row and I'll insert two rows and I've got two rows I'll select them both so I left click here and drag down to select both rows and then I'll right click and insert again now it's insert two rows and then I'll highlight all of these right click again now I've got 8 rows all right and I can right click again I've gotten that 12 rows that's how you can insert a lot of rows quite quickly if I only wanted 10 I could delete them select these two and just delete them now I've only got the 10 rows that I wanted rather than doing that operation one by one 10 times I always take you forever that's right click and delete this let's save this and there's a few other things I want to show you before you finish sometimes you want to find out how much have you invested throughout the whole year or forgiven you know give a period let's say this period here we can click here just as we can fill up yes or no we can select this and select bozo company and click OK and now we can see how much we've invoice to this particular company we can click here click autosum so those souls in the end talk and we know that we've been worse than 455 pounds let's click the delete button or select the delete button to remove that and that's how you can fill with that and you can sort on more than one company you could do ABC Ambo or maybe they belong to the same business owner but are two different companies and you want to know how much every invoice that particular person we can click ok now we see both companies listed right you may want to say how much are we invoice to all companies except for X Y Zed we can do that we'll click OK now we see all companies except for X Y Zed we can click here click autosum select one of these in hit enter now we see all of the money that we received apart from that XYZ company that's a bit of a strange kind of way of you know pattern of doing something but you know no you may want to do that let's delete this let's click back here select all and then click OK and then we're back to our original spreadsheet so remember one thing to know right let me just show you here let's say that we want to see all the bank transfers we'll click this one and click OK now sometimes it can get a little bit confusing because you might select more you can actually run multiple filters right you can fill that bank transfer here then you can for our specific company over here for example Bojo only now we can see bank transfers that were all by this company or bozo and how do you know which filters have been used and which ones haven't the icon here will change is really hard for you to see probably if i zoom in I feel will enlarge it doesn't really enlarge that I can't really show you clearly but let's just zoom to our maximum around 170 the actual type of icon if you notice this one's got an arrow this one has a little filters well I economy if we click it will see that it's been used we'll just click select all and click OK that's one part they drop down to remove that filter or click here select all and click OK and now all of them just got the little drop down arrow here and that is spreadsheets back to its original state the last thing that I'll show you is what I normally do is click on this sprig click on here to highlight the whole spreadsheet let's just save this work and I'll click on this drop down menu and set the borders to all all borders I like to see the borders like this maybe not like it that way but I like to see all the boarders were at the whole spreadsheet it just helps me to read the information a bit more clearly not everything so in its own column that's got its own border everything's very very clear so to summarize we've got now quite a powerful spreadsheet that allows us to track all of these invoices that we sent to our customers and we keep building on this spreadsheet for at the whole year so regardless what date you started on we just happened to start on the 1st the 1st 2019 it could be today's date right the 26th of the 7 for 2019 that happens to be our start date of our business so that time next year the 26th of the 7th 2020 will be our end of year and you'll have this one you know you might only have 20 invoice is in here but 20 really big ones or you might have 2,000 invoices in here or small one you might have 2,000 hopefully 2,000 invoices are loads of big big payments right oh hope that for you as well but regardless of what you see what you're having this spreadsheet you will see two things green paid and blue unpaid and that will be like old dust for your content you'll can't we'll be able to do your books super quick you'll know how much you've been paid you'll better look at your bank statement all of these green have been paid I can see them all and the bank statement you've been telling your accountant the date that they've got paid how do they get paid here's the reference you've given him so much will hurt so much information that um your accounting fees should be a lot less if your accountant so happens to be charging you the same amount of money regardless whether you do this work or not then you need to find another accountant because you're giving them you're doing their job for them basically in in a way a part of their job I shouldn't say you're doing their job you're doing a part of their job but this job is more important to you than it is to the accountant because this allows you to make keep track of your payments right so it's not really for your it's for your accountant but it's more important for you than your can let's say let's say put it that way okay I've done a lot of talking that is about 55 minutes worth of tutorial there for you to think about I'm gonna move on to the second part of this a tutorial where we will create this custom-built Excel spreadsheet where we'll take some of this data and we're going to create some fictitional invoices and then I'll show you how I send those invoices to my customer just as a simple attachment in an email and we're going to create PDFs of those invoice it's not going to send them spreadsheets we'll use the spreadsheet to create the invoice template but then we'll generate a PDF and we send them a PDF copy right so let's close this down that's the end digit oriole I'm gonna go make myself a cup of tea and we'll start the second part so I check the YouTube description for the second part of this small business bookkeeping tutorial I hope you find this tutorial useful and I look forward to see you in this [Music] you