Flux and variance analysis with AI: prompt that drafts explanations you can defend

When to use it

Use it for the monthly balance-sheet and P&L flux review, budget-vs-actual commentary, or quarterly analytics for auditors. The gain isn't the subtraction (Excel does that). It's drafting first-pass explanations from the GL detail, so reviewers spend their time on the items that don't make sense.

Input data

• Trial balance for current period, prior period and (for P&L) prior-year same period and/or budget.
• GL transaction detail for the accounts over threshold (or all accounts for the current period, if the file size allows).
• Your flux thresholds, e.g. both $[X] and [Y]%, or a materiality-based scale.
• Known events this period (acquisitions, price changes, reorgs, one-time items), from your close meeting notes.

The prompt

Replace the [bracketed] items. Paste it with your files attached. It works in any capable assistant; tool-specific notes are in the steps.

You are helping me prepare the [month/quarter] flux analysis.

Attached:
- Trial balances: current [period], comparison [period], budget [if applicable]: [files]
- GL detail for [period]: [file]
- Known events this period: [paste bullet list]

Thresholds: flag any account where the change is greater than $[X] AND greater than [Y]% (or greater than $[Z] regardless of %).

1. Build the flux table: account | current | comparison | $ change | % change | over threshold (Y/N). Confirm both trial balances foot (debits = credits) and say if they don't.

2. For each account over threshold, draft an explanation using ONLY the GL detail and the known events:
   - Identify the specific journal entries, vendors, customers or sources that drive most of the change, with amounts that add up to at least 80% of the variance.
   - Say what share of the variance you explained with specific entries, and what remains unexplained.
   - Note whether the driver looks recurring, one-time, timing (it reverses next period) or a reclass.
   - Flag anything that looks like a possible misposting: unusual account/vendor combination, entry that offsets in a different account, manual entry near period-end, round amounts.

3. Write each explanation in 1–3 sentences in this style: "[Account] increased $[X] ([Y]%) primarily due to [driver, $amount] and [driver, $amount]." No adjectives, no speculation.

4. If the GL detail doesn't explain the variance, write "UNEXPLAINED – needs preparer input". Do not fill the gap with a plausible business reason.

Steps

1. Pull the trial balances from the system of record after preliminary close, not a stale export.
2. Run the prompt. Check that the flux table foots and the threshold logic matches your policy.
3. For each explanation, open at least the top-driver entries in the ERP and confirm they say what the AI says they say.
4. Pay the most attention to the "possible misposting" flags and to any explanation that covers less than 80%.
5. Preparers complete the UNEXPLAINED items through inquiry with the business.
6. The reviewer approves the final flux in your normal template. If anything is reclassed, it goes through JE review.

Control map

What the AI may do, step by step. How ratings work.

Audit trail

• Store the source trial balances with the flux workpaper, plus the AI draft and the final reviewed version. Differences between them show the review happened.
• Reference JE numbers in the explanations so an auditor can trace them.
• Record preparer, reviewer and review date as usual.

Failure modes

• The "beautiful explanation" problem. A fluent, specific-sounding reason for a variance that was really a misposting. That's why the prompt demands entry-level drivers and an explained percentage.
• Explaining the wrong direction. Credit-balance accounts (revenue, liabilities) get sign-flipped. Check that "increase" means what you mean.
• Netting. Large offsetting movements inside one account hide behind a small net flux. Consider gross-activity thresholds for key accounts.
• Partial file reads on large GL detail. Compare the activity total the AI used to the TB movement.

Data handling

• Use a company-approved AI workspace whose terms say your inputs are not used for model training. Check your plan; don't assume it.
• Strip or mask what the task doesn't need: employee names, bank account numbers, customer PII, SSNs or TINs.
• If you are a public company, treat pre-release results as material non-public information and keep them inside approved systems.