1.Executive summary
Hartwell Fabrication Ltd (“HFL”, ~42 staff) plans production, costs jobs and books dispatches through a single Excel workbook, HFL_Production_Planner_v13.xlsm, built and maintained since 2012 by an operations manager who retired in 2025. Roughly a dozen people use it daily; nobody currently at HFL can modify the VBA with confidence. The question we were asked: keep it, fix it, or replace it — and at what cost?
In one paragraph: the workbook is not dying — it is concentrated. Three of its six jobs (order intake, dispatch emailing, archiving) are exactly the fragile, integration-heavy parts that break when Windows, Office or one hard-coded path changes, and they are the parts that will fail first — the dispatch email module already depends on classic Outlook automation that new Outlook deployments do not support. The costing engine, by contrast, is battle-tested and correct (we verified it against 12 golden cases). The rational move is to stabilize now (15–20 h), move intake and dispatch to boring managed services (40–60 h), and leave costing in Excel behind a cleaned-up interface — revisiting a full rebuild only if HFL's volume or team changes.
Top 5 risks (of 10 identified — full register in §5)
| # | Risk | Severity | Why it matters now |
|---|---|---|---|
| R1 | Key-person risk: sole author retired, no documentation | HIGH | Any non-trivial failure is currently unrecoverable in-house |
| R3 | Dispatch emails via classic Outlook COM automation | HIGH | New Outlook does not support this; one IT rollout away from stopping dispatches |
| R2 | Hard-coded personal paths (author's desktop) in the import chain | HIGH | Order import silently depends on a leaver's profile folder being recreated |
| R5 | No versioning; parallel “v13_FINAL_(2)_USE_THIS” copies circulating | MED | Divergent copies are already producing conflicting numbers between teams |
| R4 | 14 of 86 named ranges broken (#REF!), 2 in the costing path | MED | Errors are being masked by On Error Resume Next — failures will be silent |
Recommended path and cost
- Phase 0 — Stabilize (do regardless): 15–20 h. Removes the failure modes that can stop production this quarter.
- Phase 1 — Migrate intake & dispatch: 40–60 h. Replaces the two integration-fragile jobs with managed, supported services.
- Phase 2 — Contain costing in Excel: 10–15 h. Refactor, document, test-harness. No migration.
Estimates are ±30% and assume written access to one HFL power user for questions. Detailed breakdown in §8.
2.Scope and method
What we received: one anonymised copy of the workbook (customer names and prices masked per our checklist — the anonymisation did not affect the analysis), plus written answers to nine intake questions from HFL's operations lead.
What we did: programmatic inventory of every sheet, formula, named range, external reference and VBA procedure; manual code review of all 13 VBA components; reconstruction of the six “jobs” the workbook performs; verification of the costing logic against 12 golden cases supplied by HFL; risk scoring; architecture options.
What we did not do: we did not run the workbook against live systems (ODBC, Outlook, network shares are outside an anonymised copy), did not review surrounding processes, and did not audit security beyond the workbook itself. Findings that depend on HFL's live environment are marked as assumptions.
3.What this workbook actually is
Twelve years of accretion have produced a system with six distinct jobs. Naming them is the single most useful step in deciding its future, because the verdict differs per job:
| Job | Where it lives | Health | Direction |
|---|---|---|---|
| 1. Order intake (CSV import from ERP export) | modImport + Orders | Fragile | Migrate |
| 2. Daily production planning | modMain + Dashboard/Capacity | Workable | Keep, simplify |
| 3. Job costing | modCosting + JobCosting/BOM_Lookup/Materials | Sound | Keep, contain |
| 4. Dispatch emailing | modEmail (Outlook COM) | Fragile | Migrate |
| 5. Archiving | modArchive + archive sheets | Workable | Migrate with #1 |
| 6. Label/paperwork printing | modPrint | Workable | Keep, fix path |
4.Inventory
4.1 Workbook metrics
| Metric | Count | Notes |
|---|---|---|
| Sheets | 19 | 17 visible + 2 hidden (Scratch, zOld_Rates) |
| Formulas | ≈41,200 | of which ≈1,870 volatile (OFFSET, INDIRECT, NOW, TODAY) |
| Named ranges | 86 | 14 broken (#REF!) — 2 referenced from the costing path |
| VBA components | 13 | 9 standard modules, 3 sheet modules, ThisWorkbook — ≈3,380 LOC, 47 procedures |
| External dependencies | 6 | see §4.4 — 2 confirmed dead, 2 environment-fragile |
| Array formulas (legacy Ctrl-Shift-Enter) | 24 | concentrated in Capacity |
4.2 Sheets
| Sheet | Purpose | Used rows | Status |
|---|---|---|---|
| Dashboard | Daily plan view, KPI cells, refresh button | — | Active |
| Orders | Live order book (imported + manual edits) | ≈180 | Active — mixed manual/imported data, no input validation |
| OrderArchive_2019 / _2020_22 | Frozen order history | ≈150 | Dormant — safe to extract |
| Capacity / MachineList / Shifts | Machine load model (10 machines) | ≈60 | Active — 24 legacy array formulas |
| BOM_Lookup / Materials / Suppliers | Costing reference data | ≈175 | Active — clean structure, good candidate for a reference table |
| PriceBands / JobCosting | Quantity bands; per-job cost build-up | ≈90 | Active — core logic, verified in §7 |
| DispatchLog / Quality_NCR | Dispatch records; non-conformance log | ≈175 | Active |
| Holidays / Params / Lookup_Refs | Calendars, settings, list sources | ≈40 | Active — Params mixes config with hard-coded paths |
| Scratch, zOld_Rates (hidden) | Author's workspace; 2013 rates | ≈70 | Dormant — zOld_Rates still referenced by 1 broken name |
4.3 VBA modules
| Component | ≈LOC | Role | Health notes |
|---|---|---|---|
| modMain | 310 | Orchestration, daily plan build | Workable; DoEvents loops, screen-update juggling |
| modImport | 280 | CSV order import | Hard-coded path C:\Users\g.hartley\Desktop\orders_export.csv; pervasive On Error Resume Next |
| modCosting | 870 | Job cost engine | Logic sound (12/12 golden cases); style poor — GoTo flow, undeclared variables, magic numbers |
| modEmail | 240 | Dispatch emails | Classic Outlook COM only — incompatible with new Outlook |
| modArchive | 190 | Year-end archiving | Sheet-copy approach; created the “_FINAL” copy culture |
| modUtils | 220 | Shared helpers | UK/US date-parsing bug (CDate on dd/mm strings) — active data-quality risk on import |
| modPrint | 150 | Labels & paperwork | Printer path hard-coded (\\HFL-SRV01\Zebra-Dispatch) |
| modLegacy_DO_NOT_TOUCH | 420 | Dead code (2014 planner) | 99% unreachable — but one live call remains (see R9) |
| modAuth | 90 | Sheet protection | Hard-coded password, reused everywhere; protection ≠ security |
| Sheet modules ×3 | 380 | Change-event side effects | Hidden coupling: edits trigger recalcs and writes users can't see |
| ThisWorkbook | 230 | Open/close hooks | Auto-connects legacy ODBC DSN HFL_JOBS (Access 2010 era) |
4.4 External dependencies
| Dependency | Used by | Status |
|---|---|---|
C:\Users\g.hartley\Desktop\orders_export.csv | modImport | Fragile — leaver's profile folder, currently recreated by IT as a workaround |
\\HFL-SRV01\shared\rates.xls | 2 formulas + 1 name | Dead — file removed ≈2021; values frozen since |
| Outlook (COM automation) | modEmail | Fragile — classic Outlook only |
ODBC DSN HFL_JOBS | ThisWorkbook | Legacy — Access-era DSN, per-machine setup, undocumented |
Printer \\HFL-SRV01\Zebra-Dispatch | modPrint | Fragile — breaks on printer rename |
Add-in HFL_Tools.xlam | 3 formulas | Missing — not supplied; 3 cells show #NAME? (confirmed cosmetic only) |
5.Risk register
| ID | Risk | Sev. | Likelihood (12 mo) | Impact if it fires | Mitigation |
|---|---|---|---|---|---|
| R1 | Key-person: sole author retired; no docs, no handover | HIGH | Already realised | Any breakage → external help, days of downtime | This report + Phase 0 documentation; test harness (§7) |
| R2 | Hard-coded personal path in import chain | HIGH | ~60% | Order import stops silently | Config sheet + UNC path (Phase 0); then managed intake (Phase 1) |
| R3 | Outlook COM dispatch emailing vs. new Outlook rollout | HIGH | ~70% (IT plans migration) | Dispatch notifications stop same-day | Phase 1: move to Power Automate / Graph-based flow |
| R4 | 14 broken named ranges; 2 feed costing via error-masked code | MED | ~40% | Silent wrong costs | Phase 0: fix or retire all 14; remove On Error Resume Next from costing path |
| R5 | No versioning; divergent “_FINAL” copies | MED | Already realised | Teams working from different numbers | Single master + SharePoint version history (Phase 0) |
| R6 | ≈1,870 volatile formulas → full recalc on every edit | MED | Certain (perf, not failure) | 30–90 s freezes; users disable calc, then forget | Replace OFFSET/INDIRECT with INDEX/structured refs (Phase 0/2) |
| R7 | UK/US date parsing bug in modUtils on import edge cases | MED | ~30%, monthly window | Wrong due dates on ~1st–12th of month orders | Phase 0 quick fix (explicit format); Phase 1 removes the path entirely |
| R8 | Hard-coded protection password, widely known pattern | LOW | — | Accidental (not malicious) structure edits | Treat as tamper-seal only; real permissions live in SharePoint (Phase 1) |
| R9 | Dead module still referenced by one live call | LOW | ~15% | Runtime error on a rarely-used button | Phase 0: sever the call, quarantine the module |
| R10 | Legacy ODBC DSN auto-connect on open | LOW | ~25% | Slow/failed opens on new machines | Phase 0: make connection lazy + documented; retire with Phase 1 |
Likelihoods are our calibrated estimates given HFL's written answers about its IT environment; they are judgements, not measurements — we state them so you can challenge them.
6.The verdict, reasoned
Why not “Keep”? Keeping means accepting R2 and R3 — two high-severity risks with >50% likelihood that stop order intake or dispatch when they fire, in a workbook nobody in-house can repair (R1). “Keep” was the right answer in 2023. It stopped being the right answer when the author left.
Why not “Stabilize” and stop there? Stabilization (Phase 0) is necessary and we recommend doing it first and regardless. But it cannot fix R3: Outlook COM automation is being deprecated around the workbook, and polishing VBA that drives a disappearing interface is spending money on borrowed time.
Why not “Replace”? Because the costing engine — the only part that would be genuinely expensive and risky to rebuild — is the part that works. It encodes 12 years of shop-floor pricing knowledge (setup times, scrap factors, band logic) that exists nowhere else. A full rebuild would re-derive that knowledge at high cost and high error risk for zero operational gain. Replace remains a future option after the fragile edges are migrated; nothing in this path forecloses it.
Therefore: Partially Migrate. Move the two integration-fragile jobs (intake, dispatch) plus archiving to managed services; contain and document the healthy core in Excel. Smallest intervention that removes all HIGH risks.
7.Testing blueprint (how you'll know it's safe)
Every change in the recommended path is gated by the same discipline — golden outputs and a parallel run. This is what turns a risky migration into a boring one:
- Golden outputs (before touching anything). We freeze 12 reference cases — real jobs across the quantity bands and material grades, including the two known edge cases (bank-holiday scheduling; multi-material BOMs). For each: inputs, every intermediate figure, final cost. During this assessment we already ran the 12 cases against the current engine: 12/12 reproduce exactly, which is the evidence behind “the costing core is sound”.
- Acceptance per phase. A phase is done when all golden cases match to the cent, the affected job runs end-to-end on a machine the author never touched, and a named HFL user signs off in writing.
- Parallel run. Two full weekly cycles old-vs-new for intake and dispatch; discrepancies triaged in writing within 24 h.
- Rollback. The old path stays runnable (frozen, clearly labelled) for 60 days after cut-over.
8.Effort and cost estimate
| Phase | Content | Effort | Elapsed | Risk removed |
|---|---|---|---|---|
| 0 — Stabilize | Fix/retire 14 broken names; extract paths to config; single master + versioning; sever dead-code call; date-bug quick fix; unmask errors in costing path; 1-page runbook | 15–20 h | 1–2 weeks | R2 (interim), R4, R5, R7 (interim), R9 |
| 1 — Migrate intake & dispatch | Order intake to a managed flow (SharePoint list + scheduled import); dispatch mail via supported API flow; archiving becomes a by-product; decommission ODBC autoconnect | 40–60 h | 4–6 weeks | R2, R3, R7, R10 |
| 2 — Contain costing | Refactor modCosting for readability (no logic changes, golden-gated); replace volatile lookups; document the pricing model | 10–15 h | 1–2 weeks | R6, halves R1 |
Ranges are ±30% (confidence 75% that actuals land inside). Assumes: written access to one HFL power user; no scope growth; HFL's Microsoft 365 tenant available for Phase 1. Prices for fixed-scope execution are quoted separately and only if you ask — this report is complete without buying anything else from us.
9.Next steps
- One round of written Q&A on this report is included — send questions any time within 30 days.
- If you proceed with Phase 0 (with us or anyone else), start with the named-range repair and the master-copy consolidation: cheapest, highest-yield, zero-risk.
- Before any IT rollout of new Outlook, re-read R3 — that is your hard deadline.
A.Appendix — method notes
Inventory produced programmatically (sheet/formula/name/reference walk + VBA parse), then reviewed by hand; numbers in §4 are exact counts on the anonymised copy, not estimates. Verdict scale: Keep (leave as is, monitor) · Stabilize (fix in place, no architecture change) · Partially Migrate (move fragile jobs out, contain the core) · Replace (rebuild). We have no stake in the verdict: we do not sell licenses, and follow-up work happens only at your written request. Confidence percentages express our calibrated judgement — we would rather be checkably wrong than vaguely right.