VBA, AI, and Excel - Paul Barnhurst’s Key Takeaways from 40 Episodes
In this special solo episode, host Paul Barnhurst, also known as "The FP&A Guy," reflects on key insights gathered from past episodes of Financial Modeler’s Corner. With over 40 episodes under his belt, Paul dives into recurring rapid-fire questions posed to expert financial modelers worldwide. From circular references to VBA use and Excel's future, this episode offers valuable wisdom on the art and science of financial modeling.
Paul Barnhurst, an accomplished financial modeling professional and host of the podcast, takes center stage. Paul is the founder of “The FP&A Guy” and has earned the prestigious Advanced Financial Modeler (AFM) certification from the Financial Modeling Institute (FMI). His wealth of experience and insights makes this solo episode both engaging and informative.
Key takeaways from this week's episode include:
The most common preferences of expert financial modelers on key technical decisions.
Why dynamic arrays and other Excel functions are becoming increasingly important, and the pace of their adoption.
Insights into how artificial intelligence (AI) could impact financial modeling and whether it will replace human modelers.
The importance of good design principles in financial models, as shared by leading financial modeling experts.
Fun and practical Excel shortcuts used by seasoned modelers to boost efficiency and accuracy.
Here are a few quotes from Paul in this episode:
"Circular references are dangerous because most people don’t really understand what’s going on, and it can lead to other errors in your model." - Paul Barnhurst
"If at all possible, don’t use VBA in your model. The less you use it, the simpler the model is." - Paul Barnhurst
"I’m of the belief that AI will build models for us. It’s a question of when, not if." - Paul Barnhurst
In this special solo episode, Paul Barnhurst takes a moment to reflect on the lessons learned from over 40 episodes of Financial Modeler’s Corner. He shares key takeaways from the rapid-fire questions posed to some of the top financial modelers in the world, offering a wealth of advice on best practices, common pitfalls, and emerging trends in financial modeling. As always, he invites listeners to reach out with feedback, questions, and guest suggestions, reinforcing the show's commitment to fostering a global community of financial modeling enthusiasts.
Follow Paul:
Website - https://www.thefpandaguy.com
LinkedIn - https://www.linkedin.com/in/thefpandaguy
YouTube - https://www.youtube.com/@thefpaguy8376
Follow Financial Modeler's Corner
Newsletter - Subscribe on LinkedIn-https://www.linkedin.com/build-relation/newsletter-follow?entityUrn=7079020077076905984
Sign up for the Advanced Financial Modeler Accreditation Today and receive 15% off by using the special show code ‘Podcast’.
Visit www.fminstitute.com/podcast and use the code “Podcast” to save 15% when you register.
In today’s episode:
[01:09] - Introduction and background
[03:18] - Circular references in models
[04:55] - VBA in financial models
[06:45] - Dynamic arrays: the future of excel?
[16:50] - Will excel ever die?
[18:16] - AI and financial modeling
[20:31] - Financial models as corporate decision-making tools
[22:26] - Favorite excel shortcuts from top financial modelers
[30:34] - Closing thoughts and invitation to audience
Full Show Transcript
[00:01:09] Host: Paul Barnhurst: Welcome to Financial Modelers Corner. I am your host, Paul Barnhurst. This is a podcast where we talk all about the art and science of financial modeling with distinguished financial modelers from around the globe. The Financial Modelers Corner podcast is brought to you by the Financial Modeling Institute. FMI offers the most respected accreditations in financial modeling, and that is why I completed the Advanced Financial Modeler and encourage you to do the same. This episode is a special episode. We have no guest this week. I'm going to talk to you about some of the answers we've had to rapid fire questions, and just some of my thoughts about the show over the last year, we're over 40 episodes now. Top ten podcast globally according to listen notes.
[00:02:02] Host: Paul Barnhurst: And that's all because of you, the audience. I can't thank you enough for taking the time to listen to the show. I started The Financial Modelers Corner because I love talking to different guests, and I felt like financial modeling was a subject that didn't have much in the way of podcast, where we could really go deep into how to think about financial modeling. I've become a better financial modeler by doing the show, by taking the advanced financial modeler from FMI. Where I want to start is I just want to ask you, the audience, to reach out to contact me if you enjoy the show, to let me know subjects you want me to cover, guests you'd like to have on the show, and future questions I can ask of our guests. I'm always looking for new ideas. If you have any ideas of what you'd like to see, please let me know. Don't hesitate to contact me. You can contact me at thefpandaguy.com on LinkedIn. Or you can email me at pbarnhurst@thefpandaguy.com. Before I jump into specific episodes and just some thoughts, I want to go through the rapid fire questions, which is something we've expanded over the years. It started with just a few questions. Now we have about a dozen we ask our guests.
[00:03:18] Host: Paul Barnhurst: So let's go through the first one. This is one that is one of those that has the clearest winner. When I asked this question, circular or no circular references in models, I've asked that question 40 times and I've received 36 no's and four yeses. 90%. Sometimes I get asked by people, well, what should we do instead? And we've talked a little bit about that. The reason circular references are dangerous is most people don't really understand what's going on. I can't say I fully do, and it can lead to other errors in your model. The concern isn't just interest. It's the impact it can have on other areas. If you have circular references that you're not aware of. So there are many different solutions. Some people can solve it algebraically and will choose to do that. Some, like me, wherever possible, will just use the opening balance and not use the opening and ending. So it's not a circular reference. Some build a macro and there are other methods people use as well. The bottom line to this question is use circular references sparingly and make sure you really understand them. You run the risk of introducing errors into your model. I had a model, this is a few years back where someone else had built it. It had some circular references and the person, instead of finding them, just turned on iterative calculations. And there were times it caused some issues with the model.
[00:04:49] Host: Paul Barnhurst: Today I would have figured that out. I wouldn't have let the model run the way it did, but I've learned a lot there. The next question we ask is VBA or no VBA? This is one that I won't say is controversial, but there's a lot of differing opinions. There's not a clear cut winner here. I've had 20 people when it comes to VBA, 51% have said no or 20 people, 3% have said a limited yes, and that limited yes says use VBA for formatting only. You shouldn't use it in any other areas and 16% has said yes. The one area that's pretty clear, even for those that say yes, is creating user defined functions. Most people would say no to that. My view on VBA is, if at all possible, don't use it in your model. If you have to have it, then go ahead and use it. But the less you use it, the simpler the model is. Simpler to understand and follow. Anytime you add VBA, you increase the chances of the model breaking. Everybody has a different opinion on it, but that's my take. The next one horizontal or vertical model? This is total preference. There is no right answer at all here. There's really not a lot of risks between either method. When we say horizontal we're talking lots of sheets each schedule on their own sheet. When we're talking vertical, your schedules are stacked vertically on one sheet. Then there's also a hybrid where sometimes you might use a combination of both.
[00:06:19] Host: Paul Barnhurst: Maybe your three statements are all on one sheet, your schedules are on separate, or all your schedules are on one. You have a little bit of a mix. 50% have said horizontal. Vertical has been 47. One person said they were indifferent. We've had that question answered 36 times. Not a real exciting question. We'd love to know. Let me know if I should keep asking that one or if I should just skip it. Not much excitement to that answer. The next one is really interesting. Dynamic arrays 65% have said yes. 32% have said no. Of some of those who have said no, it's said it's not ready yet. Really interesting to see more and more yeses. Even most of those people that say yes may not be building fully dynamic models. The reality is dynamic arrays are here to stay. The question is how quick the adoption will be. You have people like Giles Male that think it's coming quickly. You have Nicholas and Cameron Hay who are already using it for all their modeling, for all their customers. You have Craig Hatmaker, who's building lambdas to handle corkscrews and other complications that dynamic arrays on their own don't handle well. There are certain type of formulas and layouts in your model that dynamic arrays really struggle with, and you need Lambda to complete that. You need those custom functions. My view on this is that more and more people are going to use dynamic arrays.
[00:07:48] Host: Paul Barnhurst: But much like Power Query, Power Pivot, Other new things in Excel. The adoption is going to continue to be very slow. I don't think we're rapidly moving toward all dynamic models. I do think the technology is there to do it, and that's going to continue to get better. And so we'll see more and more people implement it because there are some real benefits to having dynamic models. So I'm excited to see that develop and grow and more and more people use it. One interesting conversation I had with somebody who said they built a fully dynamic model. They had one where they weren't changing the dates. The dates were set for the model. What they did is they built a fully dynamic model, and then anywhere where there could be a manual input, they added a manual row so you could manually make adjustments and it just folded into the dynamic formulas when it some things, which I thought was a great way to manage that because I'd have people ask, well, how do I deal with manual inputs if the model is fully dynamic? I believe it was Mark Proctor who told me he built a model that way. So there's an innovative use case where you're allowing all those manual inputs. Because one of the challenges with dynamic arrays, as many of you will know, is you can't adjust any of the cells in your dynamic array.
[00:09:04] Host: Paul Barnhurst: It's set. It's one formula. So you can't do those manual overrides. Next question. This is another one that has a very clear winner much like circular references. External workbook links. In general, the answer is almost always no. I've had three people say yes. And funny enough, a couple of those people are heavy in FP&A, which doesn't surprise me. I used it there when I get really complex workbooks, and I might have a headcount model and a cost center model and a revenue model, all in different files, and I didn't build it in through Power Query. Today, I think I'd build that through Power Query. I've learned a lot, and my view is use them only if you absolutely have to. Try to avoid it. I still think of the interview we had with Randy, who tells about how he had 40 something work book links, and some of them went seven deep in the files. And I think, what a nightmare. I would say hardcode your numbers and say where they're coming from before using workbook links. So I'm definitely on the no side with most of the people here. Be very careful if you're going to use them, but there are definitely some who say, hey, go ahead and use them. Next one is named ranges. I thought this would be a little more controversial when I did it. It has an overwhelming majority saying yes, go ahead and use them 76%.
[00:10:34] Host: Paul Barnhurst: But there's usually a caveat. If you're going to use them, use them sparingly. Don't go overboard with named ranges. A lot of people say use them for single inputs and your assumptions. Maybe you have a month or you have an interest rate or an inflation, and maybe you'll create named ranges for those cells to use in your model. Either way, you know, 25% of people say, no, don't do it at all. 76% roughly say yes. The next one we have here is formal modeling standards. And that one's about 60-40, 60% say no, they don't use a formal modeling standard. 40% say yes. One of the answers I liked was from Jim earlier. Diarmuid early. Lebron James of financial modeling. Two time world champion. He goes, my dirty little secret is I don't know any of the standards, but I'm going to assume I probably follow them. If you're using good design principles, you follow a lot of those standards. I'd say some of my issues with the standards, sometimes they're just too long. I think one of them is nearly a hundred pages. They can be very prescriptive at times. That's why I'm a big fan of following guidelines code recommendations, making sure you're doing things like no hard coding, clear colors for different types of inputs, whether it's an input or a formula or a calculation, the different things in your spreadsheet, you have your assumptions clearly marked, and in a certain area you have error checks.
[00:12:11] Host: Paul Barnhurst: If you're following good guidelines and you're designing your model with a good layout, you're going to be okay. You don't have to follow a formal standard. Yes, it may be good, and it's good for work to have some standards. You have some people who've talked about that. Some of the recommendations have been agree on what you're going to do in a model, what color coding you're going to use, how you're going to lay out your assumptions, maybe executive summary of what that should look like. Table of contents. That way everybody's having a similar flow, but there's still that ability to bring some of your own personality into this, because at the end of the day, as Jeff Robinson said, every modeler has their own fingerprint and you can quickly tell often who's built a model. It says, I can tell sometimes if I train that modeler when I see their model for review, because he had trained thousands of people, because they have some of the similar things to how I build a model. Everybody has their own style. There's an incredible amount of art, despite all the science that goes into financial modeling. The next question, and this is a fun one. One of my favorite is asking people what their favorite lookup function is, and I think we've had about eight different answers far and away. Number one is index match or index ex match, about 40% of people.
[00:13:39] Host: Paul Barnhurst: And what I find there is many of these people or people who've been in modeling a long time. And so X woke up came along later, which is number two at 11, 28%. What's really interesting is when I ask FP&A people, not financial modelers, what their favorite function is, it was often Vlookup ahead of index match, and xlookup. I think some of that has to be due to when you started and how far you got along in your modeling, because Vlookup is where we start our lookup when you first learn it, like, wow, I can do this, then you learn index match, you're like, I can do even more. Then if you're following modern Excel, a lot of people will switch to Xlookup. So between Xlookup Index Match and Vlookup, we cover 90%. The rest is we've had one person say choose. We've had another person say offset. One of my favorite answers is use whatever works, I don't care, just do the job right. Another person told me he has an identity crisis. He's torn right now. That was Derek Baker. Whether I should use Xlookup or index match. She let me know he has an identity crisis on that. So the bottom line is there isn't one right way to do lookups. There's a number of different ways it can be done and do what works for you. As I always like to say, and I've shared this a lot on LinkedIn because there's so many battles.
[00:15:10] Host: Paul Barnhurst: Xlookup, Vlookup, index match. Use this one because it's superior. Use that one because it's superior. The reality is they can all work in most cases. It's about understanding the pros and cons, whether you should use xlookup or an offset, or a choose or lookup or digit or Power Query. The bottom line is learn how they work. So that's a fun one to ask. We definitely, you know, have two dominant answers in and xlookup and index match and then everything else.
[00:15:45] FP&A guy here and as you know, I am very passionate about financial modeling and the Financial Modeling Institute's mission. I have been a huge fan of the FMI for years, and I was super excited when they decided to sponsor the Financial Modelers Corner. I recently completed the Advanced Financial Modeler certification and loved the entire experience. It was top notch from start to finish. I am a better modeler today for having completed the certification. I strongly believe every modeler needs to demonstrate they are a qualified financial modeler, and one of the best ways to do that is through the FMI program. Earning the accreditation will demonstrate to your current and future employers that you are serious about financial modeling. What are you waiting for? Visit www.fminstitute.com/podcast and use code Podcast to save 15% when you enroll in an accreditation today.
[00:16:50] Host: Paul Barnhurst: Next one. This is fairly recent that we started asking this, so we have an ask of as many guests as we have some of the other questions. We start asking them Will excel ever die? A lot of people say, not in my lifetime. I hope not or yes, but wait till after I'm dead. In fact, Chris Argent and Lance Rubin have a bet going and I think it's a few years away now. I don't remember when we said it, but I think it was within the next ten years. I think it's like 2030 that Excel will no longer be the dominant tool for finance. There'll be something else. And I believe they bet a bottle of whiskey. I was kind of the mediator on that one. So it'll be interesting to watch. But at this point I put my money on Lance. I don't think Excel is going anywhere. So, Chris, if you're listening, you probably are going to owe Lance a bottle of whiskey there in a few years. So what we've seen is 75% roughly have said, no, it's not going to die. It's here to stay. Excel is just going to be with us forever. About 23% have said yes. And then one really interesting answer. One of my favorite, again, Jeff Robinson said it should. His argument was if it doesn't, we're not making progress. We should advance so we can do things that Excel can't do today and go way beyond that. He goes, yeah, they may still call it Excel, but it will look completely different.
[00:18:13] Host: Paul Barnhurst: So quite a few different answers there. That's kind of a fun one. Next one we have is will AI build the models? This is a controversial question. A lot of people are like, I don't know if I like that question. Maybe the question is should it build the models or what part of the models? But in general, the answer is yes, it will build the models, but you still need the human to interpret it. Roughly 75% of people have said, yes, AI will build models for us. And if anyone's used the new preview FOR I or for, I think it's FORI, for ChatGPT that has reasoning. It's the first kind of version of this new project, strawberry, which is supposed to be able to do complex reasoning. It's pretty amazing what it can do. It's showing how far we've come. Other things that are taking place that are leading us toward AI getting there is Microsoft has built a spreadsheet LLM, to be able to read spreadsheets, particularly around processing and parsing and understanding the data in spreadsheets. Really exciting to think you can feed your spreadsheet, give you a list of those corrections, probably even make them for you someday. I am of the belief I'm on the side of yes, I do think the day will come when AI will build the models for us. I think it's a question of when, not if.
[00:19:39] Host: Paul Barnhurst: That being said, you'll still need human judgment. You still need to know how a model can be built and all the things that go into it. It doesn't mean you just press a button. You sit back and you're done building the model should not be the majority of your work. It's making good assumptions. Talking to the business. We hear that again and again as we ask people about modeling and they'll say, you know, maybe 50% or 25% of your time is spent in Excel. A lot of your time is spent with the business, understanding what you're doing, walking them through the model, prototyping it, and understanding the assumptions. Those are all critical. Telling the story. There's a lot that goes into modeling beyond just Excel. And then a fun one we've added is: Is financial models or are financial models the number one corporate decision making tool? I love this question. I asked this question on LinkedIn probably a year ago now, maybe even more and over 700 people responded. 90% said yes. It is the number one corporate decision making tool. The numbers are a little lower here. Roughly 60% have said yes and around 40% have said no. We had one person who said probably we had one who said no. And the number one answer is storytelling, which I found interesting. The corporate decision making tool is how you can tell that story. That story could be told from a financial model.
[00:21:25] Host: Paul Barnhurst: So it doesn't exclude modeling, but it goes beyond it. The next one I thought this was a really interesting answer from one of our guests. He said no. The decision making tool is humans. It's not any application we use, it's the actual people. Again, I thought that was a really unique answer. I really enjoyed that one. My favorite fun answer that somebody said no. And I've seen this twice. Now it's politics. Sadly, that probably is true in many cases. And then the last one is it's a combo of things you need more than just the model. So no, it is not the number one decision making tool. Let me know in the comments. You can comment on Spotify. You can comment on my website. You can contact me. Let me know what you think. I'd love to know any of these questions we've asked so far. Where do you stand? What's your view on these? How do you think about them? And then a fairly new question we started asking people is what's their favorite shortcut key and what's amazing is the diversity of answers. I think we've had like 15 different answers. We've only had a few people that have said the same thing. One is control T that builds tables. Three people have said that's their favorite. I love that answer as I'm a huge believer that we should be using tables. You know, beyond that, some of the other answers we've received are, you know, some have been as simple as F11 or F2 and F4.
[00:23:07] Host: Paul Barnhurst: Other people have been like it's control, right. Plus control D for down. Control, backslash for testing formulas and seeing if they're consistent across the row. We've had alt h o w, control C plus alt es. Some have said hey, I have my own custom shortcuts alt ESV. So pay special values. Control shift V, which is another version of that. And so there's a lot of different ones in there. One that I think is really great for those who have to audit a lot of models, is the control left bracket, takes you to where the first part of your formula is, and then you can do F5 enter to go back. So it's a great way to bounce back and forth between sheets. I think there's a couple trends in here. Control T obviously for your table, F2 and F4. And then beyond that is some kind of paste control C control v alt es, alt esv, control shift V. And then beyond that is usually something to help you understand formulas, whether it's control left bracket F5 and enter control plus backslash, whatever it might be. So they usually fall into a few groups on shortcuts. There's really not that many shortcuts you need to know to be a lot more efficient. Yes, you can learn hundreds and you can be part of the no Mouse Club, as some people put it, and that will make you faster.
[00:24:34] Host: Paul Barnhurst: But I am not a no mouse club. I've got a lot better in my shortcuts. And if that's what you want to do, great. And if you're building a ton of models where you need to be really fast, then I recommend learning the shortcuts. Or if you're going to do AFM, I'd recommend learning the shortcuts. So that covers the rapid fire questions. Next, I just want to talk a little bit about AFM. I get asked all the time about the advanced financial modeling. Hey, should I take it? Why should I take it? If you want to demonstrate your modeling skills and you have solid three statement modeling skills, I highly recommend taking the Advanced Financial Modeler. I think the content they have is top notch. The test, the program, the team. There's a reason I've sponsored with them. If you ever have questions about it, you can reach out to me. There's lots of other wonderful resources for training. Giles Male does a great job with full stack modeling. We've had him on the show. Chris Riley does fabulous work by financial modeling education and many others. I have a Design Principles course myself on Thinkific that I share with people from time to time. My biggest point is make sure you learn good practices, good behavior in your practice. Because as you all know, we start every episode with tell me that horror story.
[00:25:58] Host: Paul Barnhurst: And we've had so many horror stories about terrible models, billion dollar mistakes where it doesn't balance because somebody wrote a formula wrong, or companies making decisions with just bad data or overly complex models. There's lots of different examples that people share, but what's clear is many have seen horrible models or built horrible models. It takes practice, it takes skill, it takes discipline to build a good model, but I believe the most important thing in that process, and my favorite quote, and it came from FP&A tomorrow, is from In Schönau. The director, you know, modeling design principles, it almost always comes back to design full stop. The number one problem, and he says it again and again, is poor design. People will call them and say, I have a problem with this or that's not working, and, you know, blah, blah. They'll be like, no, that's not your problem. Your problem is you designed it wrong. If you designed it right, it'd be easy to do. So spend more time on the design phase. Last, I just thought I'd talk about a few guests and kind of some fun. I've really had. You know, an episode I really enjoyed was episode nine The History of Financial Modeling, in particularly when Colin Human talks about using punch cards to build a three statement model. How fascinating. Dim early as he talks about the financial modeling competitions. The World Cup now used to be model of you know what it was like winning and how he's become a much better modeler by competing.
[00:27:36] Host: Paul Barnhurst: And I encourage people to compete. I hope to find more time to compete next year. I've competed some. Another fun one was Emily Williams. Emily Williams, who started as a mine engineer and then got into modeling and actually competed in FMWC and on ESPN. She competed in one of the battles and her story. Sam Severian. He was great. He shared some awesome advice from his days working as a modeler in telecommunications and what he's doing today. We had guests from the World Cup. We've had Lance Rubin, Derek Baker in Bennett, Roderick McKinley, which was a great one on Bitcoin. Derek Baker was great. It was great for sass and talking about software as a service. Carolina Lago and her view on financial modeling and FP&A, as that's where she used it. Stephen Aldridge, who came on and talked about modeling standards and some of the work he's done there. Cameron and Nicholas Hay, building fully dynamic models. George Mount talking about analytics and Python and his journey from finance to analytics. Lindsey Weber came on and talked about quantrix and supply chain modeling, shared some fabulous advice about the importance of using both financial and supply chain models to make decisions, really understanding the holistic business. Your operational models. Ken Puls, the Godfather of Power Query, or as John Acampora calls him, his Jesus, his Power Query Jesus, that's Ken pulse.He came on and talked all about Power Query.
[00:29:20] Host: Paul Barnhurst: We had Chris Riley come on and do an episode all about how to build the three statement model. How he thinks about it. The process he goes through. It's a great episode to listen to. David Benaim. He came on and talked about Google Sheets and Excel, the differences between the two and how Google is better than Excel in a lot of areas, how it has more unique functions, how it's good at data entry and collaboration. And how he'll use both and how to think about them. You know, Adam Rakich came on and talked about data. How do you get your data out of different systems? Talk a little bit about API's and different ways to think about data and many other guests. You know, most recently we had Gilbert Hangartner on and he talked about how he uses Quantrix. He also shared how we don't do enough risk modeling and how it's easier than we think. Monte Carlo and other tools, we should really be using those. One of my favorite stories is he shared how he's taught Quantrix to his nine year old. Now 11 year old child. How he taught his first few children excel. And then he decided Quantrix was a better tool. And we've had many other people on. I'd love to know from each of you. What's your favorite episode? What's the guest you've most enjoyed? Is there somebody you'd like me to have back on the show? That's really what I wanted to cover this episode and just have a little fun and talk about what we've done so far in over 40 episodes.
[00:30:50] Host: Paul Barnhurst: We've had so many great guests, and if you want to be a guest, please reach out. If you have someone you think should be a guest, let me know. Give me their information. If you have a question you want me to ask. A subject. If you have an idea for a future panel discussion, LinkedIn live, I'd love to do another one of those. Thank you to each and every one of you for listening to the show, for making this one of the most popular financial modeling podcasts, and one of the most popular finance podcasts in the world. I couldn't do it without each of you and a wonderful sponsor we have: Financial Modeling Institute. Thanks again for listening to me as I ranted or talked or whatever you want to call it for 30 minutes here. I hope you enjoy this episode and I'll be signing off next week. We'll be bringing you another great guest for the show. Financial Modelers Corner was brought to you by the Financial Modeling Institute. This year I completed the Advanced Financial Modeler certification and it made me a better financial modeling. What are you waiting for? Visit FMI at www.fminstitute.com/podcast and use code Podcast to save 15% when you enroll in one of the accreditations today.