Many aspiring modellers think that they need to be proficient in macro building in order to become serious financial modellers. Whilst it’s not a bad idea to have a working knowledge of macros—and the language they are built in, Visual Basic for Applications (VBA)—it’s certainly not critical for a financial modeller to become a super VBA programmer. As we know, the best sort of financial model uses the simplest tools and introducing macros to a financial model brings a whole new level of complexity.
Before launching further into the pros and cons of including macros in your financial model, let’s have a quick overview of what macros are and how to build them. VBA in Excel is a huge topic in itself, and one I’m not planning on going into too deeply here. Once you start writing macros, it’s quite easy to get hooked, as you can do some truly amazing things in Excel, which will bring the functionality of your work to a whole new level. If you develop an interest in the subject, there are lots of books and training courses specifically focused on macros and VBA. Make sure that you buy a book or attend a course on VBA for Excel and its use in finance, as there is a lot of programming that is done in VBA that is not finance related.
What is a Macro, Anyway?
A macro is a collection of commands that are performed in a set order. A macro enables you to repeat operations that you would normally do by hand, but it is much faster and, when written correctly, much more reliable. Often, a macro will do in seconds what takes hours or days by hand. It can also perform tasks that are physically impossible manually. If you find you are performing the same commands or actions over and over again, in exactly the same sequence, you can create a macro to record all those actions for you. You can then assign the macro to a button and run it using a single click, or you can even assign the macro to a keyboard command.
Macros in Financial Modelling Case Studies
There are hundreds of situations where macros can save time and increase accuracy. They might not necessarily be built and saved within financial models, but they could be used in the collection of the data that goes into the model. Here is a small sample of a few of the most common problems in financial modelling that are solved by macros.
- A company is doing a salary comparison between different countries and wants to know how much they would need to pay in Malaysian ringgit so that their salary is the same as someone working in Singapore. If a worker in Malaysia earns the equivalent of 100,000 MYR after tax, how much would the company need to pay the worker before tax? The problem is that tax is calculated on pretax salary, so our formula gives us a circular reference. We can use a macro with a goal seek to repeatedly set the pretax salary to the amount entered by the user.
- Fifty identical budget templates have been created, and you discover a formula needs to be changed. Instead of making the change 50 times, record a macro. It will be much quicker and far less prone to error.
- A nonprofit organisation sets its pricing so that all costs are fully recovered to break even. The costs keep changing, though, and the user is a senior account manager who doesn’t know how to do a goal seek in Excel. The modeller creates a simple macro using a goal seek so that all the user needs to do is change the costs and press the button to find out how much the pricing needs to change under the new costing.
- A dump of information containing several thousand rows is exported from a database into Excel every day. The data needs to be formatted and manipulated manually to be used in a daily takings report. Automating this with a macro or Power Query can literally save hours of manual data manipulation.
- A reporting model is built using a PivotTable, but when new data is entered, the user sometimes forgets to refresh the PivotTable. The modeller builds a button to refresh a PivotTable every time it’s pressed.
Dangers and Pitfalls of Using Macros
Handy as they are, macros should always be used with caution. Quite often formulas or filtering will achieve the desired result, without the need for building a macro. Always try a formula or standard Excel solution before considering the inclusion of a macro in your model.
- Unless ranges are defined properly using relative cell references, macros don’t move their references the way that formulas do, and therefore named ranges are recommended when building macros. If any data has moved since the macro was written, the macro will refer to the wrong cell. A badly written macro can copy and paste over data, and you won’t know what it has done!
- Visual Basic is a complex programming language and a skill that many people don’t have. Therefore, many people are not able to edit or follow what a macro is doing—especially if it hasn’t been documented properly.
- Macros are time-consuming to write, and often a much quicker and more transparent solution can be built just as easily with a plain formula.
- Running macros requires security settings to be changed (as shown above), and if your model is used by other people, they may not realise they need to change their settings, causing the model to not work properly.
- Macro-enabled files need to be saved in .xlsm file format. If they are accidentally saved as a .xlsx macro-free workbook, the macros will be removed from the file. If this happens, the macros have been deleted, and you’ll need to either rewrite the code or copy it back in from a backup copy!
- Unless you will be maintaining the model yourself, most business Excel users have more than enough to do without also trying to learn how to program.
- If you’re the one running the macro each time the model needs to be updated, that’s fine, but what will happen after you’re gone? If the process stops working due to a macro problem, it can hold up monthly procedures significantly.
Basically, there are more things that can go wrong if a financial model contains macros. When it comes to building financial models, consider whether a VBA solution is absolutely necessary before including it in your model. When building a financial model, macros should not be used unless there is no other option to achieve the required result. Also consider that VBA code has not changed for many years and there are new tools such as Power Query, Power BI and Power Automate Microsoft is investing in. So before you think about learning VBA, consider your time might be better spent learning some of the Power Excel tools.
This article is an edited extract from the third edition of “Using Excel for Business and Financial Modelling” by Danielle Stein Fairhurst
