Producing a Monthly Management Pack From SQL Queries

The management pack is the document that a leadership team reads once a month to find out how the business went. It is often a workbook, assembled over two days by someone who copies from five systems and fixes the formulas. The same pack can be produced in minutes by a script, which leaves the person two days to think about what the numbers mean. This article describes how to build one from SQL: the sheet layout, the comparison columns, the checks, the commentary and the sign off. It applies the methods of building Excel reports from SQL queries people trust to a recurring, high-stakes document.

Fix the shape of the pack

Readers form habits. They go straight to page three for the cash position. Keep the same sheets, in the same order, with the same names, every month. A typical pack has:

1. Summary. The headline figures and the three or four things that need attention.

2. P&L. Revenue, costs and profit by line, with comparisons.

3. Revenue detail. By region or product line.

4. Cash and balance. Cash position and key balance items.

5. KPIs. The handful of operating measures the business tracks.

6. Commentary. Words from the owners of each area.

7. README. Period, sources, definitions, version and checks.

Do not add a sheet because someone asked once. A new sheet is a standing commitment. Put one-off analysis in an appendix workbook. When a section really is no longer read, retire it by agreement.

One query per sheet, with the comparisons built in

Management reads a number against something: last month, last year, budget. Compute those comparisons in SQL so that every sheet uses the same logic and the workbook does no arithmetic you cannot audit.

WITH m AS (
  SELECT account_group,
         sum(amount) FILTER (WHERE period = %(period)s)                                   AS actual,
         sum(amount) FILTER (WHERE period = (%(period)s::date - interval '1 month')::date) AS prior_month,
         sum(amount) FILTER (WHERE period = (%(period)s::date - interval '1 year')::date)  AS prior_year
  FROM ledger_monthly
  GROUP BY account_group
), b AS (
  SELECT account_group, sum(amount) AS budget
  FROM budget_monthly
  WHERE period = %(period)s
  GROUP BY account_group
)
SELECT m.account_group,
       m.actual, m.prior_month, m.prior_year, b.budget,
       m.actual - b.budget                                   AS var_budget,
       round(100 * (m.actual - b.budget) / nullif(b.budget, 0), 1) AS var_budget_pct
FROM m LEFT JOIN b USING (account_group)
ORDER BY m.account_group;

FILTER clauses pick each comparison period from one scan of the table. nullif avoids a division by zero when the budget is zero. A left join keeps lines that have no budget instead of dropping them, so the totals still tie to the ledger. The period is a parameter, a date for the first day of the month, and the query file is named for the sheet (designing a report query before touching Excel explains these choices).

Sign conventions are where packs go wrong. Decide whether costs are positive or negative, and show variances so that a positive number is always good news or always a larger amount, and say which in the README. Mixed conventions in one pack lead to confident misreading.

Build the workbook from the results

Each sheet is a data frame written with its formats. A helper keeps the sheets consistent.

def write_sheet(xw, name, df, money_cols=(), pct_cols=()):
    df.to_excel(xw, sheet_name=name, index=False)
    wb, ws = xw.book, xw.sheets[name]
    money = wb.add_format({"num_format": "#,##0;[Red](#,##0)"})
    pct = wb.add_format({"num_format": "0.0%;[Red]-0.0%"})
    head = wb.add_format({"bold": True, "bg_color": "#EEEEEE", "bottom": 1})
    for i, col in enumerate(df.columns):
        ws.write(0, i, col, head)
        if col in money_cols:
            ws.set_column(i, i, 14, money)
        elif col in pct_cols:
            ws.set_column(i, i, 10, pct)
        else:
            ws.set_column(i, i, 22)
    ws.freeze_panes(1, 1)
    return ws

For a percent column stored as 12.3, divide by 100 before using the percent format, or store the number and use a 0.0 format with a "%" in the header; do not mix the two. Put totals in a final row with formulas that sum the visible data, and write the formulas so the reader can click the cell and see them.

A chart or two on the summary is worth the effort: revenue against budget by month, and a bar of variance by region (sending charts from a query to an Excel sheet). Keep the summary to a screen.

Check that the pack ties out

A management pack that does not tie to the ledger is worse than no pack. Build the checks into the job.

• Sheet to ledger. The P&L total equals the ledger total for the period, to the cent.

• Sheet to sheet. Regional revenue sums to the P&L revenue line.

• Period to period. The prior month figures equal what last month's pack reported as actual (unless a restatement is recorded).

• Sanity. No line moves more than a set percentage without a flag; opening balances equal last month's closing.

checks = []
checks.append(("PL revenue equals ledger", round(pl_revenue - ledger_revenue, 2) == 0))
checks.append(("Regions sum to PL", round(region_total - pl_revenue, 2) == 0))
failed = [name for name, ok in checks if not ok]
if failed:
    raise SystemExit("Pack blocked: " + "; ".join(failed))

Write the list of checks, with pass or fail, onto the README sheet. A reader who sees "All 12 checks passed" trusts the pack more, and a reader who sees a failed check knows not to rely on a number. If you cannot fix a failure before the deadline, publish with the failure shown rather than hiding it, and let the owner decide.

Commentary belongs to people

The numbers can be produced by a script. The reasons cannot. Revenue is down because a large customer delayed an order; the margin gain is a one-off. That explanation is the part of the pack the leadership reads first, and it should come from the person who knows.

Store commentary as data, in a small table the owners edit: period, section, text, author. The build reads the rows for the period and writes them to the Commentary sheet.

SELECT section, body, author FROM pack_commentary WHERE period = %(period)s ORDER BY section;

If a section has no commentary by the deadline, the sheet says so rather than leaving a blank. That nudges owners and shows that the omission is known. It also means the commentary survives a rebuild: you can rerun the pack after a correction without anyone retyping their words.

Schedule, review and sign off

Run the build on a fixed day, usually the second or third business day after close, once the ledger is locked. A scheduler runs it, writes the workbook to a staging location and notifies the reviewer. The reviewer opens it, looks at the summary and the checks, and approves by recording their name and the time, either in a table or by moving the file to the distribution folder. Only then does it go out.

# 07:00 on the 3rd business day: build to staging, notify reviewer
python build_pack.py --period 2026-09-01 --out /srv/packs/staging/

Keep every version: the file, the commit of the queries, the check results, the reviewer and the distribution list. Six months later, when a director asks why September was restated, the archive answers. The same archive supports audit requests without a scramble (version controlling the query behind an Excel report).

Restatements and late adjustments

Books change after the pack goes out. A late invoice posts to September in October. Decide the policy before it happens. The common choices are to restate the prior month in the next pack with a flag, or to leave the original and show the correction as a current-month item. Whichever you pick, make the prior-month column show the restated figure when the policy says so, and add a line in the README listing changes since the last pack. Silent restatements destroy trust faster than errors do.

Access and confidentiality

A management pack holds the most sensitive numbers in the company. Treat distribution as a control: send to a named list, prefer a link to an access-controlled location over an attachment, strip hidden data and metadata, and consider a watermark with the recipient's name for packs that must not spread (hidden sheets and metadata leaking out of shared workbooks). Lock formula cells so numbers are not changed in transit (protecting a generated workbook from accidental edits), while remembering that this is a guard against accidents, not against a determined editor.

When a management pack is the wrong deliverable

A workbook pack is the wrong deliverable when leaders want to explore, since a fixed document answers the questions you anticipated and a dashboard answers the next ones (building dashboards in Excel from SQL output is the middle path). It is wrong when the business is small enough that a single page of numbers and a call serves everyone. And it is wrong when the underlying data is not trustworthy yet, because a polished pack over uncertain numbers persuades people more than the numbers deserve. Fix the data first, then automate the presentation. The PostgreSQL documentation on aggregate expressions covers the FILTER clause used above, and the XlsxWriter format reference lists the number format options.