Budget Builder + Budget vs Actual#

Overview#

This pipeline handles the full FP&A budget cycle for QBO-connected clients:

1. Build an annual budget from QBO history with seasonal patterns + growth assumptions
2. Compare YTD actuals vs. budget with material flags, commentary, and rolling forecast

Both modes output Excel workbooks in a standard suite format (Calibri, dark header, F/U coloring, CDC log).

---

Script Location#

text

scripts/pipelines/budget-builder.py

Cache Locations (auto-created)#

text

.cache/budget-builder/{slug}_budget_{year}.json   # Budget file for Mode B
.cache/budget-builder/{slug}_cdc.json             # CDC accuracy tracker

---

Mode A: Budget Builder (--build)#

What It Does#

1. Pulls 12 months of monthly P&L from QBO (trailing from today's last completed month)
2. Discovers all accounts and their section types (income / cogs / expense)
3. Loads growth assumptions (global + per-account overrides from JSON)
4. Auto-detects seasonal patterns for each account (strength ≥ 1.30 = seasonal)
5. Generates monthly budget: base_monthly_avg × seasonal_index × growth_factor
6. Applies manual dollar overrides from CSV (full precedence over calculated amounts)
7. Saves budget to JSON cache + produces Excel workbook

Excel Output (4 tabs)#

TabContentsBudget SummaryAnnual KPI totals + monthly Revenue/EBITDA gridMonthly DetailAll line items × 12 months + Annual totalAssumptionsGrowth method, rate, base avg, annual budget per accountSeasonal PatternsMonthly indices, strength, peak/trough months per account

Usage#

bash

# Basic — use default growth rates (revenue +5%, COGS +3%, expenses +3%)
python3 budget-builder.py --slug <client-slug> --build --year 2026

# Custom growth assumptions
python3 budget-builder.py --slug <client-slug> --build --year 2026 --assumptions growth.json

# With manual overrides for specific accounts
python3 budget-builder.py --slug <client-slug> --build --year 2026 --overrides overrides.csv

# Custom output directory
python3 budget-builder.py --slug <client-slug> --build --year 2026 --out ~/Desktop/reports

Assumptions JSON Format#

json

{
  "global": {
    "income":  0.08,
    "cogs":    0.04,
    "expense": 0.03
  },
  "overrides": {
    "Rent": {"method": "flat"},
    "Software Subscriptions": {"method": "pct_growth", "rate": 0.12},
    "Total Income": {"method": "pct_growth", "rate": 0.08}
  }
}

Methods:

• pct_growth (default) — apply percentage growth rate to trailing average
• flat — no growth from trailing average (rate = 0)

Overrides CSV Format (--overrides)#

For hard-coded monthly amounts that override the calculated budget:

csv

account,section_type,2026-01,2026-02,2026-03,...,2026-12
Rent,expense,3500,3500,3500,...,3500
Owner's Draw,expense,8000,8000,8000,...,8000

Columns: account + section_type + one column per YYYY-MM (or Jan/Feb/Mar shorthand).

---

Mode B: Budget vs Actual (--compare, default)#

What It Does#

1. Loads saved budget (JSON cache or supplied file)
2. Pulls YTD actuals from QBO P&L (Jan 1 → end of last completed month)
3. Also pulls each month individually for per-month variance detail
4. Computes: $ delta, % delta, Favorable/Unfavorable per line item
5. Flags material variances: revenue ≥5% or ≥$2,500 | expenses ≥10% or ≥$2,500
6. Generates WHAT/WHY/ACTION/OUTLOOK commentary stubs for flagged variances
7. Builds rolling forecast: actuals locked for closed months + budget × blend for open months
8. Updates CDC accuracy log and surfaces systematic bias patterns

Excel Output (6 tabs)#

TabContentsVariance SummaryHeadline KPIs + all material variancesMonthly DetailAll line items × closed months (Budget | Actual | Var per month)Material FlagsSorted by $ variance + monthly trend + action promptsRolling ForecastFull-year forecast by account (gray=closed, blue=forecasted)Management CommentaryWHAT/WHY/ACTION/OUTLOOK per material variance (draft)CDC LogBudget accuracy tracker + systematic bias analysis

Usage#

bash

# Default: compare through last completed month, use cached budget
python3 budget-builder.py --slug <client-slug> --compare

# Specify through month
python3 budget-builder.py --slug <client-slug> --compare --through 2026-02

# Use explicit budget file
python3 budget-builder.py --slug <client-slug> --compare --budget-file ~/Desktop/Budget_client_2026.json

# Use manually-prepared budget CSV
python3 budget-builder.py --slug <client-slug> --compare --budget-file my_budget.csv

# Custom output
python3 budget-builder.py --slug <client-slug> --compare --through 2026-03 --out ~/Desktop/reports

Budget CSV Format (for --budget-file)#

If the budget was prepared outside this tool (e.g., in Excel), export as:

csv

account,section_type,2026-01,2026-02,...,2026-12
Total Income,income,50000,52000,...,85000
Cost of Goods Sold,cogs,15000,15600,...,25500
Rent,expense,3500,3500,...,3500

---

Material Variance Thresholds#

Category$ Threshold% ThresholdRevenue≥ $2,500≥ 5%Expenses≥ $2,500≥ 10%

Both conditions use OR logic — either trigger alone is enough to flag.

---

Seasonal Pattern Detection#

• Seasonal strength = max_monthly_index / min_monthly_index
• Accounts with strength ≥ 1.30 are marked IS SEASONAL = YES
• Monthly index = month_amount / annual_average_monthly
• Index > 1.0 = above average month; index < 1.0 = below average
• Peak/trough months are identified and highlighted in the Seasonal Patterns tab

Example: Q4 revenue spike → indices for Oct/Nov/Dec will be > 1.0, Q1-Q2 will be < 1.0. Budget will correctly allocate more to Q4.

---

Rolling Forecast Logic#

text

Closed months:  Actual values locked in (not adjusted)
Open months:    Budget × blend_factor

blend_factor = (0.70 × ytd_factor) + (0.30 × 1.0)
ytd_factor   = actual_YTD / budget_YTD

• If actuals are running 15% above budget → open months forecasted at +10.5% above budget
• If actuals are running 10% below budget → open months forecasted at -7% below budget
• Blend weight (0.70) is configurable in script FORECAST_TREND_WEIGHT

---

CDC — Budget Accuracy Tracker#

Tracks per-account variance data across all BvA runs. After 2+ periods, identifies:

• Consistently over-budget accounts (actual > budget 75%+ of the time)
• Consistently under-budget accounts (actual < budget 75%+ of the time)
• Average variance magnitude per account

Use case: If Marketing always runs 20% over budget, the CDC will surface this after 2-3 months. Use for next year's budget calibration.

CDC is appended, not overwritten — it accumulates across fiscal years.

---

Workflow Integration#

text

1. [Month 1 of new FY] Run --build to generate annual budget
   → Saves: .cache/budget-builder/{slug}_budget_{year}.json
   → Share Excel with the reviewer for approval before using in BvA

2. [Each month after close] Run --compare
   → Pulls actuals from QBO automatically
   → Flags variances, generates commentary stubs
   → Updates CDC accuracy log

3. [Mid-year review] Re-run --build with updated assumptions + YTD overrides
   → Export updated budget JSON, pass to --budget-file in --compare

---

Error Handling#

ScenarioBehaviorQBO connection errorRaises RuntimeError with stdout/stderr for diagnosisMissing month in historyFills with $0; warns in consoleBudget file not foundRaises FileNotFoundError with clear message + instructionsDivision by zero (budget = $0 for actuals)Returns ZERO variance; flags if actual > $2,500Invalid CSV column formatSkips unrecognized columns; warns

---

Dependencies#

bash

pip install openpyxl
# Node.js QBO client must be configured with valid auth tokens

---

Output File Naming#

text

Mode A: Budget_{slug}_{year}.xlsx              (e.g., Budget_acme_2026.xlsx)
Mode B: BvA_{slug}_{month_label}.xlsx         (e.g., BvA_acme_Feb_26.xlsx)
Cache:  .cache/budget-builder/{slug}_budget_{year}.json
CDC:    .cache/budget-builder/{slug}_cdc.json

---

Example: Full FY2026 Cycle#

bash

# January 2026: Build the budget
python3 budget-builder.py --slug <client-slug> --build --year 2026 \
  --assumptions clients/<client-slug>/budget-assumptions-2026.json

# February 28 (after Jan close):
python3 budget-builder.py --slug <client-slug> --compare --through 2026-01

# March 31 (after Feb close):
python3 budget-builder.py --slug <client-slug> --compare --through 2026-02

# Q2 reforecast (after March close):
python3 budget-builder.py --slug <client-slug> --compare --through 2026-03
# Review CDC log — which categories need budget adjustment?
# Build revised budget with Q1 actuals as overrides:
python3 budget-builder.py --slug <client-slug> --build --year 2026 \
  --overrides clients/<client-slug>/q1-actuals-overrides.csv