The Excel Techniques for Modelers to Build Real World-Business Forecasts - Luke Phillips

It was a model that was much less dynamic than I would build now. Like, I was hard coding, I was doing all the stuff that you're not meant to do. He wasn't hiring me on my technical ability, um, he was definitely hiring me on my potential. Luke Phillips is a senior business analyst and chartered financial modeler leading solver-driven budgeting and reporting solutions at Access Analytic. With hands-on experience across industries like mining, oil and gas, and retail, he blends deep Excel expertise with a unique background as a former NCAA Division I basketball athlete now competing in Australia's NBL One West. Being able to take the past, take your assumptions and push it forward. It's a bit of an art.

What motivated you during the certifications? What was the experience like? I think— Financial Modelers Corner is the world's premier modeling podcast. It is brought to you by Financial Modeling Institute, the world's leading financial modeling accreditation Accreditation organization. Welcome to Financial Modelers Corner. I am your host, Paul Barnhurst, the FP&A Guy. This podcast, 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. That's why I completed the Advanced Financial Modeler and hope to take the Chartered Financial Modeler. I'm thrilled this week to welcome to the show Luke Phillips. Luke, welcome to Financial Modelers Corner.

Thanks, mate. It's great to be on. Thank you for having me.

And if you can't guess from the word mate, Luke is Australian, joining us from Australia. So it's morning for him. He has a beautiful picture of the beach in the background. A little jealous.

Yep.

But excited to have him on. How close are you to the water?

Oh, I reckon it'd be about a 15-minute drive, just in the middle of Perth city at the moment. So 15 minutes, no traffic.

That's not bad. I'll take it.

It's not bad. It's a good life out here in Australia.

Great. All right. So let me give a little bit of Luke's background and we'll jump into our questions. So Luke is a chartered financial modeler who helps his clients tackle complex reporting and budgeting requirements through Solver and Excel modeling. At Access Analytic, he is the Lead Solver Technical Consultant, providing expertise on integration, data transformation, budgeting, and reporting. He has helped clients across a variety of industries, including mining, oil and gas, professional services, and manufacturing distribution. He holds strong interest in leveraging dynamic array functionality and working through circularity in Excel. Luke completed his Bachelor's of Business Administration and Finance at the University of Louisiana at Monroe. Where he was a student athlete competing on the basketball team. All right. So let's start with this first question. Tell me about that worst financial model that you ever had, that horror story.

Yeah, it's actually a bit of a recurring story at Access. It's actually the, I did it for an entrepreneurship Pelican Cup. It was a competition at my university and I'd been exposed to financial modeling before in my previous role, but I, it was me trying to replicate that without a lot of the skills. And when I first went for my interview at Access, that was kind of my reference material that I provided. And I remember Jeff said that he wasn't hiring me on my technical ability. He was definitely hiring me on my potential. So it was a really fun process. And I think it was a model that was much less dynamic than I would build now, much less safe. All this stuff, like I was hard coding, I was doing all the stuff that you're not meant to do. But I do think that it helped me then appreciate what I was then able to learn through working at Access, going through some of the training and accreditation programs and all of that. Appreciate that, hey, this is why we do it the proper way. So yeah, not, not proud of that model. I don't want to look at it ever again, but, um, definitely a good story.

We all have some stories like that. And I love that Jeff told you that we're not hiring you for the model you built or the technical skills. They're hiring you for potential. And really that's true for most of us out of college.

Yeah, for sure.

Dennis, speaking of college, I know you played college basketball and you also played overseas. What made you decide to focus on a career as an analyst? Kind of maybe talk a little bit about your experience with basketball before we jump into modeling.

Yeah, so I played Division I at Louisiana Monroe, enjoyed that. It was an amazing experience. Kind of come back and I played in Australia at the NBL 1 level. So it's a semi-professional league out here. Played here for 3 or 4 years, and I've just come back from a stint over, over in Europe. So I was in Cyprus and Georgia. So love my basketball, really enjoy it. But I also, I've got that other side of my brain where I love to be analytical. I love numbers. I love especially the spreadsheets, the way that you can pull lots and lots of complex calculations together and, and form a view of a company going forward. So I think that for me, I've got separate passions, and I'm lucky enough that now I get to play, um, play locally in the NBL One, but I'm also able to kind of work full-time and, um, and pursue the other passion.

No, that is great you can do both. And just a couple more questions about basketball, then we'll get to the rest of modeling. One, so did you play in college basketball? You would have played during the whole name, image, and likeness, right? That was during your time, or were you there before that? I'm just kind of curious.

Yeah, I was, I think by last year you could, but it was well before it got crazy. So.

Because you see it now, the numbers are just outrageous.

Yeah, the numbers are wild. It makes me wonder whether I should have been born a couple of years later. But no, I think for me, the experience of going to America and then the getting my education paid for, for me, that was amazing in itself. The money is life-changing for some people, but I'm I'm very happy with my experience.

That's wonderful. And yeah, it still blows my mind sometimes to see the numbers now, but since we're numbers people, I'm sure you're the same way going, wait, somebody's making how much?

Yeah. Oh, it's set some people up absolutely for life.

So I'm a big BYU, Brigham Young University basketball fan, and they signed AJ Devonta and the numbers I've heard, I've heard anywhere from $4 to $8 million a year is what he's making. Depending on what report and how much is university versus other stuff. And if he's making $8 million for one year of college, if you're smart, that's life-changing money.

Yeah, 100%.

Yeah. Oh, pretty crazy. Any, any great stories or experiences, maybe favorite memory or most interesting from playing professionally? I know you and I talked a little bit and share whatever you want there. I know you played in Europe. I think you said Cyprus for a little while.

Yeah, yeah. Well, I might share a story from college actually, if that's okay, because of course I've got a number of stories. I could be here all day, but the one that sticks out to me was during COVID So I played during COVID where, you know, you could rock up for testing one day and suddenly you've got half your team out. And I remember we were going on this road trip swing. We were going to Texas State and UTA, and UTA, top of the pool, I'm sorry, top of the conference. So they were really strong. We were having a weaker year and we ended up having like all of our guards were out and we had, so there's a bunch of guys that probably hadn't seen as much court time that year or maybe even throughout their careers where it's like you're playing, you're playing 30 tonight and you might be a power forward, but you're playing point guard. And I remember we went into the top of the conference and came out with a win. It was on some of the news networks and all that. And that was just huge for us. And some of my closest friends and being able to go do that together and have that experience together. That was just massive. And it was absolutely a slog because, you know, you've got guys that aren't, aren't playing that many minutes and, you know, suddenly you've just got to go into it and it doesn't matter if you're tired, you just gotta, gotta find a way. And yeah, that was amazing.

Well, thank you for sharing. And I can imagine the whole COVID year is quite the experience.

Yeah. Yeah.

Nothing like that for any of us, especially sports. I remember remember well all that and following it with sports. All right, so I know you work at Access Analytic. Tell us a little about the company and your role. You work a lot with Solver, so maybe talk a little bit about the company and then what exactly is it you do? What is Solver for our audience?

Well, Access Analytic, it's been around for 25 years and started off with Excel financial modeling and that and Excel calculations and really helping our clients with Excel-based problems. And that kind of expanded out to Power BI when Power BI started. And now we provide services across really 4 main areas, which is now Excel modeling, Power BI, Fabric, and Solver. And I'm mostly helping the financial modeling and the Solver clients that we have. Solver's a budgeting and forecasting as well as reporting software that allows you to kind of create your budgets, consolidate your data up in the cloud environment. It's got an Excel design layer. It allows for a bit more of an intuitive feeling for, you know, those familiar with Excel, but allows for some of the consolidation and the nice things that data allows us to do that spreadsheets find a bit harder. So yeah, I help a lot of our clients with just working out the ways to solve their problems across a range of different areas.

Okay, cool. Yeah, no, I'm definitely familiar with Solver. I know it's a, you know, planning, consolidation, budgeting, forecasting tool, and it's cloud with the Excel interface to help for familiarity versus having to learn everything all new. Obviously you have to learn some stuff new. So I'm curious, I know you, you work with a lot of different industries, obviously being with Solver and with modeling, and I know you do some BI stuff. Do you have a favorite industry you like working for, or is there one you've really enjoyed learning about? Because as you get into that, you often have to learn, you know, the drivers and the different pieces of those industries.

Yeah, for sure. I think that there's a couple. I've definitely, um, gone across a range of industries. I did spend a fair bit of time in, um, supply chain section of a company, and that was really interesting because it's not necessarily— it wasn't as necessarily financial modeling focused, but it was really interesting understanding the realities of the reasons why people do things. And, you know, it's not always quite so simple as just, um, you know, how we can turn it into on a spreadsheet. So that was really interesting, just learning about the operational realities. But I suppose the other one is manufacturing and distribution. I've really enjoyed working with that. And I find that that's also a really good one where a lot of the processes might be quite similar across entities and across regions. And that can be something where I find a lot of power in being able to build like one, one type of template that then can find use across a variety of different areas. So yeah, those are probably the two.

I like something you really said there with the supply chain is helping you understand the operational realities, because we've all seen where you can model anything you want, but if you don't understand the operations and the constraints, what you modeled may look great on paper but make no sense in reality. Have you, have you experienced that as you're kind of building models? Someone's like, this would never work and here's why.

Yeah, no, 100%. And a lot of times there might not even be a way to model it because the operational reality is something that's just, you don't know and you have to make then your best assumption. One of the ones that we found interesting was around containers and, you know, once they go on to a location, they can either come back or they can go get used for various different reasons, storage. So when you're trying to calculate how many containers do you actually need, that it's a lot more than just how much are you moving. It becomes how much do you need storage on, on the site and wear and tear, and it becomes a lot more nuanced than, um, just purely, you know, what, what, what's the, what's the cycle time. So yeah, absolutely seen that.

Yeah, I, I think we all have, and I, I appreciate that example. The containers makes a lot of sense because, right, there's all these different things and Where, how do you balance, this kind of just makes me think of a question. How do you balance the trying to mirror operational reality with simplicity, right? Not being too complex. Do you have kind of a rule of thumb of how you think managing that? Because I know I, we've probably all been there where sometimes you're just like, okay, this model has way too much complexity. It's going to fall under its own weight. At least I did. I've built a few like that myself.

Yeah, I look, I try and keep it as simple as possible. And usually I've had the pleasure of working with a whole bunch of people that kind of understand that you're never going to be able to map the exact operations. Usually I'll just, I'll make a best call. What this is what I think gets us closest to it simply. And then I'll usually just run it through with the experts and go, hey, this is what I've kind of done. Is that close? Is that going to get us to our best estimate. And usually they have a better feel for it as the, you know, the subject matter experts. So yeah, I think it's definitely about, as a modeler, you end up seeing more and more examples of it and you just get a bit of a feel that this is where the line is. So yeah, I think it's more an experience thing than anything else.

Yeah, it makes a lot of sense. So I know, you know, your role, I think your title is business analyst or senior business analyst. So I know you do some modeling. I know you like to work with Power BI. So you're definitely doing more than just financial modeling. What do you like about that mix of having that traditional analyst work in the Power BI with modeling? What attracts you to that kind of combination?

Yeah, I think that for me, I like solving problems. I like solving problems. I like when a client's got something that really takes a long time for them to do or It's a real challenge for them, and I like being able to go and simplify that or make that process easier for them. So I think when you're creating those Power BI models that can create more visibility, like I've seen some of the clients that we've worked with, I remember this one situation, it was around environmental reporting and the amount of visibility that they had over their data. The data hadn't changed, but it was just the way that we were showing it. They loved it. And then, you know, the people that they were showing loved it. And that type of thing is really cool. And maybe not exactly the type of work that you'd be able to do in Excel or doing financial modeling because you're more doing the predictions. So I love with working with the actual data and whatever types of data, but I also really do enjoy the forecasting piece and the, and Excel and really bringing together the future because I think that's something that it's a bit of an art being able to take the past, take your assumptions and push it forward. So I guess I've just got a passion for both.

Yeah, and I love how you said it's part art because always, like I say, there's art and science. I mean, there's lots of room for opinion and model even though there's some things that you should probably never do.

Exactly.

On Power BI, so are you mostly building data models, doing DAX for reporting, or kind of where's your, you know, what's the kind of work you're doing with Power BI?

Yeah, so I think it depends whether I'm doing a financial reporting suite or whether I'm doing other areas. So I Like I said, I've worked on some environmental stuff. I've done some supply chain stuff. So I think that the, if it's not financial data, I'm usually pulling from a combination of data lakes, databases, data warehouses, that type of thing. And then showing what the client wants, but usually trying to take my analytical capabilities to show it in the best way that tells the story. But then the financial stuff, usually that's going to be coming from another system. And because I work quite heavily with Solver, it's oftentimes it's the visualization of the data that sits in that Solver data warehouse.

So a lot of times people are sticking Power BI on top of the Solver data.

Yeah, so Solver more applicable for kind of Excel-based reports, like more of a how you'd put it in paper. But if you want dynamic graphs that are, you know, having that cross-filtering capability, that's definitely where you want to be starting to use something more like Power BI.

When you start to get more in, you want it more standardized, you want all the cross-filtering, it can be tough in Excel.

Yeah, oh no, very much better to use Power BI in that case for sure.

Yep, no, totally get it. So you earned both your AFM and CFM, and I believe in short order you did them both in the same year if I remember correctly. So what may— what motivated you during the certifications? What was the experience like? Talk a little bit about that.

Yeah, so I think in terms of motivation, um, when I first started Access, I, like I said, technical skills weren't necessarily there. And yeah, I remember my first day, I had a, um, Jeff came along, he gave me a stack of books this big, and, um, he sent me this email with a whole bunch of training resources, and it was pretty much, you know, um, just being in the office for 2 months straight and just start learning. So In that time, I went through and I learned Power BI. I went through the FMI resources to learn the financial modeling aspect of it. And I just really got hooked. I loved it. Going through those videos and learning it kind of on a very basic level, like this is how you do it. There was no shortcuts. There was no fanciness about it, but this is how you pull a model together. So I loved that. And I was, yeah, going to take that AFM exam just to kind of demonstrate the, the capability. And yeah, managed to pass that one and went, oh, may as well knock out the, the CFM while I'm at it. So I think I booked in the CFM right after and yeah, managed to pass both. So it was, it was a good experience. I love doing both of them. They were definitely challenging and yeah, it was just an amazing experience.

Well, my background is in FP&A. I am also passionate about financial modeling. Like many financial modelers, I was self-taught. Then I discovered the Financial Modeling Institute, the organization that offers the Advanced Financial Modeler program. I am a proud holder of the AFM. Preparing for the AFM exam made me a better modeler. If you want to improve your modeling skills, I recommend the AFM AFM program. Podcast listeners save 15% on the AFM program. Just use code PODCAST. That's great. I know a lot of people, the CFM, you know, is a hard one. They really, you know, spend a lot of time on it. I know a guy who does training and I remember him telling me, I'm not confident I'll pass. And he had spent 100 hours studying and he trains people to build financial models. So I know CFM is you know, a really, a really challenging one. I think AFM is great as well. I've done AFM. I haven't done CFM. I will admit it scares me a little bit. And I was supposed to do it this last year but didn't have the time due to some, some stuff that happened. But so kudos to you. I think that's awesome. And I'm curious, when Jeff gave you a stack of books, was there any— was there a favorite book or one that really stuck out to you from that stack?

There was a couple that I got into. There was a couple that I didn't actually end up finishing. I think that the, the one that And I'm going to forget the name of it, but there was an Excel-based book that really started to help me with just some of the stuff that, you know, if you're a financial modeler, you know, like with the shortcuts and the, you know, just the basic best practices, but coming from someone that just didn't have that base and hadn't been taught it obviously in college. So that was super valuable for me to read through that one. But yeah, I probably did more of my learning actually on the online resources.

That works. And so funny, if anyone doesn't know Jeff Robinson, I've had him on one episode very early on to talk dynamic arrays. So I told Luke this before, but I'll share my first story with Jeff. First time I met him was I saw a webinar he had done. And I wasn't live, I mean, first time I, uh, kind of saw his content and it was very early days of dynamic arrays and he was using the mold to try to sum things and here's the workaround I found for this and I thought dynamic arrays were the coolest thing, but I was watching some of that going, okay, there has to be easier ways. They're going to hopefully figure this out. Like, this is painful. But he had figured out and was building, you know, some of the early models using dynamic arrays. And so that kind of always stuck with me because it was clear he was pushing the boundaries and really trying to figure out what was possible. So I think you have a, you know, both Wynn and Jeff, you have two world-class people there to learn from. And I know there's others in the company, but those are the two I see.

Yeah, no, for sure. Yeah. It's a, it's an amazing company to be a part of. Yeah. To be able to learn from, you know, skilled guys at the director level, but everyone in the company, very skilled. I learn every day.

That's awesome. No, it's great to be, always great to be with smart people.

100%. Yeah.

So something I found really interesting when we chatted, cause it's just foreign to me. So yeah, I learned my first spreadsheet was. I think it was Quattro Pro or Lotus 1-2-3. I can't remember which one was first, but in high school, one we had at home and one was, uh, at school. So I date myself where there weren't many functions in those early days. And so, you know, we chatted, you mentioned you used Lambda all the time, but you've never really written an index match, which is foreign to probably 90% of the audience. One, it shows how young you are. So I might call you the baby. And two, just the difference in learning of when you learn it. So why is that? You know, kind of how, how did that happen?

Yeah. You're telling my embarrassing secrets out here.

I don't think it's embarrassing. I think it's great.

Yeah. No, I think that I learned XLOOKUP and I, once you have XLOOKUP, you don't need INDEX MATCH. You cannot obviously use it. And there's definitely times where I've got a number series at the top and I'm using it to, you know, find lookup values back. Back in time. There's definitely times where I'm doing that and I'm like, yeah, this would be better if I was using an index, but I just feel really comfortable with DEX lookup and I like the, I feel comfortable with the, the following parts to it too, like the if not found and the, the search functionality and all of that. So I just find that it's easy for me to just use that. But whereas, on the Lambda side, sometimes I need Lambda to do the, the dynamic array calculations that I want to do. So because I need to do it, I use it. So yeah, I suppose that it's just a case of what you learn and INDEXMATCH didn't come quite as naturally to me as XLOOKUP. And yeah, I just like to use that from there.

Makes sense. I'm a big XLOOKUP fan. I use it 90% of the time. You know, there's occasional where I might use something else. Obviously I grew up on VLOOKUP. I remember when I first learned INDEXMATCH, it's like, oh wow, this is cool. I can do this two-way lookup. And you know, this works so much better than the VLOOKUP. I remember trying to tell someone at the company, look at me like, that's way too complex. I'm not doing that. But yet they're concatenating like 10 items at the front to be their lookup. Like, it's really not that much harder.

Yeah. Yeah. It's just what you're comfortable with and what you've learned and you've done over a number of times. You just built up that comfortability with it that it becomes easy. Whereas for me, writing an index match, I'd have to question myself, you know, just a small question and just don't need to do it.

No, I totally get it. It took me probably 6 months to get totally comfortable with XLOOKUP. Every so often I would start typing the column number. Still, every once in a great while, I'll start typing and I'll do that. I'm like, why am I doing that? Just because I used it for so long. So it definitely, a lot of it's just preference and what you use. Right, so kind of speaking of preference of fun things, do you have a favorite Excel shortcut?

I've got a lot of shortcuts I like. This might be a new one though. Alt W V G. I do on any of my new, yeah, gridlines. So I just, any, any time I've got any type of new template, get rid of it. It's, I just don't like looking at the gridlines.

I don't anywhere. I use that one all the time.

So yeah, but, no, I try and keep away from the mouse as much as possible. Shortcuts are king.

Shortcut warrior. I get it. See, I like my mouse. I'm using it right now.

It's good, but not quite as fast.

100% agree. I'm a mix. I used to use my mouse a ton and then I learned the shortcuts and when I'm in a hurry, I'll often use shortcuts, but just depends on the work I'm doing and whether I want to or not. I know a lot of them. So I, I get both sides of it. I just like to tease people that are the, the shortcut warriors.

Because I learned some of the shortcuts early on. I reckon it might take me a little while to actually figure out where the gridlines button is. Because I just, I learnt the shortcut and I just, it's all about what you know and if you know how to do it.

Have you ever seen, so you'll laugh at this, I'm going to show my keyboard here. Have you ever seen Inge Knorr's keyboard?

Uh, what does he have the, um, F1 button ripped out?

No, it is completely blank. There are no keys on it.

Oh, really?

Yeah, he prefers that. He works faster. Just a black keyboard.

Wow.

Yeah, that, that's someone that's, um, and he is a, he is a keyboard warrior. Yeah. So it's, it's, he, he will show us sometimes in training and I'm just always like, I couldn't do it. Even though I know where the keys are. Yeah. I think I could actually, I pretty much have memorized, but just the idea of it is so foreign to me. I'm like, why would I do that?

Maybe it's something to do with just not looking at the keys because if you're looking at the, the screen, you catch your errors a bit quicker. Yeah, I don't know. I've never been taught, I've never learned like the touch typing the proper way. So I'm sure I just do the wrong fingers to the wrong keys. So yeah, I don't know how that'd go for me.

So there's one for you when you talk about, you know, taking it to the extreme.

Yeah, there you go.

So I am curious, you know, we hear a ton of talk of AI and modeling. So have you tried building a model using an AI agent? And what was the experience like if you have?

No, I haven't. I've got to be honest with you. I think that it's very hard to expect an AI agent to be able to do a model. I mean, I think like some of the basic, like build a model, sure. But oftentimes I find that, you know, you talk with the client, the client doesn't even necessarily know what they want. You've got to work that through with them. So the idea that an AI agent model is going to be able to do it first try. I find that hard to believe, but I did have a moment just recently with Wim Hopkins and we went through and tried the new one that they've got in Excel.

I remember he finally got it installed on his computer. He had quite the journey on LinkedIn telling you he couldn't get it installed on his computer, if I remember.

Well, he'd had it, it worked by that point. And anyway, we put in some prompts and, you know, built out. I was actually pretty impressed that it built out what it did. But the fact is that half of the modeling journey is understanding your model and knowing what your model's actually got. So I think that, you know, vibe coding a financial model is a recipe for danger, especially if you're talking about valuations and M&As and, you know, these big transactions that rely on this. You just need someone that knows what's actually going on in the model. Yeah. So haven't done it. Bit scared of it, the, to trust it, but it is cool to see how, how fast it's moving.

I appreciate you sharing. You make a lot of the same points others have made of even if it can build it, you still need to understand it really well. Yeah. And the more customized it is, the harder it is for an agent to be able to do it. Some standard modeling, sure. I, I, I'm with you. I think we're seeing similar things. I think we'll get better. I think the data may get there. They can do it, but it's still never going to take away the need for us to know really well, because you got to validate it all at a minimum. Bad financial models can lead to bad decisions or worse. So how do you minimize the risk of a bad model? You make sure the models you build are great. Financial Modeling Institute developed the Advanced Financial Modeler Accreditation program to help modelers like you. The AFM program offers a step-by-step approach to building world-class financial models. The program ensures that you know the best practices in model design and structure and will help you brush up on your Excel and accounting skills too. Be the one on your team to build great models. If you want to impress your boss and your clients, get AFM accredited. Podcast listeners save 15% on the AFM program. Just use code PODCAST at fm institute.com/podcast.

Yeah. Have you seen, when you were testing those models, um, did you see any of the models kind of come back to you and go, Well, hey, what do you mean by this? Like ask you questions as the, you know, as the prompt person. Because I think that that's what a normal financial modeler would do.

Yeah, they definitely have. Where it was really good is, so I fed it a headcount schedule and asked it to build out a schedule for headcount for people. And it did a really good job of asking a ton of questions. But I also sometimes will do this, and I think it's really good with modeling, or with AI, is here's my data. Review the data and let me know what questions you have. And it will ask you a bunch of questions and you go through and it actually even created out like an assumption table that it was using for the model. And so it was kind of that back and forth conversation. It still got a few things wrong and I was more seeing how it would do things. So I was impressed, but I could also see, hey, if you didn't know what you're doing, it could be dangerous. My favorite that we had, and I don't know if you saw this, but we had one model where, so the balance sheet wouldn't balance. We pretty much none of them balanced, almost always had a balance sheet issue. And we asked it to fix it, you know, find out where it's wrong. And it came back and it got a little closer. It's like $1.3 million out on a billion dollars, right?

Yeah.

And, uh, it finally came back to us and said, that's close enough. I'm done looking. That's an acceptable variance. We're like, this is not cash flow reconciliation where, you know, $20,000 on $5 billion, no big deal. Just plug it and move on. Yeah, you can't be a million out on a balance sheet.

You can't be, you can't be a dollar out on a balance sheet because this is not how it works.

Yeah, I mean, if you're a penny out for rounding, whatever, but you know, outside of a tiny rounding error, you can't. Exactly, it has to balance. And we were just like, so that was our, probably our favorite moment. We're like, yeah, this isn't ready yet, but, but it is.

You look at where it was a year ago, it is coming. And I think that you're going to see more people, especially in my line, where they'll come to you with a half-finished model and kind of think, hey, can you just, you know, fix this last little bit? And probably the answer more often than not might be, this is going to need a rebuild.

Well, yeah. And the example I'll use, how often if you inherit somebody else's model, if you ask modelers, how often do they start over? Quite often. Now you have AI building it, which often uses more complex formulas. You can't ask 'em questions the same way you can a human. Yes, you can ask 'em questions, but it's different. That back and forth and that relationship. Why do you think you would never start over with an AI-built model? So that's where I was stated at. I, I'm with you. What's the most unique or fun thing you've ever built in your life with the model? Personal life or whatever, the most. Interesting thing you've done with the spreadsheet?

I think—

waiting for a March Madness tournament ultimate spreadsheet here.

I'm just kidding. I probably haven't had the fun project that I've done. The one that I did, it was more of a, you know, personal kind of— I had a problem with understanding how it worked and I kind of built a solution for it. I don't know if it's the type of solution I would ever use with a client, but with Yeah, dynamic array circularity and working through schedule circularity with that. And I had a problem. It's like anytime you use a cash revolver or cash interest, anything that requires across multiple schedules with dynamic arrays, it becomes a circularity issue very, very quickly. So I kind of, I was like, oh, this is surely I can find a way to solve this. And yeah, I ended up building a relatively complex Lambda function that did it. I think from my learning from that is that I kind of call the line the second I get to model circularity. I just, I stop with my dynamic arrays and build it using traditional cells now. But that was a really fun process to actually go through and, you know, build the, the Lambda formula that actually solved that problem. But, you know, probably learned in that time that it's a bit too rigid. For a, for a solution for, you know, mass use.

Yeah, it's not something you're going to use in your day job, but it was cool to do.

It was, it was cool to solve the problem for sure.

No, that, that's awesome that you did that. I would have been like, I'm, I'm out, back to tradition, forget this.

Yeah, well, it was harder, hard a little bit to even explain to people what I was trying to do and, and the problem I actually had because, you know, um, circularity that you have just in the singular, um, schedule, you can solve it with scan or, you know, Craig Handmaker's got the 5G functions that do it. But yeah, this one was a, um, it's actually just an Excel limitation. So you either solve it all in one big array or you don't solve it without iterative calculations.

Got it. All right. You ready for rapid fire?

I'm ready for rapid fire. Let's go.

Here's the rules. Well, just so everybody's on the same page here. No, it depends. You have to pick one side or the other, and then you could elaborate at the end because I know there's some nuance. So you ready?

Yep.

Circular references, yes or no?

No.

VBA, yes or no?

No.

Do you prefer a horizontal or vertical model layout?

Vertical.

Okay. Use Excel dynamic arrays in models?

Yes.

How about fully dynamic array models, like where 100% are dynamic arrays?

No.

I, I have a feeling you'll— you're struggling with that one. External workbook links, yes or no?

No.

Good answer there. You can see my opinion. Named ranges, yes or no?

Uh, yep.

Do you follow a formal standard like FAST or SMART when you're modeling?

Yeah.

Okay. Should financial modelers learn Python in Excel?

No.

What about Power Query?

Yes.

Power BI?

No.

Okay. Will Excel ever die?

No.

That's, that's the most common answer. Do you believe financial models are the number one corporate decision-making tool?

No.

What is?

Humans. Oh, maybe it's the most common tool. I'll give you tool, but it's not the most common. Designer.

Yeah, I get what you're saying. I think my favorite answer on that is someone said, no, it's, uh, politics.

Not a bad answer. Not a bad answer.

I was like, I can't really argue with you there. And then I think I know the answer, but we'll ask it. What's your lookup function of choice?

Normal Excel XLOOKUP.

Yeah. All right. So what about non-normal Excel? You said normal Excel. Power Query?

Like sometimes if I'm using Solver, I might use like SUMPRODUCT as a as a lookup function.

Sure. Okay. Got it. I've definitely seen that done.

Yeah.

All right. Go ahead and elaborate. I know there's one or two in there. Let's talk dynamic arrays. That one you were struggling with.

Yeah. So I love that if you're doing 3-statement models, you can't do 3-statement models with fully dynamic array unless you do what I was kind of explaining earlier. But what I do do is I go in, in every cell after you hit model circularity. So it's usually after interest in the income statement. Everything after that, or any schedules that require it, I'm kind of wrapping my function in an if, refer to the model timeline, is number, and then do the formula, otherwise blank. And then it behaves like a fully dynamic array model, but it doesn't run into the circularity issue. So that's how it tech it's kind of dynamic array, kind of not. But yeah, so that's what I would generally do. I love dynamic arrays. I want to use them wherever I can, but Excel's got that limitation.

You'll have to share an example sometime. I'd be curious to kind of see what that looks like. What I was going to say is I'm excited for when the day comes. I imagine at some point Microsoft will solve it when we could do an array of an array.

Array of an array would be, would be really nice. And I would love, I think I got Wynn to push this to the MVP chat and it, it got shot down, but I would love an ability to go iterative calculations only for dynamic array circularity, but I don't think that one's coming anytime soon.

Yeah, I'd be surprised if you get that one anytime soon, but if you do, that'd be cool.

It would be cool.

I'll have to go look at the chat for the MVPs.

Yeah, yeah, exactly. And then I had a couple of other ones. What was the—

So there was VBA, circular references, external workbook links, named ranges, Python, Power Query, Power BI.

Yeah, so external references, um, only if you've got multiple people using it and you've got to hide like HR data. I don't like to do it. Usually there's probably a better way of doing it, but if sometimes you've got sensitive data you can't show. And then the other one, Power BI, I think a great thing to learn but not essential.

Yeah, makes sense. I do— I would agree with you on Power BI. I think for 95% of modelers, Power Query— some are not pulling data, they're just building a model and letting— okay, if you're never pulling data, fine. But if you're pulling any level of data, I think you have to know Power Query. So I'm with you on that one. I tend to agree with you on Power BI and Python as well.

I think I've seen some stuff recently on LinkedIn that maybe you can do similar stuff with Python that maybe you could do with Power Query. So yeah, potentially if you've got stuff like that where you're replacing the actual requirement, then yeah, that makes sense. But yeah, I think Power Query is the answer.

I definitely think you could, I'm sure some calls and you could do some things with Python. They also, it's in beta, they just released two new functions, import text and import CSV. So you can import them as an array straight into the grid. So I could see some situations where maybe you don't want to use Power Query. They come in as an array, but you have to refresh the data for it to refresh.

So it's a non-volatile function, but you refresh it with like, uh, the refresh button, or the refresh—

you refresh it with the refresh button. It is an array, so you could bring in the data and use like take or drop. I only want the first 10 rows, or I want the last 5 rows, or whatever. So I could see some situations where, hey, you're not really comfortable with Power Query, or you just have something where all you need to bring it in, you're not going to change it every month, you're running some formulas off it, and you had been copy and pasting for whatever reason, or doing it through Power Query. I could kind of see it. I still haven't tried it, so I can't say how it works, but it was just barely— I learned about it, I think, this week. So very new in the beta channel. I did see I had the function. but what I don't like is you have to copy in the whole path. It doesn't have a dropdown, so I can't say import CSV and go link to find my file. I gotta go find that path and copy the whole path in at a time. I'm like, ah, I don't wanna deal with this right now. Yeah.

And I, I feel like it's not added to over the Power Query method.

And I, I agree. They mentioned right in their notes, it's more for people that maybe are intimidated by Power Query or want a way to simply bring something in. If I know I'm not gonna do any manipulation, Okay. I could see where maybe in a hurry, maybe I go that route, but I'm with you.

Yeah. Yeah. It'll be interesting to see where that lands and whether people use it or not.

Yeah. And if they even release it in the fi— you know, how they did, what they decide, what changes they make. So it was just kind of an interesting one. All right. So let's go ahead and probably wrap up here. So just, uh, one last question. If I was to ask you what advice you would give to, for modelers to be a better modeler today. To get better at modeling? What's the advice you'd give them?

I mean, I've got two answers. So the, the first answer is if you're not familiar with modeling and you don't know how to do it, I think that using the FMI training material, going to training courses and really trying to learn the technical side of it to make sure that you can have that table and be able to understand what needs to be done. I think that that's the first thing that you need to build. But then the next thing to me is really listening and understanding what the client wants, what the actual people that work in it operationally need. And how that worked. I think the skill of financial modeling almost becomes how well can you synthesize what people are telling you and then push that into the logic and the model. So I think the more you learn, the more you realize that modeling becomes an exercise in communication, working with people.

I love that. I might have to take that and put to the tagline for this episode. The more you learn, you realize modeling becomes a relationship in communication and working with people. So often they think modelers, right? They're just a nerd that sits at their computer and types on a spreadsheet. And it really is so much more than that. So I really, I like that encapsulation. And we'll go ahead and put your link to LinkedIn. So if anyone wants to contact you or reach out, I'm going to guess that's the best way for people to kind of connect. We'll go ahead and do that. And thank you so much, Luke, for joining me. I really enjoyed chatting with you and appreciate you sharing some of your experiences and stories, and best of luck with Access Analytic.

I appreciate it. Great to come online and, and talk. It's been a really good chat, and we'll stay in touch for sure.

All right. Hey, thanks, Luke. 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 modeler. What are you waiting for? Visit FMI at www.fm institute.com/podcast and use code podcast to save 15% when you enroll in one of the accreditations today.