A spreadsheet looks like a simple source. A cell with a number, a tab with a name, an export from another system that has been pasted in. But between that cell and the figure that eventually ends up in a report lies a sequence of operations that is usually recorded nowhere. Source-to-report mapping is the process of mapping out that sequence: every step a number goes through from the moment it enters the spreadsheet to the moment it lands in a report line.
With a system that has fixed fields and a fixed structure, it is often still possible to trace which field comes from where. A spreadsheet does not have that structure by default. Someone adds an intermediate tab to aggregate. Someone else copies a column to another file to perform a conversion. A third person pastes the result as a value into a reporting sheet, which makes the formula, and with it the trail, disappear. None of this is wrong at the moment it happens. The problem arises a year later, when someone has to explain where a figure came from and the answer can no longer be reconstructed.
Source-to-report mapping for a spreadsheet source consists of a number of recognisable steps, each of which needs separate attention.
The first step is the origin of the raw data: which file, which tab, which cell or cell range, and who enters or supplies it. Without this anchor point, there is no source to point back to.
The second step is which operations sit between source and report when the source is a spreadsheet. Think of units being converted, filters that exclude certain rows, formulas that add up or rescale values. Every operation changes the number, and every operation that is not recorded is a step that can no longer be verified later.
The third step is aggregation: multiple rows, tabs or files that are combined into one figure. With a spreadsheet this often happens manually, with a press of the sum function over a range that someone has defined themselves. How that range was chosen and what is in it determines the figure just as much as the underlying data. That is exactly why it needs to be recorded how you record aggregation when the source is a spreadsheet: not as a formality, but because the aggregation step itself is a source of errors that no one else sees.
The fourth step is normalisation: different units, different reporting periods or different locations that are brought to a common basis before they are comparable. Here too, the choice of a conversion factor or reference value drives the result, and that choice must be traceable. How that works is set out in how you record normalisation when the source is a spreadsheet.
The last step is the place where the number lands: the report line, the indicator, the annual total. That transition too must have a trail, not just a reference to the source document.
It is tempting to think that a list of who changed what is enough. That is a log, and a log records changes without showing the logic behind them. An audit trail that means something shows not only that a cell has changed, but also why, based on which rule and with what result traceable back to the original source. That distinction is explained further in the explanation of why an audit trail is more than a log when the source is a spreadsheet.
Most organisations that work with spreadsheets do not have a system that automatically tracks lineage. That does not mean mapping is impossible, it means it has to be done manually, with discipline instead of software. Which steps are needed for that and how a spreadsheet-driven process can still become traceable is described in how you create lineage without a tool when the source is a spreadsheet. A related question that is often skipped is who owns the definition of a data point: without a designated owner of the definition, the meaning of a data point changes along with whoever happens to be looking at it at a given moment, and then even the most beautiful mapping has little value.
A tool that makes reports look nicer changes nothing about the reliability of the figures that go into them. If the path from cell to report has not been recorded, a tool produces neater reports about the same uncertain figures. The mapping from source to report is therefore not a step that comes after the tool, but one that comes before it.
Once the steps between source and report have been written out, it also becomes visible which of those steps are human work and which follow a fixed, repeatable operation. That distinction is the basis for the work scan from FTE TO AI, which calculates per task which part of the work can be taken over by AI, based on what has already been recorded about the operation, the rules and the origin of the data.
Vraag maar waar een datapunt vandaan komt. Dat is meestal de hele vraag.
Answers come from this site’s knowledge base. Not tailored advice, and not a scan of your company.