Sixteen years of member records, reconciled and generated on demand
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.
Jan 2023 to Jun 2024
Illustrative figures. Layout, source labels and the cutoff are the real statement’s.
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.
- Name differs between datasets
- Coded to the wrong member
- Transaction omitted
- Record incomplete
- Disagrees with year-end figures
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.
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.
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.
Rows after the chosen date fade. The source label flips at the cutoff and never anywhere else.
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.
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.
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.
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.
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.
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.