In the past QuickBooks had an optional Audit Trail feature that you could choose whether or not to enable. However, recent versions of the program automatically enable Audit Trail, so every change made to a transaction in QuickBooks is logged automatically.
Although this may seem Orwellian, you may find that you sometimes need to carry out forensic research on a particular QuickBooks transaction. In layman’s terms, this means looking into who changed or deleted a transaction, determining what date the transaction changed, and how the transaction looked before it changed.
In this article we’ll discuss five different audit reports that QuickBooks provides, as well as show you some easier ways to mine the data within these reports.
Expert tip: It’s best to assign a separate user ID to each QuickBooks user. To do so, choose Company, Set up Users and Passwords, and then follow the onscreen prompts. Once you set it up, you’ll be able to have accountability for every transaction entered or modified in QuickBooks.
Audit Trail Report
As previously discussed, the Audit Trail is automatically enabled in QuickBooks, and it cannot be disabled. To view the audit trail, choose Reports, Accountant & Taxes, and then Audit Trail. As shown in Figure 1, the audit trail report will appear onscreen.
Figure 1: By default, the Audit Trail shows all activity for today.
Although the Audit Trail report defaults to today’s date, you can easily change the date range at the top of the screen. As you might expect, this report may contain a lot of data, so you may need to trim down the data shown:
- Click the Modify Report button.
- Click on the Filters tab.
- Choose Transaction Type from the Filter List, and then choose Multiple Transaction Types from the Transaction Type list. As shown in Figure 2, you can then select one or more transaction types to display.
- Click OK twice to display the report.
Figure 2: You can limit the Audit Report to certain transaction types.
Even with changing the filters and date range, you may still have a tough time navigating the report. Unfortunately QuickBooks does not allow you to search the report onscreen, however, you can easily export the report to Excel or another program so that you can carry out your research:
- To export to Excel: Click the Export button at the top of the Audit Trail report screen, choose A New Excel Workbook, and then click Export.
- To export to another program: The Export button also allows you to export the report to a CSV file, which means a comma-separated value format. This type of report is best viewed in a spreadsheet such as Excel.
- If you don’t have Excel available, choose File, Save As PDF, and then save the report to a PDF file. You should then be able to copy and paste the resulting report into the program of your choice or use the search feature within your PDF viewer – the free Adobe Acrobat Reader is a common choice.
If you choose to export the report to Excel, you’ll have some advanced filtering capabilities at your disposal:
- Excel 2007: Click on cell A1, and then press Shift-End-Home. This will select the entire workbook. You can then choose Sort & Filter from the Filtering section of the Home ribbon, and then choose Filter. As shown in Figure 3, you can then click the arrow in cell J1 and choose to which transactions to display:
- Latest means the most recent version of the transaction.
- Prior means the transaction has been edited. The Audit Trail shows both the latest and previous versions of the transaction.
- Deleted means that the transaction has been deleted and must be manually reentered in QuickBooks if necessary.
- Earlier versions of Excel: Click on cell A1, press Shift-End-Home, and then choose Data, Filter, and then AutoFilter. You can then click any of the arrows in row 1 to filter the list to meet specific criteria.
Figure 3: Sending the report to Excel enables you to filter for deleted or modified transactions.
Alternatively you can press Ctrl-F and search for the words Prior or Deleted. Click the Find Next button to move to the next transaction as you carry out your review, as shown in Figure 4.
Figure 4: Excel’s Find feature is another way to sift through a lengthy Audit Trail report.
Fraud alert: Perpetrators often generate checks or invoices under one vendor or customer ID, and then modify the accounting records to obfuscate their deed. Always review transactions with a Prior label carefully.
Voided/Deleted Transactions Summary and Detail Reports
Deleted transactions often appear as a discrepancy when you attempt to reconcile a bank or credit card account. Typically the starting balance that QuickBooks displays will differ from the ending balance on your bank statement. In such instances, it’s a good practice to first check the Voided/Deleted Transaction Reports:
- Choose Reports, and then Accountant & Taxes.
- Select either the Voided/Deleted Transactions Summary or Detail reports. Both provide basically the same information, but the Detail report includes the entire transaction, rather than just the top level information shown in Figure 5.
Figure 5: Double-click a transaction on the summary report to view its details.
Closing Date Exception Report
You can use this report to determine if anyone has made changes to transactions subsequent to you specifying a closing date in the QuickBooks preferences. To do so, choose Edit, and then Preferences. Next, choose Accounting, and then Company Preferences.
Finally, click the Set Date/Password button, and then follow the onscreen prompts. Going forward you can choose Reports, Accountant & Taxes, and then Closing Date Exception Report to monitor any chances to closed periods in QuickBooks.
Customer Credit Card Audit Log
QuickBooks offers additional protection if you store customer credit card data in QuickBooks. The Customer Credit Card Audit Log report, shown in Figure 6, records all activity related to customer credit cards:
- When credit card numbers are entered
- Whenever credit card numbers are displayed onscreen
- When credit card numbers are edited or deleted
Figure 6: The Customer Credit Card Audit Log tracks all activity related to customer credit cards.
To enable logging of customer credit card activity in QuickBoo