Allocation Guide

Konsolidat supports driver-based cost allocation with dynamic multi-step cascading. Costs are distributed from source cost centers to targets based on measurable drivers (headcount, square meters, revenue, and more). The engine is N-step, not fixed at three: the number of steps is derived at run time from the rules, so adding another step is just another rule row.

Overview

graph TD
    TB[gold_trial_balance<br/>Source pool amounts] --> ALLOC[Allocation Engine]
    RULES[allocation_rules<br/>ordered by step_order] --> ALLOC
    DH[allocation_drivers_headcount] --> ALLOC
    DS[allocation_drivers_sqm] --> ALLOC
    DR[allocation_drivers_revenue] --> ALLOC
    ALLOC --> RESULTS[gold_allocation_results]

Allocation Rules

Defined in seeds/allocation_rules.csv:

Rule ID	Name	Step	Source Account	Source CC	Driver	Target Account
ALLOC_001	IT Cost Allocation	1	7100	IT	headcount	7100
ALLOC_002	Facility Cost Allocation	2	7200	FACILITY	sqm	7200
ALLOC_003	Management Fee Allocation	3	7300	MGMT	revenue	7300

The seed ships three rules, but the engine processes however many step_order values exist — there is no hard-coded step count.

Rule Fields

Field	Description
allocation_rule_id	Unique rule identifier
rule_name	Human-readable name
step_order	Execution order (1, 2, 3, … N) — later steps see cascaded amounts
source_account	GL account to allocate from
source_cost_center	Cost center holding the pool
driver_type	Driver name: headcount, sqm, revenue, composite, conditional, or tiered
target_account	GL account to allocate to
allocation_method	step_down (default cascade) or reciprocal (iterative circular allocation). Available on staging rules.
driver_formula	Optional formula for composite (e.g. headcount*0.6+sqm*0.4) or conditional (CASE …) drivers; empty for a simple driver. Available on staging rules.

Driver Data

Three driver seed files, all with the same schema:

Column	Type	Description
data_area_id	String	Legal entity
cost_center	String	Receiving cost center
driver_value	Decimal	Driver quantity (e.g., headcount = 25)
fiscal_year	UInt16	Year
fiscal_period	UInt8	Period

Driver weight is computed as:

driver_weight = driver_value / SUM(driver_value)
                OVER (PARTITION BY data_area_id, fiscal_year, fiscal_period)

The source cost center is excluded from receiving allocations (no self-allocation).

Revenue drivers filter out driver_value ≤ 0 to avoid division issues.

Multi-Step Cascade

The allocation engine runs each step sequentially in step_order, and each step can see amounts allocated by prior steps. The step count is dynamic: the macro queries MAX(step_order) from the rules at run time and unrolls that many steps, so the engine is not limited to three. The three steps below match the default seed.

Step 1: IT Cost Allocation (Headcount)

Pool = SUM(period_net_amount) FROM gold_trial_balance
       WHERE main_account = '7100' AND cost_center = 'IT'

Allocated = pool × driver_weight (headcount)
Target: Each non-IT cost center gets 7100 amounts proportional to headcount

Step 2: Facility Cost Allocation (Square Meters)

Pool = SUM(period_net_amount) FROM gold_trial_balance
       WHERE main_account = '7200' AND cost_center = 'FACILITY'
     + Any Step 1 allocations that landed in FACILITY cost center

Allocated = pool × driver_weight (sqm)

Cascade: If Step 1 allocated IT costs to FACILITY, those amounts flow into Step 2's pool.

Step 3: Management Fee Allocation (Revenue)

Pool = SUM(period_net_amount) FROM gold_trial_balance
       WHERE main_account = '7300' AND cost_center = 'MGMT'
     + Any Step 1 + Step 2 allocations that landed in MGMT cost center

Allocated = pool × driver_weight (revenue)

Worked Example

Suppose for USMF, 2024, period 5:

Step 1 — IT Costs ($100,000):

Cost Center	Headcount	Weight	Allocated
SALES	50	50/80 = 62.5%	$62,500
FACILITY	10	10/80 = 12.5%	$12,500
MGMT	20	20/80 = 25.0%	$25,000

Step 2 — Facility Costs ($80,000 original + $12,500 cascade = $92,500):

Cost Center	SQM	Weight	Allocated
SALES	500	500/800 = 62.5%	$57,813
IT	100	100/800 = 12.5%	$11,563
MGMT	200	200/800 = 25.0%	$23,125

Step 3 — Management Fees ($50,000 original + $25,000 + $23,125 cascade = $98,125):

Cost Center	Revenue	Weight	Allocated
SALES	$2M	2M/2.5M = 80%	$78,500
IT	$0.5M	0.5M/2.5M = 20%	$19,625

Output: gold_allocation_results

Each row represents one allocation line:

Column	Description
allocation_rule_id	Which rule (ALLOC_001, etc.)
step_order	Step number (1, 2, 3)
data_area_id	Entity
fiscal_year / fiscal_period	Period
source_account / source_cost_center	Where the cost came from
target_cost_center / target_account	Where it was allocated to
driver_type	Driver used
pool_amount	Total pool for this step
driver_weight	Recipient's share (0–1)
allocated_amount	Amount allocated to this target

Tests

Test	Assertion
assert_each_step_sums_to_pool	|pool_amount − SUM(allocated_amount)| ≤ 0.01 per step
assert_no_self_allocation	Source cost center never appears as target

Allocation Methods

The cascade above is the step-down method (allocation_method = 'step_down'), the default. The engine also ships two additional methods:

• Reciprocal (allocation_method = 'reciprocal') — handles circular allocations (e.g. IT and Facilities both serve each other) via iterative convergence. The allocation_engine_reciprocal() macro feeds each iteration's output back as input until the change falls below 0.01 (max 10 iterations). Reciprocal rules are processed before step-down rules.
• Tiered (driver_type = 'tiered') — applies per-tier rates with cap/floor clamping, driven by an allocation_tiers table (tier_order, lower_bound, upper_bound, rate, cap, floor). Implemented in allocation_engine_tiered().

In addition, the driver_formula field enables composite drivers (a weighted blend of multiple driver types, e.g. headcount*0.6+sqm*0.4) and conditional drivers (CASE-based selection), resolved through resolve_allocation_driver.

Adding a New Allocation Rule

1. Add a row to seeds/allocation_rules.csv with the next step_order
2. Create a driver seed CSV (or reuse an existing driver)
3. Run dbt seed && dbt build

No macro edit is needed to add a step — allocation_engine_multistep() derives the step count from the rules at run time, so a new step_order row is processed automatically.

See Extending dbt Models for the full workflow.

Next Steps

• Consolidation Guide — Multi-entity consolidation
• Budgeting Guide — Budget spreading
• Macro Reference — Allocation engine macro details