Secrets to Building Error-Free Financial Models with Cameron Hay & Nicholas Hay

If you can understand how cash flows around a business, that's how you really actually get in and understand a business.

Dynamic array Excel treats it as one thing. You need lambdas for that. Otherwise it's going to create circularity and just not work. Lambdas are just basically functions built in Excel which are custom. You build your own function and then you can then reference it later on as if it's a native Excel function.

The real benefit of using dynamic arrays and lambdas is when the full model is dynamic arrays and lambdas. If you start mixing and matching, I think you lose a lot of the power.

There is no substitute for testing to test computer code is reading it most efficient way is testing it. Named range for us would typically contain 2000 values for example. So it's very powerful to be able to reference that as just a named range when you're typing in formulas. Massively speeds up the build process.

Just making sure you keep all hard coded values in the same place.

When you're working with dynamic arrays, it's quite important to have.

Welcome to Financial Modeler's 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 podcast is where we talk all about the art and science of financial modeling with distinguished financial modelers from around the globe. The Financial Modeler's Corner podcast is brought to you by the Financial Modeling Institute. FMI offers the most respected accreditations in financial modeling. I'm thrilled to welcome on the show this week. Cameron and Nicholas Hay, welcome to the show.

Thanks.

Great. Thanks very much for having us.

Very excited to have you. And just so our audience knows, I'm gonna share up front. The theme for this week's show is dynamic arrays. We're going to talk about why they're building models that are fully dynamic using dynamic arrays. So I'm. I'm thrilled for this discussion because I think it's something a lot of people are wrestling with of how much to use them, how to think about them. Are they ready to do full models? So looking forward to that. But before we jump into that and before we get to know you a little more, gotta start with the question I ask everybody. We'll give you both the opportunity. I know you know what's coming. I see the smile. We'll go with Nick first. Nick, tell me about that worst financial model you've ever seen. I know you have a horror story.

So yeah, I Did grapple with this for a couple of different ones. I had one which was colored, basically all the colors of a rainbow. So very difficult to tell what was going on. But the one I settled on was actually probably the worst mistake I've seen in a financial model because it was to do with the staff costs. They were all split up into different departments, but then there was a total at the bottom which was totaling up all the subtotals as well as the individual staff salaries. And then especially when you apply a growth rate and an inflation multiplier to all that, then the staff costs get out of hand pretty quickly. So the good thing was the model was actually very well structured, so it was very quick to identify that problem. But that was definitely the worst mistake I'd seen.

I want to be one of those employees. If they're paying me based on the model, I'm good.

Yeah, it just is quite nice. It was a funding model, so the ask went from like $8 million to two. Yeah. Just with just a one there.

Yeah. I'm sure they were happy when that was the case. Yeah, that's pretty bad. At least add structure. So that. That's good. Cameron, what's your story?

So mine's going to be back when I was working in mergers and acquisitions, and it was, yeah, a business that was struggling a little bit. And then they came up and they said, okay, right. You know, the burn rate's too high. So we've created this new model which is our path to profitability and how we're going to get there in the next nine months. And it was just basically cutting out almost all the costs and everything in sales and marketing and doubling the growth rate with all hard coded numbers. And that was the new. The way that they didn't need to raise. And I thought that was quite, quite, quite an interesting way to go about it.

I'll give it to him for ingenuity, I guess. Yeah. You mean you can make. It's like they say, you know, there's lies, lies and more lies and then there's statistics. Right. You can make numbers do whatever you want them to do. It doesn't mean it's going to happen. And we've all seen it in a model where you're just like, okay, you need some realistic assumptions. This isn't possible. What about a key takeaway? Any kind of key takeaways from those horror. Horror stories you just shared or those models. Cameron, what about you? Any kind of key thought or takeaway?

Well, I mean, really just making sure to show all your workings and to have all the KPIs underneath the financial model. So when you're looking at, know your revenue growth versus your sales and marketing costs, you know, are things starting to.

Go out of whack?

And the more things you can bring together, the easier it is to see when things are, you know, looking a bit strange.

I think that's a great point of being able to have things in such a way that you can quickly tell if something's getting out of whack. If you use common sense and you apply that and you understand basic ratios of businesses, a lot of times you can just catch it by looking at a trend. I had a boss that had worked in investment banking for years and a lot of M and A. And all the time he'd asked me to graph something. All kinds of different lines because he just quickly wanted to see, okay, does it look realistic? Is there a hockey stick? Is there a big dip? And if so, why? And so I can remember all the time, like, can you graph this for me? Can you graph that? Was always asking me to graph a line just so he could do a quick sanity check and say, should I be drilling down here or do I feel comfortable that this is realistic?

Yeah, no, we're really big on that. And making sure that if you can visualize everything in a really nice and clear way, it's. Your eye is just so naturally. Well, I mean, your brain really is just so naturally, you know, adapted to reading, well, charts and lines and looking at trends rather than actually looking at raw numbers.

So.

Makes a huge difference.

Yeah, it's a much easier to see a spike or a dip in a chart, a graph, than it is in the numbers itself. Especially when you get a wall of numbers. Here's this big, huge P and L, and it's like, hey, there's 500 numbers here. Which one's important?

No, that's exactly right.

What about you, Nick? What's your kind of maybe key takeaway from those horror stories?

Key takeaway is probably just the importance of structure, because like I said, everything was super well laid out. And so it was very quick to identify where the mistake was occurring. So, yeah, just the importance of breaking things up and having structure.

Yeah. As I like to say, design, design, design some more.

Yep.

So important. I just, I didn't realize that early in my career and I look at some of the things I built and I'm like, I wish I would have taken a good class that would taught me proper design principles and color coding and all those Things that make it so you don't absolutely hate that person when you get their model because we've all been there. You inherit something and you're just like what were they thinking?

Yep. It's always the random cell in the middle of a calculation block colored yellow. We've all seen those.

Oh yeah, I've been guilty of a few. Why don't we ask both of you to tell us a little bit about yourself and your background. We'd love to get to know you a little bit more before we jump into the dynamic arrays discussion, which I'm really excited about. So Nick, why don't you tell us a little more about yourself?

Sure, yeah. I mean I probably had a slightly unusual route into the world of financial modeling where I actually studied modern languages degree at university but then after that did an MBA and moved into roles within banking and asset management. So there I did my CFA and I was always working in kind of data analytics and automation roles within that space. And then it was really kind of joining forces with with Cameron and starting the company together that I did the like did the Full Stack Modeler course. So I already consider myself a reasonably proficient Excel user. How wrong I was. But anyway then it did did Full Stack Modeler and yeah great Lear how to build three statement financial models. Just having a structure and then yeah that's really takes me up to now so good.

I'm going to, I'm going to record that part, send it to Giles with a bill letting him know that there was advertising forum. Nah, Giles is great but Cameron, go ahead. Great.

Yeah so I trained at PwC and so I did my chartered accountancy degree and then moved into the world of corporate finance and mergers and acquisitions and was really there in doing all my training that I really got into financial modeling and really seeing how it can be used as a very effective tool to then do consulting services on top of Whereas actually I think too often it's seen that people are, you know, you use a financial model and it's, it's just a process that doesn't not use for anything else. But that's where I fell in love with that. And then yeah just sort of worked with tech companies and saw that this was an area where a lot of companies saw a lot of value and then led us to start cinematic consulting.

Great. I appreciate a little bit on your background. And another question I have for you Cameron, you started in accounting. How did you become passionate about financial modeling? When did you realize that that's what you wanted to do? Is to help people build models. And I know building the models for more than just the sake of building the model, but is that something you wanted to be kind of a core part of what you were doing?

Yeah, it was really just understanding. Well, coming back to the point of, with business, if you can understand how cash flows around a business, that's how you really actually get in and understand a business. And in my opinion, just, you know, with a lot of people that I spoke to. So it was really understanding originally when I first moved into mergers and acquisitions and then thinking, oh, well, if I want to go into private equity, for example, this is a key skill set that private equity firms look at now. I didn't end up going down the private equity route.

Sure.

So, yeah. So hopefully that. That answers that.

Yeah, no, that, that does. And I totally agree with you. Right. Understanding the numbers and the cash flow of the business is critical. Like so often I tell people, look, if you want to know your business better, take a dollar and run it through the entire process and understand what happens at each step to that dollar and you'll understand the business much better. You'd be able to model it better as well. And so I'm totally with you. It really is a great way to learn about a business. Nick, what about you? What led you to start a company with your brother?

Yeah, I mean, honestly, we've been looking for something to do together for a long time. I mean, we've been best friends for over 30 years and, yeah, just trying to find that right thing. But it was during, actually Covid, when we were working from home together. So at the time, Cameron was working in mergers and acquisitions and I was working in automation solutions for banks and asset managers. And it was really then when we saw the mergers and acquisition process, I was struggling to understand it, how it takes two to three months for preparing all the data for a transaction process. And I was just there obviously working in the area I was working, thinking, surely this is just something which should just be ready on an ongoing basis. You just load in new data, hit refresh, and everything's ready to go in the format you need was really that the. That we kind of thought there's probably something we can combine and do here and give companies that information on an ongoing basis. But, yeah, it's been fantastic working together. We've got very good, as I say, working relationship, good balance, you know, go to the gym at lunchtimes during the week, play golf on Friday mornings. It's. It's great.

Yeah, sounds rough. Can I swap You I want to play golf on Friday morning. That's great that you guys can do that together. And it works so well. And always good to have a partner that you know you can trust.

Yeah, exactly. I mean we get so often like told from lawyers being like, yeah, you need to formalize this, put this agreement in place and you go, look, you know, if something goes wrong between us, you know the other one's going to have to answer to mom. And that's a much bigger penalty than having to deal with the UK legal system.

So definitely. Oh so true. I could totally relate to that one right there. There is nothing like the scorn of a, a mom.

Yeah, yeah.

The wrath of mom. We've all been there. So next question I have and we'll ask this one of you, Cameron, how did you guys decide to focus on, you know, SaaS companies, a lot of private equity backed. Feels like that's kind of your niche spot. How did that come about that that's where you wanted to focus?

Yeah. So it really came down to just that's what my experience was in while working in corporate finance and, and also the contacts that I had as well. So it's like with the private equity funds we work with, a lot of them I'd come across in my previous role. So it was quite an easy transition to then say, well you know, you, we know each other, we've worked together. So they've already had the proof of concept of your work while you were a sell side advisor. So when you're going and saying, well I'm going to do the same thing that I was doing in my old job but just do under a different umbrella, it's not too much of a shift rather than going to a completely new industry or. Yeah, completely new area and then having to build all those connections and build that trust from the bottom up totally makes sense.

I figured it was probably something like that is that's kind of where your experience is. That's usually how it works because it's easiest to get customers, people are more likely to trust you versus oh, I'm going to go into this industry. Have you modeled anything in that industry? No, but it's all the same. Do you know anything about it? Yeah, I watched Bloomberg News. It just doesn't. Doesn't quite work. No, no, it makes a lot of sense.

Yeah. And with our projects, I mean I would probably say 30% of the time is spent actually doing financial modeling or less than that. And so much more of it is around what's the information Going in. Does that make sense? And actually doing the consulting part around, like how is the information getting from the day to day tasks of the bookkeeper to the private equity fund that's monitoring this company? And then how can we make that more efficient and more accurate?

Yeah, if you had told me most of your time was spent on modeling, I'd be a little worried. You know, 30, 50, fine, if somebody tells me 70, 80%.

Yeah.

I'm not sure you're doing the customer a service. No. Right. So there's. The model is obviously important, but there's so much that goes into that. Understanding the business, the flow of everything, the data, the consulting, the providing good advice. The model will help with all that and it's critical to the whole process. But there's so much more that goes into being a good modeler than Excel and the three statements and just being able to build it. Yeah, I see you both nodding your head. So I know you know that I want to, I want to jump in and spend a little bit time focusing on dynamic arrays. And for anyone who's not familiar with dynamic arrays, the whole idea is the array can spill and adjust, so your range can grow and contract based on inputs to come into a formula that will cause it to resize, versus the old way of working in Excel where everything was static unless you use Control Shift enter, which really wasn't dynamic, but was the way to deal with an array. So the first question I have, and we're going to throw this one at you first Nick and then Cameron. I'll give you an opportunity to add any thoughts to it. How did you guys decide to build all your models? Dynamic arrays? Like, how did you come to that conclusion? Walk me through a little bit of the process and how that all happened. I'd love to know the backstory.

Yeah, absolutely. I mean, I guess for the last 12 months we'd gradually been using dynamic arrays a bit more and more, just kind of building them in where we could. But then, and just over time we noticed how it was making our models a bit faster, a bit more robust, I guess, because you have everything very structured and then they were just a bit faster to recalculate as well. So we just thought there might be something here to actually investigate if we can use this even more than we're currently doing. And it was really that point around kind of reduction in error points that really got us convinced. Because if you think about a normal Excel file for an anchor model, which could have, let's say, 200,000 formulas in it then that's obviously 200,000 potential error points. Like a blank Excel workbook doesn't have any errors in it until you add data and formulas and things. So theoretically minimizing the number of formulas you're using should reduce the error potential of the file. So that's what really kind of convinced us because really dynamic arrays, like inconsistent formulas within a calculation block are impossible. There's no need to copy formulas down and across anymore. So yeah, I think it just makes it easier for everyone because it's easier to check because there's fewer formulas to look at.

So yeah, yeah, the thing I would add to that is actually because we don't maintain any of the tools that we build for clients, we very much do the building and then hand over to them and they then run it from there then onwards. And it just makes it a lot easier when if they come back to you and say, oh, can you make these adjustments in six, nine months time? There isn't that, you know, hard coded yellow cell somewhere in the cash flow which actually is throwing off the balance sheet and then, you know, something comes back with, with an error with, you know, without you having to go through and check it. Whereas, you know, if it's array based, unless they've gone into Excel labs and started amending the lambdas, you're probably pretty safe that everything's as it was.

Yeah. So I'm going to say, do you worry about them not understanding the model since you're used? We'll get to lambdas in a minute. But because you're using lambdas and dynamic arrays, which say most people aren't quite comfortable with unless you're an Excel nerd for the most part. Right. Like dynamic arrays are not used by the majority of people, not even majority of financial modelers at this point. This point. And so I'm just curious, is there, do you have concern around people not understanding the model, any of those kind of issues or how did you get comfortable from that end? Because I totally agree with you on the. You reduce the error points. If you can build it fully dynamic, there can definitely be some benefits. So I'm just kind of curious a little bit that. And I'll throw that out to either one of you.

So. Yeah, so. Well, I mean this is really, I mean we spent a lot of time with Craig Hat Maker chatting this through. But you know, if somebody, if you engage somebody to build something, a functionality on your website and you say if somebody fills in their email address, I want them to email this. If you wanted to test that you would put your email in and see if it emailed you. You wouldn't go in and review the WordPress code. So when you're reviewing a financial model you would tweak the assumptions and see what did to the outputs. And that's going to be the fastest way to review a financial model. I mean we're also very lucky that all of our clients are going to be in well within the top 1% of Excel users. So while, you know, they're not necessarily using everything on a day to day basis, it's not really a big step up from where they are to, you know, to follow it. And certainly we've not had so not had anything from clients yet being, you know, I mean, yes, they've acknowledged it looks different but it's not. Oh, I can't follow it because it's. You're still laying everything out in the same way.

Yeah, you're still building three statements. It's not like you're hey, I got this fourth financial statement and dynamic arrays come check it out. Right. It's just, it's like when new formulas come out in Excel. So before we get into kind of how you handled corkscrews lambda, some of that and you've mentioned Craig Hatmaker, I want to just ask a question here because I'm curious and we'll start with you, Nick. Do you have a favorite dynamic array that you like to use? Is there one that's kind of you your go to?

I guess we're probably excluding lambdas from this. Yes, I'm going to exclude lambdas. Kind of a bit of a rogue one I think but I'm going to actually go with the expand function and actually because when you're working with dynamic arrays it's quite important to have matching timelines and things of a matching width. So it's just so useful to be able to throw in the expand function. So if you've got a range which is only 30 columns for example, it then expands it out to 90 if you need the reference to be 90. So I'm going to go with that one.

Interesting. Yeah, that's definitely not one. I don't think I've ever had someone tell me that's their favorite yet. So there you go. Not that I usually ask this this specific of just dynamic race but Cameron, what about you?

I'm gonna go with sequence because like on the face of it, if you use it by itself it feels like it's completely useless. But when you actually start, when you actually start Incorporating it into all the others, you realize it's probably the most powerful one there is.

Yeah, I use it for dates all the time. I know it can be used in a lot of other areas, but that's really where I use it, is around dates. It's so handy there.

Yeah, yeah, sure.

I agree with you. When you first see it, you're like, well, where would I use this? Yeah, you know, and in the old formulas, when you needed to increment a range when they weren't dynamic and you wanted to be, I'm like, okay, I can see that. But I'm like, okay, with dynamic rays, where am I going to use that? That incrementing? So it took me a while to. As soon as I saw the date, I was like, all right, now I got it. This is power. Yeah. So let's talk corkscrews, because I know dynamic arrays on their own do not handle corkscrews. At least I think some people have used my molt and tried in some different ways, but you really need lambdas and there's some real complexity that comes with corkscrew. So how did you guys kind of handle that? How did you overcome that challenge with dynamic arrays?

Yeah, no, so you're right. This is definitely a bit of a challenge. That was a learning curve at the beginning because there are certain things when you've been using dynamic arrays compared to, you know, traditional Excel, where one cell is one value that do get more complicated. So offsets another one corkscrew for sure. Just because the dynamic array Excel treats it as one thing. So to reference the closing balance in your opening balance and things, it doesn't really work. You need lambdas for that, otherwise it's going to create circularity and just not work. So that's really where lambdas came into it. And yeah, we actually met Craig Hatmaker through the Full Stack modeler community. So another shout out to Giles Mail there. But yeah, we came across the work that he was doing with lambdas, and that was really, I guess, the turning point for us where we realized actually we can use this more widely because these lambdas solve this problem that we've been coming up against.

So, real quick, can you define what lambdas are just in case we have someone listening that's not familiar with them? Can you give us kind of a definition of what they are? Nick?

Yeah, lambdas are just basically functions built in Excel which are custom. So you just, you build your own function and then you can then reference it later on as if it's a native Excel function. So just a very simple example is if you have a dynamic array which is spilling vertically and horizontally across a range, you can have then a lambda, which does some cols, as in some columns, and just totals up each individual column within that range, which if you're not using that as a lambda, you would need to use the kind of bicolor and then a little lambda function after it. So it's really just a way of creating custom Excel functions.

Yeah. But it's also noting that you don't need to create them yourself. You can import them. Yeah. From somebody else. So, you know.

Yeah, so talk a little bit about that, Cameron, because it sounds like you're using a lot of them that have been created by Craig. So what is it that Craig has done? Why are you using his lambdas? Maybe talk a little bit about that.

Well, the main reason we're using his lambdas, because they're fantastic, they work and, you know, we wouldn't trust ourselves to build them. So you're better off making sure that it's somebody who really knows what they're doing in that field, is doing them and has checked them, however many thousands of times rather than doing something, you know, ourselves.

In today's business world, financial modeling skills are more important than ever. With Financial Modeling Institute's Advanced Financial Modeler Accreditation program, you can become ready for your financial modeling skills, join the Financial Modeling Institute's community of top financial modelers, gain access to extensive learning resources and attain the prestigious Advanced Financial Modeler accreditation. Visit www.fminstitute.com podcast and use code podcast to save 15% when you register. So I'm curious, you think with, you know, 5G and lambdas, do you see dynamic arrays becoming the future for modeling? And how long do you think something like that will take? And we'll ask both of you that question. Just want to get your thoughts about that.

Yeah, I mean, I think it's probably going. Realistically going to take a while because something like Power Query, for example, which has been out for a long time, is still not that widely adopted. So realistically, I think it's going to be a while. And you know, at this point I want to emphasize there's nothing wrong with modeling in Excel with without dynamic arrays, it works perfectly fine for us. We've just found that it's a bit of a faster way to build and a bit more robust. So, yeah, I think it will take time.

And so I'm curious on that front, how much quicker do you think it is? Like, if I asked you to build the same model. One you're doing dynamic, one you're not doing dynamic. Do you have a feeling, Is it saving you 10%, 15%? Is it just. The spreadsheet generally recalculates faster and feels cleaner. Like maybe list some of those. Anything you can quantify around the benefits. We'll start with you, Nick, and then we'll go to Cameron. I'd love your thoughts.

Well, I'll start with a little record we had where when converting all our like some of our old models to two dynamic arrays is we managed to cut down a calculation block with 27,000 formulas in it and make it one formula. That was the best we managed to do.

That's a pretty good ratio, which is fantastic. Any day of the week.

Yeah, so that was great. I mean, I think it has probably reduced the, like the number of formulas in our spreadsheets by over 99.9% for sure. But yeah, I mean, I think the.

Thing in the file size we did. Well, the last project we did without dynamic arrays and the first project we did with dynamic arrays, the two files were the same size and one was a $2 million a year business and then the one afterwards with a single entity and then the other one was probably closer to. Was well into the tens of millions with multiple entities, thousands and thousands of transactions going through. And the models were exactly the same size and it just went. One is however many, 30, 40 times the size of the other, but they're still the same file size and we're still calculating at the same speeds.

It's pretty amazing. So what advice would you offer to. Let's say somebody's listening to the show and they're like, I'd like to start doing dynamic arrays. I want to fully model in dynamic arrays. We'll start with you, Cameron. What advice would you offer them if they're thinking about going down that route?

I would say is just make sure it's something that you're really wanting to commit to. Because I think the real benefit of using dynamic arrays and lambdas is when the full model is dynamic arrays and lambdas, if you start mixing and matching, I think you lose a lot of the power and, and the fact of all the, like the ranges and timelines matching up. Because that's one thing that I would say if you, if you're going to dip your toe into it, I think it's, you're probably not something. I think you lose a lot of the power and, and the fact of all the, like the ranges and timelines matching up. Because that's one thing that I would say if you, if you're going to dip your toe into I think it's yeah probably not something to, to do is you got to something you really got to make sure you do absolutely full heartedly with 100% commitment.

So it's basically fully commit or, or don't do it. Don't go half and half which. Yeah, I, I hear you. I've built some models, played with them a little bit and a lot of them in half and half. As you just kind of learn you're like okay, this is painful if it's half and a half.

Yeah.

Nick, your take?

Yeah, I'd probably say say similar actually with, with dynamic arrays you just kind of dive in and gradually just try and convert even just converting an old model that you built before and just gradually trying to convert it into dynamic arrays. Like it will take a bit of time to get your head around it and you know, learning the power of vstack and Hstack and filter and as Cameron said matching up the timelines but it will get there and I do think once someone invests the time in it and really gets gets to grips with it, I don't see you ever going back to the other way and.

Talk a little bit more about what 5G is from Craig Hatmaker, you know, how many lambdas maybe you're using in your model, how someone could learn more about that. So why don't we start with you Nick on that question.

Yeah, so in terms of Craig Hatmaker. So yeah, he's kind of like our mentor in this area I guess. But he's his website beyond Excel he's got all his lambdas uploaded. There's in terms of our models we probably import about 40, I would say of his lambda functions, you know there are definitely sort of 10 or 15 that we would use more regularly. But yeah, 5G is basically just using lambdas to you know, enhance the power of Excel models and leverage dynamic arrays and really the power of that, which you'll see if you look at some of his videos and things is you start thinking about Excel in terms of blocks rather than cells which might sound a bit weird but you really just think about it and like oh, this is a block which contains I don't know, 10,000 cells for example. But it's one thing, it's not 10,000 cells.

Give an example of maybe what one of his lambdas solve, like what one of them does. Can you give me an example?

Yeah, I mean, well Corkscrew is probably the most relevant example because it's something.

I figured that's what you would go with.

Yeah, well, I mean, there's many that I could go with because there are lots of fantastic ones, but yeah, course, probably the best one to talk about because it just allows you to have that process where you go from opening balance and then adding flows, getting to a closing balance and then going back to opening balance, which you just can't do with Excel's native dynamic arrays.

What would you add to that, Cameron? What would. Any thoughts there?

No, nothing else to add.

You have a favorite lambda from Craig.

I think the answer was basically like sumif, but goes, you know, dynamically, internally.

Okay, got it.

So dynamic.

Cool. Fun. So I think that covers most we want to cover on dynamic arrays. There's a couple other questions I want to ask you before we get to the favorite section that everybody loves. Rapid fire. I know you guys have your answers ready, so first I want to ask you. And we'll start with you, Cameron, on this one. What is your favorite Excel shortcut? What Excel shortcut has saved you the most time and why?

I would say alt O R E for adjusting column heights. So it's just when you want to create like a small little gap in between each of these like sections of KPIs, for example, is one that you just think, oh, well, I always need to like go back and like manually drag down. But it's just you can just do that and set them and especially set them all to the same height as well, rather than if you used to just do it and drag it manually. Then I think that's my favorite.

Alrighty, nick, what's yours?

F11 for making a graph out of a selected data set. So I guess it's an oldie but a goodie.

Yeah, that's a good one. You can. I always think of alt F11 going into VBA when I hear F11. I just always think VBA, even though I know there's other uses. All right, so this next question I want to ask you as you guys look back on your career and we'll start with you here, Nick. What is the number one lesson you've learned during your career that's helped you the most?

I would probably say that there is no substitute for testing. And I guess talking to Craig actually reinforced this even more. But I really appreciated that when I was working on a project at HSBC and the business analyst there would always just, you know, be testing things very rigorously, of course. But he Would always say, like, you know what the most inefficient way to test computer code is? And said no. And he said, reading it, do you know what the most efficient way is? And said no, testing it. So again, that very much comes back to Craig Hatmaker's point of why would you try and debug an Excel formula by looking at it? Just plug in values and see if it gives you the result that it should give you.

Yeah, it's kind of like if you turn all your assumptions to zero and you're getting some numbers out of your model, you might have an issue.

Yeah, exactly.

I totally get it, Cameron.

Mine's going to be a lot more basic than that, but actually how valuable it is when you're working on something later in the evening, just not sending it in the evening, just going to bed and just waking up and just checking it first thing when you wake up in the morning. The number of things that you can spot, which are really obvious when you've just, you know, come in fresh. Mine's going to be a lot more basic than that. But actually how valuable it is when you're working on something, you're just looking at everything first thing in the morning versus trying to look at something just before you send it out. Yeah, it makes an absolute world of a difference.

Spoken like a modeler that's done many late evenings.

Yeah, I think later in the evening, just not sending it in the evening, just going to bed and just waking up and just checking it first thing when you wake up in the morning. The number of things that you can spot, which are really obvious when you've just, you know, come in fresh.

Yeah, no, there's something to be said for that. It's kind of like when you said that I also thought of.

And you're just looking at everything first thing in the morning versus trying to look at something just before you send it out. Yeah.

Whenever you're emotional and you're writing an email or anything, sleep on it, then read it in the morning and see if it comes across different. Most of the time it does. It's like, my price should change that word and this word or I really should never send this thing is we've.

Got chat GBT now. So you can just copy and paste in and say, make this nicer.

That. That's a good point too. You definitely can do that. Of. Okay, take the emotion out of this because I'm pissed right now. I wonder if I said it that way, what it would say.

But there you go. You can Test it later.

Yeah, I, I might try that later when I. When I am that way. Right. One more question before we jump into rapid fire. Just looking at your guys's website and I know you have a number of resources out there, but one thing you had, I think it was the video around is you guys mention a four step process that you like to use with your companies to help your companies. I think it was examination, transformation, presentation and optimization. Let me give you an opportunity to just kind of talk about why that is a key process. You use how you came up with those four steps. And Nick, we'll give you the floor first on that one.

Okay. Yeah, I mean, I guess we'll take it in turns. But the examination is really just making sure we always start from source system data and just evaluating the quality of that. Because a lot of the time when we start these projects, people send us across their working file in terms of their budget or their rolling forecast, things like that. Well, actually let's start from what are your source systems saying? So let's get your accounting system like trial balance extracts, let's get the data from your CRM system, let's get the data from your hr, get your payroll reports and let's just see what that's telling us first and then we go into the transformation stage.

Yeah, well, I mean on the examination it's just making sure that all the information that you want to be kind of seeing at that, what we were talking about earlier about at the, you know, the private equity firm or at your board meetings, that's actually being captured at the granular level, whoever is maintaining the data on a day to day basis. So making sure if there are adjustments needing made that we can do that in the examination and then yeah, transformation is really taking all that source information and then starting to pull it together and you know, this part the next day, just. Yeah, pulling it together and making sure all the different systems are talking to each other. So does the CRM actually match up with the invoice listing? So if you're all these deals closed, do you actually. Are these clients paying you that are closed deals in Salesforce? And yeah, then just building it all into a picture which we can then present back to the client and that comes into that presentation layer?

Yeah, no, it's a presentation really. Actually I'd never really appreciated the power of data visualization. I'm going to give my third shout out of the recording to, to full stack modeler again because that was one of the modules that they have where really just Seeing the power of data visualization and how to bring the most out of the data. And coming back to what you were talking about earlier with visualizing models and having graphs which show that we actually have a section of graphs called forecasting Sense Check. So it literally just tells you if the forecast is making sense compared to the previous ones.

And yeah, yeah. And every industry has a whole load of different ones that make it. I mean, ours is obviously deferred income is huge in the software. In the software space, it's actually going. How have you modeled deferred income in the same way that it's behaved historically. And that's always a quick way because that can drastically change the cash position if you're not careful. And then the optimization part is really just that part of making sure that what we're doing and how we're restructuring our clients processes fits in, making sure they're really comfortable with it. And it's saving as much time as possible because to transfer someone's processes, you got to make sure that they've got the benefit of it as well. There's no point in going in and saying you got all these extra insights, but this is going to take you way longer to do and you didn't have time before. So making sure it fits in with everything and, and really just handing it over to the client and making sure that we've captured everything that they want to in their solutions. And this really does save them all the time that they were looking for it to do.

Awesome. Great. Appreciate you sharing. That makes sense to me. We're going to move into rapid fire, but I just want to let everybody know, Nick and Cameron have been kind enough. They're going to share a model that we'll make available to just kind of show how they build a dynamic race. You guys can kind of look at a file, so that will be with the show notes that people will be able to find. So I just wanted to mention that now. And now we're going to play a little game to pick who gets to go first. All right. So, Cameron, Nick, you both ready?

Yeah.

Have you done paper, rock, scissors before?

Yeah.

All right, it's paper, rock, scissors. Winner gets to go first.

Okay. One, two, three, go. One, two, three, go.

Oh, no. Okay.

They are brothers.

There you go. Yeah.

All right. I think you won there, Nick. So you get to go first here. Here's the rules. You get more. You get no more than 10 to 15 seconds to answer. You can't use. It depends. You can elaborate on one or two. So you just need to take a side. We'll run through them all. And Cameron, where I'll give you the opportunity is I either can run through all of them or you can just say, hey, I want to tell where I'm different. It's up to you. But we'll start with Nick here. Circular or no circular references in your model?

Nope. Use on those.

VBA or no vba?

No. Power query should be able to do most of it.

Do you prefer a horizontal model or a vertical model? As far as layout? Not. Not time dimension, but more vertical in that all your schedules are on one page horizontally. Kind of like to have them each on a different page.

No, vertical, I think horizontal can get. You can get a lot of tabs very quickly now.

I don't know how you'll answer this one, but I'll ask anyway. Dynamic arrays, yes or no?

Yes. For absolutely everything.

External workbook links, yes or no? No. Use power query named ranges versus no named ranges in your model.

Named ranges.

Okay. Do you follow any kind of formal standard from a board for your modeling, like FAST or some of the others that are out there?

Not a formal standard. Both of us have, as they've been trained, full stack and, you know, so we have elements of that in ours, but we have a very strict internal structure. Yeah, sure.

Got it. Will Excel ever die?

No.

Will AI build the models for us in the future?

Yes.

Should you use sheet cell protection in your models, yes or no?

We don't.

Okay, fair enough. Do you believe financial models are the number one corporate decision making tool? Yes or no?

Yes. Especially if you bring together all the different data sources.

Got it. What is your lookup function of choice? Choose Vlookup, index match, xlookup or something else.

So I very much used to be xlookup all the way, but in the new world of dynamic arrays, you really need to be index, x matching.

All right, so you're an index and X match guy. All right, Cameron, do you want me to run through all of them or do you want to just go through the ones where you're different? I'll leave it to you.

I think probably just a couple where I'm. I'm a bit different on. Well, I mean, the thing is, I say I'm a bit different on, but we all work on the same file, so whatever Nick says goes. So I can't really. I can't really be different.

Come on, you got to give me at least one where you would be different. It's so fun if you're all the Exact same.

The name drains I could tell you the ones we had the biggest debate about and the name Rangers was the one and table referencing was one that I thought is going to be the biggest is the hardest one for if someone's trying to follow the formula through. And again if you're people aren't used to that type of working it then becomes a lot more complicated rather than just seeing like a sheet name and a cell reference where it's very easy for someone to go back and trace. So that is the one bit we did go back and forth on for a lot. As I say, Nick, you know, had executive, did an executive order and just, you know, overruled me. So couldn't do anything.

Is that because Nick's older?

No, I'm actually older Nick.

I actually. I figured you were. I just had. Yeah, yeah.

Well no, Nick. Nick's just the one in charge, you know.

So that's it.

And then is there any of those answers and I'll ask either of you any of them that you want to elaborate on like why you chose the answer you did and just share a little bit more outside of dynamic race I think we've covered that one. Any of the others?

Well, I'm gonna focus on the named Rangers one and come back at Cameron on his so because it's something we were very big on in like automation roles that I had in the past, it's just having these named ranges and defined variables that you could easily reference later on. So on the name ranges we have compromised slightly in terms of we always make sure the named range starts with the tab name just so it is that little bit easier for someone else to follow because I do understand that obviously we spend all day in these models so we know where everything is. But yeah, named ranges especially with dynamic arrays and thinking like I said before thinking about Excel in blocks instead of individual cells. So a named range for us would probably typically contain I don't know, like 2000 values for example. So it's very powerful to be able to reference that as just a named range when you're typing in formulas. Massively speeds up build process.

Makes sense. All right, well I appreciate you guys sharing and is index xmatch your lookup of choice too, Cameron?

Well, it has to be has to be with lambdas because the xlookup doesn't seem to work quite as easily when you're looking at two different when you're looking at the well arrays really going down and across other than just individually.

Interesting. Yeah, because I'm definitely I prefer xlookup in most situations, but I'm not using Lambdas, so.

Yeah, no, yeah, xlookup. Whenever it's just like one row, I'm going for it. Yeah, I'd always use xlookup.

Got it. All right, well, thank you for that. Enjoyed that good paper, rock, scissors battle there. Only took three rounds to get a winner. All right, so we just have two more questions here and we're going to wrap up. So the first one, and we'll ask both of you, we'll start with you, Cameron. What's the one piece of advice you'd give to our audience that can help them become a better financial modeler?

It might be a, might be a basic one, but it's really just making sure you keep all hard coded values in the same place and just making sure that there's nothing that, I mean, even, we even have it for outputs, if you've got things that are, you know, for example, like year to date, year to go targets all being within the assumptions tab, so that if you're handing over your model to someone else, they know that everything that can influence the model is in one place and it's just a case of going when you hand over. Okay, for you to be comfortable with this, just go on this tab and run down every single cell that we've, you know, you've got an input cell and make sure you're comfortable with the number that we've got in there and then that's a good place to then go and sense check the outputs once you only have one sheet to run through. Rather than handing someone a 15 tab model and saying, oh, what do you think?

Yeah, there's some real value of making sure your assumptions are very clear and whenever possible, all in one place. I'm with you. I'm a big fan of trying to keep them on one sheet as much as as possible there. I, I, I like that approach for sure. I'm with you, Nick. What's your piece of advice?

Probably similar to what we were discussing earlier about design and really just saying that structure is everything because I appreciate what we've been talking about is a bit of Excel complexity that not everyone's super familiar with, but if you just make sure you lay everything out in a super clear way where, you know, according to the P and L structure, for example, just make sure your assumptions, your inputs are all structured according to the P and L structure exactly the same order, you go, your forecast calculations are laid out in exactly the same order, you know, you add comments to Things just to make sure to explain the different steps that are happening in your model. So as long as you structure everything really clearly, people can follow it, even if you're using a couple of formulas they might not have seen before.

No, I'm with you. I'm a big fan of In Schnorab, the Financial Modeling Institute. And I had him on a podcast and he said the number one problem far and away, and there's not a close second, is design. Like, it almost always comes back to design. He's like, someone will call me and go, my model's not working. And I got this.

And I got this data.

He's like, stop. I don't. That. That's not your problem. Your problem is you designed it wrong.

Yeah.

You designed it right. You wouldn't have that problem. And it's amazing how often it comes back to design. So I. I'm with you. I'm a huge believer that design makes a big difference. Otherwise you end up with what I like to call Franken models.

Yeah.

And we've all built a few, right, Those Frankenstein models where you're just like, what was I thinking? Especially the first time you build a model. Yeah. You go back and look at those first few models you built, you're like, oh, that was. That was garbage. At least I. At least that's what I do. So, last question here. If our audience wants to learn more about you guys, you know, get in touch with you, what's the best way for them to do that? And we'll start with you, Nick.

Yeah, I mean, both pretty active on. On LinkedIn and then our website, Finmatic Talk.

Perfect. Anything you'd add to that, Cameron?

No, yeah, just. Yeah. Cameron. Hey. On LinkedIn at Finatic.

Perfect. We'll put both your LinkedIn profiles in the show notes so people can contact you as well as your website. But I just want to close by saying thank you for joining me. I had a great time talking and I'm gonna have to go look at a few more of his Lambdas from Craig. I've. I've talked to Craig a few times and I've attended a presentation here or there, but have not really dabbled in it yet. So it's exciting to see people pushing the envelope because that's the only way we're ever going to get to where people are comfortable using dynamic arrays, is having people push the envelope. So thanks for sharing your experience. I really enjoyed it and I'll let you guys go so you can enjoy the evening. I can say over the last hour, I've watched the light slowly become less and less. So I know it's close to evening for you guys.

Yep, absolutely. And yeah, we'll send over that kind of slightly pared back model just to demonstrate some of the functionality of the Lambdas and dynamic arrays.

Yeah, we look forward to hearing how you get on with it all. As remember, you've either got to dive in or not go at all, so see which one you choose.

We'll see. We'll see who all dives in when you send it out. All right, thanks, guys. Great.

Thank you.

Financial Modelers Corner was brought to you by Financial modeling institute. Visit fmi@www.fminstitute.com com podcast and use code podcast to save 15% when you enroll in one of their accreditations today.