Collaborate on Create Invoice from Excel Database for Finance with Ease Using airSlate SignNow

Create invoice from excel database for Finance

hey everybody welcome to this video in this video I'll be showing you how do you make an Excel database and work your way with it around a certain project or maybe you can make just make an Excel database and uh use it for keeping track and maintaining records of your business uh maybe anything that is that that is relevant to it obviously you don't want to make databases for toys in your house of course so anything you're doing that needs a database can be done in Excel and it's simply very easy I'm going to be showing you in this video so just stick till the end and make sure to watch it throughout completely so I'm making a little kind of a inventory here serial number particulars with their rate quantity and amount okay so I'm going to be making this into a record and now this is please note that I'm I'm doing this in sheet number one so this is one tab of excel okay so let me just format it a bit give it a yellow color so it looks more like definitely a business don't you think okay so here's going to be the company name or company address okay company name goes in here uh for sure company address and uh hold up a second company address goes in here and the third row let's just use the third row for uh let's say company name company address okay we can say contact contact number contact information or just simply contact okay let's just form out these a bit as well so that they they go in well they blend in well with the rest of the um uh you can say information hold up color yellow hold on let me just increase the font size a bit okay looks much prominent and better okay and now this is you can add in these are placeholders obviously you can add in your company name here I'm just giving a basic template obviously don't you I know you can make much better ones so I'm just the purpose of this video is not to give you templates it's to show you how do you use databases so color it white hold up a second I need to highlight it to a different color let's just do it green okay that looks better that looks Fair Okay so we've got company name company address and contact now underneath this we're going to merge in center and we're going to write in invoice okay so this is going to be our invoice invoices this is sheet one remember okay we're making an invo voice and how do we sort of like make it in a professional way is what I'll be showing you in this video using databases so again let's just do a bit of formatting okay so let's just insert a few more Call ar rows here so that we have some space to at least do something so over here we can add an bill number okay and uh over here we can add in uh let's say date when a particular bill was made an invoice okay and uh let's say over here Merion Center first let's just hold up a second we can format it again as well this this one would seem nice okay build number we've got date we're going to insert a few more rows over here and uh now we can basically make hold up a second name name of what has been sold or something like that I'm not good at accounting and making invoices so don't judge me here I'm just doing a random piece of business here okay name and contact so not name of the goods we sold obviously the name of the person who is being build okay there contact we're going to add in the the the particulars details underneath the last uh row that we have formatted this serial number particular is Right quantity amount okay so the initial have been done they're in place we've got invoice company address name and contact we've got bill number date we've got name contact address of the person who's been built the person the customer something like that or uh yeah or maybe I don't know who who who is there on the invoice I don't I have no idea so yeah I don't know who goes the invo who makes the invoice and who's details go in there so I'm sorry if if I sound like a Nutter but I don't know it actually so okay in the serial number where we can have basically the you can say the codes let's just make a table for examp first here format it a bit okay we've got the row column we've got the serial number column we've got the particulars column the quantity column and I'm sorry if my voice sounded weird I was yawning okay we've got amount column now what we're going to want to do next hold up a second what we have to do next to make an invoice we need a database Excel needs to know if we're making an invoice where does our data come from okay now Excel doesn't does not need to know we can make it manually as well but it's like uh it's better to make a database because uh that makes your work more easier you know what a rate because let's say there's some random person in a farway warehouse working and making an invoice they don't know what the rate of uh lollipop strawberry lollipop is or maybe lemon flavored lollipop pop is they need to know what the rate is and they're making an invoice now they not they're not the relevant people to know it so they need a database now the database will be stored in Excel obviously if it's a small business I'm I'm I'm not talking about a big business that's that that is somewhat similar in scale to Airbus or boing I'm talking about small stuff you can do every day right so just chill out relax we've got total amount sales GST I don't know what that what the hell is it sales GST no it's definitely not going to be sales G GST I know what GST is it's general sales Tex correct me if I'm wrong but sgst cgst beyond my understanding man I'm not going to bother worrying about what on Earth they even are there are something some taxes let's say so our invoices pretty much looking decent and good it looks like some some uh some human made it okay authorized see authorized uh sign sign okay signatur okay so the person at the end obviously has to sign is that the invoice is verified so we're going to make a space for him to do it don't you think and uh this is sheet number one again the invoice is in sheet number one we're going to make our database in another sheet that's going to be tapped to in Excel so then we can con basically connect our invoice with that with that database now sounds like a pretty decent invoice don't you think right okay so now let's say we're selling a shirt of a certain size certain design certain material and it has its own serial number now we know it's a shirt how do we know what what the rate is and maybe the rates are changing every to every other day or there are there there are price there are price fluctuations that people making the invoices don't know so what we're going to do is we're going to make a database and the rates can definitely change in the database so anytime an invoice is made so now you see I've switched to tab number two now here I'm making my database so products and now I'm I'm adding their adding in their rates and I'm adding in there okay hold on a second I'm adding in products now so shirts trousers okay next one we've got sir what do we have in stock okay we've got uh sir sorry again okay we got T shirts man adding that what else sir we've got James okay boy adding James we've got lowers okay adding lowers okay now we've got products in place now we need to know their rates excuse me sir what is the rate of a shirt 500 what is the rate of a trouser 600 t-shirt 500 jeans 800 I'm definitely not buying it it's the most expensive thing I'm selling man I myself I'm not spending that much money I don't know what currency that is but it's got to be expensive okay uh imagine if it's uh $800 Jamaican dollars don't you think okay okay now I'm making a database so these are the rates the the price lesss okay now it seems very tidy right now because there are just a barely few items right imagine if I had like a thousand items or maybe like tens of thousands of products or different things and whose prices are changing day every day don't you think that's a cumbersome to deal with that is definitely unless you've seen how hard it is to you won't realize how hard it is unless you've seen it so we have to make databases of course so now let's just uh if you see if you saw it I had the particulars in a drop down menu in my particulars Tab and I'm using the wheel lookup tool to sort of like um no uh can let the D let the databases and the products be connected I mean you can hold on a second let me show you okay so these are my products these are their rates now let me just highlight this in the v lookup tool so it's included in the formula and uh it's been done we've got B13 sheet to and then we've got this entire thing highlighted and sorry for the silence I yawning again I'm feeling kind of sleepy but don't worry it's all cool so B13 sheet 2 I've got the formula highlighted and now it say it's not available okay now let me just add in my thing there we go now it knows that a trouser costs this much and a shirt costs that much it's basically able to look up prices associated with a particular product so for trousers it's 600 for t-shirts it's 500 so this way you can go on to correlate two different tabs and not correlate basically maintain correspondence between two two things in Excel your invoices and your database so you're when you're making invoices those you're making invoices they don't have to worry about rights they know that they're being done in the they're being taken care off in the database they just have to look at it and just make the invoice add in particular products those are relevant and then make their uh they get their rates automatically there we go Traver is 600 now this way it's much much easier to take care of and it's really simple t-shirt 500 now jeans is going to be 800 again expensive I must say that's a total of uh 1,900 I don't know what currency but that is 1900 units or units of price so amount definitely it has to be this thing times the quantity correct there we go boy oh hold on a second boom that's that's embarrassing boy quantity two we've got two pairs of we've got two trousers a pair of trousers that's costing 1,200 units of price now the value is invalid for the rest because we've got we've got no data in their relevant uh uh what do you call these cells right so now hold up 1,00 let's just just a second and I if error hold on a second if error let me just tweak it a bit okay C13 * D13 basically rate times the quantity and then okay there we go so now we're going to expand this and make sure it's it's following the rest of the the rest of the cells are also following this particular instruction so now you see I've got my proper invoice in place okay I've got uh let's say the next thing I have in the invoice is t-shirt now okay now you see it's it's it's did you see what happened was I when I added t-shirt the rate was 500 because I since I did not have the quantity the amount was not properly mentioned there so this is what happened so now let's just add in serial numbers there we go boom quantity four there we go oh my God this m this man made a lot of spent a lot of money you he's he's making a big Bill don't you think he purchased two trousers four shirts and five jeans my God is he a human or what how many clothes you feel are purchasing man man just take it easy 7200 units of price and now there's going to be 9% Tex 9% that's that that's the sgst and the next is cgst that is also 9% and boom we've got basically two slots of taxes in place as well and now we're going to add the net price where the taxes and the grand total is Sir here we go 8496 units of price are you ready to pay this sir yes I'm ready to pay it come on cash or card so that's how you're going to deal with your customers okay you're GNA make a heavy voice Hey sir this is 8496 a price you want to pay yes sir I want to pay how many uh you've got cash recard okay yeah that's that's how you're going to do it and uh there we go it looks like a neat neat tidy in voice right so cheeky cool I mean it's amazing and once it's been built the person who is responsible who's the signatory is going to sign it and I've added some placeholder addresses to make sure it looks cool this is a PDF I'm going to export it as PDF so nobody can play with my prices and make sure the build is not tempered and uh there we go my invoice has been made and that's how I used databas I used my own database to make an invoice look at that how nice it looks man it's so amazing okay so did you see how I do it and and uh that's it hold on a second so I'm sure you like the invoice it's really good I've used my own database to make it and uh here I'm playing with the macros hold on a second reord macro macro name shortcut control control let's see it's let's assign the particular control K okay there we go and now I have I have a macro in place so let me just empty this thing empty all this boom boom boom nothing with me right now boom boom boom boom boom there we go Hiroshima dropped on my invoice it's all empty now you've got nothing to sell I'm on the road I've got no invoices to make no money to make what the hell am I going to do in thison huh okay Bill number 12 oh my god I've so sold 12 I've sold things to 12 customers that's crazy man okay 2224 that's it second FB 2024 Bill name William William oh Serena Williams we've added surnames okay contact number okay address ABC they live in the the children's book the children's alphabetic book they live there don't got particulars Serena Williams purchased a trouser uh four trous and she spent 2400 units of price then she purchased a shirt uh six shirts I'm sorry that's going to be 3,000 Miss Arena and we've got lures how many lures have we got we've got ah oh my God six lures she's not common slow man she's spending about a 9 9k units of price almost let's empty that control K I used him accuracy as soon as I pressed my shortcut I just my macro did it my clear Dean Vo voice like it clean as a whistle yeah calm is a right so this is what did I do uh now I didn't have to manually go and sort of like erase all the things I had to just press the shortcut and boom things were done so another macro control uh let's say control Q boom now what I'm going to do is press my shortcut and see what happens hold on a second let me just put a plug it in here hold on a second uh there we go this is my data okay and now what I'm going to do is I'm going to ah let me just highlight it all and uh a line left okay so this is the same thing I've I've had over there I had there okay this is the information of Serena Williams and hold on let me go back here there we go what's say it's saying paste special different methods of pasting material okay okay there we go it's now horizontally pasted and instead of it's instead of uh columns it's pasted as rows it's as a single row so now what we going to want to do is highlight this all and just let's go back to sheet number five and there we go I have it here okay I'm going to insert no hold on a second I that right now H bill number date name contact address total sgsd cgsd grand total so it's basically making entries of the invoice you see how I'm making a database here first I had the pricing and now I have the invoice details like this is the invoice number so I'm basically making a complete full-fledged data so it's making helping me keep track of everything that I do so that at the end everything is easy to keep track of and things are more manageable so this is what is happening here you see I have my two invoices I made an invoice here but now it's going to be empty the next second I make another one but still it's going to be stored in my database there which is going to help me keep track of it so I can retrieve previous invoices without worrying about anything boom Another nuclear bomb dropped in my invoice and now it's all clear I'm not selling anything I'm all empty empty Pockets what you think so what do we have to do here okay record macro now the macro has been recorded okay now it knows once I press that this thing erases from here and goes into the database got it now see I'm adding a random another random invoice 100 13 2224 I'm selling a lot of things on this day I sold a lot of things on this day don't you think name who's going to who's it going to be it's going to be uh Carl Carl San don't you think Mr Carl you're here at our tour welcome that's your contact number one of the weirdest numbers I've seen in the world okay where do you live you live in the alphabet's book of children okay we're going to deliver in the first page ABC serial number three he's purchased three things particulars he's got shirts he's got trousers he's got uh come on James again okay 23 my God he's so rich 65 boy boy oh boy he's he's purchased things worth 10679 units of price don't you think now I press the shortcut and let's see where this thing goes bad boy goes and where hold on hold on hold on 2224 nil2 n22 n42 what am I saying it's okay name Coral corl San did you think hold up okay now with the new number again with the same address living in the book ABC book or maybe ABC can be Abraham's Building Company don't you think okay sure we've got trousers we've got uh J t-shirts 12 23 and about like 32 so a lot of shopping going on here that's my third yawn for this video guys I'm too sleepy I don't know why there we go okay what happened is it get it went and there in my database I didn't have to go and manually added there so this is how you can use macros and use databases to make cool stuff in Excel see you next [Music] time