Skip to main content

Multi-Step Cascading Allocations

Problem​

Real allocations require ordered steps where output of step N becomes input to step N+1. Example:

  1. Step 1: Allocate IT costs to all departments by headcount
  2. Step 2: Allocate Facilities costs (which now include some IT) by square meters
  3. Step 3: Allocate shared services to business units by revenue

This is implemented as a dynamic N-step engine: the number of steps is not hard-coded. allocation_engine_multistep() derives max_steps at run time by querying MAX(step_order) from the rules (run_query) and unrolls exactly that many steps via Jinja. Adding a 4th (or Nth) step requires only a new rule row plus its driver values — no macro change.

Requirements​

R1: Step ordering in allocation_rules seed​

Add column step_order (integer) to allocation_rules.csv:

allocation_rule_id,rule_name,step_order,source_account,source_cost_center,driver_type,target_account,description
ALLOC_001,IT Cost Allocation,1,7100,IT,headcount,7100,Step 1: IT by headcount
ALLOC_002,Facility Allocation,2,7200,FACILITY,sqm,7200,Step 2: Facilities by sqm
ALLOC_003,Mgmt Fee Allocation,3,7300,MGMT,revenue,7300,Step 3: Mgmt by revenue

R2: Cascading allocation macro​

Macro allocation_engine_multistep that:

  1. Reads all rules ordered by step_order
  2. Derives max_steps dynamically at run time (run_query on MAX(step_order)) and unrolls that many steps
  3. For step 1: source pool comes from gold_trial_balance
  4. For step N>1: source pool comes from gold_trial_balance PLUS allocated amounts from steps 1..N-1 that landed in this step's source cost center / account
  5. Each step produces allocation rows with step_order column

R3: Model gold_allocation_results​

  • Shipped as gold_allocation_results (the original design name gold_allocation_results_v2 was never used)
  • Contains all steps' results
  • Columns: step_order, allocation_rule_id, data_area_id, fiscal_year, fiscal_period, source_account, target_cost_center, driver_type, pool_amount, driver_weight, allocated_amount

R4: Driver seed flexibility​

  • Each rule references its own driver seed via driver_seed_name column
  • allocation_drivers_headcount (existing), add allocation_drivers_sqm, allocation_drivers_revenue

Acceptance Tests​

TestAssertion
assert_allocation_step_orderAll rows have valid step_order matching their rule
assert_step2_pool_includes_step1Step 2 pool_amount > original TB amount for that account (proves cascade)
assert_each_step_sums_to_poolPer step: sum(allocated_amount) = pool_amount within 0.01
assert_no_self_allocationNo row has target_cost_center = source_cost_center

Shipped Allocation Methods​

Beyond the step-down cascade, the following methods are implemented in code (controlled by the allocation_method and driver_formula rule fields):

  • Reciprocal / circular allocation — allocation_engine_reciprocal() (allocation_method = 'reciprocal'). Iterative convergence: each iteration feeds allocated amounts back as input until the delta falls below 0.01 (max 10 iterations). Reciprocal rules run before step-down rules.
  • Tiered / threshold allocation — allocation_engine_tiered() (driver_type = 'tiered'). Per-tier rate applied to each band, with cap/floor clamping, driven by the allocation_tiers table.
  • Composite & conditional drivers — resolved via resolve_allocation_driver using the driver_formula field: composite (weighted blend such as headcount*0.6+sqm*0.4) and conditional (CASE-based) drivers.

Out of Scope​

  • None of the originally-deferred methods remain out of scope: reciprocal (circular/iterative) allocation now ships via allocation_engine_reciprocal().