 |
Allen Computing and Business Analysis
|
Page Content
- Investigative Analysis
- Mimicking other Computer Systems
- Generating Complete Audit Trail Data
- Analysing Field Descriptions from old Databases
- Extracting Data from Difficult Sources
- Auditing
Related Pages
Audit and Investigative Analysis
ACBA's analytical investigation service specialises in the review of older non-windows based systems. Frequently these will have been built before the strict audit compliance provisions like the USA's Sarbanes-Oxley Act (SOX) had come into force.
MS Excel is used as the primary investigative and analytical tool combined with ACBA's own specialist tools. Where Excel cannot be employed directly by virtue of the limit on the number of lines, we hold the primary records in an Access database which has no such limit or analyse the data directly from a text file.
Investigative Analysis
Generally this involves extracting reports from the source systems (often as text based files) and converting them into a more suitable format for investigation. ACBA maintains very strict integrity controls over the conversion process and these are always available for direct review by the client.
While we are happy to undertake all kinds of analysis, ACBA (UK) LTD specialises in the the following
- mimicking systems processing
- generation of missing audit trails
- regeneration of old file descriptions
- data extraction from difficult sources
Mimicking Systems Processing
In this process ACBA uses the power of Excel's sophisticated formulae and its specialised programming language to replicate what the main system(s) under review are doing. Because the process is now under ACBA's control we can choose exactly how to report the results to give the best indication of possible errors or inconsistencies.
Typically this process highlights errors both big and small. However the apparently insignificant errors can cause great damage. The following example shows the input and output results of a file conversion process used within an automated interface between two major systems handling over £10 millions worth of transactions each year.
This demonstrates how the values in columns X and AA misinterpret the values of a quasi-hexadecimal representation of a negative figure ending in Q (column BA). The value which should end in 7 is treated as ending in 8 generating a one penny reconciliation error each time this condition is met. Such minor errors of interpretation can cause whole systems to crash.
Generating Audit Trails
In this example a local authority was claiming extra money from the UK Central Government on the basis of specific work carried out on fraud investigations. The work carried out was flagged up on their standard benefits system and the benefits system had duly generated an arithmetic summary of the case load to support their claim. However, there was no list of the individual cases on which the work had been carried out so the government auditors were not able to verify the specific activity and whether it justified the claim.
ACBA discussed directly with the software supplier how the underlying data was analysed in order to generate the summary. This was complex involving the absence of a series of combinations covering nearly 20 possible flags. While this would have been very costly to regenerate an audit listing report in the original software it quick and economic to do so in Excel.
The software supplier confirmed the reliability of the audit listing report through the comparison of a pivot table based on the case list and their sofware's original summary of the case load.
Regenerating Field Descriptions of old Records
Notwithstanding the sophistication of modern technology, many businesses still rely on old (in computing terms) systems for some of their most important transactions. Under perfect IT management, a full record of the whole system and all the detailed descriptions of the files, processes and detailed output reports would be retained in perpetuity. Only perfection rarely exists.
In this example ACBA regenerates the main elements of a report file generated in a system built in America in the early 1980s. The detailed field description for the report records has long since been mislaid. The regeneration is built on comparison with known elements of data from other sources.
The primary record is a 154 character string like
This is then broken down into segments that we are sure about and those we are not.
Our knowledge of the structure of this specific report is now sufficient to allow us to undertake reliable analyses on the basis of the known data.
Data Extraction from Difficult Sources
A difficult source is one which does not conform to a structure or style that can be imported easily into one of the primary business tools (a spreadsheet or database) for analysis. ACBA (UK) LTD has created a way of handling such sources so that you can get at the underlying data.
A typical example of a data source that does not import easily into Excel is the output from a web site log. These logs do not have either
- consistent field delimiters, or
- a fixed field structure
but it is still possible to automate data extraction from such sources. The add-in file ACBA TextToExcel.xla will extract the search strings contained in a weblog that has been saved as a 'Notepad' or text file.
Most modern computer software systems are built so that users with appropriate rights can gain access to (copies of) the underlying raw data. This usually comes in an easily analysable form as a data table. The data table can be posted into a spreadsheet or database at the request of the user.
However, many corporations and government authorities still use systems based on older technologies. Access to the underlying data has to be negotiated with the systems administration staff or the software house and is often still not in a user friendly format. In the course contract work with clients ACBA (UK) LTD has come across the following problems frequently:
- Files which are too large to be imported into MS Excel directly
- Output containing variable single line record structures
- Records which cover more than one line of text
- Output files carrying more than one style of report
- Partially completed records – where the first record in a series is complete but subsequent references to the identifier are omitted
- Report files that include page headers and spare line spacing
All these factors interfere with the standard methods for importing a file into one of the primary business tools. ACBA (UK) LTD has developed a methodology for reviewing text-based records without physically importing them into Excel. The methodology copes effectively with each of the problems outlined above.
There are clear advantages to this methodology:
- Speed - the process takes places in a single continuous phase rather than the traditional import, then cleanse/manipulate, then analyse
- Verification – the process always reports the basic data findings – how many records, total value etc.
- Sophistication and complexity of extract analysis – ACBA (UK) LTD specialises in text string analysis, in particular the extraction of numeric data
- Potential for summarising data as the review proceeds – the programmatic approach allows us to collect and summarise data at the same time
- Reporting of errors or potential errors – the methodology requires a tailored program for each data source, accordingly for each record the analysis will seek to post a value of the correct data type to each essential field. Omissions and incorrect data types will be reported.
Auditing
Auditors are trained to cover the whole range of audit activities, but over time we tend to specialise our efforts into particular types of audit activity. ACBA and close associates specialise in
- operational systems (production control at both strategic and tactical levels and traffic management) and the management modules (invoicing, purchasing and payroll) of financial systems
- quality control systems & performance indicators, management information analysis and the relationships with the standard financial indicators
- personnel, recruitment and educational/training systems
- Central Government grants, subsidies and claims