Skip to content
Reporting and Analysis

Build a Budget vs Actual Report in Google Sheets with Gemini

View source

Short answer

In a native Google Sheet, ask Gemini to pivot your actuals by account and month, add budget, variance and variance % columns, and highlight accounts over budget. It needs an eligible Workspace or Google AI plan, and you check each preview card before applying it.

What the source describes

Google's help page for Gemini in Google Sheets says Gemini can create tables and formulas, generate data analysis and insights, build charts, and perform actions such as conditional formatting, pivot tables, filters and number formats, each shown on a preview card you apply or undo. It works best with native Google Sheets files, so Google says to save an Excel file as Google Sheets first. Inserted charts go on a new tab with their data and do not change when the original data changes, and the conversation history is lost when you reload or reopen the file. The feature requires an eligible Google Workspace or Google AI plan.

Result or outcome

According to the source, Gemini proposes each action on an Action preview card that you apply, adjust under Settings where available, or reverse with Undo, and you can focus its attention on specific tabs from the dropdown in the prompt box.

How to run it

  1. If the budget or the ledger export is an Excel file, save it as Google Sheets first, then put the budget and the actuals on separate tabs with clear headers.
  2. Click Ask Gemini at the top right, and use the dropdown in the prompt box so only the Budget and Actuals tabs are selected.
  3. Paste the prompt on this page and review each Action preview card before you click Apply, using Undo if a result is wrong.
  4. Insert anything you want to keep into the sheet, since the conversation history is lost when you reload or reopen the file.
  5. Each month, add the new actuals and rebuild the report, because inserted charts do not update when the original data changes.

Try it yourself

Copyable prompt

This spreadsheet has two tabs. [Budget] has one row per GL account with columns Account, Account type (Income or Expense), and one column per month from [Jan] to [Dec]. [Actuals] is a general ledger export with columns Date, Account, Memo, Amount. Using only these two tabs: 1) Create a pivot table of Actuals with Account in rows and month in columns, summing Amount. 2) On a new tab called Budget vs Actual, list each account with Budget, Actual and Variance for [month], plus year-to-date columns. 3) Calculate variance as Actual minus Budget, and variance percent as variance divided by Budget, showing a blank when Budget is zero. 4) Highlight expense variances over [10]% or [$500] unfavorable. 5) List any account in Actuals that is missing from Budget. Explain each formula you add, and tell me which accounts you could not match.

Replace the [bracketed] placeholders. Remove client names, account numbers, and personal data before pasting into any AI tool.

The prompt also works in ChatGPT.

What to review

Check that total actuals in the report equal the ledger export total for the month, that variance signs read the same way for income and expenses, and that accounts missing from the budget were not dropped. Look at the source transactions behind every highlighted variance before you explain it.

Related free download: 50 AI Prompts for Bookkeepers (Prompt pack (PDF))

Source: Google Docs Editors Help (Collaborate with Gemini in Google Sheets)

See also

Not professional advice. This is general information, not accounting, tax, legal or financial advice. Check AI output yourself and consult a licensed professional for your situation. See our Terms of Use.

This entry summarizes a public source. Features, plans, and availability change, so confirm details with the vendor. Tool names are trademarks of their respective owners.

Related workflows

  • Reporting and Analysis

    Write and Fix Spreadsheet Formulas with Gemini in Google Sheets

    Google's Docs Editors Help explains that Gemini in Google Sheets can create tables and formulas, generate data analysis and insights, build charts, and perform actions such as creating pivot tables, applying conditional formatting, sorting, filtering and find and replace. Users open the Ask Gemini side panel, describe the formula they need with references to their sheet, and insert or retry the suggestion. When a formula shows an error, users can hover over the cell and click Fix so Gemini reviews the formula and analyzes the issue.

    Google Sheets, Google Gemini

    Google Docs Editors Help
  • Reporting and Analysis

    Draft a Client Financial Health Review from an Income Statement

    CPA Practice Advisor walks through pasting an anonymized landscaping company's income statement into ChatGPT and Gemini Advanced and asking how financially healthy the business is and what to recommend. Both tools identified the net loss, flagged salaries as a large share of expenses, and suggested cost and revenue actions. The author stresses anonymizing data, getting client permission, having an expert check every answer, and reviewing terms of service.

    Works in ChatGPT, Claude, Grok, Gemini

    CPA Practice Advisor
  • Reporting and Analysis

    Benchmark a Client P&L Against Industry Norms by NAICS

    This TechNews.Tax newsletter post describes how a tax firm reviews Profit and Loss statements from clients who do their own books, using an AI prompt that compares the P&L to typical expense categories and percentages for the client's NAICS code. The prompt asks for a NAICS summary, a category and percentage comparison, a judgment on whether expenses are in line (within a 10% variance), and a risk explanation for outliers. The author uses the OpenAI API but notes the same process works in ChatGPT, Gemini, Copilot or similar tools.

    Works in ChatGPT, Claude, Grok, Gemini

    TechNews.Tax (newsletter)
  • Reporting and Analysis

    Draft Management Report Commentary with Fathom Commentary Writer

    Fathom's help centre explains how the Commentary Writer, an AI tool inside Fathom Pro reports, drafts data-based commentary from up to six selected report components. Users add persistent Business Context (goals, strategy, market conditions) and per-period Report Context (one-off events, audience), choose a Simple or Technical style and length, then prompt and refine before adding the text to the report. Figures in the generated commentary can be hovered over to check where they came from.

    Fathom, Fathom Commentary Writer

    Fathom Help Centre