Adding actuals to a financial model

Author:

Ashish Yadav

Published:

17 Apr 2020

Comments:

0 Comments

Category:

What should I do when my starting point is a trial balance and a requirement to add actuals to a financial model?

We had a great question from one of our online course subscribers asking about thdesign 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: 

  1. Identify the key drivers of profit and cash at the heart of your business forecast. This will help you design the financial statements;
  2. Decide which lines you want to show in the financial statements – and for all 3 statements: income statement, balance sheet and cash flow;
  3. Work with a trial balance (“TB”) as your starting point. Set up a routine to bring the TB raw data into your model;
  4. 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 ledger code that existed historically but is not present in the current period);
  5. Aggregate / summarise the raw data to match the level of granularity required for the financial statements. We describe this as mapping to active lines;
  6. 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;
  7. Derive cash flow statement items from the balance sheet and income statement; and
  8. 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 I’d be pleased to speak with you or to arrange an on-screen demonstration.

 

Request a demo

 

Ashish Yadav
Ashish Yadav
Ashish Yadav joined F1F9 in April 2012 and is now part of the leadership team in F1F9's India office. He has worked on a wide range of modelling assignments across many sectors including real estate, recruitment services, power and financial services. He specialises in designing and creating Excel based solutions for optimising the performance of corporate finance functions.