Collaborate on Biz Invoice for Technical Support with Ease Using airSlate SignNow

Biz invoice for Technical Support

in this video we'll take an Excel invoice template and turn it into a simple system that we can use to manage our invoices and easily share them with our customers over the years I've tried a lot of invoicing tools they have a lot of bells and whistles but more often than not you end up having to conform to a specific template after unsuccessfully trying to bend a lot of those system to exactly match my needs what I ended up doing is creating my own simple system that I can easily adapt to any situation that uh I'm in the beauty of this approach is that you can take the exact format of an invoice that you need in your business and then easily add some additional features in Excel that would allow you to quickly and easily prepare new invoices export them in PDF and share them with your customers and all of this without having to pay for an invoicing tool or ring or something free that usually doesn't get a lot of support this template that we're developing is available to download for free and the link is in the description down below let's open Excel and dive straight in here we have an Excel template for an invoice and this is the original I'm going to keep it here for like a reference you have the typical uh elements of an invoice build to issued by the invoice uh number date what the invoice is all about I have a version here where I color it specific things that uh I want to uh focus on so everything that's in green is for your company so you can just fill it in and it's going to be left as this but everything in yellow represents elements that you need to change every time you issue an invoice you have to keep track of the numbers in specific countries you cannot even skip numbers so if you skip a number by mistake that may be an issue you need all your uh details for the company that you're building to the content of the invoice and everything else and those are the things we want to be able to select from from a drop down to do that first let's go to view and go to normal so we have a normal Excel spread sheet that we can work with and uh then I'll show you how to make sure that only the invoice is printed at the end in order to automate the whole thing we'll add two tables going to call those customers and it's going to be in a separate sheet I just pasted those here these are the columns that we need and this is just some uh dummy data so what I have is the customer ID the name address registration ID vat ID and some contact details I also have because where I'm based in Europe we have specific circumstances where we don't charge vat based of different articles of the vat act so I have this uh as an option here to provide a reason why V8 is zero or I can just provide the vat and uh you can adjust this this can be sales tax instead of uh vat or you can remove it all together the whole purpose is to grab the exact invoice template that works for you taking this adjusting it and then linking it to the customers table and also the invoices table that uh will built in a second and being able to automate it exactly the way you need it to be automate next let's add another one invoices or invoice details and here I'll just pay some uh invoice details so we have the invoice number and you see that we can have multiple lines per invoice voice so it's going to have the same number and the same date the customer ID that it relates to here I'm tracking hours but this can be number of items or it can be allog together gone and it can be just a flat amount and if I go back to customers you see that I have a hourly rate and this is uh the hourly rate I have negotiated with the specific customer so when I'm invoicing I'm keeping track of hours in this scenario and then applying this hourly rate to come up with the total invoice amount and for each invoice I also have the payment method listed here you can go one step further have those bit dropdowns with data validation we took a look at that in the previous video I'm going to link it up here you can do the same for the customer ID it can be a drop- down list that's uh linked to the customers here but in order to keep it simple let's just say that we have the invoice details like that and our customers like that now we need to start building uh this thing here and the way I want it to work is I want to have a drop down that allows me to select the invoice that should be loaded so I'll be selecting the invoice number and every time I add the new number here it would appear in the drop down to do that I'll add a few lines up here at the top and I'll say load invoice and here will be my drop down format those a bit and this one that's going to be my drop down let's make it a bit more prominent so it's really visible what I want to use here is data validation and I want to have the list option and as we saw in uh in the last video we cannot directly link to the uh column of a table we need to use the indirect formula so this table here is what we want to have in our uh data validation rule we want to have the invoice number and uh be able to pick one of the invoice numbers from the dropdown if you go to table design you see that I've named this table invoices and then my column is invoice number so all I need to do here is go to the data tab select data validation list and then I just need to say equal indirect Open Bracket open quotation marks and just say invoices and in square bracket invoice number exactly like I have it in the other tab close quotation close brackets we're not able to directly link to the column but we can use the indirect formula to pretty much parse it if I hit okay now I have the drop down and it has all the invoices here in order for all the yellow fields to be populated we need to load some details and we can do it directly but it's much easier to do it here on the side or grab those two columns and we'll be able to hide those later and here I'll say loaded details just format it a bit and we need the following things we need the customer ID that we're going to grab from the invoice then with the customer ID we're going to grab the customer name which we need here then we'll have the address have the postal code we have the city the country the registration number of the company the vat ID the contact person the vat percentage no vat reason if there's one and also the hourly rate so those are details from the customer that will be loading based on off on the invoice let's start uh doing this here it's quite simple you can use x Loop it would be a bit faster but if you're not using the latest versions uh of excel you can use a plain old V cup we'll be looking for this invoice number and we'll be looking for it within the invoice details if you come here to the edge of the table you get this black arrow that would select the entire column and you see it uh up there invoices invoice number and we're going to drag and select the first three columns comma we want the third column and we want it to be an exact match and now we have the customer ID so if I change the invoice number now it's going to grab the relevant customer ID then the customer name going to be a vup based off of uh this value here going to fix it with F4 and then my array just going to grab from here again and select the entire table all the way until it just says customers up here so this is all the data in the table comma and then I want the second column is my company name and again I want an exact match me zoom in a bit here it's a bit easier to see then we can grab this and copy down over all of those and it would all say Lex cor LLC because it's looking for the second column but as we know that uh the order of the columns is pretty much the same as we um listed them here we can just go in and change this to three it's going to be the street address change this to four going to be the postal code five is the city six is uh the country seven is the registration number eight is the V8 ID then nine would be the person 10 would be the email the 10th column so we're going to skip it going to go to 11 which is the V8 percentage 12 is the reason for uh no vat which here comes up up as zero because we have a V8 percentage and then 13 is my hourly rate of uh 75 mean those here I'm not going to bother formatting them because uh I'll just go into Data group those and then I can hide them let's leave them like that for now if we change the invoice now let's say 11 you see that all those details are updated and we now have a reason for not charging V8 we can now start linking those so my company name will be customer name my street address will be the address here you see here that I have the postal code in the city going to grab the postal code I'm going to use erson to concatenate going to have a space in quotations and then concatenate the city I'm again the city and this is going to be the country then my company registration number going to be this and my uh V8 number I'm not going to directly link it because it would come up as zero going to say if this is different than zero then give me this otherwise leave it empty or yeah just leave it empty I can go with any but uh empty is fine and then my contact person is going to be my contact here and now we can grab those hold in control and remove the yellow because those are now automatically populated based off of uh the data here you can also remove move the green here because those are our company details and we need the invoice number we have it up here but uh we're not going to directly link it because at least where I'm at you have to show the leading zeros up to 10 digits we're going to use the text formula we're going to pass this value and then we're going to pass a format which is going to be 10 zeros and that way it would still take this number but always add the Leaning zeros okay here's the invoice number then then the date in order to get the date we'll just use a vup and grab it from the first instance of the specific invoice so if one invoice has multiple lines with the same date just grab the first date say we look up grab the invoice number then go to invoice details grab the first two columns from invoice number to date grab the second column of those and we have the date time of Supply can be different if you're let's say shipping a good or things like that so you can have separate column for that but in my case it's just the same thing and now we can clear the Highlight here because those are also uh D for the items here we'll use the filter formula to list everything in here I usually just keep it at five lines because I rarely go over that but uh you can always add more lines if you need to so this here will be equal to filter and then we want to filter the item descriptions from the invoice details so we'll grab the item description this is what we want to show and our criteria to show it our include uh property here is we want the invoice number to match so we'll grab the invoice number we'll say equals go back to our invoice and uh grab this here close the brackets enter and now we have uh the same thing basically but uh if let's say we pick invoice one has two rows we should get two rows here so let's try it out here one and that's it keep in mind that this is a what's known as a spill formula you see a little highlight here so if you have more items that uh can be held in here so if you let's say list six items it's going to list the sixth one here but if you have seven items it's going to give you a spill error so there's no way to miss an item next let's fix the qu quantities and here we use a sum ifs formula and what we want to sum if here is the hours the quantity of work that we've done so go to invoice details select the hours column and our first criteria range is that we want the invoice number to match so want to only combine only aggregate the hours for this invoice the specific invoice and the criteria is the invoice number and then our second criteria is this item description here oh go to uh invoice details again select the item description column and our second criteria is our item description here enter and now we have the quantity for this specific item in this specific invoice so for invoice One financial model development if we come here and we'll see that for invoice One financial model development we have 10 if I copy it down for data processing it should be 15 going to come here and before copying this this I need to fix this invoice number I'm going to select it here press F4 to fix it and now I can copy this down with controll d and you see that for data processing we now have 15 for my unit cost I have it here my hourly rate what I want to say is if the quantity is different than zero then give me this hourly rate otherwise give me zero I can then copy this and I need to uh fix this j14 here so that it always references the hourly rate I can then copy this to the bottom with contrl d and uh my price is the multiplication of those two and then I have my V8 base which is uh the sum of those up here okay this is done let's remove the Highlight here and now for the V8 you see I highlighted it because it must reflect whether there's a V8 percentage or not so the formula here we can just grab the V8 percentage and multiply it by our VH base it's going to say zero in this case me copy this formatting down here and my balance VI is uh the same the combination of the two going to remove the highlighting for the payment information now this is one thing that uh you cannot easily automate you can type in some uh complex formula to convert the number to uh to words but uh in my use case I just type out every time but uh still it's much simpler than having to fill everything and making sure that you're updating everything correctly so I'm just going to leave this one and I might just note on the site check manually color it in red and this won't be part of our invoice when we're printing it okay let's remove this formatting uh here I want to show the reason for no vat if there is one so I'll say if j13 is my uh nov that r reason if this is different than zero because remember if there's nothing it comes up as a zero with the V look up if this is different than zero then I want to have reason for the absence of V8 and this will be concatenated with the reason up here otherwise I want this to be empty so just say enough okay let's remove the highlighting here see we have article one of the V8 act and this is what's shown here and then for the payment method you're going to have payment method and we'll concatenate this with the payment method which is actually in our invoice details so we can just vook up it and the look up value will be the invoice number and then if you go to invoice details and select from invoice number pretty much the whole table invoices we'll see that this is uh selected again you see at the bottom that it's six columns so six and I want an exact match and uh here I have the payment method then build to similarly I'm going to say equal build two and concatenate it with my contact up here remove the highlight and remove the Highlight here since this is always me and that's almost all of it so right now if we change the invoice here you see that there different uh things being loaded and everything is working correctly so for example for invoice 15 we have such and such quantity and this hourly rate that's the price the total we have V8 at 29% that's the balance deal and the only thing we need to do is to check and update this manually if you go ahead and print this right now actually doesn't look too bad but it has those details and a second page it also shows this uh load invoice so what I want to do is go to view page break and then I can just adjust what I want to be included in the print area do it like that so that only the invoice is included I can then go back to my normal view work with it add new details down here pick the new invoice and if we hit print it's only the invoice itself you can print that on paper or save it as a PDF or if you're using Adobe you can even directly create a PDF from here and that's it a really simple way to grab any invoice template that you're currently using or or even built one that exactly match what you need and then with uh some simple formulas you can convert this template to mostly automated system that works exactly for you there you have it a quick and easy way to turn any invoice template into a small invoicing tool that you can use for your own business using such a template for invoicing still requires some manual work but in my experience it's much better to build something simple that matches your exact need need than trying to figure out how to bend a bigger uh tool to your needs when it was probably never intended for your exact use case thanks for watching this video now go watch this one to learn why it's important for small businesses to keep track of what the big players in their industry are doing and also get a great financial ratios analysis template for [Music] free [Music]