Financial Modeling in Retail, Managing Insolvency & How Finance Can Master Cash Flow - Dave Hallwood

The reason that we build models is one, to make decisions and we'll talk about that a bit more, but two, to sell a story and to narrate something. And if you're creating a model that's incomprehensible to the end user, which in this case was probably an asset manager, you know, a retail part, tell me that horror story.

Worst model you've ever had to deal with or that you've seen.

I kind of inherited a budget model.

Dave Hallwood, Finance Director of a leading group of independent fashion retailers in the uk.

Over the past eight years he's helped.

Scale businesses, navigate financial crises and master the art of financial modeling. From cash flow forecasting to high stakes.

Decision making, Dave brings invaluable insights to the table.

What's the biggest lesson you've learned in modeling cash flow in retail?

The most painful thing is sometimes you believe you're going to have more cash than you do and you just have to try and be pessimistic. You do actually have to be quite ruthless sometimes.

Any advice to somebody who may be listening that's thinking of doing the cfm?

My advice is one Sign up for it if you think you'd enjoy it. 2 Foreign.

Welcome to Financial Modelers Corner where we discuss the art and science of financial modeling with your host Paul Barnhurst. Financial Modelers Corner is sponsored by Financial Modeling Institute. Welcome to Financial Modeler's Corner. I am your host Paul Barnhurst, AKA the FPA Guy, and this is a podcast where we talk all about the art and the 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. Can't recommend them highly enough and that's why I completed the Advanced Financial Modeler and this week I'm thrilled to welcome on the show somebody who's not only completed the Advanced Financial Modeler, but level two, the cfn. Dave. Dave Hallwood, welcome to the show.

Hi.

Really excited to have you today. So let me give a little bit of Dave's background and then we'll jump into our episode here. So Dave is the Finance Director of a group of independent fashion retailers in the uk. It's why he has his fashion at Hat On Right, which he's grown with two other directors over the last eight years. Dave did a degree in Business Administration, then worked in a number of retail and hospitality management roles before self studying and becoming an accountant in his 30s. He's a fellow of ACCA and holds the CFM designation from the Financial Modeling Institute. He's passionate about modeling. He's done in house modeling for pretty much every scenario a small business could face, especially a retailer. Budgets, rolling forecasts, opening new sites, closing existing sites and the list goes on. Pitching for financing and more. He has also contributed material to the FMI CFM programs learning platform and he is an exam marker with fmi. All righty. So that's a little bit about Dave. First question. Dave, tell me that horror story. Worst model you've ever had to deal with or that you've seen.

Okay. Yeah, so I think probably one of the worst ones I saw when I came into the business I'm in at the moment kind of inherited a budget model and it had been written by a consultant who clearly thought that the more complicated and wizy they made it with more external links. And back in the day before sumifs, they had all these key columns and references to multiple sheets. It was basically a budget by store, but it was put together to pitch to a landlord for a new store and it was just incomprehensible. But I think the consultant ran up his hours charging for it. And it sounds like I'm really criticizing him, but I've got to be honest, at that point in my career, I'm not going to say that I managed to improve it. I just about managed to get my head around it. It's not something that I came in and magically fixed because back then I knew it was really difficult, but I didn't actually know for myself, I suppose, how to. How to rewrite it and make it better. But I'd like to say now, you know, I've got the knack of that.

Yeah. Often that there's some truth to that first one you inherit. You're looking at it and it's horrible, but you're like, okay, how do I fix it? Yeah, can I do better? And over time, as you start building them and you realize, oh, there are simpler ways to do all of this, you look back and go, man, if I could just redo that now, it'd be so much better.

Yeah. And don't try and be flashy. Don't try and be flashy. You don't need to, but it's kind of fun.

No, I'll tell this story. So speaking of flashy, when dynamic arrays first came out, I decided to build this model for an exercise we were doing all dynamic arrays so I could automatically adjust it. Trying to impress my boss. Got into the meeting and you know, started adjusting things and all the numbers were wrong. I'd messed it all up and just looked like an idiot. And I'm like, all right, just keep it simple. No need to be flashy. That was kind of my reminder that, you know, flashy isn't what it's cracked up to be.

Yeah, absolutely.

So what was your key takeaway from that experience, other than don't be a consultant?

Well, if you're going to be a consultant, I guess you charge by the hour, but I guess the key takeaway was that you can just go with some basic headlines. And the reason that we build models is, one, to make decisions and we'll talk about that a bit more, but two, to sell a story and to narrate something. And if you're creating a model that's incomprehensible to the end user, which in this case was probably an asset manager, you know, for a retail park, then you're not really hitting half the brief.

Yeah, great point. Right. If it's. If it's something that can't be understood by our audience, then. Then you're failing because the decision maker supposed to use it to make better decisions. They have to understand it to make better decisions. That's the reminder for keep it simple, as we like to say.

Yeah.

So, Dave, you switched your career to finance in your 30s. Tell me about that. What. What prompted that? It's not very often do we see that.

No. So I guess I. I started out my career in kind of retail and management, having done basic business and I ended up running a petrol station for about eight years. And it was one of those kind of. I fell into it, it was nice and it was easy, it paid the bills, I didn't have to work enormously hard. And I just remember thinking, one day, this is fine for now, you know, I'm paying my mortgage, but I don't actually want to do this forever. I just, I, you know, I can imagine I'm going to still be here in 10 years if I'm not careful. So that made me think, well, what do I really want to do? And then, yeah, I thought I was. At the time, I was volunteering, doing a bit of kind of bookkeeping or finance work on the side for someone. And I just thought, you know what? I could. I could make a career out of this. I think I'm quite good at it and with a bit of business knowledge as well as a bit of finance knowledge, with some technical training, I can maybe pull it off. And it took some time to actually pull it off. But that's kind of where it came from and why I decided to do it and I don't regret it at all.

So basically woke up one day and said, this isn't what I want to be doing for the rest of my career. What do I want to be doing? And decided finance was an area you thought would be interesting and.

Yeah, yeah. And hopefully everyone listening to this will agree.

I would hope so. Now if they don't, we just need to convert them so by the end of this episode they'll say yes.

Okay.

That's why I'm curious. Did you do all your kind of schooling at night? Like was that a difficult kind of doing, the education, something totally different than your work or kind of?

Yeah, yeah, it was, to be honest. So I started out with some really kind of basic technical stuff and built my way up and it took me quite a few years and I was in this sort of chicken and egg situation where you want to change career and that involves starting again further down the ladder and taking a pay cut. And so I thought, well, I need to study as high as I can so I can get in as well a paid as I can. But I'm still going to have to take an enormous pay cut. And kind of knowing when to do that wasn't easy at all. So that's kind of where it came from.

You mean nobody likes pay cuts?

Well, it's not so much, you know, likes. You have to accept that you're going to take a bit of a cut. But I still got to pay the mortgage. Right. So I couldn't just go and get a job as a really basic bookkeeper. I needed to get some study and it's very chicken and egg. I didn't have any full time experience. I had some qualifications and they were building up and I had more and more qualifications but still no experience. And it's kind of the longer you go before starting, the harder it is. So knowing when to jump was particularly.

Difficult, I think makes a lot of sense. I get it. Well, any advice you'd offer to someone who may be listening, that's thinking they might want to make us a career switch. You know, any things that you would give them to kind of watch out for from your experience.

Yeah, I would say make, make a plan, but also there comes a time when you just have to jump. And I remember I was putting it off and putting it off. I'll do one more exam, I'll do one more exam. You really do need to make that jump and What I actually did was I one day I was coming home and I just thought I really, if I don't go now, I never will. So I handed in my three months notice before I had another role which was, you know, absolutely terrifying. But if I hadn't done that, who knows how long would it have taken? And actually I managed to land my first finance role within five weeks of handing in 12 weeks notice. So in the end, I was actually negotiating to try and get out my retail job as early as I could. So. So make that jump, definitely.

Yeah. As someone I know puts it, at some point you kind of have to burn the bridges or burn the boats. So you can't just retreat and you have to go forward. And giving your notice is one of those things that motivates you in a hurry when you're like, okay, after this date, I have no income coming in. I have to figure this out.

Yeah, absolutely. Yeah.

Can be a great motivator for sure. So I'm curious, you've done a lot of modeling, you've worked a lot in the retail space. Why retail?

Well, I think it was partly luck but also partly prior experience. So I was really looking for a role in a small business. Having kind of run a small business for myself and always been involved. I've never been that much of a corporate guy. I really like being in a small business where you just get stuck in and you do what Evony's doing on a given day. And so I was really looking for a small business that needed someone, you know, who's around about my level to kind of head up a small finance function. And the opportunity came along with this retail group. They were kind of running on a bookkeeper from a little office above one of the stores and, you know, spending a lot of money on consultants and external accountants. And they got to the point where they needed to hire a qualified accountant because they were about to open more stores. And it came along exactly the right time for me. You know, I met the founder, we bonded. I guess I managed to persuade him, well, look, I've been a store manager, I've done it. I know what your store manager is going to be like. I know what retail is like as well as, yes, I can add up all your numbers and make you great P L accounts. I do have commercial knowledge and I think that's really important thing to be able to contribute, especially in a small business. You know, finance business. Partnering is a bit of a. Bit of a buzzword, isn't it? But actually it is true in A small business. I don't just do figures. I get involved in all kinds of things in terms of running our organizations.

So what's some of those things you've got involved in that have nothing to do with finance? Like where some days you're like, wait, I'm doing what?

Oh, all kinds of things. So, you know, we're clothing retailer. We sell clothes online. On Black Friday, everybody, I mean, everybody is in the warehouse picking and packing orders. You know, we've dealt with enormous stock returns of 30 pallets worth of stock coming out of the warehouse. And everybody is there stacking boxes, wrapping boxes, helping, you know, the pallet trucks, get trucks on a massive articulated lorry. And then also just things day to day where because we're a small business, we don't have a dedicated HR department. We don't have a dedicated IT department. And although we have MSPs and we subcontract things out, a lot of things that need doing, they just fall on your head. And because my other two directors are very much on the coal face, and I am the department that kind of sits in the office, anything that doesn't fit neatly into selling clothes in a store, anything administratively or legal compliance tends to come my way.

So you get to be a packer, a picker, a lawyer, HR person, and a finance person.

Yeah, and also the stock IT guy. And sometimes, you know, I can't. I do what I can, but I'm not the person to phone if your tilt breaks.

I've had people think I'm their IT person, and I'm like, not really, but I'll try. Like, well, you know, a computer. Yes, I use a computer every day. That doesn't mean I know it.

Yeah. And, you know, they say a little bit of knowledge is a dangerous thing. And sometimes I feel like I might be doing that with it. And I should really leave it to our experts who we, you know, we subcontract out to.

Yep, I hear you. I can relate to that as well. So talk about modeling in retail. What are some of the things unique? You know, let's start. Let's talk, you know, more retail versus E commerce. I know you've done some both, but let's start with retail.

Okay, so in terms of retail, I guess the hardest thing is modeling your cash flow and your sales. We are 100% business to consumer. We are really at the whim of the weather, literally. And I know retailers love to blame the weather, but it's honestly true. You were always waiting at this time of year for it. To get hot, so people buy shorts. And then by the time September comes around, we won't it to get cold, so people buy coats. And just that kind of not knowing your cash coming in. You don't have a debtor's book that you can chase and forecast when it's going to come in. So day to day, knowing where your cash is coming from and actually what your sales level is going to be makes it a real challenge because you have to have such wide variation in what your sales might do in a given month or even in a given year for a store.

How do you, how do you go about kind of forecasting the cash flow? How do you think about that? Because, right. I mean, obviously you can't forecast the weather, but you can look at things historically. There are some trending. I mean, yes, there are forecasts to the weather. You can't control it. But how do you, you know, how do you kind of manage that and think about forecasting cash flow with that lovely variable?

Well, essentially we, we agree a budget with every store manager. We just like their sales target and it's usually based on the previous year. And then we just have a bit of a wide berth in there in terms of plus or minus 20%. And we try not to commit more than 80% of our forecast sales in cash flow in a given month. And the biggest challenge we have is when we can pay to get stock into the stores. And it really is the lifeblood of the business. And if you, if your sales dip and you don't have spare cash to pay your suppliers for more stock, you can end up in a spiral.

Yeah. And so I'm assuming you're probably using some kind of revolver as well, in case it's needed. It's not just 100%. Hey, cash always has to come in, go out. There's some kind of managing in between there. Or how do you.

Yeah, we try and manage a reserve. We don't have any kind of bank facilities because retail is seen as so, so risky. It's kind of low, low reward. You know, it's not common at all for overdrafts to exist in retail. So we tried to work without any kind of actual formal revolver. We, we try and do it on our own. Operating cash flow and reserves.

Yeah, I can see why that's definitely the, the most important. What's the biggest lesson you've learned in modeling cash flow in retail? What's kind of your maybe biggest takeaway over the years? I'm sure you've had a few Times where you're like, I wish I would have modeled that a little different.

Oh, yeah. I think that. I think the most painful thing is that you sometimes you believe you're going to have more cash than you do, and you just have to try and be pessimistic. And sometimes you have to play hardball with your suppliers and you have to stretch the terms and you do actually have to be quite ruthless sometimes, because if you don't, then you. You're just going to run out of buffer and you're going to run out of road really fast.

And so how often do you find yourself having to negotiate with suppliers? Is that a very common thing that you've done over your career?

Oh, yeah, yeah. It's kind of never ending. And I think part of the reason for that is because fashion is very seasonal. So for a lot of our product, we order it six months in advance. The brands manufacture to order, and then at the beginning of the season, they want to dump all of the stock on you that it's going to take you six months to sell.

And.

And they've got factories who want paying, so they'd really like you to pay for it and you're going to need six months to sell it. So that negotiation of what terms there are when stock is delivered, what type of stock is delivered, when and how we can do some kind of payment plan to pay throughout the season, it's never ending. We don't stop negotiating with suppliers. Because if you didn't, then you would run out of cash really, really fast.

Yeah. Because if. If you give the ideal term, they all want. They want you take six months of inventory and pay on day one.

Absolutely. Yeah. And I can see why they do, but it's just not realistic for us.

Sure. Well, yeah, I mean, it's not realistic for most businesses. I get it. You know, and that's the thing that we always talk about. Nobody goes out of business having plenty of cash.

No.

Right. The reason people go out of business is they run out of cash. And when you have to buy everything up front and sell it after the fact, you're always going to be in a cash position. That's a challenge. It's why so many people love subscription businesses and SaaS, especially when they're annual. It's like, wait, you're going to pay me for the full year up front? I love this.

Yeah, that would be great if we could get every customer to just give us as much money as they're prepared to spend at the beginning of the year and Then come in and use it. That would be fantastic. But it doesn't work like that.

I don't know if you've seen it in the uk. They have in the US where they've done a few of these boxings where you tell them some of the brand you like, the type of clothes you like. It's actually a subscription service they'll send you. It's like one or two outfits every month that you can order and you just pay a certain amount.

Yeah, I have seen those businesses. Yeah.

You know, so they've tried to make everything into a subscription.

Gets wearing as the consumer, doesn't it? So many subscriptions.

It does. We have one, you know, here that's cropped up over the last year or two is there's a haircut place that does a membership. Subscription, membership. I'm like, yeah, no, I'm good. But yeah, I'm sure there's people who do it. All right, so cash flow is huge. What are some of the other other things with, you know, modeling beyond the kind of cash flow, you should think about retail, like, how do you think about individual stores and modeling them? Like, what kind of assumptions go into that?

Yeah, we have to model every store separately. And there's. There's loads of things that go into it. So sometimes where a store is in its life cycle, we've got some. Some stores that have been trading for, you know, a hundred years, and they're pretty solid. And, you know, if you get 2% growth out of them, they're fantastic. We have got other stores that have been open for about 18 months, and they're on an incredible growth trajectory. So even just forecasting where their sales might grow as their customer base goes up or their customer base kind of ages, just the number of sales. But then the key variables that go into the store are what the property taxes are, what the occupation costs are, so what the rent deal is, and also the staffing model of the store. The stores are all kind of different shapes. So for an example, we've got one old store that's on several floors. So even if it's completely quiet on a Monday afternoon, you need four people in there. And we've got other stores that are nice, they're square boxes, and you can run them on two people all day long. So just the basic labor model that goes into the store and what days it gets busy. And when you need to flex up, we need to flex down. That's probably where most of the work goes. But because it's different by store, if we didn't forecast by store. I don't, I don't think we'd be able to tell, you know, which stores are doing well and we wouldn't be able to decide which ones to open, which ones to close.

Yeah. So you're really kind of building each store model with those, you know, reviewing them with those managers. And it sounds like labor assumptions are going to have to be different depending on the store layout, obviously, how busy it is, growth assumptions. I'm assuming, though, you can do some grouping sometimes. Like, hey, all these stores are similar in the sense they all have the same layout, you know, same type of retail. Maybe they're in a strip mall or whatever it might be. Are you able to do some kind of grouping with those and the assumptions? Or you really find it's almost a custom model for each one.

For the active stores, it's pretty custom. But what that actually means is when we're looking at new store opportunity, we can usually say, right, use that store as a template. It's going to be about their level of sales, going to be about that size. You know, this is the working cap, it's going to need. This is what labor is going to need. We know what rent deal is on the table with the landlord. We can look up what the property taxes are going to be. So we do have kind of a couple of different sort of size template stores we use particularly when we're looking at new opportunities.

Sure. Yeah. So you're able to, which makes sense, you're able to take a predetermined set of assumptions to start with, going, all right, well, this store, similar to this group over here, we should see a similar ramp. Need this many employees, this working capital and then tweak it, you know, based on whatever things, you know, specifically to that store and then adjust it as it actually becomes active to what you're really seeing.

Yeah.

So I'm curious, you know, as you do this, I'm assuming just like anything, every store's forecast is wrong always.

Yeah.

So it. This feels a little bit, in some ways almost like a portfolio in that, hey, bringing it all together, you're trying to get it right. And you can be relatively close with most of the stores, I'm assuming, especially older ones, established, you know, new hyper growth, those that have a big change, I'm guessing all bets are off, but I would imagine a lot of them pretty similar. You can get pretty, you know, pretty close. I'm not saying spot on, but yeah.

I think you, you certainly can. On the cost front, the Thing that can just send you out wildly is, is what the sales are going to be. You know, sometimes the store will just keep growing even though we haven't forecast it to in other stores. Their growth just falls off a cliff and you don't always know why.

And what's the analysis like? I mean how often are you able to figure out or do you occasionally have some where we're like we just don't know what's going on here.

I don't think there's any where we don't know what's going on but sometimes we feel like we don't necessarily, can't necessarily do anything about it. So I would say at least every quarter we look at the forward review and the last one we did at the beginning of this year, we actually decided to close three stores because they were formerly profitable stores. But there are headwinds coming. Property taxes are more than doubling for retailers in the UK in April. The minimum wage is going up and employer taxes on employment are also going up. So we've had a couple of stores that used to be profitable and they were going to become loss making. So we, we have proactively closed them, which is a real shame. It's genuinely a shame. We've just had to make some people redundant. But we can't have loss making stores. We just, you can't carry them in this economic environment. It just isn't possible. So yeah, sometimes you have to make a really difficult decision that makes sense.

And it's never fun. Anytime people have to be redundant, nobody enjoys that. But you want a few people to be redundant or the entire business to go under. Right. You have to think of the bigger picture and that's how we have to manage it sometimes times.

Yeah, absolutely. Yeah.

And this is definitely a tough inflationary environment. You know, if your wages are going up, your property taxes going up and other taxes are going up, plus costs are going up for everything you buy. You, you got to figure out how much can you pass on to the customer, what are others doing? Because right, we as a consumer, the more and more we see this price increases, at some point we start pushing back. And retail has a component of discretionary for sure. You know, especially fashion. Not like you have to buy a new coat every year.

No.

You know, or you need those pair of shorts that you want for the new summer or whatever it might be.

Yeah.

How much do you find? I'm curious, you've been in retail a long time. Is there a pretty big drop you see during recessions or you find it pretty stable or what's kind of your in general you see with the stores and just the business.

So for our spot in the market where we occupy now most of our stores, I would say that we, we're fairly premium and so as a rule our customers tend to do better in recessions than you know, some of the people who are lower down in the market. So we're, we're a little bit more recession proof. But we have had some brands where prices have just gone, you know, know, way up. The cost of importing in the UK has gone up. The cost of cotton and raw materials has gone up. And what we have seen is consumers who, they'll keep shopping with us but they've maybe shifted down in terms of price point the brands that they're prepared to buy and that's affected the brand mix that we have. I'd say we probably have a, a much wider variety of brands now so that we can capture kind of more, more customers depending on where they sit in the market.

FP and a guy here and as you know, I am very passionate about financial modeling and the Financial Modeling Institute's mission. I have been a huge fan of the FMI for years and I was super excited when they decided to sponsor the Financial Modelers Corner. I recently completed the Advanced Financial Modeler certification and loved the entire experience. It was top notch from start to to finish. I am a better modeler today for having completed the certification. I strongly believe every modeler needs to demonstrate they are a qualified financial modeler and one of the best ways to do that is through the FMI's program. Earning the accreditation will demonstrate to your current and future employers that you are serious about financial modeling. What are you waiting for? Visit www.fminstitute.com podcast and use code PODCAST to save 15% when you enroll in an accreditation today. Everybody wants to cover the entire range. Kind of protect yourself against different segments having big changes. So one other thing I want to ask you about. I know you've been involved in insolvency situation situations, anything different there in the modeling any kind of unique things you found as you're in that kind of environment and modeling those type of situations.

Yeah, yeah. So first let me explain a little bit about what, what we kind of went through a few years ago. So we, we entered into a deal that went bad. Basically we entered into a franchise deal to open some stores for a, a brand that's well known in the UK and the cost of fitting those stores just spiraled and it ended up being four times what it was forecast to be when we were looking at it. And then the sales in Those stores were 50% of what they were forecast to be. And we looked at it and we thought, this is just. This is actually not viable. We've only opened two stores, we've got a third one on the way. This really. We've backed the wrong horse, unfortunately. And, you know, when you're in business, you have to take a risk. And we didn't have any choice, basically, but to wrap up that subsidiary really quite rapidly. So we had to put it into liquidation. What it felt like. And Ian got back to me and he asked me to have a Zoom call and we had a Zoom chat and we ended up talking and he was like, well, we need some videos. Do you want to make one? And I was like, yeah, sure, why not? So they've now got this really great platform and it's growing all the time. And instead of just Ian from FMI making tutorials, we've now got tutorials from, I think, six or seven people, possibly more experts from all over the world, approaching real questions that have been posed in the past. And they're all doing it differently. So it's kind of like the more lecturers you have, the more potential scenarios you might see, the more approaches you might learn about. And I feel like being part of that is actually really, really special.

Yeah, I think it's a great program. I know they have the community. I don't get to be as active as I'd like in the community, but I've presented there and I've been in there and I've even considered. I haven't brought myself to do it yet. I've considered doing the cfm, done the afm.

Yeah.

But I'm like, do I really want to spend the time. I haven't been able to quite convince myself yet.

Yeah. It is an investment of time. It really is. It's not easy.

Yeah, that's what everybody tells me. It's. You really need to put in the time.

Yeah.

So any advice to somebody who may be listening, that's thinking of doing the cfm, you know, outside of me, of course.

Yeah. So, first of all, I would say I really, really enjoyed doing it, although it was very, very difficult, I really enjoyed it. So if you like kind of solving puzzles and anyone who's even vaguely interested in competing in the World cup, you will really like doing cfm. So my advice is, one, sign up for it if you think you'd enjoy it, and two, try and develop your problem solving skills. That's all it is about is solving problems. So my key thing I would say is there's loads of extract ourselves from these company guarantees. We've got some tax arrears we need to write off. And if you don't vote for this and you don't pass it, then we'll go out of business and you will lose a future trading partner. You won't have any business from us at all in the future. So the best thing for all concerned is to kind of accept this and then we can move on with a much healthier business. And that's what we did. We managed to get our voluntary arrangement passed with a really high proportion of people. And the reason that worked, one is because we did some really great modeling and I'm not going to take all the credit for that. We have some specialist advisors who did some really fantastic modeling on all the different scenarios that you could show your people. And then secondly, the relationship, the telling a story, the getting out there and communicating with everybody about. Right. This is the situation we're in. We're not the only retailer who's got this. This is our proposed way forward. Please join us. And yeah, I have to say, it was really successful and I learned so much going through that whole process. And I heard it said from our advisors that you haven't really. You've got to do some restructuring and you need to do it pretty quickly.

I guess I haven't cut my teeth.

Ask questions. Try every single one of them on your own first. Before you watch any tutorials, before you look at any stock answers, try and solve them for yourself because that is actually the skill that you need to develop. It's not like AFM where you know what you're going to get and you just need to learn kind of what the rules are. With cfm, you don't know what you're going to get. It's going to be a complex question and you're going to have to sit there and you're going to have to think, how am I going to answer this? What am I going to do? Both from what Excel formulas am I going to use, but also just how am I going to structure this? What calculations do I need to carry out? So try it by yourself and really develop your problem solving. That is what will stop you panicking. The exam.

Got it. So basically being prepared, actually studying, practicing, learning the formulas, doing your homework. Yeah, it's funny how that works in exam if you're prepared. But no, that's what Makes sense. I think he gave a lot of great advice there. So I want to ask a kind of a fun question. This is. What's the most unique or fun or creative thing you've created in your personal life using a spreadsheet?

Wow. I don't know. I don't know if it's creative. But the thing that I found the most exciting for a long, long time, probably the longest running spreadsheet that I had was tracking my mortgage and the overpayments I was making and how much money I was able to save by overpaying my mortgage even slightly added up to tens of thousands of pounds in several years. And a lot of people think that's. People I know in real life think that's terribly geeky. And why. Why are you excited about. About that? But actually, I saved a fortune and I was able to work out to the penny how much I was saving. And I really, really like that. So that probably sums me up quite well.

There you go. We know we have a numbers nerd. When you do that, you're not the only one who's done stuff like that. It's always rewarding to see all those extra payments. They really make a difference.

Yeah, they do. They really do.

All right, what's your favorite Excel shortcut?

Okay, well, I don't know about. I'm a favorite saying. I'm going to say, like, I'm really passionate. I think it's really clever. But the ones that I use the most, and I mean several times a day, are alt W N, which opens a new window. So often I'll get an Excel sheet and I want it open in more than one place. You know, if I'm marking a paper or someone sent me something, I'm going to split it out into three windows so that I can look at three tabs at once and then alt W F F or alt WFR to freeze the rows in those new panes so that I can move around in a sheet really, really quickly. It's not exciting, it's not that clever. But it's so useful and it's so quick.

Yes. When you said alt wff, I can think of. Many times I've done that also. Yeah, yeah.

And isn't it really frustrating that Excel doesn't store the view on more than one window? So every time you open up a new window, you have to reset your frame. Freezing.

You never know, one day they might fix it. Go ahead and put in that.

I really wish they would store that so you can close it down before you send it to someone else. But next time when you open it up and you hit alt wn I, all those freeze panes on pain number two and three were the same, but.

Hey, ho and I could always create a macro, I guess. Yeah, but I know what you mean. All right, we're going to move into one of my favorite sections, our rapid Fire section. So I'm pretty sure you know how this works, but I'm going to tell you the ground rules. Know, it depends. Quick answers. You pick one side or the other, and then at the end you can elaborate on one or two. Because we realized real world, it does. It depends sometimes. But that doesn't make for fun radio, so to speak. Or listening. So, you ready?

Yep.

Circular references. Yes or no?

No.

Vba. Yes or no?

Yes, but only for formatting, never for calculating.

All right, you're not the first to give that answer. Do you prefer horizontal, lots of seats, or vertical? Put it all in one.

Vertical.

All righty. Dynamic arrays, yes or no?

Yes, but don't overdo it in modeling.

What about a fully dynamic model?

No, far too volatile. Once you get up to any kind of size, far too volatile.

External workbook links, you see.

Still a necessary evil. Same answer I gave last time, but less so. Someone said to me, learn Power Query if you think they're necessary. And I've got better at Power Query. But there are some things. It's still just the best thing to do is just have a link. I'm sorry.

Now, I'm sorry. Named ranges, yes or no?

No. Takes too much time to create. People just don't do it.

All right, do you follow a formal standard like FAST or Smart or any of those that are out there when you're modeling? All right, here's. There's a few other new ones in here. We're going to get to some of those. Should financial modelers learn Python in Excel?

I wouldn't say should, but it doesn't do any harm to learn a programming language.

What about Power Query?

Absolutely. You definitely should. I promise you it will save you time. I was listening to the episode from Sean Mooney, who I met in London a few weeks ago, and he was talking about saving 75%. He's not exaggerating.

No. It's amazing how much time you can save when you really learn Power Query. It's a fabulous tool. I love it. I learned SQL first and then I was environments where I really, really didn't have access to do SQL and I learned Power Query and I was like, oh, this is Great. What about Power Bi?

Super useful, but perhaps not for modeling. More than Python, but a lot less than Power Query.

All right, what about Excel? Will it ever die?

I don't think so. I think Google sheets and the competition now keeps them both on their toes and they'll probably both be around.

There's a lot more competition, which is always good. We win when there's competition.

Yeah, yeah. How much has it changed? How much have things got better? How many new things have come it no human can keep up with it anymore.

Oh, I know. I just, like, I do a, you know, training for a living on Excel and I'm like, I can't drop. I haven't had times to learn, like, rage expressions and other things or like, have you played with this? I'm like, for a couple minutes, you know, and I'm like, I want it. I. But I do prefer sleeping. I'm gonna. I'm not gonna lie. All right. Do you think AI will build the models for us in the future?

Yes, and that scares me a little bit.

You're not alone on that. So are financial models the number one corporate decision making tool?

I'm going to say no, and I'm going to come back to that one.

All right, well, I'm going to ask you now, if you said no, what is. We'll go there now.

Gut.

Why?

Gut feeling. Because decision makers, sometimes they just go by their gut. And I find that a model is usually a go or a no go, but it's not the actual decision making tool.

And do you think the gut is a good thing or a bad thing?

I think if the person is the right person making the decision, it can be absolutely the right thing.

Yeah, it's a tough one right there. There is a balance, I think even as modelers, we recognize just because a model says yes and just because the math makes sense sense doesn't mean it's always the right decision.

No. And there's way more to it than just what the numbers say. For sure. I'm probably going to get fired now from being an accountant for saying that. But it is true.

I won't fire you. I actually agree with you. But there. I'm sure there could be a few. So don't put it on your resume. Don't put this episode on.

Absolutely.

Yeah. All right. What's your lookup function of choice? You got to look something up. What formula you using?

Index and X match.

And I think that probably ages X match when they say index.

No, I've learned to use X match because it's brilliant. But I've never, I never got to grips with xlookup because I've never needed to, because I learned to use Index Match and I'm old now, so that won't change. If I was 20 years younger, I would have said of XLOOKUP, I'm sure.

Well, you know when someone tells me Vlookup, it's usually somebody that learned Excel 20, 30 years ago and just doesn't want to change.

Yeah.

And so I get it. Yeah.

Or I learned Vlookup. Then I saw Index and thought, wow, Vlookup is terrible. I'm never touching that again.

You know, you can do a lot of things with VLOOKUP and XMatch and even if you use choose columns now, you can get through nearly every one of the weaknesses of vlookup with a few exceptions. But I still wouldn't prefer it much easier to use Index Match or xlookup.

It's so natural. And now you can use it to return a two dimensional array with a single formula. It's brilliant. And I just, I don't need to do anything else.

I know how to do it well, that's what matters. All right, we're going to wrap up here. So were there any more of those on the rapid fire questions you wanted to elaborate on?

Yeah, so just talking about decision making and why, in our case specifically, models are usually used as the go or the no go. There are a number of things that we'll put into deciding whether to open or close a store or whether to know bid for an acquisition or whatever it is. And often they're things that can't be modeled. So what brand's going to supply us in that town? What is the staffing like? Do we feel like it's going to be easy to hire the right people? What is the deal on the table from the landlord? And sometimes that not just about numbers, that's about, are they investing in the town, Is it owned by the council, Are they letting it get run down? Is it just been bought by someone who's got tens of millions of pounds to invest? So there's lots of things that go into making that decision and the model will usually tell us the payback and the forecast ebitda, and that will be a go or a no go. And sometimes the model doesn't look great but we decide to risk it. And other times I've had the situation where the model looks terrible and I've got my other two creative people saying, yeah, but my gut just Says it should work, it should work. I don't know why it doesn't. And you're like, well, look at the numbers. I don't think we should do it. And sometimes we still do do it, despite what the model says, which is why I. I don't think it's the actual decision making tool. It's like the sense check. And sometimes it can persuade the person making a gut decision not to do something, and sometimes it can persuade someone who thinks my gut says it's a bit dodgy. And you go, well, the model says you can't lose and you've tried all these scenarios and it looks really, really good. Let's go for it. And we've definitely done that before where we've looked at a deal and gone, do you know what? Yep, this will be brilliant. This is worth it.

I like the way you describe that. And it's just a reminder. There's the human factor and there's the model. You know, I kind of think it is the number one corporate decision making tool, but not in isolation, which kind of gets back to the gut and so many other things. If you're doing it strictly just off the model and always just taking whatever it says, I think you have a problem.

Absolutely. Because the world just doesn't run on numbers. I really wish it did, but it doesn't. There's too much more. It's a proxy.

It's wrong to start with.

Yeah, absolutely.

You know, and it doesn't include every variable. So it's wrong, it's biased. It's just like a human in certain ways, regardless of how brilliant you build your model.

Yes. But if either of my business partners are listening, my numbers are never wrong.

Okay, I was waiting for you to say something like that. All right, well, thank you so much for joining me. As we wrap up, if our audience wants to learn more about you or potentially get in touch with you, what would be the best way for them to do that?

So you can catch me on LinkedIn or sign up for the FMI and I'll definitely be on their platform.

All right, great. So I know they have their financial modeling community. You can check them out there. LinkedIn. I'm sure you can get some advice if you're looking to take the test. But thank you for carving out some time today, Dave. It was a pleasure having you back on the show. I know you joined me for one of the early ones where we talked about your experience with AFM and cfm, but it was fun just having you on the show. So thanks for joining me.

Okay, great. Thanks very much.

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