Variance analysis is actual minus budget with a percent of budget. Snapshotting via script freezes numbers for monthly packs.
budgetVsActualVariance fills variance and variancePct; division by zero budgets yields a blank percent.
variancePct is stored as a fraction (0.1 = 10%) so you can format the column as percent in Sheets.
Run after actuals import from GL each period.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| BudgetVsActual | A account | Account name/code |
| BudgetVsActual | B budget | Plan |
| BudgetVsActual | C actual | Actual |
| BudgetVsActual | D variance | actual − budget |
| BudgetVsActual | E variancePct | variance / budget |
What this script does
Row-wise variance and percent with zero-budget guard.
Prerequisites
Budget and actual columns populated for the period.
- Same sign convention
- Format E as percent
- Accounts unique
Walkthrough
Budget 100 actual 110 → variance 10, pct 0.1.
Edge cases
Favorable/unfavorable depends on account type — expense overruns are unfavorable.
- Blank pct if budget 0
- Cent rounding on variance
How to test
Zero budget with actual >0 → variance nonzero, pct blank.
Hardening for production
Separate volume vs price variance; YTD columns.
Variations
Invert formula for revenue accounts if you track budget − actual.
Full code: budgetVsActualVariance()
Load budget and actuals, run budgetVsActualVariance(), format column E as percent.
/**
* Compute budget vs actual variance and variance %.
*/
const BUDGET = "BudgetVsActual";
// A account, B budget, C actual, D variance, E variancePct
function budgetVsActualVariance() {
const sheet = SpreadsheetApp.getActive().getSheetByName(BUDGET);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const budget = Number(values[i][1]) || 0;
const actual = Number(values[i][2]) || 0;
const variance = actual - budget;
values[i][3] = Math.round(variance * 100) / 100;
values[i][4] = budget === 0 ? "" : Math.round((variance / budget) * 10000) / 10000;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 13: Variance definition used here.
- Line 15: Avoids divide-by-zero percent.
- Line 5: Stored as fraction for percent formatting.
- Line 14: Currency cents.
- Line 17: Writes D and E.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: budget variance
- 1Period actuals loaded
- 2Budget column correct for period
- 3Sign convention agreed
- 4Column E formatted as %
- 5Zero-budget accounts reviewed
- 6Archive a snapshot copy monthly
Frequently asked questions
Sheets percent format multiplies by 100 for display.
Some teams use budget−actual for expenses — swap if required.
Use Math.abs for magnitude reports; keep signed for statements.
Convert actuals to budget currency first.
Fine live; script helps when exporting frozen packs.
Sum child accounts into parents in a separate pass.
After compute, email rows where abs(pct) > 0.1.