Rock solid financial modeling with Modano and xltrail

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

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: Modano and 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 Time sheet:

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

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.

Conclusion

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.