Case study 15
Several recurring reports inherited with no handover, none missed
Several recurring client reports changed hands with no handover notes. None was missed, each now has its own SOP, and the weekly movement workbook is not final until every check reads TRUE.
Within a few weeks, several weekly and monthly reports landed on me from people who had moved on. Each arrived as a short screen recording and an old file. The old files were not clean either: some formulas pointed at columns that had been deleted, and one summary block had been copied in from another client. Following the old file exactly would have reproduced the old errors, on schedule.
Literal steps, real locations. I watched each recording at slow speed and wrote every action as a literal step, with the real file location and the cell or column it touched. Every number the trainer typed by hand got a note on where it came from. A step that says "update the summary" is not a step.
First run exactly as trained. I ran each report once on the newest data, following my written steps exactly, then compared my run with the last finished output line by line. Where they disagreed, I worked out whether the data had changed or the old file was wrong.
An already-broken list, kept separate. Defects that were there before I arrived went on their own list, and none of them was fixed yet. A list lets the owner decide; a silent fix surprises them.
Only questions that change the output. The person who trained me got three things: the finished run, the already-broken list, and only the questions whose answers changed the output: cadence, due day, recipients. Everything else became a labelled assumption.
An SOP per report. Each SOP was written from my steps on one approved template, linked to its recording, with a what-goes-wrong box seeded from the already-broken list. After the second run, the version moved up with anything that changed.
Tasks before data. Each report's recurring task is created before its source data lands, so the work is already on the list when the data arrives.
A workbook that checks itself. One report was a weekly sales movement report from a partner chain: units and location counts per product variant, week by week. A check row must read TRUE everywhere before it goes out: the week total equals the raw tab's sum, variant totals add up to the week total, each location block adds up to its variant total, and every location resolves in the location list. A file with any lookup error is not final, and a failing check is reported, never hidden.
Weekly movement workbook, Summary tab
| Week | Units | Stores | vs prior wk |
|---|---|---|---|
| Wk1 | 2,442 | 271 | |
| Wk2 | 2,453 | 268 | 0.45% |
| Wk3 | 2,529 | 265 | 3.10% |
| Wk4 | 2,701 | 270 | 6.80% |
| Wk5 | 2,642 | 264 | -2.18% |
| Wk6 | 2,758 | 266 | 4.39% |
| 6 wk total | 15,525 |
Weekly movement workbook, Checks tab (Wk6)
| Check | Value | Result |
|---|---|---|
| Week total = raw tab sum and partner total | 2,758 | TRUE |
| Variant A + Variant B = week total | 2,758 | TRUE |
| Variant A block sums to A total | 1,816 | TRUE |
| Variant B block sums to B total | 942 | TRUE |
| Raw stores missing from store list | 0 | TRUE |
| All checks pass (50 of 50) | TRUE |
A month-end close, written down. A client's month-end close was documented the same way, from a walkthrough, into an SOP someone else could run.
Click any image to see it full size. Every file here is rebuilt with invented data.
The results, in bands. Several inherited reports went out on schedule after handover, none missed, each with an SOP and a record of what was already broken.
- The inherited files carried quiet breakage, so copying them faithfully would have copied the errors too. Every first run is now diffed against the old output line by line, and old defects go on the separate list.
- Each week's partner file brought locations missing from the blocks and some listed twice. Adding and removing them is now a fixed step, and the check row catches any I miss.
Download the weekly movement workbook (xlsx)Download the SOP library (zip)Download SOP-FIN-01, month-end close (docx)The Takeover playbook (PDF)Taking over reports with no handoverLive demo of the movement analysis workbookLive demo of the SOP libraryPart of the work in case study 07