Collaborate on Invoice Reconciliation Template Excel for it with Ease Using airSlate SignNow

Invoice reconciliation template excel for IT

in this video i'll show you how to use our power reconcile add-in to get your reconcile report smartly as a financial specialist have you spent tons of time reconciling accounts every month have you wondered if the reconcile job can be done as easily as 1 2 3 and you can get a report within a minute here i want to introduce a magic tool for you that is power reconcile add in for microsoft excel let's walk through an example of vendor statement reconciliation we have received an account statement from a vendor called contoso and we also exported contoso's account details from our ap system now we'll use our magic power reconcile add-in to match the invoices between these two lists and find out the discrepancies click on this icon to open the power reconcile add-in for microsoft excel click sign in you can log in with any of the available options in this case i will use my google email account after login the main menu shows up let's start by assigning the whole contoso statement as data table a including the header the selection will be displayed underneath then we can pick the invoice number column as the reference and the open balance column is the amount now in version 2.0.0.5 and later we have more options to customize our report the first option is reverse sign sometimes the amounts in the original tables are expressed with opposite signs then this option can easily convert the sign to the direction you prefer we also have another option of additional column to include this feature allows us to add more useful fields in our final detailed report to show more information in this example we can include the invoice dates from both data tables for our reference next follow the same steps to set up data table b firstly assign a p account details as the data table b the selection will be displayed underneath as well then pick the column invoice number as the reference and the column invoice amount as the amount we can also include the invoice date as an additional column for our reference now all the required fields have been completed the report button is enabled just one click on this button within seconds a beautiful reconcile report will be generated in a separate spreadsheet in the same workbook the whole reconciliation process which might have taken you hours before now only took you a couple of seconds if you want to change the range in columns you can do it at any time simply repeat the above steps and click the report button again a new report will be generated without touching the previous ones now let's have a closer look at the report in this report you can find the matching status we summarize our findings into four categories perfect match half match data table a only and data table b only there are detailed records listed below the summary you may use the slicer to filter each or multiple categories so that you can easily dig into the details 1. perfect match both reference and value amounts are perfectly matched in this case it means both the invoice numbers and invoice amounts are exactly matched 2. half match only references are matched which means the value amounts are different between vendor statement and ap account 3. data table only there are no records matched in the data table b these missing records could be outstanding invoices which are not entered into the system yet 4. data table b only there are no records matched in the data table a there could be typos in the invoice numbers that's why they cannot find any matched records in the vendor statement in our detailed report you might notice there is a new field called group id group id was introduced in our latest version to help to identify the group of matched items how to identify it consists of the index numbers of all the matched items in the same group it also starts with the letter a or m which means automatically match or manually match what do i mean manually match let's find out together to improve the reconciliation result we're providing the most popular function called manual reconcile after checking the detailed report you might find that some items are not matched automatically just due to a slight variance in the references for example in our case we can tell the reference inv-24972 in data table a and reference inv24972 in data table b are actually the same invoice so they should be matched now we can click the button manual reconcile a new window pops up here by default we have lists of the items not perfectly matched in both data tables just go ahead and pick the items that should be matched from the lists if the list is too long the filter box is a good tool to easily find the items you're looking for sometimes we may match more than two items the total amount of the items we picked is displayed at the bottom once the selection is completed just click match our smart machine will reassign a new group id starts with m for the items you just matched isn't it easy you can continue to match more items but don't forget to click save after all are done your report will be refreshed and all the changes you made in the manual reconcile screen will be applied did you notice the percentage of perfect match has been increased from 68 to 71 that's fun to match as many items as you can and keep improving the matching percentage as you just witnessed with the power reconcile add in the whole reconciling process takes just seconds even when handling large volume transactions this add-in performs fast as well let's see what will happen when there are thousands of records i opened a new workbook there are 12 000 records in the contoso statement this time and a similar number of records in the ap account sheet so let's repeat the steps [Music] for around 10 seconds the report was generated successfully using power reconcile add-in can save lots of time compared with matching manually on papers it also greatly improves accuracy try it now or get more information at our website powerreconcile.tech you