Accounting Best Practices, Fifth Edition

15–21. Use Excel for Continuous Auditing

Instead of waiting for an infrequent internal audit to investigate a variety of risk areas, consider a regularly scheduled investigation using Excel and data downloaded from the accounting system. Excel downloads are a staple of most accounting software, usually resulting in either preformatted spreadsheets or comma-delimited text that can be easily converted into a spreadsheet. Once in spreadsheet format, consider making the following tests:

• Transactions during odd hours. For all types of transactions, sort the spreadsheet based on time and date to see if anyone is accessing the system outside of regular working hours, and investigate any transactions made during those times.

• Same data entry person for the same supplier. For payables transactions, sort the spreadsheet by supplier name and then by the user ID of the person entering transactions. If the same person always enters payables for the same supplier, this could be a shell company owned by the data entry person.

• Subthreshold transactions. For payables transactions, sort in declining order by invoice totals, and investigate payments for which dollar amounts are just below the corporate approval threshold. Chances are good that some involve split payments to avoid detailed analysis by an authorized approver.

• Late customer orders with no purchase order. For billing transactions, first sort on overdue customer invoices, then sort the resulting subset on billings without customer purchase ...