Streamline Your Invoice Reconciliation Template Excel for Shipping

Invoice reconciliation template excel for Shipping

now we want to start to do the matching on this part is the payment this payment means from the bank either ham this one is from the invoice made by the ladies okay so i have already the data so from here this data the payment i will sort by uh this rrn rn because i want to do the matching base on that one first initially same here the matching of the rrn means the payment number so i want also to sort the payment number okay smallest to largest anything as number because you remember we didn't change some of the things there so from here we can see it's not matching it's not matching where is the first thing that matching 6 398 not here not here six three nine eight seven nine oh five six three nine eight seven nine oh five so until from here 26 so from here up there is no uh it's not much okay the first matching in the payment is on the 26th so we will uh we will insert around here so they will like level 7905 okay plus down okay so means to say here is the first matching okay so now we will put the formula here equals if if uh this one is equals to this one then right okay if it's not same then write like question mark means i need to go back it later it's not matching so means it's okay so means this uh payment number is reconciled like there is a payment there is an invoicing this one without matching here it's become like question mark so here we will copy only the formula down like what we are doing is we are leveling which one is having correct so since here is question mark so we see where is the matching it's down so we continue putting it down okay again it's same matching so means it's like this so it's like a manual matching only but we just put a formula to make it little fast so we will not match one one one the number and then insert again okay see all these are matching all these are matching so later i will manually check only what is happen for this one if there is an invoice or maybe the the invoice is up maybe they write another reference number because we can still search uh we can after we match the payment we will later manually we will match also this one if the payment already match we are not looking it we are looking only for this one for the one having question mark so i'm just keeping it aside because now we are referring on the payment reference number so here again [Music] 8078 okay this one okay seven two five five okay five two seven no five to seven so this one this one one for this and it's so i miss i must insert here so it will become they will be the matching one is will become level to each other okay so on this one you can see normally up there is a payment but no invoice but here see you can see there is two reference two payment number same payment number only one and this is 80 also does you share who make it one lady only who make it if you go here three three zero but different mp number three three zero and seven seven zero so later we will check also this one what happened this one we don't know what happened maybe there's some payment matching with this mb number maybe she wrongly put only this payment number so that's you have to search manually later you need to figure out what happened on that invoice okay but for now i will just continue first inserting two level which one is matching and then once we finish all to the down then we can manually uh manually match okay so i will just post it and we will i will get back once i'm finished okay at this point [Music] searching manual searching means movie based iona search base not the payment details among mb number so okay so this number find november 22 november 2010 on [Music] reference so on a possible reason number [Music] voila [Music] nice beginning okay for this one we will write here invoice not paid because we search the possible reference number that we can find only the transaction number payment number and transaction code and both let's try to search without a nothing it's really nothing so in here we find there is an invoice but we didn't see the payment okay so we will write it there in voice not paid so later when we finish all sorting it out then we can see ah i should have put it in here so i know that it's not already question mark and it's it's like i don't need to look it again later on now i'm going through only with that the question mark so next here we see we found the mb and we found here also 1 mb november 24th this is november 24th this one is november 24 and this is november 24th so i will get this one cut i will put it here down so here i found that one payment there is to invoice okay same user see different like in between of them like there is five transaction so since so i can't pick up anything from here so one of the item i will align with that one i will remove this extra row on this one this one the payment with invoice i will write okay in here down what i will write here is a duplicate then i will take this number and just i will place it here because later i will use this reference because if i put it in the reconciling after here uh here if i put it here i will put it there the two number to show that this this and this is to invoice like if i write here duplicate then what is the reference of that other invoice so the ladies can check also what is the other invoice so they can check there is two invoice on this case i don't know why there's a duplicate okay so i'm just putting like this so we found another reconciling item like this only like it's uh there is no like you have to see what are the data in your front i'll see this one this one pigment and then from the top here so i will just get this one and i just put it here then this is fine okay same mb number but they put wrong payment number see what is the payment number here so i don't know from where they get this number but i see there is a mb number so i consider it like a match so we can say also there's something wrong how they are making the invoice that's why here find this one again i found only one so i just cut this one and put it there okay so again we will just continue so we will just continue searching for this one no nothing nothing okay then here you try again this one nothing okay so what i will write here in voice not wait i will just we will write here invoice not paid invoice not paid we don't see the payment whatever what we can do try this one invoice not paid what we will do we not see the payment so voice not paid so we will proceed on doing this one investment let's cut and put it again here so invoice not paid invoice not paid i don't see the payment so if they're not properly putting the invoice then what we can do so we will not consider okay so for this one normally the rp we will select filter contains rp okay initially we will put this one as paid not invoice because most of this one you can find paid not invoice yeah they are not making the invoice paid not invoiced not invoice okay i don't know if they're forgetting to put or intentionally they don't want to put because anyway they don't have a commission for that one but they must put it later if we found down because we still have down we found the mp number for this specific then we can just put no issues we will come back later after we finish this one okay okay i finished already on that one now we go back what we're doing so the question monarch must be reduced i don't know if i can make it smaller but 100 minus okay 100 it's still readable right okay okay now we continue with the question mark clearing up the question mark this one will search this mb see so we found it here no matching we will just take this one and put this one up here because we much based on the payment and they did not put the payment this one and this one see two numbers different mb also so what happened here i don't know we will see why you say payment number different mb and this one the company is different also okay so we will try to find something here november 22 8 no there's so much so we cannot rely on that one i will try to find one one five two four one one five two five different user also so in this case i don't know what it is means they put here two payment reference this is the payment reference this one is the let's try if we have another like this no this is the envy number of that one so this one is the one matching this one this payment number is already for that one so this payment number is wrong the num i cannot find anywhere so what i can say for this one nothing we can say only this one invoice not paid there is an invoice but no payment so like this you have to analyze only the data in front of you then you can just decide what it is while i'm doing this one i'm doing the cut not copy cut cut cut cut because if you copy means you are creating these two those two transactions you need duplicate so means like your main data you are already changing the accounts see no payment number but i match here because of the mb number okay so like this only again here something is here try this one okay so in here we can see 7292 6420 that this one same mba number so i will just put it down here okay so this one see the 64207292 so maybe typing error but the mb number is same okay so i just match this two now with seven two maybe that's like maybe they type it not copy paste then wrong so i just matched that one then here so we have here same envy number but with this one with matching payment november 22 and here without the payment so see again here okay okay so we want to okay one minute so this is the main the next one is this one okay because we want to put it together so we can understand four zero five seven alt i e cat fine here also have alt ie okay now we will i insert it here then we will see okay so this will be number three eight okay so we can see that this one is for this one this one uh four zero five seven is here three four okay so this one is the matching of this one because uh payment reference same and be number same okay the question is here this one same mb number aside from that payment number is not no reference so we can see here means this one is same okay but this one i will plot it here or no three eight so means these two any of these two this is same mb number that i've uh see the payment number also they put same only like this so we don't know in these two which one is the matching for this one so i can select anything i will select this one with more details to match it here and then this one without the matching i will put here near to each other also remove this extra row then this one is okay this one i can say it's okay because i consider it as the match and this one i can say this is a duplicate duplicate of what duplicate of this one these two is having duplicate so this one is a duplicate of this it's a duplicate and that this is the reference for which another invoice it is duplicated so like that only while doing the manual matching you have to analyze and you have to think what the what you think happened then just make a note on that one until we finish on the down i think i need to pause because it's taking a long time or i need to show more see these two payment reference this two mb number javier 1 mb number also same for that one okay we will just take this one to keep them just together okay 80 80 november 22 to transaction but this is november 30. why 6421 60. after finding this number again i found this number and this is 25 26. and this both has no matching so for the mean time we will put it like invoice not paid okay how we can match it you see the payment number is wrong this same payment number here is for this one payment number is wrong mb number also we don't have any more payment we receive from this mb number this mb number is for this one with same matching payment voucher payment number so this one where's the payment we don't see the payment so we don't have a choice but to put it like invoice not paid to rejo to reducing the account okay also this one mb32 same payment number same mb number we cannot say also this is the other the second one is for uh 200 because the payment here is 80 only and this is also december november 22 november 23 so what happened to this one we will check if maybe have another move for this one six four two one seven one six two no more only that one so what we can say here duplicate actually the effect the effect of uh duplicate and invoice not paid is same only but why i am putting it uh with different uh description so that it can uh you can you can have a better explanation if you put it in the reconciling item this uh like this transaction for this one i cannot identify the reason just in the invoice and that paid for this one i can identify that it's duplicated of this invoice so at least like uh they will say oh this one is paid okay that is paid if some because sometimes if they complain we give them this reconciling item they check and they said this is the correct transaction on that one you can but in like you make two invoice for the transaction that's why for the duplicate at least you can uh elaborate that yeah that's that transaction is valid but there is a two transaction of the same mb number so if they can show that there is really two transaction then again we can check what happened on that one that's why i'm differentiating it only duplicate and pay that advice because paid not invoice i invoice not paid uh like this i cannot identify the reason it's really only bus nothing and one of the reason here the the reference number they put and transaction number is wrong i cannot find any more payment for that one and it's on that different date also like this one no it's like uh it's like more white because i cannot explain that's only the the explanation and i not find the payment that this one the explanation is i not find a payment and also you make already one transaction of that one maybe it's a duplicate only on our side like this because if he say only that there is a transaction of that one then it you can note that it's a duplicate then show me two transaction of that one you understand but the effect is same only so like that uh so we will continue this one have matching all this one i cannot anymore see the payment no so this one i found okay this is november 22. so i found you one payment november 22 this mvc see like this uh for some cases see a six eight nine six one six three three okay this one have same mb number same mb number three six o two so this is the matching so that payment have no match but i notice here who is the client the client is the healthcare building contracting okay and see here up i notice here like there building contracting okay okay let's try to find out if there is an envy number for this one no nothing this one just double check before we do anything only that one nothing else so in here in here i can say that like i can use it like paid not invoice okay if i didn't notice this one but since i noticed this one and we go here the user is eight one five seven this one who is the user the user here is eight one five seven this for this lion she's the one who's doing for him means to say means to say on this one this one i can consider it for this payment okay by doing this one actually i am very like by doing like this to consider that one it's like a consideration to them because it's their responsibility to properly make the invoice okay but if i did not notice that one i can just write this one is a duplicate the second one is i receive the payment without the invoice because see this reference number is not really matching at all all right but because of the other things to consider like i saw there is a this mb number is uh typed by this lady when i found when i searched this one another transaction with this mb number is typed by this lady for this company then i have this folder duplicate here so i just consider i can i uh i am not sure but maybe what happened here only see the payment here is november 22. she make the invoice november 23. uh when she's uh when she's uh what is this on november 22 actually for this one she did the typing okay but maybe on november 22 she is busy or whatever the reason maybe she's not reconciled because the ladies sometimes like they're reconciling their invoice and their uh transaction in the ministry of labor if something is missing they are making the invoice on the day uh on the next day or on which day they make the reconciliation so maybe this one for this typing she make the typing on 22 but she check it on 23 that uh because they can check it in the ministry of labor how many invoice they make and then they can go in our invoicing system they can check how many invoice they make maybe when she found out that it's not same so on the next day she added which one is missing which is this one okay so and on this one i can consider it or if you want if you don't want to consider it it's up to you okay see another here for dar november 30 also okay i will search for this one so in here i mean i will put this one as duplicate eight one five seven six of the same company again should make here another one okay now i'm putting it as duplicate because i did not see any payment also like the chance that we give earlier now so in here same this is the duplication this is the reference for duplicate so i will write here it's a dope okay see eight one five seven oh if you have a lot like maybe on 22 she make transaction and 23 she's making the invoice actually it should not be allowed they must make the invoice on the same day this problem is because of them also then they are the one complaining if they receive lesser salary so i will show you this is duplicate even they added they will say uh but i put it as duplicate so means i will reduce their in the i will reduce in the account unless i saw some payments here because see it's 80 it's not the 40 why they make another invoice see 8 157 again see maybe she she don't know the mb number she just stop it from what you have details there okay so we'll just write this one duplicate we will just try to find maybe there is something in the number with payment okay so here also same let's search it first eight one five seven so on this one see on this one okay what is the mv number this is the whole i can be number so i insert if you are inserting something from here because i want to put here this one up i cannot just cut and i don't want to cut and put it up because i don't know the effect how i'm doing i'm inserting full raw because i it happened before when i reconcile i insert then i monitor yeah the lining of the matching here is like the change so means the matching is changed and the magic became wrong so in this case i will just cut this one i want to match this one then i will remove the new item okay so for me i will consider since this is same mb number and the same payment number so i will write this one is the one matching but this is a rate discrepancy discrepancy we invoice in 80 and it should be 40 but you make three invoices so this one part of aed 240. okay part of aed to party this one is uh this reconciling item is part of here invoice not paid so the effect of that transaction will be like from sts-140 uh the two the two extra see red discrepancy no invoice not paid means the two extra this one this two this the that of that one which is invoice not paid will be reduced here minus two and then the rate discrepancy here of 80 minus one minus one here the darkest part of still count minus one so in here we will reduce two with reduce one so three counts and in here the discrepancy we will add one so the effect is like we had one 240 and we'd reduce 380 380 means 240 also so uh that's how the reconciling item affects here at the beginning though we reduce though we reduce this to count we change also that this 80 into 214 okay we are doing it manually because they are doing like this yanny i don't know why they cannot just make one invoice with the uh 240 amount if they are doing like that so means to say this reconciling item will be reduced and the reconciliation will be smoother okay so that one so we will go here this one was done this one we will search if there is another payment for this one nothing 8157 again so i will just write duplicate this one is same they said they put team mmb number they say they put same but now we have 23 here okay salam 9101 okay so here we search here mb number see we saw it another same mb number okay so means for the semi number like the transaction that is possible to payment and here we saw it's 9101 it's it's not the eight one five seven who is having duplicate up so i cannot match this one one of the transaction to this one so let's try to copy this one because sometimes stopping errors there so what i will put here paid without invoice i received the payment without any voice okay it's for the company now this one it's 8157 november 23 more more more more let's try if some armor is here without matching voila so let's go back here so that mp number nothing is there no payment shows no i don't have so okay not invoice don't i will put this one nothing this assembly number is not there so i can write here payment invoice okay so like that same process only for the other things so see i see this matching there and i can say this is okay so same process only i think all the sample already covered so we will go i will i will come back until i finish this one see copy only because no matching i will put there as a match no matching because the reference number we sort well when we do the type matching by formula we sort it by payment number by the payment number they put this number so this is what reference number yeah i not change because there is no clue that from 2000 to 1000 beginning if they are not putting properly then why they are complaining if it's we not see the payment from either home we cannot see the payment if they not put the reference number properly because mainly see you notice also before the mb number sometimes it's like two like two transactions same and b number but the unique number that we can match them is the payment number it's a unique it cannot be time doubled or tripled it's a unique that's why we are we are basing our matching base on the payment number but see this if this is not a payment number c if there is a proper payment number this one we don't need to manually search and match it here because from the beginning it will be match okay so let me finish this one after finishing the matching we will come back for plotting in the part to plat in the summary yeah we're back we finally uh went very near to an end okay so i reached all up to here so this one it's not okay we will just find it's november 25th this one having much already so we don't want to touch that one in voiced not paid why it is writing okay because my formula here is c and x c is empty x is empty that's why it's okay but actually it is not so if you see it uh okay also you have to check also if nothing pair here and if it's okay then you cannot consider it as okay okay okay so this one is related for this one okay okay so here we can see this one is an excess cut so we can see this one is like 88 and okay this is duplicate only duplicate which duplicate duplicate of this one 87 either duplicate of 88 or duplicate of 89 but but i think it's duplicate of 89. okay so right here it's a duplicate of that one and then here we find the control find six four two six four two three three two eight four ctrl find six four two three two two eight four this one already have here okay and again have here so means to say and this is only related for 80 so means you say this is triplicate of this one okay so we will write here one this one this two f two okay and the last coding so these are the three numbers [Music] um okay and then go back down i think this is a triplicate only also 6.2284 let's go only six four go to eight four six five this number is like a duplication of this one but see different uh company there is no other mb also so what we can write here but this one same number but so we can write here invoice not paid i cannot say that this is duplication because the mb number is not there and this one is different person different company okay then about this one why too much of this one six four two two two seven three three okay see how many times you must add this one one and two so means this is also a trip liquid [Music] so this one have same description okay so i suspect this is old replicate or duplicates almost done see i don't know the series also is for yanny it's not very near to each other again i take so much of the time okay okay i suspect this all is like a duplicate i will finish this one and come back okay so i reach in the end but still there's some publishing on this matching because we match only the payment reference number no we did not match the rates now we will uh see let's match the rates we go to this one this one have no formula plus my nose that here so we match the rate between the invoice and the payment from either home okay so in here if this one is really not matching so that is fine so now here okay now here data because it's not filtered no so we will uh filter here in voice notepad so we want to select only okay why because if okay means should be zero but if it's okay but here there is a difference so means something wrong is here so means here it's 240 and invoice 40 so we will change this as part of the rate discrepancy also here also here also here rate discrepancy realized something okay we will include this one in the filtering remove the filter filter so what we will do here we will filter the okay after filtering the okay we will remove the zero only we will consider with discrepancy so here we can change rate discrepancy okay just this one just to make it short and then what else is possibly is here uh i think only that i think we can start plotting it now save save save in the summary okay for plotting it in the summary this the matching matching of data is done please watch the part 3 for making the summary of frequent