Budget vs Actual Report in Google Sheets: Category Variance
Summary
A budget vs actual report compares what you planned to spend or earn with what happened, by category and month, and shows the variance in dollars and percent. If your budget and transactions already live in Google Sheets, Better Analyst can match the two tabs, calculate variances and year-to-date totals, and explain the biggest gaps.
On this page
Who this report is for
Small-business owners, finance leads and department heads who keep the budget in one Google Sheet tab and actual spending in another and want a monthly variance review without rebuilding SUMIFS each month.
What the report includes
| Metric | Source field | Why it matters |
|---|---|---|
| Budget | Budget tab: Month, Category, Budget columns (your headers) | The plan for each category and month. |
| Actual | Actuals tab: Date, Category, Amount columns (your headers) | Transactions summed by category and month to compare with the plan. |
| Variance ($) | Calculated: Actual − Budget | The size of each gap in currency, which decides what is worth discussing. |
| Variance (%) | Calculated: (Actual − Budget) ÷ Budget | Puts small and large categories on the same scale. |
| Year-to-date variance | Calculated: cumulative Actual − cumulative Budget | Separates one-off monthly timing differences from a real overspend. |
How to build it in Better Analyst from Google Sheets
- Share the Google Sheet with sheets@betteranalyst.com. Viewer access is enough; the connector never edits your sheet.
- In Better Analyst, open Connectors, choose Google Sheets, paste the sheet URL and click Connect. Better Analyst checks the sharing, reads the tabs and syncs the data.
- Make sure both tabs use the same category names and that the budget has one row per category per month; Better Analyst can also map near-matching names if you ask.
- Ask for monthly and year-to-date variance by category, flagging anything more than 10% and $1,000 over budget.
Questions to ask
- “Compare actual spending to budget by category for September and year to date.”
- “Which categories are more than 10% over budget year to date?”
- “Explain the three largest variances this month using the transaction descriptions.”
- “Forecast full-year spend by category at the current run rate.”
Sample output
Sample data for illustration, not real account data.
| Category | Budget | Actual | Variance $ | Variance % | YTD variance % |
|---|---|---|---|---|---|
| Payroll | $84,000 | $86,100 | $2,100 | 2.5% | 1.1% |
| Software | $6,500 | $8,320 | $1,820 | 28.0% | 19.4% |
| Advertising | $18,000 | $15,400 | -$2,600 | -14.4% | -6.2% |
| Travel | $3,000 | $4,950 | $1,950 | 65.0% | 12.8% |
Charts to use
- Variance by category (Diverging bar chart): Over- and under-budget categories on either side of zero, sorted by size.
- Cumulative budget vs actual (Line chart): Year-to-date budget and actual lines, so persistent overspend stands out from timing.
Keep it up to date
Schedule the variance review for the 5th of each month, after the books close, and deliver it by email. If you'd rather keep everything in Sheets, a Data Flow can write the variance table back to a new tab in the same spreadsheet on a schedule.