Budget Builder + Budget vs Actual

Overview

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

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

CODEBLOCK0

Cache Locations (auto-created)

CODEBLOCK1

Mode A: Budget Builder (--build)

What It Does

1. 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: INLINECODE0
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)

Usage

CODEBLOCK2

Assumptions JSON Format

CODEBLOCK3

Methods:

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

Overrides CSV Format (--overrides)

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

CODEBLOCK4

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

Usage

CODEBLOCK5

Budget CSV Format (for --budget-file)

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

CODEBLOCK6

Material Variance Thresholds

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

Seasonal Pattern Detection

• - Seasonal strength = maxmonthlyindex / minmonthlyindex
• Accounts with strength ≥ 1.30 are marked INLINECODE5
• Monthly index = monthamount / annualaverage_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

CODEBLOCK7

• - 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 INLINECODE6

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

CODEBLOCK8

Error Handling

Dependencies

CODEBLOCK9

Output File Naming

CODEBLOCK10

Example: Full FY2026 Cycle

CODEBLOCK11