Prompt templates that turn a budget export and an actuals export into a P&L someone can sign off

Background

A budget export and an actuals export are two lists of numbers against the same set of codes, usually from two different systems, in two different column orders, sometimes at a different level of detail. "Build me a P&L from these" is a completely reasonable thing to ask an AI tool to do - laying rows against a chart of accounts and summing them is exactly the kind of mechanical, structured task a model is good at. It is also exactly the kind of task where a model will quietly do the wrong thing and hand you back something that looks finished.

The failure mode isn't the model refusing or getting confused. It's the opposite: it produces a clean, confidently formatted P&L with a bottom line, a gross margin percentage, and a variance column, and every one of those numbers has been calculated for you rather than checked with you. If a code appears in the actuals but not the budget, it might get silently dropped instead of flagged. If two rows should net against each other and don't, the total still balances because the model adjusted something to make it balance. None of that shows up by looking at the output - it only shows up when someone downstream trusts a number that was never actually reconciled.

This guide is deliberately separate from the variance narrative templates - that guide assumes you already have a trustworthy set of numbers and need the why written up. This one is about the step before that: getting from two raw exports to a structured report where every subtotal, every derived figure, and every gap between the two files has actually been checked, not assumed.

Setup

The shape every template below follows:

• Role - a management accountant building a report for review, not a system generating a final answer.
• Context - what the two data sources are, and that they may not already agree.
• Inputs - the actual data (or its structure, if the data itself is too sensitive to paste - see Edge Cases), and the chart-of-accounts structure you want it grouped into.
• Task - build the report to a fixed structure, showing its working, not just the answer.
• Constraints - never derive a total without showing the rows behind it, and always flag what didn't reconcile rather than smoothing over it.

Building the P&L structure itself

For getting a first structured draft from the two raw exports:

You're building a management P&L from a budget export and an actuals
export against the same chart of accounts. Show your working, not just
the final table.

Inputs:
- Chart-of-accounts groupings I want, top to bottom: [e.g. REVENUE,
  COST OF SALES, GROSS PROFIT, OPERATING EXPENSES, OPERATING PROFIT]
- Which codes fall under which grouping: [LIST OR PASTE A MAPPING]
- Budget export: [PASTE OR DESCRIBE STRUCTURE]
- Actuals export: [PASTE OR DESCRIBE STRUCTURE]
- Period covered: [MONTH/QUARTER, FULL YEAR TO DATE OR SINGLE PERIOD]

Task: build a table with budget, actual and variance (£ and %) for every
line, subtotalled at each grouping (including gross profit and operating
profit as explicit subtotal rows, not just a final total). Show which
individual rows sum into each subtotal - don't just state the subtotal.

Constraints: every number in the output has to trace back to a row in the
data I gave you - never calculate a subtotal you can't show the addition
for. If a code appears in one export but not the other, list it separately
under "unmatched codes" rather than silently including or excluding it.
Don't round anything I gave you as an exact figure.

Reconciling before you trust either export

For when the two exports don't obviously agree before you've even started - different code lists, different totals, or a period boundary that doesn't quite line up:

You're reconciling a budget export and an actuals export against the same
chart of accounts before either is used in a report - the point is to find
every mismatch, not to produce a report yet.

Inputs:
- Budget export: [PASTE OR DESCRIBE]
- Actuals export: [PASTE OR DESCRIBE]
- What "should" match: [e.g. SAME CODE LIST, SAME PERIOD BOUNDARIES]

Task: list every code that appears in one export but not the other, every
code where the level of detail differs (one export has a code split into
sub-codes the other doesn't), and the total of each export by top-level
grouping so I can see at a glance if they're even close. Don't attempt to
fix any of it - just surface it.

Constraints: don't assume a near-match is a match (a code differing by one
digit, or a name that's almost the same, is a flag to raise, not something
to merge automatically). Present the mismatches as a checklist I can work
through, ordered by the size of the discrepancy, largest first.

Deriving gross margin and other ratios correctly

For the ratios that go wrong most often - because they depend on getting the right rows into the numerator and denominator, not just doing a division:

You're calculating gross margin and operating margin from a P&L structure
that's already been reconciled and confirmed correct.

Inputs:
- Confirmed P&L rows: [PASTE THE RECONCILED TABLE, OR THE PREVIOUS
  TEMPLATE'S OUTPUT ONCE YOU'VE CHECKED IT]
- Definition of gross profit for this business: [e.g. REVENUE LESS
  COST OF SALES, EXCLUDING X IF THAT'S HOW WE DEFINE IT]
- Definition of operating profit: [STATE WHAT'S INCLUDED/EXCLUDED]

Task: state the exact rows used as numerator and denominator for each
ratio before giving the percentage, so I can check the definition matches
ours rather than a generic textbook one. Give both the current-period and
budget-comparison ratio side by side.

Constraints: if my stated definition would produce a ratio that looks
obviously wrong (e.g. a negative gross margin on a business that's
profitable at the operating line), say so and ask me to check the
definition rather than reporting the odd number without comment.

A one-page management report from the confirmed numbers

Once the structure is built and reconciled, turning it into something a manager actually reads rather than a spreadsheet dump:

You're formatting a confirmed, reconciled P&L into a one-page management
report - the numbers themselves are already correct, this is a
presentation and structure task only.

Inputs:
- Confirmed P&L table, with variances: [PASTE]
- Reader: [e.g. DEPARTMENT HEAD WITH NO ACCOUNTING BACKGROUND, OR THE
  BOARD]
- Any commentary already written for the material variances: [PASTE, OR
  "NONE YET - HEADLINE FIGURES ONLY"]

Task: a summary table (revenue, gross profit, gross margin %, operating
profit, each vs budget) followed by up to five bullet points on what
matters this period, drawn only from the commentary I gave you.

Constraints: don't add new commentary beyond what I supplied - a bare
figure with no explanation is fine to show as-is; don't let the model
invent a reason for it just to fill the bullet point.