Excel Problem-Solving Strategies for Financial Modelers to Build Robust Models with Liam Bastick

Worst model you've ever seen or you've worked on.

One was for an investment bank. The problem is all the auditing tools fail because they find circular references, but they're for different circulars. And the other one, Big hospital complex. It had about 12 inputs on it, no labels, numbers everywhere. Apparently the bank said to them, I think you should have this model professionally.

What's the takeaway from those experiences?

Learn tricks because people are very inventive and you find some cool things that people do too. Steal those and pretend you always knew them.

What's that experience been like kind of running your own firm? And maybe how has it changed over the years?

Nobody will buy what you want to sell. The fact is, you've got to realize, you've got to keep reinventing yourself.

What is the most important skill that financial modelers should master?

You've got to be someone who knows how.

Welcome to Financial Modelers Corner, where we discuss the art and science of financial modeling with your host, Paul Barnhurst. Financial Modelers Corner is sponsored by Financial Modeling Institute. Welcome to Financial Modeler's Corner. I am your host, Paul Barnhurst, AKA the FPA 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.

Thanks very much, Paul. Glad to be here.

Yeah, excited to have you. So let me give a little bit of your background. Liam is managing director of Stump Product. Is that what you put in your hair?

No, it always looks especially. Anyway, don't worry.

And he is a seasoned, passionate corporate trainer specializing in strategy, decision analysis, Excel, financial modeling, power, BI and AI. He has published over 10 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, I think it's 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, Derby County. The American in the room now.

It's like, yes, yes, I, I freely admit I'm an ignorant American and I'm okay with that.

I'm an ignorant UK person that lives in Australia because they kicked me out of the UK now. So don't worry, I'll find somewhere else.

I'm waiting for them to kick me out of the us. I'm thinking I'll go to Canada next.

Sounds good. No, we've got friends there.

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.

Yeah, I was thinking about this before I came on. And I'm going to cheat. I'll cheat 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, 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, that was a. An investment bank that should have known better. I'll remain nameless if they pay me enough. And the other one was actually for the best part of a. In US dollars, probably about one and a half billion dollars. 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 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 that that was, it was, 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.

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, I would have been like you.

My hair was dark before that.

I, 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. 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.

I can't believe.

Yeah, I imagine you can believe that one if you're dealing with that many. It's not quite like circular references, but still not fun to work with for sure.

No, no, you can get a right mess with circulars.

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?

Well, it's more. We do this with all the models we get good and bad. So I run a modeling team and where I can. In the past, we used to have loads of models. I'd always try and put new staff on onto the auditing models to start off with. Not because I 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 too. 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, with the circulars, a sanitized version of that. I actually showed how not to build a model and why, because in the world of circulars, 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 quarter of the file size. So when we rebuilt it. But you know, 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 end. So if you knew at the beginning what you know at the end, you'd think, nah, I've never 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.

Yeah, I, I had to. So 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. We finally got to the point where it was appeared all to be working. The numbers were close on our forecasting, but we had this one circular reference. I had never, 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 them 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 one. You know those massive complex ones where you're like, you know, it's going to 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?

So, absolutely.

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?

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 3 that says, you know, I wish I could be Pacino and do that. You know, just when I thought I was out, they put me back in. I always seemed to get back in. I was doing this job I really enjoyed in strategy and things like this. Working on two big large clients who were doing a merger and acquisition in the uk and the day before they were signing the. The partner rang me up, I said, you need to come in. And I've just been advising and apparently the person that's 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. I'm not a complete to finisher but, 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 that sort of propels you to keep going and thinking. 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 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. And you think, yeah, it's good for three minutes and then you need the next fix.

Yeah, it's. It really is amazing the number of things people have solved in Excel. It. It's. It blows my mind sometimes what I've seen people do. I. 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.

Yes, I, I'll be glad if I don't do. I'm just thinking of something I saw recently which started Equals Drop Reduce Let Lambda Buy Row by Cole Hstack. Something other than I thought. I don't think I want to audit models anymore.

No, 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.

Keep it simple.

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?

I think, I think my favorite project actually was a strategy piece for a not for profit organization where we built them. Well, say we built a model. We. It was, 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 it. I'M 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. They had pieces of string scoping together a model and. And it's one of those things that.

Looks a 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 love seeing what people are doing.

It's.

It's exciting. And if you're not going to jump on the bad wagon, 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.

Yes.

All right, so you, you love writing books. I think you said you've written 10, you're writing one right now. What is it you love about writing books? Let's start there. Why do you, why do you write all these books?

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. 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 it's. I like to try and impossible 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. And 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, it's, 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.

I get it. Either somebody will enjoy it or the family will find another use for it.

Absolutely. Either way, Kindle.

I like it. Yep. One of the books you wrote is Financial Modeling in Power bi. You mentioned you were an early adopter with Power Query and Power Pivot prompted you write a book about financial modeling in Power bi. That's a interesting subject. Some people say, hey, don't do anything modeling in Power bi. Others like need to put your data there. How do you think about that? How does that kind of fit this whole modeling world nowadays?

I have all my best stroke stupidest ideas in the shower for some reason. So this was one that definitely originated in there.

Yeah. Bye. Don't want to know about your shower. I'm just kidding.

Well, at least I'm clean. It's suppose. But the, the fact was it occurred to me that if you, 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, others have tried to put 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 Power Pivot, 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. So the story goes. Now I might have the facts wrong, but the tale is still valid. Even if I got the facts wrong.

That they identify, I won't validate the facts. I won't put them in AI and fact check. You don't worry.

Well, it'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, well, that's the large end of town. I'm 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 N 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. I got stuck for three and a half weeks on calculating tax losses. That's. 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, but, 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, 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 down in there. But you've only got that many levels. This in power pivot which comes up in power bi. You've, you've got the N 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 FP and a guy here. And as you know, I am very passionate about financial modeling in the Financial Modeling Institute's mission. I have been a huge fan of the FMI for years and I was super excited when they decided to sponsor the Financial Modeler's Corner. I recently completed the advanced Financial Modeler certification and loved the entire experience. It was top notch from start to finish. I am a better modeler today for having completed the certification. I strongly believe every model 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 PODCAST to save 15% when you enroll in an accreditation today. Yeah. If you have the data, you can do it. You know what's interesting, as you kind of mentioned Power BI modeling, I'm working on right now a planning, 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 they're not necessarily focused on a three statement model. What they're trying to make a lot easier is inputs. If you can input stuff, drag it and make changes and write it back to the database. Power BI is a great tool to gather data for data input.

Yeah, I'm in two minds on that. I've thought about. 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 the things so they don't have to produce the bespoke reports, 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, the, 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, yeah, there.

Could be some concern on the IT side but there are multiple tools out there that are doing it right. Power On, Actaris, Lumel, AIM Plan and others that you can plan in your BI tool. And you know, it's an interesting because I come from the FP and 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 the it. Neither's 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 absolutely 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, 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?

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, you're looking, if you're an SME that's serious about trying to steal a march on your competitors competitive advantage and, and do the analysis in 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 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, there is a price to pay and I, I, 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 got 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. 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 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 xl. I love Excel. It's just, you know, it's just that they get stuck because they have to use 32 bit office.

Yeah, no, I remember I had 32 and I was doing a lot of power query stuff and I went to our it. I'm like, you got to give me 64. This just isn't working. This is several years ago and they did and I ran something, I'm like, oh wow. Like I should have done this earlier but there's definitely that challenge and you know, it's, it's interesting to watch. Right, the other thing, as you mentioned, Excel is limited in its dimensions. You got sheets, you got rows, you got columns, there's a few other things. Yeah, you could link something but it's not multi dimensional. No, as you mentioned with the Q. 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 and it's like you bought the software, it's running, you can validate the number at the end, but you're not always good 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.

Oh yes, there's plenty of those are out and everyone's a Reliant. Don't know how to do anything first. First stock is always vba. Oh yes. Let's, let's try this in vba. But I just want to add this cell to that cell. But yeah, VBA will work fine.

Yeah, 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. Use it a lot and you know, you always have two, two schools of thought nowadays, as 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' okay, the answer is probably in the middle, like with most things. What's your take?

Well, let me tell you a little story about when I first became an mvp. I'm not going off to Tangent. This is completely relevant while 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, I was fed up of going on all these forums where every single Excel question was answered with some vba. You know, it's just like there's a much simpler way of doing this. Have you heard of the sumproduct function or the if function or anything? You know, going through? There's A way and I mean 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 main named us because I think she should admit to that herself. But we stood up and I said, you know, I said, I think I started, I'm Liam, I'm an alcoholic. Because it felt like a bit of an intervention to start off with. And then I said, and I try 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. 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 ends in script, whatever you're going to use, you 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 module spreadsheets are still being used. I had to audit as well a lotus 1, 2, 3 sheet the other week. It was, you know, it's one of these things in there. I know, I know. I thought that was completely dead. People said, oh no, we still use it, 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.

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's going more and more online and if someday they go to a fully cloud based tool, they're gonna 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 put more and more things in place that allow us to work without vba. Whether, whether you want to admit that or not. It's clear they've done more and more of that. They've had to 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 could imagine what that would do to the infrastructure if tomorrow they said, all right, VBA is out, there'd be a, a riot in Seattle, in Redmond, there'd be some picketing and so 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 before we get there. What's your favorite Excel shortcut?

I knew you're gonna ask this one. Alt number. Are you gonna say what I. I am a firm believer in the Quick Access Toolbar. We all, we all need things in there. It'd be different things. I put up things like camera tools and all recommended charts, which I know there's other things in here. The, I think alt and the number that you put it up and setting it up in there for those. That 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 ALT number which might surprise you if you think, oh, that's. I don't know.

You're the first time we've got alt number. I probably, I know I should use the Quick Access toolbar more. I don't. And I know that I see everybody 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, don't tell anyone.

It's for those that are easy to find straight off the bat, you know, it's, it's those everyone's got things they use all the time that aren't Control A or Control 1 or, or something like this, you know, Alt EIU or something like that. I'm still on Excel 2003 shortcuts because I can't remember the 2007 ones. But. But the fact is I put things that I know I'm going to use all the time. So for me, alt 8 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 it? So.

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?

I've been recruiting people for many years and I don't care how good their programming skills are, their Excel, 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 you on the whole, you've either got it or you haven't unfortunately. But you know, it's a case 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, I've heard that. Another one I hear a lot is critical thinking. I think, you know, there's some overlap between the two for sure, but there's something a little different be able to solve that problem. But so I'm with you. I tend to agree those are some of the most important because people say, well, you have to have Excel. And I go, yes, you could 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 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 it's an option. It's another tool out there.

Something else come along and beat that soon. Don't know what it is, but it will.

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, some 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 for in your personal life?

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 you, the people who tune here will know what I'm talking about, where you have 30 seconds to make the longest word you can out of nine letters. 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 a computer where I programmed 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, 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 30 second timer. There you go. Which, which was a challenge when you have different processor speeds.

You know, Liam, bless your heart, you are a total nerd.

Absolutely.

I would even try. No, I can tell that that's great. I had one the other day that he built in the spreadsheet, a counter. There was 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, to track it for a show he watched. We get all kinds. It's, it goes back to the versatility of the tool that you could do just about anything.

Absolutely. That. 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, it's.

Yeah. So I, 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 medal, and have like 15 different things like procurement, software, commission, whatever, budgeting. Second place, second place. Second place for every single one of them. And they're all Excel. I. I'm with you. I totally get. 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. 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 it. 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 one or two. Because I recognize they're all nuanced for the most part, that it's not a simple. Hey, always do it this way.

I might break these rules. We'll see.

Yes. You wouldn't be the first. I don't know if you know, in. You know, in. That runs fmi, right?

Yeah.

In Schnorr, he. He's so cerebral. Was driving him crazy. He just wanted to keep adding. But, but, but I'm like, no, here's the rules.

I'll just try.

You won't be the first to break them.

I'm gonna break.

Circular references. Yes or no?

Well, for what we said before. No.

I figured as much. Vba, yes or no?

No.

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?

Doesn't matter. But agnostic.

We get some of those dynamic arrays in your model. Yes or no?

No, but I'll clarify at the end.

I'd like to clarify that one. I think we good. External workbook links. Yes or no?

Yes, I know. And I'll explain why at the end. I'm following your rules.

Named ranges. Yes or no?

Yes.

All right. Do you follow one of these formal standards for modeling like Fast, Mars and the others out there?

No. Well, we have our own, so. Yes.

Should financial modelers learn Python in Excel?

No. They can if they want to, but they don't have to.

Okay, how about Power query?

Yes, absolutely. 100%, definitely. Can I be any more equivocal on that one?

Now, was that a yes on Power Query? I just want to make sure I got it right.

Well, up in. 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.

You and. You and Ken Pulse are both a. Yes. Imagine that. Although Ken asked me on external work. Really?

Ken Pulse?

Goodness me. He has an opinion on something.

Look at me.

He did. I was shocked. On external workbook links, he's like, does that count? Power query leaks? No.

That's one of the reasons I've got a yes.

So should financial modelers learn power bi.

Yes.

All righty. Will Excel ever die?

Yes.

Will AI build the models for us in the future?

I don't know. Probably. I don't know. That's. If I knew the answer to that, I, I, I, I would be coming from my Learjet airplane there. But I have to say, I don't ever like saying anything is impossible. So I suppose, yes.

All right. There we go. How about she sell protection in models?

Yes.

What financial statement is most important? P L balance sheet or cash flow statement?

Cash flow.

Or you could be the guy on LinkedIn that wrote a big old article telling me there's no such thing as a cash flow statement.

That's very interesting. Let's debate that with all the people who went bankrupt because they took his advice.

I'm like, you can call it whatever you want, but rebuild cash flow. It was semantics. I'm like, I'm not even going to have this argument with you.

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, that's, that's the firm of it. Because they look at the profitability, they do what's called over trading, and they forget the cash. So therefore, cash is gender neutral, as I say these days.

That's, that's why 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. Well, a business of one. So I don't need an integrated three statement model. But I, I watch it closely. I know what my cash flow is going to look like. All right. Are financial models the number one corporate decision making tool?

Sadly, yes.

Interesting. And what's your favorite lookup function? What's your lookup function of choice? Index match, Index match or index X match?

No, index match for that reason, because it would be xlockup Or X match if it worked in all versions, but it doesn't. So it's index match because it's the most versatile.

I got it. If you just had Lotus 1, 2, 3. I understand. All right. I know you wanted to elaborate on a couple, so which ones do you want to elaborate?

Oh, most of them, but we'll go. Well, no, well, I know we don't have 10 hours for this, so going through first of all the dynamic arrays, not trying to plug a book. I've, I've. This was an eye opener for me. I, I was, I've written a book on financial modeling using dynamic arrays. Now I know people like Wynn Hopkins who I think's been on here as well have actually, I'm always being told, oh, win'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 formula. Because what happens a lot of the 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 using dynamic arrays for calculations like sorting data and data manipulation or what this I'm specifically talking about when you're building financial models. The, the problem is if you start with dynamic arrays, if you want true versatility in there, every single formula must be a dynamic array or 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 are 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 are being calculated all at the same time circular. And you end up 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 to say? Excel workbook links I said yes and you looked at me Surprised? There's the power query was the first I was thinking of and I see Ken's already beating me to that Was one reason. Well, 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 in order to keep the manipulation. Yes, you've got issues if 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 and everything and I've cracked it in three seconds to get all the confidential data out of there. It's. 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?

That's usually when people say yes, they say it's a necessary evil. You're not the first that made that art.

I'm sorry.

Because nobody likes them. I think everybody would like to get rid of them. 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.

What were the other ones of the contentious in here was formal standards in there? Look, there's nothing universal out there. I've actually written about or co written about 5 of those that are out there. Hey, I was young, I needed the money, I'm not photogenic. It's one of those things in 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 Board's 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, it's it. 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. Yeah, they'll game it. Any others in here about learning VBA in there? The reason being there is it's a language that can't evolve really. VBA if there's some version that will go online then maybe. But I think understanding how to tinker with the existing ones but trying 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 I When Xlock cup 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 504. There are 542 functions at the moment. It's quite sad. Bill Jelen and I have agreed this.

Is that counting beta are those counting the functions in beta or those542 general?

It's all the functions that are presently around I've had a bit of a debate whether things like Get Workbook and Evaluate and others should be in because you can still use them through name ranges but we excluded those. But there's pleasantly 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 tie trick if I may. At the end in terms of going through 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 and all of them. Which is why I went for that one. That's right. I was going to say that when XLOOKUP got announced so Joe McDade was putting that through. They had a list of all the people going about Ra ra ra Vlookup and all like this and everything. And they said but of course There are some MVPs who absolutely hate it. And all he had was a brilliant picture. I don't know where he got it from. That was just that me me glaring to 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. But. 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 30 seconds while you figure it. It's February.

I had to think about it for a second. 8th February, would you believe?

But the fact is, the reason everybody is thinking, oh, I get weird.

Yeah.

The fact is nobody ever gets asked that. And then when you've got things in order like that, the. The best function to look up, that is lookup. Because lookup will look up the largest value less than equal to what you're looking for. And if you put it in a table, it'll take the top row and the bottom row. So if you name the range, you just go, equals lookup, cell, reference, comma, name, arrange. 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.

Like I said, you've earned the nerd. Love it. All right, well, I think we're about out of time. We've, 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?

I was going to say watch Crime Watch, but that's probably not that. The sense of humor should not be applied there some product website. So it is the best way. That's that function that's no longer used because of dynamic arrays, apparently. But the, the fact is that some product.com you can find us all there. We've got a whole load of fellow nerds I'm always happy to share, 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 words. We put the translate function on that and now we can translate the numbers.

I haven't played with the translate function yet. So that, yeah, think outside the hedgehog.

That's my, that's my motto.

Now can I translate Roman numerals if I'm using the Roman function into regular numbers?

Haven't got a clue. I can try that afterwards. I don't know, but I reckon AI do that, if nothing else.

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 that. 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.

Do you want tomorrow's sports results? I can give them to you if you want. You know, you won't have to do this anymore. That Paul, I'll keep.

I'll keep that in mind. Unfortunately, I can't place any bets on it, so it won't matter. 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 back/podcast and use code PODCAST to save 15% when you enroll in one of the accreditations today.