Reconciliation Sutra Full Tutorial - Sutra Analytics | Data Analytics & Web Design Experts

Reconciliation Sutra - Detailed Notes

With Reconciliation Sutra, automate reconciliation of two data sets in Google Sheets on few clicks. Effortless, timesaving, accurate accounts reconciliation process. Perfect tool for smart accountants and without knowing any coding skills:

STEP 1: Select data range & prepare Summary Report:

Summary report is the initial comparison report showing differences between two sets of data based on parameters selected.
Prepare Summary report by selecting Menu > Reconciliation Sutra > Generate Summary Report

Once you click “Summary Report”, a new window will open. (Refer below screenshot)

Follow below steps to prepare Summary Report

1) SOURCE 1: This is the 1st data set of the two data sets which you want to compare. We have named this as “Source 1”

i) Range: First Select the Data range of Source 1 and then click “Select”

Caution: Data Selection should not contain any merged cells. If it does, our tool will stop you before proceeding. This is a check within our tool to avoid any inconsistencies in final output due to merged cells.

Also please note that all headings (default 1st row) should be unique. This is also a check within our tool to avoid any inconsistencies in final output due to common headings

Note: Please note that you will have to select the data range first and then click on select. Clicking on “Select” first will not allow you to select range, but it will consider range of data already selected.

ii) Identifier: Select Identifier column from dropdown:

By default, 1st row of data selection will be considered as headings. This dropdown will show all the headings from your data selection
Identifier column represents common identifier between 2 data sets, i.e. columns having common values in both the data which is required for comparison purpose. Hence you should select column which contains some common comparable values in both the data.

This is important since Summary report and other further processing will be based on selections done here.

Info: Please be careful if you are using date as your common identifier. In that case please make sure dates in both the data sets have same formatting else it may not give desired output.

iii) Amount: This dropdown will only show headings which has numerical values. Select amount column from dropdown:

This is important since amount for Summary report and other further processing will be based on selections done here.

Remember: As a part of control check in our tool, we do not allow Identifier and amount column selections to be same. This is because final output will be vague and not serve purpose of reconciliation.

2) SOURCE 2: This is the 2nd data set of the two data sets which you want to compare. We have named this as “Source 2”

All selections in Source 2 will have to be made on same lines as in Source 1. Also please note you will be allowed to proceed only when all selections are made since all are mandatory selections.

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 Summary Report. Summary report will also display this amount as a part of its reporting.

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 eg if you enter 100 as nominal difference then differences upto 100 will be considered as “Matched”. Negative amounts and decimals are not allowed.

Below is the result of “SUMMARY REPORT” where Nominal Difference value is displayed and even if there is difference of 100, it is considered as “Matched”

Click “SUBMIT” button for Summary Report generation
Once you click “Submit” button, below SUMMARY REPORT will be generated. This contains all the relevant information presented in a simple and effective way, making it easier for the user to get perfect overview of the data sets.

Let us understand what information “SUMMARY REPORT” contains:

1) Common Identifier: This is the Identifier column which you selected as discussed in earlier tutorial. This is the column which contains common values in both the data sets.

2) Amount: These are the amount column which you selected. This will be for both data sets. Our tool will sum up the values against each common identifier and present it against the respective identifier.

3) Difference: As the name suggests, this represents difference between amounts in Source 1 and Source 2.

4) Remarks: If amounts are matching then Remarks will be “Matched”, else “Difference” (subject to optional nominal amount adjustments mentioned below the heading)

5) Action: This is an awesome feature which Reconciliation Sutra has to offer. Click on “Reconcile” button to reconcile differences against items where there are differences.

This way you can automate reconciliation on few clicks. We have discussed this in detailed in our RECONCILE DIFFERENCE tutorial.

Tip: You can reconcile all items which has differences OR all items (matched & difference) in just one click. Just scroll down to end of the report where you will find this option.Write your text here…

Reconciliation Sutra offers 2 options other than individual “Reconcile” (refer screenshot Above). Choose the one that’s appropriate for you:

Reconcile All – If you want to Reconcile all items i.e. Matched and Difference both , click “ Reconcile All”
Reconcile Difference – If you want to Reconcile all items which has differences , click “Reconcile Difference”

Note: While generating Summary report, Reconciliation Sutra will remove case sensitivity from the values in Common Identifier. It will convert all values into BLOCK LETTERS and paste it. This will be helpful so that even if both data set contains values differently, it will still compare and present corresponding amounts correctly. It will also remove spaces present before or after the value (if any). It will not remove spaces in between of the values.

SUMMARY REPORT WITH PIE CHART & RECORD COUNT IN GOOGLE SHEETS:

To get a better overview of the reconciliation result, Reconciliation Sutra provides you the same “SUMMARY REPORT” plotted in google sheets. It also summarises the data into 2 categories – “Difference” & “Matched” alongwith total count of records against each category. It also provides same information presented in form of Pie Chart, making it super easy for the user to get quick overview of the reconciliation result.
Have a look at our super cool reconciliation result:

You would see an additional column “Link” in the summary report plotted. This will automatically get updated with hyperlinks when you run final reconciliation reports which we have discussed in this tutorial.

Info: Since this is a completely automated result, we advise not to change any data in the summary report generated else it may not give expected result.

Caution: Please do not rename or delete the Sheet “Summary Report”. Our tool requires this to be present with same name for performing multiple actions.

STEP 2: Re-Generate Summary Report:

Reconciliation Sutra provides you an additional option to re-generate summary report generated in step 1. This option will be available after you generate Summary Report in Step 1.
For Re-generating summary report Select Menu > Reconciliation Sutra > Re-Generate Summary Report

Once “Re-generate Summary Report” is clicked below window will open.

This is the same report which gets generated when you create summary report. You can choose appropriate option whether you want to Reconcile Individual item, All Difference items or All items.
We have discussed this in detail in our Reconcile Differences & Generate Reconciliation Report tutorial.

RECONCILE DIFFERENCES:

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.

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.

B) 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

c) 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.

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.

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

If your data is large and if “Reconcile Difference/Reconcile All” is selected, it may also take some time to update all the links under “Link” column

STEP 3: Export Output to excel:

Once you are done with entire reconciliation process, you may need to save individual “Reconciliation Reports” in excel for any purpose.
You can export your generated output by selecting Reconciliation Sutra> Export To Excel.

Once you click “Export To Excel”, a new folder will be created in your Google drive named as “Reconcile_Creation Date_Creation Time”. Below is the resulting screenshot of google drive after Export to excel is clicked:

Caution: Please do not delete or rename this folder until all the files are created inside this default folder.

Please note Reconciliation Sutra considers “Summary Report” as the base for saving excel files in the google drive folder. It will consider only those files which has hyperlinks inserted in the “Link” column in “Summary Report”. Hence please do not delete/rename Summary report and hyperlink under “Link” column, else files may not be exported to google drive as expected.

In our example file “24XXXXX2524X1ZX” is saved in google drive since it had link updated in summary report

This way Reconciliation Sutra helps you create individual excel files of generated “RECONCILIATION REPORTS” on a single click.
Once all the files are saved in google drive folder, a hyperlink to google drive will also be inserted in “Summary Report”. You can directly navigate to respective folder by clicking here.

Note: Though we term this functionality as “Export to excel” , our tool will by default save file in Google sheet format. Once you download it on your computer (individual file or entire folder), it will be saved in excel.

Info: Since files exported will be in excel, there will be some changes in the formatting of the same.

Tip: If you want to save entire file into single excel sheet, you can simply use Google sheet’s inbuilt feature of downloading file in excel. To do so Click File>Download>Microsoft Excel