BYU Student Author: @Kiya_Smith
Reviewers: @James_Behling, @Jason_Nguyen
Estimated Time to Solve: 40 Minutes
We provide the solution to this challenge using:
- Excel and ChatGPT
- Alteryx
Need a program? Click here.
Overview
As an analyst at Deloitte, you are responsible for evaluating the effectiveness of clients’ internal controls. Your latest assignment is to assess an access termination control for a client. The client has provided you with a dataset of employees, including start date, termination date, and access removal date. Terminated employees must have their access removed within one day of termination. Your goal is to determine whether the control is operating effectively by identifying timely, late, or missing access removals.
Instructions
1️. Download the Access Termination Data file provided to you.
2️. Import the dataset into a new Alteryx workflow using the Input Data Tool.
3️. Create a new column titled “Removal Status” using the Formula Tool.
4️. Apply an IF statement to classify each employee into one of the following categories:
- Current Employee – The employee has not been terminated.
- Removed in Time – Access was removed within one day of termination.
- Removed Late – Access was removed, but after the one day deadline.
- Not Removed – Access was never removed after termination.
-
Apply a second formula to create another column titled “Risk” associate “high” risk with employees that were not terminated, “medium” risk for the employees terminated after the one day deadline and “low” risk for the terminated employees who had their access removed in time. Mark employees that have not been terminated as “N/A”.
-
Using the Filter tool, filter only the employees that were not removed or removed late. (These should end up on the “T” output of the “T” or “F” options). This filters out the low risk and current employees to focus on the ones where the control may not be effective.
-
Connecting to the “T” output, use the Sort tool to sort the data on the risk column in ascending order to have the high risk data at the top and the medium underneath.
-
Determine on your own if you think the control is effective. Generally, a control is effective if it works all or the majority of the time. The control is not effective if there are multiple instances of “Not Removed” and “Removed Late.”
-
Export your results as an Excel file using the Output Data Tool.
-
Use ChatGPT to generate a report based on your findings. Import your completed Excel workbook into ChatGPT and prompt it to summarize:
- Whether the control is effective.
- Evidence supporting your conclusion.
- Any recommendations to improve weaknesses.
- Post your Alteryx workflow file (.yxmd), your completed Excel file, and your generated report as your solution.
Data Files
Suggestions and Hints
When classifying employees in your “Removal Status” field, consider using the DateTimeDiff() function.
Solution