Automating monthly reporting: moving beyond the hand-built workbook

Monthly reporting, a ritual that eats into the close

At the start of every month, the same scene plays out in many companies. Someone exports sales from the CRM, someone else extracts the accounts, a third person pulls hours from the planning tool. Everything flows into an Excel workbook that has become central, with hidden tabs, formulas nobody dares touch and links to files moved long ago. A few days later, a document goes to management. It is often right. Nobody can really prove it.

The problem is less the time spent than the fragility. Reporting depends on one or two people who know the order in which things must be done. If one of them is away, the report is late or wrong. And when a figure surprises, the management meeting turns into an investigation of where it came from instead of a discussion of the decision to take.

Automating reporting does not mean plugging an AI into that workbook. The chain has to be rebuilt from a few simple parts: known sources, written definitions, a calculation you can rerun, a readable dashboard, commentary drafted then reviewed, checks and a calendar. AI steps in at one precise point in that chain, on purpose.

Mapping the data sources

The first step is an honest inventory. For each figure in the current report, trace it back to its source: which software, which table or export, who produces it, when it becomes available and when it becomes final. That last point matters: the books are not frozen on the first day of the month, and reporting built on provisional data needs to know it.

The inventory almost always reveals three families:

• Application sources: ERP, accounting software, CRM, payroll tool, e-commerce platform. They usually have an API or a structured export.
• Working files: a budget in Excel, sales targets in a shared spreadsheet, manual adjustments made every month. These are the most fragile.
• Verbal knowledge: an adjustment the controller applies "because we have always done it that way". It must be written down before it can be automated.

At the end of the inventory, you know what can be collected automatically, what must first be stabilised, and what rests on a rule that was never documented. If much of the report comes from working files, read our article on the signs it is time to replace Excel with business software.

A metrics dictionary before any calculation

Ask three people in the same company how margin is calculated and you will often get three answers. Is revenue taken at invoicing or at order? Are year-end rebates deducted? Is a customer active if they ordered this quarter or this year? Until these questions are settled, no automation will produce a figure everyone accepts.

The metrics dictionary is a short document that sets, for each metric:

• its name and a one-sentence definition a manager can understand;
• its exact formula and the sources used;
• its scope: entities, activities, exclusions;
• its frequency and availability date;
• its owner, meaning the person who decides when in doubt.

This work is more political than technical. It forces old disagreements between finance and sales, or between two subsidiaries, to be settled. Yet it is the step with the greatest effect: once definitions are written, much of the debate in committee disappears. This dictionary is also what will later let AI comment on the figures with the right words.

A reproducible calculation

The core of the setup is a calculation you can rerun at any time and that gives the same result from the same data. That rules out copy and paste and adjustments typed into a cell. In practice, the chain looks like this:

• Extraction: data is pulled from each source by connectors, and a raw copy is kept for each period.
• Transformation: the dictionary's rules are applied in versioned code, SQL or Python depending on your environment. Every adjustment once made by hand becomes a named, visible, dated rule.
• Storage: calculated metrics are saved by period in a database, with the calculation date and the rule version.

That last point answers a common question: "why did the March figure change between the early April report and the late April one?". With a reproducible, dated calculation, the answer is a comparison of two versions, not an investigation.

This foundation has nothing to do with AI. It is classic data work, which we describe on the data and integration page. We stress it because many "AI reporting" projects fail by asking the model to do the calculations. A language model is not a reliable calculator, and it must never itself produce a published figure.

The dashboard: reading rather than leafing through

Once metrics are calculated and stored, presenting them becomes simple. The dashboard is built in the visualisation tool you already use, or in a dedicated web page if you want full control. A few principles avoid reproducing the workbook's flaws:

• One summary page that fits on a screen, with the metrics the committee actually looks at.
• Every metric compared with a useful reference: budget, previous month, same month last year.
• Access to detail for those who want to understand a variance, without forcing it on everyone.
• The definition one click away, taken from the dictionary, so a reader never wonders what a figure covers.
• The data freshness date shown clearly.

Many reports grow over the years because tabs are added and never removed. Automation is a chance to ask, for each table, who reads it and which decision it serves. What has no reader can stay available in detail without appearing in the summary.

Commentary drafted by AI, reviewed by a person

This is where generative AI brings real value. Every month, someone writes a few paragraphs explaining the figures: what went up, what went down, why. It is a long exercise, often done in a rush, and it depends heavily on the person.

A language model can produce a first draft of this commentary, provided it works within a strict frame:

• It receives metrics already calculated, with variances already computed by code. It calculates nothing, it describes.
• It receives the dictionary's definitions, so it uses the right terms and does not confuse two similar metrics.
• It receives known context: events of the month, notes from business owners, previous months' commentary.
• It has a caution instruction: only offer an explanation if it appears in the context provided, and otherwise flag that a variance remains unexplained.

An automatic check then verifies that every number quoted in the text exists, identically, in the calculated metrics. If the model writes a figure that is not there, the text is rejected or the figure is flagged to the reviewer.

The commentary is then reviewed, corrected and signed by a person: the controller or the finance director. The model saves drafting time, the person keeps responsibility for the analysis. The draft's main value is that it flags the variances to explain: the reviewer spends time on the real questions instead of describing obvious changes.

This step uses model calls every month. The cost stays modest for a monthly report, but it deserves tracking like any other; our framework for managing AI costs applies here too.

The checks that make the report reliable

An automated report that publishes a wrong figure does more damage than a late manual one, because it inspires trust it has not earned. Automatic checks are therefore a central part of the setup, not an option:

• Completeness checks: have all sources been loaded for the period? Is the row count consistent with previous months?
• Consistency checks: does reported revenue match the accounting total? Do subsidiaries add up to the group total?
• Plausibility checks: does a metric move beyond a threshold you defined? If so, it is flagged before publication, not blocked.
• Text check: does every number in the commentary come from the calculated metrics?

The results are shown to the reporting owner as a simple list: what passes, what warns, what blocks. They decide to publish, fix a source or postpone. Nothing goes to management without that explicit approval.

Aligning with the closing calendar

Monthly reporting depends on the accounting close. Automating one without looking at the other produces a fast but provisional report that will have to be redone. The calendar must therefore be built with accounting:

• When does each source become final? Sales may be available very early, costs only after the last invoices and accruals are posted.
• Do you need a flash version? Many management teams like a quick first read on the available metrics, clearly marked as provisional, followed by a final version after close.
• Who triggers what? The calculation can run automatically every night, but publication stays a human act, triggered once checks have passed.

Automation often has a positive side effect on the close itself. When checks flag the same missing entries or the same delays month after month, the problem becomes visible and gets fixed at the source.

How a project unfolds

We run this kind of project in short stages, keeping the existing report running in parallel until the new one has proven itself:

• Scoping: inventory of sources, list of metrics actually read, first decisions on definitions.
• Data foundation: connectors, reproducible calculation of the first metrics, storage by period.
• Parallel run: over several closes, the new calculation runs alongside the old one. Every difference is explained and fixed on one side or the other. This is often where errors in the old workbook come to light.
• Dashboard and checks: layout, alerts, approval flow.
• Assisted commentary: added last, once the figures are stable and accepted.

Order matters. Adding generated commentary before the calculation is reliable means eloquently describing doubtful numbers. A data engineer can carry this work in direct contact with the finance team; we describe that role on the data FDE page. For a smaller first step, our method to turn an Excel file into a tool may be enough to stabilise a critical workbook.

The objections we hear

"Our reporting is too specific to automate." It is specific in its rules, not in its mechanics. What makes it hard is unwritten rules. Once written, they are coded like any others.

"We already have a BI tool." Good, it will serve for the dashboard. BI settles neither definitions, nor checks, nor commentary. It displays what it is given.

"We do not want to send our figures to an American model." That is a legitimate choice. Commentary can be produced by a model hosted in Europe, and only aggregated metrics are sent to it. We cover the options in hosting an AI project in France.

"The controller will feel sidelined." In practice, they get back the time spent consolidating and put it into analysis, which is usually what they wanted to do. Involving them from scoping is essential; our approach to change management starts from that principle.

Taking stock of your reporting

Automated monthly reporting rests on few things: known sources, written definitions, a reproducible calculation, checks, and AI confined to drafting a reviewed first version. If you want to know where yours stands and where to start, our AI diagnostic starts from your real reports and sources to propose a realistic roadmap. The overall method is also described on the workflow automation page.

Frequently asked questions

Do we need a data warehouse to automate our reporting?

Not necessarily at first. A simple database is often enough to store metrics by period and keep the calculation history. A warehouse becomes useful when sources multiply or several teams want to use the same data.

Can we keep Excel for the final presentation?

Yes, if that is what your committee reads. The workbook then becomes a plain output fed by the calculation, with no formulas or manual adjustments. What matters is that figures are no longer built inside the file.

What happens if a source is not ready on time?

Completeness checks flag it before publication. The owner then chooses to publish a version marked as provisional, or to wait. The system never replaces missing data with an estimate without saying so.

Can AI answer management's questions about the figures?

It can, provided it queries the calculated metrics and the dictionary, never raw data without checks. We recommend stabilising the report and its commentary first, then opening this kind of use to a small group.

Who maintains the setup once it is live?

Calculation rules are documented code that your data team or a provider can evolve. The metrics dictionary remains owned by the finance department. Any new definition goes through it before being coded.