BYU Student Author: @Kiya_Smith
Reviewers: @Kylie_Larsen17 @Abraham_Reyes_Leon
Estimated Time to Solve: 15 Minutes
We provide the solution to this challenge using:
- Excel
- ChatGPT
- Alteryx
Need a program? Click here.
Overview
As an analyst at Deloitte, you play a crucial role in evaluating the effectiveness of clients’ internal controls. Your latest assignment involves assessing an access termination control for a client. The client has provided you with a dataset of employees from the past year, including their start date, termination date, and access removal date. The terminated employees must have their access to the clients data removed within a day of being terminated. Your objective is to analyze the dataset and determine whether the control is operating effectively by identifying instances of timely, late, or missing access removals.
Instructions
Download the Access Termination Data excel file provided to you containing employee termination data.
- Create a new column at the end titled “Removal Status” in the dataset.
- Apply an IF Statement to All Rows in the Column to classify each employee into one of these categories:
a. Current Employee – The employee has not been terminated.
b. Removed in Time – Access was removed within 24 hours of termination.
c. Removed Late – Access was removed, but after the 24-hour deadline.
d. Not Removed – Access was never removed after termination.
e. Apply Conditional Formatting:
- Red for “Not Removed”
- Yellow for “Removed Late”
- Green for “Removed in Time”
- “Current Employees” can remain with default formatting.
-
Determine on your own first if you think the control is effective. In this case usually a control is effective if the control works all the time or the majority of the time. The control is not effective if there are multiple instances of no access removal for terminated employees and late access removal.
-
Import the completed Excel workbook into ChatGPT. And use it as well as the details of this case to prompt ChatGPT to generate a report summarizing:
a. Whether the control is effective.
b. Evidence supporting your conclusion.
c. Any recommendations to improve weaknesses. -
Post your new excel workbook as well as the report as your solution to this challenge.
Data Files
Suggestions and Hints
There will be multiple IF statements inside the initial IF statement. Start with an IF statement where the criteria is that if the “Termination Date” column is empty and enter another IF statement for the false criteria. There should be a total if 3 IF statements in your equation.
Remember to use conditional formatting to highlight your cells that have been either Removed on Time, Removed Late or Not Removed. This is found in the middle of the home tab and it says “Conditional Formatting”. Then in the drop-down you can select “Highlight Cells Rules” then choose “Equal To” and put the conditions in for each of the three (Removed on Time, Removed Late or Not Removed).
Solution