#199 Trial balance rows are read as period movements; nothing declares the basis, so period-end balances double-count

[Anonymous]

Originally created by: grynn-in

What happened

On a site whose trial balances were uploaded as period-end balances (the same closing balance for share capital appears in every year-end TB), the balance sheet models treat each row as a period movement and add them up. Share capital of one subsidiary: TB row −10,588,235.29 in both 2011-12 and 2012-12; gold_balance_sheet.cumulative_balance shows −10.6M in 2011, −21.2M in 2012, −30.9M in 2013, −50.1M in 2015. Every balance-sheet figure on the site is overstated by the sum of all prior year-ends.

Root cause

Nothing declares what a trial-balance row means, and the models assume one answer.

• gold_balance_sheet computes cumulative_balance as a running sum over periods; gold_bs_movement.sql takes period_net_amount as period_movement and lagInFrame(cumulative_balance) as opening_balance; gold_ytd_trial_balance is a running sum within the year. assert_end_to_end_bs_balances.sql says in its header that gold_fully_consolidated_tb "is a per-period MOVEMENT trial balance".
• Trial Balance Submission (konsol/epm/doctype/trial_balance_submission/trial_balance_submission.json) has data_area_id, fiscal_year, fiscal_period, tb_file, batch_id, validation_status, row_count, total_debit, total_credit. No field says whether the amounts are period movements, year-to-date movements or period-end balances. EPM Settings has no site default either. grep -rn -iE "ytd|movement|cumulative|basis" over the doctype and settings finds nothing.
• ERP exports come in all three forms (a D365 "trial balance" is usually closing balances; a GL activity report is movements; many close packs are YTD). A user who uploads what their ERP calls a trial balance gets a balance sheet that is wrong by construction, with no message.

Suggested fix

1. Declare the basis. Trial Balance Submission gets amount_basis (Select: Period movement / Year-to-date movement / Period-end balance), required, no default. EPM Settings gets default_amount_basis that only pre-fills the form. The CSV upload accepts an optional amount_basis column and refuses a file that disagrees with the form.
2. Normalise once, in silver. silver_gl_entries (or a new silver_tb_movements) converts every batch to period movements: Period movement passes through; Year-to-date → this period's YTD − the prior period's YTD in the same year (first period: as is); Period-end balance → this balance − the prior period-end balance (first period with data: the balance itself, i.e. from inception). Everything downstream keeps its one convention. A missing prior period is a warning that names the entity and period, never a silent 0.
3. Backfill. A patch cannot know the basis of 836 existing batches. It leaves amount_basis empty and a build pre-check refuses to consolidate a batch without one, naming it; a bulk action on the Trial Balance Submission list ("Set amount basis…") lets an admin set it for the batches of an entity or a year.
4. Tests: a singular test per basis with fixtures under dbt_project/test_fixtures/ (two periods each; the second period's movement must equal the expected difference), and assert_tb_submission_has_basis.

Can a user work around it in the UI?

No. Nothing in the UI changes how a batch is read. The only workaround is to re-derive movements outside konsol and upload those instead, which nothing tells the user to do.

Repro

1. Upload the same balance-sheet TB for two consecutive periods (e.g. share capital −100 in both).
2. Run a full build. gold_balance_sheet.cumulative_balance for that account is −100 in the first period and −200 in the second.

🤖 Generated with Claude Code

https://claude.ai/code/session_013WewQKFQgG7o2M3mUDPRR5

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed