Some organizations perform recurring audits, either over time (e.g. quarterly) or else location (e.g. store or branch). Each of these audits generally has very similar audit objectives and often follows essentially the same audit steps. When data to be audited is available in electronic format, this data will generally be somewhat standardized and of the same type from audit to audit.

Often, standard audit programs, work sheets and schedules are used. Over time, and based upon prior audit results, a store of audit knowledge is developed, which can then be incorporated into future audits. This knowledge, properly leveraged, will generally enhance the value of the audits.

A fundamental premise of this article is that once this audit knowledge has been captured in an Excel workbook in the proper format, it can then be reused, to a large extent, without incurring significant additional expense, and without the necessity of extensive manual intervention. Capturing this knowledge does require expertise and time, but these costs should be more than offset through future reductions in audit efforts expended.

This article presents a structure that defines a repeatable process by which a systematic and rigorous analysis of data can be coordinated.

“We already have a repeatable process. We do the same manual process again and again.” – audit senior
By “repeatable process” is meant a documented, structured approach which requires management and QA processes. Also, a repeatable process does not relieve the audit staff from exercising judgment and carefully reviewing the reports produced and the results obtained. Also, “repeatable” does not mean that the analysis is done the exact same way each time. Each review should bring to light certain discoveries which can then be leveraged in future reviews and/or further investigated in the current review.

Objectives/Scope

Although certain portions of the audit field work may become a repeatable process, this process is not always fully automated, nor is the level of skill required necessarily reduced. Although significant audit knowledge may have been acquired during previous audits, this information may not be fully leveraged for a variety of reasons including:

• Test procedures deemed too time consuming to be justified
• Excel skills required have not been fully documented or taught
• Staff turnover has resulted in “gaps” in knowledge and experience

This article addresses various approaches towards minimizing or overcoming the challenges listed above.

Roles / Staffing

In some audit organizations, depending on size, there are various audit roles (which may be performed by the same person):

• IT Audit Support – persons with significant computer audit experience who can implement/trouble shoot computer audit application systems.
• Audit Subject Matter Experts – persons who have significant knowledge of the internal control systems within the organization, as well as historical audit results/issues
• Audit Supervisors – persons responsible for planning, overseeing audit field work, audit reports and audit coordination
• Field Auditors – persons responsible for accomplishing the detail audit field work


A key challenge is how to achieve the most efficient and effective results, considering all the constraints faced by most audit organizations such as staffing levels, training, audit resources, etc.


Cost of Audit Procedures

Every audit procedure has a cost, which is generally the time needed to gather the data, perform the procedure/analysis, etc. Due to scarce resources, it has become increasingly important to minimize this cost while still obtaining adequate audit coverage. In order to achieve this, careful planning is often required.

When audit data, such as transaction details, is available in electronic format, there are a variety of analytical procedures which could be performed. Some, but not all, of these procedures may yield information which will be useful for audit planning purposes. The challenge is that the analytical procedures can require substantial computer and audit skills, substantial time resources or have a steep learning curve.

Often, the Audit Subject matter Experts and IT Audit Support may already have the skills and knowledge to perform these procedures, but are constrained by other tasks and responsibilities. Thus, one objective is to shift this work, where possible, to staff with less experience and knowledge.

Multi-Prong Approach

Computer processing speeds are steadily increasing, and hardware costs steadily declining (Moore’s Law). Thus, it makes sense to have computers perform as many analytical procedures as feasible, even if such a procedure performed manually might not be cost effective. Error conditions or other unusual patterns are a prime example of how computer applications to obtain analytical data can be very cost effective. Generally, this will consist of the proverbial “needle in a haystack” search. However, the subject matter expert will know of the error conditions that have been previously encountered, as well as understand the range of conditions that are considered either potential errors or of such an unusual nature as to warrant possible further investigation.

Computers can be used to summarize and classify data in a variety of means. This classification and summarization can typically be performed fairly quickly and in a greatly automated manner.

Some examples of classification and summarization are shown below:

Transaction amounts

Population totals (divided into debits, credits and zero amounts)
Population ranges (minimum, maximum, mode, average, standard deviation)
Characteristics (Benford’s Law, Round Numbers)
Classification – stratification, histograms, outliers, top 10, bottom 10
Ageing by Date
Exceptions/ error situations (e.g. negative cost)

Transaction Dates

Grouping by day of week
Dates falling on a federal holiday
Exceptions/error situations (e.g. future dates, nonsensical dates)

Text Values

Cross Tabulations
Similarities between text values

Other Conditions

Gaps in numeric sequences (e.g. missing check numbers, invoices)
Duplicates


Procedures to Identify/Classify Data in Excel

Once audit data has been made available in a suitable format, it can then be readily analyzed. Such formats include the following:

• Data contained in databases such as MS-Access, Oracle, MySQL, DB2 etc.
• Data contained in “flat files” in industry standard tab separated value format
• Data contained in worksheets within an Excel workbook

Command Language

Each of the procedures mentioned above can be achieved using a “command language”. A command language is simply shorthand information to enable a computer program to perform the analysis requested. The command language used here is the XL Audit Commander. There are other command languages as well, and they may or may not have similar features and capabilities. A command will be comprised of two components:

• The command to be performed, e.g. summarization
• The information (parameters) needed to perform that command, e.g. data source, data columns to be summarized, name of the worksheet where results are stored, etc.

A key advantage of a command language is that it may not require knowledge of a traditional computer programming language. Instead, it is more of a clerical function – using a checklist, specify the information required to complete the command, then enter this information into an Excel worksheet or command toolbar for processing.

Logging

For audit workpaper documentation purposes and for the development of a repeatable audit process, it is important to have a log of all commands processed in order to know what was done, how it was done and a summary of the results.

A few examples

All of the examples in this article are available for download from http://ezrstats.com/online/inno/QS.xls, which is an Excel workbook with example data for testing and learning the XL Audit Commander system.

To provide a “flavor” for what a command language procedure looks like, some concrete examples are provided below. These are admittedly simplistic, and designed only to illustrate the potential.

Suppose that the details of an accounts receivable balance are available in a file named “ar.txt” which consists of just four columns of data – customer number, account balance, due date and amount. Suppose further that the first audit step in testing the accounts receivable balance is to summarize this data by account balance. There are at least two methods that this could be achieved:

1. Import the data into an Excel worksheet and then use a “sum” formula at the bottom of the column of amounts
2. Use a command language statement to summarize the amounts.

In this example, the effort required for either procedure is roughly the same. However, as more complex analytical procedures are encountered, the command language approach will generally be much more efficient.

In this example, to process the command, the following information is needed:

Item Value
File name Ar.txt
Data source type file
Amount to be summarized “account balance”
WorkSheet to contain the results “AR Summary”

The command to process this would be the “univariate” command, which provides a variety of statistics for any specific numeric variable in a population, including something as basic as file totals (it also provides much more information).

The syntax for the command would be:

un ds=file file=ar.txt col=”account balance” recap=”AR Summary”

Suppose that the next step was to classify the balances between those which are debits, those which are credits and those which have a zero balance. For this classification, both the counts and amounts are needed.

This would be done in Excel using the “COUNTIF” and “SUMIF” functions. A total of six formula would need to be entered and processed.

However, this information would have already been obtained with the command language statement above, even though it was not specifically requested.

Suppose the third step was to age the balances. In Excel there are a variety of means of doing so, such as computing the number of days between the due date and the ageing date and then summarizing this information either after a sort or else by using the COUNTIF and SUMIF functions.

To obtain this information using a command language would require the following information:

Item Value
File name Ar.txt
Data source type file
Amount to be summarized “account balance”
WorkSheet to contain the results “AR Ageing”
Ageing Date 6/30/2007
Size of ageing bucket (in days) 30
Data column containing the ageing date “due date”

The command to perform the ageing of account balances would be as follows:

Ar ds=file file=ar.txt agecol=”due date” amount=”account balance” colwidth=30 recap=”AR Ageing” agedate=”6/30/2007”

Re-performing the procedure

In order to re-perform the procedure in Excel, generally a whole series of steps will need to be used with any new (or changed) data, unless an excel macro has been written to do so. With a command language procedure, only the new parameter information (e.g. file name, ageing date, etc.) needs to be changed, at which time the command can easily be re-performed. An additional benefit is that far fewer specific knowledge skills are required (other than typing in the parameter values, which can be based on a checklist).

More complex procedures

Although an audit of an area may involve only a single step, it is much more common to encounter the need for a series of steps. For example, a review of accounts receivable balances may involve a series of steps such as:

• Obtain overall totals for accounts receivable and tie the amount to the trial balance
• Classify (and quantify) the balances between debit credit and zero balances
• Age the balances as of the end of the fiscal year
• Test if any of the ageing amounts represent invoices generated on a holiday
• Do an analysis of round numbers
• Prepare a schedule of the largest 10 account balances
• Select a CMA sample of the balances with debits
• Perform an interval sample of accounts to determine if the customer has been approved for credit
• Etc., etc.

In this case, it is also possible to establish a series of commands to perform these procedures. Each command would be placed into consecutive cells going down an Excel worksheet. Generally, each command will specify that the results of that command be placed on a separate Excel worksheet for later review and analysis.

How to specify the commands

The syntax for each command will vary, but will include such items of information as the file name to be processed, columns to be summarized, etc, Often this information will be common to many commands. For example step1 might be to summarize the values of a column, step 2 identify the top and bottom 10 values, step 3 to perform a test of Benford’s Law etc. Although it may not be too tedious to enter these values over and over, it could become tedious if the process needs to be re-performed with a different set of values.

For this reason, it is better to specify the values separate from the commands and then have the values substituted at the time the command is processed.

As a simple example, lets suppose we were to perform the two steps mentioned above. The commands would be as follows:

un ds=file file=”c:\2007 Audits\Client ABC\yr2006\ar.txt” col=”account balance” recap=”AR Summary”
Ar ds=file file=”c:\2007 Audits\Client ABC\yr2006\ar.txt” agecol=”due date” amount=”account balance” colwidth=30 recap=”AR Ageing” agedate=”6/30/2007”



Substitution Values / Maintainability

Now assume that these audit procedures are repeated the following year. At a minimum, the file name would need to be changed, as well as the ageing date.

An alternative manner to specify the commands to be processed is to specify substitution variables, i.e. text that is to be substituted when the commands are processed. By so doing, only a single value needs to be changed in order to run the commands with different variables.

=”C::\2007 Audits\Client ABC\yr2006\ar.txt”
=”6/30/2007”