Collaborate on Invoice Reconciliation Template Excel with Ease Using airSlate SignNow

Invoice reconciliation template excel

hi guys welcome back to my channel today in this video we will learn how to prepare the render reconciliation statement in excel spreadsheet so before i start to prepare the vendor reconciliation statement i would like to tell you some additional information related to this all right so basically there are the five steps for reconciling any statement like when the reconciliation statement the step starts with the bringing the statements and ending with calculating the closing balance of the second statement that is we have to follow these five steps to complete this any reconciliation statement all right that is we have to follow the bringing the state ministry then after the deciding the first statement to follow identifying the closing balance of the first statement existing unique duplicate or any error transaction in the form of 8 or less and finally calculating the closing balance of the second statement similarly if i talk about the dividend credit rule or in the form of 8 or less in this you know statement we can see we can do it like this if the dater or vendor book balance is given then the calculation should be based on the greater book balance similarly if the creators or other book balance is given then the calculation should be based on the data book balance all right you can see under the both statement we have you know the two condition that is if the profit in dated books or other work then we have to add that is loss in the credit score or customer book similarly if the loss in the data books or other book then list should be done that is profit in the traders or customer group all right and in this you know statement the rule are same but similarly under this statement if the profit in the creditors work then we have to add that is loss in the dated books and if the loss in the creators book then we have to list that is profit in the data box all right and we have some additional two nodes the node number first is under the vendor reconciliation statement we maintain the creditors account second note is if the first statement is start with the customer or creators book then it will end with vendor book or data book similarly if the first statement is start with the vendor book then it will end with the customer book all right okay so now let's start to repeat the vendor reconciliation statement by taking an example all right here here we have a two statement that is the dated accounts and creditors account the data account is prepaid by isd company limited which is our supplier right and the creators account is prepared by the abc company limited which is our company and this company is going to prepare the vendor reconciliation statement all right now the reconciliation statement is done after existing the unique duplicate or any error transactions in this you know statement the vendor reconciliation statement can be prepared by identifying the unique duplicate or any error transactions from these two statements and then we will adjust in this statement by using this format all right so under this statement we have two sections that is the eight or less sections in this sections we will put the adjustable transactions right and we have a two you know gap here that is this one and this one here we will you know put the closing balance of the first statement whichever we will you know select it is depend on our choice all right and here we will you know calculate the closing balance of the second statement that is opposite to this statement all right which will be calculated here all right so first click here and i'm going to select this statement as a first statement and put the closing balance from this statement k all right so first click here and type the you know statement like balance as per vendor broke and i press the tab and now click here and put the balance this one that is three love fifteen thousand rupees and now press enter key all right now click here and write the statement balance as per our book okay now click here and here we will calculate the balance after adjusting these two sections all right now let's start to identify the unique duplicate or any error transaction from this statement all right we have found the one unique transaction in this statement and three unique transaction in this statement you can see and the transactions can be identified by you know matching the invoice number and the transactions with these two statements the identification process will start with the opening balance and ends with the latest transactions of the company all right okay you can see all right here and here all right so now let's adjust the identified transactions in this statement by you know reviewing their transactions nature all right so first i'm going to you know start with this statement so in this statement we have these transactions all right which is good sold by 60 000 rupees at this you know invoice number and this transaction is not recorded in the creator's account right so based on the other rules we have you know started with the vendor book but the calculation should be based on the other book so this balance is you know reduces the balance of the creditors account which is the loss or creditors account so that this transaction should be put under the less section all right so click click here and now i'm going to type the transaction goods are sold but not recorded in the creditors book and put the invoice number rst 75 and now press the tab and now put the balance 60 000 rupees all right and now press the enter key okay now we are done from this you know statement now i'm i'm going to move in this statement and in this assignment we have this one right that is the paid through check number zero nine two zero two one of rst has seven five invoice number that is the payment is made of these transactions that is uh this one all right which is not recorded in the data store right so based on our rules this balance you know reduces the balance of the creator's account but the calculation should be based on this one so both condition are matched so based on the nature of these transactions we have to put this transaction under the list section all right so click here and type the statement payments are made but not credited in the chapter book and now put the you know reference number rest hash 75 and now press the tab and now put the balance to lack rupees and i'll press the enter key all right similarly we have the next transaction is goods purchased of 30 000 rupees all right so this transaction is not recorded in this you know statement that means this balance is increases the balance of the creditors account which is you know the profit of this you know statement and ing to our rule we have to follow this statement for a calculation because we have started with the data box all right so based on this oh we have you know gain the profit so it should be you know put under the edge section so click here and you know type the statement goods are purchased but not recorded in the data board and put the reference number rst hash 729 and i'll press the tab and i put the balance thirty thousand rupees and i'll press the enter key now similarly where the last one is you know good's return of rst has seven three zero that is this transaction has been returned by 15 000 rupees right which is not shown in this you know statement that is the data account these transactions you know reduces the balances of the creditors account which is you know loss or you know expenses of this unit statement and ing to our rule the calculation should be based on this statement so this transaction should be put under the list section all right so here i'm going to type goods are returned but not recorded in the data book and now put the reference number rst 730 and operate the tab and i put the balance 15 000 rupees and now press the enter key all right now click here and now let's you know add these ranges all right so for this press the equal to sign tap the sum function now press the tab and now select the range from here to here and now press the close parenthesis and now press enter all right similarly for this one click here and now let's add this you know ranges all right so for this press the equal to sign tap the sum function now press the tab and now select the range from here to here and operate the close parenthesis and now before press the enter key click here and put the negative sign here to show the balance in the negative form all right and now press the enter key now for balances for our book it can be you know calculated by adding all these three value okay so for this press the equal to sign now time now type the sum function now press the tab and now select the uh you know value this comma this comma this and operate the close parenthesis and now press the enter all right you can see the seventy thousand rupees as per you know our book which is showing here that means our calculation or preparation as correct all right let me tell you one thing here the balance as per any book or any statement must be matched with the you know physical statement right so ing to you know preparation the balance is you know matched with this statement all right if the statement will start with the other book the closes with vendor book all right so ing to this we can see this balance showing this one and this balance showing this one all right so guys it's a very easy to calculate it that's all for now thanks for watching this video if you have any query related to this please ask me in the comment box below and if you find it useful please hit the like button and don't forget to subscribe to this channel see you in the next video signing off