“Lazy” bookkeeping: Our approach to automating bookkeeping and reconciliation
In previous posts, we’ve written about:
Why we see value in using a single process to feed both accounting for financial statements and investment reporting
How efficient accounting leads to empowered teams that can easily measure their impact
How we created automated feeds from banks and brokers using SFTP and organised the feeds into standard objects
This post dives a lot deeper into how we use the output from the process below and fulfill our accounting goals, which are to represent the most up-to-date state of the balance sheet and income statement at any point in time, and for it to be accurate enough for both decision-making and audit compliance.
We’re still actively iterating on this process, so if you’re interested in exchanging notes, scroll to the bottom of this post to find out how to get in touch.
Bookkeeping automation was not as straightforward as we expected
When we started the project, we already had automated feeds coming from our banks and brokers for most of our custody accounts, so it seemed like it would just be a hop, skip, and a jump to go from that to a fully automated ledger.
The initial approach we took was akin to how traditional accounting systems approach the problem: book each transaction as a complete journal entry and ensure that the books stay in a consistent balanced state at any point in time. However, as we started, we realised that there are some non-trivial issues that get in the way:
Timing differences. The data’s freshness is jagged, which means that the data that appears at any point in time is not a complete listing of all the actual transactions that occurred on that date. This can be for a few reasons:
Delay in reporting: time from the actual event and when it is sent through our automated feeds. This can be particularly long for private market funds that report valuations months after the fact, but it can also occur for public markets when settlements take longer than expected.
Accruals, deferrals, and depreciation: a cash transaction may not match an expense or income in the current period, or it may need to be expensed over a longer period to match how that expense benefits revenue.
Intercompany transfers of cash and/or instruments may not be booked at the same time, and entities might not report their sides of the transaction at the same time.
Matching transfers and their implicit fees and spreads. Both cash and instruments may be transferred across custody accounts. Apart from finding and matching these transactions, there may be an implicit loss of value due to currency conversion spreads or transfer fees.
Reconciliation. It was not enough to book transactions as they come. In order to trust the data, they would need to be reconciled with the latest available balances from our banks and brokers. Since we would want this to be up to date, this process must be done daily.
When we faced this, we realised that books can be in different stages of quality, and that in order to avoid waiting for all the information to be available before reflecting them in our general ledger, we needed to adopt a more flexible approach to bookkeeping.
“Lazy” bookkeeping: Ensuring the highest quality possible for the information available at different points in time
Ledgers are usually only available in two states: open or closed. Open means that the (usually human) accountant is still actively posting journals, not just original entries, but correcting and adjusting entries as well. Journal entries are also expected to always balance.
What we found was that ledgers can be in many different stages of preparation, and if we take the approach of reflecting everything in the journal even if they are not balanced, we can have very good latency for transactions (even for investment reporting) without sacrificing quality when the annual audit comes along.
To illustrate, here’s a table of the various preparation stages that our books go through, and the assurance that we can use even if they are not completely closed yet.
Click/tap to expand graphic. To close, click/tap “x”.
In the “What can we use this for?” category, we can enable use cases like daily P&L attribution with very little human intervention while still producing “closed” books in the traditional sense when they are needed.
In other words, even before the books are “closed”, they’re still useful.
The following sections go through each of the defined preparation stages, how our accounting solution LedgerPigeon executes them, and the specific challenges we encountered.
Stage 1: Reconciled
Record and reconcile cash and instrument movements from bank and broker feeds.
Information required: Automated bank and broker transaction feeds and holding feeds
Assurance provided: Cash and instrument quantities and balances reflect reality
What can we use this for?:
Investment reporting, particularly trade confirmation and P&L attribution for the portfolio
Cash management, ensuring not too much or too little cash
How often does this information arrive?: Daily via SFTP Wrangler plus APIs where available
The first step is straightforward and does not require any additional information outside of the bank and broker feeds. We first record the movements in cash and instruments as reported.
LedgerPigeon records debits and credits to cash or investment asset accounts, with metadata attached like quantities, currency, custody account, counterparty, etc.
It can be difficult to identify gross vs net values, especially with brokerage feeds. Sometimes, transaction taxes are listed as a separate transaction; other times, the taxes are included in the transaction, either as a separate column or netted out. One would need to manually reconcile the cash amounts at least once to understand what fits and what doesn’t.
After recording the debits and credits, LedgerPigeon matches the rolling sums to their respective holdings feeds for that currency and custody account. The holdings feed is a separately delivered piece of information independent of the transactions, and can therefore be used to increase assurance over the recorded transactions.
In an ideal data world, we’d have the sum of debits and credits always sum to the balance for the holdings file on that day, but there are some complications that make this a bit more difficult:
Banks and brokers are not consistent in whether they use trade dates or settlement dates for delivering the holdings file. Sometimes, different asset classes (such as funds) are treated differently in the same bank.
Banks and brokers usually deliver the holdings and transactions that are consistent for that day, but there are situations where the holdings file is delayed for one or more days.
What we’ve done right now is to be a little forgiving in either direction in terms of the timing of reconciliation, as long as they will eventually reconcile, effectively creating a kappa architecture where the last few days are potentially unreconciled, but the older days are solid.
For instrument holdings, the process is similar, except we reconcile quantities and make a revaluation of their value to mark them to market, if needed. After this step, we can represent our portfolio in the balance sheet and see profit and loss from mark-to-market adjustments in the income statement.
Stage 2: Categorised
Recognise incomes, expenses, depreciables, and transfers.
Information required:
Pattern and manual matching from metadata to the income, expense, depreciable accounts, or the transferred account
Useful lives and salvage values of depreciable assets
Assurance provided:
Company-level income statement reflects reality on a cash basis
Currency conversion spreads and transfer fees are recorded
What can we use this for?:
Budget and expense monitoring
Analysis of sources of spend and opportunities to save
How often does this information arrive?: Daily when automated, within a day of flagging if manual intervention is required
In order to categorise cash expenses and incomes, we’d need to apply some business logic. Right now, we use a combination of some simple pattern matching and manual entry to associate metadata (description, types) from the bank or broker to an account, vendor, and other information.
For cash or instrument transfers, there can be a few scenarios:
For currency conversions, we would match it to a “similar” cash flow in another currency, allowing for a currency conversion spread
For cash transfers, sometimes we are lucky that the description indicates the destination, but sometimes we’d need to match them manually
For instrument transfers, we are usually able to match the instruments incoming and outgoing, provided that there are no significant settlement delays
On a usual day with no new vendors or types of transactions, no intervention is needed. If we do need to intervene, our robots alert us and we raise a small pull request.
Stage 3: Adjusted
Adjust for timing differences.
Information required: Details about the actual timing of income and expenses different from cash flows
Assurance provided:
Company-level income statement reflects reality on an accrual basis
Company-level balance sheets reflect reality
What can we use this for?:
Complete income statement analysis for any particular period
Company-level balance sheets analysis at any point in time
How often does this information arrive?: As and when they come, but might not be complete until a monthly manual review
After we have recorded income and expenses, we need to adjust entries for which the date of the income or expense is different from the resultant cash flows. This can arise in cases of trade payables, cash flows in transit, or investment transactions that require some time to settle.
In this case, LedgerPigeon creates the appropriate receivable, payable, or suspense account to ensure that the journal is balanced daily. This has no final effect on the books unless the cash flow and expense straddle the accounting reporting period.
Stage 4: Eliminated
Recognise and eliminate intercompany transactions.
Information required: Details about the accounting treatment of intercompany transfers
Assurance provided: Consolidated financial statements reflect reality without double counting
What can we use this for?: Consolidated financial statements analysis, ignoring foreign exchange movements
How often does this information arrive?: As and when they come, but might not be complete until a monthly manual review
Intercompany transactions are a little complicated because entities may not book their sides of the transaction at the same time. Here’s an example for an intercompany loan:
While this may end up with the same end result (in that the subsidiary and parent have an intercompany loan and cash is transferred), it’s important to do it in this order so that we can have consistent books in the period in between loan signing, parent remittance, and subsidiary receipt.
For eliminations, since all subsidiaries are fully owned, it is simply cancelling out all transactions with the counterparty metadata set to a group entity.
Stage 5: Closed
Remeasure, summarise income, and translate.
Information required: All previous steps are completed
Assurance provided: Consolidated financial statements reflect changes in foreign exchange rates
What can we use this for?: Audit and statutory reporting
How often does this information arrive?: Required to be done at least annually
The “closing” part of the process is not different from how it would be done traditionally, because it only relies on data that is present in the ledger pre-closing, so it would be a mechanical process.
In this part, we:
Summarise income and book them into retained earnings.
Remeasure all monetary items in company-level balance sheets and book them in P&L.
Remeasure translation gains or losses from subsidiaries and book them in OCI.
After the books reach this stage of completion (usually after a month), they are ready for annual audit and compliance procedures, but we will have used parts of the information in prior steps for more time-critical objectives.
Improvements
I think there’s still a lot we can do to further reduce the toil of producing portfolio data and financial statements. There’s a lot to explore in the realm of:
What role could LLMs play in categorising novel transactions, where simple regex is not sufficient to handle? Could an agent perform bank or receivables reconciliation, especially where there is some fudge factor in timing and amounts?
Process development: make it such that we don’t create debt from the initial stages of the process, such as attaching data to the cash flow as we make the payments in the bank portals.
Since we’re continually iterating on this process, we’d be curious to exchange notes with others working through similar challenges. If you know us already, feel free to get in touch directly. If not, drop us a note at hello@titaniumbirch.com.
Further reading
Re-sharing the links shared above:
