Effortlessly Manage Your Invoice Tracker Google Sheets for R&D

Invoice tracker google sheets for R&D

Hello everybody welcome back to this new video tutorial in Google seits here in this video tutorial we are going to make a customer invoice and payment tracking template we are going to make this template fully Dynamic by applying different kind of formulas and tools and first of all I will explain you what are the feature of the template how to update or how to operate the template after that we will go into how to use formula to make this template Dynamic and here in the First Column the invoice number is given here we need to put the invoice number of the customer by going in the last row and the second one is invoice date here we have to give the date by simply double clicking when you double click a calendar date picker will activate here by choosing the date you can enter the date here then here in the name of the customer we have to choose the customer Name by activating this drop down here you can choose the customer name the next one is invoice amount here we have to fill the invoice value of the customer then this overdue date will be calculated by using formula here you can see the formula is used we will get this overdue date uh by adding the credit side given to the customer with this invoice date and the credit side is defined here in another sheet if you go to the customer details seat here you can see these are the credit side given to these customers the credit side is different depending upon the customer now let's go come back to the previous seat and uh here the payment received we have to fill manually when the customer pays money against the invoice then here we need to keep track of the received payment and if the payment received is not equal to the invoice amount in that case this difference will show the difference value and if it is exactly same with the invoice amount then the difference will be zero and sometime the customer may have some adjustment amount like the debit notes that is raised to the company in that case we can keep here the adjustment amount to make the difference zero and here we have to put the date of payment and uh here we it will calculate the days taken for payment from this we can analyze the payment history of the customer in how many days the customer is paying us the payment we can see from here in the days taken for payment column which is calculated by simply subtracting the invoice date from the payment date the next column is the difference as I already told you this shows the difference between the amount of invoice and amount of the payment and if there is difference then if there is some adjustment amount we have to put the adjustment amount here to make the difference zero and here in the overdue days it will calculate how many days it has been from the overdue date and this is calculated by subtracting this overdue date from today's date and here you can see different overdue days are calculated and in the last column it is the overdue group here the overdue days is grouped into different groups like uh the one is the no overdue one is one days to 7 days there are different groups given if you go to the customer detail sheet here these are the group I have created here just to create a category of the overdue like no overdue 1 day to 7 Days 8 to 15 days 16 to 30 days and 31 days and above so these are the groups created and here let me uh add some records for example here currently this invoice 103 is given as overdue because it has already crushed the overdue date and it is on the group of 16 days to 30 days but suppose that if you receive payment of this invoice number 103 here and you receive the exactly same amount of payment that is 897 165 in this case when you put this amount here here you can see it is now given as paid and when it is paid then there is no meaning of the overdue group here because this voice is already clear already paid so it doesn't fall under any group here that's why the group is removed and also this is highlighted with the blue color because here every invoice when it gets paid it is automatically highlighted with the blue color and here you need to give the date of payment suppose that this pay this invoice is paid on 17th of uh here I'm going back to September if it is paid on 17th of September you can choose the date from here and it shows you the uh days taken for payment that is 11 days this customer took 11 days to clear out this invoice number 103 so this calculates automatically and here if I change this date to a different one this group will change for example if I go here then if I change this to uh 1 of September then here you can see the group has now changed to 8 to 15 days so everything here is fully Dynamic and let's learn how to make this template by going to a new blank workbook so here I am just uh closing this and opening a new file or the copy of this workbook where I will make this values blank and I will start using the formulas so let me close this then I'm again opening my Google sheet from here in my desktop then I will go to the copy of the Google sheet uh template on invoice and payment tracking that I have already created over here so here it is loading the files and after the file gets loaded here you can see the C copy of customer invoice and payment tracker is given let me open this copy of this template then here I have to first clear out all the formulas and the validations used here I will only leave the format and the uh data here by removing all the formulas so let me remove these first now I have only the blank format of this customer invoice on payment tracker here and first of all Let's uh add some record here like the invoice number 101 date of invoice is here if you double click there is no calendar date Pier is given so to enable the calendar date Pier here first of all select the range then here also we need the date figer calendar here in this date of payment so here press the control key then also select this range also select till the last row here here let me select this again by holding the control key select till the last row this way after selecting the go to the data menu then go to data validation here here I have by mistakely clicked on the colum start I need to go to data then go to data validation here when you click on data validation here go to the add rule in this uh criteria select the is valid date option now click on the done button when you do this then when you double click on the invoice date you will see the date Pier calendar similarly here in the days taken for payment also if you double click uh days not here you need to go to date of payment if you double click here you will get the date picker calendar so here in the invoice date I'm picking the date of 1 of October 2023 so after that here we have to now enable the drop down to choose the customer name so to enable drop down here simply select your range and here just for example I am taking till row number 15 but in Practical situation there are so many invoices here so you have to take the row here by going by assuming how many records you will have in your tracker so after selecting the range here then go to data menu then go to data validation and here we have to go to add rule option in the add rule select the the drop- down from range option and here in this click on this ribbon and then go to customer details seat and after clicking on this ribbon then select here the customer names and while selecting the customer name you can take some more row because in future if the customer gets added here that will be given in the uh drop- down as well so by assuming how how many customer you may add here in future you can select some more row while selecting then here click okay and again click on the done button and here you will see now the drop down for choosing the customer if you want to change the dropdown style that is given here you can select the range then go to this uh data menu go to this data validation and here uh you can just click on this and here if you want this drop down in different style here you can choose by going to this uh Advanced option and here you can see the display style for the drop down here if you choose this Arrow then what happens is you will see this arrow in the drop down if you want to use the chip here you can click on the chip and here it will be displayed in this way if you want plain text you can choose this option if you go [Music] here there will not be any drop down given so we have to choose either the chip or row here I'm choosing the cheap option then click done now after enabling the customer name selection drop downs then you can now close the validation and here let me select one customer for example customer a b c then here in the invoice amount we have to fill this manually suppose that the amount of invoice is 123,000 765 rupees and here in the overdue date we need to use a formula because to calculate the overdue date we have to add the credit side given to this customer ABC that is 15 days to the invoice date so here the formula will be is equal to then click on the invoice date then after clicking on invoice date then give the plus sign type plus sign then here we have to combine the V lookup so type V lookup formula then open the bracket and in the search key of the vup we have to click on the customer name sale then give a comma and in the range of the vup we have to go to customer details seat then select this range from customer name to credit side and while selecting this range also you can take some more row because in future if you add more customer then it will the formula will get the detail of the added customer as well after selecting this range then press the F key in the keyboard to fix this range then give a comma and in the index we have to give column 3 because in this selection the credit side is given on the third column that's why I give here col three as a index then give a comma and in this e sorted we have to give zero for exact match then close the bracket press enter now you will get the overdue date here sometimes this may uh display in numeric format in that case you have to Simply click on the S and go to format go to number format option and then choose the date from here if it displays in number format only if it it displays on the DAT format then it is okay now after applying this formula here you can drag this down till the last row this way now here you will get not available error So to avoid this not available error you can combine the if error formula in your formula so go here in the first cell then go at the beginning of the formula then combine the if error then open the bracket and go to the end of the formula then give a comma and then type double quotation to display blank if the formula contains error now close bracket then press enter now here you can simply drag this or F this formula down by uh click then hold then drag now the and not available error is removed and when we add new record here then it will auto automatically calculate the overdue date now here in the payment received we have to put the value uh manually when we received payment from the customer then we have to enter it over here so here I'm entering 120,000 rupees for example then here in the date of payment we have to put in which date the customer paid the payment so double click on it then choose the date of payment suppose that the payment was done on 15th of October you can choose this from here now here in this dates taken for payment we have to use this formula is equal to if then open the bracket if this dates taken of payment click on the date uh date of payment is equal to or here we can say if this date of payment is not equal to blank this will Express not equ equal to to bank blank that is the less than symbol then greater than symbol then double quotation comma here in this case we have to subtract the invoice date from the date of payment so click on date of payment minus the invoice date now give a comma and in case the the this sale is blank we have to display blank here in the value if false so type double quotation close bracket then press enter now here the DAT taken for payment is calculated simply go here in the plus button and drag this down and the formula has copied you can click on this uh cross button because here it is suggesting the auto fill but here it has fixed this before reference this we don't require here so simply click on this close button now the formula doesn't autofill here the the this formula has already copied down so this is the formula we need to use here in days taken for payment now here in the difference the formula will be is equal to then in this difference also we can combine the if formula to display blank here if the sales or if the invoice number is blank here so in this difference we can use this formula is equal to if open the bracket if this payment received is not Bank in that case then give a comma in the value if true we have to substract the payment received minus the invoice amount so click on the payment receipt minus click on the invoice amount then give a comma and in the value if fals you have to say or you have to type here double quotation now close bracket press enter and you will see the difference here now simply go in the plus button drag the formula till the last row then click on this cross button in the autofield suggestion now the formula is copied down and here the difference is calculated is 3,765 because the invoice amount was this payment received was this so there is mismatch between these two so here the difference is calculated is this now here in the adjustment amount we have to put it manually suppose that there is some payable amount to this customer from the company against some claims then here you can put this adjustment amount and before giving the adjustment amount here we have to add something here in the formula because we have to link this adjustment amount also here in the difference to make it zero if we put some value here simply go in this formula and here we have to add the adjustment amount so here give after D4 give the plus symbol and then give this j4 four cell reference here j4 that is the adjustment amount then press enter now copy this formula down again now if you put here the adjustment amount for example 3765 in this case this will now become zero so this is the formula we have to put here in difference column now go to the overdue days and here in overdue days we have to use this formula and here also we need to test the logic because for the invoice to be overdue it must be less than today's date the the overdue date must be less than today for the invoice to be overdue if the overdue date is greater than today's date then it is not the overdue that's why here to calculate overdue days use this formula is equal to if open the bracket then click on the overdue date if this overdue date is less than type the less than symbol then type here today today then Open Bracket close bracket this means if the overdue date is less than today then give a comma in this case to calculate overdue days we have to subtract this overdue date from today's date so here in the value if true the formula is today bracket Open Bracket close minus then subtract this overdue date then give a comma and in the value if fals we have to give zero because if the overdue date is greater than today's date then there there will not be any overdue days that's why in the value if fals put zero then close the bracket press enter now you get the overdue days in this case the overdue date is on 16th of uh October and today is only 4th of October that's why overdue days is given ASE zero now you can go here in the plus button then drag this down till the last row now here it is given as zero if you want to remove these zeros from here again you can go here in this formula then combine the if formula so double click on it go to the beginning of the formula then combine another if formula here if bracket open then in The Logical expression to calculate overdue dates this overdue date must is necessary here so we can create The Logical expression based on this value so if then click on this overdue date then type not equal to by giving less than greater than symbol then type double quotation to represent blank if this sale is not equal to blank then give a comma in the value of true this previous formula will work and in the value if fals go to the end of the formula here and then give a comma and in the value if falls type double quotation twice to display false now close bracket then press enter now you can simply go here and drag this down now zero will be removed from here if we don't have any data here in this area now here in the overdue group we have to use a formula that is the H lookup formula here We Will We Will generate the overdue group based on this category defined over here here you can see these are the category we have so go to this then in the overdue group press is equal to then now combine here the uh H lookup formula horizontal lookup open the bracket now in the source key we have to click on this overdue dates give a comma and in the range we have to to go to again customer details seat then select this range from here we have to fix this range by pressing the F4 key in the keyboard then give a comma the index number will be two here because it is H Lo formula we have to give the row index number that is two give a comma and in the each SED we have to give one here for the approximate match we have to give one then close the bracket press enter now you will get the overd status or group of overdue and here also you can fill the formula down but before filling formula down to eliminate this not available error you can go in the formula then combine the if error here as so go to the beginning then type if error Open Bracket go to the end give a comma then type double quotation close bracket press enter now you will get or you will not see this not available error if you drag the formula down so we have used the formulas here in in in all the columns now let me add some more records here by going here in the invoice number it is one2 and here in this date let me choose the date of the previous month because if I put the current month date here none of the invoice will be given as overdue here because today is only 4th of October so let me here change the first invoice date also by going to the date picker I choose the date of the September month over here now in this second I will take the second of September customer I'm taking customer ghi invoice amount is suppose [Music] 10,549 similarly if I go here then type new invoice number 103 invoice date is suppose uh 4th of September customer name is suppose that customer pqr and here I'm keeping the invoice amount of 2 3 4 5 6 7 for example now here you can see the overdue days is given as 2 and 9 and the overdue group is also calculated but if you see here the difference amount is given at zero but still it is giving uh the overdue days in case the difference is zero this invoice is already paid so in this case to eliminate this overdue days from here or to display paid here we need to use the formula and here in the difference amount the value is not calculated here so there is something missing here in the formula because in the in the difference it should calculate the difference amount because the outstanding or over body will be calculated based on the difference amount so here in this uh if formula I have taken the F4 reference here but I need to take here this D4 cell reference so here I'm going to the difference and updating the formula here I'm going to take D4 from here by just deleting the F4 I'll take D4 then press enter now if I drag the same formula down then I will see the difference here here you can see the difference is now calculated so this change we have to make here because when we create the overdue report based on these uh overdue groups I we have to add the difference values because sometimes uh if we get the partial payment from the customer like in this case we got the partial payment only for example here also if we get part partial payment of 10,000 here in this case there is a difference of 549 and this also should come in the overdue report that's why I have made change here in this formula now after adding these values here you can see when there is zero we have to treat this invoice as already paid so there should not be this overdue days displayed if the refence amount is zero so to do that here we need to modify this formula so to modify the formula double click on this then we have to add the if formula here so go to the beginning of the formula then combine the if formula type if then open the bracket if this difference amount click on the difference amount if it is less than zer in this case then give a comma in this case this previous formula will calculate the overdue days but here in the value if falls or if the this is not less than zero or if it it is greater than zero it is the paid invoice that's why here in the value if false we have to type here double quotation then paid then double quotation close bracket close press enter now here you can see it is given as paid and it is automatically highlighted with the blue color because I forget to delete this automatic highlighting from here while generating the blank format so I I'll delete this and we will show you how to automatically highlight when there is paid return so here uh when I drag the formula down here in every sale there is paid given when there is blank also so to remove this error from here you can again go to this formula then go to the beginning of of the formula and again combine the if formula here so go to the starting of the formula then again combine another if here so type if Open Bracket and in The Logical expression click on this invoice number and if this invoice number is is not equal to blank then give a comma then if the invoice number contains some value or if if it is not blank then this previous formula will work and in case the invoice number is blank then we have to go to the end of the formula give a comma and in the value if fals we have to give double quotation now close the bracket then press enter now you will get these paid in removed from here when you drag this formula down now here you can see the paid are removed from here now here let me remove the conditional formatting and I'll show you how to do the the conditional formatting here so I I have selected this range then go to format then go to conditional formatting and here I'm just deleting the rule from here now there is no any highlight and to highlight the row automatically if there is paid we have to use the conditional formatting in this way first of all select your whole range then go to format menu then go to conditional formatting and here in this conditional formatting rule we have to select the new new rule option or the custom formula is option and here in the value or formula we have to write is equal to then we are going to give highlight based on the value given here in the I column so here type the dollar symbol then type I then give the first row that is row number four so here type dollar I4 is equal to then here if it contains paid then we have to uh display we have to highlight with blue here the column is not I here let me drag it to right the column is the K column here the paid value will be given in the K column that's why I make this K not the I so dollar K4 is equal to then double quotation Aid Double quotation close in this case now go to the format Pinter or fi color option then choose the blue color from here now click done now every uh arow that contains the paid return here in overdue days it gets now automatically highlighted with the blue color so in this way we have to use the formula because if the invoice is already paid there should not be the overdue days so here let me add some more invoice by going here in this sale invoice number 104 date of invoice is suppose that September 5th customer I am choosing customer XY Z from here and in this case if I put here the uh invoice amount like 1 2 3 4 5 for example in this case it is given as 22 days overdue it is on the group of 16 days to 30 days now suppose that if the customer paid this invoice by paying the amount here like 1 2 3 4 5 here you can see it gets automatically highlighted with the blue color because when the customer pays the full amount of the invoice then it is no more overdue here because because it is already paid now in this date of payment you can give the date of payment that is suppose that uh it is paid on 13th of September here the days will be calculated that is 8 Days the customer took 8 days to pay out the invoice payment and here there is no adjustment so so the when the customer pays the amount that is exactly same with the invoice amount then it will be given as paid let me give one more invoice here 105 and then date of invoice is 7th of September and customer is suppose that customer customer GH I'm taking this customer DF amount is suppose 23,45689 this case it is given is in the group of 1 days to 7 Days overdue and here suppose this customer paid the payment in round figure like 23,000 in this case there is a difference of 456 and sometime there may be some adjustment amount that the customer deducted while pay paying the payment paying the payment and if the adjustment if the difference amount is okay you can write it over here to adjust this amount and make it zero so here if you put 456 it becomes zero and it will be created as paid invoice now so in this way this works here let me create one more record here invoice number 106 the date is suppose that 8th of September customer is suppose customer DF and amount I'm giving 123,00 rupees now here you can see it is given is 5 days overdue by 5 days and it is on the group of 1 days to 7 Days similarly let me add one more record here 107 invoice date is suppose 20 September and customer is customer DF again the amount of invoice is suppose that 34567 in this case it is given as no overdue because the overdue date is on 11th of October 2023 but today is only 4th of October that's why this invoice is not overdue yet and hence it is G given as no overdue so this invoicing template now is complete every formula is working perfectly here now after adding some more records here then if you want to generate the overdue report based on the overdue group you can simply select your whole data then or you can select till the row where you have the actual data data then you can go to the insert menu and then here you have option of pivot table report choose this new seat option then click on the create button and here if you want to generate the overdue report you have to go to this rows and click on the add button here add the customer name and in the columns click on the add button then go to this overdue group now the overdue groups will be given here and here in the value section se you can add the difference amount because the difference amount is the actual outstanding or actual receivable so click on the difference now you will get the report here here you can see customer wise overdue report is given here so it is very easy to generate this kind of overdue report by using the pivot table option of Google Sheets and when you have more data here then you will you can generate this report by just following the method that I discussed earlier or I discussed just now so this is very one of the very useful tool of Google sheet the pivot table tool if you want to learn more on pivot table then you can go to the video description where you can get the different video tutorial on this pivot table so this is all about creating the creating this customer invoice and payment tracker template in Google seats thank you very much for watching this video If you like this video please give me Thumbs Up by tapping on the like button if you are visiting this channel for the first time I request you to subscribe so that you will get similar kind of video notifications in future for that you need to turn on all the notifications thank you very much for watching this video if you have any comment or queries related with this template then you can go to the comment section below and write comments thank you very much for watching we'll again meet on next video tutorial