Buddy Functions in IDEA

Brian Element

Forums

Hi,

 

While IDEA Data Analysis Software is replete with a bevy of top notch features and functionalities, there are few features which I would like to call 'Buddy Functions' in IDEA which go well together in meeting the analytical purpose on hand.

 

Let us take a look at some of these 'Buddy Functions' over a few posts.

 

(a) Top Records Extraction and Aging

 

Top Records Extraction falls under the Extract category in the IDEA menu toolbar while Aging falls under the Categorize group in the IDEA menu toolbar.

 

Top Records Extraction allows the IDEA user to extract the last known incident of an event from a group like the last instalment credit date on a credit facility.

 

Aging when applied to the Top Records child file allows the user to profile the records into buckets based on the last known incident of an event from a group like aging the last instalment credit date against a credit facility into 0-30, 31-60....180 and more. Its the 180 and more which would feature in our list of red flagged borrower classification.

 

In our next post we will take a look at Duplicate Key Exclusion and Summarization

 

Kind Regards

 

Jairam

 

Brian Element Sat, 11/21/2015 - 06:38

A follow up post from Jairam

Hi,

Continuing with the 'Buddy Functions' post, Duplicate Key Exclusion and Summarization are two features in IDEA which complement one another into delivery of insightful analytics.
 
Take an example of Payroll. Duplicate Key Exclusion can be applied to check whether difference '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. But the list will need to be presented more tactfully to represent maximum and minimum variations per  'Unit', 'Department', 'Grade', 'Designation' and 'Location'.
 
To glean the maximum and minimum variations, Summarization can be applied on the Duplicate Key Exclusion database to present the maximum and minimum  'City Conveyance Allowance' payments per  'Unit', 'Department', 'Grade', 'Designation' and 'Location'.
 
The same logic can be applied in 'Procurement' to study purchase rate variations and 'Sales' to monitor selling price variations.

Brian Element Mon, 11/30/2015 - 05:31

Another follow-up from Jairam.

Hi,

 
Continuing with the 'Buddy Functions' post once again, Summarization and Pivot Table are two features in IDEA which complement one another very nicely.
 
Let us refer to a case study of trend analysis of procurement prices across material codes.
 
Summarization can be applied at the first level to arrive at material wise average net price of items procured in a given month. Here the fields to summarize will be Material Code, then Month and the numeric fields to total will be Net Price. Choose statistics to display as Average.
 
Based on the summarization database generated apply a Pivot Table. In the 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 material wise across months.
 
The same logic can also be extended to any form of trend analysis like expense trend, sales price trend and more.

Brian Element Tue, 12/01/2015 - 12:43

Hi,

Field Statistics and Stratification are two features in IDEA which can be applied in tandem to study distribution of values and outliers for numerical data sets in any active database.

Field Statistics generate 'Minimum', 'Maximum' and 'Average' statistics for numerical fields in any active database.

Take an example of 'Minimum', 'Maximum' and 'Average' statistics for 'Transaction Amount' in a General Ledger. The Field Statistics reveal a 'Minimum' of Rs. 5,000 an 'Average' of Rs. 45,000 and a 'Maximum' of Rs. 12,50,000

Using these statistics, an IDEA user can apply Stratification to create intervals/buckets of 5000-50000, 50000-95000 and 95000 +

The subjective intervals in the Stratification are based on the logic of the 'Average' being Rs. 45,000. Using the 'Average' of Rs. 45,000 intervals are created from 5000 to 50000, 50000 to 95000 and 95000 + (all having uniform stratum intervals of Rs. 45,000)

** please note you can also apply variable intervals.

The Stratification result reveals an ABC-analysis (High-Medium-Low Pareto Analysis) of 'Transaction Amount' values in the General Ledger.

Kind Regards

Jairam