Excel Hacks for Financial Modelers to Simplify Valuations and Save Hours with Charles Keepman
In this episode of Financial Modelers Corner, host Paul Barnhurst (aka The FP&A Guy) welcomes Charles Keepman, a seasoned financial modeler and valuation expert, to discuss the intricacies of financial modeling, best practices, and common pitfalls. They explore real-world scenarios, discuss the value of creating dynamic models, and delve into the art of valuing companies, focusing on how different industries and stages of business impact modeling strategies.
Charles Keepman has spent years honing his skills in financial modeling, starting at Ernst & Young and later working at Economics Partners (now Ryan Valuation). With deep expertise in business valuation, Charles currently applies his modeling skills to help clients make strategic financial decisions. His career trajectory has been shaped by his passion for creating efficient and dynamic financial models that help businesses make informed, data-driven decisions.
Expect to Learn:
Key principles for building auditable, easy-to-follow financial models.
Insights into business valuation: when to use different approaches like DCF, asset, and market-based methods.
The role of qualitative factors in valuation and how they can drastically influence outcomes.
The role of Excel and financial modeling software in today’s fast-paced financial world.
Why simplicity often trumps complexity in financial modeling, and how to strike the right balance.
Here are a few quotes from Charles Keepman:
"I think AI will have a profound effect on financial modeling, but we need to make sure it’s auditable and understandable."
"Financial models are great tools, but the true value lies in understanding the assumptions behind them."
"You can get stuck in analysis paralysis if you overcomplicate your models with too many variables."
From emphasizing the importance of simplicity and clear assumptions to his preference for using tools like XLOOKUP over complex formulas, Charles provides a wealth of practical advice. His perspective on AI in financial modeling highlights the balance between leveraging technology and maintaining human judgment, ensuring that models remain accurate and useful.
Follow Charles:
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 https://bit.ly/3AK5dYo and use the code “Podcast” to save 15% when you register.
In today’s episode:
[01:24] - Introduction to Charles Keepman
[02:03] - The "Worst Financial Model" Horror Story
[05:57] - A Rundown of Charles’ Career
[09:04] - The Favorite Part of Financial Modeling
[14:53] - The Most Unique Uses of Excel in Personal Life
[17:26] - Valuing a Company: The First Steps
[21:43] - The Most Unique Business Models
[23:41] - Excel vs. Deal Work
[27:03] - The Simplicity vs. Complexity Debate
[32:49] - Excel's Evolution: Favorite Features
[36:20] - Rapid Fire Segment
[45:27] - Final Advice and Closing
Full Show Transcript
[00:01:24] Host: Paul Barnhurst: Welcome to Financial Modelers Corner. I am your host, Paul Barnhurst, aka The FP&A Guy. 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 earlier this year. This week, I'm thrilled to be welcome on the show by Charles Keepman. And Charles, welcome to the show.
[00:02:00] Guest: Charles Keepman: Thank you. Excited to be here.
[00:02:02] Host: Paul Barnhurst: Yeah. Excited to have you. So first question for you. Tell me about that worst financial model. I know you have to have a horror story. Worst one you've seen or built or dealt with.
[00:02:12] Guest: Charles Keepman: Oh, man. I can't remember. You know the exact one necessarily. But I just remember whenever things were unorganized, it was really hard to figure out what was going on. Tons of hard coded cells, formulas not being, you know, organized in a good fashion. And as soon as you open those up, you just know that it might be more time fixing than building a new one.
[00:02:40] Host: Paul Barnhurst: Yes, I know exactly what you're talking about. You open them up and you start trying to figure them out and you're just like, okay, I could spend a week figuring this out and I'm still not. May not like the way it's built, or I can just start over.
[00:02:51] Guest: Charles Keepman: Exactly.
[00:02:52] Host: Paul Barnhurst: So what do you usually pick in that case, you typically starting over?
[00:02:56] Guest: Charles Keepman: I would usually rather start over.
[00:02:58] Host: Paul Barnhurst: I agree with you.
[00:02:59] Guest: Charles Keepman: Especially when you're working like it's hard when you're working for a client, especially because they may be really proud of it and not understand why you don't like it or whatever it may be.
[00:03:08] Host: Paul Barnhurst: But yeah, I know a guy who's like, yeah, they were pretty proud of this. And it had he said something like 50 workbook links and one of them was seven deep. So to get to the base file took seven different workbooks that they had linked through. And I'm just like, no, that's not continuing. But he said, yeah, they weren't too happy with them as he rebuilt the whole thing. And because they were proud of that, I'm like, oh no, no, no, no, that's a nightmare.
[00:03:36] Guest: Charles Keepman: Yeah, that's not ideal.
[00:03:37] Host: Paul Barnhurst: So what's been your main takeaway from those kind of horror stories? Those models that just are terrible.
[00:03:43] Guest: Charles Keepman: I think what I've learned the most is to follow best practices as much as possible, but also annotate when you deviate from it. Because I feel like building a model is a lot like the English language in the sense that there's a lot of rules, but probably just as many contradictions to those rules. And so as long as I can annotate somehow, whether it's, you know, highlighting, drawing a comment in or just putting a note off into the margin, I feel like that's the best way, because whenever you're building something, you always think, oh, I'm going to remember this. It's like when I set things down, I'm going to remember where I put this. But you never do. I never do. I should say.
[00:04:22] Host: Paul Barnhurst: I don't either. My wife's always having to find stuff for me.
[00:04:26] Guest: Charles Keepman: Yeah. So from that perspective, I just try to annotate as much as possible in the moment to save myself time in the future.
[00:04:33] Host: Paul Barnhurst: And what would be your top maybe 2 or 3 rules, kind of your guiding principles with the model?
[00:04:39] Guest: Charles Keepman: I think there needs to be organization. So, you know, when I started off my career at Ernst and Young and learned a ton from their modeling team, it was kind of like, you know, inputs, calculations, outputs. And if you just follow that Organization. You know, that's rule number one. Rule number two is make sure that all of your formulas are, you know, auditable. And I would rather in the beginning of my career, I always thought it was the right thing to just make the formula as complex as possible and be efficient with the space in Excel, but now I'm the opposite. I would rather create tons of helper rows or helper cells to get me to the ultimate conclusion. That way somebody can just spend way less time auditing it, you know, F2 ING it and figuring out what's going on.
[00:05:30] Host: Paul Barnhurst: It's amazing how early in our career we think complex is cool. You know, the longer you spend, you're like, just keep it simple. And the truth is, simple is often harder. You know, it's kind of funny, but that's why I say I say it all the time. I'm like, simple is hard. Complex is easy. Don't believe me? Try figuring out how to do a lookup table in a formula versus some nasty nested if the nested if is easier, but nobody wants to order that or deal with it.
[00:05:56] Guest: Charles Keepman: Exactly.
[00:05:57] Host: Paul Barnhurst: Tell our audience a little bit about your background. You know, kind of where you started your career, what you're doing today. Give us that, you know, 2 or 3 minute rundown.
[00:06:05] Guest: Charles Keepman: Yeah. So I started at Ernst and Young in their financial modeling group. It was an awesome experience, I loved it. In fact, we probably would have stayed longer had not. You know, a few things kind of happened in my personal life that made it made sense for us to move. But it was a great experience and I loved it. It was challenging from the perspective of I never felt like I was getting good at anything specific besides just learning more and more formulas and, you know, complex, you know, series of formulas, I should say. But it was a great experience. And then from there I went to Economics Partners, which is now known as Ryan Valuation. And so I'm mainly doing business valuations at this point. There's still a good amount of modeling from the perspective of we're building, you know, valuation models, but it's a lot more templatized as opposed to custom built from scratch or, you know, reviewing really complex big deal models. Like, you know, some of the things I used to do in the past.
[00:07:07] Host: Paul Barnhurst: Makes sense. That's helpful. So you've pretty much spent your whole career modeling, from what I can tell, right? Is that pretty fair statement?
[00:07:15] Guest: Charles Keepman: Yeah, I guess I mean, I wouldn't say that, you know, the modeling is not complex in what I'm dealing with now, but yeah, I spent every day in Excel for the last. Who knows how long.
[00:07:25] Host: Paul Barnhurst: Yeah. So did you always know you wanted to be involved in financial modeling and kind of what you're doing, or. When did you know? Hey, this is where I want to be. This is what I like doing.
[00:07:34] Guest: Charles Keepman: That's a great question. I think growing up, I always thought I would be in finance, given that my dad was in finance. And, you know, I learned a lot from him. I would intern for him over the summers when I had availability, and he was willing to take me in college and high school and things like that. But then when I went to college, I always thought I would go out and kind of prove myself and then come back and work for him. But I just realized that I really liked a lot more of the financial modeling side rather than just, you know, the lending side, which I still really enjoy that side. But he was more on the lending side. And so at EY, I really enjoyed working with all these different companies and then economic partners and valuation, which is the name of the company today. I've really enjoyed just learning about all these different types of businesses and modeling out kind of, you know, whether it's explicitly or kind of mentally in order to select the proper, you know, comparable companies. But I've really enjoyed figuring out how all these different companies work. And I think that's why, you know, I joined this company thinking I'd be here for a couple of years. And that's why I've been here for almost seven now, is because I've just really enjoyed it.
[00:08:48] Host: Paul Barnhurst: And, you know, if you find somewhere you enjoy, why move if you're happy? It meets your family's needs, they pay you appropriately and you feel challenged. And that's a move. That's the beauty of it, right?
[00:08:58] Guest: Charles Keepman: So yeah. So I feel really lucky from that perspective.
[00:09:01] Host: Paul Barnhurst: Yeah. That's great. So it's always nice when you find what you love to do. So I'm curious, what's your favorite part of modeling is that process of understanding different companies, putting the model together in Excel, or what is it that you really enjoy about it?
[00:09:13] Guest: Charles Keepman: I think the most enjoyable part is not necessarily what I do on a day to day basis, but it's laying out templates to create efficiencies and it's making things that are complex, really easy to understand. I mean, even in my personal life, there's a lot of times where I need to build things out just to be able to make a decision, whether it's, you know, buying a house or making an investment or whatever it may be. And I think the most enjoyable part is building something that's dynamic because, you know, growing up you had the calculator, right? And it was great. It's a great tool. There's one on your phone now, right? So you've got one constantly in your pocket, but it's not dynamic. And I think that's what really drew me towards models is the fact that you can make it dynamic such that you're in a conversation and you're, you know, trying to decide or somebody's trying to decide whether or not to invest in something. And they say, well, what if this happened? And the best part is that you've already theoretically figured out that potential question ahead of time. And you say, oh, yeah, well, if the, you know, if the discount rate on your discounted cash flow model goes from, you know, 20% down to 15%, here's how it affects the ultimate value. And just that, that ability to be dynamic is, I think, the magic to me of modeling.
[00:10:36] Host: Paul Barnhurst: That ability to kind of do those sensitivities, the scenarios, make those adjustments and see what will happen. Like when you create a table and you can be like, all right, if we can get EBITDA up by two, two more points, or it makes this much difference in value or whatever it may be. Or hey, if my loans this rate, I ain't ever paying this thing off or whatever the answer is.
[00:10:59] Guest: Charles Keepman: Yeah. Which we actually do that a lot, right? I mean, in our business we do a lot of compliance based valuations for financial reporting and also for tax purposes like trust or estate or whatever it may be related matters. And but there's a subset of our practice where we're just doing purely strategic, helping people kind of review alternatives of, well, what should I do with this business? Maybe they inherited it from their parents, or, you know, maybe they started it a long time ago and they've just been running it and it's always paid the bills. But they're trying to figure out, well, am I being a good steward of this business that either was given to me or I've created? And it's really fun to be able to, in those circumstances, show them the sensitivities, right. Like, hey, if you can prove that your company should be in the higher end of the range of an EBITDA multiple. And here's how. Maybe you could do that from a qualitative perspective. Here's what that means quantitatively.
[00:11:56] Host: Paul Barnhurst: If you can get a 12 multiple instead of a nine, that means, you know, 600,000 or whatever, whatever the number is 3 million. Or it's like, oh, wow. It makes a big difference because the qualitative is what helps determine where you get in that range.
[00:12:11] Guest: Charles Keepman: Yeah, absolutely. And that's something that, you know, in the beginning of my career, I think doing what I was doing, there was a lot more quantitative stuff. Right. We were dealing with large data sets. We were in Excel. Sometimes we were beyond Excel and Access or Power BI because there's just too many, you know, too much data. But it was all kind of quantitative. And now the quantitative stuff and evaluation is really not that difficult. You know, it's a multiple of EBITDA, multiple of revenue. Discounted cash flow can get a little bit more complex, but at the end of the day, it's really just a present value formula. And so there's a lot more, you know, cultural aspects and qualitative aspects that really have huge differentiating, you know, effects on the ultimate conclusion.
[00:13:04] Host: Paul Barnhurst: Yeah. I mean, at the end of the day, if all you're doing for evaluation, I'm careful to use the word all you're doing. I mean, I know it's work, but if you're just using comparables, you know that that's not near as complex as this big M&A that has a bunch of debt with it, trying to combine two companies and have all these people doing due diligence and all kinds of adjustments. Right. That's a whole different level than saying, okay, based on the qualitative based on everything you've given me, if you adjustments, here's where we're looking at. And it's really more that discussion around what does that mean and what's the difference between these numbers. How do you think about it versus trying to get that big, huge model to turn out what people want and then having the discussions around. We expected it to be valued around this. Can we make these adjustments? We can. Doesn't mean they're realistic. And then we've all been there where there's times very much realistic. But there are times you're like, I don't know that you can ever achieve those. And obviously if you can achieve them, here's your value. But price should challenge those assumptions a little more.
[00:14:07] Guest: Charles Keepman: You know, granted, there are some times where it's like, hey, if this is the result you really want, then here are the inputs that will get you there, right? And so that's important to know as well. And that's really where you know where the rubber meets the road. Sometimes it's knowing okay well what do I even have to do to potentially get there.
[00:14:24] Host: Paul Barnhurst: Yeah. And then charting out a path to actually get there I had one. It's kind of funny. My boss told me I wasn't there at the time. The merger ended up happening later, but there were a company, a couple companies that were being looked at, and I had a boss one time tell me, yeah, basically that deal was sprinkled with, what do you put it, unicorns and rainbows. It never happened. You can tell why. Because that was his words. Like that's how it was. I'm like, okay, yeah, good thing it didn't happen. If that was the case.
[00:14:52] Guest: Charles Keepman: Yeah. Oh, definitely.
[00:14:53] Host: Paul Barnhurst: So I'm curious, what's the you mentioned, you know, kind of using Excel in your personal life. What's the funniest or the most unique thing you've used Excel for in your personal life? You build any unique models or kind of fun things that you've done.
[00:15:06] Guest: Charles Keepman: I've bought like a couple of real estate properties, and so definitely using it from that perspective to benchmark against. Okay, well, if I were to just put this money in the market, how would it do versus, you know, what benefits would I get from investing in, you know, a rental property. So it's it's that and I guess the other thing that I was proud of recently is I had I had a, a line of credit and there was a couple of different projects that were all on the line of credit at once, and I had to figure out what portion of the balance, what portion of the interest was attributable to each project. When these projects were drawing down and paying off at different times. And so it's not necessarily that complex, but it's one of those things where, you know, if all the projects were just completely me, would it really matter for me to like it probably wouldn't make sense from an efficiency perspective for me to spend the time building it out and figuring out what's going where. But when you have a variable interest rate and multiple partners, it's crucial that when you go through that process, you have to do it right in order to make sure that you're being fair to, you know, all the other parties involved.
[00:16:20] Host: Paul Barnhurst: That makes sense. And as you were saying that I kind of had to laugh. I'm like, I can tell I have a hardcore finance guy is, you know, all the everything is kind of that finance and valuing it and home. And I had one guy tell me he built a bathroom tracker in Excel. Yeah, he was tracking along like, so it would go to the bathroom or something. Another one was he built something to track. There was a show and you start noticing. They swore a lot in it. So he built a tracker to count how many times it swore in the show. So yours are tame by comparison to some of the fun ones I've had.
[00:16:52] Guest: Charles Keepman: Oh. That's funny. Yeah, I guess I'm not.
[00:16:54] Host: Paul Barnhurst: Trying to beat a game show if I remember right.
[00:16:58] Guest: Charles Keepman: Yeah, maybe I need to get a little bit more adventurous.
[00:17:00] Host: Paul Barnhurst: So you need to get a little more creative in your Excel. You can come back in six months and tell us some, you know, crazy thing you built.
[00:17:06] Guest: Charles Keepman: Well, I've always tells me I need to focus more on my creativity. So this is this is the universe, you know, continuing in that direction.
[00:17:14] Host: Paul Barnhurst: Yeah. Just tell her she's right.
[00:17:15] Guest: Charles Keepman: I mean, she is.
[00:17:17] Host: Paul Barnhurst: Yeah, I was going to say, if you want to be happy, just tell her she's right. I'm going to get myself in trouble if my wife listens to this episode. All right, moving on to the next question. So when you first start, you know, to create a model valuing a company, where do you typically start kind of maybe talk a little bit about that process.
[00:17:34] Guest: Charles Keepman: Most companies are going to have some sort of template model right There, they're going to have already built out kind of their efficiencies. But assuming you didn't have one of those, I mean, I think the first place you start is really taking a step back and thinking, okay, well, how will this company be valued? And really, there's three main ways, theoretically, that a company is valued. You look at their assets, you look at and mark them to market like their balance sheet and just market to market. And you look at the comparable companies and figure out if some sort of multiple of revenue or EBITDA or, you know, price to earnings or whatever it might be, makes sense. And then you look at an income approach and ultimately, at the end of the day, the true value of something is what its future cash flows are going to produce. Right. And then discounted to today from the perspective that a dollar today is worth more than a dollar tomorrow. But the question is how much more right? And that's where your discount rate and time value of money comes into play. And people's preferences and all that kind of stuff. But I think that, you know, the first place you start is just trying to figure out, well, what type of business is this? How does this business trade in the marketplace? And, you know, where does it look like if it's a really early stage business? Well, it might not have any revenues yet. And so you're going to have to do some sort of cost or asset approach. And a lot of those assets probably aren't even on the balance sheet if it's a, you know, super stable company that has really consistent growth and margin characteristics, then, you know, an income approach probably makes the most sense. If it's a company that, you know, has some combination of, you know, growth and margin that's not extremely set in stone, well, maybe you're looking more so at market approach and trying to figure out how they compare to other companies on a go forward basis.
[00:19:36] Host: Paul Barnhurst: Yeah, it makes sense. I mean, obviously a lot depends on the company, the size, the stage, the industry, etc.. For you personally, is there a favorite method you like to use? Like saying your all else equal, you tend to like to do a DCF. Like if you were buying a company and you know any method could work. Is there one you would prefer that you like to use that you think is kind of your favorite?
[00:20:00] Guest: Charles Keepman: The nerd in me loves the discounted cash flow because.
[00:20:03] Host: Paul Barnhurst: I figured you were gonna say that.
[00:20:04] Guest: Charles Keepman: Right? But kind of like the person inside of me that craves the simplistic, like, easy to understand. I love the market approach because it's like buying a car, right? You figure out what you want and then you go look for it, and you compare it to all of the other cars, and you adjust for color and mileage and features and all that kind of stuff. It's the exact same with the business, right? You're looking at an industry. You're trying to figure out how comparable it is and the best way to compare it?
[00:20:37] Host: Paul Barnhurst: Yeah, I mean, it's kind of like when you buy a home, right? They do comparables this many square feet, this many bedrooms this big in the yard, this many bathrooms as a pool. It doesn't have a pool. You just go through a list, a bunch of things and say, okay, based on location and everything else, it should run you X, you're going to pay roughly this amount. And then you look at all the ones that are way outside that and you go, what happened? Why am I paying this?
[00:21:01] Guest: Charles Keepman: Totally.
[00:21:03] Host: Paul Barnhurst: Yeah. Do you have a favorite industry? Like, is there a particular industry you like to work in or model?
[00:21:09] Guest: Charles Keepman: I think the reason why I've been here so long, I mean, I say so long, but the longer than I thought I would is because there's not an industry that I'm partial to necessarily, but the fact that I'm constantly seeing these unique businesses across multiple industries, I think that's what really is exciting to me, is understanding how each industry and each kind of business model would command different types of desire by folks purchasing them.
[00:21:43] Host: Paul Barnhurst: What's kind of the maybe most unique business you've seen? Like one that you really like, wow, that's different. I would never thought of, you know, that being a business, have you seen one that maybe kind of jumps out to you?
[00:21:56] Guest: Charles Keepman: Oh for sure. I mean, I feel like it's on a monthly basis. Where here in the office, there's some wild company that, you know, we're so surprised by. I think there was one that was trying to rent silverware on like a subscription basis. I thought that was pretty interesting. And I think the more interesting piece of this is not the crazy businesses we see. It's the ones that actually succeed and the ones that don't. It's not always what you would imagine. And I think that's where the real, the real kind of interest occurs. It's like there's no way that going to work. And then all of a sudden they IPO for some crazy price or. Yeah. Or like, oh yeah, that'll totally work. And then they go under it and it's I mean, yeah, maybe there's a reason I'm not in venture.
[00:22:47] Host: Paul Barnhurst: I remember I did an entrepreneurship undergrad and we had this class where the teacher had us work with companies that had come to him putting together some valuation stuff. I can't remember what the document was called now or how old I am. Yeah, this is 25 years ago, and I remember we had one of them and I missed the meeting because it was the teacher and the other person could only meet at the time, and we all thought it was a bad idea. And we told them as much, and they ended up raising the money they wanted. The last I knew they were doing very well. I'm like, that was what I know. Not a whole lot.
[00:23:17] Guest: Charles Keepman: Exactly.
[00:23:18] Host: Paul Barnhurst: So you just never know. I mean, I talked to a lot of startups around FP&A software, and the one thing I've learned is just how important the team is. So much of it is, you know, great founder over great technology. Give me the great founder, Great management team any day now. Obviously I want both and I want it at a low price, but that's usually not a reality, right? Of course, you know, I'm curious on average, how much time do you spend kind of working in Excel versus working on a deal?
[00:23:47] Guest: Charles Keepman: What do you mean by in a deal or on a deal?
[00:23:50] Host: Paul Barnhurst: Like talking to people, working with the business, not working.
[00:23:53] Guest: Charles Keepman: Oh I see what you're saying.
[00:23:53] Host: Paul Barnhurst: Not working on the model, kind of. Right. Because you have a lot of other stuff. There's telling the story. There may be talking to people, you know, asking questions versus the actual model building or the, you know, the fun time you get to spend in the spreadsheet.
[00:24:07] Guest: Charles Keepman: I would say that the majority of time is really spent tweaking assumptions based off of the research associated with those assumptions. You know, I think we're all kind of geeks over here. We all love Excel. We've spent a lot of time. I mean, if you summed up the hours, it's insane perfecting as much as we can our template model such that it's as efficient as possible. And so it still takes a lot of time to build out just because there's all the financials and the capitalization table information and all that kind of stuff. And especially when you're building like complex waterfalls and option pricing models to determine the value of common stock when there's preferred stock involved, it does take a lot of time to make sure that, number one, that you get it in there, but mainly that you get it in there, right? And there's no errors in it. But the majority of the time, I think we spend mainly debating kind of on like the internal deal team, if you will, the assumptions and whether the, you know, the multiples should be six x or seven x or whatever it may be, because that's really where, people are paying us for this, right? And I think that especially with AI and softwares coming out, like, you know, you could probably do a lot of this with a software, but there's that human element, the, you know, the art, if you will, associated with the science that, the gut check, the ability to kind of put into each valuation what we've seen in all of the other valuations and what we've seen in company purchases and so forth, that is really hard to nail. I feel like in some sort of software, AI.
[00:25:56] Host: Paul Barnhurst: 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 love 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.
[00:27:01] Host: Paul Barnhurst: Yeah, no, I hear you there. That's what a lot of people say. There's the human element and the judgment and hey, does this pass the sniff test? Like, we've all seen times where someone puts in an assumption and you're just like, no, sorry. It's just not doable. Like, you know, and 2022, 2021, if you assumed 1% inflation next year, everybody's going to look at you like, I don't know. We just came through 7%. One might be a little off. Right. Or. And so I'm with you. I think in that sniff test is so important, that ability to look at something and be able to tell it's off and then have a conversation because there is no right answer, you know, it's why I love George Box, he has a quote. He was a scientist, modeler, physicist, and he said all models are wrong. Some are useful. I think that's really the goal. Is it useful? Can we support the assumptions? Not are the assumptions right.
[00:27:57] Guest: Charles Keepman: Well, and that's another debate that I think we often have internally at the firm. And I think just in general, you know, we could go in and do these complex Monte Carlo analyzes and all this kind of stuff, which sometimes we, you know, that is the best approach. But it's always the question of does the added complexity give you a better result? And sometimes the answer might be yes. But oftentimes, especially in my personal life, I think it's you know, Generally, I will err on the side of simplicity because, you know, you can get analysis paralysis. You can look at things a million different ways. And, you know, it doesn't mean that you're going to get a better result or a better answer.
[00:28:39] Host: Paul Barnhurst: Yeah, more complex sometimes will create a false sense of precision. Complexity doesn't mean precision. You can do back of the envelope. And if you know something really, really well, be quite precise. And there's other times where you need to do a full risk analysis or a really detailed sensitivity scenario. Monte Carlo. What list goes on and on and on. Right. All kinds of different ways you can do that. And you know, what the right level is, is going to vary by project your knowledge, the people you're working with. I mean, we always say err on the side of simplicity. I like to say keep it as simple as you can, because whoever you're working for will complicate it by the time you're done.
[00:29:20] Guest: Charles Keepman: Yeah, there's an individual who, you know, I'm lucky enough to have kind of his mentor, Kevin Yiannopoulos. And he always talks about the illusion of precision with me and some of my colleagues and how, you know, sometimes, you know, especially for like financial statement auditor related stuff. And it's not their fault necessarily. They just need something to be able to analyze and audit and stuff like that. And so if you come with this simplistic approach or a pragmatic approach, you might be just as right as the super complex models, but they just want to see more. And so he always, you know, talks about and I've really, you know, appreciated his focus on, you know, don't fall for the illusion of precision by overcomplicating things.
[00:30:09] Host: Paul Barnhurst: I have a tendency to over complicate and I have to remind myself of that one. So I always appreciate that.
[00:30:14] Guest: Charles Keepman: Yeah, I would say most of my time is reviewing the models that we're creating internally for clients, and ultimately we don't generally share our valuation Models. It's the, you know, the analysis per se, that we're coming up with. But yeah, most of my time is spent, you know, reviewing that. And our firm is not set up in the sense where we have, you know, pure business development folks and pure execution folks. We all kind of need to do both, right? We need to keep the lights on. But it's really hard, I've found to be efficient and effective at, you know, getting new business. You can't really talk. And if you're not, if you're not doing this work on a consistent basis, it's even harder to, you know, stay sharp and be able to present well.
[00:31:01] Host: Paul Barnhurst: I hear you, I don't get to build models near as much as I used to. And sometimes it's like it's trying to stay sharp. Certain things ask questions like, it's been a while since I've done that. I might be a little rusty.
[00:31:12] Guest: Charles Keepman: Yeah, well, especially when technology is moving as quickly as it is and Excel is coming out with new formulas and, you know, all that kind of stuff.
[00:31:20] Host: Paul Barnhurst: No, it's pretty amazing to watch how much Excel has changed over the last years. It has me excited. That's one area I follow closely since I do excel training and things, but just amazing what you can do today versus five, ten, 15 years ago.
[00:31:35] Guest: Charles Keepman: Yeah, for sure. And I really appreciate how they're like trying to simplify things. Right. They noticed that, for example, I'm sure this is you know, you know, this probably most people listening know this. But like the index match formula was something that was just kind of created by blending two formulas together. And then they were like, well, let's just make it xlookup that way it can. People can actually understand how it works without having to Google it or look at somebody else's, you know, examples.
[00:32:03] Host: Paul Barnhurst: Yeah. Well, exactly. I mean, so let's take the best of Vlookup and index match and try to create something. And in 90% of situations I think it's easier to understand. I can see why people do index match. It's whatever you're familiar with. And there are cases. There are some things you can't do with xlookup but in general, I love when I have to train people. I wish they didn't know Vlookup at all because xlookup just is easier to train. It makes sense. Like I taught 218 year old girls they're getting ready to college. Their dad paid me to teach them excel and they're like, why would I ever use Vlookup? Why do I even need to know this? Like, trust me, plenty of people will use it during your career. You want to at least know it. But they're giving me this look like, why are you even teaching me this? And I explained it like, oh, that makes sense. So it was kind of funny. Favorite. What's your favorite Excel shortcut? Everyone has one.
[00:32:53] Guest: Charles Keepman: Shortcut I think it's the ability to like the alt es. So alt es, either like V or F or whatever, because when you're modeling, sometimes you want the format, you want it to look pretty, and sometimes you want the formula and sometimes you just want the value. And so just having that like quick functionality to do that is really nice.
[00:33:14] Host: Paul Barnhurst: There's even times when for me it's alt ESV with an add or subtract, a D or an S or whatever. You just have some numbers you need to subtract into something and, you know, hopefully not in a model on doing that or someone's going to hate me.
[00:33:26] Guest: Charles Keepman: But yeah. Exactly.
[00:33:28] Host: Paul Barnhurst: You know what I mean? When you're just doing something quick and dirty sometimes, like, okay, I just got to get these numbers roughly where I need them to be.
[00:33:34] Guest: Charles Keepman: So but I think the most helpful is that, like, when I'm, when I'm dealing with large data sets, it's an alt a m. So to like sort and filter the unique ones.
[00:33:46] Host: Paul Barnhurst: Yeah, I love the unique. I use the unique formula all the time to create unique lists of something. Makes sense. Yeah. Altemps as well. Yeah. Remove those duplicates. Definitely use that one more than once. All right. So what's the number one lesson you've learned during your career that's helped you the most?
[00:34:02] Guest: Charles Keepman: Ironically, I think it's a lot of what we've already talked about. It's the importance of creating something that's simplistically built and auditable. Right. People like, it's great if you create something crazy complex for yourself, but it's a bigger measure of somebody's intellect. If they can take something really complex and chop it into little parts to make it work. There's so many times where we'll have a really complex, you know, formula or, you know, data set that we're trying to figure something out on. And I learned this from, you know, a guy that I worked with at EY that was also a mentor to me and what we were trying to figure out, or what he would always do is he would say, build a really simple example and just see if the formula works, and then slowly add in like additional complex layers and see if you can edit your formulas such that it's going to work on the big one. Because if you can make sure that that formula is working on a subset that's small, that's like that's understandable. And you can know what the answer you're trying to look for is. Then you can transfer that kind of like formulaic process to the bigger, more complex model. And obviously you're still going to run your tests in your, you know, your checks and things like that. But it's a lot easier to start small and like increase the complexity rather than just build something all at once from scratch in one cell for something you're trying to figure out.
[00:35:40] Host: Paul Barnhurst: Totally agree. It's a lot easier to troubleshoot and test when you start with a small data set. I mean, I did a lot of SQL coding. Sometimes I'd start with something really small and go, okay, did I get all the right answers or all the steps? Right? And then you'd start adding more and more complexity, much like a formula. I did report writing for about a year. Year and a half is one of my jobs. And. Oh, cool. Yeah. So very similar to Excel where you're just like, okay, let me start simple and build. Because if you start complex problems, you don't even know if it's right. I think it's right. Exactly right. We'll just drag it down and hope they're all right. Not that we'd ever do that, but. All right, so we're going to move to rapid fire. Have about I think there's 12-14 questions here. The way this works, you can't use the answer of it depends. You pick a side. We'll run through all of them. Then at the end if there's 1 or 2 you're passionate about because all of these could be it depends. You can elaborate on a couple of them. Are you ready? All right. Circular or no circular references in models.
[00:36:43] Guest: Charles Keepman: Oh, gosh. No. If I can avoid it.
[00:36:47] Host: Paul Barnhurst: Vba or no VBA.
[00:36:49] Guest: Charles Keepman: No. If I can avoid it.
[00:36:51] Host: Paul Barnhurst: Horizontal or vertical?
[00:36:53] Guest: Charles Keepman: Vertical.
[00:36:54] Host: Paul Barnhurst: Dynamic arrays and models. Yes or no? No. External workbook links. Yes or no?
[00:37:01] Guest: Charles Keepman: No.
[00:37:02] Host: Paul Barnhurst: I knew where that one was going to go. Named ranges. Yes or no? Yes. Okay. Do you follow a formal standards board like fast or one or the other smart? Some of those that are out there?
[00:37:14] Guest: Charles Keepman: Not to my knowledge. I may inadvertently base off of my training.
[00:37:19] Host: Paul Barnhurst: Sure, there may be some of it you're following. Makes sense. Should financial modelers learn Python in Excel?
[00:37:25] Guest: Charles Keepman: I could see a lot of benefits.
[00:37:27] Host: Paul Barnhurst: Yeah. So is that a yes or no?
[00:37:29] Guest: Charles Keepman: But no. I'm a purist.
[00:37:31] Host: Paul Barnhurst: I get it. What about Power Query? Yes. What about power BI?
[00:37:38] Guest: Charles Keepman: Yes, absolutely.
[00:37:39] Host: Paul Barnhurst: Okay. Will Excel ever die? No way will I build the models for us in the future.
[00:37:48] Guest: Charles Keepman: I'm not going to be so pompous as to say no, because I honestly think that there's a way to do it.
[00:37:56] Host: Paul Barnhurst: There we go. Sheet cell protection in your model. Yes or no? Sure. Yeah, that's, I get it. All right, so which financial statement in your view is most important for financial modelers? Balance sheet PNL or cash flow.
[00:38:11] Guest: Charles Keepman: Cash flow.
[00:38:13] Host: Paul Barnhurst: All right. Do you believe financial models are the number one corporate decision making tool?
[00:38:19] Guest: Charles Keepman: Ooh, I hope not. No.
[00:38:22] Host: Paul Barnhurst: What is?
[00:38:23] Guest: Charles Keepman: I think it's all of the context.
[00:38:26] Host: Paul Barnhurst: Go with that. What is your lookup function of choice? Do you like choose Vlookup Xlookup index match something else?
[00:38:34] Guest: Charles Keepman: I used to be an index match. I use Xlookup more often just to make sure that people are viewing can understand it, but I will agree that I think there are some unique things you can do with index match and match match that are pretty awesome.
[00:38:48] Host: Paul Barnhurst: Yeah, and ex match allows you to do even more now.
[00:38:52] Guest: Charles Keepman: Oh, I haven't tried that one.
[00:38:54] Host: Paul Barnhurst: Yeah, because ex match will have a smaller or larger the same as Xlookup. Oh and Wild Card has those same four options. So you can do index ex match. Ex match.
[00:39:04] Guest: Charles Keepman: Wow. I'm gonna have to check that out. I haven't been dealing with as large data sets as I used to, so I haven't needed to as much.
[00:39:11] Host: Paul Barnhurst: But yeah. And I like, you know, if I need to do a nested, I'll do a nested xlookup. If you've ever done that, to do it like an index match match, you can you can nest your xlookup. Oh that's on your return array. You do your second xlookup and your first lookup value is the second value you're going to look up. And your second array is the entire inside like the match. And it will do a two way. So you can do xlookup xlookup and return both the rows and the columns.
[00:39:39] Guest: Charles Keepman: Oh that's great.
[00:39:40] Host: Paul Barnhurst: So yeah, there's a fun one for you. I did a YouTube video about it. I remember I think I picked it up from Oz is where I learned that he's an Excel MVP. I think I saw I took his Dynamic Arrays course, so I knew early on, so I took it right as they came out that you could do that. And so I've always done it that way. And most people are like, wait, you can do that? There's a fun one for you. All right. I think that's the reality.
[00:40:01] Guest: Charles Keepman: Like the biggest takeaway I have with Excel is there's just so many things you can do. Like I love power BI, and I think that there's tons of opportunities there, especially because I used to deal with axis, which power BI I think is here.
[00:40:15] Host: Paul Barnhurst: I get it.
[00:40:16] Guest: Charles Keepman: It's a much better approach. And you've got all the, you know, kind of sexy things that you can bolt on top to review and things like that and slicers. But Excel is just such a blank canvas that you can do anything with. So it's hard to like.
[00:40:30] Host: Paul Barnhurst: The beauty of the spreadsheet interface. And Excel is the dominant one out there, whether it's Google Sheets, Excel, whether you use something else. It's just great to start with something blank and be able to build just about anything you want. It really is. Yeah, that's why I say it's kind of the art and science, right? There's a ton of art you can do in Excel, but if you don't follow the basic kind of science, those basic principles, you end up with one ugly painting, so to speak, that everybody wants to get rid of. Nobody wants when it's done.
[00:40:58] Guest: Charles Keepman: Yeah, absolutely.
[00:41:01] Host: Paul Barnhurst: Yeah, it's a lot of fun. All right, so we're coming up on the end of this, but is there 1 or 2 of those answers you wanted to elaborate on, one that you were kind of passionate about?
[00:41:11] Guest: Charles Keepman: Do you feel like Power Query is still being used with power BI now? Kind of. I feel like power BI is kind of taken over a lot of that functionality that I could have been just how I was using it in the past.
[00:41:22] Host: Paul Barnhurst: I think it depends on what you're doing. I definitely still think there's a lot of places for Power Query, but yes, there are a lot of people that are using Power Query and Power BI or it all depends on how structured your data sources is and what you're trying to accomplish. Right. If you're building data models and trying to visualize stuff, and you have power BI in your company and multiple people are going to use it, and why would you continue to do Power Query and Power Pivot? But like, let's say maybe you're just trying to do things ad hoc. You use a different BI tool in your company or you're just wanting. You're just trying to bring some data sources together. I still see Power Query a lot of times getting used, so I think it depends on the use case and what you're trying to accomplish in my view. But I definitely believe there's still a lot of room.
[00:42:10] Guest: Charles Keepman: And how have you seen AI already been utilized?
[00:42:13] Host: Paul Barnhurst: From what I've seen, the biggest areas we're seeing AI use one. Helping with Excel formulas is one of the most common out there. From a study I saw. Not surprising when I had to write and a nasty indirect the other day. I'm like, I don't remember. I don't remember all the little single and double quotes. And so I just threw it all into ChatGPT in about 20 minutes. I had this big old, long, indirect and I was like, it all works. Instead of spending 45 minutes going, okay, where's that stupid bracket? You know what I'm talking about? And so I think that's one area. I think the other area great at summarizing data, transcribing, helping write emails, being creative for you, where I still think we have a ways to go. And this is generative AI. I think when it comes to modeling, machine learning is still the dominant using statistical modeling, you know, prophet, Arima or whatever, Monte Carlo, all those different things to help with that. Generative AI is good. Hey, give it some data. Tell it what graph you want.
[00:43:11] Host: Paul Barnhurst: Do some basic analysis. Do benchmarking against different companies. You know, rip apart that 10-K or 10-q and have it summarize it for you. A lot of those areas definitely with coding and having Python, you're seeing some people use that. But I think most people are not yet. And the other challenge is very few people have a built in to their workflows. So you still have some security concerns. You still have some. Well, not in my workflow. Is it really going to save me that much time? I think we're getting there. So that's kind of what I'm seeing is I think people are still struggling to figure out all how to use it, but I think just about everybody is at least experimenting with it. Then the other issue, especially smaller and midsize companies, you know, bigger companies often have the money to have this in a better place. But is your data good enough to actually use with AI? You know, because garbage in, garbage out. AI isn't going to make your data good. What are you seeing? What's your thoughts?
[00:44:06] Guest: Charles Keepman: I think that it's going to have a profound effect. I just think it's like the security concerns are huge. And there's I think that the art of the AI will be humans figuring out the best way to utilize it. Right. So you can say, hey, you know, do all this analysis and give me this answer. But if that answer isn't auditable, if it's not adjustable, if it's not, you know, understandable how they arrived at that conclusion, it's no good. And so I think, you know, as humans craft how to use it and say, okay, here's how I want to understand, but I want you to break it out by, you know, this, this and this.
[00:44:47] Host: Paul Barnhurst: Tell me the logic you use. What are the assumptions versus just give me a number and I have no idea.
[00:44:52] Guest: Charles Keepman: So I think it's going to be really cool once you know, it's more integrated. And I think it's, you know, everybody trying to figure out, okay, what's the best way to use it. What's the safest way to use it and all that kind of stuff. It's going to be interesting.
[00:45:05] Host: Paul Barnhurst: I agree, I think we can all use it today and there's ways it can make us all more effective. But over time, as we really start figuring out the agent workflow and get it built into more and more tools, feel comfortable on the security side, have a little more transparency. It's going to become even more useful than it is today. I think quite a bit. It's exciting.
[00:45:26] Guest: Charles Keepman: Yeah. For sure.
[00:45:27] Host: Paul Barnhurst: All right. Last question for you. If you could offer one piece of advice to our audience to be a better financial modeler, what would be your one piece of advice?
[00:45:38] Guest: Charles Keepman: I mean, probably your audience is better at modeling than I am, but I would say that just keep it simple. Make it easy to understand. If your grandma can understand what's going on, then I think you've done it right.
[00:45:51] Host: Paul Barnhurst: Say, if my dad can understand it, you're doing even better. He's not the modeling type. I like that, grandma. Dad? Yeah. Try that person that knows nothing about modeling. Any idea? And if you can explain it to them, you're doing a good job. Yeah. All right. If our audience wants to learn more about you or get in touch with you, what is the best way for them to do that?
[00:46:11] Guest: Charles Keepman: Yeah. Just shoot me an email. Charles dot Keepman at Ryan. Dot com. I'd love to. Love to learn more about you.
[00:46:18] Host: Paul Barnhurst: Great. Well, thank you, Charles, and thank you.
[00:46:19] Guest: Charles Keepman: Thank you.
[00:46:20] Host: Paul Barnhurst: Carving out some time today. Enjoyed chatting with you.
[00:46:22] Guest: Charles Keepman: Yeah. Likewise.
[00:46:24] Host: Paul Barnhurst: 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 www.fminstitute.com/podcast and use Code Podcast to save 15% when you enroll in one of the accreditations today.