Using VBA Macros In Excel Financial Plan Model?

I've been building financial and economic models for decades. Lotus macros used to be easy to build, and easy to deconstruct (& thus find errors when they occurred). I truly find the Visual Basic language a foreign language however, exceedingly difficult to work with and, more importantly, deconstruct. I have seen models built by others become of much less value once their macros stopped working and the poor end user wasn't sure how to fix them. I avoid them, like the plague. If Microsoft can create a language like Lotus, perhaps they can be of value. Otherwise, macros make the model only your model, and less likely a model that can be shared and used by others.

Peter Gascoyne
American Family Insurance

It sounds to me like interviewers who like to show off what they know about Excel. Or, maybe they are not interested in someone that just wants to play in Excel all day and they are looking for a "no" answer. Like Peter, I used them years ago in VisiCalc and Lotus. I see no value today.

I have been using Financial Models and Excel for years (and before Lotus and QuattroPro), and yes I have used Macros in MS Excel for financial modeling: i.e. extracting data from other system, automatizing the calculation process once the data is in the spreadsheet, etc.
I agree with the comment of Peter that can be difficult to work and fix Macros for the average user, but certainly can help and save time to the more proficient user.
Having said this, yes Marcros in MS Excel (particularly the complicated ones) are to be used for users with experience in Macros, an average user will likely not know how to fix a Macro if it is not working properly.
But definitely can find value for building Financial planning models.
As for the original topic of why the interviewers asked the question, I would be guessing, but maybe they thought that (even if they do not know how to write them/use them) someone else (the person being interviewed) perhaps know how to create/use Macros.
There are several books that show how to write good macros.

Macros are great tools for having your average-user put in raw data and produce a more sophisticated result on repetitive reports. The amount of time you devote to the planning and logic of the macro, taking into consideration the format and structure of the raw data, will produce dividends later when trying to delegate functions on the average-user to be performed daily, weekly, etc.

That said, I don't use macros. I tend to design my report templates using only formulas and specific cells for data input. I create a few colored/identified cells for data that can be entered quickly manually, or a full blank tab (spreadsheet) top copy/paste when it is a lot of data, or transactional data. If you do this and lock the cells, you eliminate need to build a macro on the vast majority of reports.

Macros are great to secure integrity and prevent damage of formulas from average-users... but they're not necessary when used amongst a group of power-users. Counter-intuitive to some extent.

I once made a graphing macro that was wonderfully useful in financial models. Hitting CTRL-G produced a graph from the cell pointer location to the last column on the right. It would automatically title the graph (from column A), number format the y axis to match format of data being graphed, and put the dates on the x axis. As a securities analyst I considered it a significant competitive advantage.

That was back in the Lotus days . . . sadly I never figured out VisualBasic well enough to replicate the functionality in Excel. But you've inspired me with the question and I just Googled a couple Excel macro developers to see if they can help me recreate.

It seems an odd question without some sort of context. Thinking about it, I'm guessing macros help when moving data from one system to another. Many years ago, I used to have macros designed to clean up and format ERP data dumps so that we could then import them into our budget/analytics model. I could also see using a file macro to assist individual managers with filling out budget templates. However, I'm inclined to think that once a company gets to a size where templates are distributed outside the finance department, you've outgrown Excel.

Over the past several years, I've been modeling for start-ups. No need for macros. However, a more interesting question would be, "Do you use iterations for your forecasting?" Hearing such a question would make me smile all day long. :)

Macros make Excel work easier in that they eliminate repetitive actions, making you more efficient in your efforts. Specifically for financial plan or valuation, I have used them for:

a) formatting,
b) running sensitivity analyses, and
c) for certain calculations,

For the following reasons:

a) Presentation-wise calculation look a little better if there is an extra row between the inputs and the total, but this row looks better if it is about half the height of a normal row, so programming a Ctrl+ command makes it easier to format this row height than going through the ribbon or right-clicking along the row

b) if you want to develop a tornado diagram, or assess your maximim and minimum value for all combinations of 5-10 input drivers at various levels (-5%, -10%, -15%, -20%, etc.) and collect relevant information from various parts of the model it is much easier to program this into a routine vs. running 100's of separate scenarios "by hand".

c) Values which rely on Goal Seek (which I use for IRR due to limitations in the standard Excel function) are more easily obtained via a control button tied to a macro than using Goal Seek each time.

Good luck on the interviews!

david k waltz

Just some insight...I see the importance of using a Macro for multiple what-ifs/number crunching. Thought I'd share mine on the more mundane tactics.

Currently, I'm temping for a bank's compliance dept. I'm using a Macro built some 6 years ago -- if it's lost no one can fix it. I'd like to tweak a few items, but I just looked at the Visual Basic detail for the first time, blah! We're using Macros for a simple task, formatting, so learning VisualBasic doesn't seem to warrant the time and headache -- it looks a bit like DOS.

However, with over 200 retirement plans, some w/over 20,000 line items, Macros using VisualBasics saves time and money. I have to run a number of reports from outside sources that dumps data (assets) into basic columns, sometimes using multiple rows for one line item. This information is generated into a financial report that is turned over to the auditors and given to the client or sent directly to the client.

The macro isn't doing much; switching columns around, setting width, adding color and separation of information. Am I glad we have a Macro, you bet!

Why I would want to know if you knew how to build a Macro:

1. I want you to know that Macros are used to reduce the time involved on repetitive tasks such as printing, page layout set up, establishing different print views. Hiding and Showing thousands of empty rows for building large accounting reconciliation models.

2. Telling me about how you build a Macro let's me know you understand something about VBA even if you are not a programmer. This means you are a higher level user.

3. Programmers spend a lot of time wasting time coding in code structures. For time reduction Macros, all you need to know is that you can Record your actions, and edit the code in the module. The only time you need to construct code structures is when you are building an event driven model, or when you are feeding Excel from a database ODBC data dump and the spreadsheet is more like a database table that gets accessed by many users, or has stored procedure code attached to it. Recording a Macro to reduce time is very valuable, but not always appropriate for all models. It just depends.

I see so many accountants wasting their time formatting all the sheets they want to print only to reformat them again and again. Why not learn how to record those steps once, edit that down to just the needed code for efficiency, and use the Macro over and over again. You can say cut 3 hours per month down to 10 minutes or less.

That is the value of a Macro. Programming module code for event driven applications is something different in total and not the subject of my answer.

Recording macros is a great way to introduce someone to the concept and get them up and running, but learning some of the language is important because the "record" process itself can introduce inefficiencies, such as as soon as a row or column are added or deleted in the target worksheet the recorded macro will no longer grab the correct cells.

Although this question was asked months ago, I just noticed it and thought a different perspective might be useful.

Back when I worked as a full-time Excel consultant, I occasionally was hired to make sense out of VBA code written by accountants who had left the department. As a rule, these macros were undocumented, poorly written, widely viewed as a necessary step in a critical process, and almost always unneeded.

Because of this experience, here are some ideas to consider...

...It's very common to spend ten hours to write a macro that will save ten minutes once a month. That's an ROI to avoid.

...Never trust a macro that's not heavily commented. This includes your own macros, because within several months your own code will look as though it was written by others, and you often won't understand or remember much of it.

...Agree that a macro never will be used in a department unless at least one other person in the department understands the code and can support it if the author should leave the department. Then include the names and phone numbers of both individuals in the comments within the code. By doing so, you improve the chances that somebody in your company will be able to support the macro if its author disappears.

...If a macro doesn't NEED to be written, it needs NOT to be written.

...Many macros are written to perform tasks that a better-designed model or report wouldn't need to be performed in the first place.

...Many long macros can be converted to short macros by making the right kind of changes to your model or report.

...Never write a macro that references cell addresses. Doing so guarantees many future errors. Instead, always reference range names. So if you don't use range names, don't write macros.

If the interviewer doesn't know a macro from a macaroon, then the question is being asked because a trusted person in the finance department uses macros and said, "Ask the candidate if he knows macros to see if he knows his stuff" or the interviewer looked up "advanced Excel skills" online and came across the term.

In general, more important than being skilled with macros is being skilled in elegantly solving the problems at hand - which may or may not necessitate the use of macros.

Occasionally, they'll need someone with that skillset because a macro was set up years ago and needs to be either fixed, maintained, or updated.

How you answer the question depends on why the question is being asked, the expertise of the interviewer, and how you want to position yourself.

Remember, this is not likely a technical interview but an "overall fit" interview.

I could see it as an issue if the company already has macro driven spreadsheets that need to be maintained/updated. However, I have not seen heavy use of macros for a number of years. I think pivot tables have be come a much bigger deal.

I have used macros for financial models in several ways:

1. Set up customized printing- so someone can just print summary parts of the model without printing the whole thing.
2. Allow users to toggle between customized views.
3. Most important of all- let users simulate key assumptions and then display table of different key assumptions and key results.

Macros can make a model much more powerful, but best when done with other best practices:

1. Key assumptions page with assumptions clearly spelled out. Even better- users can change the assumptions on that page. Include some key results right on that page so your user can see right away the impact of changing assumptions without having to move to different spots in the model.
2. Summary results page- so your user can see the big picture on one sheet.
3. Clearly designated where the user can change the inputs and which are formulas that they should not touch.
4. Flexible assumptions, rather than hard coded inputs, so the model can flex with changed assumptions, rather than requiring a lot of changes to manual inputs.
5. Using named ranges that make your formulas easier to follow.

Don't believe that macros are just for repetitive tasks or are hard to put together. Macros can make your models much more powerful for your users- and yourself!

I would be happy to answer any further questions.

Jon Paul
[email protected]

First, I would read what Charley Kyd says, above. Then read it again. He is spot on.

After that, I would add the following points:

1. Start with the basic premise that you generally try to avoid macros in financial models that will be shared with others, because it creates more to explain to the recipient and more things that can break.

2. Explain why people would use macros: If the excel user knows how to use macros properly, it can save an enormous amount of time.

3. Then demonstrate that you know the difference between the two basic kinds of macros: subroutines and functions.

- Subroutines - these are often used to perform a task on the on whole worksheet, like
setting it up for printing. I think it's bad form to use a subroutine to do any meaningful calculations, but they can be useful for automating repetitive tasks. Subroutines can be recorded.

- Functions - these are visual basic calls that you would perform in a cell. You would use these to do something that excel doesn't do natively. For example, if you wanted to sum all of the cells with bold formatting, you might want to write or find a function to do this. Functions cannot be recorded, but many examples are published and freely available.

Jason

This thread interested me as I have been viewing YouTube videos on VBA today. I have two P&L reports I want to merge together into one and was hoping learning about VBA could help with that. I get the concepts and can do basic recording, which is what the YouTube video's show. What they don't show is more complex recording, which is what I am trying to do. As a lot of people suggest in this thread it's not as easy as it seems.

Good day,
using Visual Basic in Excel is really Great think, but only if you can write it manually, i mean Programming (not by pressing "Record Macro" ))) ).

I've created Finance Models for Loans Portfolio Analysis & Forecasting, and they were created on VBA (it is impossible to reach the same analysis results using formulas or Pivot Tables).

VBA gives great possibilities in hands of VBA Programmer.
It is hard to list all positive sides of VBA.