By Fund Shared Cash Allocation Design

By-Fund Shared Cash Allocation Design

Status: Design only. Do not post any balancing entries from this document without explicit approval and the required lock/unlock confirmation flow. Reference query: supabase/queries/derive_by_fund_shared_cash_allocations.sql

Purpose

The consolidated GL can be at perfect Aplos parity while BalanceSheetByFund still shows fund-column imbalances.

This happens because Aplos transaction exports preserve the raw posting org/fund attribution, but do not expose the internal shared-cash balancing layer that Aplos uses to make per-fund balance sheet columns balance.

Goal

Create a net-zero internal allocation layer so each fund column satisfies:

Assets = Liabilities + Equity + All-Time Net Income

without changing the consolidated balance sheet totals.

Allocation Formula

For each fund F at cutoff date D:

required_shared_cash_F
  = liabilities_F
  + equity_F
  + all_time_net_income_F
  - non_shared_assets_F

allocation_needed_F
  = required_shared_cash_F
  - current_shared_cash_F

Where:

• liabilities_F = posted liabilities through the cutoff
• equity_F = posted equity through the cutoff
• all_time_net_income_F = posted revenue minus expense through the cutoff
• non_shared_assets_F = assets excluding the allocatable shared-cash pool
• current_shared_cash_F = current posted balance of the allocatable shared-cash pool

Default Shared-Cash Pool

The current derivation assumes:

• 1000
• 1001

If the business decides that another shared cash-like asset (for example 1131) should participate in the balancing layer, the derivation query must be updated before posting anything.

Posting Shape

For a fund with positive allocation_needed:

• Debit shared cash (1000, or the approved shared-cash account mix) on the child fund
• Credit shared cash on InFocus Admin

For a fund with negative allocation_needed:

• reverse the direction

These entries are net-zero at the consolidated level and therefore preserve consolidated BS parity while changing the by-fund ownership of shared cash.

Example

If CrossRoads Youth Ranch needs 112,260.73:

• Dr 1000 on CrossRoads Youth Ranch 112,260.73
• Cr 1000 on InFocus Admin 112,260.73

Verification Rules

After any posting:

1. Consolidated get_balance_sheet_imbalance must remain 0 2. Consolidated assets/liabilities/equity totals must remain unchanged 3. Each fund-column equation difference in BalanceSheetByFund must be 0 or within rounding tolerance 4. The allocation journal entries must be clearly tagged and documented

Important Constraint

This design is only needed because:

• the consolidated GL is correct
• the by-fund balancing layer is missing from the exported/staged transaction data

It should not be confused with fixing bad source transactions. It is a presentation/ownership-balancing layer for shared cash across funds.

Synced from IFMmvp-Frontend documentation: pages/accounting/18-BY-FUND-SHARED-CASH-ALLOCATION-DESIGN.md