Budget Layers — Collaborative Budgeting with Full Audit Trail
Why Layers?
Most budgeting tools give you one number per cell. If Finance cuts your budget, you lose the original. If the board adjusts it, you lose the Finance version. Nobody knows who changed what, when, or why.
Konsolidat stores every stakeholder's contribution separately and sums them at query time. The original submission, every adjustment, every override — all preserved, all auditable, all reversible.
The Four Layers
| Layer | Edited By | Purpose |
|---|---|---|
| base | Budget Submitter | Department's original submission |
| challenge | Budget Controller | Finance team adjustments (typically cuts) |
| management | Budget Manager | Executive overrides (strategic additions) |
| board | Budget Approver | Board-level final adjustments |
All roles can view every layer. Each can only edit their own.
Effective budget = base + challenge + management + board (always, for every period).
Worked Example
Scenario: BUDGET_2025, Entity: USMF, Account: 6100 (Sales Expense), Cost Center: SALES
1. Department Submits (base)
The Sales team enters their monthly plan in Frappe Desk — $100k/month:
| P1 | P2 | P3 | P4 | P5 | P6 | P7 | P8 | P9 | P10 | P11 | P12 | Annual | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| base | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 1,200,000 |
Workflow state: Draft → submitter clicks Submit for Review → Submitted
2. Finance Challenges (challenge)
The Budget Controller reviews and applies a 5% cut across all months:
| P1 | P2 | P3 | P4 | P5 | P6 | P7 | P8 | P9 | P10 | P11 | P12 | Annual | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| base | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 1,200,000 |
| challenge | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -60,000 |
| Subtotal | 95,000 | 95,000 | 95,000 | 95,000 | 95,000 | 95,000 | 95,000 | 95,000 | 95,000 | 95,000 | 95,000 | 95,000 | 1,140,000 |
3. Management Override (management)
The CFO approves additional funding for a Q3 product launch:
| P1 | P2 | P3 | P4 | P5 | P6 | P7 | P8 | P9 | P10 | P11 | P12 | Annual | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| base | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 1,200,000 |
| challenge | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -60,000 |
| management | — | — | — | — | — | — | 15,000 | 15,000 | 10,000 | — | — | — | 40,000 |
| Subtotal | 95,000 | 95,000 | 95,000 | 95,000 | 95,000 | 95,000 | 110,000 | 110,000 | 105,000 | 95,000 | 95,000 | 95,000 | 1,180,000 |
4. Board Adjusts and Approves (board)
The board adds Q4 contingency for year-end push:
| P1 | P2 | P3 | P4 | P5 | P6 | P7 | P8 | P9 | P10 | P11 | P12 | Annual | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| base | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 100,000 | 1,200,000 |
| challenge | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -5,000 | -60,000 |
| management | — | — | — | — | — | — | 15,000 | 15,000 | 10,000 | — | — | — | 40,000 |
| board | — | — | — | — | — | — | — | — | — | 5,000 | 5,000 | 5,000 | 15,000 |
| EFFECTIVE | 95,000 | 95,000 | 95,000 | 95,000 | 95,000 | 95,000 | 110,000 | 110,000 | 105,000 | 100,000 | 100,000 | 100,000 | 1,195,000 |
The approver clicks Approve → ClickHouse sync fires → dbt build runs.
The per-period effective budget above (95k / 110k / 105k …) is what Frappe resolves from the layers and writes to epm_gold.budget_monthly_input as an audit trail. In the current shipped code, however, EPM(..., "budget") reads gold_spread_budget, which is built from the budget_annual_input seed spread by spread_profiles weights — it does not read budget_monthly_input. So the downstream tables below line up with the layered figures only when the annual seed total and chosen spread profile reproduce that monthly shape. Wiring the resolved per-layer budget into the dbt budget model is tracked as a roadmap item.
What ClickHouse Stores
Every row from every layer is persisted. Nothing is aggregated at write time:
epm_gold.budget_monthly_input
| scenario_id | data_area_id | fiscal_year | main_account | fiscal_period | amount | layer |
|---|---|---|---|---|---|---|
| BUDGET_2025 | USMF | 2025 | 6100 | 1 | 100,000 | base |
| BUDGET_2025 | USMF | 2025 | 6100 | 1 | -5,000 | challenge |
| BUDGET_2025 | USMF | 2025 | 6100 | 7 | 100,000 | base |
| BUDGET_2025 | USMF | 2025 | 6100 | 7 | -5,000 | challenge |
| BUDGET_2025 | USMF | 2025 | 6100 | 7 | 15,000 | management |
| BUDGET_2025 | USMF | 2025 | 6100 | 10 | 100,000 | base |
| BUDGET_2025 | USMF | 2025 | 6100 | 10 | -5,000 | challenge |
| BUDGET_2025 | USMF | 2025 | 6100 | 10 | 5,000 | board |
| ... | ... | ... | ... | ... | ... | ... |
This gives you full audit trail: who contributed what, when, and at which layer.
What dbt Produces
gold_spread_budget does not read budget_monthly_input. It reads the budget_annual_input seed — one annual amount per scenario/account/dimension — and spreads each annual_amount across the 12 periods using normalized weights from the spread_profiles seed (period_amount = annual_amount × period_weight). There is no per-layer summing pipeline in dbt; the additive layer logic lives upstream in Frappe, which writes the already-resolved per-period rows to epm_gold.budget_monthly_input.
gold_spread_budget (what EPM() queries — shown here matching the effective budget per the caveat above)
| scenario_id | data_area_id | fiscal_year | fiscal_period | main_account | period_amount |
|---|---|---|---|---|---|
| BUDGET_2025 | USMF | 2025 | 1 | 6100 | 95,000 |
| BUDGET_2025 | USMF | 2025 | 2 | 6100 | 95,000 |
| BUDGET_2025 | USMF | 2025 | 3 | 6100 | 95,000 |
| BUDGET_2025 | USMF | 2025 | 4 | 6100 | 95,000 |
| BUDGET_2025 | USMF | 2025 | 5 | 6100 | 95,000 |
| BUDGET_2025 | USMF | 2025 | 6 | 6100 | 95,000 |
| BUDGET_2025 | USMF | 2025 | 7 | 6100 | 110,000 |
| BUDGET_2025 | USMF | 2025 | 8 | 6100 | 110,000 |
| BUDGET_2025 | USMF | 2025 | 9 | 6100 | 105,000 |
| BUDGET_2025 | USMF | 2025 | 10 | 6100 | 100,000 |
| BUDGET_2025 | USMF | 2025 | 11 | 6100 | 100,000 |
| BUDGET_2025 | USMF | 2025 | 12 | 6100 | 100,000 |
What Excel Sees via EPM()
Single Period
| Cell | Formula | Result |
|---|---|---|
| B3 | =EPM("USMF", 2025, 1, "6100", "period_amount", "budget") | 95,000 |
| B9 | =EPM("USMF", 2025, 7, "6100", "period_amount", "budget") | 110,000 |
| B12 | =EPM("USMF", 2025, 10, "6100", "period_amount", "budget") | 100,000 |
Quarterly & Annual Totals
| Cell | Formula | Result | Calculation |
|---|---|---|---|
| C3 | =EPM("USMF", 2025, "Q1", "6100", "period_amount", "budget") | 285,000 | 95k + 95k + 95k |
| C5 | =EPM("USMF", 2025, "Q3", "6100", "period_amount", "budget") | 325,000 | 110k + 110k + 105k |
| C7 | =EPM("USMF", 2025, "H2", "6100", "period_amount", "budget") | 625,000 | Q3 + Q4 |
| D2 | =EPM("USMF", 2025, "FY", "6100", "period_amount", "budget") | 1,195,000 | all 12 periods |
Variance Analysis
Assume actuals for P5 came in at $92,000 against the $95,000 budget:
| Cell | Formula | Result | Meaning |
|---|---|---|---|
| E5 | =EPM("USMF", 2025, 5, "6100", "actual_amount", "variance") | 92,000 | What was actually spent |
| F5 | =EPM("USMF", 2025, 5, "6100", "budget_amount", "variance") | 95,000 | The approved budget |
| G5 | =EPM("USMF", 2025, 5, "6100", "variance_abs", "variance") | -3,000 | Under budget by $3k |
| H5 | =EPM("USMF", 2025, 5, "6100", "variance_pct", "variance") | -3.16 | 3.16% under budget |
| I5 | =EPM("USMF", 2025, 5, "6100", "variance_favorable", "variance") | 1 | Favorable (expense below budget) |
Workflow States
| State | Who Can Act | What Happens |
|---|---|---|
| Draft | Budget Submitter edits base layer | Not synced to ClickHouse |
| Submitted | Budget Controller reviews, edits challenge layer | Read-only for submitter |
| Approved | Budget Approver approves | Syncs all layers to ClickHouse |
| Rejected | Returns to Budget Submitter | Back to Draft for correction |
Draft ──[Submit for Review]──→ Submitted ──[Approve]──→ Approved ──→ CH Sync ──→ dbt build
│
└──[Reject]──→ Rejected ──[Resubmit]──→ Submitted
End-to-End Data Flow
FRAPPE DESK CLICKHOUSE EXCEL
─────────── ────────── ─────
Budget Input doc
┌─ base: +100k/mo ─┐
├─ challenge: -5k/mo ├──→ epm_gold.budget_monthly_input
├─ management: +40k Q3 │ (48 rows: 12mo x 4 layers, sum = effective budget)
└─ board: +15k Q4 ┘ Frappe writes the resolved per-period rows.
│
[Approve] │ (audit-trail table; not a dbt source)
│
budget_annual_input seed ──┐ │
spread_profiles seed ├──→ dbt build
┘ │
▼
gold_spread_budget =EPM("USMF",2025,5,
(12 rows: annual_amount "6100","period_amount",
spread by profile weight) "budget")
│ │
▼ ▼
gold_variance_analysis 95,000
(actual vs budget)
Forecast Uses the Same Structure
The Budget Input doctype handles both budget and forecast. The scenario_id field links to a Scenario Definition where scenario_type = budget or forecast. Everything else — layers, workflow, roles, Excel retrieval — works identically:
| Formula | What It Returns |
|---|---|
=EPM("USMF", 2025, 5, "6100", "period_amount", "budget") | Approved budget (all layers summed) |
=EPM("USMF", 2025, 5, "6100", "period_amount", "forecast") | Latest forecast (all layers summed) |
=EPM("USMF", 2025, 5, "6100", "actual_amount", "variance") | Actual from GL |
=EPM("USMF", 2025, 5, "6100", "variance_abs", "variance") | Actual minus budget |
Key Design Points
| Principle | Detail |
|---|---|
| Layers are additive | No separate "final" row — the sum across layers IS the final budget |
| Only Approved budgets sync | Draft and Submitted stay in Frappe only — no stale data in ClickHouse |
| dbt spreads annual inputs | gold_spread_budget reads the budget_annual_input seed and spreads each annual_amount across 12 periods via spread_profiles weights — it does not read budget_monthly_input |
| EPM() returns one value per period | Excel users see a single period_amount, not individual layers |
| Full audit trail | Per-layer rows persist in epm_gold.budget_monthly_input (written by Frappe) — query by layer for reporting |
| Role-based editing | Each layer locked to its role; System Manager can edit all |
| Budget = Forecast | Same doctype, same layers, same workflow — distinguished by scenario_id |