The person who rebuilds the monthly report could not tell you exactly how they did it last time. That is the real problem, and the time saved is the smaller half of the fix.
Every business has a report somebody rebuilds by hand. It takes an hour or two, it happens monthly, and the person doing it could not tell you exactly how they did it last time because small decisions get made along the way and forgotten.
The distinction that makes this safe
Automate the assembly. Never automate the interpretation. The script gathers, joins and formats. A person reads it and decides what it means. The moment a report starts drawing conclusions, you have built something nobody can be accountable for.
The time saving is the smaller benefit. The larger one is that the number is calculated the same way every month, so a change in the number means something changed in the business.
How to build it
1. Write down the current manual process, while doing it
Not from memory. Open the document and record each step as you take it, including the small judgment calls you make without noticing. Those undocumented decisions are the reason two people produce different numbers from the same data.
2. Agree the definitions before automating them
What counts as an active client, a billable hour, a closed deal. Automating a metric nobody agrees on produces an authoritative-looking number that people quietly dispute. Settle this first and write it into the report itself.
3. Pull raw data into one place on a schedule
Overnight exports into a single sheet or table, one tab per source, untouched and unformatted. Keep raw data separate from calculations so you can always trace a number back.
4. Calculate in a layer above the raw data
Formulas or a script that read the raw tabs and produce the metrics. When something looks wrong you can check the working, which is what makes the report trustworthy.
5. Show the freshness on the face of it
A timestamp saying when the data last refreshed. Without it, a failed overnight job produces a confident-looking dashboard full of last week's numbers, and nobody notices for a fortnight.
6. Push a summary rather than waiting for a visit
Three or four headline numbers into the channel people already use, with a link to the detail. Dashboards get opened during good months and avoided during bad ones, which is precisely backwards.
Tools and what they cost
| Option | What it costs | Honest trade-off |
|---|---|---|
| Google Sheets plus Apps Script | Free with Google Workspace. | Where most small businesses should start. Handles scheduled pulls and calculation well. Mind the six-minute execution limit and batch your reads rather than looping row by row. |
| Looker Studio | Free. | Genuinely capable, connects to Sheets and many databases, and produces shareable dashboards. It is a viewer, so the scheduled push still needs building. |
| BI tools (Power BI, Tableau, Metabase) | Power BI is a few dollars per user; Metabase has a free self-hosted tier; Tableau is considerably more. | Proper modelling and drill-down. Overkill until you have more data than a spreadsheet can hold comfortably. |
| Connector platform into a sheet | Per task or operation, and scheduled pulls run often. | Quick to set up. The pricing model penalises frequent scheduled runs, which is exactly this pattern. |
What it is actually worth
The time is easy to calculate: hours per report, times reports per year, times a loaded hourly cost. A ninety-minute monthly report is eighteen hours a year, and that is the floor rather than the value.
The real return is consistency. When a number is produced the same way every time, a movement in it is signal. When it is rebuilt by hand each month with slightly different decisions, a movement might be signal or might be the person who assembled it. Most small businesses cannot tell the difference and do not know they cannot.
And the availability effect. A report that takes ninety minutes gets produced monthly. One that takes zero can be weekly, and weekly is where operational decisions actually live, as the burn reporting article in this series argues.
How it breaks
A source changes format and the job fails silently. The most common failure. Validate the shape of incoming data and fail loudly rather than producing a confident report from partial data.
One wrong number destroys trust permanently. People go back to their own spreadsheets and never fully return. Sample-check against the manual method for the first three cycles before retiring it.
It grows to forty metrics. Every stakeholder wants their number added. Forty metrics means no metrics. Keep the headline view to five and put the rest behind a link.
Nobody notices when it stops. The freshness timestamp is the cheapest possible defence and it is usually missing.
How to tell whether it worked
Hours spent on manual reporting, obviously. But also reporting frequency, which should increase once the cost falls, and the number of people maintaining private parallel spreadsheets, which is the honest measure of whether the report is trusted.