The process of auditing fixed asset records in an enterprise that 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, 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. Thus, for illustrative purposes, we some intentional errors have been deliberately introduced into the test data 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, this article includes a brief tutorial and provides links where more information can be found.
Rather than include all possible audit tests, we have selected just a representative portion in order to avoid repetition of those procedures which are commonly performed across multiple audit tests. Thus, although the procedures in this article are not exhaustive, they should illustrate the most common types of automated procedures that would be encountered during the actual performance of an audit. The procedural listings include comments so 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.
A Few Words of Caution
All of the software in this article uses fairly powerful computer languages. By powerful, is meant that almost any conceivable audit computation or analysis can be accomplished using the computer language, provided the auditor has the skills. However, an inherent risk in with any powerful computer language is that it will not function as intended, due to improper syntax, logic, etc. All of the code in this article has been tested to some extent, but there is no assurance that it will be suitable for any particular purpose. The code is shown here only to provide a starting point for an audit. When using the more powerful features of Excel, SAS or XL Audit Commander, some assistance from an auditor with more in-depth knowledge of computer programming languages may be necessary.
OBJECTIVE 1 – POPULATION TOTALS 3
OBJECTIVE 2 – FULLY DEPRECIATED ASSETS 5
OBJECTIVE 3 – TEST DEPRECIATION 11
OBJECTIVE 4 – ADDITIONS AND DISPOSALS 15
OBJECTIVE 5 – LARGE NET BOOK VALUE 18
OBJECTIVE 6 – UNUSUAL DATES 19
OBJECTIVE 7 – EXCEPTION ITEMS 20
OBJECTIVE 8 – OVER VALUED ASSETS 22
OBJECTIVE 9 – DUPLICATE ASSET TAGS 25
OBJECTIVE 10 – PHYSICAL VERIFICATION – CMA SAMPLE 27
SUMMARY AND CONCLUSION 33
The detail audit procedures
Objective 1 – Population Totals
Often the very first step in any audit is to tie the population totals being audited (as reflected in the trial balance) 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
Now run the first automated test, using each of the three software tools.
Excel
Excel includes a robust facility for performing complex tasks, including some of the computations typically required by auditors. A simplified overview of the process to develop and use macros follows these three steps:
1. In any open workbook, click Tools | Macro | Macros | Insert to edit or start a new macro.
2. A macro can range from a very simple single subroutine to many subroutines, functions, classes, userforms etc. Entire books have been written on the subject.
3. The macro can be invoked using a variety means, including buttons, control-key, menu command etc. etc.
4. Macros are written in the proprietary VBA language from Microsoft.
5. There are numerous resources available on the Internet to get you started (start with a Google search for “Excel macro examples”). There is also a collection of various macros which may be suitable for audit purposes at http://ezrstats.org/xlmods.htm.
For the first audit test, we do not need to use macros. Instead, many formulae are available within Excel which can be used for auditing purposes.
Open the workbook to the sheet “Data”.
Scroll to the bottom of the sheet, and enter the following formula in the three columns Fixed Assets (B4053), Accumulated Depreciation (C4053) and Depreciation Expense (G4053) (formula entered once and then copied to the other two cells)
Formula is “=sum(B2:B4051)” (without quotation marks)
Totals Obtained
19,935,623.28
8,530,514.28
1,683,679.11
Note that small differences exist between the trial balance and the amounts in the population being tested. Generally the auditor will need to use their judgment if further inquiry is merited, considering that the amount of the difference seems small in comparison with the population totals.
SAS Software
Run the following script (comments have been placed into the script to better explain what the procedural steps are doing). Comments in SAS can begin with an asterisk and then end with a semi-colon.
* fixed asset testing - step 1;
* poptotal.sas;
* determine population totals for cost accumulated depreciation and depreciation expense;
filename file1 '/temp/auditnet/fixasset.dbf';
libname db4 '\temp\auditnet';
options compress=yes linesize=72;
*read the input file which was saved from Excel as DB4 format into SAS format;
proc dbf db4=file1 out=db4.fa;
run;
* list what was converted;
proc contents data=db4.fa;
* summarize cost, accumulated depreciation and current year depreciation;
proc summary data=db4.fa;
output out=s sum=totcost totad totdep;
var cost ad depr;
run;
* list the results;
proc print data=s;
run;
Results Obtained
(Same results as Excel, but in addition automatically shows counts)
XL Audit Commander
Run the following commands:
uni file=FixAsset.txt col1=cost
uni file=FixAsset.txt col1=ad
uni file=FixAsset.txt col1=depr
(Same results as Excel and SAS, but in addition automatically shows various statistics – minimum, maximum, average, hi/lo, quartiles, deviations, etc.)
Objective 2 – Fully depreciated Assets
Excel
Open the workbook to the sheet “Data”. One common technique to use when testing values within an Excel worksheet is to insert an additional column and then place a value into that column indicating whether or not a condition exists. Often this value will be the result of a formula computation, which is part of an IF statement. The IF statement can result in a value, e.g. “1” indicating that the condition exists, or else “0” if it does not. Then once the applicable rows within the worksheet have been evaluated, the worksheet can be sorted on these computed values. Excel supports fairly complex IF statements which take the general format “=IF(CONDITION,TRUEVALE,FALSEVALUE)” where condition is a formula for comparison of values,, and can also include Boolean operators such as OR and AND, and the TRUE value is the result to store in the cell if the condition is TRUE and the FALSE value is the condition to store in the cell if the condition tested is FALSE.
In order to perform this audit test we will need to determine if the asset cost – accumulated depreciation is 0 AND the activity code is “A”. Thus, in cell O2 enter the following formula which determines if the book value is zero and the usage code is “A” (active)
“=IF(AND(((B2-C2)=0),K2="A"),1,0)” (without the quotes)
Copy the formula down to all the remaining rows
Sort the workbook descending on the flag column
Select and copy the range with “1” in the flag column to another sheet and paste it
Results Obtained
Three assets identified, asset tags 2103, 5960 and 7756
SAS Software
Run the following script
* fixed asset testing - step 2;
* step2.sas;
* list any fully depreciated assets still in use;
filename file1 '/temp/auditnet/fixasset.dbf';
libname db4 '\temp\auditnet';
options compress=yes linesize=72;
proc dbf db4=file1 out=db4.fa;
run;
data sel;
set db4.fa;
if ((cost = ad) and (accode = "A"));
run;
proc print data=sel;
run;
Results Obtained
(Same results as Excel)
XL Audit Commander
Create a new sheet in the workbook, with any name, e.g. “Code”
Select any cell in the new sheet and type the following commands into consecutive cells going down.
(Note: the code contains comments within the script to better explain what the procedural steps are doing. Comments in PHP start with double forward slashes.
init
$count = 0; $totalamt = 0;
loop
//if the cost is not equal to the accumulated depreciation, skip
$diff = abs($Cost - $AD);
if ($diff > .02) $retain = false;
// activity code must be A - active
if ($Accode !== 'A') $retain = false;
// accumulate control totals
if ($retain == true) {
$count++; $totalamt += $Bookval;
}
vars
$diff
term
echo "Number of items is $count amount is $totalamt;";
Highlight the cells containing the code and then enter the extract command in XL Audit Commander. Then click “Process”.
Once the process button has been clicked, XL Audit Commander will generate the extract program code in the PHP language (open source) and then return a hyperlink to the generated code. Another hyperlink is also generated to the PHP program code as a text file, in order to view or debug the code in case of error (auditors seldom make errors – right?). Clicking on the program code hyperlink from within Excel will cause the generated code to be run.
On the result sheet, click the hyperlink labeled “Click to Run PHP Code”.
This causes the results to be displayed in a browser. (The workstation must be connected to the internet for this to work!)
Results Obtained
Three assets identified (tag numbers 7756, 5960 and 2103, which is the same result as obtained from Excel and SAS Software)
Objective 3 – Test Depreciation
Excel
To test accumulated depreciation, we will use straight line depreciation based upon the useful life for the period between asset acquisition and the lesser of the end of the useful life period, the end of the current fiscal year being audited and the asset disposition date, if any.
Assuming that the depreciable life is in column H, the acquisition date in column J and the disposition date in column L, we would compute accumulated depreciation as follows:
Set up a new column O which would have the date upon which the depreciable life would end.
Set up a new column P which would have the date of disposition (or 12/31/2049) if there is no disposition date (date deliberately picked far into the future).
Set up new column Q which would contain the earlier of the two dates above and the ABC’s fiscal year end.
Set up new column R which would contain the number of days between the acquisition date and the ending date in column Q.
Compute the accumulated depreciation by multiplying the asset cost by a fraction whose numerator is the number of days in column P and whose denominator is the total number of days in the depreciable life (use 365.25 to factor in leap years).
Subtract the computed cost from the book cost to determine any difference.
SAS Software
Run the following script (which follows the same logical steps as Excel)
* fixed asset testing - step 3;
* step3.sas;
* recompute depreciation and accumulated depreciation, compare with book;
filename file1 '/temp/auditnet/fixasset.dbf';
libname db4 '\temp\auditnet';
options compress=yes linesize=72;
*options obs=100;
* read the output from Excel into SAS;
proc dbf db4=file1 out=db4.fa;
run;
proc contents data=db4.fa;
data sel;
set db4.fa;
*define various intermediate results needed;
length termdate 4; length yr 4;
format disdate date9.; format dispdate date9.;
format termdate date9.; format enddate date9.;
length enddate 4;
* extract the month day and year porition from the acquisition date
yr = year(acqdate); mo = month(acqdate);
da = day(acqdate);
yr = yr + life;
* define termination date as 10-31-2049 if no disposition, otherwise use the actual date;
termdate = mdy(mo,da,yr);
if dispdate = . then disdate = '31OCT2049'D;
else disdate = dispdate;
*determine the earliest of the three dates, normal termination, disposition date or end of current fiscal year;
enddate = termdate;
if disdate < enddate then enddate = disdate;
datediff = enddate - acqdate;
termdays = life * 365.25;
if termdays = 0 then termdays = 1;
* compute accumulated depreciation based on ratio of days in service;
pct = datediff / termdays;
compad = cost * pct;
diffad = ad - compad;
drop mo da yr;
run;
option obs=100;
* print the results of the extract;
proc print data=sel;
var acqdate cost ad compad dispdate diffad;
run;
Results Obtained
(Same results as Excel and SAS Software)
XL Audit Commander
The same logic would be used as above, which in XL Audit Commander code (PHP) is as follows:
In the “Codes” worksheet enter the following code on consecutive lines.
Loop
// if no disposition date, then set to 10-31-2049
$adate = strtotime($acqdate);
$ddate = strtotime($dispdate);
If (strlen($ddate) == 0) $ddate = strtotime(“10-31-2049”);
// compute life days in seconds
$lifedays = $life * 365.25 * 24 * 60 * 60;
// termdate is acquisition date + life
$termdate = $adate + ($life * 365.25 * 24 * 60 * 60);
// determine the earliest of the three dates
$enddate = $ddate;
$yearend = strtotime(“10-31-2006”);
If ($yearend < $enddate) $enddate = $yearend;
// compute accumulated depreciation as fraction service days divided by life * cost
$compad = ($enddate - $adate) * $cost / $lifedays;
Vars
$compad
Then run the command:
extract file=FixAsset.txt
On the Results worksheet, click the hyperlink to obtain the results in a browser window.
Results Obtained
(Same results as Excel and SAS Software)
Objective 4 – Additions and Disposals
Excel
Set up two additional columns in the workbook, and use condition statements to determine which assets were acquired during the current fiscal year and which were disposed of during the current fiscal year. To facilitate this, designate two cells within the worksheet which can contain the beginning date and ending date for the current fiscal year. We will then refer to these cells, instead of typing in the literal value in the formula. ($Q$1 is the start date and $Q$2 is the end date). We use $ signs to refer to the values, as we do not want the cell reference to change as we copy the formula.
Formula is as follows: =IF(AND(L2>=$Q$1,L2<=$Q$2),1,0)
This formula states that if the disposition date is between the starting and ending days of the fiscal period, then the asset cost value is placed into the column, otherwise the amount is set at 0.
A similar procedure can be performed for the dispositions.
Once all acquisitions and dispositions have been obtained, the columns can be totaled.
SAS Software
Run the following script
* fixed asset testing - step 4;
* step4.sas;
* prove totals for additions and disposals;
filename file1 '/temp/auditnet/fixasset.dbf';
libname db4 '\temp\auditnet';
options compress=yes linesize=72;
*options obs=100;
proc dbf db4=file1 out=db4.fa;
run;
proc contents data=db4.fa;
data add dispose;
set temp;
if ddate not = . then do;
if ddate > '30SEP2005'D and ddate <= '31OCT2006'D then output dispose;
end;
if acqdate > '30SEP2005'D and acqdate <= '31OCT2006'D then output add;
delete;
run;
option obs=100;
proc print data=add;
title1 'additions';
var tagno cost ad depr;
sum cost ad depr;
proc print data=dispose;
title1 'dispositions';
var tagno cost ad depr;
sum cost ad depr;
run;
XL Audit Commander
Enter the following code in consecutive cells in the worksheet “Code”.
// list asset dispositions in the current year
init
$totcost = 0; $totdepr = 0;
loop
// first, list the asset dispositions in the current fiscal year
$startdate = strtotime(“10-01-2005”);
$enddate = strttotime(“09-30-2006”);
$ddate = strtotime($dispdate);
If (strlen($ddate) == 0) $retain = false;
If ($ddate < $startdate) $retain = false;
If ($ddate > $enddate) $retain = false;
// compute totals for cost and accum depr
If ($retain == true) {
$totcost += $cost;
$totdepr += $ad;
}
term
echo “Total disposition cost $totcost total accum depr $totdepr”;
A similar technique would be used to list the acquisitions:
After the command area has been completed, highlight it and then run the following command:
extract file=FixAsset.txt
On the Excel worksheet returned, click the hyperlink to run the program and view the results in a browser window.
Objective 5 – Large Net Book Value
Excel
Excel has a built in formula “LARGE(ARRAY,ITEMNUMBER) which returns the ith largest value in the area specified.
In order to save some typing, go to the bottom of the spreadsheet and then in consecutive rows type in the numbers 1 and 2. Highlight these cells and drag them down, resulting in cells with numbers from 3 – 10.
Adjacent to each of those cells, insert the following formulae:
“=LARGE($E$2:$E$4051,D4054) where the cell D4054 contains the digit 1. Now copy and drag this formula down to the next nine rows. This will result in a display of the top 10 largest values.
SAS Software
Run the following script
In SAS software, use the built-in procedure “UNIVARIATE” to list the extreme values, i.e. top and bottom 5 values.;
* fixed asset testing - step 5;
* step5.sas;
* determine the largest book values;
filename file1 '/temp/auditnet/fixasset.dbf';
libname db4 '\temp\auditnet';
options compress=yes linesize=72;
*options obs=100;
proc dbf db4=file1 out=db4.fa;
run;
proc contents data=db4.fa;
data sel;
set db4.fa;
bookval = cost - ad;
run;
option obs=100;
proc univariate data=sel;
var bookval;
run;
Results Obtained
(Shows the same top 5 as Excel Software)
XL Audit Commander
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 file=FixAsset.txt col1=bookval
Objective 6 – Unusual Dates
Excel
Identification of dates falling on Sundays can be done with a formula. The test would be “=if(weekday(a2)=6,1,0)” (without the quotes). Excel computes the day of the week as a digit from 1- 7 with 6 being a Sunday.
Checking for holidays is a more tedious process. There are some macros available for various holidays. But generally the better solution is to set up a schedule of holiday dates and then code a macro to check each date against that table using a lookup function. We have not included any code here.
SAS Software
SAS Software can also determine the day of the week using the weekday function. In order to determine holidays, special scripts are required. You may want to look at the work of the United States Naval Observatory Astronomical Applications Department. http://aa.usno.navy.mil/faq/docs/holidays.html
XL Audit Commander
holidays file=FixAsset.txt col1=acqdate
Objective 7 – Exception Items
Excel
Use a formula, such as:
“=IF(OR(B2<=0,G2>B2,1,0)” (without the quotes)
Then sort the worksheet descending on the results of the formula
SAS Software
Run the following script in SAS Software.
* fixed asset testing - step 7;
* step7.sas;
* identify assets with negative cost or assets with accumulated depr > cost;
filename file1 '/temp/auditnet/fixasset.dbf';
libname db4 '\temp\auditnet';
options compress=yes linesize=72;
*options obs=100;
proc dbf db4=file1 out=db4.fa;
run;
proc contents data=db4.fa;
data sel;
set db4.fa;
if (ad > cost) then output;
if cost < 0 then output;
delete;
run;
option obs=100;
proc data=sel;
run;
XL Audit Commander
The same logic would be used as in the SAS Software code example above.
On the “Codes” worksheet enter the following code on consecutive lines (comments are preceeded by “//” and do not need to be entered, but can be. For some unknown reason, entering a double slash into an Excel cell can cause problems – if so, skip it as it is only for documentation purposes.
Loop
// record will not be selected unless explicitly selected
$bselect = false;
// negative cost, select it
If ($cost < 0) $bselect = true;
// depreciation greater than cost, select it
If ($ad > $cost) $bselect = true;
$retain = $bselect;
Click on the hyperlink on the sheet returned to view the results in a browser.
(Shows the same results as the other products)
Objective 8 – Over Valued Assets
Excel
In a new column, e.g. “N”, insert the following formula in cell “N2”:
“=IF(E2>D2,1,0)” (without the quotes
Copy the formula in cell N2 to the remaining cells in column N by selecting and dragging it.
Sort the work sheet on column N in descending order
The rows at the top of the worksheet, which contain the values “1” in column N are the assets whose net book value exceeds replacement cost.
SAS Software
Run the following script
* fixed asset testing - step 8;
* step8.sas;
* schedule assets valued higher than replacement costs;
filename file1 '/temp/auditnet/fixasset.dbf';
libname db4 '\temp\auditnet';
options compress=yes linesize=72;
*options obs=100;
proc dbf db4=file1 out=db4.fa;
run;
proc contents data=db4.fa;
data sel;
set db4.fa;
if bookval > replace;
run;
proc print data=sel;
title1 ‘assets with book value > replacement value’;
XL Audit Commander
Enter the following code in consecutive cells in the worksheet “Code”.
// schedule assets valued higher than replacement cost
init
$count = 0; $value = 0;
loop
// skip any assets with cost <= replacement cost
if ($bookval <= $replace) $retain = false;
if ($retain == true) {
$totcost += $cost;
$totdepr += $ad;
}
term
echo “Total assets > replacement cost $count total $value”;
After the code has been placed into the command area, highlight it and then run the following command:
extract file=FixAsset.txt
On the Excel worksheet returned, click the hyperlink to run the program and view the results in a browser window.
Results Obtained
(Shows the same results as the other products)
Objective 9 – Duplicate Asset Tags
Excel
Procedure:
Identifying duplicates in Excel can be a tricky process. However, there is a very good narrative at how to do so at http://www.cpearson.com/excel/duplicat.htm.
SAS Software
Run the following script
* fixed asset testing - step 9;
* step9.sas;
* identify duplicate tag numbers;
filename file1 '/temp/auditnet/fixasset.dbf';
libname db4 '\temp\auditnet';
options compress=yes linesize=72;
*options obs=100;
proc dbf db4=file1 out=db4.fa;
run;
proc contents data=db4.fa;
data sel;
set db4.fa;
count = 1;
run;
proc sort data = sel;
by tagno;
proc summary data = sel;
by tagno;
output out=s n=n;
var count;
data s2;
set s;
if n < 2 then delete;
proc print data=s2;
run;
XL Audit Commander
dups file=FixAsset.txt col1=tagno
Results Obtained
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.
Objective 10 – Physical Verification – CMA Sample
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.
.
Excel
Excel does not have a facility for performing CMA samples directly. Thus, it is best to use a macro to accomplish this. The macro below has the values of R, J and the random start coded into the macro, although it would also be feasible to read these values from cells on some worksheet within the workbook. The macro then reads each row of the population data (which is stored on the sheet named “Data” and writes out a sample extract to the sheet named “Sample” along with a sample reconciliation to the worksheet named “Recon”.
Sub CMASamp()
'Author: EZ-R Stats, LLC
'Date: 10/27/2006
'Purpose:
'perform a CMA sample using and Excel macro
'output (selections) are written to the Sample worksheet
'sample reconciliation is written to the reconciliation sheet
Dim c As Object
Dim dCell As Object
Dim e As Object
Dim ssheet As String
Dim numdr As Long
Dim numcr As Long
Dim amtdr As Double
Dim amtcr As Double
Dim underJ As Long
Dim overJ As Long
Dim amtUnderJ As Double
Dim amtOverJ As Double
Dim numsel As Long
Dim recno As Long
Dim sSheetIn As String
Dim sSheetOut As String
Dim R As Integer
Dim j As Long
Dim Itvl As Double
Dim rn As Double
Dim rnstart As Double
Dim d As Double
Dim rnend As Double
Dim numseloverJ As Long
Procedure:
sSheetIn = "data"
sSheetOut = "sample"
Set dCell = Sheets(sSheetOut).Range("a2")
Set c = Sheets(sSheetIn).Range("b2")
d = c.Value
'MsgBox "first cell has value " & d
'If (d < 0) Then
' MsgBox "Is negative"
'Else
' MsgBox "is positive"
'End If
numdr = 0
numcr = 0
amtdr = 0
amtcr = 0
underJ = 0
overJ = 0
amtUnderJ = 0
amtOverJ = 0
numsel = 0
recno = 0
numseloverJ = 0
Set e = Sheets("Recon").Range("a2")
' R and J values are hard-coded below (can be changed)
R = 2
j = 50
Itvl = j / R
rn = -Rnd() * Itvl
' begin with a random number between 0 and interval
rnstart = rn
e.Value = "R is "
e.Offset(0, 1).Value = R
e.Offset(0, 1).Value = Itvl
Set e = e.Offset(1, 0)
e.Value = "Random start is "
Note: due to length, macro is not shown here in its entirety; please refer to the macro in the workbook.
SAS Software
Run the following script (which is functionally equivalent to the Excel VBA macro)
* fixed asset testing - step 10;
* step10.sas;
* cma sample based upon asset cost;
filename file1 '/temp/auditnet/fixasset.dbf';
libname db4 '\temp\auditnet';
options compress=yes linesize=72;
*options obs=100;
proc dbf db4=file1 out=db4.fa;
run;
proc contents data=db4.fa;
data sel tots ;
set db4.fa end=eof;
retain r 2;
retain j 10000;
retain rn -273;
retain startrn 273;
retain numdr 0;
retain numcr 0;
retain amtdr 0;
retain amtcr 0;
retain selflag 0;
retain numsel 0;
* sampling logic follows;
if eof then do;
output tots;
delete;
end;
selflag = 0;
if cost < 0 then do;
numcr = numcr + 1;
amtcr = amtcr + cost;
delete;
end;
numdr = numdr + 1;
amtdr = amtdr + cost;
rn = rn + cost;
if rn > 0 then do;
selflag = 1;
numsel = numsel + 1;
output sel;
do while (rn > 0 );
rn = rn - j;
end;
end;
delete;
run;
proc print data=sel;
title1 "sample selections";
proc print data=tots;
title1 "sampling stats";
run;
XL Audit Commander
cma file=FixAsset.txt r=2 j=10000 rn=0 sampfile=samp.txt
Results Obtained
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.
Summary and Conclusion
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 task, 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. By doing so, organizations may be able to perform audits more efficiently and effectively. Automation and support tools such as these can be the driving force to enable greater audit efficiency and effectiveness.
About the author: Mike Blakley is currently an IT auditor with the State of North Carolina, Department of Health and Human Services. Mike maintains a blog devoted to audit software topics at http://blog.ezrstats.com and his e-mail address is Mike.Blakley@ezrstats.com.