Kennisbank

Building lineage before a tool is added

Lineage is nothing more than the path a number travels: from the place where it originates to the place where it appears in the report. That path exists even without a tool. Every time someone takes a figure from a spreadsheet, adds it to another figure, divides it by a number of FTEs or converts it to another unit, that person completes a small piece of lineage — whether it is captured or not. The question is not whether that lineage exists, but whether someone can retrace it without having to call in the original creator.

The steps between source and report

A data point in a sustainability report almost always has a number of steps behind it. First there is the source: a spreadsheet with energy consumption per site, an export from an HR system, an invoice from a supplier. Then follows an operation: adding up, averaging, converting to a CO2 equivalent, linking to an emission factor. Often an aggregation follows: figures per site become figures per country, figures per month become figures per year. Finally the figure ends up in the report, often through one more manual copying or pasting layer.

To understand what source-to-report mapping means when the source is a spreadsheet, it helps to see these steps not as a single whole but as a series of separate actions, each with its own chance of errors. Whoever only knows the beginning and end of the journey cannot see where something went wrong in the middle.

Why every step must be captured

Without capturing, lineage only exists in the mind of whoever made the spreadsheet. As soon as that person goes on holiday, changes roles or leaves the organisation, the knowledge of what happened between source and report disappears. A controller who wants to verify a figure then has to guess or ask around. An external party assessing the report has to rely on trust rather than documentation.

The operations between source and report have often become invisible because they are hidden in cell formulas, macros or an employee's memory. To find out which operations sit between source and report when the source is a spreadsheet, it is necessary to identify every formula, every manual step and every link separately — not as a black box but as a series of separate actions.

The same applies to the two operations that occur most often in sustainability data: aggregation and normalisation. Aggregation — adding up figures from multiple sources into one total — requires capturing which sources were included and which were not, and why. Whoever wants to know how aggregation is captured when the source is a spreadsheet runs into the necessity of documenting, per sum, which cells were included. Normalisation — reducing raw figures to a comparable unit — has a similar problem: which conversion factor was used, which source that factor comes from, and whether that factor remained the same throughout the year. For anyone wanting to capture this, an approach is described in how normalisation is captured when the source is a spreadsheet.

A log is not enough

A common mistake is to think that a log of changes provides sufficient lineage. A log shows when a cell was changed and by whom, but not why that change was necessary or which rule was behind it. An audit trail that only records what happened, without the underlying logic, leaves the same questions unanswered as no log at all. Why an audit trail must be more than a log when the source is a spreadsheet lies in the difference between recording an action and capturing the reason behind it.

What capturing without a tool means

In practice, without a tool this means: for every data point a fixed document or fixed section stating which source was used, which operations were applied in which order, who carried out the operation and on the basis of which rule. This can be done in a separate register, in comment fields next to the spreadsheet itself, or in a separate overview kept alongside the reporting process. It is more work than capturing nothing, and less work than setting up a tool on top of a process that does not yet know these steps. That is precisely the reason to go through these steps first: a tool placed on top of an unorganised process produces a neater output over the same untraceable figures. For the details of this approach, including the format in which lineage can be tracked per data point, there is an elaboration of how lineage is built without a tool when the source is a spreadsheet.

When manual work reaches its limits

Manually capturing every step between source and report is feasible with a limited number of data points and spreadsheets. With a larger number of sites, sources or reporting cycles, tracking every operation becomes a task that demands a lot of time and is sensitive to the very same errors that lineage is meant to expose. Once that limit comes into view, it is useful to know which part of this capturing work can be repeated according to a fixed rule and can therefore be taken over by AI, and which part still requires judgement. The werkscan from FTE TO AI calculates this per task, so that it becomes clear where manual work still fits and where repetition calls for something else.