Using IDEA's Visual Connector to detect fall in Excise Duty Collection

Hi,

IDEA's Visual Connector can be used to detect specific Item Codes where there is a sudden fall in Excise Duty levies in any given month.

 Following steps may be undertaken within IDEA -

 a. Obtain the Outward Register containing the Item Code, Item Description, Date, Location, Quantity, Unit Rate, Basic Value, Assessable Value, Excise Duty, Chapter Heading

b. Append a virtual numeric field titled 'Month' using criteria @month(Date)

c. Perform a Summarization with Fields to Group On being Item Code, Item Description and Month and Numeric Fields to Total On being Excise Duty

d. The resultant file arising out of the Summarization will have the Item Code, Description, Month,Total Excise Duty

e. Now apply a Key Value Extraction on Month to split the summarization database month wise.

f. Link all the monthly summarization databases using Visual Connector on the matching field Item Code

g. The resultant database will contain the Item Code with the Total Excise Duty levied per month.

h. Now convert this database into a chart by taking the Item Code to the X-Axis and Total Excise Duty Levied to the Y-Axis

i. The resultant chart will present a trend of Total Excise Duty Levied per month item wise.

Now a quick pictorial look up of the Bar Chart will help identify specific months where the Excise Duty levied has fallen significantly in comparison to other months.

The chart allows for a drill down into the individual sale entries for the month having low Excise Duty levies plus the display can be extracted to a separate file.

Once in a separate file a number of interesting tests can be applied like -

- Duplicate Key Exclusion - unit rate manipulation - same item same month different unit rate

- Join - transactions will related parties

Kind Regards

Jairam