We had a great question from one of our online course subscribers 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 model is 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.
