Importing MS-Excel Files having Sub-Totals and Grand-Totals

 

We came across a case recently where a MS-Excel file containing Sub-Totals and Grand-Totals was imported into IDEA by the client. The required Data Analysis (DA) was completed and at the final Reporting stage, the validation presented a vexing revelation. The DA results through IDEA was erroneous as the Total of the Excel file taken into IDEA contained Sub-Totals and Grand-Totals which inflated the Amount column in IDEA. The Net Total in IDEA was significantly higher than the real Total in the MS-Excel file. The client had failed to apply the mandatory Control Total checks upon import which resulted into incorrect reporting of DA results. (viz matching the Excel file total with the Trial Balance / GL Account Total)

 

This case once again brings to the fore the discussion on dealing with sanitized data within IDEA and also applying Control Total checks prior to DA.

 

Excel files with Sub-Totals and Grand-Totals are a common phenomenon and the clean up of the Excel file prior to import is a time consuming task and many a times defeats the purpose of using a specialised DA Tool like IDEA to expedite Audits and Analysis.

 

A prudent workaround would be to obtain the data as a Print Report / Text / PDF file, so that extraneous particulars like the Sub-Totals and Grand-Totals can be effectively and efficiently removed using IDEA's Report Reader. This is not only quick but also creates a template for repeatable import in a jiffy.

 

This case is particularly relevant to the Import of Unconverted (Text) files downloaded from ERP-SAP. An unconverted file when taken to MS-Excel requires significant time and effort for clean up plus the automation is not consistent. Using IDEA's Report Reader the clean up and automation can be achieved easily.

 

The Report Reader Tutorial.PDF file in the Documentation folder of IDEA on your PC is useful reference material for import of such files.

 

Best Regards

 

Group Admin Team