The Secrets to Automating Data Integration and Reporting in Excel With Mark Proctor
In this episode of Financial Modelers Corner, host Paul Barnhurst (aka The FP&A Guy) is joined by guest Mark Proctor. Together, they delve into the intricacies of financial modeling and data analysis, with a focus on building efficient models and leveraging automation in Excel. Mark shares his deep expertise on why structure is key, how to automate tedious tasks, and how Excel continues to evolve to meet the demands of modern financial professionals.
Mark Proctor is a seasoned financial professional with a background in accounting and finance, an Excel MVP, and the founder of Excel Off the Grid, a platform dedicated to improving efficiency and effectiveness in Excel. With decades of experience, Mark's insights are invaluable for anyone looking to optimize their use of Excel for financial modeling and beyond. He has helped countless professionals streamline their work through better data structure and automation techniques.
Key takeaways from this week's episode include:
Key differences between financial modeling and data analysis
Importance of automation in Excel and how tools like Power Query and Power Pivot can drastically improve efficiency.
Why traditional tools like Vlookup aren’t ideal for modern data analysis and what functions you should be using instead.
How to approach building models that are flexible and easy to use, even when multiple stakeholders are involved.
Practical strategies for reducing manual processes and eliminating the need for repetitive tasks in Excel.
Here are a few quotes from Mark Proctor:
"Hard coding is the enemy of a flexible financial model. Once you start hard coding, you lose the ability to see what's really driving the model."
"One of the key takeaways from bad models is that manual adjustments should always be documented and trackable."
"Automation should be about freeing up your time to focus on high-value tasks, not just about making things faster."
As Excel continues to evolve with powerful features like dynamic arrays and advanced automation, the tools discussed in this episode are essential for anyone looking to keep pace with the future of financial modeling. Mark’s emphasis on mastering the basics and thinking structurally will help you work faster and more effectively.
Follow Mark:
Website - https://exceloffthegrid.com/
Follow Paul:
Website - https://www.thefpandaguy.com
LinkedIn - https://www.linkedin.com/in/thefpandaguy
YouTube - https://www.youtube.com/@thefpaguy8376
Follow Financial Modeler's Corner
Newsletter - Subscribe on LinkedIn-https://www.linkedin.com/build-relation/newsletter-follow?entityUrn=7079020077076905984
Sign up for the Advanced Financial Modeler Accreditation Today and receive 15% off by using the special show code ‘Podcast’.
Visit www.fminstitute.com/podcast and use the code “Podcast” to save 15% when you register.
In today’s episode:
[01:54] - Financial Modeling Horror Stories
[04:55] - Lessons from Bad Models
[06:28] - Mark's Career Journey and Background
[12:59] - Data Analysis vs. Financial Modeling
[19:17] - Excel as a Data Analysis Tool: Key Innovations
[22:55] - Automation in Excel and Practical Tips
[33:59] - The Importance of Mastering Excel Basics
[44:19] - Wrap-Up and Closing Remarks
Full Show Transcript
[00:01:14] Host: Paul Barnhurst: Welcome to Financial Modelers 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.
[00:01:50] Guest: Mark Proctor: Thanks for having me. I'm excited to be here.
[00:01:52] Host: Paul Barnhurst: 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?
[00:02:00] Guest: Mark Proctor: Well, if you allow me, I'd like to go through two. The first one was, it was a big model. 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 when the model was first created, I think it was 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. 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 if they seem reasonable. And so that was possibly the worst model. I've seen it a lot. Later I got to rebuild that model from scratch using good principles.
[00:03:04] Guest: Mark Proctor: 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 five year plan model. It's all about strategy and direction. So it was done at an exceptionally high level. This is the stuff we need. The problem is that as soon as anyone was asked any questions, it was like, but 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 and the reality didn't quite come together.
[00:04:01] Host: Paul Barnhurst: Yeah. Not a lot of good when everybody has to go off and do their own big exercise. You need that balance. Either you keep it high level the whole way through or you go make it a little more detailed. But it sounds like that one just created more work for everybody.
[00:04:17] Guest: Mark Proctor: I think it did.
[00:04:18] Host: Paul Barnhurst: 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 have no idea what landmines you're walking into?
[00:04:31] Guest: Mark Proctor: 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 whether the model is working not just to generate the numbers and the one sided adjustments that you're then going to make.
[00:04:54] Host: Paul Barnhurst: Agreed. So any key takeaways from those experiences?
[00:04:58] Guest: Mark Proctor: One of the key ones. So around and this is something that I've done recently and I think I think you mentioned it on a previous session called that I started to then build in a place in your financial model, however good your financial model is, people always want to make some level of manual adjustment. You know, it might be operating costs are too high and 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. 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 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 for 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.
[00:05:58] Host: Paul Barnhurst: 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.
[00:06:36] Guest: Mark Proctor: I felt I was quite good at maths at school, so I ended up going into accounting and finance degree, and then I came out of that and went into accounting as a profession. But you know, I went through a standard audit. Then I had my own small, 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, financial control and those kind of aspects. So, you know, I've worked across a lot of areas of accounting and finance. I know that some people like to draw distinctions between what accounts do and this is what finance do. But having taken an accounting and finance degree, I just 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. 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, I think it's just a blur as to what the difference is. And I'm happy to take on either.
[00:07:45] Host: Paul Barnhurst: You know what I've always said? I don't have an accounting degree, so I'm one of those like, yeah, 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. Do you know what the difference between an accountant and an FP&A professional is?
[00:08:01] Guest: Mark Proctor: Go on.
[00:08:02] Host: Paul Barnhurst: When an accountant gets creative, they go to jail. When an FP&A professional gets creative, they get promoted.
[00:08:08] Guest: Mark Proctor: That may be the key distinction.
[00:08:11] Host: Paul Barnhurst: Yeah, exactly. Alrighty. So today you run your own business. How did you go from kind of, you know, accounting, finance to different roles to deciding starting your own business was the right thing for you?
[00:08:24] Guest: Mark Proctor: I worked in financial accounting at the time. There was a lot 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 that it's a nice little addition.
[00:09:03] Guest: Mark Proctor: And then as I changed through a couple of other roles, the last role I was in, I wasn't a good culture fit. It's 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, 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 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 that 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, I think I was an MVP at that point, so I'd already had some level of knowledge and experience in that, in that kind of space. So it was a good natural step. But I'd already built the foundations a long time before.
[00:09:59] Host: Paul Barnhurst: Yeah. It's amazing how our better halves often know us better than we know ourselves. Yeah, I remember there was a job that I'd 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, you know? And so I get it. I can relate in 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?
[00:10:36] Guest: Mark Proctor: It was tough. I mean, I had my financial model. Which is my run rate to zero. And in that there was the trigger point at which, you know, assuming 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, 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 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.
[00:11:30] Host: Paul Barnhurst: Isn't it funny? I find it so true. Almost everybody I 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, everybody does 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.
[00:11:57] Guest: Mark Proctor: I mean, so one of the first questions that you kind of ask before you step out on your own is, have I got the ability to roll with those punches? You know, there's that quote by Mike Tyson. I think it's like everyone's got a plan until they get punched in the face or 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 out on LinkedIn and then I've never had so many job offers. You put something on LinkedIn and 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 was the path.
[00:12:42] Host: Paul Barnhurst: You know, and I can totally tell we're talking to a finance person because you've mentioned options several times. Optionality, right? We think in options a lot of times. I noticed 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 and 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?
[00:13:17] Guest: Mark Proctor: I think, 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 Schnoor 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 in 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, yeah, you get so you have your, you know, you've got your tabular data, it's in columns. And hopefully it's not a 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.
[00:14:20] Host: Paul Barnhurst: No please do. That's what we like to show being a little controversial. Go for it.
[00:14:24] Guest: Mark Proctor: 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 some F as well. This was before Sumifs existed, so you then learn some F. 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, I need to look up to the left and you then start building in like, oh, I just need to copy that, 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 inputs should always 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.
[00:15:30] Guest: Mark Proctor: 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. If 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.
[00:16:39] Host: Paul Barnhurst: I love the way you explained it, especially around like 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 two 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 build a lot of Franken models. 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 write 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 explained 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, you prefer getting your hands dirty and doing some data analysis or building a model.
[00:17:41] Guest: Mark Proctor: These days I probably spend more time with data analysis than I do building models, but ultimately, you know, one often feeds into the other. If you've got your role, if you've got your rolling forecast right, you're going to do your data analysis piece of getting your data into the right format that's going to feed into your forecast. But then if you want your rolling forecast to then appear in your spam or other things, that's going to come back out and be part of your data analysis. So, I mean, you can probably tell from the way I, from 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 interest.
[00:18:23] Host: Paul Barnhurst: Mine either. We're on the same page.
[00:18:25] Guest: Mark Proctor: So you've got kind of one feed into the other. And just so I would probably spend more time now in data analysis. And that's, 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 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 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.
[00:19:06] Host: Paul Barnhurst: Yeah. 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 or 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.
[00:19:42] Guest: Mark Proctor: The first change in 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 how on column A or on column B. But 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, 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 were the biggest change by a long way. And then so 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 brought in the Power Pivot aspect in the at the same time. So if you want to get 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 got your kind of inputs reshaped into data. Then you can calculate that and get it into whatever layout you happen to need. So 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.
[00:21:25] Host: Paul Barnhurst: FP&A guy here, and as you know, I am very passionate about financial modeling and the Financial Modeling Institute's mission. I have been a huge fan of the FMI for years, and I was super excited when they decided to sponsor the Financial Modelers Corner. I recently completed the Advanced Financial Modeler certification and love the entire experience. It was top notch from start to finish. I am a better modeler today for having completed the certification. I strongly believe every modeler needs to demonstrate they are a qualified financial modeler, and one of the best ways to do that is through the FMI's program. Earning the accreditation will demonstrate to your current and future employers that you are serious about financial modeling. What are you waiting for? Visit www.fminstitute.com/podcast and use code Podcast to save 15% when you enroll in an accreditation today.
[00:22:31] Host: Paul Barnhurst: Agree. They've really made so much more available. And as they 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 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 up front and learn how to automate it, or two 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.
[00:23:18] Guest: Mark Proctor: 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 plus B1. Yep. 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 I then 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 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 pulled where you get. Here's the budget. Version one. Version two. Version three. Version final. Version finalized version.
[00:24:25] Host: Paul Barnhurst: I've been guilty of it. Not only received it, I have been guilty of it.
[00:24:29] Guest: Mark Proctor: 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 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 word that links please don't have word but links. But if you do, it means your word links should be.
[00:25:22] Host: Paul Barnhurst: We know your answer to that rapid fire question.
[00:25:24] Guest: Mark Proctor: Yes, your web 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 depends how your financial model or your report, wherever 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 there's a rolling forecast and they want to bring your model into your FP&A work, how is someone then going to access your financial model? So the best way 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 for 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 then go 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?
[00:26:49] Host: Paul Barnhurst: Only 120.
[00:26:51] Guest: Mark Proctor: 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 30s. 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 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 only takes a few seconds, but it is the manual process that causes us more work.
[00:27:53] Host: Paul Barnhurst: 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. So I got that.
[00:28:29] Guest: Mark Proctor: It's that thing of the, you know, 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 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 the expectations. So therefore you have to provide a lot of different scenarios.
[00:29:04] Host: Paul Barnhurst: Oh, 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 your favorite to work in?
[00:29:26] Guest: Mark Proctor: 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 over 20 benefit there. Because ultimately most of the stuff we're going to do is in the workbook and end up, 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 then if you want to save the most amount of time.
[00:30:01] Host: Paul Barnhurst: I'd say that's where you'd recommend people start if they're working in Excel a lot is learning Power Query as far as automation.
[00:30:08] Guest: Mark Proctor: Once you've cracked your formulas and then got and got your tables. Now assuming that tables are relevant for what you're doing is then Power Query. That's the next bit.
[00:30:18] Host: Paul Barnhurst: 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 and 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 into a handset. Thoughts on that?
[00:30:41] Guest: Mark Proctor: 100% agree. I've said the same thing myself in the past. 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 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 key just to understanding data.
[00:31:08] Host: Paul Barnhurst: Yeah, I mean, it's kind of like when you have a 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.
[00:31:35] Guest: Mark Proctor: As humans, we try to 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 that our brain thinks and we want to try and get there first. So you've got to kind of break out of that human mindset of, I want to categorize to 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.
[00:32:12] Host: Paul Barnhurst: 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 a mind shift change. I agree with you. And once people learn it, it makes it a lot easier to build structure into things.
[00:32:35] Guest: Mark Proctor: Yeah, 100%.
[00:32:36] Host: Paul Barnhurst: All right. I'm curious, what's your favorite Excel shortcut? I know it must be control C. Control V, how much do you like those?
[00:32:44] Guest: Mark Proctor: Let's see. 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 Ctrl Z, like Ctrl Z works everywhere and like on all applications. 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 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.
[00:33:22] Host: Paul Barnhurst: 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.
[00:33:31] Guest: Mark Proctor: 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.
[00:33:39] Host: Paul Barnhurst: 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. You start to learn okay. It's going to be one, two, three, three times that I hit it just by remembering which one it is depending on what you're doing.
[00:33:57] Guest: Mark Proctor: Yep, 100%.
[00:33:58] Host: Paul Barnhurst: 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?
[00:34:09] Guest: Mark Proctor: 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 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, a function because just understanding what, what values are in, in, in Excel, how they work, how rangers 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, 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 these are my immediate needs so I need a deeper understanding of what the most basic things are here. Because 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 when I have an issue or a problem. Actually, it's understanding those basics that gives you solutions.
[00:35:48] Host: Paul Barnhurst: I totally agree, I think I really started to understand lookups 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 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.
[00:36:33] Guest: Mark Proctor: Bring down the tone and I apologize for this. That's okay. 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 it's probably.. 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, you know, that's that's good money. That is just for doing something else.
[00:37:00] Host: Paul Barnhurst: I love that. Thank you for sharing. I appreciate you being willing to share that one. You still have the calculator?
[00:37:07] Guest: Mark Proctor: No, no man.
[00:37:08] Host: Paul Barnhurst: I was thinking we could put it on a link to it in the show notes, but. Yeah.
[00:37:12] Guest: Mark Proctor: No, sorry.
[00:37:15] Host: Paul Barnhurst: All right. We're going to 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 10s 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 1 or 2 that you feel like there should be more nuance to that you feel most strongly about. So you ready?
[00:37:42] Guest: Mark Proctor: Yep.
[00:37:42] Host: Paul Barnhurst: All right. And I have 1 or 2 surprises for you on this one.
[00:37:45] Guest: Mark Proctor: Okay.
[00:37:46] Host: Paul Barnhurst: All right. Here we go. Circular or no circular references?
[00:37:50] Guest: Mark Proctor: No circular.
[00:37:51] Host: Paul Barnhurst: Vba or no VBA?
[00:37:53] Guest: Mark Proctor: In a financial model. No VBA outside of a financial model? Yes, but in a financial model, no.
[00:37:59] Host: Paul Barnhurst: All right. Dynamic arrays in a financial model. Yes or no?
[00:38:03] Guest: Mark Proctor: Yes.
[00:38:04] Host: Paul Barnhurst: External workbook links.
[00:38:06] Guest: Mark Proctor: No.
[00:38:07] Host: Paul Barnhurst: 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?
[00:38:15] Guest: Mark Proctor: Yes.
[00:38:16] Host: Paul Barnhurst: All right. Do you follow a formal standards board when you're modeling, like fast or smart or some of those others?
[00:38:23] Guest: Mark Proctor: No. 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.
[00:38:29] Host: Paul Barnhurst: I like that. All right. So these are the new ones for you. Should financial modelers learn Power Query?
[00:38:36] Guest: Mark Proctor: Yes.
[00:38:37] Host: Paul Barnhurst: Should financial modelers learn power BI?
[00:38:40] Guest: Mark Proctor: No.
[00:38:41] Host: Paul Barnhurst: Should they learn Python in Excel?
[00:38:43] Guest: Mark Proctor: No.
[00:38:44] Host: Paul Barnhurst: All right. Will excel ever die?
[00:38:46] Guest: Mark Proctor: Yes. But when. 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 will 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 PowerPivot 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 will be developing in a slightly different way. So I think that it will, yes, it will die and be replaced by itself.
[00:39:37] Host: Paul Barnhurst: So you're saying I got to start spending some time on the online version?
[00:39:41] Guest: Mark Proctor: 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. 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.
[00:40:03] Host: Paul Barnhurst: 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? Yeah, if VBA wasn't an issue, I think it would be much sooner than probably any of us realize.
[00:40:24] Guest: Mark Proctor: I think so, yeah, I agree.
[00:40:26] Host: Paul Barnhurst: All righty. So 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?
[00:40:33] Guest: Mark Proctor: Yes it will, but we won't want to use them because we will have better actual intelligence alternatives.
[00:40:40] Host: Paul Barnhurst: 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?
[00:40:46] Guest: Mark Proctor: Yes.
[00:40:47] Host: Paul Barnhurst: All right. Do you believe financial models are the number one corporate decision making tool?
[00:40:52] Guest: Mark Proctor: No.
[00:40:53] Host: Paul Barnhurst: What is?
[00:40:55] Guest: Mark Proctor: So my thought process on this is that 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 head of the endowment effect. That's where people like if you own something, if I own 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 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.
[00:42:25] Host: Paul Barnhurst: 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.
[00:42:39] Guest: Mark Proctor: I told you this before, Paul, so I know, you're in for this. My favorite lookup function is filter.
[00:42:45] Host: Paul Barnhurst: Why?
[00:42:46] Guest: Mark Proctor: Because. So I mean, going back to the data thing that we discussed at the start, right. There's a lookup function. Right. It goes down a list. It finds a match and then it returns something else. What is 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 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 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 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.
[00:44:16] Host: Paul Barnhurst: 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? If someone's wanting to get better at Excel or Analysis modeling, tell a little bit about the services you offer.
[00:44:36] Guest: Mark Proctor: The main place where your listeners can, where we can benefit your listeners is we run a training academy called Excel off the grid. Com. It is an online platform. It's a membership. We have training courses there. We have tools. We have an e-book 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, you know, our website tagline is to 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 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 day. Their wife's birthday was always at month end, just because of the timing of the calendar. And for the first year ever, I've got to spend the evening with my wife on her birthday because of the things that that you've told me in this academy. So, I mean, it's stories like that that just make you 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, you know, that's a, that's the thing that you really do it for.
[00:46:00] Host: Paul Barnhurst: Agreed, that's the rewarding part. When you know somebody's 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 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.
[00:46:35] Guest: Mark Proctor: The best place is probably either Excel off the grid. 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 it'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 there. 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.
[00:47:07] Host: Paul Barnhurst: All right. Perfect. Thank you, Mark, for anyone who's interested, Mark also has a podcast he does with a few other people called the Unpivot show. Right.
[00:47:15] Guest: Mark Proctor: The Unpivot Show. Yes.
[00:47:16] Host: Paul Barnhurst: All right. So any Power Query nerds will appreciate that. If not, you should appreciate it.
[00:47:21] Guest: Mark Proctor: It's the 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.
[00:47:38] Host: Paul Barnhurst: All right. Great. Well check that out as well. And thanks again for joining me today. Mark, I really enjoyed chatting with you for a few minutes.
[00:47:44] Guest: Mark Proctor: Thanks for having me. It's been great to be here.
[00:47:47] Host: Paul Barnhurst: Thanks.
[00:47:48] Host: Paul Barnhurst: 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 model. 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.