How to Automate Variance Analysis in Excel (Without Breaking Your Formulas)

A monthly P&L review is a core part of the accounting close process. Before results are locked, accountants use it to sense-check the numbers, surface missed accruals or mispostings, and understand what drove movements in the P&L.

In practice, the process typically involves:

1. Compiling the initial P&L, including comparisons to the prior month, prior year, and usually budget or forecast.
2. Calculating variances across these periods to highlight material movements.
3. Investigating drivers of variances, drilling into transactions, journals, and cost centres to separate timing effects from genuine underlying changes.
4. Identifying and posting adjustments, such as late accruals, reclassifications, or corrections uncovered during the review.
5. Documenting variance commentary, explaining key movements for internal stakeholders, management, or the board.

Why Automating Variance Analysis in Excel Is Harder Than It Looks

Almost every finance team already uses Excel to review variances. And almost every team has tried to "automate" it at some point.

Common failure modes include:

• Copy-pasting outputs that overwrite formulas

• Adding helper tabs that quietly drift out of sync

• Power Query refreshes that break when file structures change

• Hard-coded variance tabs that need rebuilding every reforecast

• Commentary written outside the model, disconnected from the numbers

The result is often more work, not less.

Any automation that does not respect existing formulas, references, and financial logic will eventually be abandoned. Finance teams do not want a new model. They want the same model, with less manual effort wrapped around it.

The Current Ways Teams Try to Automate Variance Analysis

1. ChatGPT or Claude

Large Language Models have become increasingly capable at explaining financial movements, particularly when given clean GL-level data.

Used carefully, they can:

• Identify material variances

• Summarise drivers across large transaction sets

• Draft clear, finance-literate commentary

Where this works

• First-pass variance commentary

• Large GLs with repetitive patterns

• Translating numbers into plain English

Where it breaks

• Manual copy-paste every month

• No inherent audit trail or repeatability

• No linkage to Excel formulas

• Risk of hallucination if inputs are poorly structured

On their own, LLMs can be helpful, but they are not a process.

Note: For a practical example of how to structure an LLM prompt for variance commentary, see the "Example Prompt" section at the end of this post.

2. Variance Analysis Built Into ERP Systems

Some ERP systems now embed AI-driven variance insights directly into their reporting layers.

These tools are effective at flagging movements. They are less effective at explaining them in a way an accountant would confidently sign off on.

And they still do not replace Excel as the primary environment where variance reviews actually happen.

3. Close Management and Overlay Tools

Close management tools sit between the ERP and Excel, attempting to automate parts of variance analysis and commentary.

They typically:

• Pull structured data from the ERP

• Apply rules or AI to detect variances

• Generate draft explanations

This can work well in highly standardised environments.

Limitations

• Setup and configuration overhead

• Reduced flexibility outside predefined models

• Limited visibility into Excel-based logic

They help, but they do not fully solve the problem of Excel being the system of record.

4. Python Scripts and Custom Automation

Some teams build their own automation using Python or SQL.

This approach can be extremely effective:

• Fully deterministic logic

• Reusable month over month

• Audit-friendly outputs

However, it requires engineering effort and ongoing maintenance. Most finance teams do not want to become software teams just to explain why marketing spend moved.

A Practical Middle Ground

In practice, the most effective approach combines:

• Excel as the calculation and review layer

• Structured GL data as the source of truth

• Automation for extraction, grouping, and reconciliation

• AI for narrative and summarisation

Crucially, any automation must:

• Preserve formulas and references

• Write *around* the model, not over it

• Be repeatable month over month

• Leave a clear audit trail

Without those properties, automation creates fragility, not efficiency.

Closing Thought

Variance analysis does not fail because accountants lack judgement. It fails because too much time is spent doing mechanical work that obscures judgement.

The goal of automation is not to replace Excel, nor to replace professional judgement. It is to remove repetition, preserve trust in the numbers, and give accountants more time to focus on more value-adding work.

Excel is not the problem. Poor automation is.

Example Prompt: Automating Variance Commentary with ChatGPT

Below is an example of how an accountant could use an LLM to generate structured, audit-friendly variance commentary from GL data.

Instructions

• Identify material variances
• Focus on accounts with meaningful MoM movements (absolute and/or %).
• Ignore immaterial noise.

• Link variances to GL transactions
• For each material P&L line, identify the key invoices or journals driving:
• Current Month balance
• Prior Month balance
• Call out specific invoice numbers and descriptions where relevant.

• Explain variances in accountant-style language
• Suitable for month-end close, management review, and audit support
• Distinguish between:
• Timing differences
• Genuine volume or price changes
• One-off or non-recurring items

• Use the following structure

Account: [AccountName/GLCode]

Current Month: [TotalAmount]

Explained by:

-Invoice [X]: [Amount]– [Description]

Prior Month: [TotalAmount]

Explained by:

-Journal [Y]: [Amount]– [Description]

Variance explanation:

[Conciseexplanationofwhatchangedandwhy]

• Flag risks or follow-ups
• Unusual items, reversals, or large one-offs
• Suggested follow-up actions where appropriate

• Tone
• Concise, neutral, professional
• Avoid generic filler
• Do not invent data