Top Analytic Tests Tutorial Series December 2013

This month, we’re taking a break from journal testing and focusing our attention on travel and entertainment (T&E) expenses. Often, this is not an area that warrants detailed attention because the transactions are too small to be material. What’s the point in chasing after a $75 steak dinner?

Of course, you’ve heard it before: small transgressions of corporate expense policy can be an indicator of broader ethical risk. After all, if your employees aren’t treating their corporate expenses with seriousness, are they more or less likely to take a chance and participate in bribing public officials to win business?

But how exactly do you go about identifying the problem? Is it useful to print out pages upon pages of slightly-too-expensive employee meals to chase after?

An organization came to us with this exact problem last year. After some M&A activity, their T&E expenses had risen nearly two-fold year over year. Instead of generating exception report after exception report, the approach we took was to highlight the departments who were the “best” at contributing to the increase in costs. To do this, we sliced the data by department and expense type, and considered averages and totals.

Risk: A corporate culture exists where travel and entertainment (T&E) expenses are not well controlled.

Test: Identify average expense transaction sizes by business unit/division/department.

Data Acquisition:

Chances are, if your organization isn’t using spreadsheets to submit and reimburse expenses, you’re using either Concur® or an ERP provided solution.

If you’re using Concur, Concur provides a standard detailed interface file that’s used to integrate with accounting systems, called the Concur Standard Accounting Extract (SAE for short). Luckily for us, the SAE contains all of the data elements that you’ll need to perform this analysis (and nearly anything else you’d want to test expenses). I’ve provided a script as an example of how to read in an SAE file below (in “The Goods” section). It’ll need some adjustment before it can be used, but intermediate ACL users should find it valuable.

If you’re using an ERP provided solution, then you’ll have to obtain a dump of the travel and entertainment expenses within the audit period you want to review. You’ll want at least these fields (probably more):

Required Nice to have for context/other analysis
Expense Report Number Expense Country
Expense Line Number Expense Amount (Original Currency, LC)
Cost Center (Business Unit/Division/Department) Expense Currency
Expense Category Attendee Name
Expense Amount (Functional Currency, FC) Expense Payment Type (i.e. Cash vs Card)
Employee Number Expense Description
Employee Name Expense MCC
Employee Department Merchant Name
Expense Date Merchant Country
Number of Attendees Merchant Address
Merchant State
Expense Approver

Test Steps:

  1. Obtain a dump of all the T&E expenses from within the audit period you want to review.
    • See above section on Data Acquisition
  2. Import the expense data into ACL™ Analytics using the import wizard.
  3. Ensure completeness by verifying the data against control totals (e.g., count, and total on expense amount to tie to audit period actuals).
  4. Summarize on Cost Center (could be department or business unit) and Expense Category, subtotaling on Expense Amount (FC).
  5. Calculate the average expense amount by defining a computed field.
    • Total Expense Amount (FC) / COUNT (number of transactions in the same cost center and expense category)
  6. Examine the results of the Summarize (step 4) to review the cost centers with the highest total spend in each category.
  7. Examine the results of the average calculation (step 5) to review the cost centers with the highest average spend in each category.

The Goods:
Import SAE.ACL
The SAE import script mentioned above. You’ll need to modify the parameters to get it to work.


This analysis might highlight that your human resource department has been spending significant amounts on travel for prospective candidates. Or it might show that your legal department has been traveling business class for short domestic trips. It can also guide you to which expense categories might warrant further detailed analytics. Sharing these results with the CFO or other executives on a regular basis (made possible by the scripting power of ACL Analytics, and the dashboarding capabilities of ACL Analytics Exchange) might drive a top-down shift in corporate culture—no executive wants to be in charge of a department that is tops for a particular expense category.

(Source: AuditNet)

Sunday, December 1, 2013 In: Hot Topics Comments (None)