Streamline Your Processes with Our Invoice Reconciliation Template Excel for Entertainment

Invoice reconciliation template excel for Entertainment

hi welcome to chandu. org and uh thank you so much for joining me in this video uh episode where we're going to talk about an interesting problem this is a problem that uh I received by email from one of our readers in New Zealand her name is christien and uh she works as um you know in in accounting department let's just say that and uh one of the challenges when you are working in a department where there is a lot of money involved is uh you always have transactions that are not matching up this is a real problem that many of us face for example uh let us just say you are balancing your checkbook or something like that uh what what happens is you would have written checks for so much amounts and your bank statement would also have similar amounts reflecting and you trying to match up the transactions uh sometimes you see that you have written a check for 100 but uh there is no matching $100 figure in your bank bank statement that means there is no balance in that scenario so this is the same problem that our friend Kristen was facing and she asked me if there are any solutions and last week I wrote an article where I explained the problem and solution using formulas we talked about some ifs formula so I'm going to show that solution to you as well uh but one of the readers commented there saying uh wouldn't it be better if we use a pivot table in this example and obviously pivot table is a better solution in this case so I thought I'll do a followup and uh uh we'll take a look at the pivot table solution as well so let's just jump into Excel and uh and here is the Excel workbook that I have prepared as an example so let us just say you are looking at uh a bunch of transactions starting from 1st of April 2014 to somewhere in the middle of May 2014 so about a month and half of transactions uh each time we have vendor reference and the amount that we have uh we have to receive from that vendor let us say so here what it means is vendor one vendor 001 needs to pay us $264 it's a really small company okay and uh and then for for the time being just ignore that column I'm just going to hide that okay likewise from vendor 2 we are supposed to receive $872 34 now you can also see that subsequently vendor 2 has paid some amounts because they have paid the amount is in Brackets this is the standard accounting notation which means it's a negative amount that means we are receiving that money so you could see that if I select all those three Embers I uh the sum is $872 34 34 so essentially we are supposed to receive $ 8723 for from vendor 2 and subsequently those amounts are realized although uh it was not one payment it was three payments that total up to the amount due so in this case we can consider vendor two as matched that means whatever is due has been paid up uh so we can technically call Vendor 2 as match so this was the problem that Kristen was facing she wanted to know how to tally those totals uh without doing it manually or verifying the numbers uh by calculating with hand so that that is where we proposed a solution with Su ifs formula uh I'm not going to walk through all the details of the suip formula but I'm just going to show you the simple formula here so it is a Formula that looks like this uh remember this is a table so we don't have to write C1 C2 or something like that we just refer to the data by the column name uh So within brackets vendor reference would refer to the vendor column within brackets amount would refer to the amount column so let's just uh take a look at that and I'm going to uh Zoom it up a little bit so you could probably read this uh better so we are saying uh for the second ignore the round part we are saying sus of amount uh for the vendor uh where vendor name is same as that that in current row so that's the formula so what this is doing is it is asking Excel hey what is the total amount for vendor 01 uh and then it comes up and says uh something like 2.64 assuming there is no other incidents of vendor 001 in this table so then what you would do is you would just uh round it up by two decimal points this is just uh uh to prevent any errors what happens is sometimes Xcel is known to throw a a very very small value when it is zero so when you are expecting zero Excel would throw something like 0.01 or something like that uh this is probably due to an error in the way floating Point numbers are represented anyway this is technical stuff that we don't have to worry about so since we don't care about amounts less than 1 cent we just round it up to to the nearest scent so what we are asking Excel is uh how much amount is due from vendor 001 and round it up to the nearest scent so once that is done we are checking whether that is zero or not if it is zero that means uh for that particular vendor whatever amount is due has been paid up so it is a condition that we are checking is it equal to zero or not so if it is zero then we will get true here else we'll get false here so that's what happens uh and you see these false and True Values so this is true that means vender 2 is matched vender 12 is matched uh wend 14 has matched when w 15 is matched like that okay so this is uh one simple way to calculate the uh status of transaction matching and once we calculate we could use a little bit of conditional formatting to highlight the rows that are matched so that we know uh these things are tallied and whatever is not not highlighted that's the one that requires our Focus okay now while the formula approach Works beautifully and it gives the result that we are looking for it is also a little bit long winded so instead we could use uh the beautiful powerful pivot tables to do this so for that what I'm going to do is I'm going to delete this column we don't want to calculate this anymore so I'm going to delete that and uh and click anywhere inside this table and uh insert pivot table so what we are saying Excel to excel is hey I want a pivot table from this data uh this data is named as table one so Excel selects all of it and then we'll click okay we get a blank uh worksheet with the pivot table layout all of this is uh pretty standard and I'm sure you have already seen the pivot table screens if not I'm going to provide a link to our pivotable table tutorial in in the uh in the description of this video as well as on the article that corresponds to it with it there you will you will be able to learn more about pivot tables so once we have it here all we just need to do is drop the vendor reference into the rows and amount into the values so essentially you're asking Excel uh What's the total amount due for each vendor and you could see that Excel has calculated those numbers and uh and this is what I was referring to uh 5.68 434 e minus 14 that really means uh Excel is saying the value for vendor 2 is uh 5. 6843 uh multiplied 10 ^ of -4 10 ^ of-4 is nothing but dividing by 10 ^ 14 so it's a really small value it's it's it's a minuscule fraction that uh because of the floating Point error in Excel that just comes up there so if we have used the round formula here that would have fixed this problem anyways we calculated the sum so to find out which vendors are matched all you have to do is filter this pivot table I'm going to insert a column here and move it somewhere down so that you can see the whole thing go to Value filter and then we'll just say greater than zero so that means I'm asking Excel uh show me the vendors who have uh some amount greater than zero now if I just say zero I'm going to see vendor two also because technically that value is greater than zero it is it is something like 0.00000000 and then one or two or something like like that and we don't care for that amount that's a very small amount uh so maybe we could say something like is greater than or equal to 0.01 that means if at least 1 cent is due I want to know such vendor name otherwise don't show it so this would kind of remove the vendor names that are uh that are uh you know technically zero and uh everything else is shown so you can see that these are the vendors with an outstanding if I want to see the reverse I could just do the opposite also you could go to Value filters and you could say say less than 0.01 so this will show up all these vendor names these are the ones that have paid up again you can see that many of them have a small fraction instead of zero uh and uh in case vender 9 he has paid us $22 uh although there is no other outstanding probably he was paying off the depth from prior prior months or prior Financial quarter or something like that okay so this is how you can use P tables to quickly nail down those vendors who have an outstanding or those vendors who have paid up so this is also a powerful way to analyze data like this and uh do the reconciliation or matching up again with the pivot table solution uh it's almost chpl all we have do done is inserted a PIV table and then we just dragged the vendor reference here and amount there and then filtered the pivot table on a value filter uh so that we could choose which vendors have uh you know how much amount you and then eventually filter it out okay uh there are many other things that you could do here for example uh you could uh let me clear the value filters you could sort it by the amount due so that you could you could get a list of top 10 vendors without standing so you can go to Value filters and um you know more sort options and then we could just say descending of sum of amount so you could see that vendor 85 has $14,000 $ 157 uh as the due vendor 103 has $6,000 vendor 101 has $5,000 vendor 66 has $5,000 so these are the vendors who uh who needs to give was a lot of money and uh you might as well end this video go ahead and call them to get that money okay so this is how pivot tables can quickly calculate the total and also give us the flexibility of uh sorting or filtering based on what we are looking for like okay so I hope you enjoyed that this little technique uh I know this is a short video but again I wanted to demonstrate how powerful PIV tables can be and how sometimes we overlook them just like I did when I was creating the suip formula uh although it occurred to me when I when I read Christian's question in email that a pivot table would be a better way to solve this uh where I eventually set out to do it in Excel I went with formulas because that's the natural tendency for most of us analysts we want to uh write a formula and calculate the total right it's it's just out there so you you naturally go towards that direction uh but sometimes a pivot table approach is incredibly easy and uh and really powerful like like in this case here so I hope you have enjoyed this this example go ahead and play with the data and uh you know uh a sincere suggestion to you is try out P tables more often than uh you usually do because they can save you a lot of time and they can let you focus on the important task which is in this particular scenario uh chasing the vendors who need to give us money and then collecting it from them so thank you so much stay awesome and I'll see you in another video bye-bye