From scattered Excel files to one figure: the path to a single point of truth

Why two departments have two values

A plan in Excel starts out as one file. Then it is sent around, and one file becomes many. Every copy lives on: sales adds a column, the plant corrects a rate, controlling merges everything back together.

Four causes almost always come together:

• Copies: Nobody knows for certain which version is valid.
• Definitions: Sales' revenue includes items that controlling's revenue excludes.
• Points in time: One file reflects Tuesday's status, the other Friday's.
• Transfer: Values are carried by hand from one spreadsheet to the next.

Research on spreadsheets has described this for decades. Errors are rare per cell. In large spreadsheets, however, it is very likely that at least one bottom-line result is wrong [1]. The only method proven effective so far is cell-by-cell inspection [2], and nobody does that during the month-end close.

What single point of truth means

A single point of truth is a data foundation that everyone involved refers to. Every KPI has exactly one definition there. Every input is made in exactly one place. Every change is traceable: who, when, what.

Two misconceptions persist. A very large Excel file on a shared drive is not a single point of truth, because it keeps being copied. Nor is a new system on its own, as long as the old files are still maintained alongside it.

The path in six steps

1. Define the KPIs

For the ten to twenty most important KPIs, write down how they are calculated and who decides on them. This step needs no tool. It shows where the business areas contradict each other today.

2. Map the sources

For each KPI: where do the actuals come from, where the plan values, and which files do they pass through? The result is a picture of the data flow with every manual hand-over.

3. Build the data model

A model defines the dimensions used for planning and reporting: cost centre, product, period, version. The master data behind them must carry the same names in all sources. This is usually where the real work lies.

4. Move the inputs

Plan values are entered in the model and validated there. A validation rule catches a wrong entry at the point of input. In a file, the same error only shows up when the total does not add up.

5. Generate reports from the model

Once reports sit directly on the model, the copying-together disappears. The figure in the board report is the same one the planner entered.

6. Retire the old files

This step is often skipped. As long as the old file keeps running next to the model, there are two truths again. A fixed date from which only the model counts belongs in the plan.

Where it gets stuck in practice

Three things hold such projects up, and none of them is technology. First, master data: as long as a cost centre has two names in two systems, nothing can be merged. Second, ownership: every KPI needs someone who decides on its definition. Third, the transition: for one cycle, the old and the new process run side by side, and this duplicate effort has to be planned for.

Does Excel have to go?

No. Excel remains a good tool for quick analysis. What matters is that it reads the data from the model and does not hold its own version. Planning and storage happen in one place.

How you can tell you have got there

• In a meeting, nobody asks any more which version is meant.
• A new analysis does not require a new round of data collection.
• Every figure can be traced back to its input.
• If the person who built the file is absent, planning carries on.

The effort involved today can be estimated. Our benefit calculator works with your assumptions. The building blocks for planning and reporting are on our SAP Analytics Cloud page.