Audit Commander has 32 tests which can be performed on fixed asset records using check boxes, drop down lists and "fill in the blanks". Test results can be then be loaded into an Excel workbook for further review and analysis. The purpose of this document is to describe the procedures and explain how they are performed for fixed assets. (There are similar procedures for accounts payable).

The fixed asset data can be in any of the following formats:

• Microsoft Access (2002/2007)
• Microsoft Excel (2003/2007)
• Text File (comma separated value)
• Text File (tab separated value)
• Dbase IV

Overview

The process involves six steps:

1. Identification of the source data (Excel, Access, etc.)
2. Identifying the table or worksheet containing the data
3. Specifying the key column names for asset cost, useful life, etc.
4. Selecting the tests to be performed and the parameters to be used
5. Running the tests
6. Loading the output reports into an Excel workbook
   

Identification of the source data

From the Audit Commander main menu select “Audit Areas | Fixed Assets”

When this menu item is clicked, the fixed assets audit form will be displayed. The form contains five tabs:

1. Fixed Assets input parameters
2. Fixed assets tests
3. Additional tests
4. Errors
5. Processing parameters

We will discuss each of these in detail, beginning with the input parameters.

Fixed Assets Input Parameters

The form is completed from top down.

The first item is to specify the data source, i.e. in what type of file does the audit data reside? The selections may be made from a drop-down list consisting of Excel, Access, text Files, Dbase IV and SQLite. In this example we will use an Excel workbook named Test.xls which has a sheet named FA.

The second step is to click on the button called “Data File Source”. The name of the Excel workbook can then be selected by using the standard Windows file explorer.

The next step is to specify the name of the table or worksheet to be used. This is also done using a drop down list. In this case the name of the sheet containing the audit data to be tested is called “FA”.

The remaining steps for this tab are to specify which columns represent the asset cost, acquisition date, accumulated depreciation, etc. Also, the audit period start date and end date are specified here. These dates are used for the calculation of depreciation.

Once all the information has been entered, it will look something like this:

We are now ready to move over to the second tab, called “Fixed asset tests” which appears as follows:

This form indicates that there 16 audit tests or tasks which can be performed. (There are additional tests on the following tab). In order to perform a test, the auditor checks the box on the left and then completes any additional information required on the line.

For example, the very first test is to obtain the largest fixed asset items. If the auditor wanted the largest 20 fixed asset costs, the first box would be checked and the number 20 typed into the box.

The second test is simply to determine if any fixed assets have a zero or negative cost. This test is specified by checking the box.

Other tests require that the column name be selected in order to perform the test. For example, test 4 is to obtain a cost summary by some sub-total. The auditor would select the column name from the drop down list. For example, in order to obtain sub-totals by location, the auditor would select the column named “location” from the drop down list, etc.

Once all the tests have been specified on the tab, the auditor would then go to the next tab and perform a similar process.

Example of a completed form

Shown below is an example of a completed form. Note that not all of the tests have been selected to be run.

Now the auditor would go to the tab, called “Additional tests”

Additional tests

Here the process is very similar: fill in the blank, select from drop-down lists etc. An example of a completed form might appear as follows:

At this point all of the processing parameters have been entered. So now the auditor has two choices:

• Click the “Parms” button to review the parameters entered and check if there are any errors or missing information, or
• Click the “OK” button to begin the processing. When the OK button is clicked, if there are any error identified, the system will display a message on the status bar and place a description of the errors in the tab called “errors”.

Clicking on the parameters button will result in the following:

The system has identified one error, which is shown under the errors tab as follows:

The system indicates that for test 5, a column name must be selected.

Once processing has been completed, the data produced can then be imported into an Excel workbook for further review and analysis. The system includes a workbook named “FAReport.xls” which can be used to perform this task. The lead sheet is named “$Index” and appears as follows:

To load the data into the worksheets, a two step process is followed:

• Check if the name of the report directory is correct. The default file installation is to the directory “c:\program files\ezs\ac\fa”. However , this location may have been changed. If so, type the correct folder name in cell B2 of the workbook.
• Click the button labeled “Load fixed assets reports into workbook”

The system will now load all the data into the workbook and place hyperlinks on the lead sheet to each detail worksheet. This workbook should be saved under a different name, because the next time fixed asset tests are run, the contents of the workbook will be changed.

Detail descriptions of each of the tests are as follows:

Largest fixed asset amounts

Purpose – identify the largest fixed assets based upon cost amount.

Information needed – number of fixed assets to identify, e.g. largest 5, 10, 20 etc.

Assets with zero or negative cost

Purpose – identify possible errors where fixed assets have a zero or negative cost.

Information needed – name of fixed asset cost column.

Assets with book value < salvage

Purpose – identify fixed assets which have been over depreciated

Information needed – name of columns for asset cost, accumulated depreciation, salvage value.

Cost summary

Purpose – summarize fixed asset costs by various criteria, e.g. location, asset type, etc.

Information needed – name of the column by which the assets are to be summarized.

Duplicates

Purpose – identify any duplicate items, based upon values in a single column, e.g. asset tag number

Information needed – name of the column by which duplicates are to be checked.

Benford’s Law

Purpose – check if specified numeric values conform with that which would be expected using Benford’s Law

Information needed – name of the numeric column by which numeric values are to be checked.

Compute straight line depreciation

Purpose – compute depreciation using the straight line method for testing depreciation expense. Calculation is based upon the number of days in service, and considers asset cost, salvage and useful life.

Information needed – name of the columns for fixed asset cost, useful life, accumulated depreciation, salvage value and acquisition date

Compute declining balance depreciation

Purpose – compute depreciation using the declining balance method for testing depreciation expense. Calculation is based upon the number of days in service, and considers asset cost, salvage and useful life.

Information needed – name of the columns for fixed asset cost, useful life, accumulated depreciation, salvage value and acquisition date, and declining balance rate

Transactions on a holiday

Purpose – identify transactions occurring on a federal holiday.

Information needed – name of the date column to be tested.

Day of the week

Purpose – perform a data extract based upon the specified day(s) of the week

Information needed – name of the date column to be tested and which days of the week are to be extracted.

Date ranges

Purpose – perform a data extract based upon the specified date range

Information needed – name of the date column to be tested and the starting and ending date (transactions on the starting or ending date are included in the extract)

Date Near

Purpose – perform a data extract based upon the specified date range

Information needed – name of the date column to be tested and the number of days within the date. For example, if the test date is specified as July 30, 2008 and the number of days is 2, then five dates will be selected – July 28, 29, 30, 31 and August 1.

Round Numbers

Purpose – extract transaction amounts with the specified number of digits of round numbers.

Information needed – name of the numeric column containing the amount to be tested. Number of digits of round numbers to be tested. For example, if the number 10 is selected, then round numbers such as 20, 540, 6070 will be selected, but other round numbers such as 500, 3000 will not (they have too many round digits). Similarly, if the number 1000 is selected, then round numbers such as 51,000, 643,000, 7,050,000 will be selected, but other numbers such as 400, 6070, 5400 would not be.

Sequence Gaps

Purpose – determine if any numeric values, e.g. check numbers, purchase orders, etc. contain any “gaps”, i.e. missing document numbers.

Information needed – name of the column which contains the sequential number.

Duplicates

Purpose – identify any duplicate items, based upon values in two columns, e.g. asset tag number and location. In this case a duplicate would be considered any situation where both the same tag number and the same location were identified, but not in the situation where the same tag number existed, but in two different locations.

Information needed – name of the two columns by which duplicates are to be checked.

Time ranges

Purpose – perform a data extract based upon the specified time ranges (hourly)

Information needed – name of the date column to be tested and each of the 24 hour time periods that are to be extracted. Time periods are divided into a.m. and p.m. Hours begin at midnight or noon and go to the following noon or midnight. To select transactions within an hour period, check the indicated box.

Fuzzy match (VBA)

Purpose – extract records based upon a “fuzzy” match, i.e. use “wild cards” to specify how text is to be matched. There are four primary types of matching characters:

? (single question mark) will match any single character

* (single asterisk) will match any one or more consecutive characters

[range from – range to] enclosed brackets match any single character in the range, e.g. [A-F] will match any letter between A and F, i.e. A,B,C,D,E,F

# matches any single digit

[character list] matches any single character in the list, e.g. [CDR] would match any of C, D or R

[!character list] matches any character that is NOT in the list

Information needed – the name of the column to be tested. The wild card characters to be used to perform the test.

Fuzzy match (LD)

Purpose – extract records based upon a “fuzzy” match done using the Levenshtein distance. The Levenshtein distance is a count of the number of inserts, deletes and changes required to make two character strings match. This technique is useful for identifying two strings which are very nearly equal except for a transposition, extra character, etc. For example, the Levenshtein distance between two invoice numbers 1074B and 1047B 2 because the transformation consists of a single character deletion and a single character insertion. 1074B -> 104B -> 1047B ( a two step process)

Information needed – the name of the column to be tested. The Levenshtein distance amount which serves as the cutoff number

Population statistics

Purpose – compute most of the relevant population statistics measures such as count, sum, minimum, maximum, standard deviation, range, skewness, kurtosis, number and amount of debits, number and amount of credits, number of zeros etc.

Information needed – name of the numeric column for which the statistics are to be computed.

Assets with a useful life of one year or less

Purpose – identify fixed assets with a life of one year, zero years or negative years (i. e. error condition)

Information needed – the name of the column containing the useful life.

Assets with debit accumulated depreciation

Purpose – identify fixed assets whose accumulated depreciation amount is a positive value (should be a credit amount)

Information needed – the name of the column containing the accumulated depreciation.

Round number Summary

Purpose – classify the population overall as to the quantity of values with round numbers (and those which are not)

Information needed – name of the column which contains the numeric amount to be tested.

Holiday Summary

Purpose – classify the population overall as to the number of transactions which fall on the various holidays

Information needed – name of the column which contains the date value to be tested.

Data stratification

Purpose – stratify the population using the values specified.

Information needed – name of the numeric column to be analyzed. Strata values (must be in ascending order and separated by spaces). For example to stratify a population with amounts from -100 to 0, 0 to 200, 200 – 400 and 400 – 1000 the strata would be specified as “-100 0 200 400 1000”.

Further details.