Budget vs actual variance analysis with Gemini in Google Sheets

Gemini in Google Sheets turns a plan-versus-actual table into a ranked list of variances with a hypothesis and a question for each line item, and it does this in the side panel without copying data into a chat. The analysis takes about 30 minutes, most of which is preparation of the table and checking the two largest variances by hand. What the model gives you is a list of questions; the causes come from the people who own the budget lines.

Prepare the sheet so the panel reads it correctly

The panel reads a range with headers and one data type per column. Merged cells, empty headers and dates stored as text produce wrong answers, and in a budget file all three are common. Build a flat table: line item, division, month, plan, actual, with the header in the first row and nothing merged.

Then add two formula columns yourself: variance in amount and variance in percent. This matters more than it looks. When the numbers are already calculated, the model sorts and interprets; when it has to calculate, it sometimes rounds or flips a sign. Your formulas also give you the control total the final comment must tie to.

A worked example

Suppose the marketing division shows actual spend far above plan in March. Without context the model calls it an overspend and suggests "uncontrolled advertising costs". If your prompt says the company runs a spring campaign every March, the same line becomes a timing difference, and the question for the owner changes to "was the campaign budget moved from April?". Meanwhile a small line such as software subscriptions running a few percent over plan three months in a row is the real signal, and the prompt asks for exactly those repeating lines.

The 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.

Fill {{period}} with the exact months, {{industry}} with one line about the business and the seasonality field with what actually happens in your year. The threshold can be a percent, an amount or both; pick the one management uses.

What to do

1. Build a flat table with line item, division, month, plan and actual, headers in row one, no merged cells.
2. Add formula columns for variance in amount and in percent.
3. Select the range, open the Gemini panel and paste the prompt with your period, industry, seasonality and threshold.
4. Compare the top five variances with a manual sort of the variance column.
5. Check the two largest against source data and discard hypotheses that do not hold.
6. Send the questions to the line owners and replace every [CAUSE, confirm] with the answer you get.
7. Ask for the five-sentence comment again with confirmed causes and check that the total variance ties to the table.

Checks and limits

The result is done when the top five match your manual sort, every cause in the comment comes from the owner, and the total ties to the sheet. If you ask Gemini to write a formula along the way, test it on five rows before filling it down. A budget with real division names and payroll lines goes into a personal account only anonymized; inside a corporate Workspace you can work on the real file.

Gemini in Sheets comes with Google AI Pro at 19.99 USD per month or Workspace Business Standard at 14 USD per user with annual billing, where company data stays out of model training. The pricing page lists both. Teams that work in Excel can run the same analysis there; see Gemini in Google Sheets vs Copilot in Excel. The prompt is also kept on the budget variance prompt page.