Analyze budget variances, actual versus plan

Accountant · Time: 30 min · Finance

Tools that fit this task

• ChatGPT $20/mo
• Gemini in Google Sheets $14/mo
• Copilot in Excel $20/mo
• Microsoft 365 Copilot $20/mo

Prompt

Role: you are a financial controller.
Context: a budget table for {{period}} with columns line item | division | month | plan | actual | variance | variance %. The company works in {{industry}}, seasonality: {{description of seasonality or "none"}}. Materiality threshold {{percent}}% or {{amount}}.
Task: find all variances above the threshold, rank them by amount, and for each suggest one hypothesis for the cause and one question for the owner. Mark separately the line items where the variance repeats three months in a row.
Format: a table line item | division | plan | actual | variance | % | hypothesis | question. Then a list of systematic variances and a five-sentence comment for management with [CAUSE, confirm] where the cause needs confirmation. Take numbers only from the table.

Steps

1. Prepare a table: line item, division, month, plan, actual, with headers in the first row and no merged cells
2. Add formula columns for the variance in amount and in percent so the model works with ready numbers
3. Give the assistant the range with a prompt to find variances above a threshold and hypotheses for the causes
4. Get the top variances with a hypothesis for the cause and a question to ask the owner
5. Check the two largest variances against the source data and discard false hypotheses
6. Ask for a five-sentence comment for management and replace the hypotheses with confirmed causes

How to check the result

The top 5 variances match a manual sort, every cause in the comment is confirmed by the owner, and the total variance ties to the table

Pitfalls

• The model calls a seasonal peak an anomaly and ignores systematic overspending: set thresholds and seasonal context in the prompt
• A budget with real division names and payroll lines goes into a public service anonymized; inside a corporate Workspace or Copilot you can work on real data

Data that stays out of public AI tools

• Employee names, tax IDs, dates of birth and salaries: into a public service only as [Employee 1] and [amount]
• Account numbers, tax numbers and counterparty names from bank statements and the trial balance: banking and trade secrets, replace them with codes C1, C2
• Digital signature keys and passwords to online banking or the taxpayer portal: never, anywhere

Other tasks for accountants

• Reconcile the trial balance with the bank and source documents
• Categorize a bank statement by cash flow line
• Find discrepancies in a counterparty reconciliation statement
• Check a source document for required fields
• Prepare a reply to a tax authority request
• Write an overdue payment reminder
• Get a spreadsheet formula from a plain-language request
• Summarize changes in legislation for colleagues
• Explain the numbers to the business owner
• Draft an internal company order
• Write a business letter
• Summarize a long PDF
• Keep a contract register and track deadlines

AI tools for accountants · Prompt: Analyze budget variances, actual versus plan