From spreadsheet to dashboard

Monday, nine in the morning

Someone on the team opens four files, exports a list from the management system, pastes columns, reconciles totals that never quite reconcile, formats the table and emails an attachment to eleven people. That took three hours. By Thursday the document no longer describes how the business is doing; it describes how it was doing on Monday.

This scene is the starting point of almost every project that reaches me. Nobody is to blame. Spreadsheets are a great tool and, for a while, the manual report does its job. Trouble starts when the business grows, the number of sources multiplies and that one file becomes the bottleneck for every decision.

Why the manual report eventually fails

It is always late. A hand-built report is refreshed as often as someone has time to build it. If one person assembles it every Monday, the business is being viewed with a week's delay, and every decision that depends on it arrives a week late too.

Every copy is a different version. The report goes out as an attachment. Whoever receives it adds a column, forwards it, and someone else filters part of it for their own meeting. Two months later six versions with the same file name are circulating with figures that do not match. When two people argue about which one is correct, meeting time goes into the data instead of the decision.

The work does not compound. Next month it starts from zero: same export, same copy and paste, same formatting. The effort leaves nothing behind.

Errors go unnoticed. A formula that stopped covering the last row, a filter someone left applied, a product code change nobody propagated. These errors are silent: they pile up until somebody notices that one product's margin has been wrong for months.

The real cost, in hours

It is worth putting numbers on it before deciding anything. A common example in a mid-sized company:

• Weekly sales report: 3 h × 46 weeks = 138 h per year
• Monthly close: 6 h × 12 months = 72 h per year
• Monthly quality report: 4 h × 12 months = 48 h per year

That is 258 hours, roughly 32 full working days spent copying, pasting and formatting. At an internal cost of €30/hour, close to €7,700 a year on work that adds no new judgment. And that is only the visible part: it excludes the cost of deciding late and the cost of deciding on a wrong number.

What you need before drawing a single chart

The most expensive mistake is starting with the screen. A beautiful chart built on numbers that do not reconcile only makes the error travel faster. Four things come first.

1. The source inventory. Where each piece of data lives, who maintains it and how often it changes. Half a page of notes like these settles most of the project's open questions:

• Orders — owned by Administration, refreshed hourly, feeds revenue and margin.
• Warehouse — owned by Logistics, refreshed daily, feeds stock and stockouts.
• Purchasing — owned by Procurement, refreshed weekly, feeds cost of supply.
• Customer service — owned by Support, refreshed daily, feeds open incidents.

2. One definition per metric. What exactly counts as a "sale": the order, the delivery note or the invoice? Are returns deducted, and when? That conversation takes an afternoon and saves months of later argument. Every metric needs a written definition, an owner and a calculation rule that can be audited.

3. A loading schedule. Not everything needs to be instant. Sales possibly do; purchase cost can be weekly. Setting the frequency source by source avoids building an expensive machine for data that changes once a month.

4. An owner per source. If a load fails, it must be obvious who gets the alert and who fixes it.

The transition, week by week

A first complete dashboard can be live in about eight weeks without disrupting day-to-day work.

Weeks 1 and 2. Questions and inventory. I sit down with the people who make decisions and write down the real questions: which products are losing margin, which stores are down against last year, how many orders ship late. Those questions produce the list of metrics, and that list determines which sources are needed. The inventory runs in parallel.

Weeks 3 and 4. Model and definitions. The first automated load is built, along with the model where the metrics are calculated. This is where the dictionary is closed: one definition per metric, no exceptions.

Weeks 5 and 6. First screen. The dashboard is designed backwards from the decisions, not forwards from the available data. It is validated with two or three people who will use it daily, and anything that is not understood on first sight gets fixed.

Weeks 7 and 8. Automation and adoption. Scheduled loads go live, along with alerts when a load fails and automatic delivery to recipients. Training happens here, and it rarely takes more than half an hour if the screen is well built.

Five mistakes I see often

1. Trying to migrate everything at once. Start with the report that eats the most hours. Once it works, the next one costs half as much.
2. Recreating the spreadsheet as it is. A screen is not a colored table. If someone has to read 40 rows to understand anything, the design has not done its job.
3. Publishing before reconciling. Before anyone sees the screen, compare a closed period against the source system down to the last decimal. If it does not reconcile, it does not get published.
4. Leaving sources without an owner. A source nobody owns will drift out of date.
5. Saving training for last. If the team discovers the screen the same day the old report is withdrawn, they will go straight back to the old file.

How to tell whether the change worked

Six months later, these five statements should be true:

• Anyone with permission opens their dashboard without asking a colleague for anything.
• Two people asking the same question get the same number.
• Time spent assembling reports has dropped below one hour a month.
• When a load fails, somebody knows the same day rather than the following month.
• The team opens the screen on their own initiative, not because they are reminded to.

So spreadsheets are out?

No. The spreadsheet is still the right tool for a one-off calculation, for testing an idea or for an analysis that will never be repeated. What it should stop being is the official source of the company's numbers. That distinction is the whole project: what repeats every month gets automated; what is exceptional is done by hand and forgotten.

Where to start

Pick the report that consumes the most hours and answer three questions: what decisions are made with it, where the data comes from and who maintains it. Those three answers are enough to map the full path.

If you would like me to take a look, write to me and I will run a free diagnostic of your data sources.