34|ALTERYX – Audit Attention

DylanKing · May 1, 2023, 6:53pm

BYU Student Author: @DylanKing
Reviewers: @Christian, @Boston, @Alex_Garrett
Estimated Time to Solve: 10 Minutes

We provide the solution to this challenge using:

Alteryx
Go here to perform this challenge in SQL

Need a program? Click here.

Overview
This challenge works in connection with Challenge 52|ALTERYX – Balance Sheet Boost. As a first-year associate who recently graduated from school, you have joined a small local accounting firm in the area. Your first assignment is to audit the Financial Statement Controls of a local chain of car repair shops. The senior member of the engagement team informed you that they had strengthened the controls around the income statement in the previous year. However, they remain concerned about some of the controls around certain balance sheet accounts that do not meet the firm’s standards.

You decide to use your skills in Alteryx to identify balance sheet accounts that require the most attention during the audit. After meeting with others on the engagement team, you determine that accounts that have 1,000 transactions or more during the month and have an ending debit or credit balance of 50,000 or more will require the most attention. You notify the other members of the audit engagement that you will find which accounts meet these criteria.

Instructions
Using Alteryx, create a workflow to identify which accounts meet the aforementioned criteria. Remember, accounts must have 1,000 transactions or more and have an ending debit or credit balance of 50,000 or more. Sort by the number of transactions in descending order and be sure to sort out the data that has a null account number. In the end, your output should have the following columns: Acct, GLAcctDesc, EndingDebitBalances, NumOfTransactions.

Data Files

Suggestions and Hints

When trying to find the accounts that follow the criteria of having an ending debit or credit balance of $50,000 or more, remember that we are given the ending debit balance. Using absolute value can help you get around this problem.

Solution

Solution Image

Challenge34_Solution.yxmd
Solution Video: Challenge 34|ALTERYX – Audit Attention

Benjamin_Lau · May 16, 2023, 5:43pm

Thank you Dylan again for the great challenge!

Here is my solution:

TylerBooth · May 23, 2023, 5:53pm

Great refresher challenge! My solution came out just about the same as yours. I initially forgot to set the first filter to catch the credit balances. I just saw the column header, “EndingDebitBalance” and assumed the number would all be positive. Thanks for writing this challenge Dylan!