Optimiza Tu Plantilla De Conciliación De Facturas En Excel Para La Industria Tecnológica De Manera Eficiente

Invoice reconciliation template excel for Technology Industry

hi I'm gonna show you how to use vlookup for bank reconciliation okay so if you're working in accounting you're gonna have to do book to bank recons meaning that your book and bank have to match have to balance out has to be zero so I wouldn't hand set this up for us for the purpose of this tutorial I have my bank total and then I have my sir thank total right here and then I have my book total alright and as you can see I went ahead and put this in there is a three thousand five hundred fifteen dollar discrepancy so how do I find the discrepancies without looking line by line as you know there's gonna be thousands of bank transaction so it's going to be impossible or just gonna take forever alright so I'm gonna go here and I can I'll just start the vlookup forming up on my book side now enter it in so equal vlookup alright and I'm gonna use the lookup value as a transaction ID so because it's a unique identifier all right you want something that's very unique that's not gonna have repetitive numbers client name is not gonna be good enough usually a transaction ID or a client ID works works well enough so click that comma and then my table rate it's gonna have to be from the bank statement so here again I have the pain statement here highlight all of it okay oh one thousand of it alright comma alright and then I'm gonna use column the next number three which is going to be this column because I want it to look up the dollar amounts okay comma and then I'm going to put in false alright false means that the the amounts needs to match dollar-for-dollar okay so I always use false okay so it pulled up the amount well this is not gonna be good enough so what I'll do is I'll add an extra a little bit to the formula so minus b3 so b3 is that is this column right here alright so what he's gonna do is it's gonna take this amount from the book and it's gonna subtract it from the bank amount alright and if it's if there are no amount that means it's 0 it's balanced so it's perfect I don't need to look at that so I'm gonna drag this formula all the way down and again this is on my book side and bam there you go there's some numbers here so that means that should be some discrepancies alright now I'm gonna do a little trick here to make it even more efficient alright cuz you're dealing with thousands of data I'm gonna put a filter in right here no I'm gonna sort it I'm gonna take away anything that has a zero that was it cuz I don't want to look at that and there you go so I have about one two three four five six it's six bank transaction that aren't matching that has a discrepancy if I highlight this I should get the 3515 bam there you go now I know exactly which transaction has the discrepancy thank you for watching