Computer Assisted Audit Tools and Techniques

March 10, 2008 REVENUEAUDIT MEMORANDUM ORDER NO. 1-08 SUBJECT : Computer Assisted Audit Tools and Techniques TO : All Internal Revenue Officers and Others Concerned I. BACKGROUND Information Technology (IT) has caused the present pervasive use of Information System tools for taxpayers to conduct their business and has changed rapidly the manner of recording transactions. Indeed, technology is so pervasive that it would be difficult for many taxpayers to meet their accounting and financial reporting objectives without using technology. The changes in the use of information technology by business have had a profound effect on accounting and auditing because these records are almost universally recorded, processed, and reported digitally. Consequently, changes brought about by information technology have changed the very nature of the evidence of transactions and have created a challenge for the tax auditors to maintain their competence. This is because the change in business process results also in the removal of traditional source of information in which conventional manual audit techniques become inadequate and therefore demands the creation of new audit procedures to conduct the tax compliance audit. II. OBJECTIVE This order is issued to: 1. Supplement the existing Audit Manuals to improve the quality of a tax audit but with avoidance of duplication of work; 2. Prescribe the mandatory use of Computer Assisted Audit Tools and Techniques (CAATTs) for the audit of all taxpayers using different accounting application programs, computerized accounting records and components thereof; aDIHCT 3. Provide revenue officers with minimum standard procedures and techniques for data analysis, calculation, reconciliation, exception testing, etc., using applicable commands/CAATTs application available in an audit software as provided under the Computer Audit Manual, hereto attached as Annex "A"; 4. Reduce audit cycle in order to provide for timely reports of investigation and to focus on critical aspects of taxpayers' financial operations; and 5. Simplify audit documentation. III. POLICY To ensure quality audit of tax returns of taxpayers using Computerized Accounting System (CAS) or any components thereof, revenue officers shall utilize their technical skill, training, and experience in computer assisted audit, and follow the minimum computer assisted audit tools and techniques as prescribed in the Computer Audit Manual. IV. EFFECTIVITY This Order takes effect immediately. (SGD.) LILIAN B. HEFTI Commissioner of Internal Revenue ANNEX A Bureau of Internal Revenue Computer Audit Manual I. Overview The emergence of various technologies in data processing has changed the way a tax audit or examination can be efficiently and effectively done by a Revenue Officer (RO). Ignoring the computer, treating it as a black box, and auditing around it is no longer feasible given the continuous advancement in technologies used by the taxpayers. While there is no doubt that the use of computers has improved exponentially the reliability of processing financial transactions and integrity of financial data, the Bureau realizes that there are significant changes that impact on the traditional audit procedures Performed by the Revenue Officers. EAISDH The challenges faced by the RO in the use of computer technology are in the following: 1. Retrieval of data in electronic form; 2. Interpretation of electronic data; 3. Identification of audit trail; 4. Storage of the data in electronic media; and 5. Application system deficiencies/unauthorized changes and other forms of technical manipulations. It is in this context that this Manual is prepared to enable the ROs to address these major challenges brought about by the widespread use of computer application systems by taxpayers. The Computer Audit Manual is the supplemental reference of the ROs in the investigation of the internal revenue tax liabilities of the taxpayers who use a Computerized Accounting System (CAS) or components thereof. II. Objectives and Scope This Manual is intended to: 1. Supplement the existing Audit Manuals but with avoidance of duplication of work; 2. Prescribe the mandatory use of Computer Assisted Audit Tools and Techniques (CAATTs) for the audit of all taxpayers using different accounting application programs, computerized accounting records and components thereof; 3. Provide ROs with minimum standard procedures and techniques for data analysis, calculation, reconciliation, exception testing, etc.; 4. Reduce audit cycle; and 5. Simplify audit documentation. This Manual covers the following areas: 1. Definition of technical terms EHSTDA 2. Preliminary audit work 3. Examination proper 4. Documentation and reports 5. Review of audit reports III. Definition of Technical Terms ASCII refers to data that has been encoded according to the American Standard Code for Information Interchange (ASCII). It is the native character set of most minicomputers and all personal computers. Audit Software (AS) software designed for data extraction and analysis of accounting transaction files. Audit Command Language (ACL) a commercial audit software used as an audit tool for analyzing large data files quickly, easily and accurately. CD-ROM (Compact Disc-Read Only Memory) is a data reader for optical discs commonly called CD drive. CD-R (Compact Disc-Read Only) a variation of the Compact Disc which is a Write Once, Read Many (WORM) optical media (though the whole disc does not have to be entirely written in the same session) and retains a high level of compatibility with standard CD readers. These optical discs are read-only which cannot be modified, deleted or written over with new data. Conversion transforming data files of a taxpayer from its native format to a preferred format readable by an audit software ( e.g. delimited text files, dbf, mdb, excel spreadsheet) Component is something that forms part of a whole system ( e.g. Purchase module, Cash Disbursements module, etc.). Data letters, numbers or symbols representing information suitable for processing, communicating or interpreting. A computerized data is stored in files, which are composed of records. Records are composed of fields. Data Dictionary a reference containing information about the name, source, purpose and a narrative explanation of each data element in the system; a table in a database that stores the names, field types, length, and other characteristics of the fields in the database tables. An active data dictionary is automatically updated as changes occur in the database. The data dictionary may maintain information on system hardware, software documentation, users, and other aspects. ESAHca Data Type defines the type of data stored in a field such as character, numeric or date. .dbf (database format) dBASE's underlying file format that is widely used in many other applications needing a simple format to store structured data. Disc/Disk a round plate or platter on which data can be encoded; a secondary data storage medium which is either a magnetic disk (diskette or hard disk) or an optical disk. A diskette may look like a small stereo record, usually 3.5 inches in diameter or in some cases, 5.25 inches. Hard disks usually have more storage capacity than diskettes and also offer faster access to the data they hold. With large computer systems, hard disks are often contained in disk packs. An optical disk uses a laser beam to store large volumes of data inexpensively. Download retrieval of files from another computer, probably a larger computer or a host computer to be sent to the computer of the user who requested the file. DVD-R (Digital Video Disc-Recordable) a disc which has a larger storage capacity than a CD-R, EBCDIC (Extended Binary Coded Decimal Interchange Code) an extension to 8 bits of BCDIC, an earlier 6-bit character set used on IBM computers. Field Name the unique name describing the data in a field. Field Length the maximum number of characters that can be contained in a field. Fields or Elements specified areas of a record used for storing a particular class of data. A field is a sub-unit of a record. Field Start Position the starting point of particular character in a field. File Record Layout contains information such as record lengths, field positions, data types, and field descriptions. Files named collection of records stored or processed as an individual entity. Files are composed of records. Flat ASCII refers to flat files that are composed of characters and spaces not necessarily arranged in any particular order. ScHADI Flat File is a computer file that is usually read or written sequentially and does not have indexes. FTP (File Transfer Protocol) a program or net access method which allows transfer of files from a remote computer to another computer. FTP Server (File Transfer Protocol Server) a network server program or computer which responds to requests for files via FTP. A busy internet archive site may have one or more computers dedicated to running FTP server software. These will typically have hostnames beginning with "ftp" , e.g. ftp.denet.dk. Importing a File into ACL transferring a file generated by another program to ACL. Input File Definitions/Edit Table Layout record layouts used to describe the structure and content of a data file and to specify where the data file can be found. Key a value used to identify a record in an index database as opposed to a flat file with no keys and is read sequentially. Location (as in memory location) a byte, word or other small unit of storage space in a computer's main memory that is identified by its starting address and size. Log File records all commands and results obtained during the data analysis. It can be used to retrace steps and repeat earlier results. It also provides an excellent resource for the generation of reports. Microcomputer Files files from a personal computer (PC). .mdb the database file format of Microsoft Access. Protocol a set of rules for the exchange of data between a terminal and a computer or between two computers. Record Length the maximum number of characters that can be contained in a record. Records collection of related fields containing data items grouped for processing. Records are subunits of a file. Secondary Data Storage Medium additional storage space separate from built-in memory. TcSHaD Sesam (a.k.a. Eskort computer audit) a commercial audit software developed by Swedish Tax Administration. Tape a secondary data storage medium which comes in reel or cartridge, similar to a tape that is played on a tape recorder. Magnetic tape is usually used for backup purposes for "data insurance" because tape is inexpensive. .wkl the spreadsheet file format of Lotus 123 IV. Preliminary Audit Work A. Pre-Audit Analysis Prior to examining the taxpayer's books of accounts through the use of CAATTs, analyzing the return is essential to identify potential issues and items for clarification with the taxpayer. The RO needs to familiarize and understand the taxpayer's business operations and accounting processes in connection with CAS or components thereof. The RO also needs to gain an understanding of the used accounting system initially based on documentation included in CSET dockets compiled during the processing and issuance of permits to adopt the CAS or components thereof. B. Work Planning This stage includes the preparation of an audit plan. (Refer to Appendix 1 for a sample audit plan.) The audit plan shall include the audit objectives, audit procedures to be undertaken and the timeframe for the audit. In preparing for the application of CAATTs in the tax examination, the RO shall do the following tasks: a. Understand the taxpayer's Computerized Accounting System (CAS) or components thereof. b. Identify the type of tax liabilities of the taxpayer. c. Obtain the relevant documents and reports from CSET relative to the issuance of permit to use CAS, or components thereof, to: i. Understand the interfaces between application systems where the identified significant transactions are being processed; ii. Understand the formula for programmed computations; iii. Assess whether these formulas are in accordance with the tax laws and regulations; acITSD iv. Identify the relevant data needed by the system in performing such computations; v. Identify the table/files where such relevant data are located; and vi. Understand the retention policy of the taxpayer in order to assess the availability and recoverability of historical data needed. d. Examine the annual affidavit submitted by the taxpayer after each year-end to demonstrate that backup files are being tested for readability and completeness, and can be made available to the Bureau for examination anytime. Although the CAATTs application may vary on a case to case basis, this Manual provides a list of potential CAATs applications that a RO should follow/execute (Appendix 5 and discussion under "V. Examination Proper"). These CAATTs applications will now be the basis of the RO in the preparation of a checklist of files/data to be requested from the taxpayer which shall be attached to the Letter of Authority (LA) to be served. (Refer to Appendix 2 for the sample letter request for data and Appendix 3 for checklist of files to be requested from the taxpayer depending on industry type.) C. Contact with the Taxpayer Within three (3) days after approval and signing of the Letter of Authority (LA)/Audit Notice (AN) by the authorized revenue official, the LA/AN shall be served to the taxpayer, including the list of requirements (Appendix 3) and Survey Sheet* (Appendix 4) for their accomplishment. The RO may request for a systems overview discussion with the taxpayer's Information Technology (IT) personnel. After the overview, the RO shall: 1. Update information on the CAS or components thereof; 2. Identify unreported significant changes to CAS or components thereof and other pertinent data that will be useful in assessing the feasibility of the CAATTs applications; 3. Document unreported changes in the CAS or components thereof to update the CSET documentation and note the violations committed; and 4. Confirm the contents of the files to be requested, expected file size, date of submission, and other related information needed. aSCDcH * The survey sheet shall also serve as a guide for the RO during discussion to the taxpayer's authorized IT personnel. D. Developing CAATTs Application Prior to discussion with the taxpayer, the RO has already listed CAATTs' applications to be used. However, with the information obtained during the discussion or meeting with the taxpayer, the RO shall determine their applicability. At this point, the RO shall: a. Prepare preliminary CAATTs Design; and b. Draft a high-level CAATTs flowchart depicting the input files, the major processing steps, and the expected output files/reports. A narrative description of the key steps to be performed during the execution phase may be prepared. This will and in building commands and finally executing them. E. Acquiring the Data (Initial and additional request) The following steps shall be undertaken by the RO in relation to acquisition of data: 1. Ascertain the completeness of the following data as requested from the taxpayer: a. Data files; b. File layout; c. Table of valid codes (if necessary); d. Control totals (record count and amount totals); and e. Reports against which control totals may be reconciled. 2. Ensure that files received from the taxpayer conform to the following preferred file formats (Appendix 7): a. DBF files; b. Delimited text files; and c. Flat text files. TcDHSI 3. Perform data file conversion using available software, if necessary (such as ACL, Sesam, Word, Excel, Access, etc.) F. Verify Data Integrity The RO shall: 1. Reconcile control totals with report totals to clear any discrepancies between these amounts. 2. Count the number of records to ensure that the files received are complete and accurate. 3. Check and ensure that no unusual items exist. Unusual items may include the following: a. Duplicates; b. Gaps; c. Transactions with negative amounts; d. Transactions with invalid codes; and e. Transactions outside the period under examination. 4. Clarify with the taxpayer the existence of unusual transactions. Depending on the justification of the taxpayer, the RO may opt to isolate these transactions as they may require different parameters and conditions from those of regular items, or he may automatically treat them as invalid items, hence considered exceptions. 5. Upon establishing the completeness and accuracy of data files and clarifying unusual items, analyze further the data files. 6. Request for another download of the files, in case the files received do not meet the data integrity test. The discrepancies may be caused by errors on downloading or conversion form one format to another. V. Examination Proper General Procedures In addition and/or in lieu of the traditional procedures performed during tax examinations, the RO shall execute the following minimum CAATTs' application/s to carry out the audit plan: ETIDaH A. Determine differences during the test run if these are due to faulty program logic in the CAATTs application or due to taxpayer's system error. If the discrepancy is caused by faulty program logic, abnormal input data not considered in the computation, or incorrect record layout, modify and rerun the application as deemed necessary until such time that the CAATTs application proves to be responsive of the CAATTs objectives and tested to provide accurate results. Test run may be applied in any of the following: 1. The whole data file, if there is only one file provided; 2. One of many files provided, e.g., 1st month in a group of monthly files; and 3. A subset of the data file, it the whole file is very large. B. Execute the applicable CAATTs' application (Note: Always keep track totals of record count and amounts to ensure that the data is processed completely; and Document each CAATTs procedure while executing CAATTs, especially in repeated tests.) 1. General Ledger a. Obtain the transaction files that interface with the general ledger which should contain balances of each account together with transaction history, various references and descriptions. b. Understand and analyze coding structures of more complex general ledgers which have segmented account code for different purposes. Transaction data should include sources, dates, accounting month, transaction references and posting reference. Sophisticated ledgers will hold each individual posting to allow inquiries, analyses and "drill down". c. Generate ledger balances i. Summarize totals of entries generated by different sources/modules ( e.g., purchase or sales ledger, journal vouchers, etc.); ii. Foot (total) the balances to ensure they balance to zero. Note: some ledgers include memo amounts which may need to be excluded; and iii. Total (or summarize) transactions by account. HITAEC d. Perform Exception Tests: i. Test for transactions with dates outside the taxable period under audit; ii. Analyze per account and look for unusual debit/credit entries and consequent tax exposure; and iii. Test for duplicate postings. e. Match and Compare generated account groupings against balances per audited financial statements. Document unreconciled discrepancies, if any NOTE: Some General Ledgers hold non-financial data, e.g., employee numbers, for use in management reporting and apportioning of costs. There will be some form of type code to identify these and they should be excluded from the computation of the trial balance. 2. Sales Generally a sales history file should be available, although on some systems, transactions are archived monthly. In this case, arrangement/s will have to be made to copy and save the file each month. The Append Databases feature of the Audit Software can then be used to create an annual file (or whatever period is required) from the monthly files. It is worthwhile conducting some analyses of sales by appropriate headings and then using exception testing. a. Analyze i. Analyze sales by month, category or customer (for VAT or percentage tax analysis); and ii. Analyze open orders and open invoices by customer. b. Generate ledger balances of sales transactions i. Reproduce sales summaries and posted totals; ii. Test pricing and discount computations on invoices; and iii. Relate between items shipped and items booked as sales. EHACcT c. Perform Exception Tests i. Extract all sales data for audit by customer, product, region, etc., and generate exceptions pertaining to sales prices less than expected for product type or large discount given (transfer pricing and large credit notes). d. Look for Gaps and Duplicates i. Test for missing sales invoice numbers; ii. Test for missing shipping/delivery receipts; and iii. Test for duplicate invoices (either duplicate invoice number, shipping order number or customer and detail/amount). e. Match and Compare i. Match cash receipts to receipts posted to the Accounts Receivable ledger for credit transactions. Identify any unmatched records. 3. Inventory Inventory can vary in volume and cost within organizations. Where inventory is a significant item, audit testing is used to provide assurance on existence, completeness (cut-off) and valuation. a. Analyze and Summarize i. Analyze high value transactions ( e.g., input tax verification); ii. Summarize stock on-hand by group, location, type, etc.; and iii. Summarize products by group, location, type, etc. b. Generate Ledger balances relative to Inventor i. Generate report on products in order of profitability; and ii. Test clerical accuracy of totals and extensions. c. Perform Exception Tests SDTIaE i. Identify stock acquired from associated companies; and ii. Isolate inventory items with cost greater than retail price, with zero/negative quantities, or with zero/negative prices. d. Look for Gaps and Duplicates i. Test for duplicate item numbers or descriptions. e. Match and Compare i. Compare cost to selling price to look for valuation discrepancies. 4. Payroll Payroll auditing is an excellent application of the Audit Software. The main objective is to check validity and accuracy by testing existence of employee, correctness of pay and accuracy of withholding taxes made. There are many regulations and taxes associated with payroll and compliance therewith can be checked. Privacy concerns/confidentiality may limit verification but the following common tests may be applied: a. Analyze and Summarize i. Analyze and summarize costs for special pay, overtime, premiums, etc.; and ii. Summarize payroll distribution for reconciliation to general ledger. b. Generate ledger balances of Payroll-related accounts i. Total gross pay, net pay, deductions and any other value fields; ii. Check computation of gross pay; iii. Check computation of taxable/non taxable pay; iv. Classify employee by position; v. Classify and summarize employees' pay by cost center and compare against MCIT; vi. Recompute withholding taxes and compare against remittances; and DacASC vii. Compare payroll master file with payroll list or Alpha List. c. Perform Exception Tests i. Extract all employees not included in the employee masterfile; and ii. Extract all employees with missing Tax Identification Number (TIN). d. Look for Gaps and Duplicates i. Duplicate employees' TIN on payroll file; and ii. Duplicate names. 5. Fixed Assets Fixed Asset registers vary in the detail of information and volume of records. In organizations with significant volumes of plant and machinery, the Audit Software can be used to scan for problem items. a. Analyze i. Provide a total of fully depreciated assets still in use. b. Generate ledger balances on Fixed Assets related accounts i. Segregate the file providing the total cost, depreciation and net book values and net recoverable value; ii. Recalculate depreciation figures; iii. Prove totals of additions and disposals and recalculate profits or losses on disposal; and iv. Segregate impairment losses. c. Perform Exception Tests i. Identify items with unusual movements in Net Book Value ( e.g., appraisal increment charged to depreciation); ii. Supply details where the depreciation rate is unusual; prcd iii. Test for dates in the appropriate period; iv. Identify assets transferred or acquired from group companies; v. Identify items with zero or negative cost and items where depreciation exceeds cost; vi. Identify items where description does not match coding; vii. Extract assets with useful lives or depreciation rates beyond set norms; viii. Identify fully depreciated items and assets higher than replacement costs; ix. Analyze changes in capital asset accounts and verify existence of any sale, exchange or disposition of fixed or capital assets during the year under audit; and x Determine assets held for rent or lease to tenants, and extract the amount of rental income received on these properties. d. Look for Duplicates i. Test for duplicate reference numbers. e. March and Compare i. Compare book and tax depreciation and indicate variances; and ii. Compare useful lives of assets by category. 6. Accounts Receivable (AR) Tests of Accounts Receivable are performed to establish existence, completeness and valuation. Items of particular concern are old unpaid invoices, unmatched cash and large balances. These can all be picked up with exception tests. a. Analyze and Summarize i. Profile accounts using stratification to identify large accounts and the proportion of value in the larger items; and TCacIE ii. Generate summaries by customer, invoice, amounts, products, etc. b. Generate ledger balances of AR-related accounts i. Total the file. Separate debits and credits; ii. Check transaction totals to the balance on each account; and iii. Show variances between delivery documents and invoices. c. Perform Exception Tests i. Report credit balances; ii. Identify unmatched cash or credits; iii. Test for items with shipping dates or numbers outside the expected range; iv. Identify part payments of accounts or invoices; v. Identify invalid transaction types; and vi. Identify high value credit notes, balances, and invoices. d. Look for Gaps and Duplicates i. Identify for duplicate invoices, credit notes or receipts; and ii. Report gaps in sequences of invoices and credit notes generated 7. Accounts Payable (AP) It is important to establish that liabilities are neither understated nor overstated. Current standards also look for potential fraudulent activities in the accounting systems. Many accounts payable tests also relate to supplier master file details. This often resides on a different file of the detailed ledger items. Apply the common tests as follows: HCaEAT a. Analyze and Summarize i. Reconcile check register to disbursements by vendor invoice; and ii. Summarize invoices by supplier to prove individual balances. b. Generate ledger balances of AP-related accounts i. Total the file. Separate debit and credit balances. c. Perform Exception Tests i. Identify debit balances; ii. Identify unusual stand-out data; and iii. Identify and total liabilities for goods received and not yet invoiced. d. Look for Gaps and Duplicates i. Test for duplicate payments/invoices. e. Match and Compare i. Match subsequent period payments against period and balances to identify unmatched payments; ii. Compare invoices posted against purchase order amounts; and iii. Compare recurring monthly expenses to paid invoices. Moreover, despite the above enumerations, the RO is not precluded from applying other techniques which are deemed necessary in a particular case. C. Generate a report on the items with discrepancies (audit findings). Investigate and resolve the issues with the taxpayer. For example, the RO may obtain copies of supporting documents ( e.g., sales invoices, official receipts, delivery receipt, sales reports, collection reports, etc.) relating to the exceptions. Additional Procedures for Selected Industries After conducting the minimum general procedures discussed in the preceding paragraphs, the RO is mandated to execute necessary CAATTs' application relevant to the taxpayer's industry. Please refer to Appendix 5 for this. DISaEA VI. Documentation and Reports Documentation exists in every phase of an audit cycle, from preliminary works to the examination proper, since one of the best practices in audit is "documenting while working". However, in this stage, the documentation and summarization that need to be done pertains to the results of the CAATTs application that will either form part of the audit report, or as supporting papers to the audit report. The documents prepared should be collated and retained in a manner that will be understood by other ROs who are not part of the original examination, other authorities within the Bureau, and third parties who might challenge the results of the tax audit. In a computer audit, the analysis conducted by the RO is in electronic form (stored in CD-R or DVD-R or any optical media), and the tests are documented through a "log file", which can only be seen when an authorized third party interested to view the audit case looks into it. Since the CD-R should form part of the audit docket, the RO must affix his signature on the "label" side of the CD-R. The standard contents of the CD-R to be prepared by the RO are the following: Working Paper (includes analysis of data) Planning Memo (containing CAATTs objectives, scope and output) List of CAATTs objectives and CAATTs application Computation of findings Commands used to generate findings List of Identified Critical Files and Fields Necessary for CAATTs CAATTs System Flowchart Reconciliation/Validation Report of Files Obtained (includes control totals) To view the contents even without opening the CD-R, a printed copy of the "Table of Contents" of data stored on it, shall form part of the case docket. ROs are required to make a report after the computer assisted audit has been conducted. The documenting and summarizing done will aid the RO in making a good audit report. To make a good audit report, it should contain the reporting requirements prescribed under existing revenue issuances and audit manuals, provided, these will not duplicate the herein prescribed contents of the working paper in CD-R, ( i.e. if the existing revenue issuances or audit manuals require the schedules of sales and purchases, and these schedules are already in the Working Paper in electronic form, there is no need to attach schedules in hard copies). acHTIC Hereunder is the list of the basic documents that need to be attached to the case docket containing the audit report, numbered in order of appearance (Item No. 1, being the first document at the bottom of the case docket): 1. Copy of the Letter of Authority/Audit Notice issued; 2. Copy of the Letter-request for data, including the checklist of required documents/files and the duly-accomplished Survey Sheet; 3. Duly Validated Tax Returns/Declarations; 4. Proof of Tax payment, including Tax Debit Memo/Tax Credit Certificates; 5. Agreement Form (for agreed assessments); 6. Notice for an Informal Conference/Post Reporting Notice with summary of findings (for non-agreed assessment); 7. Comparative Report of Deficiency Tax Paid/Assessed, if applicable; 8. BIR Form 0500 Series (Audit Reports); 9. Narrative Memorandum Report; 10. Table of Contents; 11. Docket Locator Form; 12. RO's Activity Report/Log Sheet; 13. CD-R prepared lay the RO (with table of contents in printed copy); and 14. Source Data in CD submitted by the taxpayer, with his signature. As prescribed under RR No. 16-2006, the ROs should keep source data and output files in CD until the report of investigation and the tax docket of the case has been reviewed and approved by the authorized Revenue Official. The ROs shall then return to the taxpayer their computerized accounting books/records intact and in their original form upon such termination, otherwise, the source data as well as the back-up electronic accounting documents and records should be retained. ( e.g., litigation reference). VII. Review of Audit Report AHcaDC Reviewing an audit report pertaining to tax audit is a mandatory procedure prior to the approval of the audit reports by authorized officers of the Bureau. To review an audit performed using CAATTs, the reviewer must also have knowledge on how the used audit software runs. Listed hereunder are the minimum review procedures that a reviewer shall follow/execute: 1. Identify potential CAATTs application per audit plan and compare these to the CAATTs application done by the auditors; 2. Check if the CAATTs application done by the auditors meet the audit objectives; 3. Examine whether the procedures performed are in accordance with the CAATTs design, to ensure that the procedures are properly executed and appropriate parameters or conditions are applied; 4. Examine the details of the CAATTs flowchart and compare them with command log file; 5. Validate the final results of the CAATTs processing for accuracy; 6. Take note of any deviations in the results of CAATTs applications review the documented reports; 7. Prepare summary of finding for discussion with the auditors; and 8. Affix signature/initial to pertinent work paper documentations. APPENDIX 1 Sample Audit Plan Timeframe for audit 60 days I. SALES A. CAATTs Objectives: 1. To check undeclared or unrecorded sales. B. CAATTs Procedures: 1. Compare delivery receipts with recorded sales; 2. 100% samples for vouching; 3. Check sequence and gaps of the sales invoices, debit/credit memos and delivery receipts; 4. Check consistency of branch codes in the sales file; 5. Determine debits to AR not coming from sales transactions; 6. Extract consigned goods for more than 60 days; 7. Compare consigned goods (net of returned items) with sales; 8. Check for unusual items ( i.e., unusual DMs); and 9. Compare Sales (adjusted for promo items) with Raw material purchases. II. VAT A. CAATTs Objective: To check accuracy of VAT liability. B. CAATTs Procedure: 1. Classify and summarize discounts and compare with VAT claims; 2. Compare customers with withholding code (withholding agents) vs. tax credit claims in the ITR; 3. Compare sales invoice prices vs. price list; and 4. Extract purchases from suppliers without TIN. III. Expenditure A. CAATTs Objectives: 1. To check overstatement of expenses; and 2. To verify accuracy of VAT input claims. B. CAATTs Procedures: 1. "Annualized" income vs. withholding tax; 2. Sequence check (receiving report, A/P voucher, check register); and 3. Classify and summarize by type: a. Service b. Operating c. Capital goods d. Goods e. Local/Imported f. Raw Materials key/non-key g. Supplies h. VAT/non-VAT 4. Check for Receiving Reports without suppliers' invoice; 5. Classify and summarize expenses by account code then compare with Alpha List, then with Summary List of Purchases (SLP); CAaDSI 6. Classify and summarize expenses by location; 7. Search for A/P debiting Inventory without Receiving Report and PO reference; 8. Aging of A/P; and 9. Check for unusual items ( i.e., unusual DMs). IV. Payroll A. CAATTs Objectives: 1. To check the accuracy of withholding tax. B. CAATTs Procedures: 1. Classify employees by position; 2. Classify and summarize employees by cost center, by branch, and compare against MCIT; 3. Recompute withholding taxes every month and compare against remittances; 4. Classify and summarize employees by income types; 5. Compare payroll master file with payroll list or Alpha List; and 6. Recompute withholding taxes at year end using the Alpha List. APPENDIX 2 Sample Letter Request for Data The President _____________________ _____________________ Attention: The Chief Financial Controller/The Tax Compliance Officer/The Chief, MIS EaHIDC Subject: Data Files request for Tax Audit per Letter of Authority No. 0000000 Sir: Pursuant to our Letter of Authority No. 0000000 dated ____________ for the examination of your books of accounts and other accounting records for the year beginning _______________ to _____________ we hereby request for the reproduction of the following master and transaction files in electronic form in accordance with Revenue Regulations 16-2006 dated August 16, 2006. General Ledger (transaction file) Subsidiary Ledger (transaction file) Other modules integrated in the system: _______________________________ _______________________________ _______________________________ _______________________________ We request that each of the above will be submitted to us in separate files, reflecting all transactions captured by the system from _____________ to __________ and that the data should be given in the following formats: delimited text files, .mdb or .dbf file on a CD-R or DVD-R per module. Together with the data, please document the file record layouts, including the skip length and record lengths. For each field, document the field name, field start position, field length, data type, and formats of numeric and date items. We also require documentation of key control information, including the transaction cut-off date and time, the number of records in the file and control totals of numeric fields where applicable. Also include in separate files, the Chart of Accounts and source system codes. This represents our initial data request associated with this audit. We anticipate a necessity for subsequent data requests, but we will endeavor to keep these requests to a minimum, in consideration of your time and resources. Likewise, we are requesting for a presentation of the overview of your complete computerized accounting system to assist us in our CAATT analysis. We appreciate your assistance with this data access, and look forward to working with you during the course of our audit. We expect our files on or before _______________ which shall be submitted in softcopy and duly signed by an authorized officer of your company. If you have any questions, please do not hesitate to contact us at _________. Very truly yours, (Authorized Revenue Officer) APPENDIX 3 Checklist of files and fields to be requested from the taxpayer FILES TO BE REQUESTED AND RETAINED I. MANUFACTURING INDUSTRY A. Revenue 1. Sales Transaction File 2. Customer Master File B. Expenditure 1. Purchase File 2. Vendor/Supplier Master File C. Payroll 1. Payroll Transaction File 2. Employee Master File D. Conversion 1. Inventory Master File 2. Finished Good Master File 3. Product Transfer File 4. Sales Invoice File 5. Bill of Materials E. Fixed Assets 1. Depreciation Expense File 2. Fixed Asset Master File F. Financial Reporting 1. Extract JV's and look for unusual items/transactions II. RETAIL INDUSTRY 1. Suspense Account File 2. Sales Transaction File 3. Purchase File 4. Inventory Master File HCSEIT 5. Supplier Master File III. INSURANCE INDUSTRY 1. Transaction File 2. Policy Master File a. Policy Number b. Plan Code c. Plan Holder's Name d. Mode of Payment e. Full Premium Paid Date f. Sum Insured of Plan g. Modal Premium (Premium) h. Policy Date i. Accounting Entry Date j. Effective Date k. Maturity Date l. Policy Status m. Cancellation Date n. Cancellation Code 3. Premium and Commission Data File a. Policy Number b. Plan Code c. Policy Status DEcSaI d. Renewal Number e. Agent Code f. Agent Name g. Agent Commission Transaction Number h. Modal Premium (Premium) i. Total Commission Amount j. Amount of Commission Processed k. Payment Detail Status l. Policy Year m. Commission Production Date n. Policy Date o. DST p. Withholding Tax 4. Policy Transaction File amendments 5. Agent's Commission Expense Transaction File 6. Reinsurance File a. Policy Number b. Plan Holder's Name c. Effective Date of Policy d. Maturity Date e. Accounting Entry Date f. Type of Reinsurance (Treaty/Facultative) THcEaS g. Sum Insured of Plan h. Assuming Company (Outward Reinsurance) i. Type of Assuming Company (Authorized/Unauthorized) j. Commission Income k. Amount of Claims Recovery l. Issuing Company (Inward Reinsurance) m. Type of Issuing Company (Authorized/Unauthorized) n. Commission Expense o. Amount of Share in Claims/Losses 7. Journal Voucher Transaction 8. Claims and Losses File a. Policy Number b. Plan Code c. Effective Date of Policy d. Plan Holder's Name e. Sum Insured of Plan f. Accounting Entry Date g. Date when Claims Filed h. Claims Expense i. Date when Claims Paid j. Amount of Claims Paid k. Claims Recovery on Reinsurance IScaAE IV. BANKING and OTHER FINANCIAL INSTITUTIONS 1. Customer Master File 2. Deposit Transaction File 3. Withdrawal File a. Certificate Numbers b. Date of Placement c. Pre-termination Dates d. Maturity Dates e. Principal Amount f. Interest Rates 4. Deposit Interest Rates File 5. Deposit Liability File a. Taxable Deposits i. Demand Deposits ii. Savings Deposits (monthly) With auto transfer Without auto transfer iii. Time Deposits iv. NOW (Negotiable Order of Withdrawal) Accounts v. Accounts Special Savings Deposit b. Non-taxable Deposits i. Demand Deposits IDaCcS ii. Savings Deposits (monthly) With auto transfer Without auto transfer 6. Loans Master File a. Loans by classification i. Commercial ii. Industrial iii. Real Estate/Home Mortgages iv. Auto Loans v. Agri-Agra Loan vi. Credit card operations vii. Loan Account Number viii. Borrowings (Loaned Amount) ix. Principal x. Terms xi. Interest Maturity Date Pre-termination Date 7. Loans Transaction file/Payment Transaction file a. Account Number b. Amount c. Mode of Payment ETHCDS d. Rate e. Terms f. Date 8. Statement of Loans File a. Account Number b. Date c. Amount d. Mode of Payment e. Other Charges 9. Notes File 10. Letter of Credits File 11. Transaction File (payments) V. TELECOM INDUSTRY 1. Invoice File 2. Statement of Accounts File 3. Billing Summary File 4. General Ledger (GL) File 5. Card Database 6. Subscriber Master File 7. Tariff Table 8. Settlement Transaction File 9. Payable File CDHSac 10. AUC (Asset Under Construction) File 11. Billing Transaction File 12. E-wallet database (dealers' balances) 13. Call Detail Record (CDR) APPENDIX 4 SURVEY SHEET The following is a survey questionnaire of your computer system pursuant to Letter of Authority No. __________ dated ________. This questionnaire has been designed to collect the information for an efficient audit of your accounts. Please answer the questions to the best of your knowledge. If you wish to complete this questionnaire electronically, we can supply you with an electronic version on diskette or send it to you by E-mail via Internet. If you need assistance or additional explanations, we will be pleased to help you. You may contact us by telephone or by E-mail: your name@your domain. com Taxpayer Identification Name ___________________________________________ Address ___________________________________________ Telephone No. ___________________________________________ Fax No. ___________________________________________ E-mail Address ___________________________________________ TIN ___________________________________________ Taxpayer's representatives for tax and/or computer issues Name Title Telephone No. ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ Are accounting records partially or totally computerized? THaDAE [ ] [YES] Please answer the following questions [ ] [NO] You don't have to answer the following questions Computer 1. Indicate the type of computer used over the last 4 years: Brand Name Model Operating From To System (mm/yyyy) (mm/yyyy) PC Stand-alone _________ _________ _________ _________ _________ PC Network _________ _________ _________ _________ _________ Mini-computes _________ _________ _________ _________ _________ _________ _________ _________ _________ _________ Main frame computer _________ _________ _________ _________ _________ _________ _________ _________ _________ _________ 2. Presently, if you have a mini or main frame computer, can it be connected to a personal computer and how? (Examples: Local Area Network, Serial or Parallel Port, Infrared, Modem. . .) Types of connection [ ] [Yes] _______________________________________________ [ ] [No] _______________________________________________ Software 1. Which Operating System do you use? Give details. Name of the Operating Version/Vendor Comments System __________________ __________________ __________________ __________________ __________________ __________________ 2. List type of accounting software used over the last 4 years: Name Version From To (mm/yyyy) (mm/yyyy) Commercial Software (Note 1) ____________ ____________ ___________ ___________ __________ ____________ ____________ ___________ ___________ __________ ____________ ____________ ___________ ___________ __________ ____________ ____________ ___________ ___________ In House Software ____________ ____________ ___________ ___________ __________ ____________ ____________ ___________ ___________ __________ ____________ ____________ ___________ ___________ Note 1: Edwards, Ex etc. 3. Do you use a programming language? Specify [ ] [Yes] _______________________________________________ [ ] [No] _______________________________________________ Examples: Cobol, RPG, C, Basic, Visual Basic, etc. 4. Do you use a software package for data extraction and/or analysis? Specify [ ] [Yes] _______________________________________________ [ ] [No] _______________________________________________ Example: IDEA, ACL etc. Storage & Backup 1. Please detail your backup policy and software used, if any. 2. Please specify in detail storage media and data transfer mechanisms available. A few examples are given below. Magnetic Tape, 9 or 18 or 36 tracks _____________________ DAT cartridge 4 or 8 mm _____________________ Streaming Tape 1/4 in. _____________________ Removable disk (specify brand, model and capacity; examples: Iomega Zip Disk 100 MB, Iomega JAZ 1 GB, Syquest Sytel 1.5 GB) _____________________ CD-R (Compact Disc Read Only) _____________________ DVD-R _____________________ Diskette 1.4 MB _____________________ Others (specify) _____________________ 3. Which are the file formats available for data transfer? (Pls. check appropriate box) Dbase (dbf) ____________________________________ Lotus (Wks) ____________________________________ Excel (xls) ____________________________________ ASCII/EBCDIC fixed length ____________________________________ ASCII Delimited ____________________________________ Others ____________________________________ Record Retention 1. Provide information related to retention of computerized accounting records: HETDAa Accounting Ledgers Retention Retention Medium Comments & Beginning Other Records (mm/yyyy) General Ledger ____________ ____________ ____________ Accounts Payables ____________ ____________ ____________ Accounts ____________ ____________ ____________ Receivables ____________ ____________ ____________ Sales ____________ ____________ ____________ Fixed Assets ____________ ____________ ____________ Excise Records ____________ ____________ ____________ Others ____________ ____________ ____________ Connectivity 1. Do you use Electronic Data Interchange (EDI) technology to exchange electronically documents with other business partners? Document types electronically exchanged [ ] [Yes] _______________________________________________ [ ] [No] _______________________________________________ 2. Are you connected to INTERNET? Your E-mail address Your Web site address [ ] [Yes] _____________________ www._________________ [ ] [No] _____________________ ______________________ Comments: ________________________________________________________ APPENDIX 5 List of Potential CAATT's Application by Industry The following topics enumerate the suggested computer audit procedures and techniques based on commonly used application systems by industry. The Revenue officer may use the following audit techniques when applicable. aESTAI I. MANUFACTURING INDUSTRY A. Revenue 1. Relate delivery receipts with recorded sales; 2. Check Sequence and Gaps of the sales invoices, debit/credit memos and delivery receipts; 3. Classify and Summarize discounts and compare with VAT claims; 4. Extract debits to AR not coming from sales transactions; 5. Filter and Total consigned goods for more than 60 days; 6. Compare consigned goods (net of returned items) with sales; 7. Summarize the Account for the airway bills and/or bills of lading and compare using Relate or Merge with recorded export sales; 8. Check liquidation reports covering imported goods/merchandise on consignment (goods/merchandise owned by foreign customers) versus export sales to determine that no consigned importations were diverted to local markets or mingled with imported goods/merchandise owned by the domestic taxpayer; 9. Classify or Summarize sales discounts (per sales/revenues classification, e.g. taxable, zero-rated, exempt and toll/processing fees) and compare with ITR and VAT returns; 10. Classify or Summarize debit/credit memos representing sales returns (per sales/revenues classification, e.g. taxable, zero-rated, exempt and toll/processing fees) and compare with ITR and VAT returns; 11. Compare Sales in quantity with inventory sold (deemed sales transactions); 12. Match sales per customer vs. tax credits claimed in the ITR (e.g. Summary list of sales vs. Summary Alpha List of Withholding Taxes (SAWT)); 13. Filter and Total products available for sale still covered with incentives and compare with declarations per ITR and VAT returns. ( e.g. PEZA, pioneer industry); 14. Check for unusual items ( i.e., suspense account analysis); and 15. Test for the validity of Gross Profit Margin (sample by department). B. Expenditure 1. Annualize expenses and compare with annual alpha list of withholding taxes; 2. Extract the input taxes generated from the acquisitions/purchases of capital goods and compare the same with the amount of deferred input taxes on CAPEX per VAT returns; 3. Summarize income payments to non-resident companies such as trademarks/royalties to determine whether payments were subjected to withholding VAT, Income Tax, etc.; 4. Sequence/Duplicate Check (receiving report, A/P voucher, check register); 5. Search for Accounts Payable debiting Inventory without RR and PO reference; 6. Summarize cash disbursements by account, bank, group, vendor, etc.; and 7. Summarize and relate Monthly Summary List of Purchases (SLP) against Annual Alphabetical List of expenses subject to withholding taxes. C. Inventory Cost Analysis 1. Recompute and validate Work-In-Process Inventory; 2. Recompute and evaluate costing method of inventory; 3. Stratify balances by value brands; 4. Total the file, providing sub-totals of the categories of inventory; 5. Recompute any calculations involved in arriving at the final stock quantities and values; 6. Recompute materials and labor cost calculations on assembled items; 7. Identify any items with excessive or negligible selling or cost prices; 8. Test for movements with dates or reference numbers not in the correct period (cut-off); 9. Compare cost and selling price and identify items where cost exceeds net realizable value; 10. Reconcile inter-company transfers; 11. Extract pricing and receipt quantity variations by vendor and purchase order; 12. Identify duplicate purchase orders or receipts without purchase orders; and 13. Reconcile purchase orders and delivery receipts by comparing accrued payable to received items. II. RETAIL INDUSTRY A. Revenue 1. Relate delivery receipts with recorded sales; 2. Check Sequence and Gaps of the sales invoices, debit/credit memos and delivery receipts; 3. Classify and Summarize discounts and compare with VAT claims; 4. Extract debits to AR not coming from sales transactions; 5. Filter and Total consigned goods for more than sixty (60) days; 6. Compare consigned goods (net of returned items) with sales; 7. Summarize the Account for the airway bills and/or bills of lading and compare using Relate or Merge with recorded export sales; 8. Classify or Summarize sales discounts (per sales/revenues classification) and compare with ITR and VAT returns; 9. Classify or Summarize debit/credit memos representing sales returns (per sales/revenues classification) and compare with ITR and VAT returns; EHDCAI 10. Compare Sales in quantity with inventory sold (deemed sales transactions); 11. Match sales per customer vs. tax credits claimed in the ITR (e.g. Summary List of Sales vs. Summary Alpha List of Withholding Taxes (SAWT)); and 12. Classify and summarize gift checks per status (balance and expiry date). B. Expenditure 1. Annualize expenses and compare with annual alpha list of withholding taxes; 2. Extract the input taxes generated from purchases and compare the same, with summary list of purchases (emphasis on deferred input taxes on CAPEX); 3. Summarize income payments to non-resident companies such as trademarks/royalties to determine whether payments were subjected to withholding VAT, Income Tax, etc.; 4. Sequence/Duplicate Check (receiving report, A/P voucher, check register); 5. Search for Accounts Payable debiting Inventory without RR and PO reference; 6. Summarize cash disbursements by account, bank, group, vendor, etc.; and 7. Summarize and relate Monthly Summary List of Purchases (SLP) against Annual Alphabetical List of expenses subject to withholding taxes. C. Inventory Cost Analysis 1. Recompute and evaluate costing method of inventory; 2. Stratify balances by value brands; 3. Total the file, providing sub-totals of the categories of inventory; 4. Test for movements with dates or reference numbers not in the correct period (cut-off); 5. Compare cost and selling price and identify items where cost exceeds net realizable value; 6. Reconcile inter-company transfers; 7. Extract pricing and receipt quantity variations by vendor and purchase order; 8. Identify duplicate purchase orders or receipts without purchase orders; 9. Reconcile purchase orders and delivery receipts by comparing accrued payable to received items; 10. Check for unusual items ( i.e. , suspense account analysis); and 11. Test for the validity of Gross Profit Margin (sample by department). III. INSURANCE INDUSTRY The main transaction streams are premiums and claims although there are special areas such as re-insurance where THE AUDIT SOFTWARE can also be used successfully. Premiums The main tests are concerned with ensuring all premiums are raised and the correct rate is used. However, analyses of risk and exception tests of old or forward dated items are useful. A. NON-LIFE: 1. Account for the numerical sequence of insurance policies issued and account for any break in the series; 2. Total premiums received (net of cancellations) per premium register and compare against Output VAT per return and Insurance Commission Report and Income Tax Return; 3. Compute DST on total premiums written inclusive of returns and cancellations. Determine existence of insurance policies issued which are exempt from the payment of DST as provided for under Section 199 (a) of the Tax Code. Deduct the premiums on these exempt policies in computing the tax base; SaITHC 4. Extract reinsurance transactions; 5. Compute commission revenue/expense; 6. Extract all policies with current year claims and compute company's share of reinsured losses; and 7. Extract the transactions which are zero-rated and/or exempt from VAT. B. LIFE: 1. Look for Gaps and duplicates on policy number. Test-check the entries to the premiums receivable register. Verify existence of transactions affecting production that may have been omitted in the declaration of premiums from insurance policies written subject to income tax; 2. Total all collections of premiums subject to premium tax under Section 123 of the Tax Code; 3. Summarize policy claims against sum assured ( e.g., face value) "Claims" is a major expenditure in the insurance business. There is a considerable analysis available as claims can be matched back to policy details and there are usually various claim types, reason codes and risk codes; 4. Compute DST based on premium production; 5. Compute premium revenue based on active policy master records against declared amount; 6. Summarize payment of insurance premiums from cash surrender value of the policy under the "automatic premium clause" which is subject to income and Percentage Taxes; 7. Extract Policy Loans, premium receipts and dividend application to premiums and compute applicable DST/Percentage Tax; 8. Classify interest income based on sources in General Ledger as a basis for the computation of interest arbitrage; 9. Check whether recipient of commission expenses are employees of the company; Relate employee file against Annual Alpha List on EWT; EHTIcD 10. Reconcile all income payments subject to final withholding taxes with the amount of actual payments as reflected in the pertinent account in the audited financial statements such as interest expense, royalties, dividend declarations, etc. Segregate taxable from exempt transaction to determine the correct taxable base; 11. Extract a list of expired premiums during the period. Make an appropriate test to determine whether premiums are actually expired; 12. Extract breakdown of all insurance assumed by the company segregated by type of reinsurance contract/agreement (facultative or treaty contracts) and the corresponding commission expense claimed by the taxpayer; 13. Extract breakdown of all insurance ceded to re-insurers indicating thereat the name of the assuming company, the type of ceded reinsurance arrangement per company, amount of premiums accepted by each company and the corresponding commission income of the subject taxpayer; 14. Extract the total amount of ceded premiums in the Reinsurance Register and determine whether the amount of ceded premiums per Reinsurance Register tallies with the amounts reflected in audited financial statements, income tax returns, and annual statement submitted to the Insurance Commission; ECaHSI 15. Verify the accuracy of the computation of reserves that were set up at the end of the year under audit; 16. Review the covering lease contracts to determine the amount of rental and other pertinent terms and conditions reflected thereat. Check whether the declared rental income is in accordance with terms and conditions embodied in the contract; 17. Verify whether all claims for losses are covered by unexpired insurance policy and all these claims are filed during the taxable year; 18. Extract paid and unpaid losses. Consider the possibility of reverting such unpaid losses to income account; 19. Analyze date of incident to date claim reported as part of assessing claims incurred but not recorded; 20. Filter high value claims. Check for duplicate claims (same policy number and either same amount or same date of incident); and 21. Determine existence of any claim by the Philippine branch office of a foreign insurance company licensed to do business in the Philippines for allocated home office expenses. Determine the basis of allocation used and the reasonableness of the ratable share charged to the branch office. Check consistency in the application thereof from year to year. Scrutinize the details of allocated home office expenses and ascertain the propriety of charging said expenses to the Philippines branch office. Consider the possibility of disallowing allocation of charges to the account that have no direct or indirect benefit to the Philippine offices. IV. BANKS AND FINANCIAL INSTITUTIONS The use of Audit Software is on most areas of financial institutions including current accounts, personal and corporate lending, treasury functions and investment accounts. The usual guidelines on volume and depth apply. However in some banking systems, the volume of transactions can be too great to download to PC. It may be appropriate to download a segment of the data ( e.g. a region) or a selected number of fields. A. Internal Revenue Tax Liabilities CDHacE 1. Final Withholding Tax on Interest a. Formula: Average Daily Balance x Interest Rate = Interest Expense Interest Expense x Tax Rate = Withholding Tax b. Analysis: i. Extract Customer MF, Transactions File (deposits), Withdrawal File, and Rates File; ii. Recompute withholding tax on interest; iii. Compare result of computation with Summary Report; iv. Extract customers not subject to withholding tax; v. Recompute Income Tax of the bank; vi. Extract Loans MF, Transaction file, and Statement of Loans File; vii. Recompute withholding tax on interest; and viii. Compare result of computation with Summary Report. 2. Gross Receipts Tax a. Formula: Gross Receipts x Tax Rate = GRT b. Analysis: i. Total gross income subject to Gross Receipts Tax (GRT) such as interests, commissions/discounts from lending activities and income from financial leasing based on the remaining maturities of the instruments; ii. Segregate beginning and ending balances of loans/other income receivables and unearned discounts. Classify those receivables/discounts into accounts from taxable and exempt income accounts; iii. Ascertain that all income subjected to final tax are properly taken up at gross amounts; and iv. Test-check whether gross receipt tax rates applied on the interest payments received were adjusted when remaining maturities change, as in the case of pre-terminated loans. HTSAEa 3. Documentary Stamp Tax (DST) a. Formula: Documentary Stamp Tax Certificate of Deposits/Loan Documents/Bills of Exchange or Letters of Credit Issued/other transactions x Tax Rate = DST b. Analysis: i. Summarize the Debit/Credit transactions of the following on a daily basis (Section D of RAMO No. 2-00 dated February 28, 2000, for further reference): Certificate of Time Deposit Special Savings Accounts (SSA) Interbank-Loans Receivables and securities purchase under agreement to resell Investment Account Financial assets at fair value thru profit and loss Available for sale investments (held for trading) Held to maturity investments Loans and Receivables Age, interest and principal amount Sale of Property and Equipments ROPOA (Real and Other Properties Owned and Acquired) Investment in associates and joint venture Investment Properties Other Assets Liability Accounts TcIHDa Bills Payable Deposit Liabilities Segregation of borrowings from: Local Banks Foreign Banks BSP Others Manager's check and demand draft (per check issued) Subordinated debt Lower tier 2 capital (issue price or principal, maturity, discount or coupon rate) Held for Trading Investments (HTI) Hybrid capital securities Equities Surplus Reserves 4. Income Tax a. Formula: Taxable income x Income Tax Rate = Income Tax Due b. Analysis: i. Classify Regular Banking Unit (RBU) and Foreign Currency Deposit Unit (FCDU) Expenses; ii. Allocation of common expenses between RBU and FCDU Formula: use acceptable allocation method ( e.g., gross income, rate, per area or usage); iii. Identify allowable deductions, as well as direct cost for Minimum Corporate Income Tax (MCIT) purposes pursuant to RMC No. 04-2003; TaCEHA iv. Extract all regional charges claimed; v. Check computation of interest and bank charges, currency conversions; vi. Check whether bank charges are being booked as part of income (bank charges on dormant accounts); vii. Identify interest charges attributable to preferred stock; viii. Compare book and tax depreciation and indicate variances; ix. Identify fully depreciated items and assets higher than replacement cost; and x. Extract assets with useful lives or depreciation rates beyond set norms. B. Test Relevant Loans and Mortgages The following tests are relevant where the accounts are either commercial or personal loans: 1. Analyze and Summarize a. Stratify balances and create a summary file; b. Analyze balances and interest rates by risk rating; c. Analyze interest margin on accounts by type; d. Analyze loan portfolio by servicing officer, location, loan type, etc.; e. Summarize loan portfolio information based on data in any field; f. Summarize loans by collateral and purpose to analyze portfolio risk; 2. Generate ledger balances a. Total accrued interest, balance; b. Reprove arrears calculations and reporting; DICcTa c. Check interest and service fee calculations; 3. Perform Exception Tests a. Identify staff loans; b. Identify large loans; c. Identify loans with unusual interest rates; d. Identify balances greater than original advance; e. Identify negative balances; and f. Create custom reports on new, renewed and past due loans. 4. Match and Compare a. Using cross-matching techniques can pick up multiple loans to the same address; and b. Compare balances at different periods to identify movements. C. Other tests applicable in certain cases include: 1. Identify forward dated transactions; 2. Provide totals of forward dated transactions; 3. Identify overdue maturities; 4. Identify customers over their overdraft limit or customers with expired limits; 5. Identify dormant accounts and transactions thereon; 6. Re-perform currency conversions; 7. Provide details of currency exposure; and 8. Test for cut-off. V. TELECOM INDUSTRY 1. Extract the Receipts subject to VAT and compute VAT from the Billing/Invoice File; IcSADC 2. Filter overseas calls, overseas SMS and roaming services and compute Overseas Communication Tax from the Statement of Accounts File; 3. Extract and summarize unearned revenue and compare it with subscriber balance; 4. Compute interconnection revenues and interconnection payables from the Settlement transaction file. Check offsetting; 5. Extract Value Added Service (VAS) transactions of the Content Providers; 6. Review of customer deposits; 7. Recognize expired cards as income; 8. Analyze leased facilities leased expenses; 9. Analyze Dealer Sales and compute withholding tax; 10. Check capital expenditures greater than the threshold for deferred input tax; 11. Analyze components of AUC (Asset Under Construction); 12. Check transfer of AUC to Fixed Asset a. Recompute asset life/depreciation expense vs. GL 13. Check Fixed Asset Disposal; VI. DISTRIBUTION AND SERVICE Distribution and Service companies have many of the standard ledgers, payroll, accounts payable, etc. which can be key concerns. Typical area of tests include Sales Analysis: 1. Generate sales/profitability reports by sales representative, product, customer; 2. Recap product sales by region, customer, category, etc.; 3. Identify high volumes by region, customer, category, etc.; 4. Extract all sales data for audit by customer, product, region, etc.; TAaIDH 5. Compare ratios of current sales to open receivable (high-low; low-high); and 6. Summarize shipments by warehouse for product distribution analysis. APPENDIX 6 Use of Audit Software for Fraud Examination FRAUD EXAMINATIONS THE AUDIT SOFTWARE can be used to identify unusual and suspected transactions as part of a fraud investigation. This section does not present a complete guide for conducting a complete fraud investigation and checklists of audit tests only serve as an aide-memoire. There are a number of tools to prevent and detect fraud including personnel vetting, independent authorization of transactions and observation of employees. THE AUDIT SOFTWARE does not replace any of these techniques but adds a tool that is particularly useful in the right circumstances. Clearly the relevant information to check must reside on accessible computer files. Generally, the larger the volumes and the more detailed the information held, the more useful THE AUDIT SOFTWARE becomes. Using THE AUDIT SOFTWARE on copies of the files can be done without alerting those under suspicion and can build up evidence to prove what has occurred. However, it should be noted that there could be problems in submitting computer records as evidence to a court. Hence, expert advice should be sought if information gathered through THE AUDIT SOFTWARE needs to be submitted. Further, this guide does not cover procedures on how to deal with fraudsters for which specialist help should be sought. Most common areas of fraud are: I. Sales II. Payroll III. Purchases aSHAIC APPENDIX 7 Acceptable File Formats A. File Formats that can be imported in an audit software: 1. Data Files that are ODBC complaint such as: a. Oracle b. SQL c. Access d. Foxpro 2. Text File (ASCII or EBCDIC) a. Flat Text File b. Delimited Text File c. Excel d. Dbase e. XML f. Print Report Files 3. Native formats: a. AS/400PL/1 b. SAP J File Format c. AccPac File Format B. File Formats that can be exported: 1. Text File a. Flat Text File b. Delimited Text File aCcEHS 2. Excel 3. Lotus 123 4. Microsoft Access 5. Dbase 6. Word Merge File 7. Word Perfect Merge File 8. SML 9. Clipboard