Streamline Your Invoice Tracking Spreadsheet for Management Efficiency

Invoice tracking spreadsheet for Management

welcome back on this part three video tutorial on automatic invoice in Excel in this video you will learn how to maintain invoice and payment tracker sheet dynamically we will transfer the details of invoice to payment tracker sheet and automatically adjust the incoming payments of customers to the receivable invoices based on first payment first allocation method let me quickly show you the demo of what we are going to do here here I have an invoice and payment tracker sheet in the next sheet I have to enter the records of incoming payments from different customer as soon as a detail of payment receipt is entered it will automatically allocate in the payment received amount column of payment tracker sheet let me create here few invoices of some customers in the invoice and payment tracker sheet the details of invoice is automatically populated suppose that I have received 12,000 rupees from customer AB then I will have to enter this payment receipt here in this payment receipt sheet let's enter it here click and see in the invoice and payment tracker sheet the pay payment of 12,000 is automatically allocated to the invoices of customer AB the first invoice is fully settled and second invoice is partially settled here you will see the final balance status of the selected customer which is 879 receivable suppose I received another payment of 3,000 from customer AB let's enter it in receipt sheet check in the inv and payment tracker sheet second invoice also fully settled down and the remaining payment is given as advanced payment if you receive 5,000 from customer GH enter it in the receipt sheet check in the invoice and payment tracker the payment required for first invoice of customer GH is settled and remaining payment goes in the advance so that was the demo of what we are going to do let's start with the process of adding this feature in automatic invoice template add two new sheets rename first sheet as IAP tracker which means invoice and payment tracker in the invoice and payment tracker sheet in A1 cell write the title invoice and payment tracker in row number number three put the column headers which are invoice date invoice number buyer's name invoice amount invoice due date and payment received amount select column headers and one row below it then create table put the name of table as IAP tracker in F1 cell write balance status in E1 cell create a drop- down list for customer selection rename sheet two as payment receipt put the title as payment receipt details in the r number three put the column headers these are date customer name and received amount here too create a table with the name of PMT receipt formatting of the new sheets is complete now we have to transfer the details of invoice to this invoice and payment tracker sheet every time we create and save a new invoice for this let's go to VBA and create a sub procedure first Define the variables dim I AP as worksheet dim IV as worksheet dim LR as long set the values of these variables set I AP is equal to this workbook doe sheets I AP tracker setv is equal to this workbook doe sheets invoice LR is equal to I AP do cells I AP do row do count one do and XEL up doe row plus one to copy and paste the invoice date to last Blank cell of invoice date column write this code in do range H1 do copy IAP doe range a and LR doe paste special XEL paste values and number formats now copy and paste this code and change the cell and column reference for invoice number buyer's name invoice amount and due date to exit from copy mode write this code application do Cut Copy mode is equal to false next we have to call this procedure every time we save the invoice for this go to print and save subprocedure then below this call data transfer call data transfer IAP again go to save invoice procedure and call data transfer IAP below the call data transfer let's make an invoice and check if it is working as expected here you see data successfully transferred to invoice and payment tracker sheet here when I save the first invoice a blank row is created just delete this blank row of the table create few more invoices here you see the details of invoice is automatically given here in invoice and payment tracker sheet next we have to use a formula in payment received amount column to automatically allocate the incoming payments of customer to their invoices based on first payment first allocation method for this click on this cell then start writing the formula the formula I'm going to use here is little longer which has combination of multiple functions so watch it carefully let me increase the height of formula bar so that the whole formula will be visible click on F4 cell click on the formula bar press is equal to type Min function open parenthesis in the number one parameter of Min function use the sum IF function in range put the customer name column of payment receipt table put a comma in criteria click on the first customer name in the IAP tracker table put a comma in some range put the received amount column of PMT receipt table close sum function with a parenthesis put minus symbol again use another Su function in arrange combine offset function in reference of offset function click on the buyer's name header cell skip the row and column parameter by putting three commas in the height parameter combine the row function in the reference of row function click on the first buyer's name close row function with parentheses then put minus symbol again use another row function in the reference click on buyer's name header sell then close row function and offset function by two parentheses put a comma in the criteria click on the first buyer's name cell put a comma in the sum range combine the offset function again in reference click on payment received amount header cell skip rows and column parameters by putting three commas and in the height parameter use the row function in reference of row function click on the cell below the payment received amount header due to the formula the cell is not clickable so click on e4 cell then press the right arrow key in keyboard to select that cell close row function with parentheses and put minus symbol again combine another row function in the reference click on the header cell of payment receip received amount then close row function offset function and sum of function with three closing parentheses put a comma in this number two parameter of Min function click on the first invoice amount cell then close the Min function with the parenthesis and hit enter formula has displayed zero in the allocated payments because I have not entered any detail of payment in receipt tracker sheet suppose that customer CD paid payment of 8,000 then enter it in the payment receipt sheet to select the customer name create here a drop-down list using data validation put 8,000 in the received amount now check in the invoice and payment tracker sheet out of 8,000 7,572 is allocated to the first invoice and the remaining payment is partially allocated to the second invoice of customer CD to check the final balance status of customer selected here subtract the total invoice of each customer from Total payment received of each customer by using the sum of function e to display receivable advance and nil along with the balance value use here custom cell formatting here you see 2951 is the final receivable balance from customer CD let's out a payment receipt of 3,000 from customer GH out of 3,000 27192 is allocated to this invoice and remaining payment of 281 is in the advance when you generate a new invoice of customer GH this Advanced amount will auto adjust in the new invoice here you see Advance amount of 2 81 is allocated to the new invoice automatically and the new balance is 1,483 receivable in this way this will keep on auto allocating the received payments from customers to their outstanding invoices automatically in the next part of this video we will learn validating the quantity column of invoice sheet with the available stock or inventory because while generating an invoice it is very important to check the available stock to ensure timely Supply and to reduce the chance of invoice cancellation for example when you put the invoice quantity of 500 units here how do you know that this stock is available in Warehouse or not for that I will show you how to automatically connect the invoice sheet to inventory management sheet and get stockout notification in real time while generating an invoice if you want to download the work book file used here in this video visit . Excel trining you.com and search for automatic invoice in Excel so that's all for this video thank you so much for watching see you in next part of this automatic invoice and Excel with more interesting features