Anyone working on financial models knows that a model goes through a ton of revisions and different model versions will be circulated to different stakeholders. Every time a new model version is being circulated, the big question arises : What changes have been made to the financial model?
In my previous job as a financial modeler for lenders, 90% of the time I was reviewing other people’s financial model and 10% of the time, I was the author of the model. Every time I was receiving a new version of the model, I was using an Excel add-in to track the two files and highlight the differences. However, it was still a pain to understand exactly what has been modified in the new version eventually I had to do a overall check and a complete review of the new model version.
So, I decided to come up with a system. I added a sheet within the model that was recording the key inputs and outputs of the model and asking the modeler to keep it within the model. So in the next iteration, I could compare the key items and figure out teh main changes.
Now, it’s been couple of year that my main job is to build financial model and I still keep the same template in my own models. Every time I want to circulate a new model version for example let’s say I want to send version 01 then I save version 01 main inputs and output in a column as hard-coded figures. For the next iteration, I will again save the same parameters in another column. This simple system, enables the recipient to understand what were the main changes and their impact on the main project metrics. Here below is a snapshot of my template:
Last year, I received a comment from someone saying that although they appreciated the tracker sheet, however they thought it is not sufficient and they wanted to know in writing exactly what has been changed in the model with cell references. When I read the comment, I was furious! I couldn’t understand the logic! so I tried to convince them that the “Tracker” sheet is good enough and if they want, I can add a column next to the changes and add an explanation. They came back insisting that they need something more detailed. Then I gave up but I was still not convinced. so with a nagging mind, I inserted another sheet within the model which I labeled “Log” and I started documenting every single change that I was making from one model to another. Here is an example:
After I started including this sheet, I realized how useful that sheet is and I started applying it across all my models. Why?
- It made me to be more systematic when making changes to my financial models. for example, if I want to update a model, I list all the changes I want to apply and systematically apply all the changes and while doing them, I record them one by one in the Log sheet and also track the marginal impact of each change on the main project metrics.
- I can create waterfall charts to show the impact of changes applied on the model and compare the results across different model versions. Look the below example, anyone looking at the chart can easily understand what has been changed and what was the impact.
3. Even prior to including that sheet, I was listing the main changes from one model version to another in the body of my emails when sending a new models. So having this sheet, made it easier to draft the email. Just have to copy and paste that table from my excel to my email.
I really recommend that you also start using some kind of financial model version control system across model and check out my suggested template here:
How about you? Do you have a system for keeping track of different model versions? Let me know in the comment and if you found mine useful please share with your community.
Take care,
Hedieh
