Collaborate on Excel Invoice Bill for it with Ease Using airSlate SignNow

Excel invoice bill for IT

hi everyone welcome back here in this new video tutorial today we are going to learn how to create a automatic system for allocating the incoming payment to account receivable invoice and this is useful for the accounting professionals and for the business who are selling their product or service to different customer in credit term and they need to keep track of their invoices and the payment received from the customer customer and here the received payment will be automatically allocated to the account receivable invoice based on first payment first allocation method and the Assumption here is every time the customer are not paying the payment that is exactly same with the invoice amount sometime they pay same with invoice amount and sometime they also pay the payment in partial basis and here first of all I will show you how does this system work then I will go here in the blank format and then start creating this system so here I have some customer and their invoice detail and here I have the detail of payment re if I delete the payment receip from here here you see there will not be any payment reip amount and suppose that here I have the three invoice of customer ab and here the customer AB paid 50,000 Rupees for example in this case here you see the 50,000 is auto allocated here in the first invoice payment because the invoice amount is 65,000 and suppose that if this customer paid 75,000 in this case here you see then the remaining payment of 9568 is auto allocated to the second invoice of the customer similarly if the customer GH paid here 35,000 rupees here in this case you can see the 35,000 is allocated to the first payment of the customer G and here in the customer final balance it will show the final bance status of the customer that you select from this drop down here in this case the customer AB has 4,975 rupees reable and if I make this 150,000 for example here you see it automatically change to the advanc payment now because the customer AB has total invoice amount of 11 15,9 75 but here we already received 150,000 so there is the advance amount of 34,000 25 and if there is a advance payment then the color is blue here and if there is outstanding or theable then color will Auto change to the red color for example if I put here 100,000 only in this case the color is given as red and if the payment is nil then it will show NE here for example if I put here the whole invoice amount 11 15,7 975 in this case it will show as Neil so everything here is fully Dynamic only we have to enter the detail of the invoice number date of invoice customer name and invoice amount then here we need to put the detail of date customer name and the amount received the adjustment process is dynamic here so let's go to this second seat where there is a blank format this blank format you can download from the video description below then start how to use formula here because the main important part of this template is the formula used here in the payment received column which should be able to dynamically allocate the received payment to the receivable invoice so let's go here in the blank format first of all you need to create a customer name selection drop down here for that you have to prepare your customer name list somewhere in your Excel sheet which I have listed here these are my customer name list so after preparing the name list then here select the sale go to data tab then go here in the data validation and in this data validation dialog box choose list from here then in the source click on the source then go to your customer name list then select the name of the customer now click okay and here you will see the drop down from which you can choose the customer name now you can simply copy this sale then paste the validation here by selecting this right click then go to this past spal choose the validation option click okay now here also you will have the customer name selection drop down after creating this customer name selection drop down now let's F some data here for this I'm going here in the first sheet and I'll directly copy these details from here contrl C then I will create a copy of it here now I have some data here and here in this detail of payment also I am going to take this First Data control C then here I'll create a copy of this now I have some data now the thing we have to do is to write the formula here in the payment re column which will automatically allocate this amount here in these invoices so for this first of all we need to write here the Su if formula to calculate the total payment received from this customer so here write this sum if formula open the bracket and in the range of the sum if select this customer name range of the detail of payment section then lock this range by pressing the F4 key in the keyboard then give a comma and here in the criteria select these customer name then give a comma and in the some range select the amount received range of the payment detail now lock this by pressing F4 key then close bracket press enter now here you see this has given the total payment received from the customer a this is the total payment received from this customer if you add one more payment of this customer like in 25th of January 2024 the customer AB paid another payment which is for example 15,000 rupees in this case here you see the total payment paid by this customer is given here now we need to substract the payment that is already allocated to some invoice for that we have to expand this formula to get the already allocated payment we have to again use the Sumit formula and by subtracting the already allocated payment from this total payment we will find out the unallocated payment the payment that is not allocated and then the not allocated payment will be allocated here for other invoices so here double click on the formula and then type the minus sign then start the sum if formula again sum if open the bracket and here in the range of some if we have to combine the offset formula offset open the bracket here in the reference of the upset let's click on this customer name that is the colum header then lock this range by pressing F key then give a comma and here in the row and column we don't have to give anything so type two more comma here then it will go to the height parameter and here in the height parameter we have to combine the row function so type row here Open Bracket and in the reference of the row function click on this first customer name close bracket minus then again write another row function open the bracket then click on this customer name colum header then lock this by pressing F key close the bracket now close one more bracket for or upset function then give a comma and here in the criteria of the Su we have to select the customer name that is the first customer name here so click on this C9 sell then give a comma Now here in the sum range of the sum if we have to again combine another offset so type offset function again open bracket in the reference of the second offset let's click on this payment received that is the colum header then lock this by pressing key then give a comma and here also skip the row and column parameter by giving two more comma then here in the height parameter we have to again combine the row function so Type R Row open the bracket and here in the reference of the row we have to take this first payment value that is given in the E9 cell here because of this formula I not able to click on the sale so here I can simply pass the sale address that is E9 now close the bracket then minus write another row function r w Open Bracket then here in the reference of the second row function click on this column header that is payment received then lock this by pressing F4 key close the row function now close close the offset function by another bracket then again close one more bracket for the Su IF function now the formula is complete you can now press enter and here you will get the unallocated payment here it is given as the whole payment because in this case there is not any allocated payment that's why the total is given here but when this formula is copied down later then in the second sale it will start identifying the unallocated payment then the unallocated payment will be allocated for the invoice if the unallocated payment is greater than the invoice amount then the amount that is equal to invoice amount will be given here and if the invoice amount is greater than the unallocated payment then the unallocated payment value will be given here for that we have to combine the minimum function now so double click on this go to the starting of the formula then type m i n mean function open bracket in the number one of the mean function this entire combination of formula will work now go to the end of the formula give a comma and in the number two of the minimum function we have to click on the first invoice amount sale that is the D9 sale so here type D9 then close bracket then press enter and here you see now the invoice amount is given here because out of the unallocated payment and the invoice amount the unallocated payment is greater than the invoice amount that's why here we have to take the invoice amount so the minimum function here is identifying what value to allocate here if the invoice amount is less than unallocated payment then it will take the invoice amount here and if the unallocated payment is less than invoice amount then it will take the unallocated payment here now if you go here in the plus symbol then drag this down here you will see the remaining payments are automatically allocated to the invoice of the customer AB here if you see we have received 13,976 but the the invoice total is only 11 15,9 so in this case the customer has the Advan amount as I show you earlier here now the advance amount should be calculated here in this sale so to calculate the advance amount or the receivable amount or let's say the final amount of the customer here we have to use the Su if formula again so here the formula will be equal to then type some if open the bracket first of all we need to calculate the total amount received from this customer that is given here so for that in the range we have to take this customer name range then give a comma in the criteria click on this customer name comma in the sum range select this amount received range then close this sum formula then type the substraction symbol then again write another Su if open the bracket in the range of the second Su if take this customer name range from this uh payment or Auto allocation section then give a comma in the criteria select this customer name comma and here in the S range select this invoice amount range this way now close the bracket and press enter and here you see the receivable from customer GH is 55432 it is given on negative number because it is the receivable amount so here go to the drop down choose customer AB now here you see the advanced amount of customer AB is given that is 15,000 rupees because this 11597 has settled all these three invoice and this is the advanc payment that's why here you get the Advan payment of customer AB suppose that here if I receive a payment from customer GH in January 26 2024 I will choose customer GH from here and here the payment is suppose that 60,000 rupees in this case here you see the 55432 is allocated here and the remaining payment will go here in the advance payment if I take customer GH from here here you see 4568 is the advance so in this way we calculate the customer final balance now we need to display the receivable amount ount in red color and the advance amount in blue color with the advance and receivable attached with the numbers for that we have to use the custom sale formatting so for that right click on the sale then go to format sale option and here we have to go to the custom option and here we have to create a formatting so remove the general from here then we have to first uh Define the formatting for the positive value here which is the advance amount so here first of all open a square bracket square bracket then type the color that you want for advance so type here blue color square bracket close then type zero give a space then double double quotation then type here advance then double quotation close after this type semicolon then square bracket then type R red for the negative value square bracket close then type zero give a space double quotation then here type receivable then double quotation close type semicolon then double quotation Type n i NE double quotation now click okay and here you see the custom formatting is applied the advance amount is given in blue color with the advance attached with the number this means 4568 Rupees is in advance for customer G if you change this to customer a here you see 15,000 rupees in advance once but if you add a new invoice here for customer AB for example it is made on 6th of January 2024 I'll choose the customer name customer AB from here and invoice amount is suppose 33234 in this case here you see the 15,000 is allocated for this invoice and there is receivable amount of 18234 so this system is now dynamically working and here if you add more invoice then add more payments then it will keep on allocating automatically and here you can check the final balance status of the customer if it is if there is no any receivable or Advance then it will show as nil for example here the customer G has the amount of 55432 and if you make it 55 432 here in this case if you check the final balance of customer GH it will be given as Neil So in this way we can create this fully Dynamic automatic allocation of incoming payment to receivable invoice system in Microsoft Excel thank you very much for watching this video if you like this video please give me Thumbs Up by tapping on the like button below and also subscribe to the channel to get similar video updates in future thanks for watching we will again meet on next video tutorial