The Mistakes in Structure and Data Thinking That Break Models with Mark Proctor

Tell me that horror story. I know you have one. Worst model you've ever seen.

It was a big model. It had a few thousand lines in it, but it also had a few hundred hard-coded numbers. It turns out that once you take out those hard-coded numbers, things just don't balance. The strategy and the reality didn't quite come together.

So any key takeaways from those experiences?

Give a section in the financial model where people can make manual adjustments that are visible.

You've done obviously a lot of financial modeling and a lot of data analysis during your career? How does the mindset differ between them? How do you make that switch or think about both of those?

It's all about structure, whether you're doing data analysis or financial modeling, but the structures are completely different. When it comes to data analysis, you have to get into a data mindset. This is where finance people struggle because they don't come from data background.

What is the number one thing you've learned that's helped you the most over your career?

One thing I've learned is to make progress in most things, what I actually need, and I think what most people need, is a Financial Modelers Corner is the world's premier modeling podcast.

It is brought to you by Financial Modeling Institute, the world's leading financial modeling accreditation organization. Welcome to Financial Modelers Corner. I am your host, Paul Barnhurst. This is the podcast 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 on the show Mark Proctor. Mark, welcome to the show.

Thanks for having me. I'm excited to be here.

Yeah, I'm excited to have you. All right, tell me that horror story. I know you have one. Worst model you've ever seen.

Well, if you allow me, I'd like to go through two. The first one was a— it was a big model. It was— it had a few thousand lines in it, but it also had a few hundred hard-coded numbers. Finding those hard-coded numbers was tricky, and it It turns out that once you take out those hardcoded numbers, things just don't balance. And actually, you chase around these hardcoded numbers all over the place. I don't think that— I think when the model was first created, I think it balanced. I think the person who originally created it did an exceptional job, but it was passed from person to person who didn't understand the technicality. People, you know, as the business changed, there were changes needed. And I would say that for for that business, it made about half a million profit, for example, but the one-sided adjustments added up to about 2 million. So it was very much around reviewing the outputs and seeing, did they seem reasonable? And so that was possibly the worst model I've seen. A lot later, I got to rebuild that model from scratch using good principles, and it was happy days. But that was definitely the worst. Another one, which was almost on the other end of the spectrum, was because the model was so simple. The idea was it was a 5-year plan model. It's all about strategy and direction. So it was done exceptionally high level. This is the stuff we need. The problem is that as soon as anyone was asked any questions, there's like, but the model gave me this, the model gave me that. And it was a case of people then had to rebuild their own financial model using the same methods as we'd used. But in a greater level of granularity so they could answer any questions that then came along. So ultimately, you ended up with all these business units having their own off-sheet financial models just to fill out a high-level financial model. So that also gave us a lot of problems. So the strategy and the reality didn't quite come together.

Yeah. Not a lot of good when everybody has to go off and do their own big exercise.

Yep.

Right. You need that balance. Either you keep it high level the whole way through, or you go make it a little more detailed. But sounds like that one just, uh, created more work for everybody.

I think it did.

And then the hard coding. Yeah, when models get passed from person to person for years and half the people don't know how they work— we've all been there— those are the worst because you just You have no idea what landmines you're walking into.

No. And I said it was predominantly, it was reviewing the outputs of that model that then you decided whether the numbers seemed reasonable, which partly defeats the purpose of a model, right? I mean, you need to review the outputs to see if they look reasonable, but your review of reasonableness should not be, should be whether the model is working, not just to generate the numbers and the one-sided adjustments that you're then going to make.

Agreed. So, any key takeaways from those experiences?

One of the key ones, so around, and this is something that I've done recently, and I think you mentioned on a previous session, Paul, so we start, or I start to then build in a place in your financial model, because however good your financial model is, people always want to make some level of manual adjustment. It might be, oh, operating is costs are too high in this or whatever. They always want to make some level of manual adjustment. So let's give a section in the financial model where people can make manual adjustments that are visible and we can control that. We know exactly all those adjustments are, we know where they've gone into the model, we've got descriptions of what they're there, why they're there, who's made them. So it's kind of, it's outside of your main assumptions, but equally everyone can see what they are and what they're for. So that's a way of kind of getting around the fact that however good your financial model is, if you understand your stakeholders, there's a really good chance— or the stakeholders in this scenario, you know, they're going to want to make manual adjustments possibly. So just factor that into when you build the model.

Agree. There is some real value in just having manual adjustment inputs, especially if you have a lot of different people working in it. If you're the only one doing it, I've had models like that. I would put a comment, change the color so I knew it was hardcoded and move on. And it worked well enough. Would it have been better to put a manual input? Sure. But it wasn't like, you know, 20 different people were using the model. I agree with you. If you have a lot of users, you're definitely going to want some kind of manual input. Otherwise you're going to end up with numbers all over the place. Tell our audience a little bit about your background. I know you're Excel MVP. You run your own business today, but Give us the rundown of who Mark is.

I felt I was quite good at maths at school, so I ended up going into an accounting and finance degree. And then I came out of that and went into accounting as a profession. I went through a standard audit, then I had my own small portfolio of clients. We used to do financial modeling there for clients as they needed. And then I kind of left there, went into project accounting, and then From there, I went into FP&A and business partnering and projects and financial control and those kind of aspects. So, you know, I've worked across a lot of areas of kind of accounting and finance. And I know that some people like to draw distinctions between this is what accountants do and this is what finance do. But having taken an accounting and finance degree, I just, I just view it as all, it's all the application of the same types of things. One of it's retrospective, one of it's prospective, but ultimately, it's the same knowledge being applied in slightly different ways. You deal with stakeholders in a different way, but in my mind, accounting and finance, they just go together. So, when I see on LinkedIn, you get the difference between accounting and finance, and I think it's just a blur as to what the difference is, and I'm happy to take on either.

You know what I've always said? I don't have an accounting degree, so I'm one of those like, "Ah, it's different," but I totally get what you're saying. They really are in many ways, you know, two sides of the same coin. But I like to joke, and I get myself in trouble, you know what the difference between an accountant and an FP&A professional is?

Go on.

When an accountant gets creative, they go to jail. When an FP&A professional gets creative, they get promoted.

I think, yeah, I mean, that, that may be the key distinction.

Yeah, exactly. All righty, so today you run your own business. How did you go from kind of, you know, accounting, finance, the different roles, to deciding starting your own business was the right thing for you?

I mean, I worked in financial accounting at the time. That was a lot of kind of legal and statutory accounts, a lot of balance sheet work. And the business that I was working with, they had a restructure. My team was made redundant, I was made redundant. And at that point, I mean, I got another job exceptionally quickly, but at that point I was like, I need something else, some other kind of security blanket, as it were, possibly in terms of income. So if this ever happens again, I've got options. So that's when I started Excel Off the Grid. It was a blog. It was at the time we sold a few add-ins and various other things. So it was just a way of, I've got something else going on. There's a, you know, there's a few thousand pounds coming in a year, but it's a nice little addition. And then as I changed to a couple of other roles, The last role I was in, I wasn't a good culture fit is probably the best way to describe it. And after about a year there, my wife actually said to me, she said, "You're going to hand in your notice today and you're going to do that other thing full-time." So I said, "Okay." And yeah, I haven't looked back. I mean, ultimately, I think it's one of those things where the people you're around know you best. They know the challenges you're having at work. She was exceptionally wise in saying, you're handing in your notice today and you're doing the other thing full-time. So that's pretty much how it happened. Then I went full-time and it was just, I'd already had 5 years' worth of the blog existing and other things. I think I was an MVP at that point. So I'd already had some level of knowledge in that kind of space. So it was a good natural step, but I'd already built that, the foundations, a long time before.

Yeah, it's amazing how our better halves often know us better than we know ourselves. I remember there was a job that I got another job, I put in my notice and they made an amazing counteroffer, one I had to think about. I was spending all weekend, I was like, this is really good money. And my wife finally looked at me and she goes, you know, you're miserable. Why are you even considering this? I was like, all right, there's the answer. Oh, and so I get it. I can relate my own way. That's a great story though. And how were those first few months? Because you'd have built something up, but when you were now doing it full-time, how was the challenge of making that transition?

It was tough. I mean, I had my financial model, which was my run rate to zero. And in that, there was the trigger point at which, assuming we earn no money from this, how long is our money going to last? At what point does it hit zero? How long do I think it's going to take me to get a job? Therefore, I mean, there was a financial model behind all of that, but it was tough. I mean, the original plan was to do predominantly consulting work and then slowly over time build up more of a course and training business. But ultimately, I started, you know, first day, here I am, and it's like, I don't even know how to get clients. So So I started building the courses and the training work, and that's the bit that's taken off. I have some clients, I have some consulting work that I still do, but it's predominantly through, through training and other aspects.

Isn't it funny? I find it so true. Almost everybody have on the show, and anyone you talk to, what you think it's going to be and where you end up are different. You pivot along the way. I mean, it's everybody that has— even something as simple as a solopreneurship where you're thinking, I'll do 2 or 3 things, you get in, you find, okay, either I don't like that one, or I'm not generating money with that one, or whatever it might be. And you often switch to a totally different mix than you ever thought it would be, at least at the beginning.

I mean, one of the first questions that you kind of— before you step out on your own is, have I got the ability to roll with those punches? There's that quote by— was it Mike Tyson? I think it's like, everyone's got a plan until they get punched in the face, or it's something like that. And it's like, you've got a plan until you get to day one. And I must admit, on day one, I put something on LinkedIn, and then I've never had so many job offers. You put something on LinkedIn, you know, this is what I'm doing, this is my plan, and I've got more job offers than I've ever had before of people saying, don't do that, come and join our business over here. So I think that kind of meant I felt like I had options, other areas I could go to if necessary, but I think I decided that that was That was the path.

You know, and I could totally tell we're talking to a finance person because you've mentioned options several times. Optionality, right? We, we think in options a lot of times. I, I notice how often I hear that word. I love that. All right, let's get into a little bit more modeling. Fun to learn a little bit about background in business. Curious, you've done obviously a lot of financial modeling and a lot of data analysis during your career, and I think anyone who does works in finance is going to do some level of both, right? So how does the mindset differ between them? How do you make that switch or think about both of those?

I think the— I mean, for both of them, the one of the key distinctions is that it's all about structure. Success with both of them, I think, is about structure. You've had Ian Schnorr on here who's mentioned, you know, when someone says there's something wrong with my formula or something, he's like, no, no, there's something wrong with your structure. And that's the same with whether you're doing data analysis or financial modeling, but the structures are completely different. And I think that's the main difference on mindset. And I find that when it comes to data analysis, you have to get into a data mindset. And often, I think this is where finance people struggle because they don't come from a data background. So, the idea that if we think about how data is structured and even in First Normal Form, which is how Excel really wants to analyze stuff. You get, so you have your, you know, you've got your tabular data, it's in columns and hopefully it's not crosstab, you don't crosstab, everything goes down. But then even then somebody, and I don't want to get controversial, but.

No, please do. We like the show being a little controversial. Go for it.

Early in my career in terms of Excel, you know, you pick up VLOOKUP and you're like, man, I know everything I need to know. And then you find out VLOOKUP doesn't, it only picks the first value. So, oh, I need to learn SUMIF as well. This was before SUMIFs existed. So you then learn SUMIF, you're like, oh, I do know everything now. But it's not till a long time later that you go, you know, you think, oh, I need to look up to the left. And you then start building in like, oh, I just need to copy that column over there and doing a lot of manual adjustments to things. And that's because we don't really understand what data is and how it's structured. And the idea is that data, if any column moves, it doesn't change anything. Like it doesn't break anything. Everything should still work. Equally, if any rows move, everything should still work. So you start with thinking about data and the fact that your inputs should be, should almost be free. So you can, so in terms of VLOOKUP, VLOOKUP is not the right function for data analysis at all because it relies on a position. It's a positional lookup function, which for anything to do with data is a no-go. So how you structure things is completely different. But when it, well, for financial, for data analysis, when it comes to financial modeling, you're still thinking about structure, but it's all about position. Okay? You want your months, assuming you've got a horizontal model, you want your months to go month by month across the top. You want them to be in order. If anyone changes the order of one of your columns, then you're like, what on earth are you doing? You've broken everything. So there is an entirely different approach and mindset between data analysis. I'm not restricted by where rows and columns are compared with financial modeling where I want those rows and columns to be in that place because that's how we think about this calculation and that's how that calculation is built up. So the approach is very different. And I think often people that come from a predominantly financial modeling background initially struggle with data analysis, and people that come from a data analysis background initially struggle with financial modeling because they're used to working in a certain way and thinking about how structure works. But you need to break out of that and then think about it as a slightly different discipline.

I love the way you explained it, especially around VLOOKUP isn't designed for data analysis. I'd never thought of it that way, even though I come from a data analysis background more than the modeling. I worked in kind of a business analyst role. I did 2 years of report writing, SQL, you know, a lot of reports in Excel. And then I moved into an FP&A role and started building models with having no good design principles or any of those type of things and built a lot of Frankenmodels. But I've always been really good and very valuable on analyzing the data and making sure the data is structured right, thinking of that. Tabular, you know, normalized format, all those type of things that make it so much easier to analyze data. Like, right, crosstab, bad idea if you want to analyze data. Summarizing it, bad idea if you're going to then try to analyze it. All those type of things. I really appreciate the way you explain that there. Next question I have for you: do you have a preference? Like, if you could do anything you want, you had a project, do you prefer getting your hands dirty and doing some data analysis or building a model?

These days I probably spend more time with data analysis than I do building models, but ultimately they, you know, one often feeds into the other. If you've got your rolling forecast, right, you're going to do your, effectively, your data analysis piece of getting your data into the right format. That's going to feed into your rolling forecast, but then if you want your rolling forecast to then appear in your FP&A model and other things, that's then going to come back out and be part of your data analysis. So, I mean, as you can probably tell from the way I, the way I explain things, that virtually all my experience in terms of financial modeling is all from inside companies. It's from that kind of mindset, you know, the whole investment and banking world and all of that kind of stuff is not my area of—

Mine either. We're on the same page.

So you've got kind of one feeds into the other. And just, so I would probably spend more time now in data analysis. And that's where, in terms of our training academy, that's where we spend most of our time because actually it's— there's a method and an approach to financial modeling, which is reasonably specific to financial modeling, but data analysis has a much broader context in terms of other areas. So at the moment, we— I do more data analysis than financial modeling, but I'm happy with both. I like the challenges that they both bring, and so it's, yeah, I'm happy with both, but I spend more time in data analysis these days.

Yep. No, I agree with you. I enjoy both. I don't spend near as much time as I used to on either. Sometimes I wish I had more time, especially on the analysis side, to get my hands dirty and all that's going on in Excel. Speaking of that, I'm curious, you know, if someone hasn't been paying attention to Excel, can you share maybe a little bit all the changes that have happened over the last few years to make it a better data analysis tool? Because to me it feels like the changes just keep coming fast and furious. Maybe just a 2-3 minute kind of overview of how it's changed and how it's really a much better platform than it used to be.

Not to say it was bad by any means, but I mean, the first change and the most significant change was the introduction of tables. I think originally it was the lists in 2003, then became tables in 2007. That was the point that we could then name columns and refer to ranges by columns, which is much more aligned to how we think about data. When otherwise you're thinking about, oh, on column A, on column B, but that's— I often joke that you are, if you're using grid references, so you've got a table which is structured, you've got everything laid out as it should be. If you're using grid references, it's just disparate items placed near each other in a grid structure because ultimately Excel doesn't understand that there's relationships and how things tie in with each other. So tables was the, was the biggest change by a long way. And then, so, and we then kind of follow that through with this kind of, well, how do we get stuff into those tables? Then you've got Power Query. That's then made a huge change because to analyze data, you need to have it in whatever inputs you've got, you need it in a structure that you can then use. And Power Query is the, is the best tool that, that brings that along. Microsoft then kind of brought in the, Power Pivot aspect in this at the same time. So if you want to go big data, you've got that, you've got that tool there. And that was then ultimately kind of Power Pivot, Power Query, all of that was spun off into Power BI. They're the key things. You've got tables and Power Query. So they're the key things about how we structure things. Then how we calculate on those, the big change was then dynamic arrays.

Yep.

So you've, you've got your kind of inputs get reshaped into data, then you can calculate on that and get it into whatever layout you you happen to need. So that, that they're the, they're the 3 key things. Tables, Power Query, dynamic arrays, uh, are what's really setting us free from a lot of the challenges of the past.

Well, my background is in FP&A. I am also passionate about financial modeling. Like many financial modelers, I was self-taught. Then I discovered the Financial Modeling Institute, the organization that offers The Advanced Financial Modeler program. I am a proud holder of the AFM. Preparing for the AFM exam made me a better modeler. If you want to improve your modeling skills, I recommend the AFM program. Podcast listeners save 15% on the AFM program. Just use code PODCAST. Agree, they really made so much more available, and as they've redid the calculation engine, they went to, you know, data frames and data types, which now is allowed between the fact that they spill and you can have data frames is allowing Python to work in Excel now, right? These, these core changes have opened up the entire platform to do a lot more.

Yes.

Thank you. All right, automation. I know you're a big automation fan. I've seen a lot of your posts and things talking about, like, don't, don't waste 20 minutes doing this every month, spend an hour upfront and learn how to automate it, or 2 hours or whatever it might be. So, in your mind, how should we think about automation? Where should we start if we're maybe in a role and we've inherited a process that's very manual?

From a financial modeling perspective, the simplest form of automation in Excel is a formula, right? Because you have a formula and you type, you know, somewhere you've got A1 B1, and then you change A1 and it flows through, right? And that's the way that In my mind, the majority of Excel should work, and that's the way financial models should work. It should literally be predominantly on the calculation engine. Our inputs change, everything flows through. Now, in terms of automation, what we— what I then think about is, well, what's outside of that financial model or what's outside of that report? And that's where automation gets more interesting. So we've talked about Power Query. That's the way you can get your data in. That's a key automation tool. But then you think, well, how do we handle those inputs that then go into Power Query, that then go into our tables, that then reshape and cause our formulas to recalculate? And often it's about, again, back to data principles. If people, I mean, you must have had it, Paul, where you get, here's budget version 1, version 2, version 3, version final, version final A, version final.

I've been guilty of it. Not only received it, I have been guilty of it.

And so we do this, but equally, and we do it with our inputs as well. What we should do is think about if our input is a data source of some kind, we don't want that path or that name to change. Imagine if in a business, the IT department came along and said, "We've just backed up our database. We're now going to have to repoint every application to that database." Like it would, it just wouldn't happen, right? So we need to think about our inputs as that. So once you get whatever your input is, whatever that file name is, that never changes, right? When you get a new version, save that up in a backup, give it a backup version number. So you can go back, you can go back to previous versions. There's no issue there, but that file path never changes, which means that when you refresh your Power Query, it's always going to pick up from the same source. If you've got workbook links, please don't have workbook links. But if you do, it means your verbal links should be—

Well, we know your answer to that rapid-fire question.

Yes. Your verbal links should be already to the right location. And then you can use tools like Power Automate Desktop or VBA to then control that refresh piece. So that's kind of the input bit. Once you get into the formulas, then on the other end you've got the outputs, which is where it depends how your financial model or your report, whatever you're working on, what's that then used in? What's the next step? Because often with financial models, people are like, I've built my model. But if somebody then wants to use your model, let's say there's a rolling forecast and they want to bring your model into your FP&A work, how's someone then going to access your financial model? So the best way to do it is actually if you've got an output table that contains that key information that they can then take into Power Query. And that means that if the numbers in your financial model change, that's fine. It updates your output table. That table is an easy source of Power Query that can then grab those bits and they can then use it in subsequent steps. Or if you've got your financial model, it might be used in a PowerPoint presentation. You might have to present a deck and we've all, we've all had scenarios where the numbers change. So you update everything, you think you'll update your PowerPoint presentation. I once had a PowerPoint presentation that was 379 pages long. Now, I admit I was only responsible for about 120 of them, right?

Only 120.

But in that scenario, you soon learn that if someone comes along and says, "I'm just going to make a change to general provisions," that that small change is going to take— is going to cause you days of work potentially of just copy and pasting those PowerPoint presentations back in. So, you've got to think about how can you work with PowerPoint to then automate that piece? And that's again where VBA comes in so that you can then go, right, someone's made a change. I click one button, in 30 seconds my PowerPoint presentation is up to date. It's got everything I need. Potentially you've even dynamically written sentences so that your sentences then update in your PowerPoint presentation and it's then ready to go within seconds rather than thinking I've got to update all of these, I've got to copy and paste all of these PowerPoint pictures. So eradicating copy and paste is the, is It's almost the thing you've got to do. Anytime you go to copy and paste, think, how can I avoid this action? And that will often lead you to automating the process. It's copy and paste, which is, it only takes a few seconds, but it is the manual process that causes us more work.

But I thought that's why we had Ctrl+C, Ctrl+V. No, I, yes, anytime you are repeating, if you're like, I'm going to be copying and pasting every month, you should be asking yourself, is there a way to automate Yes, one-time exercise, fine, knock yourself out. Sometimes we all do it. But if it's a repeat thing, I agree with you. We should be thinking about how can we automate this? How can we speed it up? If I see a 369-page deck, I think, how can I run? But I thought I had some big ones. That is, that is probably one of the biggest we've had on here.

So I got to get that thing. It's that thing of the, you know, the, the CFO, he's going to present something. And he wants every scenario of everything so that he's ready, he's prepared no matter what comes along, he's ready for it, right? And often you see things like people, like even with Power BI, you've got this PowerPoint add-in that you can integrate from Power BI into PowerPoint, but is your CFO going to be, are they going to want to be there clicking buttons, working out what view they want? They want to have prepped and had everything there. So it was a case of, they wanted to exceed expectations, so therefore you have to provide a lot of different scenarios.

Well, I, I get it. We've all been there in different ways. I know what you're talking about. All right, so do you have a favorite automation tool within Microsoft? You know, VBA, Power Query, Office Scripts, Power Automate— maybe I missed something— Python, I guess if you want to call it automation. Is there one you really like that you probably use the most, or that's your favorite to work in?

For a long time it was VBA, but I'd say now it is, because we have this kind of cycle of inputs refresh into tables and then into formulas, I'd say Power Query is the most important one. I think that's the lowest hanging fruit. That's the 80/20 benefit there, because ultimately most of the stuff we're going to do is in the workbook. And under, you know, what the things I said a few minutes ago, you're kind of using those other tools outside of the workbook. So I'd say Power Query is the tool to spend the time on and learn to save the most amount of time.

Let's see, so that's where you'd recommend people start if they're working in Excel a lot is learning Power Query as far as automation.

Once you've cracked your formulas and then got your tables down, assuming that tables are relevant for what you're doing, it's then Power Query. That's the next bit.

Yeah, I saw, and I'll get your thoughts on this, George Mount posted, and I tend to agree with them. If someone's brand new to Excel, I'd almost start with teaching them tables with how important data is now in Power Query and then get into formulas. Talk about data structure, tables, Power Query, and then say, okay, now that you've kind of got your data into the grid, let's talk about how you use formulas to build reports and to enhance it. Thoughts on that?

100% agree. I've, I've said the same thing myself in the past is that if, if Excel education started with tables, I think people's understanding of what Excel does and how it works would be very different. So I, I would, I'm 100% on board with that tables and understanding what data is, understanding how it works rather than just, I've got this grid of stuff and I can place stuff wherever I like. Tables are, are key just to understanding data.

Yeah. I mean, it's kind of like when you have word processor, I can write a book any way I want, but nobody's going to read it if I put the table of contents in the middle and mix up the chapters.

Yep.

But yet we often do that with our data. In Excel by, you know, just throwing things all over the place because we don't think about structure, especially when we first start. And then over time we learn, oh, that doesn't lend itself to writing a formula or to automating this or whatever we're trying to accomplish.

As humans, we try and make sense of the world and we try and find patterns and things like that. And so because of that, we have an instinctive nature that we want to pre-categorize things. So we want to categorize things as early on as possible What that means is that we end up with horizontal and vertical and groupings and across worksheets because we're trying to group before we analyze because that's the way our brain thinks and we want to try and get there first. So you got to kind of break out of that human mindset of I want to categorize too. I've just got to let the data do what it needs to do and then I can work with it. That's the mindset we need to get into.

While you may know me as the FP&A guy, what you may not know is that I am also passionate about financial modeling. Like so many other financial modelers, I was self-taught. I didn't know that there was an organization dedicated to advancing the discipline of financial modeling until I discovered Financial Modeling Institute. FMI offers the Advanced Financial Modeler Accreditation, a program designed to strengthen and and validate your financial modeling skills. I am a proud holder of the AFM accreditation. Preparing for the accreditation exam made me a quicker and more confident modeler. If you want to become a better modeler, I can't recommend the AFM program highly enough. Podcast listeners save 15% on the AFM program. Just use code PODCAST at fm institute.com/podcast. I agree, we want to form a story, kind of that categorization, that grouping, see it kind of come together versus having that data at the lowest level that's designed to best work for analyzing, for optimizing. And it's very different. It's, it's a mind shift change. I agree with you. And once people learn it, it makes it a lot easier to build structure into things.

Yeah, 100%.

All right, I'm curious, what's your favorite Excel shortcut? I know it must be Ctrl+C, Ctrl+V, how much you make— you like that.

Well, actually, my favorite is Ctrl+Z, which is— I mean, let's face it, we all make mistakes. Ctrl+Z is by a long way the one that I use the most. For a long time, I had no idea that there was an undo button in Excel because I just knew Ctrl+Z. Like, Ctrl+Z works everywhere and like on all applications. Locations. So when someone clicked a button, I'm like, what are you doing? Why, why would you click a button to do that? It's Ctrl+Z. I mean, it works everywhere. So, so literally, I, for years, like, I promise you, years, I had no idea there was like an undo button that you could click. It was just Ctrl+Z works everywhere. So yeah, that's probably my favorite.

I'm probably embarrassed to admit that I actually learned the undo button before I learned Ctrl+Z or Ctrl+Z, but I love Ctrl+Z. That's a great one.

But I mean, after that is a, you know, the next one's got to be F4 just to cycle through all the dollar signs because otherwise that's just a pain, isn't it?

Yeah, that's a fabulous tool versus sitting there going, shit, okay, dollar sign, move it over, dollar sign, you're there forever versus, okay, just F4 and you start to learn, okay, it's going to be 1, 2, 3, 3 times that I hit it, just remembering which one it is depending on what you're doing.

Yep, 100%.

All right, what is the number one thing you've learned, kind of lesson that's helped you the most over your career? If there's going to be one story or lesson you've learned, what is it?

The one thing I've learned is that to make progress in most things, what, what I actually need, and I think what most people need, is a really deep understanding of things that are basic. Because often we jump into something and we don't really understand how something works But we then start working with it and then we go, well, I don't understand why this doesn't do this and why this doesn't do that. And the truth is we never went through the boring stuff. So I've got, I'm working on a Dynamic Arrays course at the moment. And I think there is a, I mean, I think there's 90 minutes of content before we even get into a function because just understanding what values are in Excel, how they work. How ranges work, what operators are, what's broadcasting, what's lifting, what's— how does logic work, what's type conversion, all of those things. There's a lot there before you even get into, you know, they're the inputs into all the functions. So you have to kind of spend time learning that. And the same is true with Power Query. Like we come into Power Query and we're greeted with this table and then we start merging and appending and unpivoting, but we don't even understand what a value is. And so it's kind of, you've got to kind of pull back from the, these are my immediate needs to, I need a deeper understanding of what the most basic things are here. Because unless you don't understand the basics, when it comes to something that's outside of that, you're like, I don't know how to manipulate this and change it into something else. So I would say a deep understanding of basic things is probably the lesson that I've learned the most and has led to the most breakthroughs of when I have an issue or a problem, Actually, it's understanding those basics that gives you solutions.

I totally agree. I think I really started to understand LOOKUP when I thought of it from the basics of, okay, I can do a positional lookup. I can do another one that's just basically like INDEX MATCH is just a map with positions. You're giving it numbers for positions and starting to realize and really think about all the different ways these work and the different ways to do it. Then I can look at a problem and go, okay, What's the right way to solve this versus how do I make this formula work in this situation? And so the more you understand, the more it really does help you be able to solve things in an efficient way. What's the funnest thing you've ever created or most unique, maybe in your personal life in Excel?

This might bring down the tone, and I apologize for this. That's okay. I once, when I was younger in my career, I created a I think a bathroom break calculator is probably the best way to describe it. How much did I earn today whilst in the bathroom? I think is probably the— And when you work out that you've earned, you know, a few pounds in the UK or a few dollars in the US, you're like, wow, that's good money. That is just for doing something else.

I love that. Thank you for sharing. I appreciate being willing to share that one. You still have the calculator?

No, no, man.

I was thinking we could put it on a link to it in the show notes, but I'm—

yeah, no, sorry.

All right, we're gonna move into the section that everybody gets to go through. This is our rapid-fire section. I know you know how this works, but I'll set out the ground rules real quick. No more than 10 seconds to answer the question. You can't say it depends, so no consultant answers. You got to pick one or the other side, kind of a yes or no. And then at the end, you can elaborate on one or two that you feel like there should be more nuance to, that you feel most strongly about. So you ready? Yep. All right, and I have one or two surprises for you on this one.

Okay.

All right, here we go. Circular or no circular references?

No circular.

VBA or no VBA?

In a financial model, no VBA. Outside of a financial model, Yes, but in a financial model, no.

All right, dynamic arrays in a financial model, yes or no?

Uh, yes.

External workbook links, no. Most people say that with authority. I'm like, no, I just take that question out. I do have a few yeses though. Name ranges, yes or no?

Yes.

All right, do you follow a formal standards board when you're modeling, like FAST or SMART or some of those others?

No, but I wish I had known about them at the time I did more financial modeling. So no, I don't, but I wish. I think I should.

I like that. All right, so these are the new ones for you. Should financial modelers learn Power Query?

Yes.

Should financial modelers learn Power BI?

No, no.

Should they learn Python in Excel?

No.

All right, will Excel ever die?

Yes, but why not? What, what Microsoft would be wise to do would be, and I think, I think they're kind of on this path, but this is just my opinion. This is no insight whatsoever. So I think that ultimately Microsoft will build up Excel Online and then at some point they will kill Excel Desktop and we will all just use Excel Online or in Desktop it'll be a skinned version of Excel Online. So I think Excel will be. Different to what it is today because things like VBA we know won't work. We know that the data model in Power Pivot won't work. So I think that's where it's headed. So I think Excel will die, Excel Desktop will die and be replaced with Excel Online, which is developing in a slightly different way. So I think that it will, yes, it will die and be replaced by itself.

So you're saying I got to start spending some time on the online version.

No, no, I think you've got to use the best tool and the best tool at the minute is Excel desktop. Once online starts to get up there, then I think the answer is yes, but I can't see why they would take any other direction. It's much better if everyone's on the same version. You don't have to worry about backwards compatibility. They don't have to support previous versions. So I think, I mean, if that's just a sensible way for it to go from an economics perspective.

I tend to agree with you. I've seen that. I think there will be an online version, whether they call it Excel or something else. I think that's ultimately where we're heading. The biggest challenge is all those files, all the VBA that's used. How long does it take to make that cut over?

Yep.

If VBA wasn't an issue, I think it would be much sooner than probably any of us realize.

I think so. Yeah, I agree.

All righty. So there's, there's the one we got to go deep on. Next question for you. I got a few more. Will AI build the models for us in the future?

Yes, it will. But we won't want to use them because we will have better actual intelligence alternatives.

I like it. I might have to ask a little bit more about that one here at the end. Sheet cell protection, yes or no? Yes. All right. Do you believe financial models are the number one corporate decision-making tool?

No.

What is?

So my thought process on this is that the humans are ultimately irrational, right? So a financial model is trying to be a logical tool. So therefore, it's almost like it's the wrong tool for humans to work with because we're going to make irrational decisions based on that financial model. And you think about the— have you heard of the endowment effect? That's where people, like, if you own something, uh, if I am— I own this pen, you can't see this on the podcast, apologies everyone. I own this pen. If I came to sell this pen, I would want more for this pen than if I were to want to buy this pen secondhand. So because I own it, I irrationally place more value on this pen than it actually has.

Sure.

And I think, and I think the, the risk of loss in humans is higher than the benefit from gain. So therefore, I think there's an emotional relationship there, which financial models can never understand, but they are an important aspect of that in terms of that decision-making. But the thing that I hope is the most important decision-making tool is alignment to an organization's purpose and why. So I would hope that it's like, this is what we do for our customers, this is how we solve their problems, we want to be better, we want to improve, therefore this thing that we're going to do, it might not give us the most profit, but it is the right thing to do to support our customers and our vision and what we're trying to achieve. That's what I hope is the number one decision-making tool. Alignment with strategy and purpose and why.

I love that. Alignment with the values, the purpose, the strategy is the number one guiding thing in making your decisions. I haven't had an answer that way before, and I like that. That makes a lot of sense. All right, your lookup function of choice.

I told you this before, Paul, so I know you're ready for this. My favorite lookup function is FILTER.

Why?

Because, so I mean, going back to the data thing that we discussed at the start, right? There's what is a lookup function, right? It goes down a list, it finds a match, and then it returns something else. What is, what's a filter? Well, it returns all those items that match and then return them. So ultimately a lookup and a filter, if you're in a, if you're in the scenario of a unique record, it's going to return exactly the same thing. If you're not in the situation of a unique record, the number of times people go, but I want to return the second lookup. I want to return this, I want to return that, and filter gives us all of those scenarios. So from a data perspective, it's almost as if a lookup doesn't really exist. Think about it in terms of data. If you think about Power BI or Power Pivot or any of those kind of BI tools, they do two things ultimately. They filter and they aggregate. They're kind of the two activities that they do. So it's almost like a lookup isn't really a thing. It's just a filter that then returns whatever that thing is. So I think filter is the ultimate lookup tool because it does everything that you could ever want. Now, saying that, do I always use filter? No, because performance can be a dog, right? So there's an element of It's not designed to do all of that heavy lifting, but it is— it can do, and it's flexible enough, and it does anything and everything. So I would say FILTER. If not, if you're in a performance scenario, then I'd say XLOOKUP is your option, but FILTER is my favorite.

All right, thank you. I appreciate that and the explanation there. All right, so we're going to go ahead and start our wrap-up here. We got just a few minutes left. I've really enjoyed our chat so far. First, I want to ask you, what services do you offer? Someone's wanting to get better at Excel or analysis modeling. Tell a little bit about the services you offer.

The main place where we can benefit your listeners is we run a training academy called over at exceloffthegrid.com. It is an online platform. It's a membership. We have training courses in there. We have tools. We have an ebook library, we have live events, we have a community. So there's lots of things going on. And our main aim of that entire membership, that entire platform, is to try and help people do their work faster, to automate their work so that they can go home on time. I think our website tagline is, "Make working late a thing of the past." And it's just trying to teach people those elements around structure so that therefore they can make Excel as easy as possible to work with. And actually, in many ways, if you set things up the right way, you spend less time in Excel because it just works for you. So that's where we can provide the most benefit. And we've got— we have honestly, we've got members who have saved hundreds of hours. We had one member who at their month end, they used to spend every— their wife's birthday was always at month end just because of the calendar. For the first year ever, I've got to spend the evening with my wife on her birthday because of the things that you've taught me in this academy. So I mean, it's stories like that that just make you— we can talk about Excel and learning and all that, but when you know that you're making a real difference to people's lives and that's the thing that you really do it for.

Agree. That's the rewarding part when you know somebody saved hours or it made a difference in their life versus, hey, I taught them the difference between a VLOOKUP and an XLOOKUP. You know, on the surface, not really important to life. I hear you. I agree with you. I love when you are able to make a difference. So thanks for sharing about your services. I know Excel Off the Grid is a great, great resource and you do great work there. If our audience wants to get in touch with you, is Excel Off the Grid the best place? LinkedIn? What's the best way for them maybe to reach out? They listen to this and like, I want to reach out.

So the best place is either Excel Off the Grid. We've got a— there's a link there. You can contact customer support. They'll come through to me. We can have a chat with people or just on LinkedIn. LinkedIn is— that's me. It's just under my name. And so yeah, LinkedIn, you can DM me there. You can put something in there. I post on there. I try to be more regular than I am, but equally, yeah, I try and respond to messages as I can. Sometimes I get a lot and sometimes, you know, you can't keep up to date with all of them, but Yeah, if people say I heard you on the podcast, then let me know and I'm happy to talk to them.

Perfect. Thank you, Mark. For anyone who's interested, Mark also has a podcast he does with a few other people called the On Pivot Show, right?

The On Pivot Show, yes.

All right. So any Power Query nerds will appreciate that. If not, you should appreciate it.

It's for Power Query, it's for Power BI. From a financial modeling perspective, we've got Giles Male on there. He's holding up the financial modeling end. So we cover the full spectrum of topics on Excel and Power BI, data, financial modeling, hopefully everything that your listeners are interested in.

All right, great. Well, check that out as well. Thanks again for joining me today, Mark. I really enjoyed chatting with you for a few minutes.

Thanks for having me. It's been great to be here.

Thanks. Financial Modelers Corner was brought to you by the Financial Modeling Institute. This year, I completed the Advanced Financial Modeler certification. And it made me a better financial modeler. What are you waiting for? Visit FMI at www.fminstitute.com/podcast and use code podcast to save 15% when you enroll in one of the accreditations today.