AccountsPayable_AEAP2009

This is the AuditNet Standard Risk Control Audit Matix which incorporates formats used by many audit organizations in their documentation working papers. There are format templates for risk control, audit procedures, questionnaires and checklists. There is a blank workpaper and a report summary that can in used by audit organizations. AuditNet has prepared a monograph for guidance on preparing and developing audit work programs, checklists, questionnaires and matrices. The monograph is available to AuditNet subscribers. For more information go to www.auditnet.org

This is an analytic-enabled audit program, providing an exam ple of how analytics can be used throughout the audit process. Partnering with AuditNet, ACL has taken this audit program, provided by AuditNet, and simply integrated some example audit analytics into the appropriate program steps. As your organization's organization's testing needs may dictate changes to the audit program, so too might your needs for different or additional analytics. This workbook's tab tit led "Analytic Procedures" introduces the data sources that most organizations would need, need, but your needs needs may be different. The tab titled "ACL Steps" introduces the procedures that a user could execute to perform the analytic in ACL. To learn more about the imporance of implementing analytics within audit programs, please contact ACL.

Audit Program Area: Accounts Payable

1.

CONTROL OBJECTIVE Proper segregation of duties.

2. Obtain and review the detailed listing of trade accounts payable

4.

Evaluate reconciling items

Ensure that any adjusting entries resulting from reconciliations are properly authorized and posted to control accounts and are not fictitious. q

AUDIT PROCEDURES 1. Determine whether there is proper division of duties between the following functions: q Preparing creditor payments q Authorizing creditor payments Preparing creditor reconciliation q q Reviewing and approving creditor reconciliation. 2. Document above segregation of duty in t erms of persons performing above functions. 3. Conclude on whether proper segregation of duties exist. 1. Obtain and review a detailed listing of trade accounts payable balances by supplier (creditors ledger) 2. Assess the list for completeness of information 3. Inquire from management how liabilities for goods and services received but not yet invoiced are treated ie. Whether these are included in the list and what procedures are in place to ensure that the list is complete. 4. Trace totals per the detailed list to the general ledger and previous audit working papers if applicable. 5. Scan the above-mentioned list and investigate significant unusual items (e.g., debit balances and old unpaid invoices) 6. Test the mathematical accuracy of the detailed listing by casting amounts. 1. Obtain and review company procedures for preparing supplier reconciliation statements 2. Assess the adequacy of these procedures.

WP Ref

Auditor Initials

Time Spent

Date Date Checked Expected Finished Remarks By:

Perform data integrity testing on the Accounts Payable transaction data

n/a Review Accounts Payable adjustments and outstanding balances for authorization and accuracy

3.

5. q

6.

7.

Examine support for liabilities Test accuracy of the creditors listing Test accuracy of supporting documentation.

Inquire about unrecorded liabilities.

8. Examine files of unmatched delivery notes or invoices 9. Examine cut-off procedures to ensure that these are appropriate.

Examine selected reconciliations and perform the following: q take special note of all reconciling items. q Be alert for audit risks contained in the accounts for example, the existence of old unpaid invoices or uncleared debit advices which may indicate disputes with suppliers or t he inclusion of invalid invoices. q Examine support for any significant adjustments made by the division in reconciling supplier records with control accounts in the general ledger. q Determine whether the adjusting entries resulting from the reconciliations are properly authorized. 1. Trace selected recorded liabilities on the listing to supporting documentation (e.g., invoices, receiving reports, purchase orders, supplier statements) to determine accuracy of the values contained in the listing. 1. Choose a sample of 5 liabilities from the listing. 2. Test the extensions and postings on the supporting documentation. 3. If invoices have not been received, the liability may be determined by comparison with priced purchase orders, supplier price lists et c. 1. Inquire about potential sources of unrecorded liabilities. 2. Judge responses to the above in t he light of knowledge of the company’s operations. 3. Obtain files of supplier statements and perform the following: q Choose a random sample of 8 supplier invoices or delivery notes. q Trace these back to the creditors ledger by agreeing supplier details, amounts, dates. q Conclude on existence of unrecorded liabilities. 1. Examine files of unmatched delivery notes or unmatched or unpaid vendor invoices. 2. Investigate or obtain explanations for long outstanding items. 1. Obtain a sample of 5 delivery notes and invoices for goods/services received in the first week of January 2001. q Trace back to December 2000 creditors listing and ensure these delivery notes/invoices a r e n o t included on the listing. 2. Obtain a sample of 5 delivery notes and invoices for goods/services received in the last week December 2000. q Trace back to December 2000 creditors listing and ensure these delivery notes/invoices a r e included on the listing.

ANALYTIC SUMMARY Perform a segregation of duties analysis for the Accounts Payable function

Match data to verify accuracy of creditor values (unmatched join) Perform data integrity testing by reviewing liabilities for incorrect totals

n/a

Match data to review for unrecognized liabilities (unmatched join) Perform an Accounts Payable transaction cut-off analysis

\\vboxsrv\conversion_tmp\scratch_1\212138350.xls.ms_office > Audit Procedures

Audit Program Area: Accounts Payable u Program Step Analytic Summary Prep

Preliminary analytic preparation

Control Objective(s) Addressed n/a

Analytic Objective

Segregation of Duties Ensure that proper segregation of duties is established

2 Perform data integrity testing on the Accounts Payable transaction data

Completeness, Accuracy, Validity

4 Review Accounts Payable adjustments and outstanding balances for authorization and accuracy

Authorization, Accuracy

Accuracy

6 Perform data integrity testing by reviewing liabilities for incorrect totals

Accuracy

8 Match data to review for unrecognized liabilities

Completeness

9 Perform an Accounts Payable transaction cut Completeness, off analysis Validity

Data Elements Needed to Perform the Analytic**

n/a

1 Perform a segregation of duties analysis for the Accounts Payable function

5 Match data to verify accuracy of creditor values (unmatched join)

Page 2 of 11

● Employee master file ● AP transaction details file (covering the audit period)

Confirm integrity and reliability of Vendor Master details

● Vendor master file with beginning and ending balances for the audit period ● AP transaction details file (covering the audit period) Confirm reliability and ● General ledger postings file (covering the audit accuracy of financial period) transactions recorded in sub ledger and control accounts Confirm validity and ● AP transaction details file (covering the audit integrity of period) transactions created ● Employee master file as a result of ● Reconciliations file for audit period reconciliation process ● Authorized approver table for the AP f unction Confirm integrity of unpaid invoices and uncleared debit memos Confirm integrity and accuracy of reported liabilities

● AP transaction details file (covering the audit period) ● PO transaction details file (covering the audit period) ● Receiving transaction details file (covering the audit eriod Ensure accuracy and ● PO transaction details file (covering the audit reliability of supporting period) documentation

Confirm validity of unmatched delivery notes, unpaid invoices and unmatched payments

Confirm reliability of cut-off procedures

Analytic Procedure ▬ Acquire the data and prepare it for analysis ▬ Insure the data's integrity and completeness before starting analysis ▬ For all AP transactions, filter ins tances where the "entered by" user is the same as the "approved by" ID, or where there is no entry f or approver. ▬ Compare permissions table information to the users entering and approving transactions, and filter records that are inappropriate. ▬ Filter on debit balances ▬ Perform a fuzzy name match on all vendors in the Vendor master file ▬ Recalculate trade payables aging

▬ Trace all adjustments from the A P transaction details through to the G/L. ▬ Filter instances of adjustments: hitting the G/L but not represented in the AP transaction details, or those represented in the AP transaction details but not hitting the G/L.

▬ Match the AP transaction details table and the PO transactions table and review for inv oices with no corresponding PO. ▬ Match the AP transaction details table and the receiving transactions table and review for invoices with no recei t. ▬ For each PO, recalculate the PO total amount by multiplying the unit price by the unit quantity. Compare the calculated total for each PO to the actual PO total amount. Extract any records where the variance is <> 0. ▬ Perform a match of receiving transactions file and the AP transactions fi le to identify receipts that were not recognized as a liability.

● AP transaction details file (covering the audit period) ● PO transaction details file (covering the audit period) ● Receiving transaction details file (covering the audit period) ● Payment details file (if applicable to your s stem coverin the audit eriod ● AP transaction details file (covering the audit ▬ Review the receiving records file for any goods period plus one additional month past audit period received in the f irst week following the audit period

Auditor WP Ref Initials

Time Spent

Date Expected

Date Finished

Remarks

Checked By:

Audit Program Area: Accounts Payable u Program Step Analytic Summary Prep

Preliminary analytic preparation

Control Objective(s) Addressed n/a

Analytic Objective n/a


Segregation of Duties Ensure that proper segregation of duties is established






Accuracy

6 Perform data integrity testing by reviewing liabilities for incorrect totals

Accuracy

8 Match data to review for unrecognized liabilities

Completeness

9 Perform an Accounts Payable transaction cut Completeness, off analysis Validity


● Employee master file ● AP transaction details file (covering the audit period)

Confirm integrity and reliability of Vendor Master details

● Vendor master file with beginning and ending balances for the audit period ● AP transaction details file (covering the audit period) Confirm reliability and ● General ledger postings file (covering the audit accuracy of financial period) transactions recorded in sub ledger and control accounts Confirm validity and ● AP transaction details file (covering the audit integrity of period) transactions created ● Employee master file as a result of ● Reconciliations file for audit period reconciliation process ● Authorized approver table for the AP f unction Confirm integrity of unpaid invoices and uncleared debit memos Confirm integrity and accuracy of reported liabilities

● AP transaction details file (covering the audit period) ● PO transaction details file (covering the audit period) ● Receiving transaction details file (covering the audit eriod Ensure accuracy and ● PO transaction details file (covering the audit reliability of supporting period) documentation

Confirm validity of unmatched delivery notes, unpaid invoices and unmatched payments

Confirm reliability of cut-off procedures

● AP transaction details file (covering the audit period) ● PO transaction details file (covering the audit period) ● Receiving transaction details file (covering the audit period) ● Payment details file (if applicable to your s stem coverin the audit eriod ● AP transaction details file (covering the audit period plus one additional month past audit period end) ● Receiving transaction details file (covering the audit period)

Auditor WP Ref Initials

Analytic Procedure

Time Spent

Date Expected

Date Finished

Remarks

Checked By:

▬ Acquire the data and prepare it for analysis ▬ Insure the data's integrity and completeness before starting analysis ▬ For all AP transactions, filter ins tances where the "entered by" user is the same as the "approved by" ID, or where there is no entry f or approver. ▬ Compare permissions table information to the users entering and approving transactions, and filter records that are inappropriate. ▬ Filter on debit balances ▬ Perform a fuzzy name match on all vendors in the Vendor master file ▬ Recalculate trade payables aging

▬ Trace all adjustments from the A P transaction details through to the G/L. ▬ Filter instances of adjustments: hitting the G/L but not represented in the AP transaction details, or those represented in the AP transaction details but not hitting the G/L.

▬ Match the AP transaction details table and the PO transactions table and review for inv oices with no corresponding PO. ▬ Match the AP transaction details table and the receiving transactions table and review for invoices with no recei t. ▬ For each PO, recalculate the PO total amount by multiplying the unit price by the unit quantity. Compare the calculated total for each PO to the actual PO total amount. Extract any records where the variance is <> 0. ▬ Perform a match of receiving transactions file and the AP transactions fi le to identify receipts that were not recognized as a liability.

▬ Review the receiving records file for any goods received in the f irst week following the audit period where the ownership terms are upon s hipment (i.e., FOB). Trace these items to the AP subledger and G/L to ensure that all goods shipped before the end of the audit period were recognized as received during the audit period. ▬ Review the receiving records file for any goods received in the f irst week following the audit period where the ownership terms are upon receipt. Trace these items to the AP subledger and G/L to ensure that all goods shipped before the end of the audit period but received after the audit period were not recognized as received durin the audit eriod.

** Your data requirements may vary depending upon your ERP and subledger systems used.

\\vboxsrv\conversion_tmp\scratch_1\212138350.xls.ms_office > Analytic Procedures

u Program Step Prep A

Analytic Summary Acquire and verify the data

Control Objective(s) Addressed


n/a

Page 3 of 11


Steps in ACL ● Obtain data definitions, dictionaries, and mappings.

Assumptions / Prerequisites for this Step Know and understand where the data is and which specific tables and fields you will require. If the data comes from multiple sources/tables, how the sources/tables are related. The database connectivity is available and a user account with proper privileges has been set up (if using ODBC or other direct database connection).

Stage of Completion

0

● Determine method for accessing data ○ Gaining access to a production or reporting database, then connecting to the d atabase via ODBC ○ Open Data Base Connectivity (ODBC) allows you to access ODBC-compliant data such as MS Access, SQL Server or Oracle. You must have the correct ODBC drivers installed on your system before you can access ODBC data from any source. The database table or view is imported into ACL as a flat, fixed length, sequential file. The data in the ACL table is not automatically refreshed to reflect changes in the original database table. Import the entire table, or selectively import data by applying a WHERE clause during the import process. The exact syntax of the WHERE clause is specific to the database you are accessing and you may need to request assistance from your IS department. To import ODBC compliant data, start the Data Definition Wizard and select the ODBC option. One way to start the wizard is to select Data > External Data > ODBC. ○ Request IT/IS team to provide data extracts.

Make a request to the IT/IS team to provide flat file extracts of the required files. One format that would work for ACL analysis would be a comma delimited, carriage-return/linefeed terminated, and double quote text-qualified file.

○ Using the ERP system to extract the data.

Some ERPs allow for users with sufficient privileges to extract their own data. Flat-file formats (as opposed to formats like Excel) are more ideal.

● Import the data into ACL Using the Data Definition Wizard, define table layouts (if not previously done) for: 1) Employee master file 2) AP transaction details file (covering the audit period) 3) Vendor master file with beginning and ending balances for the audit period 4) AP transaction details file (covering the audit period) 5) AP transaction details file (covering the audit period plus one additional month past audit period end) 6) General ledger postings file (covering the audit period) 7) Reconciliations file for audit period 8) PO transaction details file (covering the audit period) 9) Receiving transaction details file (covering the audit period) 10) Payments details file (if applicab le to your system) (covering the audit period) Best practice: When saving the tables, use "SRC" in the table name to indicate that the data is a source file, i.e. save the table name with the prefix SRC_ to indicate that the file is a source file, for example, SRC_Employee_Master.

● Verify data against summary report or with the source's owner. ○ Ensure that your ACL table is complete. Select Count Records from the Analyze menu and click OK to obtain the total record count of your ACL table and compare it to the control totals provided by the source. Select Total Fields from the Analyze menu to obtain a sum mary total for all numeric fields and compare the totals with the control totals provided by the source ○ After confirming that the table is complete, select Verify from the Data menu and apply the command to all fields in your table. Verify tests if the data conforms to the field descriptions specified in the table layout, such as: O nly valid characters are stored in character fields, numeric data is stored in numeric fields, and that dates are valid. If errors are reported, the record number, field name, and hexadecimal code for the data will be displayed on a results tab. You can then provide this report to your IS department to investigate these errors by analyzing the hexadecimal code in the report


Segregation of Duties ● Employee master file ● AP transaction details file (covering the audit period)

a) Open the AP transaction details table. Ensure that proper segregation of duties is established b) Filter the transaction details table where the "entered by" user ID is equal to the "approved by" u ser ID. ○ To apply the filter, enter = into the Filter box above the table, replacing and with the actual field names (no brackets). Note that both fields must be defined with the same data type. For example, both fields must be character fields. If one field is d efined as numeric and the other as character, applying the filter results in a Expression Type Mismatch error. Click Enter to apply the filter expression. c) Extract the resulting SOD violations to a results table. To extract the records, select Extract Data from the Data menu. Select the Record option to extract all matching records in their current format and field order, or select the Fields option to individually select some or all fields in the desired order. Enter a descriptive name into the To box and click OK. Note: Setting the filter and extracting the matching records can be combined into a single step, b y specifying the condition = in the IF box within the Extract command. d) Join the on the results table to th e field contained in the employee master file to determine relevant employee names. ○ To join the tables, first confirm that th e field and the field are defined with the same length, data type, and that the alignment of the data within the field is the same (leading vs trailing spaces, leading zeros, etc). If the fields are not defined in an id entical manner, you will need to create computed fields first, standardizing the two key fields. Next, open the extracted results table. Select Join Tables from the Data menu. The Join dialog box opens. Under Primary Keys, select the field. Under Primary Fields, select all fields you wish to carry forward to the final table. Under Secondary Table, select the employee master table. Under Secondary Keys, select the field, and under Secondary Fields, select the employee name field, and any other relevant fields from the employee master table. Ensure that Presort Secondary Table is selected, unless the employee master file is already sorted in ascending order on the employee ID field.

0

ACL Table Name(s)

ACL Engagement

e erence (Location in the Engagement)

u Program Step Prep A

Analytic Summary Acquire and verify the data



n/a


Steps in ACL ● Obtain data definitions, dictionaries, and mappings.

Assumptions / Prerequisites for this Step Know and understand where the data is and which specific tables and fields you will require. If the data comes from multiple sources/tables, how the sources/tables are related. The database connectivity is available and a user account with proper privileges has been set up (if using ODBC or other direct database connection).

Stage of Completion

ACL Table Name(s)

ACL Engagement


0

● Determine method for accessing data ○ Gaining access to a production or reporting database, then connecting to the d atabase via ODBC ○ Open Data Base Connectivity (ODBC) allows you to access ODBC-compliant data such as MS Access, SQL Server or Oracle. You must have the correct ODBC drivers installed on your system before you can access ODBC data from any source. The database table or view is imported into ACL as a flat, fixed length, sequential file. The data in the ACL table is not automatically refreshed to reflect changes in the original database table. Import the entire table, or selectively import data by applying a WHERE clause during the import process. The exact syntax of the WHERE clause is specific to the database you are accessing and you may need to request assistance from your IS department. To import ODBC compliant data, start the Data Definition Wizard and select the ODBC option. One way to start the wizard is to select Data > External Data > ODBC. ○ Request IT/IS team to provide data extracts.

Make a request to the IT/IS team to provide flat file extracts of the required files. One format that would work for ACL analysis would be a comma delimited, carriage-return/linefeed terminated, and double quote text-qualified file.

○ Using the ERP system to extract the data.

Some ERPs allow for users with sufficient privileges to extract their own data. Flat-file formats (as opposed to formats like Excel) are more ideal.

● Import the data into ACL Using the Data Definition Wizard, define table layouts (if not previously done) for: 1) Employee master file 2) AP transaction details file (covering the audit period) 3) Vendor master file with beginning and ending balances for the audit period 4) AP transaction details file (covering the audit period) 5) AP transaction details file (covering the audit period plus one additional month past audit period end) 6) General ledger postings file (covering the audit period) 7) Reconciliations file for audit period 8) PO transaction details file (covering the audit period) 9) Receiving transaction details file (covering the audit period) 10) Payments details file (if applicab le to your system) (covering the audit period) Best practice: When saving the tables, use "SRC" in the table name to indicate that the data is a source file, i.e. save the table name with the prefix SRC_ to indicate that the file is a source file, for example, SRC_Employee_Master.

● Verify data against summary report or with the source's owner. ○ Ensure that your ACL table is complete. Select Count Records from the Analyze menu and click OK to obtain the total record count of your ACL table and compare it to the control totals provided by the source. Select Total Fields from the Analyze menu to obtain a sum mary total for all numeric fields and compare the totals with the control totals provided by the source ○ After confirming that the table is complete, select Verify from the Data menu and apply the command to all fields in your table. Verify tests if the data conforms to the field descriptions specified in the table layout, such as: O nly valid characters are stored in character fields, numeric data is stored in numeric fields, and that dates are valid. If errors are reported, the record number, field name, and hexadecimal code for the data will be displayed on a results tab. You can then provide this report to your IS department to investigate these errors by analyzing the hexadecimal code in the report


Segregation of Duties ● Employee master file ● AP transaction details file (covering the audit period)

a) Open the AP transaction details table. Ensure that proper segregation of duties is established

0

b) Filter the transaction details table where the "entered by" user ID is equal to the "approved by" u ser ID. ○ To apply the filter, enter = into the Filter box above the table, replacing and with the actual field names (no brackets). Note that both fields must be defined with the same data type. For example, both fields must be character fields. If one field is d efined as numeric and the other as character, applying the filter results in a Expression Type Mismatch error. Click Enter to apply the filter expression. c) Extract the resulting SOD violations to a results table. To extract the records, select Extract Data from the Data menu. Select the Record option to extract all matching records in their current format and field order, or select the Fields option to individually select some or all fields in the desired order. Enter a descriptive name into the To box and click OK. Note: Setting the filter and extracting the matching records can be combined into a single step, b y specifying the condition = in the IF box within the Extract command. d) Join the on the results table to th e field contained in the employee master file to determine relevant employee names. ○ To join the tables, first confirm that th e field and the field are defined with the same length, data type, and that the alignment of the data within the field is the same (leading vs trailing spaces, leading zeros, etc). If the fields are not defined in an id entical manner, you will need to create computed fields first, standardizing the two key fields. Next, open the extracted results table. Select Join Tables from the Data menu. The Join dialog box opens. Under Primary Keys, select the field. Under Primary Fields, select all fields you wish to carry forward to the final table. Under Secondary Table, select the employee master table. Under Secondary Keys, select the field, and under Secondary Fields, select the employee name field, and any other relevant fields from the employee master table. Ensure that Presort Secondary Table is selected, unless the employee master file is already sorted in ascending order on the employee ID field. On the More tab, select Matched Primary Records > Include All Primary Records. This option ensures that any entered_by IDs that cannot be found in the employee master table are carried forward to the final output. If you do not select this option, only those records where the ID is present in both tables are carried forward to the final output. On the Main tab, enter a descriptive name for your final output into the To box and click OK.

e) Perform similar testing for other SOD violations you wish to test.



● Vendor master file with beginning and ending balances for the audit period ● AP transaction details file (covering the audit period) ● General ledger postings file (covering the audit period)

Confirm integrity and a) Perform a Summarize command using the date field in the transaction details table as a means for determining the total debits and credits which should be posted to the general ledger each day. Save this summary to a file. reliability of Vendor Master details

0

○ Select Summarize from the Analyze menu. Under Summarize On, select the transaction date field. Under Subtotal Fields, select the amount field. Under Other Fields, select any other fields that you need to carry forward to the final table. Note that ACL groups records by unique date and returns a single record for each unique date in the output table. Where there are multiple records with the same date, only the data from the first record will be carried forward for the selected Other Fields. Ensure the Presort option is selected, unless the table is already sorted in ascending order on date. On the Output tab, select the File option and enter a descriptive name into the Name box, then click OK. Note: To obtain separated summarized tables for debit transactions and credit transactions, apply a filter in the IF box on the Main tab - for example >= 0. Then repeat the steps above with the filter < 0. b) Perform a Summarize command using the date field in th e general ledger postings table as a means to determine the total debits and credits which were actually posted to the general ledger each day. Save this summary to a file. ○ Select Summarize from the Analyze menu. Under Summarize On, select the transaction date field. Under Subtotal Fields, select the amount field. Under Other Fields, select any other fields that you need to carry forward to the final table. Note that ACL groups records by unique date and returns a single record for each unique date in the output table. Where there are multiple records with the same date, only the data from the first record will be carried forward for the selected Other Fields. Ensure the Presort option is selected, unless the table is already sorted in ascending order on date. On the Output tab, select the File option and enter a descriptive name into the Name box, then click OK. Note: To obtain separated summarized tables for debit transactions and credit transactions, apply a filter in the IF box on the Main tab - for example >= 0. Then repeat the steps above with the filter < 0. Alternatively, if there is a debit / credit indicator field, include this field in Summarize On so that records are grouped if the date is the same and th e debit / credit indicator is the same. c) Perform a Join command on both summary tables using the transaction date as the key field. Filter the resulting table for any transaction debits that are not equal to GL debits, or transaction credits that are not equal GL credits.

\\vboxsrv\conversion_tmp\scratch_1\212138350.xls.ms_office > ACL Steps

u t Program Step

Analytic Summary



Page 4 of 11

Analytic Objective

Steps in ACL

Assumptions / Prerequisites for this Step

Stage of Completion

○ To join the tables, first confirm that the date fields in both tables are defined with the same length, date data type, and that the date format is the same). If the fields are not defined in an identical manner, you will need to create computed fields first, standardizing the two key fields. Next, open summarized transaction table. Select Join Tables from the Data menu. The Join dialog box opens. Under Primary Keys, select the date field, and if used in the previous step, select the debit / credit indicator field as well. Under Primary Fields, select the amount field, and any other fields you wish to carry forward to the final table. Under Secondary Table, select the summarized GL table. Under Secondary Keys, select the date field and if applicable, select the debit / credit indicator field. Under Secondary Fields, select the amount field, and any other relevant fields from the summarized GL table. Both tables are already sorted in ascending order on the key field(s), so neither Presort Primary Table nor Presort Secondary Table need to be selected.

d) Perform a Fuzzy Name Match analytic (SP05) and review results for potential duplicate vendor entries in the master file register. ○ To run analytic SP05, right-click this analytic in the Project Navigator panel on the left side of the screen and click Run.

Confirm reliability and a) Perform a Summ arize command using the vendor ID field in the transaction details table, consolidating both debits and credits, as a means to d etermine the total accuracy of financial adjustment by vendor for the audit period. transactions recorded in sub ledger and control accounts

0

○ Select Summarize from the Analyze menu. Under Summarize On, select the vendor ID field. Under Subtotal Fields, select the amount field. Under Other Fields, select any other fields that you need to carry forward to the final table, including the field. Note that ACL groups records by vendor ID and returns a single record for each unique vendor ID in the output table. Where there are multiple records with the same vendor ID, only the data from the first record will be carried forward for the selected Other Fields. Ensure the Presort option is selected, unless the table is already sorted in ascending order on vendor ID On the Output tab, select the File option and enter a descriptive name into the Name box, then click OK. Note: To calculate a balance of credit vs debits by vendor ID, the amount field must contain positive and negative values. If this is not the case, create a conditional computed field first to multiply either the debits or credits by -1, based on the value in the Credit / Debit indicator field. Then use this computed field under Subtotal Fields.

b) Create a computed field to calculate the vendor beginning bal ance for the year by subtracting the total adjustm ents for the period from the ending balance. Save the field. ○ Open the summarized table created above and select Table Layout from the Edit menu. On the Edit Fields / Expressions tab, click the Fx button (Add a new Expression). Enter a d escriptive name in th e Name box. In the Default Value box, enter the following expression: amount_field - ending _balance (where amount_field is the previously subtotaled field. Ending_b alance must be defined as a nu meric field) Click the green checkmark and close the Table Layout window to return to the view of the summarized data. To add the new field to the view, right-click anywhere in the view and select Add Columns. Double-click the new computed field so that it appears under Selected Fields and click OK.

c) Perform a Join command between this summarized table and the vendor master table. ○ To join the tables, first confirm that the field in both tables is defined with the same length, data type, and that the alignment of the data within the field is the same (leading vs trailing spaces, leading zeros, etc). If the fields are not defined in an identical manner, you will need to create computed fields first, standardizing the two key fields. Next, open the summarized results table. Select Join Tables from the Data menu. The Join dialog box opens. Under Primary Keys, select the field. Under Primary Fields, select all fields you wish to carry forward to the final table. Under Secondary Table, select the vendor master table. Under Secondary Keys, select the field, and under Secondary Fields, select the vendor name field, and any other relevant fields from the vendor master table. Ensure that Presort Secondary Table is selected, unless the vendor master file is already sorted in ascending order on the vendor ID field. On the More tab, select Matched Primary Records > Include All Primary Records . This option ensures that any vendor IDs that cannot be found in the vendor master table are carried forward to the final output. If you do not select this option, only those records where the ID is present in both tables are carried forward to the final output. On the Main tab, enter a descriptive name for your final output into the To box and click OK. d) Filter on all records where beginning (balance + adjustments) <> ending balance. ○ To apply the filter, enter + = into the Filter box above the table, replacing , , and with the actual field names (no brackets). is the previously summarized amount field. Note that all fields must be defined with a numeric data type. If the data types are not numeric for all fields, applying the filter results in a Expression Type Mismatch error. Click Enter to apply the filter expression. e) Extract the resulting exceptions to a results table for further investigation. To extract the records, select Extract Data from the Data menu. Select the Record option to extract all matching records in their current format and field order, or select the Fields option to individually select some or all fields in the desired order. Enter a descriptive name into the To box and click OK. Note: Setting the filter and extracting the matching records can be combined into a single step, by specifying the condition + = in the IF box within the Extract command.



● AP transaction details file (covering the audit period) ● Employee master file ● Reconciliations file for audit period ● Authorized approver table f or the AP function

Confirm validity and a) Filter on reconciliations table where approval ID is blank or approval ID = creator ID. integrity of transactions created as a result of reconciliation process ○ To apply the filter, enter ISBLANK() OR = into the Filter box above the table, replacing and with

0

ACL Table Name(s)

ACL Engagement


u t Program Step

Analytic Summary



Analytic Objective

Steps in ACL

Assumptions / Prerequisites for this Step

Stage of Completion

ACL Table Name(s)

ACL Engagement


○ To join the tables, first confirm that the date fields in both tables are defined with the same length, date data type, and that the date format is the same). If the fields are not defined in an identical manner, you will need to create computed fields first, standardizing the two key fields. Next, open summarized transaction table. Select Join Tables from the Data menu. The Join dialog box opens. Under Primary Keys, select the date field, and if used in the previous step, select the debit / credit indicator field as well. Under Primary Fields, select the amount field, and any other fields you wish to carry forward to the final table. Under Secondary Table, select the summarized GL table. Under Secondary Keys, select the date field and if applicable, select the debit / credit indicator field. Under Secondary Fields, select the amount field, and any other relevant fields from the summarized GL table. Both tables are already sorted in ascending order on the key field(s), so neither Presort Primary Table nor Presort Secondary Table need to be selected.

d) Perform a Fuzzy Name Match analytic (SP05) and review results for potential duplicate vendor entries in the master file register. ○ To run analytic SP05, right-click this analytic in the Project Navigator panel on the left side of the screen and click Run.

Confirm reliability and a) Perform a Summ arize command using the vendor ID field in the transaction details table, consolidating both debits and credits, as a means to d etermine the total accuracy of financial adjustment by vendor for the audit period. transactions recorded in sub ledger and control accounts

0

○ Select Summarize from the Analyze menu. Under Summarize On, select the vendor ID field. Under Subtotal Fields, select the amount field. Under Other Fields, select any other fields that you need to carry forward to the final table, including the field. Note that ACL groups records by vendor ID and returns a single record for each unique vendor ID in the output table. Where there are multiple records with the same vendor ID, only the data from the first record will be carried forward for the selected Other Fields. Ensure the Presort option is selected, unless the table is already sorted in ascending order on vendor ID On the Output tab, select the File option and enter a descriptive name into the Name box, then click OK. Note: To calculate a balance of credit vs debits by vendor ID, the amount field must contain positive and negative values. If this is not the case, create a conditional computed field first to multiply either the debits or credits by -1, based on the value in the Credit / Debit indicator field. Then use this computed field under Subtotal Fields.

b) Create a computed field to calculate the vendor beginning bal ance for the year by subtracting the total adjustm ents for the period from the ending balance. Save the field. ○ Open the summarized table created above and select Table Layout from the Edit menu. On the Edit Fields / Expressions tab, click the Fx button (Add a new Expression). Enter a d escriptive name in th e Name box. In the Default Value box, enter the following expression: amount_field - ending _balance (where amount_field is the previously subtotaled field. Ending_b alance must be defined as a nu meric field) Click the green checkmark and close the Table Layout window to return to the view of the summarized data. To add the new field to the view, right-click anywhere in the view and select Add Columns. Double-click the new computed field so that it appears under Selected Fields and click OK.

c) Perform a Join command between this summarized table and the vendor master table. ○ To join the tables, first confirm that the field in both tables is defined with the same length, data type, and that the alignment of the data within the field is the same (leading vs trailing spaces, leading zeros, etc). If the fields are not defined in an identical manner, you will need to create computed fields first, standardizing the two key fields. Next, open the summarized results table. Select Join Tables from the Data menu. The Join dialog box opens. Under Primary Keys, select the field. Under Primary Fields, select all fields you wish to carry forward to the final table. Under Secondary Table, select the vendor master table. Under Secondary Keys, select the field, and under Secondary Fields, select the vendor name field, and any other relevant fields from the vendor master table. Ensure that Presort Secondary Table is selected, unless the vendor master file is already sorted in ascending order on the vendor ID field. On the More tab, select Matched Primary Records > Include All Primary Records . This option ensures that any vendor IDs that cannot be found in the vendor master table are carried forward to the final output. If you do not select this option, only those records where the ID is present in both tables are carried forward to the final output. On the Main tab, enter a descriptive name for your final output into the To box and click OK. d) Filter on all records where beginning (balance + adjustments) <> ending balance. ○ To apply the filter, enter + = into the Filter box above the table, replacing , , and with the actual field names (no brackets). is the previously summarized amount field. Note that all fields must be defined with a numeric data type. If the data types are not numeric for all fields, applying the filter results in a Expression Type Mismatch error. Click Enter to apply the filter expression. e) Extract the resulting exceptions to a results table for further investigation. To extract the records, select Extract Data from the Data menu. Select the Record option to extract all matching records in their current format and field order, or select the Fields option to individually select some or all fields in the desired order. Enter a descriptive name into the To box and click OK. Note: Setting the filter and extracting the matching records can be combined into a single step, by specifying the condition + = in the IF box within the Extract command.



● AP transaction details file (covering the audit period) ● Employee master file ● Reconciliations file for audit period ● Authorized approver table f or the AP function

Confirm validity and a) Filter on reconciliations table where approval ID is blank or approval ID = creator ID. integrity of transactions created as a result of reconciliation process

0

○ To apply the filter, enter ISBLANK() OR = into the Filter box above the table, replacing and with the actual field names (no brackets). Note that all fields must be defined with a character data type. If the data types are not character for all fields, applying the filter results in a Expression Type Mismatch error. Click Enter to apply the filter expression. b) Save exceptions to a results table for further investigation. To extract the records, select Extract Data from the Data menu. Select the Record option to extract all matching records in their current format and field order, or select the Fields option to individually select some or all fields in the desired order. Enter a descriptive name into the To box and click OK.

Confirm integrity of unpaid invoices and uncleared debit memos

Note: Setting the filter and extracting the matching records can be combined into a single step, by specifying the condition ISBLANK() OR = in the IF box within the Extract command. a) Perform an Aging command on invoices and uncleared debit advices, then follow-up on results greater than an acceptable threshold (threshold determined by the organization).

0

To age a date field, select Age from the Analyze menu. Ensure the field to be aged is defined as a date type, and select it from the Age On list. Under Cutoff Date, select a suitable date using the calendar tool. Under Aging Periods, enter the desired date ranges (e.g, 0 to 30 days, etc) or accept the default aging periods. If necessary, select a field in Subtotal Fields. On the Output tab, select Screen and click OK. The results will be displayed on-screen, allowing you drill down into a particular date range and see the underlying transactions in th e view. Note: If the cut off date is not a constant date, but rather another date field in your table, follow this process: Apply the following filter in the an IF condition: - >= X where X is the acceptable threshold, expressed as a number.

Filter box or extract records with

Note: There may be a need to apply a filter to isolate open / uncleared items in your AP transaction table for this analysis. With in the Age command, enter the correspondin g filter into the IF box. If using the filter expression above, use - >= X AND

b) Identify invoices that are approved by unauthorized users

Open the AP Transaction Details file. Select Join Tables from the Data menu. The Join dialog box opens. Under Primary Keys, select the field. Under Primary Fields, select all fields you wish to carry forward to the final table. Under Secondary Table, select the authorized approver table. Under Secondary Keys, select the field, and Ensure that Presort Secondary Table is selected, and also that the unmatched option is selected.

\\vboxsrv\conversion_tmp\scratch_1\212138350.xls.ms_office > ACL Steps

u Program Step

Page 5 of 11

Analytic Summary


Control Objective(s) Addressed Accuracy

Data Elements Needed to Perform the Analytic** ● AP transaction details file (covering the audit period) ● PO transaction details file (covering the audit period) ● Receiving transaction details file (covering the audit period)

Analytic Objective

Confirm in tegrity and a) Perform an Unmatch accuracy of reported b) Perform an Unmatch liabilities

a) Open the AP Transa field. Under Primary Fie Keys, select the
b) Open the AP Transactio Primary Fields, select al field unmatched option is

6 Perform data integrity testing by reviewing Accuracy

● AP transaction details file

Ensure accuracy and a) Summarize the Rece

u Program Step


Analytic Summary


Accuracy

Data Elements Needed to Perform the Analytic** ● AP transaction details file (covering the audit period) ● PO transaction details file (covering the audit period) ● Receiving transaction details file (covering the audit period)

Analytic Objective

Confirm in tegrity and a) Perform an Unmatch accuracy of reported b) Perform an Unmatch liabilities

a) Open the AP Transa field. Under Primary Fie Keys, select the
b) Open the AP Transactio Primary Fields, select al field unmatched option is

6 Perform data integrity testing by reviewing Accuracy liabilities for incorrect totals

● AP transaction details file (covering the audit period) ● PO transaction details file (historical) ● Receiving transaction details file (historical)

Ensure accuracy and reliability of supporting documentation

a) Summarize the Rece b) Summarize the Invoic c) Summarize the PO T d) Perform a Join from t e) Filter on instances wh 1) Item quantity <> P 2) Item quantity <> re 3) Invoice amount <> f) Join the resulting exce

Client Name Internal Control Framework Date Completed: Completed By: Reviewed By: Question

Yes No* Comments /Description

Employee Responsible for Task

Client Name Internal Control Framework Date Completed: Completed By: Reviewed By: Question

Yes No* Comments /Description

Employee Responsible for Task

To the best of my knowledge, the answers and comments noted above are accurate and reflect the current

Name and Title of Person Completing Form (please print)

Name and Title of Department Director (please print)

Signature of Person Completing Form

Signature of Department Director

2/18/2014 Date Form Completed

Date of Department Director's Signature

Finding Ref #

Control Testing

Finding

Management Response & Treatment

AccountsPayable_AEAP2009

Recommend Documents