Excel Problem-Solving Strategies for Financial Modelers to Build Robust Models with Liam Bastick
In this engaging episode of Financial Modelers Corner, host Paul Barnhurst, aka "The FP&A Guy," sits down with Liam Bastick, Managing Director of Sum Product and a highly respected figure in financial modeling and Excel expertise. Known for his complex modeling work, quirky sense of humor, and love for a good challenge, Liam shares insights into the nuances of financial modeling, discusses his favorite Excel tips, and reveals his fascination with the evolving role of AI in modeling.
Liam Bastick is a seasoned corporate trainer, author, and financial modeling expert, with a focus on Excel, Power BI, and AI applications. With a career that includes awards like Microsoft’s Most Valuable Professional and the inaugural Lifetime Achievement, international recognition, and extensive teaching, Liam provides listeners with both practical advice and fascinating anecdotes from his career.
Expect to Learn:
Excel optimization tips and learn how to effectively use Quick Access toolbar
Circular references and financial modeling horror story insights
Challenges and advantages of modeling in Power BI
How AI is changing financial modeling and what the future might hold
Liam’s approach to problem solving to stay at the forefront of innovation
Here are a few quotes from Liam Bastick:
“The best tip I can give is to put the tools you use most often on the Quick Access toolbar. It’s underused but incredibly helpful.”
“If you've got circular references in your model, you’re already on thin ice. They can lead to wrong answers and complete chaos.”
“With AI, you can do the front-end and back-end work in financial modeling, but it’s still not there yet for the mechanics in the middle.”
In this insightful episode, Liam Bastick shared his passion for financial modeling, bringing both humor and depth to complex topics in Excel, Power BI, and AI. His reflections on adaptability, continuous learning, and challenging the limits of traditional tools serve as powerful reminders for anyone in finance or data analysis.
Follow Liam:
Website - https://www.sumproduct.com/
LinkedIn - https://www.linkedin.com/in/liam-bastick-73a39114/
Follow Paul:
Website - https://www.thefpandaguy.com
LinkedIn - https://www.linkedin.com/in/thefpandaguy
YouTube - https://www.youtube.com/@thefpaguy8376
Follow Financial Modeler's Corner
Newsletter - Subscribe on LinkedIn-https://www.linkedin.com/build-relation/newsletter-follow?entityUrn=7079020077076905984
Sign up for the Advanced Financial Modeler Accreditation Today and receive 15% off by using the special show code ‘Podcast’.
Visit www.fminstitute.com/podcast and use the code “Podcast” to save 15% when you register.
In today’s episode:
[01:52] – Introduction to guest Liam Bastick
[03:44] – Liam’s Modeling Horror Stories
[08:32] – Liam’s Professional Journey and Challenges
[14:17] – Running a Business and Reinvention
[16:05] – Discussing AI’s Impact on Financial Modeling
[20:39] – Books and Teaching Tips
[35:50] – Rapid Fire: Excel Best Practices
[46:57] – Advanced Modeling Techniques and Final Tips
[54:16] – Contact Information and Wrap Up
Full Show Transcript
[00:01:11] Host: Paul Barnhurst: Welcome to Financial Modelers Corner. I am your host, Paul Barnhurst, aka The FP&A Guy. This podcast is where we talk all about the art and science of financial modeling with distinguished financial modelers from around the globe. The Financial Modelers Corner podcast is brought to you by the Financial Modeling Institute. FMI offers the most respected accreditations in financial modeling, and that is why I completed the Advanced Financial Modeler earlier this year. I'm thrilled to welcome to the show, Liam Bastick. Welcome to the show Liam.
[00:01:47] Guest: Liam Bastick: Thanks very much, Paul. Glad to be here.
[00:01:50] Host: Paul Barnhurst: Yeah, excited to have you. So let me give a little bit of your background. Liam is managing director of Sum Product. Is that what you put in your hair?
[00:02:00] Guest: Liam Bastick: No. It always looks messy. Anyway, Don't worry.
[00:02:05] Host: Paul Barnhurst: And he is a seasoned, passionate corporate trainer specializing in strategy decision analysis, Excel Financial Modeling, power BI, and AI. He has published over ten books and has written for many years for numerous accounting and actuarial institutes around the world. He has been awarded Microsoft's Most Valuable Professional for Excel, won the inaugural Lifetime Achievement Award for Financial Modeling 2021 from FMI, and was one of three finalists for the 2023 Lifetime Achievement Award in Excel. A finalist in his only entry to the Financial Modeling World Cup, Liam is a shy, retiring and unaccustomed to public speaking. Take that for what it's worth, having been a serial host of several events such as unlock Excel, Excel Summit South, Excel Virtually Global, and the Global Excel Summit. He has a truly dreadful sense of humor. I can attest to that and loyalty. He's an avid Derby County and England fan. I'm sorry.
[00:03:18] Guest: Liam Bastick: Derby County. I spotted the American in the room now.
[00:03:27] Host: Paul Barnhurst: I Freely admit I'm an ignorant American and I'm okay with that.
[00:03:30] Guest: Liam Bastick: I'm an ignorant UK person that lives in Australia because they kick me out of the UK now. So don't worry, I'll find somewhere else to get kicked.
[00:03:37] Host: Paul Barnhurst: I'm waiting for them to kick me out of the US. I'm thinking I'll go to Canada next.
[00:03:41] Guest: Liam Bastick: Sounds good. Now we've got friends there.
[00:03:44] Host: Paul Barnhurst: All right. We always like to have some fun with this one. Tell me that horror story. Worst model you've ever seen or you've worked on?
[00:03:52] Guest: Liam Bastick: Yeah, I was thinking about this before I came on. I'm going to treat, I'll treat a lot of your questions. Paul, I'll warn you now. I'm going to come up with two. I'll give you two horror stories at different ends of the spectrum. One was for an investment bank, which has the record number of circular references I've ever seen in a financial model at 28. If anyone can beat that, I will go through. And the problem is, all the auditing tools fail because they find circular references, but they're for different circulars and you get completely lost trying to figure out where it was. And that was great fun. In the end, we gave up and we had to rebuild it. So that was one at one end. And that was an investment bank that should have known better or remain nameless if they pay me enough. And the other one was actually for the best part of a in US dollars, probably about $1.5 billion big hospital complex in the subcontinent. And what happened was a bunch of architects in Australia tried to build a financial model. Now, as financial modelers, they were brilliant architects. They built a 15 kilobyte model for $800 million worth of debt. They wanted to raise it and had about 12 inputs on it. No labels, numbers everywhere. And I was quite impressed. Apparently the bank said to them, I think you should have this modeled professionally, and it was easy to understand compared to the other one, but I think for other reasons I would say that I'd give the joint award to both of them.
[00:05:21] Host: Paul Barnhurst: I like it. The 28 circular references. I can't even imagine trying to chase that thing out. I get why you started over. I would have been like you.
[00:05:30] Guest: Liam Bastick: My head would start before that.
[00:05:31] Host: Paul Barnhurst: I believe it, you know, I did have the guy who told me one time he had 48 external workbook links to different workbooks, and some of them were seven deep. So to get back to the first workbook, you had to go through seven different files. And I was just like, no, I'm just not doing it, he said. It was like six months before he found them all and fixed it.
[00:05:51] Guest: Liam Bastick: I can't believe that.
[00:05:52] Host: Paul Barnhurst: Yeah, I imagine you could believe that one. If you're, you know, dealing with that many. It's not quite like circular references, but still not fun to work with for sure.
[00:06:00] Guest: Liam Bastick: Not now. You can get a right mess with circulars.
[00:06:03] Host: Paul Barnhurst: Yeah, you can for sure. So what's the takeaway from those experiences? I mean, what did you take away from that model with all the circular references?
[00:06:12] Guest: Liam Bastick: Well, it's more we do this with all the models. We get good and bad. So I run a modeling team. In the past, we used to have loads of models. I'd always try and put new stuff onto the auditing models to start off with. Not because I hate or hated all my new hires, but more the fact that I'd say, look, this is how you don't build models and this is why we build them this way, but also learn tricks because people are very inventive and you find some cool things that people do to steal those and pretend you always knew them. That's probably should be my motto. I should put on my t-shirt, steal it, and pretend you always knew it. But it's a case of the circulars, are sanitized version of that. I actually showed you how not to build a model. And why? Because, you know, in the world of circulars, you just. It may not converge. It'll give you the wrong answer. People rely on it and they'll make terrible, wrong decisions, never mind have a terrible model. And rebuilding it sort of showed them that they needed to build it simpler. And I think the model was only about a quarter of the file size. So when we rebuilt it, you know as well as I do, and I'm sorry to just ramble on here, but any model you build, you build it a second time. It's always much, much better. Because if you knew at the beginning what you know at the end, you'd think, no, I'd never have done that. And so I can sort of sympathize with them. But 28 different circular references. I've never had anything come near that before or since.
[00:07:33] Host: Paul Barnhurst: I had so, you laughing on circular references. I spent six months with someone. We built this model, most complex model I had built. We had to learn the business as well. The data was a huge mess, so we finally got to the point where it was appeared to be working. The numbers were close on our forecasting, but we had this one circular reference. I never figured out where it was coming from in the model. I finally rebuilt it about a year later. By that point, I really knew the business. I was actually able to hold him accountable like it was, you know, 50 times better than that first model. But I just had to find a week or two because it was just what, you know, those massive, complex ones where you're like, you know, it's gonna take you forever to rebuild it. So you keep dreading doing it. But, you know, if you don't, you're not going to do what you really need to do. Yeah, it was one of those type. And I finally just said, forget it, I'm doing it. I was so, so happy when I got it done, but in the middle of building it, I was like, what did I get myself into?
[00:08:30] Guest: Liam Bastick: Yeah, absolutely.
[00:08:32] Host: Paul Barnhurst: So what attracted you to financial modeling? I mean, I know you do decision support and a lot of other things, but what is it you love about modeling?
[00:08:39] Guest: Liam Bastick: I'll let you into a little. It's a bit of an open secret here. I've tried many times to get out of it. And there's a scene from Godfather three that says, you know, I wish I could be Pacino and do that. You know, just when I thought I was out, they pull me back in. I always seem to get back in. I was doing this job I really enjoyed in strategy and things like this. They were working on two big, large clients who were doing a merger and acquisition in the UK, and the day before they were signing the partner rang me up and said, you need to come in. And I've just been advising and apparently the person that had been building the model, it was just a total wreck. And I had all night to rebuild it because they wanted to sign in the morning. And unfortunately I did it and everybody was happy, but I got a reputation I could never shake, which was, you know, oh, he's the modeler, go use him and so on. And it stayed with me ever since. So there comes a time in your life when you realize you need to put food on the table. You think, you know what? I'm a modeler. And the fact is, I like the challenge of the puzzles.
[00:09:42] Guest: Liam Bastick: I'm not a completer finisher, but I like the problems. There was one very recently where I spent three and a half weeks trying to solve something, and I got there in the end. I actually asked all the experts I knew all around the world and nobody could crack it, and I was delighted when I managed to finally do it and think, yeah, the old guy still got it. And it was nice to do that. So I suppose it's intellectual vanity and that sort of propels you to keep going and think. I think one of the things in it is that too often you hear in financial modeling, oh, you can't do that in Excel. No, no, no, you can't do that. You need something else. And I always think of two words that come to mind every time. Challenge accepted. It's you know, there isn't anything I have anyone's ever told me you can't do in Excel. I haven't found a way of doing it. I may have had to cheat and, you know, thought outside the hedgehog, as I say, but there'll be some way in there. You'll eventually crack it and find it. And then? Then you think, yeah, it's good for three minutes and then you need the next fix.
[00:10:41] Host: Paul Barnhurst: Yeah. It's really amazing the number of things people have solved in Excel. It blows my mind sometimes what I've seen people do. I see some formulas people write. I'm like, I'm never writing a formula like that. I know that works. Good on you for figuring that out. I'm not even sure I want to try.
[00:11:01] Guest: Liam Bastick: Yes, I'll be glad if I don't do it. I'm just thinking of something I saw recently which started equals drop. Reduce. Let lambda, by row by col stack, something or other. I thought, I don't think I want to audit models anymore.
[00:11:16] Host: Paul Barnhurst: I see some of the, if you know, Bo Ryan and some of the others that, you know, write some of the formulas they write, I just look at them and I just go, all right, good on you. That's not me. So I get it.
[00:11:29] Guest: Liam Bastick: Keep it simple.
[00:11:31] Host: Paul Barnhurst: What's your favorite project you've worked on? Is it a modeling project? More on the strategy side over your career? Favorite deal? Do you have a favorite project you've done?
[00:11:41] Guest: Liam Bastick: I think my favorite project actually was a strategy piece for a not for profit organization where we built a well. So we built a model. We it was we were trying to get money in for, I don't know if I can say who it is, so I won't. But it was for I based in Melbourne, Victoria in Australia, and it was for one of the not for profit agencies down here who was desperately trying to get more money for the arts. Of course, the trouble is that the money's coming. If they take the money there, then it's not going to be hospital beds, police, ambulance, firefighters, all those. So it's a tough call to move it. And they've been trying for years to get more money because they were going to have to close down and everything. And we thought of a different way of selling it to the Victorian Treasury. And what we did was we mind mapped it using PowerPoint. So this is before Visio and all these other things here. On New Year's Eve one year, we had a big wall, we had post-it notes, and we had pieces of string Scoping together a model, and it's one of those things that looks a nightmare when you actually see it on the wall.
[00:12:47] Guest: Liam Bastick: It can't be understood at the macro level, but you can understand it when you look at the bits in there. And the actual people I was with who really weren't quite sure what I was doing, the clients, they said, that's easy to build. And they went off and built it. And so I didn't. All I did was scope it, and I quite enjoyed that because it was quite a complex model. But with all the post-it notes string, New Year's Eve, and we heard the fireworks going off outside and everything, and they got an extra $25 million more than they were asking for as a result of it. It was, it was nice to do something which for once wasn't. And yes, we've done another one that makes the banks more money. And sorry for the banking clients who are listening at the moment. Yes, we love you too.
[00:13:25] Host: Paul Barnhurst: I did it so and so merged in the bank. Got 2% or what? You know, half a percent or whatever the number is. I totally get it. Versus we got 25 million for this charity. It's going to make a real difference in society. Not that deals don't make a difference. But it's just different. Plain and simple. I understand it, you know, sometimes you're like, oh, great. I lined some banker's pocket. That's wonderful. So somebody tells me you love a good challenge. It doesn't matter. Excel strategy games. You're a curious thinker and you love to try to make things work. That's fair.
[00:14:05] Guest: Liam Bastick: Yes. Somebody tells me it's not possible. That's it. I'm sold on it. I'm just. I've always been like that. But once I've solved it, I'm bored of it. It's move on to something else.
[00:14:16] Host: Paul Barnhurst: So speaking of that, I mean, I know about 15 years ago you started your own business. Yep. Sounds like you've done a number of different things. What's that experience been like? Kind of running your own firm and maybe. How has it changed over the years?
[00:14:29] Guest: Liam Bastick: We're looking at changing it again now. It's slowly but surely growing. Unlike a lot of people who start up their own business, I sort of had a cheap way in because I built up two other businesses beforehand, albeit for other people with their money. This time it was mine, so it's gone a bit slower, which has been a bit frustrating, but I sort of at least knew what I was doing because I got proof of concept and I'd done it twice before and I'm trying to get up there. But the fact is, you've got to realize you've got to keep reinventing yourself. You've got to listen, like nobody will buy what you want to sell. You have to sell what they want to buy. And one of the best bits of advice I've ever been given, and it's nothing to do with financial modeling or anything. It's the humble glass of water. This thing, if you're going off and you're listening to clients, the tip I'll give anyone who's trying to set up a business and win work from clients is look at the glasses of water you have before and at the end of the meeting. And the aim is that your glass is emptier than theirs is because that means they've done the talking.
[00:15:32] Guest: Liam Bastick: And if they've done the talking, you've done the selling. And I think that's one of the best tips I can give. And you have to keep reinventing yourself. So about 8 or 9 years ago, I discovered right at the beginning PowerPivot and Power Query. And we jumped onto that and we started telling all our clients, and we had the tagline trying to change the world, one accountant at a time, and we're off again. We are now going into the world of AI. So we've been watching what's been going on. I've got this is not a plug, it's just telling what it is because there's nothing to buy or sell here at the moment at all. I'm in the midst of writing a book, financial modeling with AI. Everybody wants to know, can you build a financial model with AI? At the moment, the jury's out on whether you can do all the mechanics in the middle, but you can certainly do the beginning part. We can get the scrape, the intro as the intro, the inputs. You can decide whether you've got all the key drivers, find out what the risk, the variability is, what other people are modeling, and at the other end you can do the interpretation, the what if analysis and so on. The mechanics in the middle you do simple things.
[00:16:29] Guest: Liam Bastick: You can get it to do a depreciation calculation. You can get it to do valuations, put them together, No large language models aren't there yet. They're falling over completely. But a year ago, I remember watching something that was Wall Street prep or somebody else like that doing a valuation, and they were just taking the Mickey out of, I think I won't say which AI it was, but it was, it was, it was one of them in there that just couldn't even get the numbers right because it was in effect, almost googling what's three times four in there to try and find out the answer because it didn't have a calculation engine. They've come a long way from there. And I think anyone that just keeps sitting and watching what's going to go on is in danger of being left miles behind. And in the last month alone, I've probably had ten inquiries for training and consulting on modeling an AI together. And you've just got to get on the bandwagon. And so that's what I try and do. Lead from the front. I'm older, but I keep trying to learn all the new stuff. So this is an old dog that's trying to learn new tricks.
[00:17:27] Host: Paul Barnhurst: You know, it is really amazing how far it's come. I think we all remember how terrible 3.5 was when it first came out. Was that math? You know, you'd ask it if you were two years older than your sister and she was half your age. How old is she at 36. It'd be like 72. And you're like, did I miss something? You know, those type of things.
[00:17:49] Guest: Liam Bastick: Have you tried the vanity thing? Paul I was going to say I did it when I was having a bit of an argument with my daughter. And apparently I'm going to get a knighthood by Queen, the Queen of England. Unfortunately, it is no longer. But it was said by Queen. And I was thinking, well, I didn't realize I was up for a knighthood, but I quite feel like Brian may lie to me and he can rock me or whatever. You know what it is? It's amazing how they hallucinate. And whilst it's funny, if it's something that's not so obviously stupid that comes up, there's a danger that businesses will actually believe. And I'll give you a little story. I've run some training courses, and one of the things I'd like to do is a vanity search for the companies that are in there. And it was about three weeks ago. We did it in-house for a particular company. And we just put into word because I think it works very well in word.
[00:18:37] Guest: Liam Bastick: And I was using copilot write a business plan for this company and, you know, make it ten pages and everything. Produced a brilliant business plan actually, because it's getting really quite good at that. But it mentioned a strategic acquisition. They were in the middle of making that had not been announced to the market, and somehow it had got hold of it. And people were thinking, well, hang on a minute, isn't this supposed to get stuff from donkey's years back and things like that? But it came up in there and there were people in the room that were employees there. This was an announcement to them. And so people were trying to say, oh, that's hallucinating. That's not true. But they said, actually, we don't know where it got that from, but that's a bit worrying. So it's interesting where AI is going and how much it's learning. Maybe Terminator is closer than we think.
[00:19:25] Host: Paul Barnhurst: I know. Let's see what the future's like. As one person said, I always like to say please and thank you to my AI model and hope that they'll remember when they're my overlords. You know, I just kind of laugh. That's Glenn Hopper. I don't know if you know him, but he has a new book coming out on the 29th of October, which will be out by the time this is released. But he did an AI in mastery book he just wrote. He did a deep corporate finance one. Before that, I co-host another show where we talk about AI and technology and where we're going, and it is exciting. I wish I do so many podcasts. I don't get as much time as I'd like in AI. I'm like, I would love to just spend a couple days digging in. I feel like I never have it. I mean, I spend time, I mean, I have multiple different subscriptions and I love seeing what people are doing. It's exciting. And if you're not going to jump on the bandwagon, you're just leaving yourself behind. That's the reality because it's not going away. You know, we can all debate when it will build a model. Can it do it? What can it do? All that stuff. Fine. But the reality is it makes you more effective today if you use it, right?
[00:20:38] Guest: Liam Bastick: Yes.
[00:20:39] Host: Paul Barnhurst: All right. So you love writing books? I think you said you've written ten. You're writing one right now. What is it you love about writing books? Let's start there. Why do you write all these books?
[00:20:49] Guest: Liam Bastick: Well, look, I started off life as a professional mathematician, but that was because I thought maths was the hardest. My best subject was always English. So I suppose I've gone back to what you know. You know. So in terms of, I don't think I'll ever be able to write a work of fiction. AI is much better at that than I am, as we both discussed just a minute or two ago. But I like to try and impart some of the tips and tricks I've learned over the years, because certainly if I look back to myself 30 years ago and look at now, I do things much more simply now than I did then. Part of that is Alzheimer's and senility and those things kicking in. But part of it as well is experience in the school of hard knocks. I think it's good to try and impart it, and if anybody listens or reads it then and gets some benefit out of it, then great. And if not, it's something for my daughter to look at and think, oh, that'll, that'll help with this wonky table I need to fix and do it that way.
[00:21:43] Host: Paul Barnhurst: I get it. Either somebody will enjoy it or the family will find another use for it. Either way.
[00:21:48] Guest: Liam Bastick: Kindle in more ways than one.
[00:21:51] Host: Paul Barnhurst: I like it. Yep. One of the books you wrote is Financial Modeling and Power BI. You mentioned you were an early adopter with Power Query, and Power Pivot prompted you write a book about financial modeling and power BI. That's an interesting subject. Some people say, hey, don't do anything modeling and power BI. Others, like you need to put your data there. How do you think about that? How does that kind of fit this whole modeling world nowadays?
[00:22:15] Guest: Liam Bastick: I have all my best stroke stupidest ideas in the shower for some reason. So this was one that definitely originated in there.
[00:22:22] Host: Paul Barnhurst: Yeah, I don't want to know about your shower. I'm just.
[00:22:25] Guest: Liam Bastick: Kidding. Well, at least I'm clean. It's supposed. But the fact was, it occurred to me that if you built a model in power BI. Now, to be clear, lots of people build models in Excel or some other software and then import them into power BI. I'm actually talking about building the model in power BI. Now, if you use Power Query and things like that, I think people like Ken Paulson and others have tried and built some of that stuff together. You're building up. I was actually thinking of using Data Analysis Expressions Dax to drill down, and I thought, you know, if you can do that, then the large end of town isn't going to come near you because they'll have all their systems anyway. But all the small and medium enterprises, the SMEs out there, they're going to think, this is something I can use. This will give me bang for buck. I can find out where the stories are. I remember years ago, I think Microsoft, when they were testing PowerPivot, Power Query, power BI all out. They were looking for large corporates around the world, and one of them was an airline industry over here. And I think by them testing this, they were giving them free insights as well. And I think so the story goes and I might have the facts wrong, but the, the, the tale is still valid, even if I've got the facts wrong that they identify.
[00:23:41] Host: Paul Barnhurst: I won't validate the facts, I won't put them in AI and fact checked you don't worry.
[00:23:46] Guest: Liam Bastick: Well, they'll be true if you do that. But one of the things here was they identified there was a missed opportunity attracting more single women to fly from Melbourne to Sydney midweek. And they did this. Apparently the profits went up a few percent as a result of this. And it got me thinking, you know, well, that's the large end of town doing this. But if somebody could get a small model and put it through, they can see for the mom and pop shops, the businesses that come out here, they can see what's working, what isn't, and have that sort of end dimensional analysis at their fingertips, that it's only the big end of town that's been able to get it. And that's what we did. We tried to build three way integrated financial models using Dax, and there are some challenges in there that I got stuck for three and a half weeks.
[00:24:35] Guest: Liam Bastick: On calculating tax losses. I know it's an exciting topic and I know you'd like me to spend 30 minutes on that. I promise I won't let you do 29. But the fact was, I was so pleased when I cracked that because nobody could figure it out. But it's one of these things that if people get that, it's going to change the whole dimension of financial modeling, because in a model you've got rows, you've got columns, you've got sheet tabs, and you can link it down seven levels or whatever you want to do, as you were mentioning before. And put it, put it all down in there. But you've only got that many levels. This is in PowerPivot which comes up in power BI, you've got a dimensional hypercube. You can do it in whatever levels you've got. If you want to know what your profitability is on a Thursday to left handed single women in Ipswich. You can do that.
[00:25:22] Host: Paul Barnhurst: FP&A Guy here. And as you know, I am very passionate about financial modeling and the Financial Modeling Institute's mission. I have been a huge fan of the FMI for years, and I was super excited when they decided to sponsor the Financial Modelers Corner. I recently completed the Advanced Financial Modeler certification and love the entire experience. It was top notch from start to finish. I am a better modeler today for having completed the certification. I strongly believe every modeler needs to demonstrate they are a qualified financial modeler, and one of the best ways to do that is through the FMI's program. Earning the accreditation will demonstrate to your current and future employers that you are serious about financial modeling. What are you waiting for? Visit www.fminstitute.com/podcast and use code Podcast to save 15% when you enroll in an accreditation today.
[00:26:28] Host: Paul Barnhurst: Yeah, if you have the data, you can do it. You know, what's interesting is you kind of mentioned power BI and modeling. I'm working on a guide around planning in power BI, right. Using Writeback capability. There's a lot of add on tools now where they're trying to automatically write the Dax, do all the calculations. And, you know, they're not as necessarily focused on a three statement model. What they're trying to make a lot easier is inputs. You know if you can input stuff, drag it and make changes, write it back to the database. Power BI is a great tool to gather data for data input.
[00:27:07] Guest: Liam Bastick: Yeah, I'm in two minds on that. Let me I've thought about it. There's many times we've wanted to write back functionality. Absolutely. But one of the things is that you probably noticed as Power Pivot, Power Query and Power BI have been more embraced. We found with our clients that IT departments are happily letting people run riot with connectors back to their things, so they don't have to produce the bespoke reports and the queries and things and let them go through. I think that will all die if it becomes right back capabilities. I think at that point they may then draw a hole, and I think it's going to be a double edged sword. I'm not trying to sound negative, I just see some dangers when the masses get right back capabilities, because there's no way IT departments will want that. And I think they might pull the rug from some of them after that. I know at the moment.
[00:27:58] Host: Paul Barnhurst: Yeah, there could be some concern on the IT side, but there are multiple tools out there that are doing it right. Power on actress Lumel Aim plan and others that you can plan in your BI tool. And you know, it's interesting because I come from the FP&A world, right? That's my background. That financial planning and analysis. Mostly large companies. And so it's interesting to watch because you do you pull all your data into a planning tool, or do you write back and have one source, that data warehouse sitting on top of your analytics. And they both have problems. And there's it there. Neither is perfect, but it's interesting to watch the convergence and the different ways people are, you know, trying to solve it within your BI tool. So it'll be fun to watch how that all plays out. So would you recommend, when would you recommend somebody do a model in power BI like you did it all with Dax? Is that something you think, makes sense in many cases or typically is it still. Hey, you probably should do that in Excel. How do you think about that?
[00:29:03] Guest: Liam Bastick: In Excel, it's simple and transparent. In Dax it's not, but it's what do you want to get out of it at the end. And do you trust the black box that comes through? I think if you're looking if you're an SME that's serious about trying to steal a march on your competitors. Get a competitive advantage and do the analysis here. Yes, you can try and go down the AI route to try and spot trends in there. But you're using Excel spreadsheets or something like that where it's only going to have so many dimensions, or you import it into power BI or something similar to Tableau. And the rest, and you try and go down that route. But if you've got control of something like this, you can see all the profitability and get that this is your model and there is a price to pay. And it does take time and it will end up black box at the moment, unfortunately, because it is complex, it's very similar to dynamic arrays in Excel. Actually they've got very similar problems. But once you crack them, it actually works quite well and you've just got more power at your fingertips. One of the problems a lot of people have is they're confined to a 32 bit environment because they're using a 32 bit management information system, and they can't link it with 64 bit features and things like that. Power BI lets you have 64 bit on top of a 32 bit world, so you've got more grunt at your computers. Everybody's got powerful machines now that can do this. They don't want to see the blue circle of death going on in Excel, which it often does, and nothing against Excel. I love Excel, it's just, you know, it's just they get stuck because they have to use a 32 bit office.
[00:30:32] Host: Paul Barnhurst: Yeah. No, I remember I had 32 and I was doing a lot of Power Query stuff and I went to it. I'm like, you got to give me 64. This just isn't working. This was several years ago. And they did. And I ran something and I'm like, oh wow. Like I should have done this earlier. But yeah, there's definitely that challenge. And you know, it's interesting to watch, right? The other thing is you mentioned Excel is limited in its dimensions. You got sheets, you got rows, you got columns. There's a few other things you could link something, but it's not multi-dimensional. No as you mentioned with the cube. Right. But the cube generally is a black box. You think of all the different planning tools where it's anaplan or planful, all these that you'll build their own models. Sometimes people are concerned, they're doing algorithms. You're like, well, I don't understand. I want to understand every little number. We're finance people. It's like you bought the software it's running. You can validate the number at the end, but you're not always going to see all the sausage making. And as modelers, we often want to see that and be able to validate it. And you know, Dax or some of the others, not always as easy, you know, in Excel, if it's built with good design, it's easy to trace out. If it's not built well, it's still a black box, right. 28 Circular references. I'm sure you felt like that thing was a black box.
[00:31:52] Guest: Liam Bastick: Yes, there's plenty of those around and everyone's relying. I don't know how to do anything. First. First stop is always VBA. Yes. Let's try this in VBA. But I just want to add this cell to that cell. But yeah, VBA will work fine.
[00:32:06] Host: Paul Barnhurst: You know, it's interesting. I'm not a big VBA. I've done a little bit some macros. It's just never stuck with me very well. I love Power Query. I use it a lot and you know you almost have to. Two schools of thought nowadays is you've seen more and more things come into Excel. You have those that love VBA and like it does everything. You have to always use it. And you have this kind of a newer group starting to come up. It's like VBA is a dead language and don't use it. And I'm like, okay, the answer is probably in the middle. Like with most things, what's your take?
[00:32:37] Guest: Liam Bastick: Well, let me tell you a little story about when I first became an MVP. I'm not going off on a tangent. This is completely relevant. Why I was smiling. One of the first articles I ever wrote for any magazine was Those Who Can do, those who can't use VBA, and it was a tongue in cheek look at. I was fed up of going on all these forums where every single Excel question was answered with some VBA, you know, it was just like, there's a much simpler way of doing this. Have you heard of the Sum product function or the if function or anything you know, going through there's a way and a me. But I remember when I first became an MVP, I went to Redmond and met a lot of peers of some are very good friends now. And there was myself and another one who I won't name was because I think she should, admit to that herself. But we stood up and I said. I think I started, I went, I'm Liam, I'm an alcoholic because it felt like a bit of an intervention to start off with. And then I said, I said, and I tried everything I can not to use VBA. And, I think that I'm too extreme. I think there are times you can and I know how to I don't give myself enough credit to know more, but the problem is that Excel is moving onto the web.
[00:33:51] Guest: Liam Bastick: Like it or not, we're going online and VBA architecture just can't cope there. So whether it's office scripts, JavaScript, TypeScript, whatever else, engine script, whatever you're going to use, you've got to come up with something else. Having said that, there are lots and lots of legacy spreadsheets out there from 9690 modules. Never mind VBA modules. Spreadsheets are still being used. I had to audit a Lotus 123 sheet the other week. It was, you know, it's one of these things in there. I know, I know, I thought, I thought that was completely dead. And people said, oh no, we still use it. You think you can use it. And the fact is in here there's a time and a place for them. And I am a firm believer that if it ain't broke, don't fix it. Why have you got to move on? If the VBA thing is working, you know, it's probably put man on the moon for all I know. And I know it wasn't around back then, but you know what I mean. And so I'm not hypercritical of it. What I'm concerned of is people in the past, I think, fell back on it too soon.
[00:34:51] Host: Paul Barnhurst: I could totally see that. I get what I get what you're saying. It'll be interesting to watch. I agree with you. There's no question Excel is going more and more online, and if someday they go to a fully cloud based tool, they're going to have to figure out how they phase out VBA. I don't want to be the one that figures that all out, because we know how many spreadsheets and what's all out there, but we know they've put more and more things in place that allow us to work without VBA. Whether you want to admit that or not, it's clear they've done more and more of that. They've had to deal with the online. And, you know, in an ideal world, would they want a fully cloud based version? I think so, I think that's where they'd like to go. But we all know it's not as simple as just turn a switch. We can imagine what that would do to the infrastructure of tomorrow. They said, all right, VBA is out. There'd be a riot in Seattle, in Redmond, there'd be some picketing. And so it will be interesting to watch for sure. All right, so we're gonna ask you some. We have some standard questions we like to ask. In a minute we'll get to our rapid fire, which is one of my favorite sections. But before we get there, what's your favorite Excel shortcut?
[00:36:01] Guest: Liam Bastick: I knew you were going to ask this one alt number. Are you gonna say what. I am a firm believer in the Quick Access toolbar, we all need things in there. It'd be different things. I put up things like camera tools and or recommended charts, which I know there's other things in here. I think alt and the number that you put it up and setting it up in there for those is the best tip I can give anybody that's watching this. If you've got something you use all the time, put it on the Quick Access toolbar, see what number it is, and away you go. So everyone's going to have their own favorites, but I recommend people under use that. I remember when it first came out, Microsoft said, oh, you can only put 30 things on there. And I put every single one I could find on there. It's like playing Space Invaders, bringing it down. I couldn't break it. I tried, I was trying to break it, but, yeah, I think outnumber, which might surprise you if you're thinking, oh, that's I don't know.
[00:36:52] Host: Paul Barnhurst: I think that would be the first time. You're the first time we've got alt number. I probably know I should use the quick Access toolbar more than I don't and I know that I see here we're using it. I'm like, I always go, I should really start using that. And then I never do. But don't hold it against me. Don't tell anyone.
[00:37:09] Guest: Liam Bastick: It's for those that aren't easy to find straight off the bat. You know, it's those everyone's got things they use all the time that aren't control a or control one or something less, you know, alt eiu or something like that. I'm still in Excel 2003 shortcuts because I can't remember the 2007 ones. But the fact is I put things that I know I'm going to use all the time. So for me, alt eight is recommended charts. You know, I'm not saying recommended charts is the best thing out there, but the fact is, I know for me that's what it is. And that's better than whatever recommended charts is on the ribbon. See, I can't even remember what it is now. But what do I know about Excel?
[00:37:49] Host: Paul Barnhurst: Well, I've heard you know, a thing or two. Maybe you've written some books or something. All right. What is the most important skill that financial modelers should master?
[00:38:00] Guest: Liam Bastick: I've been recruiting people for many years and I don't care how good their programing skills are, their Excel or any of that power BI, you can teach all that innate problem solving. You've got to be someone who knows how to solve a problem and have some disciplined approach to doing it, and being able to try and break it for me. And that's I'd love to say there's a great course out there that teaches it, but it's one of those you can try and practice and develop. But I think on the whole, you've either got it or you haven't, unfortunately. But, you know, it's a case of if you're not, then you're probably a master sportsman or something like that. Instead, you know, it's one of those things. But I think being able to solve a problem and being a self-starter with it.
[00:38:41] Host: Paul Barnhurst: Yeah, I've heard that. Another one I hear a lot is critical thinking, which I think, you know, there's some overlap between the two for sure, but there's something a little different being able to solve that problem. But so I'm with you. I tend to agree those are some of the most important. Like when people say, well, you have to have Excel. And I go, yes, you can argue you need it, but you don't need Excel to build a financial model. Is it the preferred tool? Sure, I can build it in causal Quantrix, Google sheets, Equals. That's just a tool having the logic and the problem solving and understanding the financial statements and being able to think in such a way that you could solve problems in my mind, is what is needed. So I'm fully with you and the tool you use is just a tool. I can train somebody to learn a tool. I mean, you built a full integrated three statement model in power BI. Most people wouldn't do that. But again, it's an option. It's another tool out there.
[00:39:35] Guest: Liam Bastick: Something else come along and beat that soon. I don't know what it is, but it will.
[00:39:39] Host: Paul Barnhurst: Yeah. And maybe, you know, AI will be building a lot of it for us. And I think then, you know, that critical thinking, problem solving becomes even more important. And that validation as it starts doing more and more of the technical things, I mean, it's already doing it in a lot of areas. All right. What's the most unique, creative, fun thing that you've ever done a model in your personal life.
[00:40:03] Guest: Liam Bastick: Hi, I'm Liam, I'm very sad. I have been on a couple of quiz shows in my life. I don't know if you have it in the States, but there's a program in Australia called Letters and Numbers. It's called countdown in the UK. Some of the people who tune here will know what I'm talking about. Where you have 30s to make the longest word you can out of nine letters, and then you have to do some numbers parts. And I sorted out a practice before I went on the quiz show, where I tested myself by pitching myself against the computer, where I programed Excel to work out what the longest word was in the English language. So I was using dynamic arrays. I was using all sorts of things for permutations and combinations, and that was the saddest thing I ever put together. And I did something similar for calculating the numbers, where you've got to try and get a target number from six. It's stupid things and it even had a built in 32nd timer. There you go. Which was a challenge when you have different processor speeds.
[00:41:03] Host: Paul Barnhurst: You know Liam, bless your heart. You are a total nerd.
[00:41:05] Guest: Liam Bastick: Absolutely. I'm proud of it.
[00:41:08] Host: Paul Barnhurst: I wouldn't even try it. No, I can tell that. That's great. I had one the other day that he built in the spreadsheet a counter. There is a show he watched that he wanted to count how many times it was saying profanity. There were so many in it. He'd never watched a show with so much profanity. So he built a counter into Excel to track it for a show he watched. So we get all kinds. It goes back to the versatility of the tool that you could do just about anything.
[00:41:35] Guest: Liam Bastick: Absolutely. That's exactly why. It's the thing I always say about Excel. It's the second best software in the world. And they say, for what I say, for what you can't afford, it's yeah.
[00:41:47] Host: Paul Barnhurst: So I saw someone put a image on LinkedIn that showed like 15 different software types, and every single one had Excel with like second place. And so I built something similar to that in Canva where I have a model below, you know, a little metal, silver metal and have like 15 different things like procurement software, commission and whatever, budgeting, second place, second place, second place for every single one of them. And they're all excel. I'm with you. I totally get it. And I share that every so often on LinkedIn. I might have to dig that one out again and share it here soon after this conversation. So I hear you. All right. We're going to move to our rapid fire section. So I'll give you the ground rules for this. I think you're familiar with only yes or no answers, even though I know many of those. You can say it depends. We're forcing you. If you have to pick one or the other, then when we get through all these, you can elaborate on 1 or 2, because I recognize they're all nuanced for the most part, that it's not simple, hey, always do it this way.
[00:42:48] Guest: Liam Bastick: I might break these rules. We'll see.
[00:42:51] Host: Paul Barnhurst: You Wouldn't be the first. I don't know if you know Ian. You know Ian that runs FMI right. Yeah. Ian Schnpor. He's so cerebral. It was driving him crazy. He just wanted to keep adding. But I'm like no, here's the rules.
[00:43:05] Guest: Liam Bastick: I'll just try.
[00:43:06] Host: Paul Barnhurst: You won't be the first to break them.
[00:43:08] Guest: Liam Bastick: I'm going to break them. I know it.
[00:43:11] Host: Paul Barnhurst: Circular references. Yes or no?
[00:43:13] Guest: Liam Bastick: Well, what we said before. No.
[00:43:15] Host: Paul Barnhurst: I figured as much. Vba, yes or no?
[00:43:18] Guest: Liam Bastick: No.
[00:43:19] Host: Paul Barnhurst: Do you prefer horizontal, like multiple sheets in a financial model or more of a vertical layout where you have all your schedules for the most part, on one sheet?
[00:43:29] Guest: Liam Bastick: Doesn't matter. But agnostic. Can't do that.
[00:43:32] Host: Paul Barnhurst: We get some of those dynamic arrays in your model. Yes or no?
[00:43:38] Guest: Liam Bastick: No. But I'll clarify at the end.
[00:43:40] Host: Paul Barnhurst: I'd like to clarify that I think we're good. External workbook links. Yes or no?
[00:43:45] Guest: Liam Bastick: Yes and again. I'll explain why at the end. I'm following your rules.
[00:43:52] Host: Paul Barnhurst: And named ranges. Yes or no?
[00:43:55] Guest: Liam Bastick: Yes.
[00:43:56] Host: Paul Barnhurst: All right. Do you follow one of these formal standards for modeling like fast, smart and the others out there?
[00:44:03] Guest: Liam Bastick: No. Well, we have our own, so. Yes.
[00:44:05] Host: Paul Barnhurst: Should financial modelers learn Python in Excel?
[00:44:09] Guest: Liam Bastick: No. They can if they want to, but they don't have to.
[00:44:12] Host: Paul Barnhurst: How about Power Query?
[00:44:13] Guest: Liam Bastick: Yes. Absolutely. 100%. Definitely. Can I be any more equivocal on that one?
[00:44:20] Host: Paul Barnhurst: Was that a yes on Power Query? I just want to make sure I got it right.
[00:44:23] Guest: Liam Bastick: It could well have been. I'll go back to that one afterwards because that's a pet. That's a pet peeve of mine. I'll go on about that in a minute.
[00:44:30] Host: Paul Barnhurst: You and Ken Pulse were both a yes. Imagine that. Although Ken asked me on external work.
[00:44:35] Guest: Liam Bastick: Really? Ken Pulse, goodness me. He has an opinion on everything.
[00:44:41] Host: Paul Barnhurst: I mean, he did, I was shocked.
[00:44:43] Host: Paul Barnhurst: An external workbook link. He was like, does that count? Power query links? No.
[00:44:50] Guest: Liam Bastick: Well, that's one of the reasons I've got a yes.
[00:44:52] Host: Paul Barnhurst: For sure. Should financial modelers learn power BI?
[00:44:57] Guest: Liam Bastick: Yes.
[00:44:58] Host: Paul Barnhurst: Alrighty. Will excel ever die?
[00:45:00] Guest: Liam Bastick: Yes.
[00:45:01] Host: Paul Barnhurst: Will AI build the models for us in the future?
[00:45:05] Guest: Liam Bastick: I don't know, probably. I don't know. If I knew the answer to that, I would be coming for my Learjet airplane there. But I'll have to say, I don't ever like saying anything is impossible, so I suppose. Yes.
[00:45:19] Host: Paul Barnhurst: All right, there we go. How about cell protection in models?
[00:45:23] Guest: Liam Bastick: Yes.
[00:45:23] Host: Paul Barnhurst: What financial statement is most important? P&l balance sheet or cash flow statement?
[00:45:29] Guest: Liam Bastick: Cash flow.
[00:45:30] Host: Paul Barnhurst: Or you can be the guy on LinkedIn that wrote a big old article telling me there's no such thing as a cash flow statement.
[00:45:36] Guest: Liam Bastick: That's very interesting. Let's debate that with all the people who went bankrupt because they took his advice.
[00:45:42] Host: Paul Barnhurst: I'm like, you can call it whatever you want, but we build cash flow. It was semantics. I'm like, I'm not even going to have this argument with you.
[00:45:50] Guest: Liam Bastick: If you're setting up a company, everybody gets swayed by profitability and then goes bust. 82% of all companies that go bust in the first five years don't start off with enough cash. That's the fun of it. Because they look at the profitability, they do what's called overtrading and they forget the cash. So therefore cash is gender neutral, as I say these days.
[00:46:11] Host: Paul Barnhurst: There's a reason I review my invoices and bank account every week. And I know what my credit card balance is and all those things. Exactly. A business of one. So I don't need an integrated three statement model, but I watch it closely. I know what my cash flow is going to look like. All right. Our financial model is the number one corporate decision making tool.
[00:46:32] Guest: Liam Bastick: Sadly, yes.
[00:46:33] Host: Paul Barnhurst: Interesting. And what's your favorite lookup function? What? What's your lookup function of choice?
[00:46:39] Guest: Liam Bastick: Index match.
[00:46:40] Host: Paul Barnhurst: Index match or index X match?
[00:46:43] Guest: Liam Bastick: No index match for that reason. Because it would be Xlookup or X match if it worked in all versions, but it doesn't. So its index match because it's the most versatile.
[00:46:51] Host: Paul Barnhurst: I got it. If you just had Lotus one, two, three, I understand. All right, I know you wanted to elaborate on a couple. So which ones do you want to elaborate on?
[00:47:01] Guest: Liam Bastick: Most of them, but we'll go. Well, no, I know we don't have ten hours for this, so going through. First of all, the dynamic arrays not trying to plug it, but this was an eye opener for me. I've written a book on financial modeling using dynamic arrays. Now, I know people like Wayne Hopkins, who I think has been on here as well. Actually, I'm always being told, Wayne's done that and things like this. The difference was, I wanted to build a financial model that only had dynamic arrays in and had no other nothing other than dynamic array formulae. Because what happens a lot of times people will build a financial model using dynamic arrays, and then for the bits that they can't figure out how to do, like tax losses or working out what the maximum dividend is, they'll go back to Legacy Excel calculations and that's cheating. And I decided to do this and it. It made me realize when I was doing this that I wrote the book, if you're going to go down dynamic arrays. Now, I'm not talking about when you're using dynamic arrays for calculations like sorting data and data manipulation or like this I'm specifically talking about when you're building financial models. The problem is if you start with dynamic arrays, if you want true versatility in there, every single formula must be a dynamic array. Otherwise it will fall over. And the problem why I said no is because, in the book and others, I've got calculations that are horrendous for dividends, depreciation, tax losses and maximum dividend calculations because you end up with circularities that you don't realize as circularities, because when you're doing what, whether you do a control account or not, you have calculations where you've got opening debtors plus sales, less cash receipts gives closing Debtors problem is closing debtors equals opening debtors, and both have been calculated all at the same time, circular and you end up.
[00:48:50] Guest: Liam Bastick: Then some of them are easy to crack. There's other ways around it. Some of them are a nightmare and therefore legacy. Excel is much, much easier to calculate. Hence I said no to that one. What else did I say? Excel workbook links I said yes and you looked at me surprised. The Power Query was the first one I was thinking of. I see Ken's already beaten me to that. It was one reason. But there's another good reason. Sometimes you've got confidential data in a file and you're building things collaboratively and you need inputs. You send them a file to input it, and then you bring it in. And in order to keep the manipulation, yes, you've got issues with people. Insert rows and columns. I get all that. But you've got to protect confidentiality. The number of times I've seen people hide very hide put macros on everything, and I've cracked it in three seconds to get all the confidential data out of there. It's a much bigger risk. It's not that I particularly like links to a workbook. They are a necessary evil you require. What else was there in here?
[00:49:48] Host: Paul Barnhurst: That's usually when people say yes. They say it's a necessary evil. You're not the first that made that argument.
[00:49:54] Guest: Liam Bastick: I'm sorry.
[00:49:55] Host: Paul Barnhurst: Because nobody likes him. I think everybody would like to get rid of him. They're a headache, but I can get it. I've used them. There are times when they might be the easiest or best solution you can come up with, not the ideal solution.
[00:50:08] Guest: Liam Bastick: What were the other ones of the contentious in here? Was formal standards in there that, look, there's nothing universal out there. I've actually written about or co-written about five of those that are out there. Hey, I was young. I needed the money. I'm not photogenic. It's one of those things here. But the fact is that I've tried to break it down to four words and it's consistency, robustness, flexibility and transparency. And that's what we live by at our company. So it's not the whole standards, the whole thing. I was involved in the spreadsheet standard review boards. Best practice spreadsheet modeling standards, which proves I've not had an alcoholic drink before I came here. I can say all that in one go. It was a 500 page document, and I can tell you only three people have read that, and they're probably the three people that wrote it. You know, people won't follow it. They don't like rules. And some of the modeling standards out there, I could mention they're too prescriptive and people will just yes, they'll game it.
[00:51:00] Guest: Liam Bastick: Any others here about learning VBA in there? The reason being there is that it's a language that can't evolve really VBA. If there's some version that will go online, there may be, but I think understanding how to tinker with the existing ones but try to use something else is fine. And going back to choose Vlookup and all those, I'd like to put a special shout out to the lookup function. Lookup is brilliantly elegant. When Xlookup got announced at Redmond, Joe McDade was explaining it to everybody and they had all the people who love Vlookup. So Vlookup, no big secret. It's number three in the most used functions out of 542 functions in Excel. It's sad. I know there's 540. There are 542 functions at the moment. It's quite sad. Bill Jelen and I have agreed this.
[00:51:43] Host: Paul Barnhurst: Is that counting beta? Are those counting the functions in beta or those 542? General.
[00:51:48] Guest: Liam Bastick: It's all the functions that are presently around. I've had a bit of a debate whether things like get a workbook and evaluate and others should be in, because you can still use them through name ranges, but we excluded those. But there's presently 542 in there that excel. And special shout out to Charles Williams who found me a whole load I wasn't aware of in Thai. So I might finish with a Thai trick, if I may, at the end. In terms of going through that, that was quite useful. But Xlookup supposedly works in 2019. If you read the Microsoft documentation, we can't make it work there. We think it only works in 2021 onwards. Plus 365 and online. Otherwise it's great. Except you can't look up a row with a column unless you use the transpose function. Index match gets round that, and it's versatile in all of them, which is why I went for that one. That's right. I was going to say that when Xlookup got announced.
[00:52:39] Guest: Liam Bastick: So Joe McDade was putting that through. They had a list of all the people going rah rah rah, Vlookup and all like this and everything. And they said, but of course, there are some MVP's who absolutely hate it. And all he had was a brilliant picture. I don't know where he got it from. That was just me. Me glaring at it and the whole room laughed. I still remember that. It's quite funny. It was one of those things in there and Vlookup gets forgiven. It's volatile. People don't remember this. It's like, you know, they'll still use it, but the fact is, I like lookup because while Vlookup has got problems, I can break Vlookup a lot of the time. In financial models you need dates, and dates are always in chronological order. The simple challenge here is Paul. What's the fourth month of the year? Alphabetically, we'll edit out the next 30s while you figure it out. It's February.
[00:53:25] Host: Paul Barnhurst: I had to think about it for a second. It's February, would you believe?
[00:53:28] Guest: Liam Bastick: But the fact is, the reason everybody is thinking.
[00:53:32] Host: Paul Barnhurst: Oh, I get what you're saying. Yeah.
[00:53:35] Guest: Liam Bastick: The fact is, nobody ever gets asked that. And then when you've got things in order like that, the best function to look up that is lookup. Because lookup will look up the largest value less than or equal to what you're looking for. And if you've got it in a table, it'll take the top row and the bottom row. So if you name the range, you just go equals look up cell reference common name a range. Done. That's it. No other formula is that short for looking it up. And it's I just find that so beautifully elegant but you know, bit sophisticated.
[00:54:01] Host: Paul Barnhurst: Like I said, you've earned the nerd title. Love it.
[00:54:06] Host: Paul Barnhurst: All right, well, I think we're about out of time. We've taken our hour here. It's been a real pleasure getting to chat with you. I've really enjoyed it. So last question. If our audience wants to learn more about you, maybe get in touch. What's the best way for them to do that?
[00:54:23] Guest: Liam Bastick: Well I was going to say watch Crime Watch, but that's probably not. The sense of humor should not be applied there Sum Product website. So it's the best way. That's that function that's no longer used because of dynamic arrays, apparently. But the fact is that sumproduct.com You can find us all there. We've got a whole load of fellow nerds. I'm always happy to share tricks and tips and so on. And every now and then we came. We come up one which strikes gold, which is going back to that tie thing. I was going to tell you where we finally found a use for Bart text. So the Bart text is the one that will actually turn a number into the word. We put the translate function on that, and now we can translate the numbers.
[00:55:01] Host: Paul Barnhurst: I haven't played with the translate function yet so yeah.
[00:55:05] Guest: Liam Bastick: Think outside the hedgehog. That's my motto.
[00:55:08] Host: Paul Barnhurst: Now can I translate Roman numerals? If I'm using the Roman function into regular numbers?
[00:55:13] Guest: Liam Bastick: I haven't got a clue. I won't. I can try that afterwards. I don't know, I reckon I do that, if nothing else.
[00:55:20] Host: Paul Barnhurst: Oh yeah, I'm sure AI would do it. I was just trying to think because I know the translate function is new. All right. Well, thank you for joining the Financial Modelers Corner also known as nerds hour. Really enjoyed it. I hope our audience enjoys it as much as I did, and you have a good rest of your day. I'm going to bed because, see, it's yesterday for me.
[00:55:41] Guest: Liam Bastick: Tomorrow's sports results. I can give them to you if you want. You know, you don't have to do this anymore. Then, Paul.
[00:55:46] Host: Paul Barnhurst: I'll keep that in mind. Unfortunately, I can't place any bets on it, so it won't matter. Financial Modelers Corner was brought to you by the Financial Modeling Institute. This year, I completed the Advanced Financial Modeler certification and it made me a better financial modeling. What are you waiting for? Visit FMI at www.fminstitute.com/podcast and use code Podcast to save 15% when you enroll in one of the accreditations today.