Build a QuickBooks P&L Budget vs. Actual Variance Bridge from CSV

How to export Budget vs. Actuals or P&L data from QuickBooks Online

1. In QuickBooks Online, go to Reports and search for Budget vs. Actuals (or Profit and Loss Comparison for period-over-period variance).
2. Set your Report period and Accounting method (Accrual or Cash), then click Run report.
3. Click the Export icon next to Print and export to CSV, or select Export to Excel. Drop a .csv above for a first chart, or import the .csv or .xlsx file into the xmetric app to build the bridge.

QuickBooks CSV columns used

• Account (or Distribution Account on detail exports): the chart of accounts category (4000 SaaS Revenue, 6100 Cloud Hosting, 6200 Payroll).
• Actual: realized revenue or expense for the period.
• Budget: budgeted revenue or expense for the period.
• Over Budget and % of Budget: variance columns exported by QuickBooks.

Tableau Gantt Waterfall Formulas and SQL for Variance Bridges

In QuickBooks, positive Over Budget on an Income account is favorable, whereas positive Over Budget on an Expense or COGS account is unfavorable (it reduces Net Operating Income). A clean Net Income bridge must flip the sign of expense variances so every bar shows its true impact on bottom-line profit.

1. Net Income Impact Variance ([Favorable / Unfavorable])

Test this formula in the formula playground before building the chart:

// [Net Income Variance Impact]
IF [Account Type] = "Income" OR [Account Type] = "Other Income" THEN
  [Actual] - [Budget]
ELSE
  // For COGS and Expenses, spending less than budget increases Net Income
  [Budget] - [Actual]
END

2. Waterfall Running Position (Table Calculation on Gantt Bar Mark)

To draw a Tableau-grammar waterfall bridge in the xmetric app:

1. Change the Marks card type to Gantt Bar.
2. Place [Account] on Columns (between Budget Net Income and Actual Net Income).
3. Place RUNNING_SUM(SUM([Net Income Variance Impact])) - SUM([Net Income Variance Impact]) on Rows.
4. Place SUM([Net Income Variance Impact]) on Size and SIGN(SUM([Net Income Variance Impact])) on Color.

Equivalent SQL

WITH clean_gl AS (
  SELECT
    TRIM("Account") AS account_name,
    "Account Type" AS account_type,
    CAST(REPLACE(REPLACE("Budget", ',', ''), '$', '') AS DOUBLE) AS budget_amt,
    CAST(REPLACE(REPLACE("Actual", ',', ''), '$', '') AS DOUBLE) AS actual_amt
  FROM quickbooks_budget_vs_actuals
  WHERE "Account" IS NOT NULL
    AND TRIM("Account") NOT LIKE 'Total %'
    AND TRIM("Account") NOT IN ('Gross Profit', 'Net Operating Income', 'Net Income')
),
variances AS (
  SELECT
    account_name,
    account_type,
    budget_amt,
    actual_amt,
    CASE
      WHEN account_type IN ('Income', 'Other Income') THEN actual_amt - budget_amt
      ELSE budget_amt - actual_amt
    END AS net_income_impact,
    ROUND(100.0 * (actual_amt - budget_amt) / NULLIF(ABS(budget_amt), 0), 1) AS variance_pct
  FROM clean_gl
)
SELECT
  account_name,
  account_type,
  ROUND(budget_amt, 2) AS budget,
  ROUND(actual_amt, 2) AS actual,
  ROUND(net_income_impact, 2) AS profit_bridge_delta,
  variance_pct,
  CASE WHEN net_income_impact >= 0 THEN 'Favorable' ELSE 'Unfavorable' END AS variance_flag
FROM variances
ORDER BY ABS(net_income_impact) DESC;

Cleaning QuickBooks CSV formatting quirks

• Metadata header rows at the top of the file: QuickBooks report exports place the company name, report title, and date range in the top rows before the column header row. Delete those rows (for example in Excel) so the column names are the first row before you import the file.
• Double counting Total subtotal rows: QuickBooks inserts summary rows like Total Income, Total Cost of Goods Sold, and Total Expenses inline with leaf accounts. The query above filters out any row starting with Total ; in the app, add a filter that excludes those rows so your variance bridge never double counts subtotals.

Explain variances faster with AI agents

Every shelf placement, filter, and formula in xmetric is a command. An MCP server for Claude and ChatGPT is in development, so your assistant will be able to inspect a QuickBooks P&L export and draft monthly board variance commentary from computed numbers instead of its own arithmetic. Live connections to Postgres and BigQuery are also coming.