Last updated on November 23, 2018
Rock solid financial modeling with Modano and xltrail
Posted by Felix Zumstein - Comments
While Microsoft Excel is used across all industries and for anything you can (and cannot) imagine, the financial modeling industry is definitely one of the industries with the highest dependency on Excel. Accordingly, they have come up with a lot of guidelines and tools to structure, format, audit and error-proof these financial models. After all, an error in a financial model can lead to a wrong outcome in a critical decision, like for example a loan approval.
In this blog post, we’re going to look at how some of our users are combining Modano, an Excel add-in for financial modeling, with xltrail, our solution for versioning and comparing Excel files, to create rock solid spreadsheet models.
Modano is an Excel add-in that helps you to easily follow best practices when working with Excel models. It handles tedious tasks like cell locking, allows you to painlessly introduce new sections in a consistent manner across all sheets (no risk to forget to adjust any formulas) and makes it a breeze to change the time horizon of your model. On top of that, it has auditing capabilities to identify things like unique formulas or cell errors. The free community edition can be downloaded here.
Our financial model
After installing the Modano add-in, the Excel ribbon has two additional tabs:
Build. Also, when right-clicking on cells, the context menu will have additional Modano action items, like inserting a new category.
In this blog post, we’re going to work with the example model that comes bundled with the community edition of Modano. Just click on the
Open Example Model in the Modano side bar that should open when you start Modano for the first time:
Extending the time horizon
Our first action is to change the
Term from 5 to 6 years and the
First Financial Year from 2018 to 2019 on the
After Modano has done its magic, we can look at the
Revenue and Expenses sheet for example, to see that the years have been updated correctly (we have already filled in some values for the additional year 2024):
But how can we see what exactly Modano has changed behind the scenes? This is where xltrail comes into play.
xltrail is a web based version control system for Excel workbooks (similar to GitHub, but for Excel workbooks). It creates an audit trail of your document changes and allows you to answer questions like: Who changed this value, when and why?
After committing the version with the changed time horizon on top of the initial template, xltrail shows us the following history:
Click on the commit message of the second version and xltrail will list all sheets that were modified by our simple time horizon change, which are indeed quite a few:
When we now, for example, click on
Rev_Exp_Out modified, we can see exactly what happened: Modano added the new column O and also changed all error checks to properly include the new column. Note that green means added/new, while red means deleted or old:
Financial model review
Because of the criticality of financial models, there is often a formal review process where an independent team would review the model, change or correct a few things and then send it back to the model author, usually by email. Now the author will spend some time to find and verify the changes, before adding new features and starting a new round of this back and forth review process.
xltrail allows you to send around a simple URL instead of the full document. It will also allow that both, the author and the reviewer, see what the other party changed.
Modano and xltrail play nicely together when developing financial models: Modano helps you to prevent and find spreadsheet errors through automation and auditing tools, while xltrail makes it easy to see what was changed, by whom and why, therefore keeping an audit trail and simplifying the model review process dramatically.