IDEA Tech Tip: Pairing IDEA Functions for Deeper Analysis
Top Records Extraction and Aging
Top Records Extraction allows an IDEA user to extract the last-known incident of an event from a group of records (e.g., the last instalment credit date on a credit facility). Aging, when applied to the Top Records Extraction child file, allows the user to bucket the records based on age, like “0–30 days”, “31–60 days” and “180 days and more”. It is the “180 days and more” category that would be flagged for further investigation or placed in a specific borrower classification.
Duplicate Key Exclusion and Summarization
Consider payroll as an example: Duplicate Key Exclusion can check whether different ‘City Conveyance Allowance’ amounts are being applied in payroll payments to the same business unit, department, employee grade, designation and location. Here fields to match will be ‘Unit’, ‘Department’, ‘Grade’, ‘Designation’ and ‘Location’. Fields that must be different will be ‘City Conveyance Allowance’.
The Duplicate Key Exclusion will reveal a list of variations for ‘City Conveyance Allowance’ payments. To glean the maximum and minimum variations, Summarization can be applied on the Duplicate Key Exclusion database to present the top and bottom ‘City Conveyance Allowance’ payments per ‘Unit’, ‘Department’, ‘Grade’, ‘Designation’ and ‘Location’.
The same logic can be applied to ‘Procurement’ data to study purchase rate variations and ‘Sales’ to monitor selling price variations.
Summarization and Pivot Table
This pair of functions can be used to analyze trends. In this case, we will use these to analyze procurement price trends across material codes. Summarization is first applied at the average net price of items procured in a given month. Here the fields to summarize will be ‘Material Code’ and then ‘Month’. The numeric fields to total will be ‘Net Price’. Choose statistics to display as ‘Average’.
Based on the summarization database, apply a pivot table. Drag and drop the ‘Material Code’ in the pivot row, the ‘Month’ in the pivot column and the ‘Average Net Price’ in the pivot data. The output result will reveal a trend analysis of average monthly ‘Net Price’ of material across months. The same logic is applicable to any form of trend analysis, including expenses and sales price trends.
Field Statistics and Stratification
These two features work together to study distribution of values and outliers for numerical data sets in any active database. The Field Statistics function generates the ‘Minimum’, ‘Maximum’ and ‘Average’ statistics for numerical fields in any active database.
Using these statistics, an IDEA user can apply Stratification to create intervals or buckets. The intervals of the buckets are based on the logic of the ‘Average’. You can also apply variable intervals. The results from Stratification reveals an ABC-analysis (High-Medium-Low Pareto Analysis) of amounts. This IDEA pair is ideal for looking at ‘Transaction Amount’ values in general ledgers.
For more IDEA tech tips or to ask your own IDEA questions, visit IDEAScripting.com.
About Alain Soublière:
Alain Soublière has many years of experience working with computer audit software. He worked in a senior management role as the IDEA Product Manager for many years before becoming Director of Product strategy for CaseWare Analytics and more recently the Chief Product Strategist.
Connect: Alain Soublière