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

scripts/pipelines/budget-builder.py

Cache Locations (auto-created)

.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)

Tab	Contents
Budget Summary	Annual KPI totals + monthly Revenue/EBITDA grid
Monthly Detail	All line items × 12 months + Annual total
Assumptions	Growth method, rate, base avg, annual budget per account
Seasonal Patterns	Monthly indices, strength, peak/trough months per account

Usage

# 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

{
  "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:

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)

Tab	Contents
Variance Summary	Headline KPIs + all material variances
Monthly Detail	All line items × closed months (Budget | Actual | Var per month)
Material Flags	Sorted by $ variance + monthly trend + action prompts
Rolling Forecast	Full-year forecast by account (gray=closed, blue=forecasted)
Management Commentary	WHAT/WHY/ACTION/OUTLOOK per material variance (draft)
CDC Log	Budget accuracy tracker + systematic bias analysis

Usage

# 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:

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	% Threshold
Revenue	≥ $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

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

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

Scenario	Behavior
QBO connection error	Raises RuntimeError with stdout/stderr for diagnosis
Missing month in history	Fills with $0; warns in console
Budget file not found	Raises FileNotFoundError with clear message + instructions
Division by zero (budget = $0 for actuals)	Returns ZERO variance; flags if actual > $2,500
Invalid CSV column format	Skips unrecognized columns; warns

---

Dependencies

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

---

Output File Naming

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

# 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