Finance: Automated Variance Reports

Automating Financial Variance Analysis

Variance analysis—comparing actual figures against budget or prior periods—is one of the most time-consuming tasks in finance. A reasoning model set to an appropriate effort level, paired with code execution (Data Analysis in ChatGPT or the Code Interpreter tool in the API), drastically reduces this workload. This lesson shows you exactly how to do it, from selecting the right settings to delivering the final report.

What a Variance Report Must Answer

A variance report compares two sets of figures—actual vs. budget, month vs. prior month, or year vs. prior year—and analyzes significant deviations. It must always answer three questions in this order: what, which line items show significant variance; why, what are the probable causes; and now what, what actions do you recommend. This is a recurring, labor-intensive task. When scoped correctly, working with ChatGPT significantly cuts the time required, provided you reserve time for verification.

The Right Settings for This Use Case

A medium effort level (medium in the API, Medium in Chat) works well for the variance table itself. However, root cause analysis and decompositions of volume, price, and mix—where you isolate effects that partially cancel each other out—require a high effort level. In the API, GPT-6.1 Terra suffices for a standard income statement; switch to GPT-6.1 Sol for fine-grained decompositions. Most importantly, route all calculations through code execution. This ensures numerical accuracy and generates charts, whereas a model working with raw numbers alone remains prone to error.

The Typical Workflow

Start by preparing your data. Upload the Excel or CSV file containing the month’s actual figures, the budget or comparison period file, and, if you need trend analysis, the prior month’s data. Then, launch the analysis with a single comprehensive prompt rather than breaking it into multiple steps.

“Here is the actual P&L for March 2026 and the corresponding budget. Produce a complete variance report:

1. Calculate the absolute and percentage variance for each line item.
2. Identify significant variances (>5% or >€10,000).
3. Rank them by impact on net income.
4. For each significant variance, propose 2–3 explanatory hypotheses.
5. Format everything in a table with color coding (green = favorable, red = unfavorable).
6. End with a 5-line executive summary.”

The significance threshold in step 2 is not a minor detail. Without it, the model comments on every variance, including negligible ones, making your report unreadable. Once the hypotheses are established, drill down into the largest line item by providing the necessary detailed data.

“The variance in personnel expenses (+12%) is the most significant. Can you decompose this variance into volume effect (headcount), price effect (average cost per employee), and mix effect (distribution of permanent vs. temporary vs. contract staff)? Here is the detailed data: [data]“

The Waterfall Chart

With code execution, the model generates a waterfall chart. This image visually shows each line item’s contribution to the total variance. It is the most effective tool for management presentations because it makes the path from budget to actuals readable at a glance.

“Create a waterfall chart showing how we move from the budgeted result (€250,000) to the actual result (€218,000), broken down by line item. Use green for favorable variances and red for unfavorable ones.”

Three Advanced Analyses

Three-level decomposition distinguishes between quantities, rates, and product mix: “Decompose sales variance into: (1) volume effect, (2) price effect, (3) product mix effect. Here is the data by product and unit: [data].” Only this approach reveals whether revenue growth stems from commercial gains or a shift in product tier.

Trend analysis looks at dynamics rather than a single point: “Here are the variances for the last six months. Identify line items showing a deteriorating trend (negative variance worsening month over month) and those improving. Rank by urgency.” A stable item at -3% is less concerning than one sliding from -1% to -4% over three months.

Finally, rolling variance smooths out seasonal effects: “Calculate the 3-month rolling variance to smooth seasonal effects. Compare actual Q1 to budgeted Q1 and Q1 of the previous year. Identify one-time effects versus structural trends.” This phrasing prevents you from mistaking a billing timing difference for a fundamental drift.

The Complete Report in a Meta-Prompt

Once you have refined the process, you can request the entire output in one go using high reasoning effort.

“You are a senior financial controller. Using the attached files (actual P&L and budget), produce a complete variance report including:

Section 1 — Executive Summary: 5 lines summarizing the month’s performance.

Section 2 — Variance Table: All P&L line items with absolute variance, percentage variance, and a visual indicator.

Section 3 — Top 5 Variances: The five largest variances by absolute value, with explanatory hypotheses.

Section 4 — Waterfall Chart: From budgeted result to actual result.

Section 5 — Alerts: Line items showing an unfavorable trend over three months.

Section 6 — Recommendations: Priority actions for the next month.”

Your Turn

Create a variance report. If you work in finance, test this with your real figures from last month. Otherwise, have the model generate a dataset: “Generate a realistic dataset: a monthly P&L with 15 lines (revenue, COGS, fixed costs, variable costs, etc.) for both actuals and budget. Include varied variances (some favorable, some unfavorable, some significant). Then analyze it as if it were a real case.”

Two checks determine success. Upstream, ensure actuals and budget have the exact same structure; a one-line offset produces a false report. Downstream, never treat explanatory hypotheses as conclusions—they are leads that only your domain knowledge can validate or discard. Also, remember that budget variance tells only part of the story; compare against the prior month and the prior year as well.

Official sources: Data analysis with ChatGPT (OpenAI Help Center: Python calculations, charts, files) · Reasoning models (effort levels) · Models (GPT-6.1 Terra and GPT-6.1 Sol)

Key Takeaways

• Reasoning and code execution produce a complete variance report, including calculations.
• Use medium effort for the table; use high effort for root causes and volume, price, and mix decompositions.
• Structure your prompt: variance table, top variances, waterfall chart, recommendations.
• Waterfall charts are highly effective for communicating with management.
• Always validate explanatory hypotheses with your domain expertise.