Reporting automation
Why your month-end lender reporting still lives in Excel, and how to move it
Ask a non-bank lender how month-end reporting works and the answer is often the same. Someone runs a set of extracts from the loan system, pastes them into last month’s workbook, fixes whatever broke and checks the totals against the trial balance. Two or three days later, the funder pack goes out.
It works, until the person who built the workbook goes on leave, a funder asks for a new cut of the data, or a number doesn’t tie and nobody can say why.
Why it stays in Excel
- The logic lives in people’s heads. Which transactions count as a repayment? How is a partial payment treated in the arrears calculation? These rules are often only written down in formulas.
- Loan system data is hard to read. Servicing platforms store data for transactions, not for reporting. Turning it into balances, buckets and movements needs real knowledge of the schema.
- Every report has its own copy of the truth. The arrears report, the board pack and the funder report each calculate the same numbers slightly differently.
A practical path out
Step 1: Pick the report that hurts most
Don’t start with a platform project. Start with the one report that takes the longest or causes the most questions, often the funder or warehouse report.
Step 2: Write the rules down once
Document every definition the report relies on: balances, arrears days, product classes, exclusions. These become the measures in a shared data model, not formulas in a workbook.
Step 3: Build a model that reconciles to source
A clean SQL and Power BI model, refreshed automatically, with a reconciliation page that proves the totals match the loan system and the general ledger.
Step 4: Run both in parallel, then switch
Run the new report beside the old one for a cycle or two. When they agree, or the differences are explained, retire the spreadsheet.
Month-end should be a refresh, not a project.
Once one report runs this way, the next one is much quicker, because the definitions and data model already exist.
Bring one report that causes you pain.
A 30-minute discovery call, no obligation. We’ll talk through how it could run itself.
Book a discovery call