VBA, AI, and Excel - Paul Barnhurst’s Key Takeaways from 40 Episodes

Most people don't really understand what's going on. I can't say I fully do. I want to go through the rapid fire question, circular or no circular references, make sure you really understand them. You run the risk introducing errors into your model. There are many different solutions. Some people can solve it algebraically, some, like me, wherever possible. Anytime you add vba, you increase the chances of the model breaking. I don't think we're rapidly moving toward all dynamic models. One of the challenges with dynamic array is you can't adjust any of the cells. Don't go overboard with named ranges. You're following good guidelines and you're designing your model. Everybody has their own style. There's an incredible amount of art. Use this one because it's superior. Use that one because it's superior. The reality is they can all work in most cases. Will AI build the models in general? The answer is foreign. Welcome to Financial Modelers Corner where we discuss the art and science of financial modeling with your host, Paul Barnhurst. Financial Modelers Corner is sponsored by Financial Modeling Institute. 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 guests 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 10% podcast globally according to Listen notes and that's all because of you, the audience. I can't thank you enough for taking the time to 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 podcasts 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. So 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 atthefbnaguy.com on LinkedIn or you can email me at P. Barnhurst@the FPNA guy.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. So let's go through the first one. This is one that is one of those that has the clearest winner when I ask this question Circular or no circular references in models. I've asked that question 40 times and I've received 36 no's and 4 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 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 Mel 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 Hapmaker, who's building Lambdas to handle corkscrews and other complications that dynamic arrays on their own don't handle well. There's 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 more and more people are going to use dynamic arrays, 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 sum things, which I thought was a great way to manage that because I 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. It's, 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 some 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 build up 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 had with Randy who tells about how he had 40 something workbook links and some of them went seven deep in the files. And I think what a nightmare. I would say hard code your numbers and say where they're coming for 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%. 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 in 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 dim earlier. Give Me it 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. 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 100 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. Your fault. Following good guidelines and you're designing your model with a good, probably even make them for you someday. I am of this 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. 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 a 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 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, 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 our 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. 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 your comments and comment on Spotify. You can comment on my website. You can contact me. Let me know. Look up index match. Use this one because it's superior. Use that one because it's superior in 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 DK 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, an xlookup and index match and then everything else FP and a guy here and as you know, I am very passionate about financial modeling and in 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's 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. Next one and this is fairly recent that we started asking this, so we haven't asked it of as many guests as we have some of the other questions we started 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 10 years. I think it's like 20, 30 that Excel will no longer be the dominant tool for, you know, finance. There'll be something else. And believe they bet a bottle of whiskey. I was kind of the mediator on that one. Now, it would be interesting to watch, but at this point, I put my money on lamps. I don't think Excel's 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. I said, no, it's not going to die. 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. Let me know in the your 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 on 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, 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. Is. 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. Some have been as simple as F11 or F2 and F4. 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. Then you can do F5 enter to go back. It was 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 EE S 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 + 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 could be part of the no mouse club as some people put it, and that will make you faster. 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 Model. Hey, should I take it? Why should I take it? If you want to demonstrate your modeling skills and the 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 the 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 Mel does a great job with Full Stack Modeler. 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 and you practice. Because as you all know, we start every episode with Tell me that horror story. 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 FPA tomorrow is from in. In Schnorr, the director, you know, modeling design principles. It almost always comes back to design full stop. The number one problem and he sees it again and again is poor design. People will call him and say I have a problem with this or that's not working and you know, blah blah, blah blah blah. And he'll be like no, it'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. Kind of some fun I've really had now. An episode I really enjoyed was episode nine, the history of financial modeling. And 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 off. You know what it was like winning and how he's become a much better modeler by competing. And I encourage people to compete. I hope to find more time to compete next year. I competed some. Another fun one was Emil 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 Savarian 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 Grace was great for SaaS and talking about software as a service. Carolina Lago and her view on financial modeling and FP&A is 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 Pulse, the godfather of Power Query or as John Ankin Pora calls him, his Jesus. His Power Query Jesus, that's Ken Pulse. He came on and talked all about Power Query. 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 Benign he came on and talked about Google Sheets in 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. Adam Rekich came on and talked about data, how to get your data out of different systems, talk a little bit about APIs and different ways to think about data and many other guests. You know, most recently we had Gilbert Hengartner 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 9 year old, now 11 year old child, how he taught his first few children Excel and then he decided quantrix was a better tool. 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 is just have a little fun and talk about what we've done so far in over 40 episodes. We've had so many great guests and if you want to be a guest, you know, 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 podcast 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 Modeler's 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 modeler. What are you waiting for? Visit fmi@www.fminstitute.com podcast and use code podcast to save 15% when you enroll in one of the accreditations today.