Excel Reports That Finance Teams Rely On Month After Month

A finance team judges a report by one thing before any other: does it agree with the books? Not roughly. To the cent, and in the same way next month. A dashboard that looks wonderful and is out by 300 units is worse than a plain table that ties out, because the dashboard gets quoted. Excel is where most finance work ends up, for good reasons: the tool is familiar, flexible and trusted by people who must explain every number to someone else. This guide covers how to build Excel reports that stand up to that scrutiny when the data comes from a database. It sets out the rules for reconciliation, rounding, currency and evidence, then points to the spokes for bank reconciliation, budgets, invoices, rounding policy, timesheets and multi-currency columns. If you are starting from the technical side, building Excel reports from SQL queries people trust is the general guide this one specializes.

What finance needs from a report

Ask finance people what makes them rely on a report, and the answers are consistent. They want to know where each number came from. They want it to agree with the ledger or the bank, and they want the differences explained. They want the same method every period, so that a change in the number reflects a change in the business and not in the method. They want to see who prepared it and who checked it. And they want to be able to reproduce it six months or seven years later, when an auditor or a tax authority asks.

None of that is about spreadsheets. It is about process, and a spreadsheet can serve or undermine it. Hand-assembled workbooks, with figures pasted from five systems and formulas edited in place, fail every one of those tests. A workbook generated from versioned queries, checked in code and archived with its inputs passes them. The difference is not the tool but the build.

Tie to the ledger first

The first requirement is agreement with the system of record. Before any formatting, decide what the report must equal: the trial balance for the period, the bank statement closing balance, the sum of posted invoices. Compute that control figure independently, with a simple query that shares no logic with the report, and compare.

SELECT sum(amount) AS ledger_total
FROM ledger
WHERE account_group = 'sales'
  AND posted_at >= DATE '2026-09-01' AND posted_at < DATE '2026-10-01';

If the report says 4,812,300.15 and the ledger query says 4,812,300.15, you have a foundation. If not, stop and find the difference before you format anything. Typical causes are a date field mismatch (invoice date versus posting date), a status filter that excludes adjustments, a currency conversion applied in one place and not the other, or a late entry posted after the extract. Each has a name and each is found by comparing subtotals until the difference localizes.

Build the comparison into the job. The job fails if the difference exceeds a stated tolerance, usually zero for amounts that should tie exactly, and a stated small figure for known timing items. The result of every comparison, pass or fail, goes into the evidence record.

Reconciliations are reports

Reconciling one source to another, such as a bank statement to the cash ledger, is a report in its own right and the one finance trusts most, because it exists to find differences. The output is not a total; it is a list: items in the statement not in the ledger, items in the ledger not in the statement, and matches with differing amounts. Each item has a reason or an owner. A reconciliation that shows "difference: 0.00" and no list of what matched is hard to believe. One that lists 412 matched items and 3 reconciling items with explanations is believable (reconciling bank statements against a database table walks through the matching).

Rounding, written down

Finance numbers are rounded, and the rule matters. Decide where rounding happens (per line, per document, at the total), which direction ties go and how a rounded total is allocated across lines. Implement it once, in the query or in a small function, and use it everywhere. Test the cases that expose differences: a half cent, a negative half cent, a tax computed per line against per invoice. Write the rule in the report's notes. An auditor who asks "how do you round VAT?" should find the answer in a sentence and in a test (rounding rules that survive audit in spreadsheets and SQL covers the policy and rounding and formatting numbers for finance readers the display side).

Do not round for display and then sum the displayed figures; the column will not add up. Keep exact values, round at the defined point and say so.

Currency, stated and converted once

Multiple currencies are a common source of errors that are small, frequent and hard to see. Rules to adopt: store the original amount and its currency code on every line; convert to the reporting currency in one place, using a stated rate source and date (the transaction date, the month average or the closing rate, depending on the accounting policy); keep the rate used beside the converted amount so the conversion can be audited; never sum amounts in different currencies. A report that mixes currencies in one column without saying so is misleading by construction (handling multi currency columns in workbooks and tables and handling currency and locale in generated workbooks cover the details).

Evidence for every run

An auditor does not want to be told the report is right. They want to see how you know. Make the evidence a by-product of the build. For each run, store:

• The parameters (period, entity, currency).

• The query files and their version (the commit hash).

• The row counts and control totals, with the comparison results.

• The output file and its checksum.

• The time of the run and the identity of the job.

• The reviewer's name and the time of approval, once given.

Store them together, in a place with controlled access and a retention period that matches your obligations. When a question arrives about the September report in March, you pull the record, rebuild from the stored commit if needed and show the same numbers. The README sheet in the workbook carries a summary of the same facts (putting a README sheet into every generated workbook).

Controls that matter more than formatting

Finance auditors and internal control frameworks look for a few things in any report process. These controls are cheap to build in code.

Separation of duties. The person who writes or changes the query is not the person who approves the report for release. A pull request approved by someone else, and a sign-off recorded by someone else, satisfy it.

Change control. Changes to the logic go through review and leave a history. Version control gives this for free (version controlling the query behind an Excel report).

Access control. The job's database role is read-only. The output location is writable only by the job. Distribution is to a named list.

Completeness and accuracy checks. Row counts against the source, control totals against the ledger, and sanity checks against the previous period.

Locked outputs. The released file is a final version: copies are read-only, the original is archived, and later edits create a new version, not an overwrite.

Exception handling. A failed check stops the release and notifies an owner. Overrides are rare, require a named approver and are recorded.

None of these is about Excel specifically. They apply to the process around it.

The common finance workbooks

Finance reports cluster around a few shapes, each with its own checks.

Bank reconciliations match two lists of transactions and explain the rest. The checks: both lists complete for the period, no item matched twice, differences classified.

Budgets and forecasts combine actuals from the ledger with assumptions from planners. The checks: actuals tie to the ledger, budget versions are labeled and frozen, variances are computed one way (building a budget workbook from query output reliably).

Invoice exports list documents with lines and totals. The checks: line totals add to document totals, tax calculated by the stated rule, no gaps in the invoice number sequence (exporting invoices to Excel with totals that add up).

Timesheets record hours by person, project and day. The checks: hours per day within limits, no duplicates, approvals present, project codes valid (timesheet data from Excel into a database without loss).

Management packs summarize all of the above for leaders (producing a monthly management pack from SQL queries).

Knowing the shape of the report tells you which checks to write before the first draft.

A worked month-end run

Take the monthly sales and cash report. On the third business day, after the ledger locks, a scheduled job starts. It checks the close flag in the ledger (if the period is not closed, it stops and says so). It runs the queries with the period parameters and a read-only role. It computes the control totals independently: sales per the ledger, cash per the bank feed. It compares them to the report's totals, to the cent. It checks row counts against the source tables and each sheet's totals against the summary.

If every check passes, it builds the workbook: a Summary, the P&L, revenue detail, cash, a Reconciliation sheet listing the items that explain the difference between bank and ledger, and a README with the parameters, the commit hash, the checks and the time. It writes the file to a staging folder and notifies the reviewer, a person in finance who did not write the queries.

The reviewer opens the Summary, reads the check results, looks at the largest movements against last month and the list of reconciling items. If satisfied, they approve through a simple form that records name and time. The job then moves the file to the distribution folder, locks it, sends the link to the named list and archives the evidence record. If a number is wrong after release, a corrected version is produced and the README of the new file says what changed and why.

The whole run takes minutes. The two days that used to go on assembly now go on reading the numbers and writing the commentary, which is the part that needs a person.

Auditors and tax authorities

External auditors test controls and sample transactions. They like deterministic processes with evidence, and they dislike surprises in spreadsheets: manual overrides, hard-coded numbers inside formulas, links to files on someone's desktop, macros nobody can explain. A workbook generated from reviewed code has none of those. Give auditors what they ask for in a form they can use: the process description, the query files, the check results for the periods sampled, the sign-off records and the output files. Keep the vocabulary of the business, not of the tooling.

Retention is a legal matter that varies by country and document type. Find out what applies, store accordingly and make sure the archive is readable years later: keep the files in xlsx and a CSV or PDF form, and the queries as text. A format that needs a particular version of software to open is a risk.

Spreadsheet risk, honestly

Spreadsheets are a recognized source of financial errors: a range that stops short, a pasted value over a formula, a lookup that matched the wrong row. Research on spreadsheet errors over decades supports a plain conclusion that no one should be surprised by: complex workbooks maintained by hand contain mistakes. Reducing the risk is not a matter of trying harder. It is a matter of keeping logic out of cells where you can, generating what you can, testing what you build and reviewing what matters. A workbook that is a presentation of a tested query carries much less risk than one that is the calculation.

Cut-off and the close calendar

Every finance report is tied to a period, and the boundary of the period is a source of disagreement. A sale invoiced on the last day but shipped on the first belongs to one period under one policy and the other under another. Pin the rule down, in words, with the finance team: which date drives each report (invoice date, posting date, delivery date), in which time zone, and how late entries are treated. Then express it in the query as a half-open date range and a named column, and repeat it in the README.

The close calendar decides when data is final. Reports built before the books are closed show numbers that change. Tie the job to the close: it should check a flag or a date in the ledger and refuse to produce a final pack for an open period. Allow a clearly labeled draft mode for planning, with a watermark and a note on the first sheet: "Draft, period not closed". A draft that is mistaken for a final is the commonest cause of numbers quoted and then corrected.

Late entries after the pack is released need a policy. The usual choices are to reissue the pack with a revision number or to carry the adjustment into the next period with a disclosed note. Choose before the first case, because the decision under pressure is always the one that avoids the awkward conversation.

Adjustments and restatements

Journal adjustments are where finance judgment enters the numbers. A report should show them, not bury them. Where possible, keep adjustments as rows in the ledger, with a type and a reference, so that the report can show the figure before and after adjustments. Where adjustments are made in the workbook (a reclassification the ledger will catch up with later), put them on their own labeled sheet, with an owner and a reason for each line, and have the totals reference that sheet. A hidden plug number inside a formula is the thing auditors fear most.

When prior periods are restated, the comparison columns in later reports need to follow. Decide whether the prior month in this pack shows the figure as originally reported or as restated, say so on the README and keep the original reported figures available. A reader who sees a prior-month figure change without explanation stops trusting both months.

Materiality and tolerances

Not every difference needs a hunt. Finance sets a materiality level, an amount below which differences are not investigated. Use the same idea in checks: exact tie-outs where the amounts must be equal (a ledger total against a subledger), and tolerances where small timing or rounding differences are expected. Write the tolerance in configuration with a comment on its origin, and make the check report the difference, not just pass or fail. A difference of 0.01 that appears every month has a cause worth a day's work; a difference of 0.00 is the baseline.

Tolerances should shrink as the process matures. If the pack ties to within 5.00 in the first month, aim for 0.00 by the sixth. Track the differences over time in the evidence record; a drifting difference is an early warning that a source changed.

Variance commentary

Numbers tell what; commentary tells why. Finance packs are read for the commentary as much as for the figures. Keep the two separate: the machine produces the numbers and the variances, the owners of each area write the explanation. Store commentary as data, tied to the period and the line, so that a rebuild does not lose it. Provide a threshold, such as a variance above 5% and 10,000 units, above which commentary is required, and have the job list the lines that exceed it with no comment yet. This turns a vague request into a short list, and it makes the pack late for a reason that everyone can see.

Resist auto-generated explanations. A sentence produced by a formula ("revenue increased due to higher sales") says nothing and trains readers to skip the section. A person who knows that a large customer moved an order into October can say so in ten words.

Working with the ledger extract

Most finance workbooks start with an extract from the accounting system. Treat the extract as a controlled input. Record its date, its parameters and its row count. Prefer a direct, read-only connection to a reporting copy of the ledger to a CSV that someone exported by hand, because a hand export has no record of the filters used. Map accounts to report lines through a table you maintain (a chart-of-accounts mapping), not through formulas that test account numbers; when a new account appears, the mapping check lists it as unmapped and the report stops until it is placed.

That unmapped check is one of the most valuable controls in the whole process. New accounts, cost centers and entities appear constantly. A report that silently drops them understates whatever they belong to, and a report that fails loudly gets the mapping fixed in an hour.

Privacy and sensitive data

Finance data often includes personal information: payroll, expense claims, supplier bank details, customer names on invoices. Limit what goes into the workbook to what the reader needs, and aggregate where you can. Name and bank details do not belong in a management pack. Distribute through an access-controlled location, not as an attachment sent to a list that grows. Strip hidden data and metadata before release (hidden sheets and metadata leaking out of shared workbooks), and remember that protection on a sheet stops accidents, not people (protecting formula cells from edits in shared workbooks).

Training the reviewers

A review is only as good as the reviewer. Give them a checklist that matches the controls, and train them to use it. What does the summary tie to? Which checks failed or were overridden? What are the five largest movements from last month, and is there a reason for each? Do the reconciling items look plausible? Is there anything on the sheets that was not there last month? A reviewer who understands what the job already tested can focus on what a person is good at: spotting what looks odd.

Rotate reviewers so that knowledge is shared and nobody becomes a bottleneck. Record who reviewed each run, and sample the reviews now and then to see whether they are substantive.

Keeping the report stable over years

Finance reports live for a long time. The person who built the first version moves on, the accounting system is upgraded, the company acquires another and the chart of accounts is reorganized. A report that survives these changes was built to be maintained. Keep the logic in small, named query files with comments that explain the business rule in business words. Keep a short document that maps each sheet to its query and its owner. Keep the tests, because they are the only reliable description of what the report is supposed to do. When the ledger changes, run the tests against the new data first; the failures list what needs updating.

Plan for the day the report has to change shape. A new segment, a new currency, a new entity: each is easier to add when the report was built around parameters and mappings and not around fixed ranges and hard-coded names. A report with a segment column and a mapping table absorbs a new segment in minutes. A report with one block of cells per segment needs a rebuild.

Retire reports deliberately. Every report has a cost: the run, the checks, the questions. Once a year, list the reports that go out and ask who reads each. If the answer is nobody, stop producing it and say so. A smaller set of reports that people trust beats a long list that nobody reads.

When Excel is not a suitable system of record for finance

Excel should not be the system of record for transactions, balances or anything that needs an audit trail of who changed what and when. A file on a shared drive has no such history and no protection against two people saving over each other. Postings belong in an accounting system or a database with transaction control. Excel is suited to analysis, presentation, adjustments that are reviewed and exported, and the scenarios and forecasts that planners play with. When a workbook starts to hold the only copy of important data, or when several people edit it in turn, treat that as a signal to move the data into a system and keep the workbook as the front end (signs an Excel file needs to become a database).

Where to go next

Start with the control layer, reconciling bank statements against a database table, and the rounding policy, rounding rules that survive audit in spreadsheets and SQL. For documents and plans, read exporting invoices to Excel with totals that add up and building a budget workbook from query output reliably. For time and currencies, read timesheet data from Excel into a database without loss and handling multi currency columns in workbooks and tables. For the general build, return to building Excel reports from SQL queries people trust. The accounting standards bodies publish their own guidance, and your auditors can tell you which applies; for the technical side, the PostgreSQL manual on numeric types explains exact decimal arithmetic, and the XlsxWriter documentation covers the workbook side. The site's tools convert small samples between CSV and JSON in your browser.