Everything I share with you on my blog is inspired by my own experiences, either exciting or frustrating. This one comes from a frustration I experienced recently reviewing a financial model.
Nowadays, most people working on financial models are familiar or have heard of the financial modeling best practices. To me the most important ones are:
- Make your models Flexible so that it can accommodate future changes (This is the F in “FAST” standards.
- Keep your financial model Simple (The S in “SMART” standards)
- Develop financial models that are Transparent ( The “T” in FAST standards)
Fortunately and due to the great work of people like Kenny Whitelaw–Jones from gridlines and Rickard Wärnelid from Mazars (previously Corality) and their teams, nowadays I receive more and more models that are well designed and structured. Having to work with a standard model saves me a lot of time and allows me to focus more on the inputs and helping the deal team to come up with better structures and policies and negotiate better deals instead of wasting weeks of work trying to simplify and beautify a badly structured model and sometimes rebuilding a model from scratch.
However, I sometimes see that the basic rules of financial modeling that I mentioned above are not applied to the macros included in the models. It has been my experience that model auditors are not as sensitive to the nonconformity of codes with the best practices.
In this post I want to mention the things that bothered me while reviewing and using other people’s macros and I came up with the below rules for myself.
For the sake of transparency, do not hard code inputs within the VBA code
Look at the below example extracted from a real project finance model. It’s a goal seek macro to set the tariff based on a target equity IRR. As you can see the modeler has hard-coded the target equity IRR within the code.
Why would you do that? 70% of people I know around me, even professionals who are users of Excel don’t even have the developer tab in their Excel ribbon. How do you expect them to change the target IRR? and the worst thing is that in that specific model, the goal seek macro was included in a copy and paste macro so the average user who doesn’t open the VBA editor doesn’t even know that there’s a goal seek that is keeping the equity IRR fixed. This is a real example of not being transparent with your users.
Instead, they could have included the target equity IRR in the Input sheet and indicate that the tariff is an output of the model and equity IRR is an input. The example below from my models shows how i apply the same in my models:
So don’t hard code within the code and include the inputs you need for your code in your worksheet; in the Input section and create a range name and use it in your macro.
Color code outputs of you macro differently so that they can be recognized from Inputs
Most of the time, the output of the macro will be a hard-coded figure. For example, when you are using a goal seek macro to set your price, the price will be hard-coded and fixed by the macro and the user might not understand if that hard-coded input can be changed or not. As the developer of the model, you should color code the outputs of your macro differently. In the below example, I have color coded cells that are the outcomes of a macro in blue background and in my guide sheet I mention that cells with the blue background are not inputs but are results of running a macro.
If you are using someone else’s macro, mention the reference
I consider myself as a user of VBA macros and not a developer. Most codes are already written and people are generous and share them for free online. So when I need to automate a task, I google it and copy and paste the code in my VBA editor within my model, make necessary changes if needed or most of the time, I use the code as is. So like any other digital products, codes are also the property of someone and the least we can do is to mention where we got the code from and acknowledge the work of the person who developed the macro. When citing within the code, the citation information could be placed as a comment above the code, as shows below:
Explain to the user what each macro in your model is doing
If you really want to be transparent when it comes to your macros, then I would suggest to apply the below 3 techniques:
- Put texts in your macro to provide information about the code. This is mainly for users who want to go and check your macro and even if they are not familiar with VBA, they should be able to understand the code easily so provide comments for each block in your code.
2. Include a worksheet within your model and list all macros included in your financial model and their functions.
3. I would go as far as including a message box within your macro that pops out when the user presses the button and explains what the macro will do and if the user is ok with the function, they can press ok and run it.
Avoid long running Macros
I don’t know if it ever happened to you but I have come across models that I was really scared to press the “Run Macro” button! because it was taking so long for the macro to run. So I would say just make sure to limit the number of iteration when you are trying to break a loop with a macro or doing goal seek so that if it goes to a loop, then it breaks after a limited number or after a specific time.
