Building a Budget Workbook From Query Output Reliably

Budget season turns workbooks into small crises. Someone sends a version with Q3 changed, someone else edits an older file and a week later nobody knows which version is current or whether the total includes the late request from marketing. A budget is a document that many people touch over a long period and that must produce one agreed set of numbers at the end. The safest way to handle it in Excel is to make the workbook a view of a database table, with a clear path for planners' changes to come back in. This article builds that: actuals from the ledger, versions in a table, variance columns, input sheets and a release process. It applies Excel reports that finance teams rely on to planning, and the shape follows building Excel reports from SQL queries people trust.

Three kinds of numbers, kept apart

A budget workbook mixes things that must not be confused.

Actuals are what happened. They come from the ledger, are final once the period closes, and nobody edits them in the workbook.

The approved budget is what the organization agreed to. It is a snapshot at a point in time and it does not change after approval, except through a formal reforecast.

Forecasts and scenarios are working numbers that planners adjust. They change constantly and are labeled as drafts.

A sheet that holds all three in adjacent columns without labels invites the error where someone edits the "budget" column thinking it is the forecast. Keep them in separate tables in the database, and in separate, labeled blocks in the workbook.

Put budget versions in a table

CREATE TABLE budget_lines (
  version     text NOT NULL,           -- e.g. 'FY27-v3'
  period      date NOT NULL,           -- first day of month
  dept        text NOT NULL,
  account     text NOT NULL,
  amount      numeric(14,2) NOT NULL,
  PRIMARY KEY (version, period, dept, account)
);

CREATE TABLE budget_versions (
  version     text PRIMARY KEY,
  kind        text NOT NULL CHECK (kind IN ('budget','forecast','scenario')),
  status      text NOT NULL CHECK (status IN ('draft','submitted','approved','frozen')),
  frozen_at   timestamptz,
  note        text
);

A version label such as FY27-v3 identifies a complete set of lines. The primary key makes a duplicate line impossible. The status column records where the version stands. When a version is approved, set frozen_at and revoke update rights on its lines (or enforce it with a trigger that rejects changes to frozen versions). Now "the budget" means a version label, and anyone can ask for it by name.

Keep sign conventions in the column documentation: revenue positive, costs positive and subtracted in the variance, or costs negative. The choice matters less than writing it down and using one throughout.

Actuals and variances in SQL

Compute actuals, budget and variance in one query, so that every sheet uses the same logic.

WITH a AS (
  SELECT date_trunc('month', posted_at)::date AS period, dept, account, sum(amount) AS actual
  FROM ledger
  WHERE posted_at >= DATE '2026-07-01' AND posted_at < DATE '2027-07-01'
  GROUP BY 1, 2, 3
), b AS (
  SELECT period, dept, account, amount AS budget
  FROM budget_lines WHERE version = 'FY27-v3'
)
SELECT coalesce(a.period, b.period) AS period,
       coalesce(a.dept, b.dept)     AS dept,
       coalesce(a.account, b.account) AS account,
       coalesce(a.actual, 0)        AS actual,
       coalesce(b.budget, 0)        AS budget,
       coalesce(a.actual, 0) - coalesce(b.budget, 0) AS variance,
       round(100 * (coalesce(a.actual, 0) - coalesce(b.budget, 0)) / nullif(b.budget, 0), 1) AS variance_pct
FROM a FULL OUTER JOIN b USING (period, dept, account)
ORDER BY period, dept, account;

The FULL OUTER JOIN matters: a line with a budget and no actuals yet, and a line with actuals and no budget (an unplanned account), both appear. An inner join would drop the second, hiding unbudgeted spend. nullif prevents division by zero when a line has no budget. Future months have a budget and no actuals; show them as forecast, not as variance against nothing, by limiting the actual columns to closed periods.

For a full-year view, a year-to-date actual plus the forecast for the remaining months gives an outlook. Compute it in SQL with a case on the period, and label the column "Actual to date plus forecast".

The workbook layout

A layout that planners can follow:

• Summary: totals by department and the headline variance, with the version label and the as-of date.

• By department: one sheet per department, the same layout, showing months across and accounts down, actual, budget and variance.

• Input: the planners' sheet, described below.

• Lists: departments, accounts, months, version labels.

• README: definitions, sign conventions, the version, the query commit and the checks.

Write numbers as numbers, with formats set: #,##0 for budget lines, 0.0% for percentages. Freeze the first columns. Use a conditional format on variance (a minus sign and a color for adverse variance, in the direction the sign convention defines) and avoid red and green alone (writing conditional formatting from code in workbooks).

Input sheets for planners

Planners need somewhere to type. Give them a sheet that looks like a table: department (locked, filled in), account (locked), months across, input cells for the amounts. Mark input cells with a fill. Apply validation: numbers only, within a plausible range, no negatives where they make no sense. Protect everything else (locking down shared input sheets without breaking them). Pre-fill the sheet with the current forecast so that planners adjust, not retype.

When a planner returns the file, a loader reads the input table, checks it against the template (headers, types, ranges, every row accounted for) and writes the lines into budget_lines under a new draft version label such as FY27-v4-draft-ops. Never write straight into the approved version. A reviewer compares the draft with the current version in a variance report and either promotes it or sends it back (checking an incoming workbook against a template by code).

Handle several planners by department: each owns a slice of lines, the loader refuses lines for departments the planner does not own, and the combined version is assembled from the slices. That mirrors how responsibility works in the organization and prevents one person overwriting another's department.

Checks

Build checks into the load and the build:

• The total of the lines in a version equals the total the planner reported at the bottom of the sheet.

• Every dept and account in the input exists in the master lists.

• Every month in the fiscal year is present for every line, or explicitly zero.

• Actuals in the workbook tie to the ledger for each closed period.

• No frozen version changed since it was frozen: compare a checksum of its lines to the one recorded at freeze time.

• The sum of department sheets equals the summary.

SELECT version, md5(string_agg(period::text || dept || account || amount::text, ',' ORDER BY period, dept, account)) AS checksum
FROM budget_lines WHERE version = 'FY27-v3' GROUP BY version;

Store the checksum when the version is frozen and compare on every build. A change to an approved budget then cannot go unnoticed.

Release and freeze

A release is an event. When the version is approved, the job sets the status, writes the checksum, builds the final workbook with the version label in the file name and the README, locks the output and archives it with the evidence record (parameters, commit, checks, approver). Distribute by link to the archived file. From then on, "the budget" is that file and that version; changes are made in a new version, and the workbook for it shows the comparison to the previous one.

Reforecasts follow the same path: a new version of kind forecast, built from the latest actuals and revised assumptions, compared with the budget. Keeping the kinds separate prevents the common muddle where the budget is quietly overwritten by the latest forecast and variance analysis stops meaning anything.

Assumptions and drivers

Many budgets are built from drivers: headcount times salary, volume times price, a growth rate applied to last year. Keep the drivers in a table too, with the same versioning, and compute lines from them in SQL or in a clearly marked calculation sheet. A budget line that is the product of three drivers should show the drivers beside it. When someone asks why marketing is up 8%, the answer is a driver change, visible and dated (building Excel reports from SQL queries people trust covers the rule of keeping logic in queries). Avoid hard-coded numbers inside formulas, where an assumption becomes invisible.

When a budget should not live in a workbook

A workbook is a reasonable planning tool for a small organization or a single department. It stops being so when many planners edit the same lines simultaneously, when approvals need a workflow with an audit trail, when the model has thousands of lines with dependencies, or when the budget feeds other systems in near real time. Then use a planning application or a database-backed form, and let Excel be the reporting layer. Whichever you use, keep the rules above: versions, a frozen approved set, actuals from the ledger and checks. The PostgreSQL manual on FULL OUTER JOIN and constraints cover the SQL features used here.