We had a great question from one of our financial modelling online course subscribers are asking about the design of financial models with historical data at their heart, a.k.a. “actuals”.
“This is the bit I find daunting: when you start an assignment, you don’t always have a set of actuals ready for modelling. What you’ll have is access to a general ledger. Perhaps there will be a set of accounts with mappings showing how the GL accounts were constructed from the ledger. I want to get better at getting from that point to having a set of actuals ready for modelling.”
So how do you move from the general ledger to the Excel model? Here’s the advice we’d offer:
- Identify the key drivers of profit and cash at the heart of your business forecast. This will help you design the financial statements;
- Decide which lines you want to show in the financial statements – and for all 3 statements: income statement, balance sheet and cash flow;
- Work with a trial balance (“TB”) as your starting point. Set up a routine to bring the TB raw data into your model;
- Set up a separate store in your model for all TBs under the timeline. The idea here is that you should never delete anything you have previously introduced (for example a ledger code that existed historically but is not present in the current period);
- Aggregate / summarise the raw data to match the level of granularity required for the financial statements. We describe this as mapping to active lines;
- Complete active lines mapping for both balance sheet and income statement: balance sheet items can be taken straight from the latest TB, and income statement flows can be calculated by comparing the latest TB with the previous TB;
- Derive cash flow statement items from the balance sheet and income statement; and
- Link the results through to a dedicated Input_Actuals worksheet
You now have a complete set of actuals to work with. It’s a process that can be updated regularly so that the financial models are always driven off the latest set of actuals.
We have supported numerous clients in building actuals-based models and we’d be pleased to speak with you or to arrange an on-screen demonstration.
