By Haydn Palliser | October 14, 2020
Sure, there are some great Excel add-ins that help modelers with efficiency, analysis of data, and navigating around models, but the ability to compare between versions of a model tops the list for me.
Most companies maintain the good practice of comparing large legal documents in Microsoft Word to review what changes have been made between versions (called blacklining). So how do organizations not have the ability to compare versions of Excel spreadsheets?
If you care at all about risk, you must have the functionality to compare versions of spreadsheets.
The situation – late night “quick” changes to a model
Your financial modeler makes last minute, late night changes to a financial model. Critical analysis is required for an important meeting first thing in the morning. Early in the AM you receive a new model with a list of the changes made and a set of new outputs.
The problem – what on earth did you change??
You need to know that the changes made to the model are:
- The only changes made
- Correct
I live by the mantra of ‘trust but verify’, the saying perhaps made most famous (in English) by Ronald Reagan. Yes, perhaps the saying is a contradiction with the word “but”, BUT when results matter it is appropriate.
I have been the modeler making last minute changes late at night. I know a lack of sleep and urgency creates an environment that only exacerbates errors. I have made those errors. Trust but verify!
The solution – run a comparison software
A list of changes prepared by an analyst can only be trusted so far as you can verify them. This is the reason we need a tool that can check what was actually changed in the model (called ‘running comparisons’). We can only check the changes once we know what they are.
Several tools in the market can compare two versions of an Excel spreadsheet. The tools check which formulas were changed as well as how they were changed. They also check how the outputs of the model varied between versions.
Come on, enough background. Show us an example!
I have demonstrated below the main functionality of comparisons using Spreadsheet Advantage. This is the tool I have used the most, developed by Joseph Lau and Douglas Murray. Joseph also happens to be the ‘World Champion Financial Modeler’. It is not a title that has made him a fortune, or granted him fame, but he certainly deserves some respect when it comes to financial modeling. I also know him as an excellent project financier in Australia. Doug Murray is no slouch himself, having led more financial modeling and model review engagements than almost anyone in the world.
The initial model
Here is the world’s simplest example…
The project has $80m of revenue and $25m of costs for a period of 4 years (shown with a flag in row 12). We have calculated Cash Available for Debt Service (CADS) beneath that, equal to Revenue less costs.
The modified model
We can visually inspect this model, and see there are some differences, mainly because this is a simple model. However, that is both slow and dangerous so let us turn to a comparison report.
Comparison report (calculations and inputs)
An extract of the comparison report (shown below) is comparing the calculations and inputs between the two versions of the model with the click of a button. It is all consolidated in one place in a printable Excel spreadsheet.
The changes are:
- Cell C6 (input for revenue) was increased from $80m to $85m
- Cells F15:I15 were changed, by removing the multiplication of the row to the flag in row 12.
- Cell J15 was initially copied across from column I15 (denoted by ”), but is now hardcoded as negative $25m.
You could see some of these changes, but you could not easily see that a formula was replaced by a hardcode in cell J15.
Whatever the changes to the model are, the benefit of having them listed out helps you determine if the changes should have been made at all.
I have seen modelers hardcode values over a cell in the model. I have seen modelers accidently change an input. We are human, we make mistakes. Trust but verify.
Can you also check how outputs changed between versions of the model?
Absolutely! Most comparison tools allow you to check for different values in a model, either across the whole model, on certain sheets (e.g. outputs sheets), or on a defined range of cells.
What’s not to love?
Want to learn more on Spreadsheet Advantage?
Learn more about comparisons and other functionality provided by Spreadsheet Advantage,
Share This Resource
