Quick tips for using data analytics to improve EBP audits
AICPA logo
AICPA logo
  • Home
Quick tips for using data analytics to improve EBP audits

Quick tips for using data analytics to improve EBP audits

1 year ago · 3 min read · AICPA Insights Blog

Employee benefit plan audits are highly specialized, requiring deep knowledge of the appropriate procedures, risks, and standards. What if you could improve audit efficiency and effectiveness using tools already at your disposal?

It’s possible, thanks to data analytics. The JofA spoke to Jimmy Young, CPA, AICPA manager–Audit Data Analytics and a presenter at next month’s AICPA & CIMA Employee Benefits Plan Conference, to get some insights.

What is audit data analytics, and what are the benefits for employee benefit plan (EBP) audits?

Young: Audit data analytics are data techniques that can be used to perform risk assessments, tests of controls, substantive procedures, or concluding audit procedures. We do not typically see a great deal of data analytics used in EBP audits due to the type and structure of data. That offers us a lot of potential when it comes to getting the data in a digitized fashion, such as getting it into Excel or other machine-readable formats. From the payroll records to the third-party administrator reports, to the firm reports, PDFs show up and the data is difficult to pull from them. We can see how data correlates among those reports, and others, when the data is structured appropriately and digitized.

You mentioned that auditors could build a data model to automate loan testing on 100% of the loan population, instead of sampling. Can you tell us more about that?

Young: Not just loan testing, but all of EBP detail testing. Once the data is structured and digitized, we can start automating three-way matches, just as we do with financial statement audits. When data is in a structured way and adjusted properly, you can test entire populations. For example, if you can bring in payroll data, contribution percentages, and contribution change dates, you can recalculate contribution rates for the entire year and fully test contributions. Ultimately, you can build out models where you are recalculating all the transactions throughout the year rather than a sample.

What’s a key takeaway you want EBP auditors to get out of your session?

Young: I want them to realize they can start getting better at performing data analytics in EBP audits with the software their firm already has and by using some advanced Excel functions instead of manually selecting items from PDFs. And that getting the data digitized and in a workable format is critical to enhancing the efficiency and effectiveness of the audit, whether it’s in EBP or otherwise.

How can auditors use Excel to capture and make sense out of the data?

Young: Here are five tips that will help auditors immediately, and I’ll mention how each is important:

  1. Power BI (Power Query, Power Pivot, and Power View): These modules are both built into Excel as well as available in a stand-alone desktop version. Power Query is Excel’s answer to extracting, transforming, and loading data into a data model. Auditors are typically receiving multiple datasets that are all related in some way, all of which are growing in size every year. Power BI makes handling and understanding these datasets much easier and more efficient. By building models with these features, a user can easily import new or updated data without having to redo work (which means they can scale solutions across engagements).

  2. VLOOKUPs, HLOOKUPs, and XLOOKUPs: These are Excel functions that search for a certain value in a range in order to return a value from a different cell within the same range. V stands for vertical and H is for horizontal. Auditors are always comparing values from separate lists. This formula replaces the old method of an auditor searching within a list, such as pulling values from a payroll register to a testing form. XLOOKUP is a newer function (available in Excel 365 and later) that replaces VLOOKUPs and HLOOKUPs and fulfills the primary goals of the older functions but is more versatile with fewer limitations.

  3. New Window View: This is a simple convenience tip within the View section of Excel. When both preparing and reviewing workpapers, auditors often will have to flip between tabs or scroll back and forth to check values. The new window function duplicates an instance of Excel, making comparisons a breeze.

  4. Flashfill: A convenient data prep feature in Excel for users who may not know how to do prep work with traditional formulas, Flashfill automatically searches for a pattern in your data and copies it across your dataset. This is especially valuable when preparing data for certain functions.

  5. Conditional formatting: Applies a visual cue to a list or table of data that can help identify reasonableness or outliers. These functions can be used as a review tool or for setting up a testing template.

To learn more about audit data analytics for employee benefit plans, register for Young’s May 4 session (EBP2136 — Data Analytics in EBPs and Beyond) as part of the online conference May 3–5.

To enhance your data analytics readiness, check out Data Analytics Core Concepts.

Ellen Goldstein (Ellen.Goldstein@aicpa-cima.com) is the Association’s director–Communications & Special Projects.

What did you think of this?

Every bit of feedback you provide will help us improve your experience

What did you think of this?

Every bit of feedback you provide will help us improve your experience

Mentioned in this article



Manage preferences

Related content