Two departments report different revenue for the same month. One adds confirmed orders; the other counts invoices after credit notes. Both calculations may be correct. They answer different questions.
Changing the dashboard would leave that disagreement intact. The work starts with the metric’s meaning, followed by the data and transformations that produce it. An organisation can build this chain gradually while keeping its business systems and Excel where they serve a useful purpose.
Define the metric before automating it
The business owner first identifies the decision the report supports. Tracking orders awaiting delivery, invoiced sales and expected receipts requires different information. A label such as “revenue” does not settle those distinctions.
A short definition records the unit, sources and level of detail. The grain specifies whether one row represents an order, an order line or an invoice. If an export repeats the order total on every product line, adding those totals will inflate the result.
Dates and corrections belong in the definition too. The team agrees which date determines the period and how cancellations, credit notes and currencies are handled. These decisions become shared rules that the software can implement and users can review.
| Item | Illustrative definition to approve |
|---|---|
| Purpose | Track invoiced sales for the month |
| Grain | One uniquely identified invoice line |
| Period | Invoice issue date |
| Corrections | Credit notes deducted under the rule agreed with finance |
| Check | Total reconciled against a reference export |
| Responsibility | Finance approves the definition and investigates differences |
This example describes a method, not an accounting rule for every company. It makes the choice explicit before automation begins.
Build the data behind the report
The technical team identifies the sources and their identifiers, then examines how frequently each source can provide an extract. An API, a scheduled export and a database read provide different guarantees of freshness and completeness.
A shared database or warehouse receives the required information. Pipelines preserve source references and apply the agreed transformations. Where users need history, the model accounts for changes in status or scope instead of overwriting every previous state.
The architecture follows the workload and the operating capacity available. A relational database may suit a limited scope. More sources, frequent refreshes or demanding analysis may justify a larger platform. In either case, the team must be able to explain how source data became a published result.
Test a complete first pipeline
The first delivery should cover collection through to the report. Tests include invoices with several lines, late credit notes, foreign currencies, missing identifiers and interrupted extractions.
Checks cover expected files, unique identifiers and reconciliation of amounts. A job that finishes without a technical error may still have loaded incomplete data. The report should identify the period covered, the last successful refresh and any limitations that affect interpretation.
When a difference appears, the log helps the team distinguish a source issue from a poorly defined rule or an implementation error. The correction can then reach the right place instead of becoming another private spreadsheet formula.
Recovery belongs in the test. Reloading a file after an interruption must not count its rows twice. Comparing a test report with the agreed reference verifies that behaviour before regular distribution begins.
Choose presentation and prepare for change
Once the data and rules are established, the presentation choice becomes easier. Excel can read prepared data for exploratory analysis. A BI tool can distribute shared indicators. Permissions distinguish access to detailed records from access to monthly aggregates.
The project also assigns responsibility for hosting and operations. The team records who administers the platform, how failures are reported and how another person can maintain the pipelines. These decisions affect continuity as much as the database choice.
The first automated report provides a reference with an agreed definition, a traceable origin and a checked result. Later reports can reuse those foundations. The organisation gains a basis for new sources and uses without redefining the same rules for every request.
Continue with a versioned knowledge base.