Why month-end reporting still takes a week of spreadsheets, and how to retire them.

Why the workbook survives

Walk into almost any multi-site or multi-entity business between working day two and working day seven of the month, and you will find a senior controller updating a 40-tab spreadsheet. Raw trial balances, invoice registers, payroll summaries and stock reports are exported from the ERP, pasted into input tabs, and pulled through lookup tables into the management P&L and board pack.

Buying a dashboard tool rarely stops this. When someone connects Power BI or Looker directly to the raw ERP tables, the numbers do not match the board pack—because half of the business logic lives inside the controller’s workbook, not in the ERP.

The four jobs the spreadsheet is doing

To replace a month-end workbook, you first have to respect what it does. When we audit a management reporting pack, its formulas almost always fall into four categories:

Once those four jobs are separated, the problem becomes much smaller. Spreadsheets are a poor database and a dangerous place to hide data transformations, but they are only there because the platform underneath was never finished.

What belongs in the ERP vs the warehouse

A common mistake during a data platform project is to replicate every manual spreadsheet adjustment in SQL. That creates a second set of books: the warehouse shows the “adjusted” board numbers while the statutory ledger in the ERP still shows the unadjusted ones. Six months later, nobody can reconcile the two.

Draw a hard line before writing code:

• If it changes an accounting balance, put it in the ERP. Accruals, intercompany recharges, provisions and cost-centre corrections should be booked as journals in the ledger—even if calculated with the help of a model—so the trial balance remains the single financial truth.
• If it groups, joins or enriches balances for management reporting, put it in the warehouse. Mapping local ledger accounts to management lines, converting currencies at budget vs spot rates, and joining operational volumes to financial lines belong in tested SQL models.

Retiring the workbook tab by tab

Trying to replace an entire board pack in one big-bang cutover guarantees a stressful month-end. A safer path retires the workbook in four steps across two closes:

1. Automate the raw exports first. Pipe the ERP ledger, subledgers, CRM and payroll tables into the warehouse on a schedule. Even before the final views are built, the controller stops spending day one downloading CSV files by hand.
2. Move the mapping tables into version control. Take the account-hierarchy, cost-centre and entity-consolidation tabs out of Excel and store them as explicit reference tables in the repository, with automated tests that catch any new unmapped account code the day it is created.
3. Run one parallel close. For one month-end, let the warehouse produce the management P&L, gross margin schedule and working-capital tables alongside the existing spreadsheet. Every variance between the two will either expose a hidden manual plug in the workbook or a missing rule in the model.
4. Lock the numbers and generate the pack. Once the parallel close matches to the euro, connect the board views and recurring written brief directly to the warehouse reporting layer and archive the master workbook.

A close that does not depend on one person

The biggest risk of a spreadsheet-driven close is not the five days of labour every month; it is that only one person knows which cells must be updated when a new cost centre opens or an exchange rate changes. When that logic lives in tested SQL models, an automated assertion runs before any board view refreshes: if a subsidiary posts to a new GL account that is missing from the mapping table, or if the management P&L drifts from the statutory trial balance by a cent, the build halts and names the offending account.

WITH tb_by_entity AS (
  SELECT
    tb.entity_id,
    tb.fiscal_period,
    SUM(tb.credit_amount_eur - tb.debit_amount_eur) AS statutory_net_profit_eur,
    COUNT(*) FILTER (WHERE m.management_line_id IS NULL) AS unmapped_gl_rows
  FROM stg_erp_trial_balance tb
  LEFT JOIN ref_coa_mapping m
    ON tb.entity_id = m.entity_id AND tb.gl_account_code = m.gl_account_code
  WHERE tb.statement_type = 'PNL'
  GROUP BY 1, 2
),
mgmt_pnl_by_entity AS (
  SELECT
    entity_id,
    fiscal_period,
    SUM(reported_amount_eur) AS management_net_profit_eur
  FROM mart_management_pnl
  GROUP BY 1, 2
)
SELECT
  t.entity_id,
  t.fiscal_period,
  t.unmapped_gl_rows,
  ROUND(t.statutory_net_profit_eur - COALESCE(p.management_net_profit_eur, 0), 2) AS variance_eur
FROM tb_by_entity t
LEFT JOIN mgmt_pnl_by_entity p USING (entity_id, fiscal_period)
WHERE t.unmapped_gl_rows > 0
   OR ABS(t.statutory_net_profit_eur - COALESCE(p.management_net_profit_eur, 0)) >= 0.01;

With that check in place, the numbers are ready the hour the ledger closes—and anyone on the finance or data team can trace how every line on the board pack was calculated.