RetrieverRetriever
ProductTemplatesSecurityPricingStart HereGuides
Try for free
Guides
/

Budget vs Actual Template: Variance Reporting in Google Sheets

Budget vs Actual Template: Variance Reporting in Google Sheets

The problem

The budget side of a variance report barely changes. The actuals side changes every day. That mismatch is why budget vs actual reporting turns into a monthly rebuild: you export fresh actuals, line them up against a budget that lives in a different file, fix the accounts that do not match, and recalculate the variance columns by hand.

Why QuickBooks can't do it

The QuickBooks Budget vs. Actuals report ties both sides to one date range, so you cannot show year-to-date actuals against a full-year budget in the same view, which is what most clients ask for. It also gives you dollar and percent variance in a fixed layout with no room for a commentary column, and no way to group accounts the way your management reporting does.

Do it in Google Sheets with Retriever

Splitting the two sides is what makes the template work. Pull actuals live, keep the budget where you maintain it, and let the variance columns do the rest.

  1. Pull actuals on their own tab

    Bring the QuickBooks Profit & Loss in by month with Retriever. This is the side that needs to refresh, so it gets its own tab and its own schedule.

  2. Bring the budget in alongside it

    Pull the QuickBooks budget, or keep the budget on a tab you maintain by hand if it is planned outside QuickBooks. Either way it sits next to the actuals rather than in a separate file.

  3. Set the two periods independently

    Year-to-date actuals against a full-year budget, or actuals through last closed month against the budget for the same months. Because the periods are parameters on each pull, they do not have to match.

  4. Add variance in dollars, percent, and direction

    Calculate all three, and mark whether a variance is favorable or unfavorable, which depends on whether the account is revenue or expense. QuickBooks does not make that distinction for you.

  5. Leave room for commentary

    A notes column next to each material variance is usually the part the client reads first. It survives every refresh because it lives on the template tab, not the data tab.

See also

Budget vs. Actuals in QuickBooks OnlineBuild a clean Budget vs. Actuals report in Google SheetsFix Budget vs. Actuals showing the wrong datesHow to create a budget in QuickBooks OnlineGoogle Sheets accounting templates

Variance reporting without the monthly rebuild

Retriever pulls QuickBooks actuals and budgets into Google Sheets and refreshes them on a schedule. Set the periods once and the variance columns keep themselves current.

See how Retriever works

Frequently asked questions

Does QuickBooks have a budget vs actual template?

QuickBooks has a Budget vs. Actuals report, not a template. The layout, the columns, and the period logic are fixed, so custom groupings, commentary, and independent budget and actual periods have to be built in a spreadsheet.

Can I compare year-to-date actuals to a full-year budget?

Not in the native QuickBooks report, which applies one date range to both sides. Pulling actuals and budget separately into Google Sheets lets you set a different period for each.

How do I calculate variance in a budget vs actual report?

Dollar variance is actual minus budget, and percent variance is that difference over budget. Whether a variance is favorable depends on the account type, so revenue and expense lines need opposite signs to read correctly.

Why do my budget and actual rows not line up?

Usually because the budget was built against a different account list than the one currently in QuickBooks. Matching rows with a lookup on the account name instead of by position keeps the template working when accounts are added.

Questions about this? Reach out at aubrey@retrieverhq.com.