Reconcile Difference & Generate Reconciliation Report

Reconcile Differences & Generate Reconciliation Report

Here we will understand how we can automate reconciliation process in few simple clicks! This feature freely allows you to reconcile data sets based on criteria which you feel appropriate in the available data sets.

A. STEPS FOR RECONCILIATION REPORT:

We will consider below example of SUMMARY REPORT for understanding steps of reconciliation:

When you click Reconcile button in Re-generate Report, a new window (2nd screenshot below) will open on the right side of your google sheet. We will reconcile single common Identifier 24XXXXX2524X1ZX” in our example

Let us understand the steps to be performed for getting optimum results:

a) Range 1: This will be the same data range which was selected in preparing summary report. This is non-editable and is available only for viewing purpose

b) Reference column: This is the most important step in entire process of Reconciliation Sutra. By default, 1st row of data selection will be considered as headings. Select the reference column from dropdown.

In our above example, we have considered “Invoice no” as our reference for Range 1.

Reconciliation report will be based on this selection. As the name suggests, this will be used as reference i.e. the basis of reconciliation. Hence you should select the column which has some common values to compare between both the data sets for the respective Identifier.

Note: Please note that “Reference” dropdown will not display column which has been used as “Identifier” in Step 1. This is because Identifier column is already used in generating Summary report and logically there should be some other reference within the data of Identifier values which should be considered for reconciliation.

Tip: To keep it simple, Reconciliation Sutra provides only 1 reference column for reconciliation purpose. If you need to consider multiple values for reference purpose, you can simply concatenate the same in your original data and consider that as the reference column for reconciliation purpose.

C) Additional Columns: We also have option of “Additional Column” to include. This feature allows us to add more useful fields in our final report to show more information. This will be provided only as a part of additional information in the report. This will not be used as a part of comparison or reference for reconciliation purpose.

We have used “Invoice Date” as additional column 1 in our example

Info: You can add maximum 2 additional columns. We do not want the end report to look shabby with too much information and hence this restriction.

Also as a part of better presentation, additional column 1 & 2 should be different selections. Our tool will stop you to proceed further if same selections are made.

Next, follow the same selections for Range 2

Note: Please note you will be allowed to proceed only when selections which are made in Range 1 are also made in Range 2

D) Nominal Difference: There may be cases where you may not require to look into minor differences. This is very subjective and based on your reconciliation purpose. Hence we have provided an optional field wherein you can enter the amount which you think that differences upto some minor amounts are acceptable and no need to look into further.

All differences upto this amount will be considered as “Matched” in the Reconciliation Report. Reconciliation report will also display this amount as a part of its reporting. We will enter 1 as “Nominal Difference” in our below example

Info: You can leave this field as blank if you do not want to consider Nominal Difference in your reconciliation.

Note: Only amounts in the range from 1 to 1000 are allowed to be entered since this is an optional field provided to avoid you from looking into nominal or minor differences. Also amount entered is inclusive. For e.g. if you enter 100 as nominal difference then differences upto 100 will be considered as “Matched”. Negative amounts and decimals are not allowed.

Remember: “Nominal Difference” field in this case will be enabled only if Reference column in both the ranges will be selected. This is because unlike in step 1, all selections (Reference/Additional column 1/Additional column 2) are not mandatory. You can also proceed without making any selections. In such case, our tool will only compare amounts in both the data for reconciliation purpose. Refer this tutorial for detailed explanation.

Now, since all the selections are made, just click the “Run Report” button.

That’s it! You will have your final “RECONCILIATION REPORT” ready in a separate sheet in the same google sheet. Depending on your selections (Individual Reconcile/Reconcile All /Reconcile Difference) individual sheets will be created for each common identifier.

B) Understanding Reconciliation Report:

Here is the resulting Reconciliation Report.

In this report, you will see:

“RECONCILIATION REPORT” as the heading,
Name of the common identifier below it (24XXXXX2524X1ZX)
Nominal Difference = 1 below common identifier value.
New sheet created within the same file and named as (24XXXXX2524X1ZX) which is Common Identifier in Summary report.

In our example we have reconciled only single common Identifier “24XXXXX2524X1ZX” and hence only single sheet has been created.

If you select

“Reconcile Difference”, then all items which has difference in Remarks column will be reconciled and individual sheets for each item will be created within the same google sheet and will be given same name as common identifier.
“Reconcile All”, then all items (Difference & Matched) will be reconciled and individual sheets for each item will be created within the same google sheet and will be given same name as common identifier.

Info: Only 200 sheets in total can be created in single google sheet. This is because google allows only 200 sheets in single workbook. But do not worry, if you have large data containing more than 200 values, reconciliation sutra will automatically create new google sheet in your google drive named as “Reconcile_Time stamp” and other files will be saved there.

Remember (VERY IMPORTANT): As discussed in our earlier tutorials, “Re-generate Summary Report” is available only for 2 hours from creation of Summary report. Hence you are advised to complete the Reconciliation process within 2 hours after creation of Summary report. But do not worry if you cannot complete within 2 hours. You can again generate a new summary report and start your reconciliation process.

However if you have very large data and you select “Reconcile All/Reconcile difference” then “Reconciliation Sutra” will process results only upto 2 hours of creation of summary report and will stop thereafter. This is because keeping this available for more than 2 hours creates performance issue (technical issues beyond our control), hence we have limited this to 2 hours to maintain optimum performance level of our tool.

Caution: Please do not close the loader once you click “Run Report”. If you do so, you may not get desired result.

Also please do not delete and do not rename “Summary report” sheet. If it gets deleted or renamed before Reconciliation process gets completed, then you may not get desired output. This is because our tool considers “Summary Report” as the base for processing reconciliation reports. Also since summary report is a completely automated result, we advise not to change any data in the summary report generated, else it may not give desired results.

Also please do not delete Source data sheets and do not rename any sheet names of Source data after generating summary report, else it may not give desired result. Reconciliation Sutra will notify you when you initiate Reconciliation process that source data sheet is missing if it has been renamed or deleted.

Now, let’s have a closer look at the “RECONCILIATION REPORT” and understand how our tool generated it

Well, it is fairly simple. Take a look at below screenshot from our example for ease of understanding:

As you can see above, 1st column in Additional column, Reference column and Amount represents data from Source 1 (as highlighted in above screenshot). Reconciliation report will display exact headings as it exists in original source data (Invoice Date, Invoice No and Tax in our example).
Similarly 2nd column in Additional column, Reference column and Amount represents data from Source 2 (as highlighted in below screenshot).

With the help of reference column you select, reconciliation sutra will consider each and every item from Reference column (In our example, it is Invoice no) from both the data sets and sum it up against the corresponding Amount column.
Below screenshot will give us better understanding:

As it can be seen in our example above, Sum of Invoice number: 389 is done from Source 1 & Source 2 data respectively. If some invoice number is missing then it will consider zero in other column.

Subject to nominal amount adjustments:

if amounts are matching, Remarks = “Matched”,

If amounts are not matching, Remarks =“Difference”

The whole reconciliation process, which might have taken hours before, now only took some time with few clicks. If you want to repeat the same, just follow above steps and click the “Run Report” button, new Reconciliation reports will get generated.

Note: By repeating the steps, data in your existing sheets will be replaced/updated with new data if your google sheets already contains sheets with same name.

C) What happens after Reconciliation Report?

Once Reconciliation report is generated, Reconciliation Sutra will insert hyperlinks in summary report under “Link” column. If you have observed earlier, “Link” column was blank before reconciliation report was run.
“Link” column before running Reconciliation report

“Link” column after running Reconciliation report. If you click on the hyperlink, you will be navigated to the respective Reconciliation report.

Remember: Please do not edit or delete the formula which is applied in the link column else it may not work properly

+91 63 5184 7486
‎+91 79 3580 3261

support@sutraanalytics.com

10:00 AM To 7:00 PM
Monday To Friday

607, Link, 100 Feet Rd, opposite Shree Vishnudhara Gardens, Gota, Ahmedabad, Gujarat 382481