The Excel Techniques for Modelers to Build Real World-Business Forecasts - Luke Phillips
In this episode of Financial Modeler’s Corner, host Paul Barnhurst sits down with Luke Phillips, Senior Business Analyst at Access Analytic. Luke shares how he transitioned from Division I basketball to financial modeling, and how he helps clients solve complex budgeting and reporting problems using Solver, Excel, and Power BI.
Luke Phillips is a chartered financial modeler and the Senior Business Analyst at Access Analytic. He works with clients across mining, oil and gas, manufacturing, and professional services to streamline budgeting and reporting through Solver and Excel. Luke holds a BBA in Finance from the University of Louisiana at Monroe, where he also played Division I basketball.
Expect to Learn
How a bad college project helped kickstart Luke’s modeling career
What Solver is and how it supports complex planning and reporting
Tips for simplifying models without losing value
The role of communication in building useful models
Luke’s take on AI and dynamic arrays in Excel
Here are a few quotes from the episode:
“We're not hiring you for your technical ability, we're hiring you for your potential.” – Luke
“I love spreadsheets, the way you can pull complex calculations together and form a view of a company.” – Luke
Luke Phillips brings a grounded, real-world perspective to financial modeling, shaped by both professional experience and athletic discipline. His approach blends technical skill with a clear focus on communication and usability. Whether solving client challenges with Solver or exploring new Excel features, Luke keeps modeling practical and purpose-driven.
Follow Luke:
LinkedIn: https://www.linkedin.com/in/luke-phillips-75859013a/
Company: https://www.linkedin.com/company/access-analytic/
Follow Financial Modeler's Corner:
LinkedIn Page- https://www.linkedin.com/company/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/497oAqW and use the code “Podcast” to save 15% when you register.
In today’s episode:
[02:09] - Life in Australia & Background
[03:16] - First Model Mistake & Lesson
[04:46] - From Basketball to Finance
[08:49] - What is Solver & Luke’s Role
[10:33] - Learning Supply Chain & Ops
[13:11] - Simplicity vs. Complexity
[18:24] - AFM & CFM Exam Journey
[22:46] - XLOOKUP Over INDEX MATCH
[27:46] - AI in modeling & Challenges
Full Show Transcript
Host: Paul Barnhurst (00:00):
Welcome to Financial Modeler's 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 Modeler’s Corner podcast is brought to you by the Financial Modeling Institute. FMI offers the most respected accreditations and 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 Modeler's Corner. Thanks,
Guest: Luke Phillips (00:52):
Mate. It's great to be on. Thank you for having me. And
Host: Paul Barnhurst (00:55):
If he 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, but excited to have him on. How close are you to the water?
Guest: Luke Phillips (01:09):
Oh, I reckon it'd be about a 15 minute drive just in the middle of Puff City at the moment. So 15 minutes, no traffic.
Host: Paul Barnhurst (01:16):
That's not bad.
Guest: Luke Phillips (01:17):
It's not bad. It's a good life out here in Australia.
Host: Paul Barnhurst (01:19):
Great. All right, so let me give a little bit of Luke's background and we'll jump into our questions. 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 in 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 a strong interest in leveraging dynamic array functionality and working through circularity in Excel. Luke completed his Bachelor of Business Administration in Finance at the University of Louisiana at Monroe, where he was a student-athlete competing on the basketball team. So let's start with this first question: tell me about the worst financial model you ever built—that horror story.
Guest: Luke Phillips (02:16):
Yeah, it's actually a bit of a recurring story and 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 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. 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 in access, going through some of the training and accreditation programmes and all of that. Appreciate that, Hey, this is why we do it the proper way. So yeah, not proud of that model. I don't want to look at it ever again, but definitely a good story.
Host: Paul Barnhurst (03:22):
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.
Guest: Luke Phillips (03:28):
We're
Host: Paul Barnhurst (03:28):
Hiring you for potential and really that's true for most of us out of college.
Guest: Luke Phillips (03:32):
Yeah, for sure.
Host: Paul Barnhurst (03:34):
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? Talk a little bit about your experience with basketball before we jump into modeling.
Guest: Luke Phillips (03:46):
Yeah, so I've played division one at Louisiana Monroe. Enjoyed that, it was an amazing experience. Come back and I played in Australia at the NB one level, so it's a semi-professional league out here. Played here for three or four years and I've just come back from a stint over in Europe, so I was in Cyprus and Georgia. So love my basketball, really enjoy it. But 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 form a view of a company going forward. So I think that for me, I've got two separate passions and I'm lucky enough that now I get to play locally in the MDO one, but I'm also able to work and pursue the other passion.
Host: Paul Barnhurst (04:35):
No, that is great. You can do both. And just a couple more questions, basketball, then we'll get to the rest of MA one. So did you play in college basketball? You would've 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.
Guest: Luke Phillips (04:49):
Yeah, I think my last year you could, but it was well before it got crazy.
Host: Paul Barnhurst (04:56):
You see it now. The numbers are just outrageous.
Guest: Luke Phillips (04:59):
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 getting my education paid for me, that was amazing in itself. The money is life changing for some people, but I'm very happy with my experience.
Host: Paul Barnhurst (05:20):
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,
Guest: Luke Phillips (05:32):
Set some people up Absolutely for life.
Host: Paul Barnhurst (05:34):
So I'm a big BYU, Brigham Young University basketball fan, and they signed AJ Desa. And the numbers I've heard, I've heard anywhere from four to 8 million a years 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.
Guest: Luke Phillips (05:54):
Yeah, a hundred percent. Yeah. Oh,
Host: Paul Barnhurst (05:57):
Pretty crazy. Any great stories or experiences, maybe favourite 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.
Guest: Luke Phillips (06:09):
Yeah, yeah. Well I might share a story from college actually, if that's okay. 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 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 in UTA and UTA top of the pool, sorry, top of the conference. So they were really strong. We were having a week a year and we ended up having, 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 30 tonight and you might be a powerful forward, but you're playing point guard.
(06:57):
And I remember we went into the top of the conference and came out with a win and 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. You've got guys that aren't playing that many minutes and suddenly you've just got to go into it and it doesn't matter if you're tired, you just got to find a way. And yeah, that was amazing.
Host: Paul Barnhurst (07:24):
Well, thank you for sharing. And I can imagine the whole COVID year is quite the experience.
Guest: Luke Phillips (07:28):
Yeah,
Host: Paul Barnhurst (07:28):
That'll be like bat for any of us, especially sports. I remember well all that and following it with sports. Alright, 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?
Guest: Luke Phillips (07:49):
Well, access Analytics, it's been around for 25 years and started off with Excel financial modeling 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 four 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 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 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.
Host: Paul Barnhurst (08:55):
Okay, cool. Yeah, no, I'm definitely familiar with Solver. I know it's 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 into some stuff new. So I'm curious, I know 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 favourite 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 the drivers in the different pieces of those industries.
Guest: Luke Phillips (09:33):
Yeah, for sure. I think that there's a couple, I've definitely gone across a range of industries. I did spend a fair bit of time in supply chain section of a company and that was really interesting because it's not necessarily, it wasn't as necessarily financial modeling focus, but it was really interesting understanding the realities of the reasons why people do things. And it's not always quite so simple, it's just 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 one type of template that then can find use across a variety of different areas. So yeah, those are probably the two
Host: Paul Barnhurst (10:29):
I like something you really said there with the supply chain is helping you understand the operational realities we've all seen, or you can model anything you want, but if you don't understand the operations and the constraints, what you modlled may look great on paper, but make no sense in reality. Have you experienced that as you're kind of building models where someone's like, this would never work and here's why?
Guest: Luke Phillips (10:50):
Yeah, no, a hundred percent. 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 in Interesting was around containers, and 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, it's a lot more than just how much are you moving? It becomes how much do you need storage on the site and wear and tear and it becomes a lot more nuanced than just purely what's the cycle time. So yeah, absolutely seen that.
Host: Paul Barnhurst (11:37):
Yeah, I think we all have, and I appreciate that example, the containers makes a lot of sense, all these different things. And how do you balance, this just makes, take a question. How do you balance 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? I know 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've built a few like that myself.
Guest: Luke Phillips (12:11):
Yeah, 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 make a best call. 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 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 at this is where the line is. So yeah, I think it's more an experience thing than anything else.
Host: Paul Barnhurst (12:56):
Yeah, it makes a lot of sense. So I 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 and the Power BI with modeling? What attracts you to that kind of combination?
Guest: Luke Phillips (13:19):
Yeah, I think that for me, I like solving problems. I like solving problems. I like when a client's got it, something that really is, it takes a long time for 'em 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. 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 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 do in financial modeling, 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 Excel and really bringing together the future. 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
Host: Paul Barnhurst (14:31):
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
Guest: Luke Phillips (14:41):
Exactly
Host: Paul Barnhurst (14:42):
On Power bi. So are you mostly building data models, doing DACs for reporting? What's the kind of work you're doing with Power bi?
Guest: Luke Phillips (14:53):
Yeah, so I think it depends whether I'm doing a financial reporting suite or whether I'm doing other areas. So like I said, I've worked on some environmental stuff, I've done some supply chain stuff. So I think that 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 I work quite heavily with Solver. Oftentimes it's the visualisation of the data that sits in that solver data warehouse.
Host: Paul Barnhurst (15:37):
Got it. So a lot of times people are taking Power BI on top of the solver data.
Guest: Luke Phillips (15:42):
So Solver more applicable for Excel based reports, more of a, hey, you'd put it in paper, but if you want dynamic graphs that are having that cross filtering capability, that's definitely where you want to be starting to use something more like Power bi.
Host: Paul Barnhurst (15:58):
When you start to get more in, you want it more standardised, you want all the cross filtering. It can be tough in Excel.
Guest: Luke Phillips (16:05):
Yeah. Oh no. Much better to use power beyond that case for sure.
Host: Paul Barnhurst (16:09):
Yep. No, totally get it. So you earned both your A FM and CFMI believe in short order. You did 'em both in the same year, if I remember correctly. So what motivated you during the certifications? What was the experience like? Talk a little bit about that.
Guest: Luke Phillips (16:24):
Yeah, so I think in terms of motivation, when I first started at Access, like I said, technical skills weren't necessarily there. And yeah, I remember my first day I had a, Jeff came along and he gave me a stack of books this big and we sent me this email with a whole bunch of training resources and it was pretty much just being in the office for two 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, 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 going to take that a FM exam just to kind of demonstrate the capability and managed to pass that one and went, oh, may as well knock out the CFM while I'm at it. So I think I booked them the CFM right after and yeah, managed to pass both. So it was a good experience. I loved doing both of them. They were definitely challenging and yeah, it was just an amazing experience.
Host: Paul Barnhurst (17:39):
That's great. I know a lot of people, the CFM is a hard one. They really 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 a hundred hours studying and he trains people to build financial models. So I know CFM is a really challenging one. I think A FM is great as well. I've done a FM, I haven't done CFM, I will admit it scares me a little bit and was supposed to do it this last year, but didn't have the time due to 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 a favourite book or one that really stuck out to you from that stack?
Guest: Luke Phillips (18:19):
There was a couple that I got into. There was a couple that I didn't actually end up finishing. I think that the one that, 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 if you're a financial modeler with the shortcuts and just the basic best practises, 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
Host: Paul Barnhurst (18:52):
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, but I mean first time I kind of saw his content and it was very early days of dynamic Rays and he was using the moult to try to sum things and here's the workaround I found for this, and I thought dynamic raises 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. This is painful. But he had figured out and was building some of the early models using dynamic arrays, and so that always stuck with me. It was clear he was pushing the boundaries and really trying to figure out what was possible. So I think you have 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.
Guest: Luke Phillips (19:47):
Yeah, no, for sure. Yeah, it's an amazing company to be a part of, to be able to learn from skilled guys at the director level. But everyone in the company very skilled. I learn every day.
Host: Paul Barnhurst (19:59):
That's awesome. No, it's great. Always great to be with smart people.
Guest: Luke Phillips (20:03):
A hundred percent. Yeah.
Host: Paul Barnhurst (20:05):
So something I found really interesting when we chatted, 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 at school. So I date myself where there weren't many functions in those early days. And so 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? How did that happen?
Guest: Luke Phillips (20:46):
Yeah, you're telling my embarrassing secrets out here.
Host: Paul Barnhurst (20:50):
It's embarrassing. I think it's great.
Guest: Luke Phillips (20:52):
Yeah, no, I think that I learned X lookup and once you have X lookup, you don't need index match. You can obviously use it. And there's definitely times where I've got a number series at the top and I'm using it to find lookup values 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 lookup and I feel comfortable with the following parts to it too, the if not found and 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 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 index match didn't come quite as naturally to me as X lookup. And yeah, I just like to use that from there.
Host: Paul Barnhurst (21:47):
While my background is in fact, 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 program podcast listeners save 15% on the AFM program. Just use Code Podcast.
Makes sense. I'm a big X lookup fan. I use it 90% of the time. There's occasional or I might use something else. Obviously I grew up on vlookup. I remember when I first learned index match, it's like, oh wow, this is cool. I can do this two-way lookup and this works so much better than the vlookup. I remember trying to tell some of 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. It's really not that much harder.
Guest: Luke Phillips (22:54):
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 just a small question and don't need to do it.
Host: Paul Barnhurst (23:11):
No, I totally get it. It took me probably six months to get totally comfortable with X lookup. 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 definitely a lot of it's just preference and what you use. So speaking of preference of fun things, do you have a favourite Excel shortcut?
Guest: Luke Phillips (23:39):
I've got a lot of shortcuts. I like. This might be a new one though. Alt WVGI do on any of my new grid lines. So any time I've got any type of new template, get rid of it. I just don't like looking at the grid lines. I don't anywhere.
Host: Paul Barnhurst (23:56):
I use that one all the time. Yeah.
Guest: Luke Phillips (23:59):
But no, I try and keep away from the mouse as much as possible. Shortcuts are king
Host: Paul Barnhurst (24:06):
Shortcut warrior. I get it. See, I like my mouse. I'm using it right now.
Guest: Luke Phillips (24:09):
It's good, but not quite as fast.
Host: Paul Barnhurst (24:12):
A hundred percent agree. I'm a mix. I used to use my mouse a tonne and then I learn 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 'em, so I get both sides of it. I just like to tease people that are the shortcut warriors.
Guest: Luke Phillips (24:30):
I learned some of the shortcuts early on. I reckon it might take me a little while to actually figure out where the grid lines button is. I learned the shortcut and it's all about what and if you know how to do it,
Host: Paul Barnhurst (24:43):
Have you ever seen, so you'll laugh at this. I'm going to show my keyboard here. Have you ever seen inch Nors keyboard?
Guest: Luke Phillips (24:48):
What? Does he have the F1 button ripped out?
Host: Paul Barnhurst (24:52):
No, it is completely blank. There are no keys on it.
Guest: Luke Phillips (24:55):
Oh, really?
Host: Paul Barnhurst (24:56):
Yeah, he prefers that he works faster. Just a black keyboard.
Guest: Luke Phillips (25:01):
Wow. Yeah, that's someone that's,
Host: Paul Barnhurst (25:03):
And he is a keyboard warrior, so 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. I think I could actually, I pretty much have memorised, but just the idea of it is so forward to me. I'm like, why would I do that?
Guest: Luke Phillips (25:23):
Maybe it's something to do with just not looking at the keys. If you're looking at the screen, you catch your errors a bit quicker. Yeah, I don't know. I've never learned the touch typing the proper way, so I'm sure I'd just do the wrong fingers to the wrong keys. So yeah, I don't know how that'd go for me.
Host: Paul Barnhurst (25:41):
So there's one for you when you talk about taking it to the extreme.
Guest: Luke Phillips (25:45):
Yeah, there you go.
Host: Paul Barnhurst (25:46):
So I am curious, we hear a tonne 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
Guest: Luke Phillips (25:58):
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 some of the basic build a model, sure. But oftentimes I find that you talk with a 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,
Host: Paul Barnhurst (26:37):
I remember installed on his computer. He had quite the journey on Lincoln telling you couldn't get it installed on his computer, if I remember right. Well,
Guest: Luke Phillips (26:46):
It worked by that point. And anyway, we put in some prompts and 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 actually got. So I think that vibe coding a financial model is a recipe for danger, especially if you're talking about valuations and M and these big transactions that rely on this. You just need someone that knows what's actually going on in the model. So haven't done it, bit scared of it to trust it, but it is cool to see how fast it's moving.
Host: Paul Barnhurst (27:28):
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. And the more customised it is, the harder it is for an agent to be able to do it. Some standard modeling. Sure, I am with you. I think we're seeing similar things. I think we'll get better. I think the day may get there, they can do it, but it's still never going to take away the need for us to know really well. You got to validate it all at a minimum.
Guest: Luke Phillips (27:54):
Yeah. Have you seen, when you were testing those models, did you see any of the models kind of come back to you and go, well, hey, what do you mean by this? Ask you questions as the prompt person. I think that that's what a normal financial modeler would do.
Host: Paul Barnhurst (28:09):
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 tonne 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'll 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 favourite 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 pretty much none of 'em balanced. Almost always had a balance sheet issued and we asked it to fix it, 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.
Guest: Luke Phillips (29:07):
And
Host: Paul Barnhurst (29:08):
It finally came back to us and said, that's close enough. I'm done looking. That's an acceptable variance where this is not cashflow reconciliation where $20,000 and 5 billion, no big deal. Just plug it and move on. You can't be a million out on a balance sheet.
Guest: Luke Phillips (29:25):
You can't be a dollar out on a balance sheet. This is not how it works.
Host: Paul Barnhurst (29:29):
I mean, if you're a penny out for rounding, whatever, but outside of our fine rounding error, you can't. Exactly. It has to balance. And we were just like, so that was probably our favourite moment. We're like, yeah, this isn't ready yet.
Guest: Luke Phillips (29:45):
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 fix this last little bit? And probably the answer more often than not might be this is going to need to rebuild.
Host: Paul Barnhurst (30:06):
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, that relationship. Why do you think you would never start over with an AI build model? So that's when I was data that 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? Waiting for a March Madness tournament, ultimate spreadsheet here. I'm
Guest: Luke Phillips (30:50):
Kidding. I probably haven't had the fun project that I've done. The one that I did, it was more of a 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 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 was like, oh, this is surely I can find a way to solve this. And yeah, ended up building a relatively complex lambda function that did it. I think my learning from that is that I kind of call the line the second I get to model circularity. 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 build the Lambda formula that actually solved that problem, but properly learned in that time that it's a bit too rigid for a solution for mass use.
Host: Paul Barnhurst (32:04):
Yeah, it's not something you're going to use in your day job, but it was cool to do.
Guest: Luke Phillips (32:08):
It was cool to solve the problem for sure.
Host: Paul Barnhurst (32:11):
No, that's awesome that you did that. I would've been like, I'm out back to tradition. Forget this.
Guest: Luke Phillips (32:17):
Yeah. Well it was hard a little bit to even explain to people what I was trying to do and the problem I actually had. Because circularity that you have just in the singular schedule, you can consult with scan or Craig Maker has got the 5G functions that do it. But yeah, this one was, it is 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
Host: Paul Barnhurst (32:47):
Bad financial models can lead to bad decisions or worse. So how do you minimise the risk of a bad model? You make sure the models you build are great Financial modeling Institute developed the Advanced Financial Modeler accreditation programme to help modelers like you. The AFM programme offers a step-by-step approach to building world-class financial models. The programme ensures that you know the best practises 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 a FMI accredited podcast listeners, save 15% on the AFM programme. Just use code podcast at http://www.fminstitute.com/podcast.. Alright, you ready for rapid fire?
Guest: Luke Phillips (33:55):
I'm ready for rapid fire. Let's go.
Host: Paul Barnhurst (33:57):
Here's the rules. Well just so everybody's on the same page here. Know it depends. You have to pick one side or the other and then you could elaborate at the end. I know there's some nuance. So you ready?
Guest: Luke Phillips (34:08):
Yep.
Host: Paul Barnhurst (34:09):
Circular references? Yes or no. VBA, yes or no?
Guest: Luke Phillips (34:14):
No.
Host: Paul Barnhurst (34:15):
Do you prefer a horizontal or vertical model? Layout.
Guest: Luke Phillips (34:19):
Vertical.
Host: Paul Barnhurst (34:20):
Okay. Use Excel dynamic arrays in models?
Guest: Luke Phillips (34:24):
Yes.
Host: Paul Barnhurst (34:25):
How about Foley Dynamic array models? Like we're a hundred percent are dynamic array.
Guest: Luke Phillips (34:31):
No,
Host: Paul Barnhurst (34:33):
I have a feeling you elaborate on Ms. Army. You're struggling with that one External workbook links, yes or no?
Guest: Luke Phillips (34:40):
No.
Host: Paul Barnhurst (34:41):
Good answer there. You can see my opinion named ranges, yes or no?
Guest: Luke Phillips (34:47):
Yep.
Host: Paul Barnhurst (34:48):
Do you follow a formal standard like Fast or Smart when you're modeling?
Guest: Luke Phillips (34:52):
Yeah.
Host: Paul Barnhurst (34:53):
Okay. Should financial modelers learn Python in Excel?
Guest: Luke Phillips (34:57):
No.
Host: Paul Barnhurst (34:59):
What about Power Query?
Guest: Luke Phillips (35:01):
Yes.
Host: Paul Barnhurst (35:02):
Power bi?
Guest: Luke Phillips (35:04):
No.
Host: Paul Barnhurst (35:05):
Okay. Will Excel ever die?
Guest: Luke Phillips (35:08):
No.
Host: Paul Barnhurst (35:10):
That's the most common answer. Do you believe financial models are the number one corporate decision making tool?
Guest: Luke Phillips (35:17):
No.
Host: Paul Barnhurst (35:18):
What is
Guest: Luke Phillips (35:19):
Humans? Maybe it's the most common tool. I'll give you a tool, but it's not the most common decider.
Host: Paul Barnhurst (35:27):
Yeah, I get what you're saying. I think my favourite answer on that is someone said, no, it's politics.
Guest: Luke Phillips (35:35):
It's not a bad actor. Not a bad actor.
Host: Paul Barnhurst (35:37):
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?
Guest: Luke Phillips (35:43):
Normal Excel X lookout.
Host: Paul Barnhurst (35:45):
Alright. So what about non-normal Excel? You said normal Excel power query?
Guest: Luke Phillips (35:50):
Sometimes if I'm using silver I might use some product as a lookup function.
Host: Paul Barnhurst (35:55):
Sure. Okay. Got it. I've definitely seen that done.
Guest: Luke Phillips (35:58):
Yeah.
Host: Paul Barnhurst (35:58):
Alright, go ahead and elaborate. I know there's one or two in there. Let's talk dynamic arrays. That one you were struggling with.
Guest: Luke Phillips (36:05):
Yeah, so I love that if you're doing three statement models, you can't do three 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 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'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.
Host: Paul Barnhurst (36:57):
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, but when we could do an array of an array,
Guest: Luke Phillips (37:09):
Array of an array would be really nice. And I would love, I think I got wind to push this to the MVP chat and 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.
Host: Paul Barnhurst (37:29):
Yeah, I'd be surprised if you get that one anytime soon. But if you do, that'd be cool.
Guest: Luke Phillips (37:32):
It would be cool.
Host: Paul Barnhurst (37:33):
I'll go look at the chat for the MVPs.
Guest: Luke Phillips (37:36):
Yeah, yeah, exactly. And then I had a couple of other ones. What was the,
Host: Paul Barnhurst (37:42):
So there was VBA circular references, external workbook links, named ranges. Python Power Query, power bi.
Guest: Luke Phillips (37:53):
Yeah, so external references only if you've got multiple people using it and you've got to hide 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 think a great thing to learn, but not essential.
Host: Paul Barnhurst (38:13):
Yeah, makes sense. I would agree with you on Power bi. I think for 95% of modelers power queries, 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 Powercor. So I'm with you on that one. I tend to agree with you on Power BI and Python as well.
Guest: Luke Phillips (38:32):
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,
Host: Paul Barnhurst (38:50):
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.
Guest: Luke Phillips (39:03):
So
Host: Paul Barnhurst (39:03):
You can import 'em 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 it as an array, but you have to refresh the data for it to refresh.
Guest: Luke Phillips (39:14):
So it's a non-volatile function, but you refresh it with the refresh button or you refresh,
Host: Paul Barnhurst (39:22):
Refresh it with a refresh button. It is an array. So you could bring in the data and use take or drop. I only want the first 10 rows or I want the last five rows or whatever. So I could see some situations where, hey, you're not really comfortable with Power Query or you just have something more, 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 a 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 got to go find that path and copy the whole path in at time. I'm like, I don't want to deal with this right now.
Guest: Luke Phillips (40:10):
And I feel like it's not added to over the Power query method.
Host: Paul Barnhurst (40:15):
I agree. They mentioned right in their notes, it's more for people that maybe are intimidated by Power Query or want to way to simply bring something in. If I know I'm not going to do any manipulation, okay, I could see where maybe in a hurry, maybe I go that route, but I'm with you.
Guest: Luke Phillips (40:30):
Yeah, yeah. It'll be interesting to see where that lands and whether people use it or not
Host: Paul Barnhurst (40:34):
For sure. And if they even release it, what they decide, what changes they make. So it was just kind of an interesting one. Alright, so let's go ahead and probably wrap up here. So just one last question. If I was to ask you what advice you would give for modelers to be a better modeler today, to get better at modeling, what's the advice you'd give?
Guest: Luke Phillips (40:53):
I mean, I've got two answers. So 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 works. I think the skill of financial modeling almost becomes how well can you synthesise 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 realise that modeling becomes an exercise in communication, working with people.
Host: Paul Barnhurst (41:39):
I love that. I might have to take that and put to the tagline for this episode, the more you learn, you realise modeling becomes a relationship and communication and working with people so often they think modelers, right? They're just a nerd that sits at their computer and pipes on a spreadsheet and it really is so much more than that. So 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 Yeah, LinkedIn. We'll go ahead and do that. 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.
Guest: Luke Phillips (42:20):
I appreciate it. Great to come online and talk. It's been a really good chat and we'll stay in touch for sure. Alright.
Host: Paul Barnhurst (42:25):
Financial Modeler's Corner was brought to you by the Financial Modeling Institute. This year, I completed the Advanced Financial Modeler certification, and it made me a better financial modeler. What are you waiting for? Visit FMI at www.FMInstitute.com/podcast and use Code Podcast to save 15% when you enroll in one of the accreditations today.