Macros in FP&A

Hello everyone,

I am currently a first year FP&A analyst at a medium size company in the bay area. My current job duties consist of variance analysis on OpEx from our business operations, helping with the planning process by forecasting headcount and OpEx, and a variety of ad-hoc request my manager asks me to look into. I create most of my reports and analysis on large data dumps pulled from our Workday Financial system which contains Cost Centers and Ledger Account information.

My question is what Macros will help me save time and improve my efficiency in FP&A. I can do all the normal formulas that most Financial Analyst need, but I am looking for an edge to help me get promoted next year. I appreciate the responses!

Thank you

 

If you already know how to use macros, then use it to automate all the standard reports that you are responsible for. I started out as FP&A FA role 6 years ago for a large company (20B+ annual revenue) and we had to create variance reports for overhead departments. I knew excel VBA so I wrote a macro one weekend cut the time from half a day to 5 mins. The boss and business partners were happy and it freed up my time to work on bigger and better projects. And I got promoted a year later to Sr FA.

 

Do you mind explaining specifically what the macro did that allowed you to "cut the time from half a day to 5 mins"?

Also, you managed to progress from a FA to a Director within 6 years? That's insanely fast..

 

The process was...after month close, we had to run variance reports (vs. budget/last year) out of Oracle Essbase cube in excel and we sent those out to overhead BU owners. For most of overhead departments, each person has his/her own cost center so there were a few hundreds of them. So for each of them, I had to open the excel file, update the month, log into essbase, retrieve financials out of essbase cube, save it, and send the file. Too many repetitive steps that a robot can do, right?

I learned excel VBA in college so I knew there's a way to work smarter. After a little research, I found out that I can use macro to retrieve automatically out of essbase. So i created a macro that goes down the cost center master list. For each cost center, macro opened a new excel template, updated the month information and cost center, retrieved data out of essbase (username and pw were entered only once and automatically connected), saved the excel using cost center number and month, and emailed to the owner (macro looked up who the owner is in my master list). I even added an analysis later (macro looked for the largest variance and rank them into top 10). I hope that helps!

Yes, I was recently promoted to FP&A Director role. This is my 6th role in 6 years.

 

Good stuff- being a FA usually means you're an excel monkey, so any work that simplifies processes really makes you stand out.

I don't want to hijack this thread, but I'd love to hear about your career progression. What industry you started in, what your 6 roles were and if you changed companies or industries along the way. Your user name suggests you were born in 89 which makes you 5 years my senior. I'd love to be a manager in 5 years, let alone a director. Any career insights you might have would be greatly appreciated! Thanks

 
Best Response

Good observation on the username and thanks for making me feel old :) I'm in hospitality industry and I've been with the same company since my senior year internship. I first joined as corporate strategy intern and then came back as FP&A analyst after college for full time role.

Strategy Intern->FP&A Analyst->Sr. FP&A Analyst->Data analytic Mgr (Global Internal Audit)->Project Mgr (Global Transformation Project)->Sr. FP&A Mgr->FP&A Dir

I was in each role for less than 1.5 years. I get bored easily so I'm always looking for the next thing and that's led me to different challenges. I interviewed with a lot of outside companies during that time but the growth potential here is more valuable than extra 20-50k that outside is willing to offer. (I already have strong web of influence, reputation, and freedom here that are worth a lot more in long term...and the game gets harder as you move up).

As for career insights, you just have to do what feels right for you. It works out for me that I stay put with one company but you may have a different path. Know your strengths and position yourself. If you are just another excel expert within a team full of excel experts, then you are not going to stand out. But if you also have a good problem-solving skill, that's what you need to use to stand out. When I joined internal audit with a promotion, I quickly learned that it's the dead-end (no visibility to C-level, no room for growth) so I positioned myself to jump on the next opportunity...even though it was a lateral move.

A mentor of mine once asked me - "Do you want to be the smartest guy in the board room or do you want to be the guy who manages the smartest guy in the room?"

Keep grinding and you'll become a manager sooner than you think.

 

Thank you for the quick response! That really helps a lot. I'll spend some time working on Macro's this week. Are there any other books, articles, videos, classes you'd recommend? I'm switching over from a strategy role and am brushing up on my accounting with a coursera class. I'll also brush up on Training the Street and some other accounting books to make sure I can reduce the learning curve. Lastly, what're some things you'd recommend focusing on from the analysis side to make sure I stand out? I'd love to make an immediate impact and work on becoming the primary analyst on my team.

 

I learned Macros in college (I was an engineering undergrad then decided to be a finance guy after college) so I know how to do basic VBA. Once you know what you want to automate, most of the codes are already written and out there so you just need to search for it. I'm not an expert at all when it comes to macros.

To stand out, always work a level ahead. If you are FA, work like a SFA. Build the network and look for good mentors/role models within your organization that you can look up to. I was lucky enough to help big people who name-dropped me to bigger people within my company. Because when the crisis comes (it always comes in FP&A world) and CFO calls you at 10PM from Hong Kong or Singapore or wherever he/she is, you may be the one to save the day :-)

 

I'm not sure about macros but you can edit your enemies' autocorrect dictionaries to replace commonly used words that are horribly inappropriate for work. This will destroy the roadblocks to you ascending to your full potential when they get canned for cursing at the upper brass.

Just kidding! :D

Of course you can automate repetitive tasks that you find yourself doing often using the recording function. One thing to remember is that "undo" doesn't work with macros. So please take care to avoid accidentally making MORE work for yourself because of a faulty macro.

 

Learning and using macros and VBA is really a necessity today, it gives you a real edge over coworkers who don't know how to use it. If you want to take a bigger step forward in automating your work, you can learn C# and use Visual Studio to develop Excel add-ins or smart spreadsheets. C# programming in Visual Studio is a very user friendly and flexible way to develop tools that will help you in your work, and it's not that difficult to learn. I created several Excel add-ins for use in my work in ER- it both helped me in the analysis process and positioned my as a leading excel go to guy in my work.

Analystix.com Excel Add-ins for financial analysts
 

Occaecati esse quos sunt qui iusto. Nisi quaerat itaque nemo aperiam similique qui. Autem nihil veniam ut necessitatibus nihil deleniti. Eaque non sed ab vitae.

Delectus ut qui sint officia. Perspiciatis sit qui quibusdam amet adipisci omnis. Aut nulla non fugit nobis commodi placeat ut. Corporis porro vel consequatur. Dolor nemo qui eos iure laboriosam. Est pariatur inventore minus ullam quaerat.

Officiis et corrupti sit animi rerum et. Et dolorem corrupti est veniam sequi provident tempore. Fugit quis corrupti molestias ut sunt nam. Officiis non et minus neque dignissimos. Molestias ad ut iure ducimus. Quaerat et ipsam consequuntur expedita ipsam.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”