Mastering VBA: The Most Powerful Advanced Spreadsheet Tool for FP&A?

Among the first tasks undertaken each morning by a colleague, who for the purposes of this article we’ll call Andrew, is to produce a financial risk report to circulate to a long list of executives and other interested parties within our organisation. As is often the case in this type of situation, the content and formatting for this report reflect a diverse set of strong opinions, preferences and needs that have developed, evolved and been incorporated over a long period of time. With this witch’s brew of requirements, no ready-made report from a software solution is available or even cost-effectively possible. The result is that the ‘last mile’ of this process still involves the Excel spreadsheet. 

This is a common phenomenon in analytical work. At certain points in the process extreme flexibility, diversity, and customisation are required, and there is no better tool to handle this than the spreadsheet. This is one reason why the Association for Financial Professionals (AFP) included spreadsheets as an “extremely important” knowledge, skill and ability category for its forthcoming AFP financial planning and analysis (FP&A) certification.  

Unintended Consequence of New Tools

But Andrew’s story is not yet over. At some point in time the treasury group within Integrys held several ‘Get Familiar with VBA’ sessions over a one-month period. VBA stands for Visual Basic (a computer language) for Applications, and it is a feature of Microsoft Office products such as Excel, which makes it possible to create computer programmes and scripts that can be run within those products. 

Once our sessions concluded, a couple of analysts eager to try VBA convened with Andrew and I one day to observe his daily risk report process up close. Activities such as data acquisition from multiple sources, pasting raw content, linking cell references within the document, copying and pasting items for stress scenarios on other tabs, preparing for distribution, and double checking accuracy of existing cell references all conspired to create a 30 minute daily exercise.

Our small group then went away and discussed what we had observed, identified what items might be amenable to a programming solution, brainstormed what variables and curveballs we needed to contend with, and ultimately created the VBA code to achieve our aim: to reduce the time that Andrew spends on the daily risk report activity and turn it into less of a chore. 

The final result of our process was a new two-step version of Andrew’s spreadsheet that worked as follows: 

  • Paste data from the other sources into the worksheet.
  • Press the button we had created. 

And that was it – Andrew was done and no further action was necessary. The rest had all been automated, demonstrating the power of technology to aid FP&A procedures. 

What once took Andrew 30 minutes to accomplish could now be accomplished in five. Viewed a different way, 25 additional minutes per working day translates to 100 hours per year, the equivalent of 2.5 weeks of full-time work. This is more than some people get all year for vacation, and all achieved after about 10 hours spent by a few folks in front of a computer and whiteboard, designing a new procedure. 

What VBA Can Do

Andrew’s example is illustrative of how small improvements can translate into large savings by using VBA. While it does not create giant leaps in efficiency when the benefit is derived day in and day out, the results still add up over time. In other cases analysts will heavily use Excel in a very concentrated timeframe. For example, creating a valuation model for a complicated merger and acquisition (M&A) transaction can easily consume a full week’s worth of focused attention. In this setting VBA may save an analyst an hour here and there over the course of that week, by automating formatting, charting or possibly eliminating a large chunk of activity on some given afternoon during that week by automating scenario production. 

Whether preparing a daily report or working on a major project, mastering VBA can represent the difference between the analyst leaving the office at the same time as the dinner crowd or staying behind for an all-nighter. In addition to saving time, VBA provides analysts with a set of tools that they may not be able to acquire in any other manner.

Take Monte Carlo simulation, for example. It is simply impractical for an analyst to perform an operation in the spreadsheet, copy and paste the results, and then repeat this process 10,000 times. Even if they were fast enough to be able to accomplish this operation in one second, it would still be close to a three hour ordeal. 

Good analysts are valued for, among other things, their logical thought process, business acumen, ability to synthesise and transform data, and condense complex information into understandable chunks. Hitting the F9 key, copying and pasting the result mechanistically for three hours does not take advantage of any of those skills! 

However, thinking through an order of events, arranging them in the correct fashion, testing them at each step to verify expected results, and hunting down answers to ‘needle-in-a-haystack’ questions do take advantage of those skills, and these are some of the activities that are part of the code development process. VBA is a great match with the analysts’ skillset.

Why not start learning VBA today? You’ll be glad you did – just ask Andrew! 

263 views

Related reading