đź“Š How to Roll forward a Financial Model
Refreshing a model with actuals is one of the most challenging parts of managing a forecast.
Why?
Because the Chart of Accounts frequently changes, oftentimes because a new account was added…
and if you’re not careful when you try to replace your new set of actuals with your existing set, you’ll have a disconnect in your data.
Here’s my approach for how to remove the headache…and you only need 2 formulas.
Refreshing a model with actuals is one of the most challenging parts of managing a forecast.
Why?
Because the Chart of Accounts frequently changes, oftentimes because a new account was added…
and if you’re not careful when you try to replace your new set of actuals with your existing set, you’ll have a disconnect in your data.
Here’s my approach for how to remove the headache…and you only need 2 formulas.
and if you really want to take things to the next level, you can transform your data using Power Query. This can be applied both to your forecasts as well as your dashboards (or any excel file).
Sometimes you won’t be able to avoid a change in the formatting of your source data. That’s where these next 2 formulas come into play.
Let’s start with a scenario where your model is refreshed with July actuals, and now it’s time to update with August actuals.
To illustrate, I’m going to copy our old and new data sets to be right next to each other…in reality, they will most likely be on separate tabs or separate workbooks.
Our first formula will be extremely simple - we are going to use an = sign to compare whether the old data matches exactly to the right data…like this
As you can see, once we get to Commission Expense, we have a discrepancy.
The task now is simple - add a new line in your source data, and repeat this until all lines match up identically.
when things finally match, you can copy over your new data set and everything will line up.
This method is good because it’s really simple and quick to implement - but what if you have 8 new line items out of a chart of accounts that spans 100+ lines long? This would be exhausting.
Which is why this next formula is my preferred approach.
Instead of comparing each line one by one, we can instantly compare all lines and see which ones are new by utilizing the COUNTIF function.
The COUNTIF function works as such:
So in this case we are going to count if the value in our new data set is in our original data set.
As you can see, all values that were found returned a 1, and all values that were not found returned a 0.
We can make this stand out more by attaching a “>0” at the end to return TRUE or FALSE.
Rolling forward models used to eat up my entire morning.
Every month, same drill. Export the data. Compare spreadsheets side by side. Hunt for new accounts. Check if formulas still work. Fix the ones that broke.
Hours gone. Every single month.
I remember sitting there thinking, "There has to be a better way."
So I built one.
That's exactly why we built the Roll Forward feature in Model Wiz.
Model Wiz is our SaaS tool that builds financial models and dashboards with just a few clicks. It connects directly to QuickBooks Online, pulls your data, and generates boardroom ready reports instantly.
And the Roll Forward feature? One click. The system finds new accounts, places them exactly where they belong, and keeps all your custom formulas intact.
No more spreadsheet archaeology. No more rebuilding from scratch.
What used to take half a day now takes seconds.
Don't trust me? Try it out yourself.
What has your experience been with refreshing financial models?
Share your experience by replying to this email.
Till next time!
Josh (Your CFO Guy)
Thanks for reading my article. My goal is to help you grow in your career with proper Finance and Accounting.
If we're not already connected on Social media, let's get connected and send me a message to say hi. I promise to respond
