Quarterly audit tool

Every quarter the cost team decides which general contractor invoices go into a sampled audit. Two process owners had written the selection rules as a Python notebook, and the rules were correct.

Running the notebook meant installing Python and its libraries, editing file paths in the code and stepping through cells in order. One technical person ended up running it for everyone, and reviewers still made hundreds of edits by hand in Excel.

The team's internal hosting serves static files only, so there was no server and no Python runtime. The data is confidential and could not be sent to an outside service.

I rewrote the pipeline in JavaScript that runs in the browser. Spreadsheets are parsed and processed on the reviewer's computer and are never uploaded. A build script packs everything into one HTML file that can be hosted internally or opened straight from disk.

A nine-step wizard that follows the order the audit team already works in:

• Load the two source spreadsheets and set the quarter
• Check purchase-order and invoice totals against the source report
• Classify every line using last quarter's decisions and out-of-scope categories
• Review only what can't be resolved automatically
• Catch lines that ended up tagged both include and exclude
• Apply the value rules for credits, re-balances and small invoices
• Build the sample population and its pivots
• Download a formatted, multi-tab workbook for the witnessed random draw

The tool stops before the random draw. The draw is done live with witnesses, and that step stays manual.

I checked that no purchase order in the data had lines split between include and exclude, then grouped pending lines by purchase order. That reduced 276 individual edits to about 80 decisions.

Reviewers can filter, sort, search, select in bulk and pin the decision column. Anyone who prefers Excel can export the decisions, edit them there and import them back.

I compared every output tab against the notebook's reference workbook, cell by cell. Each run also reconciles the sample and the excluded groups back to the original total, to the cent.

Automated browser tests check for zero console errors and zero network requests, which is how the no-upload rule is verified.

Testing found real defects: a composite key that hid quality-control exceptions, total and filter rows that were counted twice, and a worksheet step with quadratic run time that froze the browser. I rewrote that step as a single pass, and it went from hanging to about half a second.

The two process owners and a second reviewer tested it on real files. Their requests turned into features: grouped and detailed views, downloadable QC tabs, a Prime filter, sticky controls, clearer labels and a reference page for first-time users.

At the final demo a senior stakeholder called it a “game changer.” The audit team estimated the workflow went from hours to under 30 minutes. That figure is their estimate, not a timed measurement, and measuring it properly is the first item on the handoff list.