From chaotic Excel to a real-time dashboard: a transformation case study

When we arrived, operations ran on a data architecture that, being generous, we would call “distributed”: a dozen Excel sheets in shared folders, versions emailed around every morning and one person spending half of every working day consolidating it all into a “master” file for management.

The problem was not a lack of data. They had too much of it, in too many places. And turning it into useful information took so long that, by the time the report reached management, it was already history.

The context: logistics with fragmented data

The company handled freight transport for B2B clients. Its operation included:

• Several warehouses entering data into local systems.
• A vehicle fleet with route sheets on paper and in Excel.
• A maintenance department keeping records in Access.
• Invoicing in accounting software disconnected from operations.
• Clients asking “where is my shipment?” and answers that took hours to arrive.

The reporting system worked like this: each warehouse emailed an Excel file to the reporting manager at 9:00. He consolidated it by hand, applied formulas, built charts and sent a PDF to management around midday. By then, the data was between 3 and 15 hours old.

The problems were predictable: conflicting versions (someone forwards an old file by mistake), human errors during consolidation, no way to dig deeper (why is this number what it is? time to open the original files one by one) and the constant frustration of operating on outdated information.

Groundwork: understand before you build

Before proposing any technology, we spent 2 weeks understanding 3 things.

Which decisions are made with which data. Management wanted to see warehouse occupancy, route performance, incidents and operating cash flow. Operations needed real-time visibility of pending loads and ongoing problems.

Where the original data lives. Some of it sat in a warehouse management system with an API. Some stayed in Excel because there was no system at all. And some started life on paper and was later typed into Excel by someone.

Which steps were actually necessary. Many consolidation steps existed only because the systems did not talk to each other, not because they added value. If a step only exists to copy data from one place to another, it is a candidate for removal.

The solution: a unified data architecture

We designed a system in 3 layers.

Layer 1: data ingestion

• API connections: systems that already had an API (the warehouse software and the fleet GPS) were connected directly. The warehouse synced every hour; the GPS, every few minutes.
• Structured uploads: for data that remained in Excel (maintenance), standardized templates with validation that were uploaded to the central database. If a field was missing or a date made no sense, the system rejected it on the spot.
• Digital forms: a simple mobile app replaced the drivers’ paperwork and fed the database directly.

Layer 2: central database and processing

A PostgreSQL database where all the data converged, with automated processes for:

• Cleaning and normalization (the same client was spelled in several different ways depending on the source).
• Derived calculations (performance per route, occupancy percentage).
• History (keeping the evolution over time so weeks and seasons could be compared).

Layer 3: dashboard and reporting

• Operational dashboard: Metabase (open source) showing automatically updated KPIs, accessible from a browser and from a phone.
• Automatic alerts: when a warehouse goes above 90% occupancy, when a route builds up more than 30 minutes of delay, and so on. Notifications by email and Telegram.
• Scheduled reports: daily and weekly PDFs sent automatically, with no human involvement.

Implementation: phases and lessons learned

Phase 1 (month 1): quick wins

We connected the existing APIs and built a basic warehouse occupancy dashboard. Visible value within the first month, which created the trust and momentum needed for what came next.

Phase 2 (months 2 and 3): manual data

We replaced the loose Excel files with structured forms. This was the most tedious part: convincing operations that the new process was better and iterating on the forms until they were genuinely easy to use.

Lesson: the resistance to change was not technological, it was habit. Everyone had “their” spreadsheets and trusted them more than any new system. We earned that trust by showing, week after week, that the dashboard displayed exactly what they saw in their local file.

Phase 3 (months 3 and 4): mobile app for drivers

We replaced paper route sheets with a simple app (a PWA rather than a native app, to shorten timelines and avoid depending on app stores). Drivers could mark deliveries as done, report incidents with a photo and check their next stop.

That was the turning point. Having each vehicle’s position and delivery status in real time changed the operation: the control center could see where every truck was and deal with incidents before the client picked up the phone.

Phase 4 (month 5): reporting automation

With data flowing, we removed manual consolidation. The person who used to spend half the day on reporting went from moving data around to analyzing it and proposing operational improvements. A change of role, not a job cut.

Measured results

6 months after completing the implementation:

Reporting time: from around 4 hours a day of manual consolidation to 0. Reports generate themselves.

Data age: from 3 to 15 hours old (depending on the time of day) to under 1 hour for most metrics, and real time for fleet position.

Errors from conflicting versions: gone. There is a single source of truth.

Client response: from “I’ll confirm in 1 or 2 hours” (while someone searched the spreadsheets) to “right now I can see your shipment is at this point and will arrive at this time”.

Operational decisions: the ability to spot trends (for example, loads piling up in a warehouse) and act early, instead of reacting to problems that have already happened.

Return: total development investment was around €25,000. The consolidation time recovered, plus fewer errors and faster responses, paid for it in less than a year.

What we got right

• Start simple and expand. We did not try to solve everything in the first release. Quick wins bought us room for the harder phases.
• Involve operations from day one. It was not “technology imposed from above” but “a tool that solves the problems you already have”.
• Keep Excel where it made sense. We did not replace it for the sake of it. For one-off analysis and what-if scenarios, Excel stayed available, now fed with reliable data.
• Document processes. The new system had clear documentation, something the Excel chaos never had. It is a part many projects skip, and it gets expensive later on.

What we would do differently

• More training up front. We underestimated how much training some non-technical users needed. One introductory session is not enough: people need support during the first weeks and a short guide at hand.
• More granular permissions from the start. We had to rebuild part of the permissions model when requests like “this department should not see that data” came up.
• Backups that are tested, not just configured. A minor data corruption incident reminded us that an automatic backup is worthless if nobody has ever tried restoring it.

Applying this to other cases

This pattern (scattered data, consolidation, dashboard and automation) works for many businesses:

• Retail: consolidating sales from several channels (physical shop, online shop, marketplaces).
• Manufacturing: connecting machines, inventory and planning.
• Services: team hours, project status and invoicing.
• Marketing: bringing metrics from several platforms into a single report.

The details change, but the 3-layer architecture (ingestion, processing, visualization) is solid and repeatable. And it does not always require changing systems: often it is enough to connect what already exists, as we explain in how to automate your ERP reporting without changing systems. If you want to work out first which information should flow on its own in your business, start with which business data should update itself.

Conclusion

The transformation was not about “dropping Excel”. It was about no longer using Excel for what Excel does badly: acting as a real-time, multi-user data system. Excel is great for exploratory analysis; as an operational database it is a problem.

The real value was not the technology (PostgreSQL and Metabase are standard and affordable). It was understanding which data feeds which decisions and designing flows that connect them without friction.

If your business lives in a maze of spreadsheets shared by email and you suspect there is a better way to work, there probably is. Tell us how your operation runs today and we will tell you, with numbers, what makes sense to connect first.