Automating the Fixed Asset Audit

Mike Blakley

Informations

• Publié par: Ella
• Nombre de lectures: 36
• Langue: English

Extrait

Automating the Fixed Asset Audit Ten Commonly Performed Tests  

  Summary   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 frompitez.axssdom/fic/atstcos./:ptrze/th, 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

Automating the Fixed Asset Audit

Page 1

Automating the Fixed Asset Audit Ten Commonly Performed Tests   this article. For those not familiar with Excel® 1  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 .............................................................................................. 4   OBJECTIVE   2   –   FULLY   DEPRECIATED   ASSETS ............................................................................... 7     OBJECTIVE   3   –     TEST   DEPRECIATION .............................................................................................. 12   OBJECTIVE   4   –     ADDITIONS   AND   DISPOSALS ................................................................................. 16   OBJECTIVE   5   –     LARGE   NET   BOOK   VALUE ..................................................................................... 19   OBJECTIVE   ....................................................................................................... 21     6   –   UNUSUAL   DATES OBJECTIVE   7   –     EXCEPTION   ITEMS ................................................................................................... 21   OBJECTIVE8–   OVERV ........................................................................................... 23        ALUED   ASSETS OBJECTIVE   9   –     DUPLICATE   ASSET   TAGS ....................................................................................... 26                                                     1 eg Rteisder   trademarks – Microsoft Corp. SAS – SAS Institute

Automating the Fixed Asset Audit

Page 2

Automating the Fixed Asset Audit Ten Commonly Performed Tests   OBJECTIVE   10   –     PHYSICAL   VERIFICATION   –     CMA   SAMPLE .................................................... 28   SUMMARY   AND   CONCLUSION ........................................................................................................... 34    

Automating the Fixed Asset Audit

 

Page 3

Automating the Fixed Asset Audit Ten Commonly Performed Tests  

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.  

Audit Objective 1.   Obtain population totals for fixed asset costs, accumulated depreciation and depreciation expense and tie these amounts in total to the trial balance.     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, clickTools | Macro | Macros | Insertto 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.   are numerous resources available on the Internet to get you startedThere (start with a Google search for “Excel macro examples”). There is also a

Automating the Fixed Asset Audit

Page 4

Automating the Fixed Asset Audit Ten Commonly Performed Tests   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 d epreciation expense; filename file1 '/temp/au ditnet/fixasset.dbf'; libname db4 '\temp\auditnet'; options compress=yes linesize=72; *read the input file which wa s saved from Excel as DB4 format into SAS format; proc dbf db4=file1 out=db4.fa;

Automating the Fixed Asset Audit

Page 5

Automating the Fixed Asset Audit Ten Commonly Performed Tests   run; * list what was converted; proc contents data=db4.fa; * summarize cost, accumulat ed 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.)  

Automating the Fixed Asset Audit

 

Page 6

Automating the Fixed Asset Audit Ten Commonly Performed Tests   Objective 2 – Fully depreciated Assets       Audit Objective 2.      Obtain a list for review of fully depreciated assets still in use.       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) the quotes)” (without 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 toanother sheet and paste it   Results Obtained   Three assets identified, asset tags 2103, 5960 and 7756  

Automating the Fixed Asset Audit

Page 7

   

Automating the Fixed Asset Audit Ten Commonly Performed Tests  

    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;

Automating the Fixed Asset Audit

Page 8

 

Automating the Fixed Asset Audit Ten Commonly Performed Tests  

run;       esults Obtained R   (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.   in it $cou nt = 0; $totalamt = 0; loop //if the co st is not equal to the accumulated depreciation, skip $diff = ab s($Cost - $AD); if ($diff > .02) $retain = false; // activity code must be A - act ive if ($Accode !== 'A') $retain = false; // accumulate control totals if ($retain == true) {  $count++; $total amt += $Bookval; } vars $diff term echo "Nu mber of items is $cou nt amount is $totalamt;";       Highlight the cells containing the code and then enter the extract command in XL Audit Commander. Then click “Process”.  

Automating the Fixed Asset Audit

Page 9

Automating the Fixed Asset Audit Ten Commonly Performed Tests  

  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”.

Automating the Fixed Asset Audit

 

Page 10

 

Automating the Fixed Asset Audit Ten Commonly Performed Tests  

     This causes the results to be displayed in a browser. (The workstation must be connected to the internet for this to work!)  

  Results Obtained

Automating the Fixed Asset Audit

 

Page 11