more-arw search

Q&A Forum

Using VBA Macros In Excel Financial Plan Model?

VBA Macros In ExcelI have been asked several times over the last few months as I interview for jobs where preparing financial planning models is part of the job description whether I use macros in the models I have built.  Although I know how to use macros and even know how write macros using the visual basic editor I don't use macros in my excel bottoms up models.  I can't see how a macro would add any "significant value" to the model except for maybe a macro in a goal seek scenario.  I don't consider vba macros in excel that help with formatting, printing, etc as adding "significant value" to the model.  I've asked a couple friends who do financial planning as their primary jobs and they have also said that they don't use macros either. 

I'm not sure why these folks have been so concerned about whether I use macros when building a financial plan model in excel.  Maybe they are confusing features like vlookup or pivot tables as macros?

If anyone can help clarify or provide insight to this it would very helpful. My friends and I are anxious to learn if we are leaving valuable macros out of our financial plans.

Thanks!

 

Answers

Topic Expert
Regis Quirin
Title: Director of Finance
Company: Gibney Anthony & Flaherty LLP
LinkedIn Profile
(Director of Finance, Gibney Anthony & Flaherty LLP) |

I agree with your experience. Macros may not make sense. For a while when I was speaking with individuals they asked about pivot tables, even though what we were discussing did not lend itself to pivoting. My recommendation, respond - "If macros improve the efficiency of the model I am producing, I use them. Excel provides multiple tools and I employ what I need to produce the best product."
Goodluck.

Sarah Jackson
Title: Associate Editor
Company: Proformative
(Associate Editor, Proformative) |

Good insight, Regis.

Anonymous, in addition to the excellent insights from the community on this page, you might also want to take a look at:

This treasure trove of free excel spreadsheets, free accounting spreadsheets, plus all kinds of other excel resources, such as tests, tutorials and more:

https://www.proformative.com/resources/free-accounting-spreadsheet

Plus, this free "Excel Shortcuts Cheat Sheet"

https://www.proformative.com/whitepapers/excel-shortcuts-cheat-sheet

Enjoy!

Best... Sarah

Peter Gascoyne
Title: Senior Analyst
Company: American Family Insurance
(Senior Analyst, American Family Insurance) |

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

Mark Matheny
Title: VP - FInancial Planning and Analysis
Company: Novolex (formerly Hilex Poly)
(VP - FInancial Planning and Analysis, Novolex (formerly Hilex Poly)) |

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.

Gabriel Krozkin
Title: Group Controller
Company: AWIN
(Group Controller, AWIN) |

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.

Miguel Nicolas Moreda
Title: Chief Financial Officer
Company: Wovenware
(Chief Financial Officer, Wovenware) |

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.

Peter Skalla
Title: CFO
Company: CFOwise
LinkedIn Profile
(CFO, CFOwise) |

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.

Robert Honeyman
Title: CFO
Company: Advanced Predictive Analytics
(CFO, Advanced Predictive Analytics) |

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. :)

david waltz
Title: Assistant Treasurer
Company: Integrys Energy Group
(Assistant Treasurer, Integrys Energy Group) |

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

Catherine Caton
Title: Staff Accountant
Company: C.P.A. candidate
(Staff Accountant, C.P.A. candidate) |

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!

(Agent, JKS Solutions, Inc.) |

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.

david waltz
Title: Assistant Treasurer
Company: Integrys Energy Group
(Assistant Treasurer, Integrys Energy Group) |

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.

Topic Expert
Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

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.

Topic Expert
Jaime Campbell
Title: Chief Financial Officer
Company: Tier One Services, LLC
(Chief Financial Officer, Tier One Services, LLC) |

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.

Mark Matheny
Title: VP - FInancial Planning and Analysis
Company: Novolex (formerly Hilex Poly)
(VP - FInancial Planning and Analysis, Novolex (formerly Hilex Poly)) |

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.

Topic Expert
Charley Kyd
Title: Founder
Company: ExcelUser
(Founder, ExcelUser) |

> "Do you REALLY want to have your organization's mission critical applications like Financial Planning and Budgeting dependent on excel?"

Yes, as a matter of fact, I do...

...With Excel, I can rely on Subject Matter Experts in my company, who understand how we do financial planning here, rather than on programmers and IT people who don't.

...With Excel, I can adapt quickly to the need for changes in our plan, which programmers and IT people never will.

> "Excel tends to be error-prone"

ALL software tends to be error-prone, Excel less than most.

Excel users do tend to be error-prone, but using the right techniques can reduce that problem significantly.

And there's a huge difference between implementing a complex, ever-changing planning process in a few days or weeks, compared to writing a generic planning system in a few months or years. Tight schedules and many changes tend to increase errors.

Also, Excel adapts easily to our needs; the generic system doesn't.

> "it presents challenges to consolidate"

Not really...particularly when we enhance Excel--not replace it--with Excel-friendly OLAP products like IBM's TM1, or PARIS Technologies' PowerOLAP.

And besides, the original question addressed financial planning and modeling, not budgeting.

> "it's not a database"

Actually, Excel adds more database-like features with every generation. Excel Tables in worksheets can contain more than one million rows of data. PowerPivot in Excel can contain many millions of records. And again, the Excel-friendly OLAP products give Excel access to a massive database that's VERY easy to consolidate, if I actually need those capabilities.

> "it certainly does not contain planning or budgeting intelligence"

Your canned planning and budgeting intelligence probably doesn't meet my unique requirements. But Excel allows me to set up the unique business intelligence that our company requires.

> (from the link...) "It does not check for errors..." "Excel does not have the flexibility to..." "Excel does not inherently understand..."

Most Excel users would laugh at these objections...

...Excel doesn't *do* anything, including checking for errors, until an Excel user tells it to. And when that happens, Excel can find errors that the other systems won't.

...Excel is the most-flexible tool there is for working with numbers. Seldom is Excel criticized for lacking flexibility.

...Excel doesn't inherently understand anything, but our Excel users do. And they understand those things as they apply to our company *much* better than do programmers in some software company, people who've never even heard of our company.

There may well be a place for planning and budgeting software in a large organization that's willing to adapt their processes to the software's requirements. But those organizations will take those actions not primarily for the reasons provided, and not necessarily with the software suggested.

Sandra Maxey
Title: VP Treasury and Planning, Asst. Corporat..
Company: Cobra Electronics
(VP Treasury and Planning, Asst. Corporate Secretary, Cobra Electronics) |

Sarah Jackson: I have read the white paper you referenced, and would like to print it for my boss. It says it is password protected. Anyway to get around this??

Sal Arena
Title: Sales Manager
Company: XLerant
(Sales Manager, XLerant) |

[Deleted; mis-read your comment.]

Jon Paul
Title: President
Company: Value Added Finance Resources
(President, Value Added Finance Resources) |

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
jonatvalueaddedfinance [dot] com

Jason Chroman
Title: Vice President Finance & Controller
Company: Tubular Labs
LinkedIn Profile
(Vice President Finance & Controller, Tubular Labs) |

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

Topic Expert
Christie Jahn
Title: CFO
Company: Prime Investments & Development
(CFO, Prime Investments & Development) |

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.

Maksym Dyma
Title: Business Modeling Program Manager (OTP B..
Company: OTP Bank
LinkedIn Profile
(Business Modeling Program Manager (OTP Bank), OTP Bank) |

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.

Raj Kribs
Title: Finance manager
Company: Self
(Finance manager, Self) |

I am a firm believer that application and creativity is more important than knowing features and formulae, I have come across many candidates who performs well during interviews but when it comes to application of features they fall short.

60040 views
Topics

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.

Excel @ Excel - 30 Courses Available