|Automating the Fixed Assets Audit|
|Benford's Law Downloads Site Map|
|Home Page | Population | Sampling | Fraud | Other | Tutorial | Macros|
|Automating the Fixed Assets Audit|
Automating the Fixed Asset Audit
The process of auditing fixed asset records in an enterprise can be daunting, especially when the number of supporting detail records is in the thousands or tens of thousands, which is not an unusual situation. Two general audit approaches can be used – sampling or 100% testing of the records using automated procedures. This article describes the latter approach, using three different software tools. For each tool, the steps needed to accomplish each of the audit objectives are described. These tools were selected for the article as they are the most familiar to the author. However, a similar approach can be used with almost any other audit software tool.
The data for testing
All of the audit tests were performed using data fabricated for this article for the mythical “ABC Corp” and consist of approximately 5,000 detail records. These detail records are the supporting detail for the fixed assets located at 12 physical locations, and operated by the three organizational units within the ABC Corp.
Why perform automated testing?
One of the key advantages of using an automated approach for audit testing is that any of the so called “impossible conditions” can be tested for. Examples include asset costs with credit balances, acquisition dates in the next century, depreciable lives of less than one year, etc. etc. Even though these situations should rarely be encountered, it is my experience that some such errors can (and do) creep into systems, and inevitably point to some sort of underlying control issue, whether manual, system or a data integrity issue. Thus, for illustrative purposes, we have deliberately introduced some intentional errors into the data being tested in order to illustrate how such conditions might be detected during the regular audit process.
This article is organized around ten audit steps, each to illustrate a particular automated procedure. For each audit step, example approaches using each of the three software tools are shown, along with the summary results of that procedure. All of the data, including the Excel workbook, scripts, commands and log files used are available for free download from http://ezrstats.com/doc/fixasset.zip, in case you wish to re-perform the tests that were done. This eliminates the need for the auditor to re-type the commands for their own use or modification. Note that when opening the Excel workbook you will receive a warning that the workbook contains macros – these were the macros used in this article. For those not familiar with Excel macros, we offer a brief tutorial and provide links where more information can be found.
Rather than include most possible audit tests, we have selected just a representative portion of those procedures in order to avoid repetition of procedures which are commonly performed across multiple audit procedures. Thus, although the procedures in this article are not exhaustive, they should illustrate many of the types of automated procedures that would be encountered during the performance of an audit. The procedural listings include comments, in order that auditors not familiar with the particular language are better able to understand the detail logic of the procedure. In order to make it clearer to the reader and to distinguish between the text of the article and the text of any procedural code, commands or formulae, all procedural code is shown in a different font (Arial Black). The font usage does not indicate any particular importance, but only that a code example being shown.
This article was written five years before the release of Web CAAT which has completely replaced Audit Commander. Web CAAT is an open, web based software package which can be used for a variety of application audits as well as many audit procedures including the audit of fixed assets. General details are available at the site. There is an article devoted to audits of fixed assets.
The detail audit procedures
Often the very first step in any audit is to tie the population totals as reflected in the trial balance being audited to the system records being tested. This is our first test.
The Trial Balance of ABC Corp at 10-31-2006 (end of fiscal year) has the following account balances:
Fixed Assets – Buildings, Property & Equip $19,940,000
Reserve for Depreciation $8,600,000
Depreciation Expense $1,690,000
Run the following commands:
uni ds=file file=FixAsset.txt col=cost recap=FA1
uni ds=file file=FixAsset.txt col=ad recap=FA1
uni ds=file file=FixAsset.txt col=depr recap=FA1
The objective is to prepare a schedule of all assets which have been fully depreciated. This is done using the extract command after first specifying the extract criteria.
The code to specify the extract criteria is as follows:
The command to run the extract is as follows:
Extract ds=file file=c:\test\fixasset.txt recap=”FA4 – Obj2”
Test the depreciation amounts by comparing book amounts for accumulated depreciation with auditor recomputed values.
Similar extract criteria would be used, except additional data elements are used. The extract code is as follows:
Prove totals of additions and disposals.
Additions and disposals can be extracted based upon the acquisition date and the disposition date. If the disposition date is blank, then there has been no disposition of the asset. The extract code is as follows:
After the code has been entered, use the following command to obtain the data extract:
extract ds=file file=FixAsset.txt recap=”FA – Obj4”
The results were already produced as part of step 1 above. (Shows top and bottom 10 values, the number of standard deviations from the mean, etc.)
Uni ds=file file=FixAsset.txt col=bookval recap=”FA – Obj1”
holidays ds=file file=FixAsset.txt col=acqdate recap=”FA – Obj6”
The extraction criteria for possible audit exceptions is as follows:
The code to do the extract would be as follows:
Then run the command:
Extract ds=file file=FixAsset.txt recap=”FA – Obj7”
To extract a list of assets which are valued higher than replacement cost, enter the following code and then run the extract command:
After the extract criteria has been entered, run the following command:
extract ds=file file=FixAsset.txt recap=”FA – Obj8”
dups file=FixAsset.txt col1=tagno
1,375 duplicate tag numbers were found within 15,113 fixed asset records. This summary is shown on the Excel Status Bar and the detail is provided in the Results sheet.
CMA sampling, also known as dollar unit sampling, can be accomplished by using the following algorithm. Note that although credits are tallied in the population being sampled, but they are not considered as part of the sampling process. To do a CMA sample, three factors are needed: an “R” value (reliability) which may be either 1,2 or 3. (A reliability of 1 will result in the most amounts sampled and 3 the least). Second, a “J” factor which is the interval. Often this will be set at 5% of the population value, but it is up to the auditor’s discretion. Finally, a random number which is negative and whose absolute value is less than J divided by R.
The samples are selected using the following logic. A running total is accumulated by adding each debit amount to the running total. Once the running total becomes positive, the transaction is selected. Then the running total is repeatedly decreased by the interval amount until the running total again becomes negative, whereupon the process continues.
cma ds=file file=FixAsset.txt r=2 j=10000 rn=0 recap=”Samp”
The sample reconciliation summary is shown below in an Excel worksheet, and the sample extract file can be obtained using the “list” command, once the auditor is satisfied with sampling results.
The process of auditing fixed asset records in an enterprise can be daunting, especially when the number of supporting detail records is in the thousands or tens of thousands, which is not an unusual situation. By using an automated approach, it is feasible to perform testing of 100% of the records in certain instances. Most audit software is suitable for this process, although some skill and training may be required. Hopefully, auditors will expand their automated testing procedures as well as further share some of their techniques, code and approaches taken, where feasible.
|Complete Auditor resources|
| Web Page last updated on 08-18-2007 |
© EZ-R Stats, LLC 2005-2007