County Permits Office: Macro Workbook Sprawl
County permits office processes 1,400 applications monthly. Staff rely on a 9-year-old Excel workbook with 27 macros to merge intake CSV exports, calculate fees, and produce PDF letters. The workbook is copied to a shared drive; each of 12 clerks edits a personal copy. Auditors found 3 fee errors and 11 missing letters last quarter. IT proposes replacing it with a low-code platform at $38,000/year; the clerk who built the workbook says the macros can be fixed in two weeks. The office has no version-control policy, no test environment, and two clerks due to retire within 18 months. Two clerks have asked whether they can keep local copies during the transition. Management wants a recommendation before the next budget cycle.
1. Diagnose the operating failure and separate its process, control, and knowledge causes. Which single cause would you address first, and why?
Read the worked answer
The symptoms are not a macro defect alone. Process cause: 12 personal copies create divergent logic and no single source of truth; the shared drive is storage, not version control. Control cause: there is no testing or reconciliation, so fee errors and missing letters passed undetected; detective controls are absent. Knowledge cause: one clerk holds undocumented macro logic and two clerks retire within 18 months, creating key-person risk.
Address the process cause first - consolidate to one controlled master or a platform with a named owner and change log. Fixing macros in 12 copies multiplies rework and leaves the audit trail unchanged. Interim controls: make the shared master read-only for clerks, route changes through a change request, and run a daily fee/letter reconciliation. Success measures: zero unapproved copies, 100% letter reconciliation, and documented macro logic. This converts an individual fix into a controlled process.
2. Compare fixing the workbook with buying the low-code platform over a three-year horizon. Recommend a decision and the conditions that would change it.
Read the worked answer
Build option: two weeks of one clerk's time plus testing, but it concentrates risk in a retiring employee and does not fix version control, audit trail, or fee-calculation testing, so recurring defects are likely. Buy option: $38,000/year = $114,000 over three years, plus migration and training, but it provides access control, audit logs, and vendor support.
The comparison must include the cost of the current error rate: 3 fee errors and 11 missing letters in one quarter imply rework, reputational exposure, and audit findings, plus the cost of two retirements. Recommend buying only if a requirements fit-gap and pilot prove fee and letter logic. Otherwise use a hybrid: one controlled master workbook with version control and documentation as a 6-month bridge, then migrate. Change conditions: if the platform cannot replicate complex fee rules without custom code, or if annual cost exceeds quantified error and rework cost, choose the controlled rebuild. Do not approve a two-week macro fix as the end state.