The FP&A Guy

View Original

Beyond the Numbers: Lance Rubin's Insights on AI, Power BI, and the Future of Financial Modeling 

Guest User
See this content in the original post

Show Notes

In this episode, Paul Barnhurst is joined by Lance Rubin, who has a wealth of financial modeling experience including working for PwC & KPMG, Investec Bank Corporate Finance & Advisory, National Australia Bank, and starting his own practice. 

Lance spent two decades working for corporations (PwC & KPMG, Investec Bank Corporate Finance & Advisory, National Australia Bank). It was during this time he gained a love of modeling.  

 Lance was previously the CFO of fin-tech start-up Banjo (SME lender) and Sequel CFO whilst founding Model Citizn, a financial modeling, analytics and automation consultancy firm following his 20 years in corporate. He has delivered a number of online training workshops in financial modeling and Power BI whilst also being a certified trainer for the FMI and he wrote a large portion of the CA (ANZ) study guide on financial modeling. He was also a judge at the world’s first Financial Modeling Innovation Awards and he presented at the Power BI Global Summit in 2022. 

Listen to this episode as Lance shares:  

  • His learning from the worst models he has come across. 

  • His journey into Financial Modeling and how he fell in love with modeling. 

  • The key to Financial Transformations. 

  • The HACK Framework. 

  • The importance of Power BI and similar tech. 

  • His advice on the use of tools and shortcuts. 

  • His position on controversial modeling issues, including circular references, dynamic arrays, modeling standards, AI in modeling, and more. 

 Quotes:  

“Financial Transformation is the combination of process, tech and people. With people being the most important.”  

“The HACK Framework (Hygiene, Automation, Capability, Knowledge) allows you to bring technical skills and soft skills. together....You need to develop capability and knowledge and you need to bring that together with hygiene and automation.”   

Sign up for the Advanced Financial Modeler Accreditation or FMI Fundamentals Today and receive 15% off by using the special show code ‘Podcast’. 

Visit www.fminstitute.com/podcast and use code Podcast to save 15% when you register.  

Go to https://earmarkcpe.com, download the app, take the quiz and you can receive CPE credit.  

Images from Episode:

 

Follow Lance:    

Follow Financial Modeler's Corner  

In today’s episode:  

(00:22) Intro; 

(00:47) Welcoming Lance; 

(01:00) The worst financial model Lance has ever seen; 

(05:14) Takeaway from the worst financial model; 

(08:02) Lance’s background; 

(11:27) Key to Finance Transformation; 

(17:37) The Hack Framework;  

(18:43 - 19:29) Validate your Financial Modeling Skills with FMI’s Accreditation Program (ad); 

(19:30) What led to Lance’s love for Modeling?;  

(22:38) The most interesting model;  

(26:30) Importance of Power BI and similar Tech;  

(31:51) AI and Financial Modeling; 

(36:10) The learning that saved Lance a lot time while Modeling;  

(41:16) Rapid Fire;  

(46:58) Connect with Lance; 

(48:15) Outro; 

Full Show Transcript

Host: Paul Barnhurst 

 

Welcome to Financial Modeler’s Corner, where we discuss the art and science of financial modeling with your host Paul Barnhurst. Financial Modeler’s Corner is sponsored by Financial Modeling Institute. 

 

Welcome to Financial Modeler's Corner. I am your host, Paul Barnhurst. This is a new podcast where we talk all about the art and science of financial modeling with distinguished financial modelers from around the globe. 

 

The Financial Modeler’s Corner podcast is brought to you by Financial Modeling Institute. FMI offers the most respected accreditations in financial modeling. I'm thrilled to welcome on the show Lance Rubin. 

  

Lance, welcome to Financial Modellers Corner!  

 

Guest: Lance Rubin 

 

Thanks, Paul, and great to be here.  

 

Host: Paul Barnhurst 

 

Yeah, really excited to have you. So Lance is joining us from Australia here. And the first question we like to ask each modeler is tell me about the worst financial model you've ever seen in your career.  

 

Guest: Lance Rubin 

 

So Paul, we've seen a lot, and we've worked with a lot of different clients and their models. And so rather than focusing on one, I'd actually like to just give a summary of, you know, we use tools to assess what I call hygiene and I'll talk a little bit more about hygiene in spreadsheets and models. We assess them according to various criteria, number of ref errors, number of hard-coded formulas, hidden columns, you know, external links, and all of those sorts of things. So I'm going to just call out of the 16 files that we anonymized and I've used for this discussion, I'm going to give you some of the worst, which is the max that we've found out of that 16. So in one of the files, we had 79,000 hidden columns, so that's columns that have been hidden. You know, we've got 24 ref errors, 17103 hard-coded formulas. So that's the maximum in one of those files. Formula with intersheet links, 24000. Formula with functions, 42000. Formula with text, 14000. 56 external links. Now that might not be a lot, but each of those are different sources and different links, and the way that it calculates, if it's the same formula and the formula is correct and it's just one source, that's one link. So it's not like individual links that's 56 different types of links externally. So I'd say, you know, if I look at, and unfortunately, I can't share the screen, but I'll share the graphic after the chat and we can talk about it, but, you know, I said, well, let's count of the files, of the 16 files, what's the percentage of files that had things like hard-coded formulas? And I'm gonna say hard-coding is probably the single most challenging issue and difficult issue that we need to face into. And it's a 100%. 100% of the files that we looked at, every single one of them have hard code.  

 

Now, there is an exception to that. I'll accept binary. I'll accept a 1 and a 0 in a formula, because if something, then 1, if not 0 for flags. That's the only exception to that hard code. But I will not accept 1, 2, 3, 4, 5, 0.678, or 12.5%, or even the number 12, which might mean number of periods, or it might mean number of divisions.  

 

What is it? A hard coding is just an app. When I listen to all your other guests, because I listen to your podcast often, and I always listen to guests, and a lot of the riddled with hard coding is often the challenge, lack of transparency through external links and a range of other things. I'd say that each of the different client models have different challenges, and I'd say the single worst thing is the hard coding. It's just phenomenal how literally every single file that we've ever looked at had elements of hard coding.  

 

Host: Paul Barnhurst 

 

Yeah. It's amazing how prevalent it is, especially if it's a model that's been used for a long time. You know, if it's a brand new model, okay, maybe, but operational models, they just creep in and keep creeping in, it seems like, and it's scary. 

 

Guest: Lance Rubin 

 

What's a hack, right? So we don't want to spend the time splitting it out, the formula is too difficult, we're just going to quickly, you know, we're under pressure. I can understand why it happens. It's just, you know, let's sort of slow down a bit and speed up later. The problem is that we don't, and we're just hacking a number or hacking an overlay or do something, and then down the track, the person leaves, you come back and you're like, what is this plus 5225? You know, what does that mean? And there's often not a comment field which says, 5225 is this adjustment that we had in other accounting adjustments or something like that. But no, you won't find that either.  

 

Host: Paul Barnhurst 

 

Generally, if they're hard coding, they're not taking the time to document why they're hard coding. They’re using it as a hack and a shortcut and they're like, oh, I'll just fix it later, and then it never gets fixed.  

 

Guest: Lance Rubin 

 

To balance my balance sheet, maybe. 

 

Host: Paul Barnhurst 

 

As dealing with one of those today, but that's another story. So what's kind of a key takeaway from seeing all those files outside of hard coding? Anything else that you kind of see as maybe a key takeaway or learning from just seeing so many models with issues?  

 

Guest: Lance Rubin 

 

Yeah, it's training. I think all of these point to a lack of training, unfortunately. I don't think teams have been taught good modeling practices and how to be hygienic with a model building. And let's not even talk about a model, let's just talk about a spreadsheet. So just generally, the hygiene/care factor on education and training is missing, which is why you've got this plethora and repetition of these problems. So to me, it just says we haven't spent time dedicating and training our team. Today, it's actually quite weird that we're doing this podcast today, because today, I'm also running a training session for the cash rewards team in-house around hygiene, spreadsheet hygiene, and financial modeling basics 101, which is, what does it mean to be hygienic? What does a good spreadsheet look like? What are the things we need to look out for? And I think most people will kind of get it, but I'm going to make it really, really clear and then say, well, there are critical things, external links- bad, hard-coded formulas- bad, you know, hidden stuff, whether it's hidden in VBA or hidden in sheets, and then inconsistency, inconsistency as a concept and then structure. You know, structure formatting and some of that is inconsistency related, like commingling, you know, time series on the same line, inconsistent formulas going across, you know, jumps between actuals and forecast, well, I'm not gonna separate that out and use an if statement, or add different blocks together and let the formulas do the hard work, not the human.  

 

So I think it's education. What it shows to me is that we just don't have the right training in teams and therefore these errors exist and it's everywhere.  

 

Host: Paul Barnhurst 

 

I would 100% agree with you, especially now that I do a fair amount of training and I do a number of Excel courses and I've even gone in and helped a few companies with design, whether it's Power Query or a little bit around modeling and different things to think about, and I know for my career, I was never trained. It was only really in the last few years I really started to learn how to build models well. And I still could obviously improve, but yeah, I think out in corporate finance in particular, the training is very poor. I think if you go that investment banking route or M&A or some of those where, hey, they bring you in and take you through a boot camp, you generally see better skills.  

 

Guest: Lance Rubin 

 

 

Yeah, absolutely. It's the number one thing you straight away get modeling training, Excel training.  

 

Host: Paul Barnhurst 

 

So why don't we have you tell our audience a little bit about your background, just kind of get a little bit of your history and what you're doing today.  

 

Guest: Lance Rubin 

 

Let's sort of look at the evolution of kind of where I've started to where I am now. So my career has gone through various phases and I started off as an accountant/auditor, you know, then moving into an investment banker and corporate finance, and that's where I sort of really cut my teeth into proper modeling. 

 

Moving from there, I went into finance business partnering within a bank, then into a CFO role, and now business owner/finance innovator, and of course, trainer. So I think a key part of that journey is the learning and development that I've had to do for myself as part of moving from auditing, where I audited, so one of the things that I first did when I did an audit is I audited an actuaries model that made one plus one equals three. I don't want to go into too much detail in how that worked, it was quite controversial, but in the end, it blew me away. I didn't even know the word financial modeling back then. I was only familiar with the term of financial engineering, which is kind of what this was. And actuaries have been doing that for a very long time, and actuarial disciplines have been doing modeling for a very, very long time. It's sort of getting that bug that's bitten me in terms of, hey, there's this cool thing called modeling, I want to do more. And that led me to go into investment banking and building models in property and corporate finance.  

 

And then when I realized that that was something that I was passionate about, I just followed that path. And that's kind of what I've done throughout. Modeling has been part of what I've been doing, whether it's a CFO, whether it's a finance leader, whether it's a finance business partner. There's not a role in finance that modeling doesn't play a significant role in terms of adding value and decision-making. So I'd say that's kind of where I've had my journey, following that pathway.  

 

So we're talking about, you know, PWC to Investec Bank, to National Australia Bank, to Banjo. You can see very much a finance banking background to Model Citizn, then launching xrcloud, a SaaS business, and we're soon going to be launching a data-driven CFO consulting, which is doing CFO consulting for SMEs and helping smaller businesses who don't have, they've got their compliance accountant, they've got their bookkeeper, but they don't really have anyone in the middle, and I don't think it's a sector that's really well served.  

 

So we're going to use our own tools and our own team and go in and actually help them. We've really got a few clients, but we can expand that further. So I think it's just a great time in terms of what we're doing and the technology and Fabric and Power BI and all of those other interesting evolutions. We were talking earlier about Python in Excel and dynamic arrays and lambdas and Copilot and chat GPT and all. I just feel there's this massive wave of tools that are at our disposal that this, you know, back in the day when I started, the only thing we had was VBA, you know, that was it. That was our tool, Excel and VBA. Now we've just got so much more at our disposal, which is exciting.  

 

Host: Paul Barnhurst 

 

It really is amazing how far we've come. I remember when I started my finance career, you know, when we were deploying micro strategy and building that out. Now I look at how far the BI tools have come, compared to what that was, and it's really amazing to watch.  

 

So I know one of the things you get involved in with your company is finance transformation, helping companies with that. So how do you go about that? What is the key for you when you're helping a company with transforming financials and those type of projects?  

 

Guest: Lance Rubin 

 

 

It's a great question, and the thing is, there's a lot of consistent themes when we do that, but there's a lot of nuances. So, every company and someone said to me, you know, they said, what industry do you focus on when you build models? I said, we do all. They said, oh, well, that's not possible, you need to be an industry expert. I said, well, you don't, because you know all of the fundamental building blocks are quite similar. But there is nuance, there's nuance in how people generate revenue, there's nuance in how people price and some of their accounting policies. So you have to diagnose and understand the situation on those clients first. So we can't just march in and say, okay, throw out Google Sheets, use Excel, there you're go. That's not going to work. So we actually have to sit down and go through a proper analysis and what we call a tech diagnostic, where we actually sit down and we unpick all the different elements of what their problem is, what's their business problem.  

 

So some organizations have a forecasting and planning problem, some people have, you know, they've got lots of data, they don't know how to extract the insight. And so it's a combination of process, tech, and people, with people being the most important. So what we've noticed is the people element is the biggest challenge in transformation, and I think part of the problem is that we come in with these pre-conceived solutions, pre-baked in ideas, and we sort of change the name of the deck and send it to the new client, and that's how you do transformation. And I think that's how most large-scale consulting firms work, and I think that that's just fraught with danger because we haven't actually sat down, we interview leaders, we interview people on the ground, we understand their real problems and their challenges. And some of them are just not aware of some of these functions and tools within the existing tools, let alone bringing new tools in. So we really understand that, we then set a strategic roadmap in terms of where they are now, we understand, we do a hygiene assessment, so we actually run our tools across their Excel spreadsheets, bring up all the warts and all, and saying, this is actually what you've got below the surface of what you see.  

 

In most cases, people are aware of it, but then don't know how to quantify how bad it is or how big it is, and we do that. And then we say, okay, well, you've clearly got a spreadsheet linking issue if you've got all these external links, as an example. But how do we then connect the spreadsheets? How do we make it more streamlined? And how do we look at the process?  

 

So, we really go through and we co-design and co-develop a roadmap that suits their needs, suits their budgets, suits their timelines. And we assess the team as well. Are the teams set up and ready for change? Because sometimes they're not. What often happens is a new CFO would come in, as to some degree I'm doing in Cash Rewards, as an interim CFO, and helping the team and look, do we have the right people? Are there people that are open to change? If not, what's going to happen, and how do we face into some of those people challenges, and I think that's a big element of what transformation is- is the people.  

 

Then you can look at the process. If people are open to change, then process is a lot easier, and then tech is just another tool, really. I mean, it doesn't really matter what it is. Even if it's just teaching people Power Query, that's still tech. That's still change. So whether you're giving them a third-party add-in or web-based tool, or Power BI or something else, tech is tech. It's actually not the difficult bit, the difficult bit is really the people and the process and making sure that the tech fits with both of those two previous elements.  

 

Host: Paul Barnhurst 

 

100% agree with you. You know, what I often say is you get your people, you get your processes right, or at least have that mindset of constantly automating processes and streamlining, you enable technology. And if you got the first two right and you enable technology that's going to make you more efficient, that's really when magic can happen. You can accomplish a lot more. But implementing tech for tech's sake, it's just never going to work well.  

 

Guest: Lance Rubin 

 

Yeah, absolutely, and I think that's where a lot of transformations fail. I think a lot of great salespeople have chats to CFOs and say, yeah, we could streamline this process and make these changes and get rid of Excel and all these sort of grand statements. And yes, Excel is actually not the problem. It's the way people use it that's the problem. So let's fix the way people use a tool with a new tool. When you say it like that, it's like, hang on, wouldn't you just fix the way that people use the tool in the first place? And I think that's the big thing, it's just like, well, we're going to put in a new planning tool and that's going to make it all good. I’m not saying that planning tools are not useful, they are, but it's about making sure that they fit for purpose on the problem as opposed to just using it to fix all problems that are largely blamed on Excel.  

 

Host: Paul Barnhurst 

 

Agree, there are times when you need to use a tool other than Excel, but you need to make sure you're thinking through it and it's not a people or a process problem, that you're really bringing the tool because it's needed to solve a problem. There's a quote I love, and I think it was Hastert Consulting. It said, “new technology plus old processes equals expensive broken processes”.  

 

Guest: Lance Rubin 

 

That is right, yeah, absolutely. Definitely expensive and potentially even more broken processes.  

 

Host: Paul Barnhurst 

 

Correct. Yeah, that's kind of what he's implying. I totally agree. So, I mean, I think, with what talked about, I think it covered kind of some of these other questions I had, which really, you know, the advice to anyone that's thinking about is you really need to step back, look at the strategy, the people, the processes, and then look at how technology can help enable you versus seeing technology as a solution. 

 

Guest: Lance Rubin 

 

Yeah, I mean, I'm busy in the process of writing a book and I talk about finance transformation and I use the acronym HACK. And the reason that is, is because there's a clear need to become hygienic, there's a definite need to be more automated. So both of those two are kind of more technical in nature and the two other elements are capability and knowledge. 

 

So the HACK framework that I've developed allows you to bring both the technical hard skills and the softer skills. I actually think that the softer skills are more difficult than the technical skills, but that's a separate issue. And so you need to develop capability and you need to develop knowledge and you need to bring that together with hygiene and automation.  

 

So HACK is really a framework that I'm building on. And that's exactly what we've been doing in all our transformation elements: understand, assess, help, develop, enable, and maintain and sustain in terms of knowledge and growth.  

 

Host: Paul Barnhurst 

 

Great. And I like the HACK. That's a great principle. I'm definitely going to remember that one.  

 

Commercial break: 

 

In today's business world, financial modeling skills are more important than ever. With Financial Modeling Institute's Advanced Financial Modeler Accreditation Program, you can become recognized as an expert in the field by validating your financial modeling skills. Join the Financial Modeling Institute's community of top financial modelers, gain access to extensive learning resources, and attain the prestigious Advanced Financial Modeler Accreditation. 

Visit www.fminstitute.com/podcast and use code PODCAST to save 15% when you register. 

Host: Paul Barnhurst 

 

So we talked a little bit about technology and transformations, I know you're a huge fan of technology kind of pushing the envelopes there. Obviously also a big fan of modeling. So kind of what led to your love of modeling? You mentioned seeing that first one, the one plus one equals three, the actuary and be like, wow, you can do this. Is that where you started to develop kind of a love for this and going, I want to learn more. How did that come about?  

 

Guest: Lance Rubin 

 

It sort of started with, I guess an appreciation, which turns into a love of Excel. I think it starts with that, like, oh, this is just such a cool tool. This is such a cool tool because it's sort of, it allows me to be creative. I think I remember you saying on a podcast here, if an accountant's creative, he gets thrown in jail, if an FP&A person's creative, they get a gold star or something to that effect.  

 

Host: Paul Barnhurst 

 

Basically, yeah. 

 

Guest: Lance Rubin 

 

And I think that is kind of, I felt stuck in the sort of backward-looking accounting world, and if I wanted to be in the more sort of forward-looking strategic, consultative advisory world, then it was a natural fit for me to have Excel and therefore by extension modeling. Now I didn't know what modeling was when I first started using Excel. I was an auditor and I was using Excel for our lead schedules and analyzing information and building. I think that was the first time I probably built a pivot table and just to sort of slice and dice, but beyond that I didn't use it for modeling. But ultimately that was kind of what got me hooked is that, this is a much more amorphous, creative world that I want to be in in my career, that is not so much, you know, dotting I's and crossing T's because I found that that wasn't too inspiring for me. And when I joined Investec Bank in corporate finance, that just ballooned that just sort of really opened my mind up to, wow, a spreadsheet can support $120 million in equity capital raising, a bank is going to lend you $200 million on the back of a spreadsheet. And when you say it like that, it's like yeah, that's what happens. Project finance models get built. Bridges and roads and toll roads and infrastructure get built off an Excel spreadsheet. It's fact. The poll that we've got running on LinkedIn at the moment shows that. So I think there's so much value, like real value, and I attribute value to cash flow and discounted cash flow and valuation, can be built with an Excel model and with modeling skills. And I think that's the thing that has kept me going, and now it's just the tech overlay. So that's what's sort of really exciting around how's modeling going to evolve and change with all of these built-ons and add-ons and add-ins and everything else.  

 

Host: Paul Barnhurst 

 

Great, thanks for that answer, and I agree with you. It's amazing to think the dollars, the value, the projects that depend heavily on Excel and Excel models. I mean, it's trillions of dollars worldwide when you add it all up. It's pretty, pretty mind-blowing when you think of it that way.  

 

Guest: Lance Rubin 

 

Well, there's that great image about the whole world sitting on top of someone's shoulders and, you know, underneath it is Excel. I mean, it's reality.  

 

Host: Paul Barnhurst 

 

Yeah. It's pretty amazing, like, you know, I've talked about, shut off Excel and watch what happens to the world.  

 

Guest: Lance Rubin 

 

Yeah.  

 

Host: Paul Barnhurst 

 

I’m curious, you've worked on a lot of different projects, seen a lot of different models, what's the most interesting model you worked on, and kind of what made it unique, maybe project or model. 

 

Guest: Lance Rubin 

 

Well, both. So it was a project that we were asked to build the model on, it was a social project. Youth homelessness was a key one in terms of youth being sort of parents are away, either kids were taken away, parents were drug affected or there were other issues. And there was a program of work in terms of dealing with the social issue. And so it was a philanthropic/government project where we had to build a model which was outcome based in terms of the government would put some money up, there were some philanthropic donations as well that were put up and we needed to model what the payments were based on certain outcomes. So if certain people stayed and they went through the program and how successful that was gonna be and then that triggered various payments to the social provider, back to the philanthropic investor who got a return, obviously, if the money went well. So it was really a good collection of government, private sector, not-for-profit, and of course, us involved and the consultant who brought us in as purely the modeling experts.  

 

So the reason it was unique is because it was something that really opened my mind to, okay, this is not just a model for capitalism. This is not just a model to make richer people richer. This is not just a model for the purposes of a business making profit or cash flow. This is really focused on making a change in society. So that's why it was unique. We probably built the most social impact models across Australia. And I guess that's the one thing that… They don't come up often. They often involved government tender and projects and they got a lot of lead time. But that's certainly very unique and it's a good feeling to work on a project that you actually know that the outputs of that project is actually going to change someone's life, as opposed to just make people more money.  

 

Host: Paul Barnhurst 

 

Yeah. When you first said it, I wasn't expecting the project, and I heard that as just kind of that excitement of, okay, this is something that's really making a difference for the world versus, like you said, you know, some projects you're working on and they may be really fun and exciting, but at the end of the day, you're exchanging money for a bunch of billionaires or millionaires or whatever, right? Versus homeless people, because everybody knows somebody who's been homeless or lost their parents or dealt with those drug issues. It's just a reality of society. And it's something we all, I think, obviously we all want to get better. So I can see where that'd be a very rewarding project and just a different way to be able to look at things where you're really knowing, hey, this makes a difference.  

 

Guest: Lance Rubin 

 

Yeah, we've dealt with chronic illness, we've dealt with youth recidivism, so that's reoffending rights. When you have people come out and they re-offend and with youth homelessness was one, affordable housing is another.  

 

So we've done a number of different projects across that social space, but I think, and they were all quite different, and, you know, because there's not a stock standard approach, it's quite creative, which I really enjoy.  

 

Host: Paul Barnhurst 

 

Yeah. I can see where that'd be really fun. The creative aspect of that. That's one of my favorite things with the model is when, you know, within reason you can get creative. You know, there are certain areas where you want to keep structure and all that but it's fun to be able to do some creativity in a model.  

 

So we're gonna switch gears here a little bit from that. I know you're a huge fan of technology, you know finance professionals particularly using power BI and there's some other tools like that. So you can talk about why that's so important particularly around, you know people are doing a lot of modeling to bring in tools like power BI and other new tech. 

 

Guest: Lance Rubin 

 

Yeah, thanks, Paul. It's a great question. And I guess I come back to that journey that I've been on in terms of, you know, what am I trying to solve? And as you do more models, you do start to realize that Excel is not the tool and, you know, people will be shocked when they hear it from my mouth, Excel is not the tool to solve all problems. And so when, for those that can see Paul's face, when I said that, it's actually about, scale and then data gets bigger. So I knew that I had this challenge and I said, well, Excel is not the tool to use. What next? What next? I guess that's where Power BI started to pick my interest, which is like, okay, well, basically you can handle more data. Number one, that's a big tick. It has a visualization interface, which is nice. It's one single tool, unlike Tableau, which was at the time when I was looking, you know, there's Tableau Prep and Tableau Visual, there's also Clicksense, ClipView, and all these other, you know, BI tools that, you know, where as now Power BI is all in one application, but it's also more aligned to Excel because they're both using Power Query. In fact, the first time I used Power Query was not in Excel, it was actually in Power BI. And then I had, hey, well, now I could also use it in Excel as well.  

 

So for me, it's that grownup story. You know, you start off in your career playing with certain tools and eventually, you've got to grow up. Eventually, we've got to deal with bigger data, you’ve got to face into those challenges. And I think that's why I'm a big proponent of focusing on the data side of financial modeling. Not many people understand that data modeling and financial modeling, they sound the same, but they're fundamentally quite different in terms of, now you're dealing with star schemas and data relationships and fact-tales and dimensions and DAX and yes, okay, there's DAX in financial modeling as well, but it's fundamentally quite different the way you approach financial modeling, which typically is done horizontal in terms of time series and then, you know, data modeling, which is more tabular based, more sort of vertical. So I think that's a big thing for me. And I guess that's why I've been a big, big advocate for people learning Power BI because you already learn, you already know some of those tools inside Excel, assuming you're using Power Query, and I guess if there's one thing that people should take away from this discussion is Power Query absolutely is the number one go-to where people should start, and I'm probably taking another question away from you, but I think that's a key thing because by doing that, you start to explore Power BI without even knowing it.  

 

And the other thing is, I guess, the innovation that Microsoft have got behind them around their tools, both Excel and in Power BI, and of course now with Microsoft Fabric. And it really means that you know, me, the single modeler, I now have tools in my toolkit, that I can deploy a database using data flows, using data marts, using proper data engineering toolkit in a low code, no code environment. I'm not writing lines of code from scratch. I'm copying and pasting and moving and clicking and I can build an end to end and that's kind of what I'm talking about in the Power BI Summit coming up this month. Oh, sorry, we're still in January, next month, tomorrow, February.  

 

So those are the things that I think are exciting, and there's people that move into the data world and they never go back to Excel, and I've met those people and I've kailed one of those and he's like, oh, I don't touch Excel anymore. I've fallen into the data world. And I wanna be that hybrid. I wanna be that sort of bridge between the two because I think there's still a lot of value in financial modeling. There's a lot of value in Power BI and data modeling, it's that bridge and that connection between both that I think we need to maintain as opposed to have a hard line. Data modeling in Power BI is not just about looking backward and doing analysis. You actually can do forward-looking modeling in Power BI. Yes, you can do it natively, it's a bit more difficult, or you can use, again, talking about other tools, InfoRiver, VelQ, those sort of tools that are available. There's a bunch of others, you know, I think those are the things that you can actually do, some of those modeling things inside Power BI and not lose completely your modeling skills.  

 

Host: Paul Barnhurst 

 

Yeah, no, a hundred percent agree. My, you know, my background, I started with doing SQL and some report writing the first couple of years out of grad school and then moved over to a more traditional FPNA role and understanding tabular data and understanding a little bit about databases and table structure has served me so well throughout my career. It's been invaluable. You know, and then when I learned about Power Query, that opened a world, started to learn Power Pivot. I've done some Power BI, by no means an expert, but you know, I definitely know my way around Power Query. And just the more you learn of being able to kind of connect it all together, the more effective you can be at the end of the day. Because like you said, there are different tools for different situations. As much as we all love Excel, we all know it's not the answer to everything. 

 

Next question here I want to ask you is, we've talked a lot about technology, how do you see AI impacting in particular financial modeling say over the next kind of five years? So that, you know, medium three to five-year range, how do you see that impacting the profession?  

 

Guest: Lance Rubin 

 

I think it's going to have a massive impact. And I think a massive impact for those that are willing and open to embrace it for the positive. I think those that are wanting to push back and resist it, I think it's going to have a negative impact. 

 

And there is a part of me that also gets a little bit concerned about the risk element of AI and, you know, and when things get a little bit out of control and we don't really understand, I read an article recently where if you start to introduce, you allow AI to go rogue, it's very difficult to unwind that and actually get it back to sort of behaving well. So I think it's a challenge and I think we don't know enough. So I think it's exciting, there's a bit of nervousness around it, but already, if we think about it, for the viewers who haven't watched the three-part episode with myself, Craig Hatmecker, Jeff, and Danielle Stein-Fairhurst on the Future of Financial Modeling series, we did three parts, definitely have a look at that, they're on our website embedded, but also on Danielle's YouTube channel. And we talk about, AI is not just about the build aspect. It's actually going to help us in all elements by prompting and scoping, and asking questions and design. There are areas of financial modeling that are not just about the build and actually about putting formulas into spreadsheets. No, it's going to help us to write some coding as well, some VBA.  

 

So I think its impact is profound, end to end. It's not just about one element. But I think what's going to happen, it's not the AI itself that's going to be having impact, it's the person that embraces it and uses it and applies it, that's going to gain the most and those that don't are going to lose the most. So you're going to have people that are going to be gaining the most, you're going to have people that are going to be losing the most, and in the middle is just going to be the AI enabling tools which are exciting. Power BI has a copilot within DAX and it's not perfect by any stretch, but DAX is one of the hardest technical elements of writing measures and calculations in that environment. And so, you know, they straight away put Copilot into DAX and quick measures, you know, I think that's an exciting place of where we can bridge the gap, bridge the knowledge gap. And I think that's the key thing. AI is going to help bridge the knowledge gap from a spreadsheet-er to a financial modeler, to really help grow and enable better modeling. I'm something that has, yeah, as I said, profound impact, but I guess it all comes down to whether are people willing to embrace it?  

 

Host: Paul Barnhurst 

 

You know, that's pretty much true of almost any technology, right? We don't embrace it. We can't take advantage, but I think especially true of AI because of how much more productive it can make us. It really is mind-blowing sometimes the things you can do with it. So definitely I agree with you that it will have a profound impact. How quickly, how much, what areas, we'll see how it all goes, I think sometimes it may be a little slower than we think, but we're already seeing big impact, no question. 

 

Guest: Lance Rubin 

 

It's definitely slower. I think when we ran our first episode of that meetup group, that virtual meetup, we had, I think about 70 or 80 people live and we ran a poll, Daniel ran a poll on how many people have tried or played with ChatGPT and it was 10%. And these are people that are interested in the space and yet only 10% of the audience had actually played and used it. And by that stage, I'd already not only used it, but I was using Jeff's VBA code embedded in and running functions that would push back prompts through the API. You know, I guess I'm always at the cutting edge, but I think yes, it will be like any other. I mean, Power Query is still not that well embedded, and it's been around for a decade. So I can't see AI suddenly being adopted overnight either, so I think that's going to be a long road.  

 

Host: Paul Barnhurst 

 

Yeah, that could be its own episode on Power Query and getting that more embedded in use. But I'm with you, it's going to be a long journey, but it's definitely an exciting journey.  

 

So, I’m going to ask you a question here back to Excel, as we come close to our rapid fire and kind of wrapping up this interview. What is one thing you have learned in Excel over the years that has saved you more time than anything else that you could share? Could be a shortcut, could be a formula. And what's that thing that you think has benefited you the most?  

 

Guest: Lance Rubin 

 

You took the answer out of my mouth, Paul. Shortcuts. And it's not just one particular shortcut. It's just the concept of shortcuts and shortcut keys and not using your mouse. It's the biggest time saver. And now people might say, yes, but it's so easy for me to drag my mouse and click on the menu. Well, just hit the Alt key and the menu pops up and you can see it. Alt HVS, copy-paste value specials. Not just Control C, Control V, copy-paste. Do a whole Alt equals, which is just a simple sum. Control R- Copy right, Control D- Copy Down, Control page up and down, navigation, to move between different sheets. Even just Control up and down puts you to the start and the end. Control right bracket takes you to the end and the formula, set of consistencies and all of that. When I started investment banking, for me, there was this watershed moment when, you know, I moved across from being just a spreadsheeter into a financial modeler. And one of those sort of key skills is absolutely embracing shortcuts. And I know it's painful in the beginning and it's not about learning the shortcuts that I've just rattled off the top of my head and I'm not learning them. I'm using them. Because I use them so often, they’re just naturally in my head and at my fingertips and it's muscle memory, so it's building that muscle memory that has really saved me a lot of time.  

 

Now, quite often when I'm training, I actually have to stop using shortcuts because I go too fast. And so I did one training when I used shortcuts and I kept on being told, oh you’re going too fast, you're going too fast, you're going too fast. People just couldn't keep up with me. And the next time I did that same training, I did not use one single shortcut, and I never had one complaint about going too fast. And I actually then asked the question, how was the speed? And they said it was perfect. 

And all I used was my mouse.  

 

So I feel like as a trainer in one way, yes, I gave them a good experience in terms of they could follow me, but I actually feel like I've done a bit of a disservice because I actually haven't unearthed this wonderful thing called shortcuts. And it's one of the things that you can't run a training course just on shortcuts. You've got to run a training course on modeling and show it as part of it. So I still don't know how to crack that one, in terms of getting people to realize the value of shortcuts. Like everything, it takes time. You have to unlearn a behavior of I’m just grabbing my mouse. But when I'm working with my team or working with a client, I'm using shortcuts all the time. And I'm saying, hey, there's this shortcut, Ctrl-R, Copyright, Ctrl-Down, all of those different shortcut keys. And I said, look, don't worry about not learning it. Hit Alt. And then the menu comes up and you've got, you know, Alt D if you want for data, and all of those things. So I think it's that. Shortcut keys are a huge value time saver, although you don't see it, right? It's not something that a client will see, but for yourself, on a personal level, it's probably the single biggest efficiency gain that I got moving out of the sort of, as I said, spreadsheeter mentality to the financial modeler. 

 

Host: Paul Barnhurst 

 

You know, it's funny, that's one that a lot of people mention. I'm kind of one of those tht is in the middle. I do shortcuts, I know there's quite a few that I have memorized, just muscle memory, but I still use my mouse a fair amount. So I suppose that to be the contrarian on that side, but I do know it can make you a lot faster. I'm not going to argue that there are a lot of benefits, but sometimes I've just focused on other areas. I always have fun when I hear that one, cause I tend to be a little more in the middle on that. 

 

Guest: Lance Rubin 

 

I don't not use my mouse. Let me just say that. I'm not just throwing away my mouse. My mouse is connected to my spreadsheet and I do use it from time to time. If I'm working in a spreadsheet, how much of my time is on a mouse versus the keyboards? It's probably 80-20. 80% on shortcuts and 20% mouse. But if you look at the Financial Modeling World Cup, they're 100%. No one's using a mouse. You just don't have the time. In the Financial Modeling World Cup where they're doing battles, there's no place for a mouse at all. At the cutting edge of speed and efficiency, I'm probably one of those that are a little bit more towards that side of the path, and you're probably somewhere in the middle. But what I'd say is, it's just a huge efficiency gain, and you can see it. You can see it by the world champions in terms of the way they work, and even I've experienced myself when I'm training, it makes me too fast as a trainer. I've got to slow myself down by using a mouse. 

 

Host: Paul Barnhurst 

 

I do know what you're talking about doing training. I can relate to that and certain shortcuts and things.  

 

All right, so now we're gonna move into my favorite section. We're gonna go to the Rapid-Fire section. So I have 10 questions here. You get no more than 10 seconds to answer. You can't tell me “it depends”. Once we get to the end, I'll let you go back and elaborate on, you know, one or two that you want to. Give you a minute to kind of elaborate. So you're ready here?  

 

Guest: Lance Rubin 

 

Yeah.  

 

Host: Paul Barnhurst 

 

All right. Circular or no circular references?  

 

Guest: Lance Rubin 

 

No.  

 

Host: Paul Barnhurst 

 

VBA or no VBA?  

 

Guest: Lance Rubin 

 

Yes. 

 

Host: Paul Barnhurst 

 

Horizontal or vertical model?  

 

Guest: Lance Rubin 

 

Horizontal.  

 

Host: Paul Barnhurst 

 

Dynamic arrays, yes or no?  

 

Guest: Lance Rubin 

 

Absolutely yes.  

 

Host: Paul Barnhurst 

 

External workbook links, yes or no? 

 

Guest: Lance Rubin 

 

So I used to be a yes, but I've actually been experienced and I'm gonna convert that to a no. A very hard no external links.  

 

Host: Paul Barnhurst 

 

 

All right, I like it. Name ranges in your models or no name ranges?  

 

Guest: Lance Rubin 

 

Yes, name ranges.  

 

Host: Paul Barnhurst 

 

Do you follow a formal standards board for your modeling?  

 

Guest: Lance Rubin 

 

Yes, with exceptions, but yes.  

 

Host: Paul Barnhurst 

 

Okay, fair enough. Will Excel ever die?  

 

Guest: Lance Rubin 

 

Not in my lifetime. And probably not for a long time, but look, I just need it to last long enough for me to collect my bet with Chris Argent on the whiskey. So I think I've got… 

 

Host: Paul Barnhurst 

 

I was thinking that exact same thing.  

 

Guest: Lance Rubin 

 

I mean, I've got another 12 years and I think I'll win the bet, but ever is a long time. I think I listened to that podcast and he was like, oh, I have to say eventually it would, I just can't fathom how. So I'd say no. Um, I just don't know how.  

 

Host: Paul Barnhurst 

 

Yeah, I can appreciate that. Cell protection in your model. Should you use it? Yes or no.  

 

Guest: Lance Rubin 

 

No.  

 

Host: Paul Barnhurst 

 

Okay. Do you believe financial models are the number one corporate decision-making tool?  

 

Guest: Lance Rubin 

 

Absolutely. There's no question it is the number one corporate decision-making tool for the reasons that we can see on the poll on LinkedIn and it's how you do corporate transactions properly.  

 

Host: Paul Barnhurst 

 

And then the last one, what is your lookup function of choice? Choose, Vlookup Index match, Xlookup or something else.  

 

Guest: Lance Rubin 

 

You should know this Paul. I'm an Index match man.  

 

Host: Paul Barnhurst 

 

I figured that's what you're going to say. I was pretty sure.  

 

Guest: Lance Rubin 

 

Yeah, you know, Xlookup, yeah, that's okay, but you know, old habits die hard. I understand if someone wants to read it, they can, I do show it from time to time, but you know, my default is Index match.  

 

Host: Paul Barnhurst 

 

Yeah, my default now is Xlookup, but I can appreciate Index match. It's a great combo too. So it's fun to hear different people's defaults. Of course, when I interviewed a Diarmuid Early, he's like, I do question if there's only four. I'm like, yes, I realize you can give me a lot more.  

 

Guest: Lance Rubin 

 

Yes, I did hear that interview with Diarmuid. And he's right. I mean, Power Query you could use instead of any other lookups as well.  

 

Host: Paul Barnhurst 

 

Yeah. You got six different ways you could do join. Yeah. There's lots of options for sure.  

 

Alrighty, so which one did you want to elaborate a little more on? You did a little bit on Excel, but was there another one there?  

 

Guest: Lance Rubin 

 

Yeah. I think I want to touch on dynamic arrays as well, but let's just go back to the Excel bit. I think in the end, because there's been so much investment and so much entrenchment, and I think the biggest challenge that you're going to have if you're going to replace Excel with something else, right? So Excel is going to die, but what's going to come in its place? You've basically got to get every single corporate around the world, you've got to get the stock exchange, you've got to get everyone who currently uses Excel. You've got to get Bloomberg, you've got to get Reuters. They're using these models and spreadsheets all over the place. You've got to get them to change as well because everyone's got to change at the same time, otherwise, if I'm on a Google sheet and you don't have Google, then how do we work together? If I'm on some other spreadsheeting technology and you're not, well, I mean, I know it's great for the person who sells the technology because now they've just suddenly got access to more people, but now I've got to go through security, I've got to go through other elements.  

 

By the way, Excel is still the most secure and the Microsoft stack is still the most secure environment to work in. Google's not as secure as Microsoft because you hand over your data to Google. Google has got the ability to look at all your data. It's actually in the Terms and Conditions and unless you specifically ask them not to, they will, whereas Microsoft don't. From a security perspective, I think the Microsoft stack is a better stack and of course that's why you see a lot of transactions, corporate transactions, which are very confidential, still remaining in that world. And so I just can't see it. I literally can't see it. Yes, there'll be on the edges, but you're talking about the corporate world changing the way they do transactions. Find me another program that'll help build an infrastructure toll road project with debt sculpting with five tranches of debt and needing to run DSCRs and solving optimization for debt, debt covenants. I just don't see it. And we've got a lot of clients in that project finance space that we help and innovate with, and they're using Excel still.  

 

So yeah, I think that's where I'd sort of still come back to, definitely not in my time, and I just can't see it in my head how something else changes. I know that there's a lot of money being thrown at disrupting Excel, but there's a truckload behind Microsoft and it's only getting better. So. 

 

Host: Paul Barnhurst 

 

If it happens, I'll bring you back on and we'll have a conversation about it.  

 

Guest: Lance Rubin 

 

Yeah. If I'm not in a, you know, sort of in an old ledge home or… 

 

Host: Paul Barnhurst 

 

Yeah. If we're both still able to have the conversation.  

 

Guest: Lance Rubin 

 

Yes, let's do it. Let's do it. And yeah, I'm happy to be wrong.  

 

Host: Paul Barnhurst 

 

All right. So we're going to go ahead and wrap up here. I think that was a good place to end that conversation today, but if our audience wants to learn more about you, you know, if they want to reach out to you or get in touch with you, what's the best way for them to do that?  

 

Guest: Lance Rubin 

 

Thanks, Paul. The first place is obviously LinkedIn, generally quite prolific and quite active on LinkedIn. I am relaunching and Danielle has gratefully handed over the Financial Modeling and Excel Meetup group in Melbourne. So I'm now officially the coordinator and host of that Meetup group. So if you're in Melbourne and in Australia, we’re relaunching that this year with face-to-face sessions for the Meetup group. I'm really excited about that because I think it's been way too long to just be cooped up behind our spreadsheets and online and virtual. It's time to get back to face-to-face, and I'm really excited about doing that. If you're in Melbourne, or even if you're traveling around the world and you've got Melbourne on your radar, try lining up to some of our meetups. We're going to be relaunching,  we've got a new host that I'm just waiting to sign up, and so we'll be announcing that soon. So, meetup group or on LinkedIn is definitely the place to be, or our website, modelcitizn.com, and no “e” in Citizn.  

 

Host: Paul Barnhurst 

 

All right. Great, well, thanks, Ian. Thanks again for being on the show,  I really appreciate it, and I'm sure our audiences will enjoy this episode as well. And I'll just say keep modeling and I'm sure we'll continue to have fun on LinkedIn.  

 

Guest: Lance Rubin 

 

Yeah, absolutely. And thanks Paul, appreciate the banter and great job at keeping up the awareness and just giving a platform for people to listen and learn more, cause I think it's a great skill, financial modeling in terms of what people are doing and how they're gonna learn and make better decisions. 

 

 

Financial Modeler's Corner was brought to you by Financial Modeling Institute. Visit FMI at www.fminstitute.com/podcast and use code PODCAST to save 15% when you enroll in one of their accreditations today.