Diagnose a real compliance program drowning in manual work and identify the three automations that save the most work.
30 min 130 XPSkills: Program analysis, Automation strategy
Brief
Almost every compliance program you join will be run out of a spreadsheet, and the spreadsheet will be excellent: carefully colour-coded, lovingly maintained, and quietly the single biggest source of risk in the function.
The problem is not that spreadsheets are bad tools. It is that a spreadsheet is a snapshot pretending to be a system. It has no version history you can query, no validation on what gets typed into it, no link back to the systems it describes, and no way to tell you it went stale three weeks ago.
The spreadsheet is not the control program. It is a report about a control program that nobody is running.
Six ways it breaks
The failure modes are consistent enough to be a checklist. No version history, so nobody can say who changed row 88 or when. Stale by design, because a human refresh cycle is the fastest it can ever be. No validation, so the owner column contains four spellings of the same person. Copy drift, because the moment it is emailed there are two truths. No population, because the list is a hand-typed copy rather than a query against the source. And human throughput: every PBC line item costs a person an hour.
Notice that five of the six are integrity problems, not effort problems. Hiring another analyst makes the sixth better and the other five worse.
One file, six independent failure modes. Only one of them is fixed by working harder.
Diagnosing before automating
The instinct on arriving at a drowning program is to automate the loudest thing. The discipline is to measure first: for each recurring activity, how many manual hours per year does it consume, and how hard would it be to automate?
Plot those two numbers and the sequence picks itself. High hours and low effort go first. High hours and high effort get a funded project. Low hours and low effort are quick wins to fill gaps. Low hours and high effort stay manual, forever, without guilt.
Sequence is a measurement problem. Automate in the order the numbers give you.
The automation trap
There is one prerequisite the matrix does not show. Automating a control that was never capable of catching the risk does not fix it: it produces the same wrong answer faster, on a schedule, with better-looking evidence attached.
So the order is: fix the design, then automate the operation. A quarterly access review that only looks at accounts created in the quarter should not be scripted. It should be rewritten, and then scripted.
Tasks
Answers are graded on the server: the answer key never reaches your browser.
Task 1: controls_master_v7_FINAL_v2.xlsx
Scenario sim+40 XP
Name what is missing, then name what happened to the file.
Incident brief
You inherit the program. In the shared drive: controls_master_v7_FINAL.xlsx. In the outgoing manager's sent items: controls_master_v7_FINAL_v2.xlsx, sent to the CFO. In the audit folder: a third file with the same name and 12 fewer rows. In Slack, a screenshot of a fourth.
Row 88, the encryption control, is marked Effective in two copies and Remediating in the other two. Nobody can say who changed it, when, or which one the auditor was given.
The manager's handover note: "It's all in the master. Just always use the newest one."
Term bank, not all of these are right
version controlcopy driftvalidationretentionaccess controlbackup
1.1Name the missing capability that makes 'who changed row 88' unanswerable. (two words)
1.2Name the failure mode of four divergent copies in four inboxes. (two words)
Task 2: Automate the loudest thing
Scenario sim+40 XP
Name the quadrant, then the artifact the tracker is only pretending to be.
Incident brief
The CISO wants the board reporting narrative automated because it is the thing he personally hates. It costs about six hours a year. Meanwhile the quarterly user access review consumes roughly 340 analyst hours a year and could be served by one scheduled API call against the identity provider.
Your colleague proposes a compromise: script the narrative, and for the access review keep the tracker but "make it the source of truth so everyone stops arguing about which list is right".
The tracker is typed up by hand each quarter from an export someone emails over.
Term bank, not all of these are right
do firstquick winsplan and fundleave manualsource of truthpopulation
2.1Name the leverage-matrix quadrant the access review belongs in. (two words)
2.2Name what the hand-typed tracker is being wrongly promoted to. (three words)
Task 3: Faster, and still wrong
Scenario sim+50 XP
Name the activity to attack, then the gate that must be cleared first.
Incident brief
Time study of the two-person team: 68% of hours go to chasing, formatting and uploading artifacts for auditors. 14% goes to updating trackers. 11% goes to meetings. 7% goes to actually judging whether controls work.
The proposed fix is a script that logs into each system every Monday, screenshots the settings pages, and files them in the audit folder automatically: reproducing exactly the current process at machine speed.
Two of the controls being screenshotted review only accounts created within the quarter, which is the reason last year's contractor findings were missed entirely.
The control tracker is a spreadsheet exported to a table. Your manager says the programme is healthy: "filter status to pass and you'll see we're at 100%".
A row is only evidence if it is fresh, owned, and machine-readable. Return the `row_id` of every row that fails any of those three tests: the rows nobody should be quoting in a status report.
Table: tracker
row_id control_id owner days_since_test status
1 AC-1 ada 12 pass
2 AC-1 ada 210 pass
3 CM-2 unassigned 30 pass
4 EV-3 bob 95 pass
5 EV-4 bob 5 pass (see notes)
6 IA-5 cara 5 pass
Programme rule: a test older than 90 days is stale.
Return one column: row_id.
The quarterly control workbook has been circulating by email for three months. You have one hour before the pack goes out and you can only escalate what genuinely misstates the programme's position.
Raise the rows where the workbook reports a control state that is not true. Leave the rows that are merely untidy.
List only the lines that qualify, worst first. Severity is one of: critical | high | medium | low. Add or delete rows as needed.
controls_q3_FINAL_v7_JS_edit.xlsx (row notes)
S-1 Row 12 formula =SUM(D2:D40) but the data now runs to D63; 23 controls excluded from the totals.
S-2 Row 4 status hard-typed "Effective" over a formula that read "Fail" in the previous version.
S-3 Row 19 owner column reads "Dave" with no surname or team.
S-4 Row 27 has inconsistent date formats, mixing 03/04 and 4-Mar.
S-5 Row 31 links to an external workbook on a departed employee's personal drive; value shows #REF!.
S-6 Row 38 uses a different shade of green for pass than the rest of the sheet.
S-7 Row 44 marked "Effective" with the evidence cell empty.
S-8 Row 51 uses merged cells in the header, so filtering the sheet is awkward.
1
2
3
4
5
6
7
8
9
10
11
12
Checks
The right findings are raised
Benign lines are left out
Severity matches the actual exposure
Ranked worst first
Next on your path · Automation & Code
Your First Automated Control
Take one access-review control from Word document to a scheduled check that produces its own evidence.
