The Secrets to Automating Data Integration and Reporting in Excel 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 experience?

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

Welcome to Financial Modelers Corner where we discuss the art and science of financial modeling with your host, Paul Barnhurst. Financial Modelers Corner is sponsored by Financial Modeling Institute. Welcome to Financial Modeler's Corner. I am your host, Paul Barnhurst. This is a 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 turns out that once you take out those hard coded numbers, things just don't balance. And actually you chase around these hard coded 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 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, 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 five 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 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 method 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 offsheet 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 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. You've all been there. Those are the worst. Because you just, you have no idea what landmine 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. You know, 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've made, who's made them. So it's kind of, it's outside of, 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 hard coded and move on and it worked well enough. Would 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 a accounting and finance degree. And then I came out of that, I went into accounting as a profession. I went through a standard kind of 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 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 accounts do and this is what finance do. But having taken an accounting and finance degree. I just view it as, it's all the application of the same types of things. One of its retrospective, one of it's prospective. But ultimately it's the same, 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, they just go together. So when I see on LinkedIn you get the difference between accounting and finance and I think I just, 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 two sides of the same coin. But I like to joke and I get myself in trouble. Do you know what the difference between an accountant and an FPA professional is?

Go on.

When an accountant gets creative, they go to Jeff. When an FPA professional gets creative, they get promoted.

I think that that may be the key distinction.

Yeah, exactly. Alrighty. 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 was a, I worked in, this is when I was, I worked in financial, financial accounting. At the time that was a lot of, kind of legal and statute accounts, a lot of balance sheet work and the business that I was working with, they had a restructure. My, my team was made redundant, I was made redundant and at that point, I mean I got, I got another job exceptionally quickly. But at that point I thought I need, 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, 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 the other thing full time. So I said, 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 that you know where the challenges you're having at work. So 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 five 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 don't really built that. The foundations a long time before.

Yeah, it's amazing how our better half 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 counter offer. 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, I get it. I can relate my own way. That's a great story though. And how, 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, it was tough. I mean the, I had my financial model, which was my, which is my run rate to zero. And in that there was the trigger point at which, you know, assuming, assuming we, we earn no money from this. How long, 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 there? I mean I, there was a financial model behind all of that, but it was tough. I mean the original plan was to, was to do predominantly consulting work and then do. And then slowly over time build up a 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 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 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 does even something as simple as a solopreneurship where you're thinking, I'll do two or three 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 bars at 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 the. You've got a plan until you get to day one. And I must admit, on so 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 can 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?

Yeah.

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, one of the key things 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 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, 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.

Yep.

You get. So you have your, you know, you've got your tabular data, it's in columns and hopefully, hopefully it's not cross tab. You don't cross tab. Everything goes down. But then, but then, even then somebody. And I don't want to get controversial, but no, please do.

That's. 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 I need to learn sumif as well. This is before sumifs existed. So you then learn sumif, you're like, I do know everything now, but it's not till a long time later that you go, you think 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 for 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 input 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. 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 your one of your columns, then you're like, what on earth are you doing? You've broken everything. So there is a 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, 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, as a slightly different discipline.

I love the way you explained it especially around like Vlookup isn't designed for data analysis. I'd never thought 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 two years of report writing SQL, you know, a lot of reports in Excel. And then I moved into an FP and a role and started building models with having no good design principles or any of those type of things and build a lot of Franken models. But I've always been really good and very valuable on analyzing the data, 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 cross tab. 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 at a project, 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 one often feeds into the other. If you've got your rolling forecast, you're going to do 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 and a model of 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 within, inside companies. It's from a. That kind of mindset, you know, the whole investment and banking world and all of that kind of stuff is not my, it's not my area of mine either.

We're on the same page, so you've.

Got kind of one feeds into the, 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 there's a method and 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 I do more data analysis than financial modelling, but I'm happy with both. I like, 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, 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 two, three 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 and 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 on column A or on column B, but that's. I often joke that you're. 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 biggest change by a long way. And we then kind of follow that through. It's 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 best tool that brings that along. Microsoft then kind of bought in the, the Power Pivot aspect 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. So you've got your kind of inputs get reshaped into data, then you can calculate on that and get it into whatever layout you want you happen to need so that, that they're the three key things. Tables, Power Query, dynamic arrays are what's really setting us free from a lot of the challenges of the past.

FP and a guy here and as you know, I am very passionate about financial modeling in the Financial Modeling Institute's mission. I have been a huge fan of the FMI for years and I was super excited when they decided to sponsor the Financial Financial Modelers Corner. I recently completed the Advanced Financial Modeler certification and loved the entire experience. It was top notch from start to finish. I am a better modeler today for having completed the certification. I strongly believe every modeler needs to demonstrate they are a qualified financial modeler and one of the best ways to do that is through the FMIS program. Earning the accreditation will demonstrate to your current and future employers that you are serious about financial modeling. What are you waiting for? Visit www.fminstitute.com podcast and use code PODCAST to save 15% when you enroll in an accreditation today. Agree. They've 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 court 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 don't, don't waste 20 minutes doing this every month. Spend an hour up front and learn how to automate it or two hours or whatever 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 a one plus B one and then you change A one 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 I don't think about is 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. 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 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 the 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 word but links, please don't have word but links. But if you do, it means your worker link should be.

We know your answer to that rapid fire question.

Yes, your worker link 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 depends how your financial model or your report whether you're working on what's that then used in what's the next step? Because often with financial models, people like I've built my model. But if somebody then wants to use your model, let's say it is a rolling forecast and they want to bring your model into your FP and a work how someone then going to access your financial model. So the best way to 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 you're the number, 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 had scenarios where the numbers change. So you update everything you think of 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 cause you days of work potentially of just copy and pasting the 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 almost the thing you've got to do. Anytime you go to copy and paste, think how can I, 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 Control C, Control 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 this 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.

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, 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 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 vba? Power Query, Office Script, Power Automate. Maybe I missed something. Python, I guess if you want to call it automation, is there one you really like 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 8020 benefit there because ultimately most of the stuff we're going to do is in the workbook and under 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 and say.

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, now 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 said the same thing myself in the past, is that 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'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 key. Just 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. But yet we often do that with our data in Excel by 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 the, that, 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, that's the mindset we need to get into.

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, 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 Control C, Control V, how much you make.

My favorite is Control Z, which is. I mean, let's face it, we all make mistakes. Control 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 Control Z, like, Control Z works everywhere on all applications. So when someone clicked a button, I'm like, what are you doing? Why would you click a button to do that? It's Control Z. I mean, it works everywhere. 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 Control 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 Control Z or Control Z, but I love Control 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. All the dollar signs. Because otherwise that's just a pain, isn't it?

Yeah, that's a fabulous tool versus sitting there going, okay, dollar sign, move it over, dollar sign, you're there forever versus okay, just F4, and you start to learn. Okay, it's gonna be one, two, three, three 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 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, 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'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, how does logic work, what's type conversion, all of those things, there's a lot there before you even get into. They're the inputs into all the functions. So you have to spend time learning that. And the same is true of 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 our value is. And so 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. And if you don't understand the basics, when it comes to something that's outside of that, 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. 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, a map, a map with position. 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.

When I was younger in my career, I created a. I think a bathroom break calculator is probably the, the best way to describe it. How much did I earn today whilst in the bathroom? I think is the. Is probably the. And when you work out that you earn, you know, a few pounds in the. In the UK or a few dollars in the us you're like, wow, I should you know, that's. 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 eight.

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?

Yes.

External workbook links?

No.

Most people say that with authority. I'm like, 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 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 what Microsoft would be wise to do would be. And 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. In Power Pivot won't work. So I think that's. 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 will be a slow. Which is developing a slightly different way. So I think that 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. 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, 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.

Alrighty. So there's. There's the one we got to go deep on. Next question for you. 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. Cheat 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. If 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 second hand. So because I own it, I irrationally place more value on this pen than it actually has.

Sure.

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 worry. 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 why 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, 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, I know you're in 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'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 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 we 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 filters, 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've 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, the main place to where your, your listeners can, well, where we can benefit your listeners is we run a, a training academy called over@excelofthegrid.com it is a, it's 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, they can go home on time. I think our website tagline is make working like a thing of the past and it's just, it's trying to teach people that 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 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, was always at month end just because of the timing 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 all that but when you know that you're making a real difference to people's lives and that, you know, that's a, that's the thing that you really, 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 X Look up, 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's. 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 probably either Excel off the Grid, we've got a. There's a, there's a link there. You can contact customer support, they'll come through to me. We can hire to 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 on the. I try to be more, 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 them. But yeah, if people say, I heard you on the podcast, then let me know and I'm happy to talk to them.

Right, perfect. Thank you, Mark and I. For anyone who's interested, Mark also has a podcast he does with a few other people called the On Pivot Show.

Right, The Implement 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. We've got. So from a financial modeling perspective, we've got Giles Mail 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. We'll 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 Modeler's Corner was brought to you by the Financial Modeling Institute. This year I completed the Advanced Financial Modeler certification and it made me a better financial modeler. What are you waiting for? Visit fmi@www.fminstitute.com podcast podcast and use code podcast to save 15% when you enroll in one of the accreditations today.