Secrets to Building Error-Free Financial Models

Written By Ankita Ghosh

Show Notes

Welcome to the Financial Modeler's Corner (FMC), where we discuss the Art and Science of Financial Modeling with your host Paul Barnhurst.

Financial Modeler's Corner is sponsored by the Financial Modeling Institute (FMI), the most respected accreditation in Financial Modeling globally.

In today’s episode, Paul engages in a conversation with Cameron and Nicholas Hay to discuss dynamic arrays in financial modeling. Cameron and Nicholas share their experiences and insights on using dynamic arrays to build fully dynamic models, avoiding common pitfalls, and the benefits of this approach.

Cameron and Nicholas are seasoned financial modeling experts and co-founders of Finomatic Consulting. Their practical advice and innovative tips have helped countless professionals enhance their modeling skills.

Key takeaways from this week's episode include:

  • Use dynamic arrays to reduce the number of formulas and potential error points. Clearly separate inputs, calculations, and outputs into distinct sheets. This enhances clarity and ease of use.

  • Ensure uniform column and row structures across sheets. This consistency minimizes errors and simplifies formula management, making models easier to audit and understand.

  • Mixing traditional Excel formulas with dynamic arrays reduces the benefits. A complete commitment ensures better performance and error reduction.

  • Using graphs and charts helps quickly identify trends and anomalies, making it easier to ensure realistic assumptions and accurate models.

  • Establishes a solid foundation, ensuring you learn best practices early on, which prevents the development of bad habits and enhances overall modeling skills.

  • Implementing dynamic arrays can drastically reduce the file size and complexity of models, making them more efficient and manageable.

  • Issues like mixed calculations and unrealistic assumptions emphasize the importance of thorough checks and realistic inputs in financial modeling.

    Download a sample fully dynamic 3-statement model Excel Files — The FP&A Guy (thefpandaguy.com), courtesy of Cameron and Nicholas Hay.

Quotes:  

Here are a few relevant quotes from the episode on financial analysis and modeling:

“A blank Excel workbook doesn't have any errors in it until you add data and formulas. Minimizing the number of formulas should reduce the error potential of the file”

“With business, if you can understand how cash flows around a business, that's how you really get in and understand a business.”

“If you're going to dip your toe into dynamic arrays, you've got to make sure you commit fully with 100% commitment.”

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 code “Podcast” to save 15% when you register.  

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

View and download the Financial Modeling Code at financial-modelling-code.ashx (icaew.com)

Follow Cameron and Nicholas:

Follow Paul:

Follow Financial Modeler's Corner  

In today’s episode:  

[00:03] - Introduction

[00:40] - Guest Introduction And Theme for The Week: Dynamic Arrays

[01:50] - The Worst Financial Model The Guest Experienced

[02:00] - Horror Stories: Mixed Calculations And Unrealistic Assumptions

[04:10] - Key Takeaways from Horror Stories

[05:00] - Guests’ Backgrounds And Career Journeys

[10:00] - The Importance of Structure And Design in Financial Modeling

[14:10] - Introduction to Dynamic Arrays And Their Benefits

[21:30] - Challenges with Corkscrews And Lambdas

[31:20] - Rapid-Fire Session And Guests’ Contact Information

Full Show Transcript

Host: Paul Barnhurst:: Welcome to Financial Modelers Corner, where we discuss the art and science of financial modeling with your host, Paul Barnhurst. Financial Modelers Corner is sponsored by Financial Modeling Institute. Welcome to Financial Modelers 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 Modelers 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. Hey, 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 upfront 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 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 Nich first. Nich, tell me about that worst financial model you've ever seen. I know you have a horror story.

 

Guest:Nicholas Hay:: 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 too, 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, you know, 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.

 

Host: Paul Barnhurst:: I want to be one of those employees. If they're paying me based on the model, I'm good. Yeah, it was just it was quite nice. It was a funding model. So the ask went from like $8 million to two, just with just with one error. Yeah. I'm sure they were happy when that was the case. Yeah, that that's pretty bad. At least they had 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 was struggling a little bit. And then they came up and they said, okay, right. We know the burn rate is 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 way that they need to raise. And I thought that was, quite, quite, quite an interesting way to go about it. I guess I'll give it to him for ingenuity, I guess. Now, you mean you can make. It's like they say, you know, there's lies.

 

Host: Paul Barnhurst:: 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've been really just making sure to show all your workings and have all the KPIs underneath the financial model. So when you're looking at, you 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&A, and all the time he'd ask 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.

Host: Paul Barnhurst:: Can you graph that? It was always asking me to graph a line just so we 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 wall charts and lines and looking at trends rather than actually looking at raw numbers. So it 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 PNL and it's like, hey, there's 500 numbers here. Which one's important? No, that's exactly right. What about you, Nich? What's your kind of maybe key takeaway from those horror stories?

 

Guest: Nicholas Hay:: Key takeaways, 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.

 

Host: Paul Barnhurst::  Yeah. As I like to say design, design design some more. Yep. So important. I just I didn't realise 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 colour 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 coloured yellow.

 

Guest: Cameron Hay:: We've all seen those.

 

Host: Paul Barnhurst:: 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, Nich, why don't you tell us a little more about yourself?

 

Guest: Nicholas Hay:: Sure. Yeah. I mean, I've 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 I did, did Full Stack Modeler and , yeah, great learning experience. How to build three statement financial models, just having a structure and then yeah, that's really takes me up to now.

 

Host: Paul Barnhurst:: 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 is Giles advertising forum. Now Giles is great but Cameron go ahead. great. Yeah.

 

Guest: Cameron Hay:: 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 it was really there and 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 or, you know, you use the financial model and 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.

 

Host: Paul Barnhurst:: Great. And 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 you're building the models for more than just the sake of building the model, but that that's something you wanted to be kind of a core part of what you were doing.

 

Guest: Cameron Hay:: 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, and just, you know, 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. So, so yeah. So hopefully that that answers that. Yeah.

 

Host: Paul Barnhurst:: 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. Nich, what about you? What led you to start a company with your brother?

 

Guest: Nicholas Hay:: 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 2 to 3 months for like preparing all the data for a transaction process. And I was just there, obviously working in the area I was working in thinking, surely this is just something which should just be ready on an ongoing basis. You just load in new data, hit refresh, and you know everything's ready to go in the format you need it. So, yeah, it was really that, that we kind of thought there's probably something we can combine and do here and give companies that, that information on an ongoing basis. But yeah, it's been fantastic working together. we've got a very good, as I say, working relationship, good balance, you know, go to the gym at lunch times during the week, play golf on Friday mornings. It's great. Yeah.

 

Host: Paul Barnhurst:: Sounds rough. can I swap you? I want to play golf on Friday mornings. And although I don't know that I want to start a business with my brother.

 

Guest: Nicholas Hay:: Well, no, but I mean, for us, it's great because, you know. No.

 

Host: Paul Barnhurst:: That's great. 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.

 

Guest: Cameron Hay:: Yeah, exactly. I mean we get so often like talk 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.

 

Host: Paul Barnhurst:: So true. I can totally relate to that one right there. There is nothing like the scorn of a mom. 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.

 

Guest: Cameron Hay:: Yeah. So it really came down to just that's what my experience was in, in while working in corporate finance for years 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 might 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 sellside 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 it 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.

 

Host: Paul Barnhurst:: Totally makes sense. I figured it was probably something like that, as 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 and it just doesn't doesn't quite work. No, no, that makes a lot of sense. Yeah.

 

Guest: Cameron Hay:: 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? Yeah. And then how can we make that more efficient and more accurate?

 

Host: Paul Barnhurst:: 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 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. So I want to I want to jump in and spend a little 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 Nich and then Cameron, I'll give you an opportunity to add any thoughts to it. What 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.


Guest: Nicholas Hay:: Yeah, absolutely. I mean, I guess for the last 12 months, we've 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 it's, you know, you have everything very structured. And then there were just a bit faster to recalculate as well. So we just thought there might be something here. It's 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 got us convinced. Because if you think about, you know, normal Excel file for an entry 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, you know, data and formulas and things. So theoretically, minimizing the number of formulas you're using should reduce the error potential of the 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, you know, 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 like, formulas to, to look at. So yeah.

 

Guest: Cameron Hay:: 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 and 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 cells 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.

 

Host: Paul Barnhurst:: Yeah. So to say, do you worry about them not understanding the model since you're using 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 the majority of financial modelers at this point. And so I'm just curious, is there do you have concerns 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. I'm just kind of curious a little bit that I'll throw that out to either one of you.

 

Guest: Cameron Hay:: So yeah, I mean, this is really I mean, we spent a lot of time with Coffee 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 to the outputs. And that's going to be the fastest way to review a financial model. And 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 they're not necessarily using everything on a day to day basis, it's not really a big step up from where they are sure to, you know, to follow it. And certainly we've not had not had anything from clients yet being you know, 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.

 

Host: Paul Barnhurst:: 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. Nich, do you have a favorite dynamic array that you like to use? Is there one that's kind of you, your go to?

 

Guest: Nicholas Hay:: I guess we're probably excluding lambdas from this, which is.

 

Host: Paul Barnhurst:: I'm going to exclude lambdas. Yes.

 

Guest: Nicholas Hay:: 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 like matching timelines and things of, of a matching width. So it's just so useful to be able to throw in the expand function. So if you're 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.

 

Host: Paul Barnhurst:: 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 specific of just dynamic phrase, but Cameron, what about you?

 

Guest: Cameron Hay:: I'm going to 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.

 

Host: Paul Barnhurst:: 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, I'm 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 arrays, 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 powerful. 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 mult and tried in some different ways. But you really need lambdas and there's some real complexity that comes with corkscrews. So how did you guys kind of handle that? How did you overcome that challenge with dynamic arrays?

 

Guest: Nicholas Hay::  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 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 and just not work. So that's really where where lambdas came into it. And yeah, that's , we actually met Craig Hatmaker through the Full Stack Modeler community. So another shout out to Giles male 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 this, these lambdas solve this problem that we've been coming up against.

 

Host: Paul Barnhurst:: So real quick, can you just, 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? Nich. Yeah.

 

Guest: Nicholas Hay:: 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, as, 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 calls as in some columns, and just totals up each individual, column within that range. So which if you're not using that as a lambda, you would need to use the kind of Bicol, and then a little lambda function after it. So it's really just a way of creating custom Excel functions.

 

Guest: Cameron Hay:: Yeah. But it's also noting that you don't need to create them yourself. You can import them from somebody else. So you know. Yeah.

 

Host: Paul Barnhurst:: 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.

 

Guest: Cameron Hay::  Well, the main reason we're using Islam is because, I mean, 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.

 

Host: Paul Barnhurst::  In today's business world, financial modeling skills are more important than ever with financial modeling institutes. Advanced financial Modeler accreditation program, you can become recognized as an expert in the field by validating your financial modeling skills. Join the Financial Modeling Institute's community of top financial modelers, gain access to extensive learning resources, and attain the prestigious Advanced Financial Modeler accreditation. Visit www,fminstitute.com/podcast 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. I just want to get your thoughts about that.

 

Guest: Nicholas Hay::  Yeah. I mean, I think it's probably 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, it's at this point I want to emphasize there's nothing wrong with modeling in Excel without dynamic arrays. It works. 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.

 

Host: Paul Barnhurst::  And so I'm curious on that front, how much quicker do you think it is when like if I asked you to build the same model when 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, Nich, and then we'll go to Cameron. I'd love your thoughts.

 

Guest: Nicholas Hay:: Well, I'll start with a little record we had. Where, when converting all our, like, some of our models to dynamic arrays 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.

 

Host: Paul Barnhurst:: Pretty good ratio, which was.

 

Guest: Nicholas Hay:: Fantastic.

 

Host: Paul Barnhurst:: Return any day of the week. Yeah.

 

Guest: Nicholas Hay:: 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.

 

Guest: Cameron Hay:: The thing in the file size we did, well, the last project we did without dynamic arrays, in the first project we did with dynamic arrays, the two files with the same size and one was a $2 million a year business, and then the one afterwards with, with a single entity. And then the other one was probably closer to was well into the tens of millions with multiple entities, you know, thousands and thousands of transactions going through. And the models were exactly the same size. And it just went, you know, 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.

 

Host: Paul Barnhurst:: It's pretty amazing. So what advice would you offer to, let's say, somebody 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?

 

Guest: Cameron Hay:: I would say it's just making sure it's something that you really want 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 that 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're if you're going to dip your toe into, I think it's, you're probably not something to do is you got something you really got to make sure you do absolutely full heartedly with 100% commitment.

 

Host: Paul Barnhurst:: So it's basically fully committed or don't do it. Don't go half and half. Which yeah, 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 half, yeah. Hmm. Nich your take.

 

Guest: Nicholas Hay:: 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 to grips with it, I don't see you ever going back to the other way.

 

Host: Paul Barnhurst:: 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, Nich, on that question?

 

Guest: Nicholas Hay:: Yeah. So, in terms of Craig Hatmaker. So, yeah, it'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. 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 in like, oh, this is a block which contains, I don't know, 10,000 cells, for example. But is one thing. It's not 10,000 cells.

 

Host: Paul Barnhurst:: So give an example of maybe what one of his lambdas solves, like what one of them does. Can you give me an example?

 

Guest: Nicholas Hay:: Yeah. I mean, well, corkscrew is probably the most relevant example because it's something I figured that's.

 

Host: Paul Barnhurst:: What you would go with? Yeah.

 

Guest: Nicholas Hay:: Well I mean there's many there's many that I could go with because there are lots of fantastic ones. but yeah, of course, was 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 an opening balance, which you just can't do with Excel's native dynamic arrays.

 

Host: Paul Barnhurst::  What would you add to that, Cameron? What would any thoughts there?

 

Guest: Cameron Hay:: No, nothing else to add.

 

Host: Paul Barnhurst:: I think you have a favorite lambda from, Craig.

 

Guest: Cameron Hay: I think that's basically like sum but goes, you know, dynamically in January.

 

Host: Paul Barnhurst:: Okay. Got it. So cool. Fun. So I think that covers most we want to cover on dynamic arrays. There are a couple of 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 saves you the most time and why?

 

Guest: Cameron Hay:: I would say, sorry for adjusting column height. 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'll always need to like go back and like manually drag down, but it's just if you can just do that and set them and especially set them all to the same height as well, rather than if they used to just do it and drag it manually. Then I think that's my favorite.

 

Host: Paul Barnhurst:: Alrighty Nich, what's yours?

 

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

 

Host: Paul Barnhurst:: Yeah, that's a good one. You can. I always think of alt F11 going into VBA when I when I hear F11, I just always think VBA, you know, I know there's other uses. So all right, so this next question I want to ask you is you guys look back on your career. And we'll start with you here, Nich, what is the number one lesson you've learned during your career that's helped you the most?

 

Guest: Nicholas Hay:: 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, do you know what the most inefficient way to test computer code is? And I'd say no. And he said, reading it like, do you know what the most efficient way is? And said, no testing it. So again, that comes that very much comes back to Craig Hatmaker's point of, you know, why would you try and debug an Excel formula by looking at it like just plug in values and see if it gives you the result that it should give you.

 

Host: Paul Barnhurst:: So yeah, it's kind of like if you turn all your assumptions to zero and you're getting some numbers out of your model, might have an issue.

 

Guest: Nicholas Hay:: Yeah, exactly.

 

Host: Paul Barnhurst:: I totally get it. Cameron.

 

Guest: Cameron Hay:: 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 just, you know, come in fresh 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. It makes an absolute world of difference.

 

Host: Paul Barnhurst:: Spoken like a modeler that's done many late evenings.

 

Guest: Cameron Hay:: Yeah, yeah, there's been a few.

 

Host: Paul Barnhurst:: Yeah. No, there's something to be said for that. It's kind of like when you said that. I also thought of 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 differently. Most of the time it does. It's like my price should change that word and this word or it's I really should never send this.

 

Guest: Cameron Hay:: Yeah, well, that's the thing is, we've got ChatGPT now, so you can just copy and paste it and say, make this nicer.

 

Host: Paul Barnhurst:: 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. But what if I said it that way? What it would say.

 

Guest: Cameron Hay:: But there you go. You can test it later.

 

Host: Paul Barnhurst:: Yeah, I might try that later when I, when I, when I am that way. All right. One more question before we jump into rapid fire. So looking at your guys's website, I know you have a number of resources out there, but one thing you had, I think was a video around, as you guys mentioned, 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. I want to 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 Nich will give you the floor first on that one.

 

Guest: Nicholas Hay:: Okay. yeah. I mean, I guess we'll 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 their budget or their rolling forecasts, things like that. And, well, actually, let's start from what your source system is 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, like getting your payroll reports. And let's just see what that's telling us first. And then we go into the transformation stage.


Guest: Cameron Hay:: 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're 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's maintaining the data on a day to day basis. So making sure if there are adjustments need to be made that we can do that in the examination and then, yeah, transformations really taking all that source information and then starting to pull it together. And you know, this is part of the next stages. we are 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've got all these deals closed or 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 goes into that presentation layer, which.

 

Guest: Nicholas Hay:: Yeah, no, it's a presentation really. And actually I'd never really appreciated the power of data visualization. I'm going to give my third shout-out of the, 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, like if the forecast is making sense compared to the previous ones. And yeah.

 

Guest: Cameron Hay:: Yeah. And every industry has a whole load of different ones that make it. I mean, you know, ours is obviously, deferred income is huge in the software and software space. So it's actually going how have you modeled deferred income in the same way that it's behaved historically? And, you know, that's always a quick way because that can drastically change the cash position if you're not careful. and then yeah. And then the optimization part is really just that part of making sure that what we're doing and how we're restructuring our client's processes fits in, you know, making sure they're really comfortable with it. And it's saving as much time as possible because. Yeah. To transfer someone's processes, you have to make sure that they've got the benefit of it as well. There's no point in going in and saying you've 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.

 

Host: Paul Barnhurst:: 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, Nich and Cameron have been kind enough. They're going to share a model that will make available to just kind of show how they build a dynamic array. So 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. Nich, you both ready? Yeah. Have you done paper, rock scissors before? Yeah. All right, it's paper, rock scissors. Winner gets to go first.

 

Guest: Cameron Hay:: Okay. One, two. Three. Go! One, two. Three. Go! Oh, no! One two. Three. Go!

 

Host: Paul Barnhurst:: Okay. They are brothers. There you go.

 

Guest: Nicholas Hay:: Yeah. All right.

 

Host: Paul Barnhurst:: I think you won there, Nich. So you get to go first here. Here's the rules. Similarly you get no more than 10 to 15 seconds to answer. You can't use it. Depends. You can elaborate on 1 or 2. 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 Nich here. Circular are no circular references in your model?

 

Guest: Nicholas Hay:: Nope. Use lambdas.

 

Host: Paul Barnhurst:: VBA or no VBA.

 

Guest: Nicholas Hay:: No Power query should be able to do most of it.

 

Host: Paul Barnhurst:: Do you prefer a horizontal model or a vertical model? As far as layout, 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.

 

Guest: Nicholas Hay:: No vertical. I think horizontal can get. You can get a lot of tabs very quickly.

 

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

 

Guest: Nicholas Hay:: Yes for absolutely everything.

 

Host: Paul Barnhurst:: External workbook links. Yes or no.

 

Guest: Nicholas Hay:: No use.

 

Host: Paul Barnhurst:: Power query named ranges versus no-named ranges in your model.

 

Guest: Nicholas Hay:: Named ranges.

 

Host: Paul Barnhurst:: 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?

 

Guest: Nicholas Hay:: 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.

 

Host: Paul Barnhurst:: Yeah, sure. Got it. will excel ever die?

 

Guest: Nicholas Hay:: Now.

 

Host: Paul Barnhurst::  Will I build the models for us in the future?

 

Guest: Nicholas Hay::  Yes.

 

Host: Paul Barnhurst::  Should you use sheet cell protection in your models? Yes or no?

 

Guest: Nicholas Hay:: We don't.

 

Host: Paul Barnhurst:: Okay. Fair enough. Do you believe financial models are the number one corporate decision-making tool? Yes or no?

 

Guest: Nicholas Hay:: Yes. Especially if you bring together all the different data sources.

 

Host: Paul Barnhurst:: Got it. What is your lookup function of choice? Choose Vlookup index match, Xlookup or something else.

 

Guest: Nicholas Hay::  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 matching.

 

Host: Paul Barnhurst:: So all right so you're an index index 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.

 

Guest: Cameron Hay:: I think you probably just a couple where I'm. I'm a bit different on. Well, I mean, the thing is, I seem a bit different on. But we all work on the same file, so whatever Nich says goes. So I can't really I can't really be different.

 

Host: Paul Barnhurst:: Come on. You gotta give me at least one where you would be different. It's no fun if you're all the exact same. Yeah.

 

Guest: Cameron Hay:: The name range, I can tell you. The ones we had the biggest debate about and the name ranges 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, Nich, you know, had the executive an executive order and just, you know, overruled me. So couldn't do. Is that because Nich's older? No, I'm actually older, Nich.

 

Host: Paul Barnhurst::  Actually, I figured you were I just had yeah.

 

Guest: Cameron Hay::  It was. No, Nich. Nich's just the one in charge, you know? So that's it.

 

Host: Paul Barnhurst::  And then is there any of those answers and 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 arrays? I think we've covered that one. Any of the others?

 

Guest: Nicholas Hay::  Well, I'm gonna focus on the name ranges 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 is 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, name range is 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.

 

Host: Paul Barnhurst::  Makes sense. All right. Well I appreciate you guys. sharing and his index x match your lookup of choice two.

 

Guest: Cameron Hay::  Cameron. Well, well, it has to be has to be with lambdas because the X lookup doesn't seem to work quite as easily when you're looking at two different, when you're looking at their, well, arrays really going down and across all the just individually interesting. Yeah.

 

Host: Paul Barnhurst::  Because I'm definitely I prefer Xlookup in most situations, but I'm not using lambdas so yeah.

 

Guest: Cameron Hay::  No. Yeah. Xlookup whenever it's just like one row I'm going for. Yeah I would always use Xlookup.

 

Host: Paul Barnhurst:: 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?

 

Guest: Cameron Hay:: 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 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?

 

Host: Paul Barnhurst:: Yeah, there are 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 like that approach, for sure. I'm with you, Nich. What's your piece of advice?

 

Guest: Nicholas Hay:: Probably similar to what we were discussing earlier about design. I'm 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 PNL structure, for example, just make sure your assumptions, your inputs are all structured according to the PNL structure, exactly the same order you go to. Your forecast calculations are laid out in exactly the same order. You know, you add comments to things just to make 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 that might not have seen before.

 

Host: Paul Barnhurst::  Now I'm with you. I'm a big fan of in honor of 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. Yeah. 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 know 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'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, Nich.

 

Guest: Nicholas Hay::  Yeah. I mean, we're both pretty active on LinkedIn. And then our website, finomatic.co.uk.

 

Host: Paul Barnhurst::  Perfect. Anything you'd add to that, Cameron?

 

Guest: Cameron Hay::  No. Yeah. Just. Yeah. Cameron Hay on LinkedIn and at finomatic.co.uk.

 

Host: Paul Barnhurst::  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 going to 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.

 

Guest: Nicholas Hay::  Yeah, 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.

 

Guest: Cameron Hay::  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 let's see which one you choose.

 

Host: Paul Barnhurst::  We'll see. We'll see who all dives in when you send it out. All right. Thanks, guys.

 

Guest: Cameron Hay::  Great. Thank you.

 

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

Ankita Ghosh
Previous

Discovering New Dimensions In Accounting
Next

Golden Rules to Prevent Excel Disasters in Financial Modeling