Streamline Your Workflow with Our Invoice Reconciliation Template Excel for Product Management

Invoice reconciliation template excel for Product Management

welcome to this week's episode of ask your XO question in this week's episode we will be learning how to write formulas that will help us allocate multiple payments made against the same invoice or multiple payments made by customers across multiple invoices so we will learn how to write the formula in this video if you have not subscribed to this channel yet please do and now let's get started with this week's episode now just to set the stage for this video this we are going to use the invoice tracker excel template that is available for free to download from n 0 calm I will also put a link to the video and also the page where you can download this template in the video description so please check that out and in this template that we have published a while ago the invoices are recorded here each invoice is entered in a separate row and then the customer for that each invoice is named here and then each invoice will have an invoice date and a due date and the invoice amount and then the paid amount is the amount that has been paid so far by the customer and that is been entered directly in this column and then the outstanding amount and what's the status and everything will be calculated by the template using formulas automatically now in this template we have entered the paid amount directly as a user input right so what we're going to do now is to extend this template further by saying what if this invoice number 1 the customer did not make one payment made in multiple payments so what will you do if the customer for example has multiple invoices like for example customer 3 has 3 invoices and they actually pay together across multiple invoices in their payments so how do you make sure that you can track the payments that the customers made and also allocate correctly to each invoice and calculate the outstanding amount so that's what we're going to learn in this video ok so now I have removed a lot of the sample data so I've kept it really simple so that we can clearly follow what's going on with the formula and the first option here is the payment by invoice so I'm gonna go into the sheet but I've already created a table so if you want to track the customer who is making multiple payments for each invoice then you will set up a table like this with just three columns invoice number payment date and amount that the customer paid for that so this means for invoice number one the customer made two payments one on December 5th $400 one on December 8th for $200 and the similarly for invoice number two there were two payments made two hundred and four hundred dollars on different dates now we want to make sure that our invoice tracker collect correctly calculates the paid amount for each invoice so first we're going to do a simple sum if function so we're going to you know use the table that we've already created so before I do that I just want to inform that this is an Excel table where you can enter more and more rows and it'll automatically expand so the formula will work only if you have created like a table and we have named the table as t underscore pym t underscore I and V so this is kind of important because this will come through in the formula so we're gonna write in the first cell in the table for a paid amount column sum if open paranthesis now we need to provide the range the range is going to be the invoice number column in this table and comma and what we are going to search for is the specific invoice number here so I click here and you will see that in the formula it says at invoice number and then comma now what are we going to sum we are going to sum the payment amount here close parenthesis and enter so now we can see that the formula is correctly calculating 300 dollars for the first invoice so if we go back here first invoice 100 plus 200 300 invoice number two two hundred plus four hundred six hundred dollars so I'm going to go back here and the second invoice say $600 third and the fourth invoices no payments have been made so they are zero so this is how you can calculate the paid amount if you have customer making multiple payments for a single invoice the second scenario we are going to deal with is a little bit more complicated where the customer is making payment across invoices so that's a little bit complicated but we will deal with that as well with the formula so I'm going to first delete the formula in this now and then we will start entering a new formula for that scenario before I do that the payments made by customer are tracked in this table so you will create a similar table and say customer when they made the payment payment date and the amount of they paid on that day so now I have customer one making a payment of $100 on December 5th ousand dollars on December 8th similarly customer two makes two payments on two different dates these payments are not specific to any invoices we are going to allocate ing to the sequence the payment made and then we will just keep allocating the amounts for each invoice so we go back here this is where we are going to write the formula so the first customer the customer one has two invoices invoice one and invoice two which is five hundred and thousand so totally thousand five hundred but the customer made a payment of hundred and then thousand so this is where it gets tricky because the payment made by the cus the first payment made by the customer is less than this invoice amount but the second payment is greater than this invoice amount so now in order to write the formula remember the logic so the logic is going to be find the minimum of available payment amount or the required invoice amount whichever is the lower is going to be the result of the formula so now let's start writing the formula so that it becomes clearer so what we're going to do is to first find out what is the available payment amount so payment amount will be first the total how much the customer has paid so far so that means some if and then we are going to go into the payment thing and then say we don't need this part we'll just go and do customer comma this is very similar to the previous formula we wrote so now instead of invoice number I have to do customer because we're doing everything by customer now and then the sum range will be the payment amount by the customer and then do the circuit so now this is the total amount that the customer is paid so customer one has paid $1,100 and then the problem with this is that now the 1100 will get allocated every time the customer one appears it'll allocate 1100 every time the customer 2 appears in allocate 700 because this formula will give the total amount paid but we want to subtract amount that is already been allocated to previous invoices so this is the part which is a little bit complicated so I'm gonna hit alt enter and then I'm going to do - and then we will do a sum if again so in this sum F what we have what we are going to do is to use the offset function so we'll do offset offset requires a starting point so I will start from here the customer header and then comma how many rows should I go zero how many columns should I go zero comma and then what's the height of my range and this is where I'm going to use a growth function to say this is our current row right that's where we are writing the formula so current row minus row of the header which is this and now this when I close the parentheses so this gives me the range of all the customers until now so it will not include the current cell current row it will include all the previous rows so for all the previous rows of customers I'm gonna copy this part because it will be helpful for me and I'm gonna do comma and the sum requires a criteria so what are we looking for we want to know that exactly the rows only where this customer is there so I'm going to click in here and that becomes at customer and now I'll do a comma again and now what are we going to sum up we're going to sum up all the paid amount column in previous rows in all the previous rows we want that so this is where I'm going to just paste what I copied earlier which is our offset function the only thing which is going to be different here is we're not going to start from the customer header we're going to start from the paid amount column header so it'll start from here and it will go all the way and before and it will stop before the current row and I will use an actual example to illustrate that better but I'm gonna close this sum if now and let's check if the sum of closest correct okay so now we have closed the sum if so this tells us the all the allocated amount the total thing will give us the allocated amount this one and then this one is the total paid by the customer so total paid minus total allocated will give us how much can we be allocating to this invoice so I'm gonna hit enter so we'll see what happens here so all the 11-hundred has been allocated to the first invoice and which means for the second invoice there's nothing to be allocated because we have already allocated all of the paid amounts similarly for customer to all the $700 have been allocated to this invoice so nothing is left to be allocated here so we are going in the right direction that we don't have the 1100 in both places now it's only one place however you don't want to allocate $1,100 to an invoice which is only 500 right so that's the last part of the formula which is to do a minimum function say minimum of the whole thing like this amount and put a comma and then say if the invoice amount is less then just allocate only the invoice amount so close parentheses hit OK now what happens is the 500 has been allocated to the invoice number 1 and then the remaining 600 are allocated to the invoice number 2 so 500 now is paid in full no outstanding amount but the second invoice only 600 has been allocated the remaining 400 amount should be paid by the customer this is because totally 1100 is what the customer one has paid totally 700 is what the customer 2 has paid and so 700 is allocated here remaining thousand 300 is still do so it is calculating correctly so so just to recap again what's happening here we have the sum if this looks at the total amount that the customers paid so far which includes all the payments and that payments by customer table and then this part is actually calculating what has already been allocated towards paying previous invoices so all the previous invoices payments made should be subtracted and so the difference here now the difference here will tell us how much more can be allocated and then we compare that with the invoice amount and then we take the minimum value so this is the formula that will help us correctly allocate the payments to watch multiple invoices one thing to keep in mind here is that we are using the offset from the top to the bottom so which means that it'll go in this order so if the order in which it's allocated is based on the order in which you have entered the table if you have any questions or suggestions about this topic please post them in the comments and I look forward to hearing your feedback I'll see you all in the next week's episode of ask your Excel question thank you very much for watching you