How a Toronto Business Automated Data Consolidation and Management Reporting

Project overview

A Toronto business assembled its weekly management reports by hand from spreadsheets and applications. We automated the whole data flow, from retrieval to scheduled summaries. Every report now shows its sources and when it was refreshed.

The solution

We implemented five connected automations:

Scheduled retrieval from spreadsheets, APIs and business systems
Normalization and validation of the data
One consolidated reporting dataset behind every report
Automatic refresh of Excel and dashboard reports, with exception alerts
Scheduled summaries sent to authorized recipients

Business impact

Management now receives reports that refresh on a schedule from named sources.

Figures are retrieved and consolidated automatically instead of copied by hand
Missing data and failed refreshes raise an alert when they happen
Each report states its sources and last refresh time

The challenge

Each reporting cycle began with collecting data from several places. The figures were copied into a workbook, reconciled by hand and formatted into a report that was then distributed to management.

Sources used different formats and naming, so the same customer or product could appear under different names. When a source was late or a figure looked wrong, the problem often surfaced only after the report had gone out. Nobody could say exactly which data a report was based on or when it had last been refreshed.

Individual automations

The automations follow the data from the sources to the people who read the reports.

Scheduled data retrieval

Data arrives on a schedule, without someone remembering to fetch it.

Each source has a schedule set by the business.
At the scheduled time, the workflow retrieves data from the configured spreadsheets, APIs and business systems.
Each retrieval is recorded with its source, time and outcome.
A source that does not respond is retried, and then reported.

The business decides which sources are connected and how often they are read.

Data normalization and validation

Different sources are brought into one consistent form before they are combined.

Names, dates, currencies and categories are mapped to a single standard.
Required fields are checked, and values outside an expected range are flagged.
Duplicate rows and obvious inconsistencies are identified.
Records that fail validation are set aside with a reason.

Rules are set and maintained by the business. Rejected records are reviewed by a person.

Consolidation into a consistent reporting dataset

The reporting dataset is the single basis for every report.

Validated records from all sources are combined into one structured dataset.
Each row keeps a reference to its source.
Previous versions of the dataset are kept, so figures can be traced and compared.

The dataset is built only from validated data and never edited by hand.

Automatic refresh of Excel and dashboard reports

Reports update themselves from the dataset.

Excel reports and dashboards are connected to the reporting dataset.
When the dataset is refreshed, the reports are refreshed too.
Each report shows the refresh time and the sources behind it.

People interpret the reports. The refresh changes the data, not the report's structure.

Exception alerts

A missing source or a failed refresh is reported at once.

The workflow monitors each scheduled run.
If a source is missing, data fails validation or a refresh fails, an alert is sent to the responsible person.
The alert says which source and which step failed.
Reports from a failed run are marked, so that nobody reads old figures as new.

A person decides whether to fix the source, rerun the refresh or send the report with a note.

Scheduled management summaries by email

Management receives the figures without asking for them.

Summaries are generated from the reporting dataset on the schedule the business sets.
They go by email to authorized recipients, with a link to the dashboard.
Each summary states the refresh time and the sources.

Recipients are controlled by the business, and each summary is only sent to people authorized to see it.

Historical comparison and trends

Where reliable history exists, reports show change over time.

The workflow keeps the consolidated datasets from previous runs.
Reports compare the current period with earlier ones.
Where the history is not reliable, the report says so instead of showing a comparison.

Trends are shown only where the underlying data supports them.

How the workflow operates

This is the data flow of a scheduled report.

The schedule starts the run A schedule set by the business starts the data flow.
Data is retrieved Spreadsheets, APIs and business systems are read, and each retrieval is logged.
Data is normalized and validated Formats are standardized, required fields are checked and problem records are set aside.
The dataset is consolidated Validated records are combined into one reporting dataset with source references.
Reports are refreshed Excel and dashboard reports update from the dataset and show their refresh time and sources.
Problems are flagged Failed or incomplete runs raise an alert to the responsible person.
Summaries are sent Scheduled management summaries go to the authorized recipients by email.

Systems and integrations

Spreadsheets: source files that are read on schedule.
APIs and business systems: configured connections from which data is retrieved.
Reporting dataset: the consolidated, validated data behind every report.
Excel and dashboards: reports that refresh from the dataset.
Email: scheduled summaries and exception alerts.
Run log: a record of each retrieval, its source and its outcome.

Related services

Automation

Results

Report preparation time reduced: 85.7% typical month (Client-reported)
Hours saved per month: 61.7 hours per month (Client-reported)
Annual labour value released: $11,106 per year (Client-reported)
Reports generated automatically: 20 reports per month (Client-reported)
Reports generated automatically: 83.3% typical month (Client-reported)

All insights · Get in touch