Two Way Match in IDEA Vs MS-Excel

 

The classic two way match is a valuable analytic benefit while working with any numerical comparison in financial and operational reviews.

 

Let us take a commonly encountered situation of a two way match between the Trial Balances for two accounting periods (let's say two quarters). This is done to observe and study material deviations in Group Head of Accounts.

 

Needless to say the Trial Balances have the Group Head of Account with the detailed Head of Accounts. So as an illustration the Group Head Administration Expenses will have detailed Head of Accounts like Utilities, Rent, Telephone, Internet and other related Administrative Expenses.

 

Now if a compare needs to be done between Quarter 1 (Q1) and Quarter 2 (Q2) Administration Expenses but at a Group Level, the common method in MS-Excel is to first pivot the data for Q1 and Q2 on Group Head of Account to get summary of transactions per Group Head of Accounts and then to V LookUp the two Pivots to bring the total transactions per Group Head of Account one next to the other for match and difference calculation between Q1 and Q2. To sum up this takes three steps in MS-Excel.

 

This can be done in a single step in IDEA using Compare. Open the Trial Balance for Q1. Click on Compare. In the Compare dialog box notice the Trial Balance for Q1 is the Primary Database. Now select the Trial Balance for Q2 as the Secondary Database.Click on Match and select the Group Head of Accounts as the matching key field in both the Primary and Secondary Database. For the numeric field to compare you can choose the Debit Account first as the field to compare.

 

The result will get you the Group Head of Account, Count of Detailed Head of Accounts in the Trial Balance for Q1, Debit Total for Q1, Count of Detailed Head of Accounts in the Trial Balance for Q2, Debit Total for Q2 and Difference.

 

This makes the two way compare both efficient and effective within IDEA.

 

You can apply this to other common two way matches like Budget Vs Actuals, Standard Process Output as per Bill of Material (BOM) Vs Actual Process Output - Yield Analysis, Standard Batch Costing for Actual Output Vs Actual Batch Costing and much more.

 

Regards

 

Group Admin Team