- All assets, liabilities, and equity interests have been recorded and disclosed correctly.
- Confirm the existence of assets, liabilities, and equity.
- Valuation of Assets, liabilities, and equity are reported at the correct amount.
- Transactions and events are recorded in the correct accounting period.
- Key stakeholders who may be needed to sign off on the relevant data collection.
- Key contact who the Auditor will work with to identify the information that the audit requires.
- An individual who will perform the data extraction. This could be a member of staff who works within the business process itself or depending on the size of the organization may be a member of the IT department/database administrator.
Microsoft Excel is one of the most common file types used and can be imported into IDEA.
There are some specific rules about the structure that the data must be stored within the Excel file. For example, there must be:
- No blank rows or columns
- No title rows
- No sub-totals or grand-totalsThis means that the data must be a straight extract from the system without any grouping or formatting applied. The Auditor must be aware that reports in a print to view/paper format can be output to an Excel file format and will most likely require manual modification to remove groupings, blank lines, subtotals and grand totals which in itself is open to potential mistakes. One reason that the Microsoft Excel format should not potentially be the first considered format, is due to the fact that depending on the quality of the reporting software all values will be written into the Excel file in the 'General' format. The potential impact being that reference numbers will be treated as numbers and leading zeros will be removed. Specific examples of this would be telephone numbers and bank sort codes and account numbers. Should the Auditor want to compare these to other datasets, this could lead to valid matches not being identified.
Plain Text Files
Plain text files are commonly used to transfer large files between companies and systems. A plain text file is a file storing tabular data in plain text.
There are two main types of plain text files that IDEA can import:
Common file extensions include .txt, and .fxd.
They are known as fixed length due to the fact that each record in the file is exactly the same amount of characters and that the fields contained within the records always start in the same character position.
One of the most common types of delimited files are CSV (Comma Separated Value) files. Other common file extensions include .asc, and .del.
Unlike fixed-length files that use spaces to ensure that fields start in the same position, delimited files use special characters to define where fields start and end and is the most efficient type of text file, due to the fact that the records are only as long as the values contained within them.
It is important to note that delimited text files can sometimes be exported from core systems configured to use different delimiters. Examples include tab, colon, semicolon, and space. The choice of delimiter can, in some cases, be critical as common delimiters can also exist within the actual data itself, for example, commas can also be used in names and addresses.
If the option to select your own delimiter is available, we recommend selecting pipe (|), tilde (~), or not sign (¬). In essence, any selected delimiter must not exist within any of the records other than being used as a delimiter.
Another option sometimes available is the inclusion of text encapsulators, the most common being quote marks (""). By wrapping encapsulators around text values any field delimiters that occur between the encapsulators are ignored (e.g., "Smith, John", "12, High Street",).
Whether it be a delimited or fixed length file that is chosen, it is advised to obtain technical information about the file's record layout. Key information which will assist when importing these files include:
- Field Name (If not already included within the files themselves)
- Field Type (Numeric, date, time, character)
- Delimiter (Delimited file only)
- Encapsulator (Delimited file only)
- Field length (Fixed length files only)
- Number of decimals
- Thousands separator and decimal point characters.
Print reports are probably one of the easiest file types to obtain as nearly all systems have the ability to produce printed reports and are simply an electronic version of a printed document and often contains extra formatting and information. The additional formatting is used to make the printed document as clear as possible for the reader.
IDEA has the capability to import print reports using a built-in tool, Report Reader, and can extract the required data for import from the file. Importing a print report again allows for a greater degree of control, as it allows the user to select and import the fields required for analysis, add in field descriptions and amend field types prior to import.
Bear in mind that although this is a powerful functionality that allows the Auditor to convert print to view reports into structured data for easy analysis, this file type should only be considered if less time-consuming options are not available.
For more guidance on the process of importing data into IDEA, refer to the IDEA and Report Reader Tutorials located in the IDEA documentation folder. More information and guidance can also be found using the IDEA Help function (F1). Also note that if you have access to CaseWare Passport support website further information, tutorials, and videos are also available.Step 6: Determine How the Data Will Be Transported Careful consideration also needs to be given to how and when the Auditor will receive the required data. Ideally, all of the pre-preparation and importation of the data at a minimum will have taken place prior to the Auditor going on-site with the client. The most efficient way of achieving this will be to use a web-based file hosting service (OneDrive, Dropbox etc..), or the CaseWare Cloud 'queries' function. The Auditor will need to ensure that the chosen service can demonstrate appropriate levels of security to satisfy clients legal requirements and redundancy against loss of data. To reduce upload times of the data and to add further levels of security, it is recommended that the client converts the data into a compressed format (.zip, .rar, .tar) and adds a secure password. Although using web-based file hosting services are the most efficient way of sharing data, some clients will only allow the sharing of data to take place physically when the Auditor is on-site. As previously mentioned, this is not ideal as the Auditor should have already pre-prepared the data and also gained some insights to assist the audit. In this case it is worth considering, depending on the size of the client, physically collecting the data prior to the start of the audit. This will add additional cost to the audit and should only be considered if the data collected will be vast or complex in nature, or the value of audit will allow. In addition to the above points, some clients may restrict the use of their data to only allow analysis to take place internally using their own IT equipment. This may have an impact on the audit, due to the pre-preparation process of data, and should be considered and factored in as part of the audit programme. Step 7: Request and Verify Provided Data The final step is to request the data from the key contacts or individuals identified in Step 3. The data can then be provided to the Auditor for verification and analysis. An important part of the import process is to verify totals prior to any testing. Some file formats such as Microsoft Excel allows the Auditor to compare totals in the files provided by the client with its IDEA equivalent. This is assuming that the data extracted is correct. So, for completeness and accuracy control totals should be provided by the client based on information available within the user interface of the accounting software or ERP system. It is up to the Auditor whether a screenshot or a physical confirmation (Auditor views the totals when on-site with the client) is applicable.
ConclusionIt is important to note that regardless of the type of audit being completed an accurate and efficient data collection process is essential to maintaining the integrity and timeline of the audit or analysis. Understanding the data, the formats available, the data sources and any data restrictions in place can reduce the likelihood of errors occurring. A data collection process is essential to ensure that the data gathered is both well-defined and accurate. This provides confidence that the assurances tested are valid and true. For more information check out our Analytics product offering
MORE RESOURCES ARE AVAILABLE HERE
- Recorded webinar Using Analytics in Audit – With Examples
- Whitepaper - Is Better Planning the Key to an Efficient Audit
- Product page information, with ebook publication "Analytics for External Auditors",