Beyond Integrated Financial Statement Modelling Part II: Data Consolidation Modelling
Recently, we wrote a blog about staff modelling as an example of modelling that doesn’t rely on integrated financial statements or accounting knowledge. In our second instalment, we’ll outline another example: Data Consolidation Modelling.
This is an interesting topic, because it can (and our example will) move even further away from integrated financial statements.
The Benefits of Learning Data Consolidation Modelling
It can really help you develop your financial modelling skills because it will make you approach a problem that you haven’t trained for.
This is because most training is usually focused on integrated financial statements. Modellers learn the best practices, the accounting and finance knowledge, and get very good at it, but it’s always within that same context.
This leads to new modellers trying to force different problems into what quickly becomes a very restrictive structure.
We’re going to look at a data transformation model. In this example, the client operated a chain of restaurants in London, and had sales data coming from a variety of sources, like their website and tills, as well as Deliveroo, Uber Eats and Just Eat orders.
All of this then needed to be consolidated, cleaned, and transformed before a report was uploaded into their cloud-based accounting system.
The client’s staff would routinely spend a lot of time manipulating the data and would sometimes make mistakes. The client had a finance analyst who had a lot of experience forecasting budgets and integrated financial statements in Excel, but had little exposure to modelling outside of that context.
The analyst was used to the framework of having a timeline across the top of each worksheet and initially tried to organise the different data sources across the top of the sheet.
This led to a lot of time lost as they tried to create consistent formulae across each row. It also led to formulae that were just too complicated, and their formula length was growing, which goes against best practice.
We suggested that they work on each batch of source data in distinct sections and consolidate at the end. This resulted in most of the calculations ended up being in a single column.
It looked odd, they felt, but of course it would feel that way, the model wasn’t a set of IFS, but it worked.
It’s just one example, but it helps drive home the point that every additional different project that you can take on, will make you a better modeller. Learn the best practices, but remember that they aren’t rigid rules, they’re guidelines.