Reporting automation: cost, architecture and a worked example
Reporting automation pays when you simplify the process first and size the build to the work it removes. Price it with one formula: hours per report × reports per month × hourly cost is what the manual process costs each month. Subtract the running cost, divide the one-off build cost by what is left, and you have the payback in months.
Every number in the worked example and the calculator is hypothetical, invented to show the arithmetic. The only measured figures in this guide come from the Empes reporting automation case study, and they are labelled as such.
Map the process before you automate it
Automating a broken process just makes it break faster. The first deliverable of any reporting project is a map of how the report is produced today: every export, every copy-paste, every hand-off between people and tools. You will use this map to decide what to remove before you decide what to build.
In the reporting automation case study, the report depended on a weekly manual export, a copy-paste routine across three separate tools, and a shared spreadsheet that was often out of date. The team spent several hours a week producing reports that were already stale when they were shared, and nobody fully trusted the numbers. The audit showed that several steps could be removed entirely. Only the steps that were left were restructured and automated.
Record each step in a table like this one, for at least two cycles of the report:
| Step | Who | Tool | Minutes | Where errors come in | Decision |
|---|---|---|---|---|---|
| Export last week’s orders | Analyst | Source system | 15 | Wrong date filter | Automate |
| Paste into the master sheet | Analyst | Spreadsheet | 20 | Rows pasted twice | Remove |
| Reconcile with the finance export | Analyst | Spreadsheet | 45 | Mismatched IDs | Fix in the data model |
| Format and send | Team lead | 15 | Old version attached | Replace with a live view |
The rows in this table are illustrative. The decision column is the useful part. Each step ends up as one of four things:
- Remove. The step exists only because of an earlier workaround.
- Fix in the data model. The step exists because the data is inconsistent. Fix the data once and the step goes away.
- Automate. The step is necessary, repeatable and rule-based.
- Keep with a person. The step needs judgement: a comment on the numbers, an exception, a decision.
Read the diagram as text
- Remove (it exists only because of an earlier workaround)
- Fix in the data model (it exists because the data is inconsistent)
- Automate (necessary, repeatable and rule-based)
- Keep with a person (it needs judgement)
Count the time people spend checking
A process audit that only counts the minutes spent producing a report undercounts. Measure the whole task, including the time spent checking figures, chasing corrections and answering questions about why two numbers disagree. Those minutes are often larger than the production time, and they are the minutes a trustworthy pipeline removes.
Count what the manual process costs
Once the steps are mapped, the monthly cost of producing the report by hand is one multiplication:
manual cost per month = hours per report × reports per month × loaded hourly cost
Each input needs a definition, or two people will calculate different answers:
| Input | What to include | Common mistake |
|---|---|---|
| Hours per report | Production, checking, corrections, follow-up questions | Counting only the export-and-paste time |
| Reports per month | Every run, including ad-hoc reruns after a correction | Counting the scheduled runs only |
| Loaded hourly cost | Salary plus employer costs, per productive hour | Using salary alone, or a billable rate |
If several people work on one report, add up their hours, or calculate each role separately and sum the results. Use one currency throughout.
Estimate the build and the running cost
The other side of the calculation has two parts: a one-off build cost and a monthly running cost. The work in the build follows the same structure in almost every project. These were the work packages in the case study:
| Work package | What it produces |
|---|---|
| Process audit | The map above, with a decision for every step |
| Data model redesign | Source data restructured for consistency, with one definition per metric |
| Automation pipeline | Source systems connected; manual steps removed |
| Reporting layer | An output that updates itself, in the format the team already uses |
| Error handling and alerting | A message to a named person when a run fails or the data looks wrong |
| Documentation and handoff | An operational guide the client’s team can run without us |
Estimate each package separately. The audit and the data model are usually the smallest packages and the ones that matter most, because every later package depends on them.
The running cost is easy to forget and it decides whether the project pays back:
running cost per month = tool licences + hosting + (upkeep hours × hourly cost)
Upkeep hours cover the times a source system changes a column, an export format or an API version. Set aside a small, honest number of hours for this.
Work through a hypothetical example
The numbers in this section are invented. They show the arithmetic, not a typical result.
Invented inputs
A team produces a sales summary twice a week, so eight times a month. Each run takes three hours once you include checking and corrections. Their loaded cost is 60 per hour, in whatever currency they use. A build is quoted at 9,000, and the tools and upkeep will cost 150 a month.
| Input | Hypothetical value |
|---|---|
| Hours per report | 3 |
| Reports per month | 8 |
| Loaded hourly cost | 60 |
| One-off build cost | 9,000 |
| Monthly running cost | 150 |
Calculate the saving and the payback
manual cost per month = 3 h × 8 × 60 = 1,440
net saving per month = 1,440 − 150 = 1,290
payback = 9,000 ÷ 1,290 ≈ 7.0 months
hours released = 3 h × 8 = 24 h per month
On these invented numbers, the build pays for itself in about seven months. After that it saves 1,290 a month, but only if the 24 released hours turn into a cost that actually goes down. The next sections explain that condition and how to test the result.
Run your own numbers
The calculator below uses the same formulas. It starts with the hypothetical inputs above. Replace them with the figures from your own process map. Nothing you type is sent anywhere.
Design the pipeline in four layers
A reporting pipeline that lasts has four layers. Keep them separate, so a change in one does not break the others.
sources → ingestion → data model → reporting layer
↘ ↘ ↘
alerting and run log
Read the diagram as text
- Sources: the systems the numbers come from
- Ingestion: pull each source on a schedule, unchanged, and keep a raw copy
- Data model: clean, join, and define each metric once
- Reporting layer: the format people already use
- Underneath every layer: Alerting and run log: a named person hears when a run fails or the data looks wrong
| Layer | Its job | Typical tools |
|---|---|---|
| Ingestion | Pull data from each source on a schedule, unchanged, and keep a copy | Power Automate or Power Flows, Python, a connector platform |
| Data model | Clean, join and define each metric once | SQL, Python, dbt on a warehouse, or a structured workbook for small cases |
| Reporting layer | Present the model in the format people already use | The team’s existing spreadsheet, BI tool or email |
| Alerting | Say when a run fails, or when the data is out of range | Email, chat notifications, the run log |
Keep the raw copy
Store what each source sent before you transform it. When a number looks wrong, you can then tell whether the source changed or your logic did. This also lets you rebuild the model after you fix a definition, without asking the source for the history again.
Define each metric once
Most mistrust in reports comes from one metric with two definitions. Put the definition in the data model, write it down in plain words next to the code, and let every report read from that one place. In the case study, this is what gave the team one source of truth and the confidence to use the numbers in decisions.
Separate time released from cash saved
The formula above gives a monthly saving in money, but that money only appears if something changes. Before you count it as a financial benefit, decide what the released hours will be used for.
| What happens to the released hours | Evidence to collect | How to report it |
|---|---|---|
| Overtime or contractor hours stop | Timesheets or invoices before and after | Cash saving |
| A planned hire is no longer needed | The approved headcount plan | Cost avoided |
| The team takes on more work at the same cost | Output volume before and after | Capacity, with its own measure |
| Reports arrive sooner and are trusted | Delivery time, number of corrections | Quality, not money |
Report capacity and cash separately. Count a financial benefit only where the evidence supports it, after the running costs. Do not count the same hours twice, once as cash and again as capacity.
In the case study, the measured result was that approximately 4 to 6 hours of manual work per week were eliminated, and that the reports now update automatically. The team regained confidence in the numbers and used data more actively in decisions. Both results are real, and neither needs to be turned into money to be worth reporting.
Build alerting and ownership in from day one
An automated report that fails silently is worse than a manual one. People keep using the last good version without knowing it is old. Error handling and alerting are part of the build, not an extra.
Check that each of these is in place before handoff:
- Every scheduled run writes a log entry: start, end, rows in, rows out.
- A failed run sends a message to a named person, not to a shared inbox.
- The data model checks itself: row counts against the last run, totals against a control figure, and no empty key columns.
- The report shows when its data was last refreshed.
- The operational guide explains how to rerun, how to add a source, and who to ask.
- Someone in the team owns the pipeline. Their name is in the guide.
Test what would make the project not worth it
A payback period calculated from a single set of inputs is fragile. Change one input at a time and see which change hurts the result most. Using the hypothetical example above:
| Scenario (hypothetical) | Net saving per month | Payback |
|---|---|---|
| Base case | 1,290 | 7.0 months |
| Each report takes 2 h, not 3 | 810 | 11.1 months |
| Four reports a month, not eight | 570 | 15.8 months |
| Build costs 50% more (13,500) | 1,290 | 10.5 months |
| Running cost triples (450) | 990 | 9.1 months |
Read the diagram as text
- Base case: 7.0 months
- Each report takes 2 h, not 3: 11.1 months
- Four reports a month, not eight: 15.8 months
- Build costs 50% more: 10.5 months
- Running cost triples: 9.1 months
You can also work backwards and find the largest build cost you would accept:
maximum build cost = net saving per month × payback you will accept (in months)
= 1,290 × 12 = 15,480 (hypothetical, for a 12-month payback)
If the project only works under the most optimistic scenario, simplify the process further or start with a smaller scope. If it still works under the worst scenario, the decision is easy.
Frequently asked questions
How long does a reporting automation project take?
It depends on the number of sources and how much of the process changes, so we do not quote a number before the audit. The audit itself is short: a few working sessions with the people who produce the report. It tells you how many sources, steps and exceptions there are, and that is what the build time follows from.
Should we use no-code tools or write code?
Use no-code tools where the sources have stable connectors and the logic is simple, and code where you need tested transformations, unusual sources or volume. Most pipelines end up as a mix. The reporting automation case study used Power Automate and Power Flows for the connections and Python where they were not enough.
What if the report changes every month?
Then automate the data, not the layout. A clean, documented data model survives changes to the report on top of it. If the questions themselves change every month, the right output may be a self-service view rather than a fixed report.
Is the saved time the same as saved money?
No. Time released becomes cash only if a cost changes: less overtime, fewer contractor hours, or a hire you no longer need. Otherwise it is capacity. That can be just as valuable, but report it separately and say what the team will do with it.
What does it cost to keep an automated report running?
Licences for the tools involved, plus a few hours of upkeep when a source system changes. Put both into the monthly running cost before you calculate payback. If you leave them out, the payback looks shorter than it will be.
Can we start with one report?
Yes, and you should. Pick the report that is produced most often, or the one whose errors cost most, and use it to build the data model the next reports will reuse. The second report is usually much cheaper than the first.
Where to start
Pick the one report your team produces most often and write down every step it takes, with the minutes each step takes, for two cycles. That list is the input to every formula in this guide, and it usually shows two or three steps that can simply stop. If you want a second pair of eyes on it, bring it to a scoping call.