From Spreadsheet Chaos to a Single Source of Truth

Almost every growing organisation reaches the same point. Sales has its own spreadsheet. Finance has another. Operations keeps a third, and someone in the MD’s office spends two days at month-end stitching them together into a report that is already out of date by the time it is read.

The instinct is to buy a dashboard tool and hope the problem goes away. It rarely does. A dashboard sitting on top of inconsistent data simply shows the inconsistency faster. The real work is underneath — and it is far more achievable than most leaders expect.

Start with decisions, not data

Before touching a single file, list the five to ten decisions leadership makes every month. Which customers to prioritise. Whether to reorder stock. Where costs are drifting. Each decision implies a measure, and each measure implies the data needed to calculate it.

This step sounds obvious, but it changes the scope of the project dramatically. Instead of “centralise all our data”, the goal becomes “produce these twelve measures reliably”. That is a project you can finish.

Agree definitions before building anything

Ask three managers what “revenue” means and you may get three answers: invoiced, collected, or booked. None is wrong, but a dashboard can only show one at a time. Write a short definition for every KPI — what it includes, what it excludes, where the numbers come from and who owns it. This single page will prevent more arguments than any software.

A dashboard on top of inconsistent data simply shows the inconsistency faster.

Build the model, then the visuals

With definitions agreed, the technical path is well-trodden:

• Connect to the sources that already exist — spreadsheets, accounting software, databases, exports.
• Clean and shape the data with repeatable steps (Power Query is ideal for this), so the same transformation runs every refresh instead of being redone by hand.
• Model the relationships — customers to orders, orders to products, products to categories — so measures calculate correctly from any angle.
• Visualise last, designing each page around one audience and one set of decisions.

Automate the refresh, then retire the old reports

The value of business intelligence is not the first dashboard; it is the hundredth time it refreshes without anyone touching it. Schedule refreshes, set up alerts for the measures that matter, and then — deliberately — stop producing the manual reports the dashboard replaces. Running both in parallel for too long is how BI projects quietly fail.

A realistic sequence

1. Week 1–2: decisions, KPI definitions and a data inventory.
2. Week 3–5: data connections, cleaning steps and the first data model.
3. Week 6–7: the first executive dashboard, reviewed with its real users.
4. Week 8 onward: automated refresh, training, and the next department.

Timelines vary with data quality and scope, but the pattern holds: small, finished increments beat ambitious, unfinished platforms.

Where to begin

If your organisation is still assembling monthly reports by hand, the best first step is a short review of the decisions you make and the data you already have. That alone usually reveals a quick win — and a clear path to a single source of truth.

BPA Dynamics Insights Team
Consultants, analysts and engineers at BPA Dynamics writing about what works in practice.