Streamline Your Finance Process with the Invoice Reconciliation Template Excel for Retail Trade

Invoice reconciliation template excel for Retail Trade

consider subscribing this channel if you are visiting this channel for the first time and also press the notification Bell icon to get regular video updates in future and uh here in this video we are going to learn how to create a invoice and payment tracking template in Microsoft Excel and here in the uh in the screen you can see already build invoice and payment tracking templates first of all we will understand how to update the template how to use how to use the template what are the features of the template then we will learn how to use formulas here in the template and how to make the template we will learn later so first of all let's understand what are given here in the in in the column A it is invoice number column B is invoice date column C is the customer name which we can write from this drop down we can choose from the dropdown this invoice date we can activate by using this calendar if you learn how to enable this calendar in Microsoft Excel then you can go to the description section of the video video and learn how to enable calendar here in Excel and invoice amount is given on the D column the due date is given on the E column which is calculated by using formula here the next one is nood from invoice it means the number of days from the invoice date how many days it has been till today from the invoice date the next one is payment reip this we have to fill manually after getting payment from the customer we have to update it here the next one is difference of amount it shows the difference between the invoice amount and the payment amount the next one is adjustment column if we want to adjust the difference and make it zero then we can use this adjustment column the next one is date of payment here we have to enter in what date the payment received by using the calendar we can enter the date the next one is dat taken for payment here we have to use formula here suppose that if I give the payment date then this dat Tak for payment will autoc calculate and the last one is the status of invoice and here status of invoice and here we have to use formula this formula is little bit complicated if you are a beginner in Excel here we have to use some uh formula combinations like uh ifs formula if formula and and formula here also the today formula is also combined so this in this way the columns are given here in the template now here I'm entering one record and showing you how does this work like here if I give invoice number four then here I have to give the date of invoice for example the invoice was made on suppose that 36th of May customer I can choose from the drop down customer B and invoice amount here I'm giving 12,300 rupees this case it says us the invoice is overdue because the uh number of days from invoice is already 23 days and if you go to the customer list the customer B has here I'm am checking customer B has the uh credit side of customer B has the credit side of 30 days and if you go here number of days from invoice date is uh 23 days only customer B here the due date is not given that's why the overdue is given here I have to the due date here now it says the invoice is due because the customer has got credit side of 30 days if you see here it is 30 days and it is only 23 days now from the invoice date that's why it is giving due but if you check change the invoice date from here if I give one older date here for example 10th of May in this case it says the invoice is overdue and it highlights the overdue invoice with the red color automatically and uh uh this is given as overdue because the customer credit side is 30 days only and the number of days is already 39 days so it is showing the overdue invoice so in this way the system works by using this nood column we can follow a payment with the customer and for example if this customer be paid payment of this invoice like here I'm putting 12,200 rupees in the payment receipt in this case still the invoice is given as overdue because we have still 100 rupees remaining for this particular invoice so it says overdue until and unless the amount becomes zero or negative if the customer paid payment in excess like 15,000 rupees in this case it says it is clear but until and unless the payment becomes uh zero difference becomes zero in that case it says overdue like if the payment is a partial payment like 12,200 rupees in this case it says overdue and if you want to adjust this 100 rupes difference you can go here in the adjustment column and give it in minus figure like minus 100 rupees and this will be adjusted and the amount will become zero then it will give us the invoice is clear so in this way the uh system works here now here in the another invoice and payment tracking I am going to show you how we can use formulas here in the template here I'm am removing the drop- down created also by going to the data validation by and then I am clearing out this drop down from here now I have a blank template you can get this template sample from the description section of the video so I'm not going to make this formatting part here I'm only going to make the use the formulas here how to write formulas I'm going to show you so the temp template format is ready here now here I am writing the first invoice number which is one for example in this invoice date I can choose the date from this calendar or I can uh also directly type the date here I'm choosing the date of 24th of May 2023 you can write this manually also by typing 5/24 this also you can do it here here I'm choosing from the calendar 24th of May 2023 and to display the customer name drop down here you have have to first select it then go to the home menu go to data validation from here go to data menu not home menu you have to go to data menu then go to data validation then here you have to choose you have to choose list and in the source you have to link the customer name from the customer list seat link the customer name from here if you have more than this customer then you can select till more row here so that in future if you add more customers then the drop down will work perfectly now click okay now here you can see the drop down menu is Created from here you can choose the customer's name in this invoice amount we have to give the invoice amount manually for example it is 13,256 for example the due date we have to calculate by using formula so here the due dat formula will be equal to then the due date is given by today type today formula bracket Open Bracket close minus here the formula will be different not the today formula here to calculate the due date we have to add the credit side given to this customer from the invoice date the due date will be invoice date plus the credit side given to the customer so formula here will be equal to invoice date Plus then use the bookup formula to get the credit side type bookup open the bracket the lookup value is this customer's name give a comma the table array we have to go to customer list then select it from customer name to credit side then we have to fix the reference of this table by pressing F key in the keyboard or you can manually type the dollar symbol here and if you want to learn vook of formula and the use of dollar symbol in x then you can go to the description section of the video and learn it now give a comma the column index number is three here because the credit side is given on the third column of our table array so type three here in the column index number we have to type three then give a comma and in the range lookup we have to give zero for the exact matth so type zero then close bracket pess enter now you get the due date if it is given on the numeric format you have to change this to the date format by going to this formatting option go to this date option if it is given in given on number so the due date is calculated the invoice was made on 24th of May 20123 and it will be due on 23rd of May 20 uh 23rd of June 2023 because the customer a has got the credit limit or credit side of 30 days so here you can see the due date is now calculated by using this formula now here in the number of days from invoice the formula will be equal to then we have to write here the today formula Open Bracket close bracket minus then click on the invoice date this will give you the number of days from invoice if you subtract this date from this date the number of days is 25 days and if this number is given in date format sometimes while leing formula it may be given in a date formatting so you have to change this to number formatting by going to the home menu go to number and remove these two digits after decimal by going to this decrease decimal now 25 days this payment reip we have to calculate by using uh payment receipt we have to put manually this is not a calculation column here we have to give the manual value when we receive payment from the customer we have to enter it here the difference amount will be calculated by the formula which is equal to the invoice amount minus payment received amount plus this adjustment amount this formula will give you the differential amount like the invoice amount was 1,456 we don't have received any payment against the invoice that's why it is given as difference is given is 13456 the next one is adjustment column here we have to F the value manually then the date of payment we have to fill manually after filling the date of payment for example here I have given a payment date like 13th of uh 13th of June and here I am giving the payment received which is like uh 13,000 rupees for example in this case the difference is given is 456 and here is no adjustment for now and in the days taken for payment the formula will be equal to the date of payment minus the invoice date and it is 20 days the days taken for payment is calculated is 20 days this column will be helpful later when we want to see the credit rating of the customer in how many days the customer is paying us the payment we can know from this days taken for payment and lastly the formula in the status status of invoice will be little bit complex so here have given the formula for this uh status of invoice which is given here this formula is applicable for this a uh for this six row so here if you see the six is given in the row reference we have to change this to four for this first example so here in the status of invoice we have to identify whether the invoice is a clear invoice or overdue invoice or due invoice so we have to here check for different Logics and display the value ing to the logic will Define here so the formula here in this column will be equal to then we have to start with the ifs IPS function here which checks the multiple condition ifs then open the bracket and here in The Logical test one we are going to display whether the uh invoice is clear or not and for the invoice to be clear the difference amount should be less than or equal to zero if this is greater than or equal to zero greater than zero then it is not a clear invoice so for invoice to be clear this amount must be equal to zero or less than equal to zero less than equal less than zero means it is exess payment so if the payment is excess then we can say the invoice is clear so the the condition or logic here will be if the differ amount is less than or equal to zero in this case the invoice is supposed to be clear so here we have to type give comma then type double quotes then type clear then double quotes close so here we have defined the first logic and the value now give a comma and in the logic test two we have to now identify whether the invoice is overdue or not and for the invoice to be overdue there should be two condition fulfilled the one is the difference should be greater than zero the difference amount should be greater than zero and another condition is the invoice due date should be invoice due date should be less than today's date if this due date is less than today's date and if the difference amount is greater than zero in that case we can say the invoice is overdue so here the formula will be we have to check multiple condition here or more than one condition here that's why we have to now combine the and formula here here so type and here and open the bracket if you want to learn this and formula separately then you can go to the description section of the video and find out the tutorial for this and function and within this and formula we have to give two logic here the first logic is this difference amount click on the difference amount it should be greater than zero this is the first logic then give a comma and the second logic is this due date click on the due date this due date should be less than today's date so here type the less than sign then type today formula today Open Bracket close bracket today formula now give a comma today formula now now we have to CL uh we have to now close this today formula so this will work as a logical test two now give a comma and here in the value if true we have to now write overdue between inverted commas double quotation then type overdue double quotes close now give a comma and here in this uh logic test three we have to now identify whether the invoice is due or not and to display due for the invoice we have to now test another logic which is this uh due date should be greater than or equal to today for the invoice to be due so here the third logic is click on the due date of invoice this should be greater than or equal to today so type today formula here bracket Open Bracket close now give a comma and here we have to now give due between inverted commas type inverted comma then type due inverted comma close bracket close then press enter now here you can see the status is given as is due because the customer has got the credit side of 30 days and the number of days from invoice is only 25 days that's why it is given as due but if you change the date from here like if you give some older date here in the invoice date in this case it will display is overdue here you can see the status of invoice is given is overdue so in this way this formula works here here I am now going to give one more more uh invoice number which is invoice number two for example invoice date I'm choosing from this drop down which is for example first of 1 of June 2023 customer I am selecting customer B invoice amount is suppose 12,32 due date is calculated calculated by formula which you can drag and F from the previous one this way the D is now calculated by formula the number of days also we have to drag and fill the formula down this way the payment amount I am leaving blank difference amount we have to drag and fill the formula this way and then the adjustment amount is we have to give manually next one is data payment which is a manual field so here in days taken for payment I am dragging the formula down and in the overdue I am dragging the formula down again in this now here you can see this is given as due because the customer B has credit limit of if you go to customer B it it has got the credit credit side of 30 days and the invoice is now 17 days only that's why it is now given as due and if you go to the previous template here here you can see the values are blank if there is no data so to make these sales blank if there is no data now we are going to learn how to do this because uh here you can see the not available error is given here some numbers are given here zero are given so if you want to remove these values if we don't have any data here in that case you can do some modification to the formula so here I'm going to the first Formula and here to remove the not available error you can go to the big beginning of the formula and combine the if error formula so type if error Open Bracket then go to the end of the formula give a comma and type double quotations it means the blank in the Excel formula if we write double quotes it represents blank now close bracket hit enter now if you drag the formula down you will get the any error removed from here now there is no not available error similarly if you want to hide these uh numbers from here you can go to the formula and combine a if formula here go to the beginning of the formula then type uh if formula here if Open Bracket and if this invoice date is blank then we can display blank here because if there is no invoice date there is no calculation for this n from invoice that's why here we can combine this formula if Open Bracket then click on the invoice date equal to if this invoice date is blank so type double quotations to represent blank here then give a comma and in the value if true we have to now write blank by double quotes it means that if the invoice date does not contain any number or value then it will display blank here in the N from invoice now give a comma and in the value it falls this today formula will work now close bracket press enter now if you drag this formula down you will be you you will see that the numbers will will be removed similarly in the difference amount also we can use the same method go to the beginning of the formula type if Open Bracket if the invoice uh date or invoice number you can here say if the invoice number equal to blank then display blank by typing double quotes then give a comma and in the value if false the formula will work now close bracket press enter Then simply double click here now here you can see the zero are removed similarly here in the D T for payment also you can do the same equal to if bracket open if the invoice number equal to blank then it will display blank give a comma go to the end of the formula then close the bracket press enter and when you double click on it it will display blank here you can modify this you can uh display value here based on the date of payment also if there is date of payment then we have to display Des ton for payment otherwise we don't have to display anything here so here the better option is you have to link this with uh invoice number with this date of payment so here I'm dragging this invoice number selection and taking it over here in the date of payment in this case the formula will work better because because the DAT taken for payment depends upon the uh date of payment given here so if there is date of payment given then it will calculate otherwise it will not calculate and here you can see this is removed when you put the DAT taken for payment here then it will calculate like if I put here 13 then here you can see the value is calculated automatically now here in the status invoice also I will link the if formula so that if the invoice number is given here then it will calculate here otherwise it doesn't calculate so here I'm going to the formula going to the beginning of the formula typing the if if open the bracket and here in The Logical test if this invoice number equals blank then give a comma and display blank then give a comma in the value if falls this entire ifs formula will work now go to the end of the formula close bracket press enter now you have to Simply double click on it and here you can see all the remaining values are removed from from here now we are almost done we are now we have to now give a conditional formatting here to give the color highlights so to display color highlights ing ing to the uh status of invoice here I am going to uh give one more record which is for the clear invoice so here I'm giving invoice number three date of invoice is suppose that first of June customer name is suppose that customer C and invoice amount here I'm giving 12,700 rupees and for example if the the customer paid the full amount like 12,700 we have to record it over here after giving this then now we have to give the uh date of payment which I'm giving from here like uh 17th 16th of June and here you can see it is given as clear because the customer C had already paid the invoice amount here in the payment receipt now after doing this now let's go to the conditional formatting to do the automatic color highlighting here for that first of all you need to select this range then go to the conditional formatting then go to new rule option and here you have to go to use a formula to determine which cell to format and here we have to use this formula equal to then we have to give this last column which is l l column and here we have to give the first row which is L4 row and here in this case we have to fix the column by pressing giving dollar sign before column L here then press equal to then here we have to now use this overdue so here I'm writing inverted comma then typing overdue inverted comma close this is our condition then go to the format option and choose the color highlight for overdue go to the field option and here you can choose the color highlight that you want to use for overdue invoice here I going to more color option and choosing a color from this uh standard color list here I'm choosing this color for the overdue invoice I'm choosing the red one I'm choosing this color for this color for the overdue invoice now click okay and then click okay and again click okay and here you can see the overdue invoice is highlighted with this color now similarly we have to repeat the step for due and clear invoice so here I'm going to here I'm going to going again to the conditional formatting of the home menu going to the new rule option and use a formula to determine which sell to format here I'm giving equal to then L4 equal to then here typing double quotes then clear double codes close here we need to fix this L colum by giving dollar sign before L now go to the format go to the field and here choose the color for the clear invoice here I am choosing this blue color for Clear invoice now click okay then click okay and here you can see the uh the uh clear invoice is now highlighted with the blue color I don't want to give any color highlight for the DU invoice so here the conditional formatting is also applied for example if here if I uh if I want to change this overdue to Du I can remove this uh I can change the invoice date for example if I give the invoice date of some later date like 26th of April it is still now giving overdue because it is 53 days here I am choosing different date I'm choosing the date of June here like if I choose the second of June in this case this invoice now gives is due because the customer a has credit side of 30 days and nood from invoice is only 16 days that's why it is giving due invoice so here in this way it works if I want to make change this to clear invoice I can put here the full amount of invoice or I can do the adjustment here the differential amount is 456 if I want to make this differential amount zero I can simply go here in the adjustment column and give negative number which is minus 456 in this way also the invoice is given as clear because we don't have any differential amount here and the invoice is now highlighted with the blue color automatically so the conditional formatting is also completed now here we have to display some values like the total outstanding we have to display here like given here in the first template so here I am going to display the total outstanding amount which will be given by the formula so here I'm going to this and writing total outstanding total outstanding and the total outstanding amount will be the total of this difference amount so here I am linking equal to this difference amount sale of H2 where we will use formula later and here I'm hitting enter so the this will be given by this total outstanding and here we have to use the subtotal formula in this value Fields so here I'm going to the sale and writing the sub total formula equal to sub total Open Bracket then for Su we have to use nine so type nine here then give a comma and here we have to select our range so here I'm selecting from the D4 to D12 now close bracket press enter you can expand this range ing to the data you have here I have taken till row 12 only for example now you can copy the formula for these value Fields like in the payment reip in difference amount in the adjustment amount and in the uh uh there is no other value field here so we have to use this sub tootal to get the total of the values here we have I have used the sub tootal because when I filter this data letter then this formula will work properly because if I want to see the detail of One customer only in that case this sub total formula will work for the filtered data also now here you can see the total outstanding is given is one20 321 here I am adding one more record of the duplicate customer like here I giving the invoice number four I am the invoice date of uh first of 2nd of June and here I'm choosing the the repeating customer like customer a have chosen here and invoice amount is suppose 2,432 in this case it is given as due because it is only 16 days and the payment received I am not writing anything here now when I add one more customer here in this case if I want to see the detail of customer a only I can go to the data menu then use the data filter now if I go to choose custom a from here customer a in this case the shof total formula will now calculate the details of the customer a only that's why here I have used this sub total here you can see the outstanding of customer a is 2,44 432 rupees only and if you choose all customer from here all the customers in this case you can see the total outstanding amount is 320 753 so in this way we design this invoice and payment tracking template in Microsoft Excel and here I am one adding one more record just for example uh invoice number five suppose that the date of invoice was made on 17th of May here I'm choosing the customer D in this case when I put the invoice amount here like 200 uh 23,89322 is given as overdue because the customer D has the credit side of 21 days only but if you see here it is already been 32 days from the date of invoice that's why it is given as overdue so in this way the system works and in this way we can use formula and functions in this template and this much for today's video thank you very much for watching if you like this video please press the like button if you have any comment or queries on this video you can go to the comment section below and write comments I would also like to request you to share this video to the people having interest in learning Microsoft Excel once again thank you very much for watching we will again meet on next video