The short answer
Work backwards from the return. Which fields does it need, at what granularity, with what definitions? Then check whether your systems capture that. Usually one or two fields are missing or captured differently, and that is what forces the manual assembly.
Fixing the capture is a smaller job than the recurring reconstruction, and it makes the number defensible.
Why returns get assembled by hand
- A required field is not captured, so it is inferred
- Definitions differ between the return and the operational system
- Data sits in several systems that do not join cleanly
- The return needs a point-in-time view and the system holds current state
- Exclusions and adjustments are applied by someone who knows the rules
The fourth is the most structural. A return asking what the position was on a date cannot be answered by a system that overwrites, which is an architecture decision rather than a reporting one.
Capture definitions at source
| Instead of | Do this |
|---|---|
| Deriving a category at report time | Capture it when the event happens |
| Inferring a date from another field | Record the date the return asks for |
| Applying exclusions manually | Record the exclusion reason as data |
| Reconstructing a past state | Keep history rather than overwriting |
The bottom row is the expensive one to retro-fit and the most valuable to get right early.
Make it reproducible
- Generate the return from a query, not a spreadsheet.
- Version the query alongside the definitions it implements.
- Store the submitted figures with the date and the query version.
- Keep the underlying rows so a challenged figure can be explained.
- Re-run a previous period and check it reproduces.
Point five is the test. If last quarter's figure cannot be reproduced today, the process is not reproducible and a query about it cannot be answered confidently.
Reconcile before submitting
Build the checks into the process: totals against the operational system, period-on-period comparison, and flags on anything outside a normal range.
Most submission errors are visible in a comparison against the previous period. Catching them before submission is considerably easier than correcting afterwards.