How to Automate a Roll-forward in Your Forecast Model
For finance professionals who use Excel budget models: how do you “reel” in the years (or quarters or months) in your forecast model? When new actual data becomes available, is there a way to roll the model forward without doing a ton of work – i.e. without moving columns around and/or laboriously overwriting formulas with hardcodes?
This is an issue encountered by nearly everybody who maintains a rolling budget model. The answer? A “Historicals Repository”. Here are the broad steps needed to employ this technique:
Step 1: Add a new sheet to your model where your historical financial data will reside. As each new set of statements become available, simply enter the actual data into the column directly to the right of the most recent column of historicals. This area will be the “repository” of the model and will simply keep expanding to the right.
click to enlarge
Step 2: In the assumptions section of the model, duplicate all the model drivers in two sets:
- Set A: Assumption inputs (sales volumes, prices, etc.) that drive any forecast columns in the rolling budget
- Set B: Identical drivers that are “backed into” (i.e. reverse engineered) using the actuals from the repository
For example, if you are using a simple margin to generate EBITDA in the model, the assumption in Set A would be an input (i.e. 35.5% – note: make it blue font!). The value for Set B would be a calculation if that period is a historical one (i.e. actual EBITDA / actual Revenue).
click to enlarge
Step 3: In the model engine, wire it so that the drivers chosen to generate the financials (such as the EBITDA margin) are selected automatically based on whether the period is an actual or a forecast. You’ll need a simple mechanism in the model to denote which columns are historical vs. forecasts.
click to enlarge
If you have questions, let us know. We’ve employed technology along these lines for many of our consulting clients and saved them significant time in the budget / forecasting process. If this is done properly, to update the model the user should just need to load in the most recent set of actuals and then refresh the driver assumptions for any forecast periods – everything else should happen automatically.