Sixteen years of member records, reconciled and generated on demand

01Case study · Controllership and automation

Sixteen years of member records. Statements on demand.

Treewalk rebuilt a First Nation housing department’s member accounts back to 2010, then built an application for the housing team to run itself.

ClientA First Nation housing department
WorkAccounting cleanup and statement automation
Housing DepartmentMember statement · Accounts receivableMember 037
Jan 2023 to Jun 2024
DateDescriptionChargePaymentBalanceSource
2023-01-01Rent charge850.002,310.00Workbook
2023-01-15Payroll deduction425.001,885.00Workbook
2023-02-01Rent charge850.002,735.00Workbook
2023-02-14Payroll deduction425.002,310.00Workbook
2023-06-01Rent charge850.002,310.00Workbook
2023-11-30EQ payment applied1,200.001,110.00Workbook
2024-03-01Rent charge875.001,985.00Workbook
2024-03-15Payroll deduction437.501,547.50Workbook
Cutoff · 31 Mar 2024
2024-04-01Rent charge875.002,422.50ERP
2024-04-15Payroll deduction437.501,985.00ERP
2024-05-01Rent charge875.002,860.00ERP
2024-05-15Cheque payment875.001,985.00ERP
2024-06-01Rent charge875.002,860.00ERP
Closing balance · 30 Jun 20242,860.00

Illustrative figures. Layout, source labels and the cutoff are the real statement’s.

16yrsof member history
160members
3statement types
80+discrepancies surfaced
02Where it started

A statement process that broke when the system changed.

The housing department issues statements to members for three balances: what a member owes on their housing account, the replacement reserve credit allocated to their home, and their equalization entitlement. Members read those statements to know where they stand. The housing team relies on them to answer questions, to support estate and equity determinations, and to reconcile to the Band’s audited year-end position.

For years the process was semi-automated. Staff exported the general ledger, pasted it into an Excel workbook, and the workbook populated each statement. When the Band migrated its accounting to a new ERP, that link broke. From then on an internal accountant had to work through the general ledger line by line and code every transaction to the member it belonged to, for roughly 160 members, across three statement types.

Manual coding at that scale produced the errors it always produces. By the time Treewalk was engaged, statements were one to two years behind and each member file was in a different state.

The old ledger · illustrative
DateMemberDescriptionAmountCoded to
2022-03-01J. PointRent charge850.00M-037Same member appears as “J. Point” here and “Point, Jas.” in the statement workbook. The two never matched.
2022-03-15Point, Jas.Payroll deduction425.00M-037
2022-04-01Point, Jas.Rent charge850.00M-073Coded to member 073, not 037. One transposed digit and two members’ balances are wrong.
2022-04-15Point, Jas.Payroll deductionmissingThe deduction ran through payroll but never reached the statement. Balance overstated from here on.
2022-05-01Point, Jas.Rent charge850.00M-037
2022-05-31Point, Jas.Adjustment312.40M-037No supporting record for what this adjustment was. Incomplete history, so nobody could explain the balance.
2022-06-01Point, Jas.Rent charge850.00M-037
Statement balance3,412.40
Year-end working papers2,987.40

Hover or tab to a highlighted row to see what went wrong. Names and figures are illustrative; the five error types are the ones found in the engagement.

03Why automation alone would have failed

The problem underneath the request.

The Band asked for the statements to be made automatic. As Treewalk’s controllership team worked through the data, it became clear that automating the existing records would have reproduced the existing errors, at scale and with more authority.

One misallocated transaction is immaterial to the housing program as a whole. It is entirely material to the member whose statement carries it.

That asymmetry made this a reconciliation problem before it was an automation problem, and Treewalk reshaped the engagement around it. First, reconcile every member’s history from the earliest records the Band held. Then build a tool that holds that reconciled history and applies each period’s activity to it, so the reconciliation stays done.

Reconciliation was done member by member and transaction by transaction. Where supporting documentation was missing, Treewalk sourced it from internal and external parties. Where Band policy on the treatment of mortgage-related costs was incomplete, Treewalk worked with the Band to develop a position and applied it consistently across every member.

The rebuilt history did something the old process could not: it surfaced the differences. Where the reconstructed record disagreed with the Band’s year-end figures, Treewalk compiled the differences for more than eighty members, with the history behind each one, and presented them to the Band to decide. Treewalk did not adjust a member’s balance without knowing why it moved.

The 160 · reconciles as you scroll

UnreconciledReconciled to the transactionDifference itemised for the Band
160 member histories rebuilt · 80+ differences itemised for the Band
04Two eras, one rule

Every figure traces to one source, and the statement says which.

The Band’s records ran back to 2010 across two eras: Excel history from before the migration and the new ERP from after it. Treewalk set a clear cutoff for each statement. For accounts receivable, the historical workbook is the source through 31 March 2024 and the new ERP from 1 April 2024 onward. Reserve and equalization statements combine their own historical workbooks with data pulled from the ERP through its OData interface.

An earlier approach had relied on an Excel format and a Power BI model, with no front end the housing staff could use themselves. That set a second requirement: the tool had to be something the housing team could operate without a specialist standing behind it.

Drag through sixteen years
Cutoff

31 Mar 2024
Source: Historical workbook
2023-12-01Rent charge850.00Workbook
2024-01-01Rent charge875.00Workbook
2024-02-01Rent charge875.00Workbook
2024-03-01Rent charge875.00Workbook
2024-04-01Rent charge875.00ERP
2024-05-01Rent charge875.00ERP
2024-06-01Rent charge875.00ERP

Rows after the chosen date fade. The source label flips at the cutoff and never anywhere else.

05The application

Built to be trusted by people who will never read its code.

Treewalk built a web application on a PostgreSQL database, hosted on Render, with sign-in limited to named, approved users. Staff search for a member, choose the statement type and period, generate the statement, review totals and source coverage, and export to Excel or CSV on Housing Department letterhead, with a source label on every line.

Each statement type has its own confirmed layout, agreed with the Band. Source workbooks are loaded through an admin upload workflow, never from anyone’s desktop, and upload slots are validated so a workbook cannot land in the wrong place.

Correct a locked row · try it
DateDescriptionAmountSourceState
2023-06-01Rent charge850.00WorkbookLocked
2023-06-15Payroll deduction452.00WorkbookLocked
2023-06-15Payroll deduction · overrideStaff editOverride
2023-07-01Rent charge850.00WorkbookLocked
Event log · illustrative

2026-09-01 09:14Workbook imported · AR compilation · 2,214 rows
2026-09-01 09:31ERP sync completed · 412 rows · no overlap
2026-09-02 14:05Statement generated · Member 037 · AR · Jan 2023 to Jun 2024

The imported row is never overwritten. The correction sits beside it, and the log records who changed what, and when.

  • Locked source rowsImported data cannot be edited in place. Corrections are recorded as overrides beside the original, and both are kept.
  • Separate adjustmentsManual adjustments are stored apart from imported data and included in generation, so nothing imported is ever overwritten.
  • Event logEvery import, generated statement, adjustment, row edit and sync is written to a log administrators can view and export.
  • Named accessSign-in is by named, approved user only. Credentials and source files live only in the hosting environment, never in the code repository or the deployment image.
  • Safe syncThe ERP sync pages through the data, tracks its own status, and will not start a run while another is in progress.
  • TestedAutomated tests cover statement logic, sign handling, the cutoff rule, workbook validation, exports, authentication and the sync. All passed at handover.
  • Owned by the BandThe plan is for the Nation to hold its own hosting account, so the tool and its data sit with the Band rather than with Treewalk.
06The time

The saving is an estimate. The arithmetic is yours to check.

Neither the Band nor Treewalk can put a reliable number on the old process. Under it, every statement required an accountant to open the member’s file, review each transaction, and copy the activity into a statement by hand. The team’s own recollection is that reconciliation alone ran into weeks. Set the assumptions yourself.

Statements per cycle480
Minutes per statement30
240estimated accountant hours per full cycle, on the assumptions above
Now: select a member, choose the statement, generate. The reconciled history carries forward on its own.

This is an estimate, not a measured result. The larger saving is harder to count: the old process could not tell the Band which statements were wrong. The new one can, and did.

07What this engagement shows

One team did the accounting and built the software.

The controllership side reconstructed sixteen years of records and set the accounting rules. The automation side turned those rules into a tool that enforces them. Neither half was subcontracted.

The mechanics carry over to any organization that issues member-level or account-level statements from a history spread across systems: housing departments, trust and benefit programs, member equity accounts. The data work is different every time, and it comes first. The application is what makes the reconciliation stay done.

Talk to our Controllership teamOr ask about this engagement directly.
For members

Every line on a member’s statement now names where it came from. The history behind every balance is preserved. Any correction sits beside the figure it corrected, with a record of who made it and when. When a member asks why a balance is what it is, the housing team can answer, and show the working.

TREEWALK · Chartered Professional Accountants
Client details are withheld to protect confidentiality. Ledger figures on this page are illustrative.