Budget Table

Overview

The UTILITIES_DB.FINANCE.BUDGET table stores monthly budget projections under three recognition types — GAAP, Cash, and Non-Financial — in a single wide table. Each row represents one month for one recognition type.

A convenience view, UTILITIES_DB.FINANCE.BUDGET_CURRENT, filters to the latest imported version.

Location: UTILITIES_DB.FINANCE.BUDGET Convenience view: UTILITIES_DB.FINANCE.BUDGET_CURRENT

Versioning

Each import carries a VERSION string (e.g. 2026-03-23_por_locked). The IS_CURRENT flag marks the active version. To query the latest budget, use the view or filter on IS_CURRENT = TRUE. To compare across versions, filter on VERSION directly.

Recognition types

The RECOGNITION_TYPE column is one of GAAP, CASH, or NON_FINANCIAL. Each type populates a different set of columns — the others are NULL. Every (version, month) pair therefore has three rows.

GAAP — Revenue is recognized when products are fulfilled and subscriptions are earned, following accrual accounting. This is the income statement view.
CASH — Revenue is recognized when cash is received, costs when cash is paid. This is the cash flow view.
NON_FINANCIAL — Unit volumes and subscriber counts. These metrics are recognition-agnostic (they describe physical activity, not dollars), so they live on their own row to avoid double-counting or implying a GAAP-vs-Cash distinction that doesn't exist.

GAAP: Income Statement waterfall

The GAAP columns walk from revenue down to operating income (EBIT):

  Hardware Revenue
+ Software Revenue
────────────────────
= TOTAL REVENUE

  Hardware Revenue − Hardware CoGS = Hardware GP      → Hardware GM = Hardware GP ÷ Hardware Revenue
  Software Revenue − Software CoGS = Software GP      → Software GM = Software GP ÷ Software Revenue

  Hardware GP + Software GP = TOTAL GP                 → Total GM = Total GP ÷ Total Revenue

  Total GP − OpEx = EBIT

GAAP column reference

Column	Description
`HARDWARE_REVENUE`	Revenue from hardware, recognized on fulfillment
`SOFTWARE_REVENUE`	Subscription revenue, recognized as earned
`TOTAL_REVENUE`	Hardware Revenue + Software Revenue
`HARDWARE_COGS`	Cost of goods sold for hardware
`SOFTWARE_COGS`	Cost of goods sold for software/subscriptions
`HARDWARE_GP`	Hardware Revenue − Hardware CoGS
`HARDWARE_GM`	Hardware GP ÷ Hardware Revenue (decimal, e.g. 0.34 = 34%)
`SOFTWARE_GP`	Software Revenue − Software CoGS
`SOFTWARE_GM`	Software GP ÷ Software Revenue (decimal)
`TOTAL_GP`	Hardware GP + Software GP
`TOTAL_GM`	Total GP ÷ Total Revenue (decimal)
`OPEX`	Total operating expenses (S&M, Personnel, R&D, Professional Services, G&A, Depreciation)
`EBIT`	Earnings Before Interest & Taxes = Total GP − OpEx

Cash: Simplified Cash Flows waterfall

The Cash columns track actual cash movements — when money comes in and goes out:

  Hardware Revenue + Software Revenue = TOTAL REVENUE (CASH)

  Cash costs:
    Inventory Cash Outlay                 (cash paid for hardware inventory)
  + Software CoGS                         (cash paid for software delivery costs)
  + Other CoGS                            (other cost of goods sold)
  + OpEx                                  (operating expenses paid in cash)
  + Taxes, Financing, Other               (taxes, financing costs, and other cash outflows)

  Software Revenue − Software CoGS = Software GP     → Software GM = Software GP ÷ Software Revenue

The bottom-line cash position flows through the cash flow statement:

  STARTING CASH
+ Change in Cash from Operations
+ Change in Cash from Investing
+ Change in Cash from Financing
────────────────────────────────
= NET CHANGE IN CASH

  Starting Cash + Net Change in Cash = CASH BALANCE AT EOM

Note

The cash flow statement lines (Operations, Investing, Financing) categorize cash movements by standard accounting classification. They represent the same underlying cash flows as the revenue/cost lines above, but grouped differently — so they do not sum directly from individual revenue or cost columns.

Cash column reference

Column	Description
`HARDWARE_REVENUE`	Cash received from hardware sales
`SOFTWARE_REVENUE`	Cash received from subscriptions
`TOTAL_REVENUE`	Hardware Revenue + Software Revenue
`INVENTORY_CASH_OUTLAY`	Cash paid to purchase hardware inventory
`SOFTWARE_COGS`	Cash paid for software delivery costs
`SOFTWARE_GP`	Software Revenue − Software CoGS
`SOFTWARE_GM`	Software GP ÷ Software Revenue (decimal)
`OTHER_COGS`	Other cost of goods sold (cash paid)
`OPEX`	Operating expenses paid in cash
`TAXES_FINANCING_OTHER`	Taxes, financing costs, and other cash outflows
`CHANGE_IN_CASH_FROM_OPERATIONS`	Cash flow from operating activities
`CHANGE_IN_CASH_FROM_INVESTING`	Cash flow from investing activities
`CHANGE_IN_CASH_FROM_FINANCING`	Cash flow from financing activities
`NET_CHANGE_IN_CASH`	Sum of Operations + Investing + Financing
`STARTING_CASH`	Cash balance at start of month
`CASH_BALANCE_AT_EOM`	Starting Cash + Net Change in Cash

Non-Financial: Volumes and subscribers

NON_FINANCIAL rows hold the unit-count and subscriber-count metrics. These are neither GAAP nor Cash concepts — they describe physical/contractual activity — so they live on their own recognition type to keep the financial rows cleanly comparable and to avoid double-counting when summing across recognition types.

  Units Sold                              (devices sold — revenue-trigger for Cash)
  Units Fulfilled                         (devices delivered — revenue-trigger for GAAP)

  Paying Subscribers Total                (active paying subscriber base at EOM)
    ├── Paying Subscribers Monthly
    └── Paying Subscribers Annual

Non-Financial column reference

Column	Description
`UNITS_SOLD`	Devices sold in the month (drives Cash hardware revenue)
`UNITS_FULFILLED`	Devices delivered to customers in the month (drives GAAP hardware revenue)
`PAYING_SUBSCRIBERS_MONTHLY`	Monthly plan subscribers at end of month
`PAYING_SUBSCRIBERS_ANNUAL`	Annual plan subscribers at end of month
`PAYING_SUBSCRIBERS_TOTAL`	Monthly + Annual subscribers

All dollar-denominated columns (revenue, CoGS, GP/GM, OpEx, cash flow lines, EBIT) are NULL on NON_FINANCIAL rows.

Key differences between recognition types

Concept	GAAP	Cash	Non-Financial
What it measures	Income statement (accrual)	Cash flow (cash basis)	Volumes and subscriber counts
When revenue is recognized	On fulfillment/earning	On receipt of payment	n/a
Unit metric	—	—	`UNITS_SOLD` and `UNITS_FULFILLED` both live here
Hardware profitability	Hardware GP & GM columns	Not broken out (inventory outlay is tracked instead)	n/a
Bottom line	EBIT (operating income)	Net Change in Cash / Cash Balance at EOM	n/a
Cost grouping	CoGS → Gross Profit → OpEx → EBIT	Inventory Outlay + Software CoGS + Other CoGS + OpEx + Taxes/Financing	n/a

GAAP and Cash views share the same cash flow statement section (Starting Cash through Ending Cash) on the Cash rows, since actual cash position is the same regardless of accounting method. Non-Financial rows carry counts only — no dollars.

Shared columns

These columns appear on every row regardless of recognition type:

Column	Type	Description
`VERSION`	VARCHAR	Budget version identifier (e.g. `2026-04-13_por_update`)
`YEAR`	INTEGER	Budget year
`QUARTER`	INTEGER	Quarter (1–4)
`MONTH`	DATE	First day of the month
`RECOGNITION_TYPE`	VARCHAR	`GAAP`, `CASH`, or `NON_FINANCIAL`
`IS_CURRENT`	BOOLEAN	`TRUE` for the latest imported version
`LOADED_AT`	TIMESTAMP	When the row was imported

The following columns appear on both GAAP and CASH rows (with different values per recognition type) and are NULL on NON_FINANCIAL rows:

Column	Description
`HARDWARE_REVENUE`, `SOFTWARE_REVENUE`, `TOTAL_REVENUE`	Revenue, sliced per recognition basis
`OPEX`	Operating expenses (cash basis on `CASH`, accrual basis on `GAAP`)
`SOFTWARE_COGS`, `SOFTWARE_GP`, `SOFTWARE_GM`	Software cost / gross profit / gross margin

Example queries

Latest GAAP P&L by quarter:

SELECT
    QUARTER,
    SUM(TOTAL_REVENUE) AS REVENUE,
    SUM(TOTAL_GP) AS GROSS_PROFIT,
    ROUND(SUM(TOTAL_GP) / NULLIF(SUM(TOTAL_REVENUE), 0), 3) AS GM,
    SUM(EBIT) AS EBIT
FROM UTILITIES_DB.FINANCE.BUDGET_CURRENT
WHERE RECOGNITION_TYPE = 'GAAP'
GROUP BY QUARTER
ORDER BY QUARTER;

Latest cash position by quarter:

SELECT
    QUARTER,
    SUM(TOTAL_REVENUE) AS CASH_REVENUE,
    SUM(NET_CHANGE_IN_CASH) AS NET_CASH_CHANGE,
    MAX(CASH_BALANCE_AT_EOM) AS ENDING_CASH
FROM UTILITIES_DB.FINANCE.BUDGET_CURRENT
WHERE RECOGNITION_TYPE = 'CASH'
GROUP BY QUARTER
ORDER BY QUARTER;

Latest unit volumes and subscriber counts by quarter:

SELECT
    QUARTER,
    SUM(UNITS_SOLD) AS UNITS_SOLD,
    SUM(UNITS_FULFILLED) AS UNITS_FULFILLED,
    MAX(PAYING_SUBSCRIBERS_TOTAL) AS ENDING_SUBSCRIBERS
FROM UTILITIES_DB.FINANCE.BUDGET_CURRENT
WHERE RECOGNITION_TYPE = 'NON_FINANCIAL'
GROUP BY QUARTER
ORDER BY QUARTER;

Compare GAAP vs Cash revenue by quarter:

SELECT
    c.QUARTER,
    ROUND(SUM(c.TOTAL_REVENUE), 0) AS CASH_REVENUE,
    ROUND(SUM(g.TOTAL_REVENUE), 0) AS GAAP_REVENUE,
    ROUND(SUM(c.TOTAL_REVENUE) - SUM(g.TOTAL_REVENUE), 0) AS DIFF
FROM UTILITIES_DB.FINANCE.BUDGET_CURRENT c
JOIN UTILITIES_DB.FINANCE.BUDGET_CURRENT g ON c.MONTH = g.MONTH
WHERE c.RECOGNITION_TYPE = 'CASH' AND g.RECOGNITION_TYPE = 'GAAP'
GROUP BY c.QUARTER
ORDER BY c.QUARTER;

Importing new versions

New budget versions are loaded via scripts/load_budgets.py:

# Default (reads from tmp/)
python3 scripts/load_budgets.py

# Specify files
python3 scripts/load_budgets.py --cash-csv tmp/new_cash.csv --gaap-csv tmp/new_gaap.csv

# Preview without executing
python3 scripts/load_budgets.py --dry-run

The script is idempotent — re-running with the same version deletes and re-inserts. The IS_CURRENT flag automatically shifts to the newly imported version.