From Classic Cars to Aircraft: The Art of Modeling Distressed Companies with Vaughan Grandin

In this episode of Financial Modeler’s Corner, host Paul Barnhurst welcomes Vaughan Grandin, Managing Director and Head of Financial Modeling at Teneo. They discuss his experiences building financial models in distressed situations, the importance of empathy in working with clients, and examples of complex models, including airlines, aircraft leasing, and classic car dealerships.

Vaughan Grandin is the Managing Director and Head of Financial Modeling at Teneo in London. He is a Chartered Accountant with the South African Institute of Chartered Accountants and has more than 20 years of experience in corporate advisory, finance, and restructuring. He leads Teneo’s financial modelling team, which supports clients and other advisory teams by creating reliable models for distressed companies, transactions, and stakeholder analysis.

Disclaimer: All examples and stories shared in this video are from Vaughan Grandin’s previous roles and do not reflect his current employer.

Expect to Learn

• Why empathy matters in financial modeling

• How to approach modeling in distressed situations

• The role of entity priority models and circular references

• Key principles for building robust financial models

• Lessons from real-world modeling cases across industries

Here are a few quotes from the episode:

• “Empathy is a word I think about a lot in modeling, especially in distressed situations.” - Vaughan Grandin

• “A good model should be able to take whatever inputs you give it and actually use them to solve a problem.” - Vaughan Grandin

• “In distressed situations, time pressure is real; sometimes, a company only has weeks of cash left.” - Vaughan Grandin

Follow Vaughan:
LinkedIn - https://www.linkedin.com/in/vgrandin/?originalSubdomain=uk

Company - https://www.teneo.com/person/vaughan-grandin/

Follow Paul:
Website - https://www.thefpandaguy.com  
LinkedIn - https://www.linkedin.com/in/thefpandaguy
YouTube - https://www.youtube.com/@thefpandaguy

Follow Financial Modeler's Corner: 
LinkedIn Page- https://www.linkedin.com/company/financial-modeler-s-corner/?viewAsMember=true  

Newsletter - Subscribe on LinkedIn-
https://www.linkedin.com/build-relation/newsletter-follow?entityUrn=7079020077076905984

Sign up for the Advanced Financial Modeler Accreditation Today and receive 15% off by using the special show code ‘Podcast’.

Visit https://bit.ly/4fYK9vY and use the code “Podcast” to save 15% when you register. 

In today’s episode:
[03:22] - Model Horror Stories
[06:52] - Empathy in Modeling
[07:55] - Inside Teneo’s Team
[12:50] - Classic Car Dealership Model
[21:16] - Modeling in Distressed Situations
[27:56] - Managing Complexity
[32:07] - Aircraft Leasing During COVID
[40:31] - Six Elements of Robust Modeling
[46:51] - Rapid-Fire Excel Q&A
[53:45] - Closing Reflections

Full Show Transcript

[00:00:51] Host: Paul Barnhurst: Welcome to Financial Modeler’s Corner. I am your host, Paul Barnhurst, aka the FP&A Guy. In this podcast, we talk all about the art and science of financial modeling with distinguished guests and modelers from around the globe. The Financial Modeler’s Corner podcast is brought to you by the Financial Modeling Institute. FMI offers the most respected accreditations in financial modeling, and that's why I completed the Advanced Financial Modeler this week. I'm thrilled to welcome on the show Vaughan Grandin. Vaughan, welcome to the show.

[00:01:44] Guest: Vaughan Grandin: Hi. It's great to be here. Paul.

[00:01:46] Host: Paul Barnhurst: Yeah, I'm really excited. I know we I think the first time we had this scheduled record was, what, six weeks ago?

[00:01:50] Guest: Vaughan Grandin: Yeah, I think so. It's,

[00:01:52] Host: Paul Barnhurst: Yeah. And I think you had some stuff come up, and then I did, and I think we've rebooked this one 3 or 4 times, so I'm glad we were able to get both our schedules together and make it happen. So thanks for being patient with me. Alrighty, so let me just give a little bit about Von's background. Von leads Teneo's financial modeling team and is a Chartered accountant from South Africa. With over 20 years of experience in corporate advisory, finance, and restructuring, helping corporate lenders and investors, often in situations where companies are distressed. He has modeled situations as varied as creating a liquidation analysis for a global automotive rental company, modeling geographic operating scenarios for a subsea construction company, and developing Excel remediation calculators for a UK clearing bank. Von helped set up the Kenyo modeling team in 2021 as a supporting business line for the firm's financial advisory practice. Its aim is to create robust, reliable financial modeling solutions both for clients and to support other teams. The team often supports companies in distress, where models or analysis must be created rapidly and withstand stakeholder scrutiny to ensure business survival. Alrighty, so here we go. First question tell me that horror story. I'm sure you've seen some models, or built some or worked on some that you're just like, how did we ever build this? What's that worst model story you have for us?

[00:03:31] Guest: Vaughan Grandin: I think there's been quite a few models, as you say, over the years, and probably the one that sort of sticks in my mind a little bit was airline model. Now it was the usual horror story. There were lots and lots of tabs. I think this one was about 25. The inputs were all over the place, and obviously what we were trying to do was to support the company with understanding their forecasts and maybe providing some sensitivities to investors. So we needed to know where the inputs were. But then there were hard codes all over the place and something just wasn't right. And it ended up being the corkscrew on the cash forecast. Suddenly the cash number just changed midway through everything, and clearly they'd actualized the cash. But it was just that it took a little bit longer than probably it should have to have worked that out. But obviously the cash changing meant the business was in a completely different situation to what the model was necessarily thinking. And so that kind of hit me hard because obviously cash is really, really important in a distressed situation. So that was one that scared me. It was kind of scary for a bit. Got to be honest though, the models that are simpler sometimes where you just go through this model and you end up with hard coded inputs which are identical to the outputs. They annoy me a little bit more. At least if someone's worked hard to make a model and their sensitivities and it's a bit crazy. I can appreciate that. But the simple ones sometimes where there's no thought around that are probably more annoying.

[00:05:05] Host: Paul Barnhurst: I could see that in the sense of you're like, okay, this is an easy model to build, and you hard coded in it and you gave no real thought to it. So it's probably more annoying in the sense that you feel like they didn't put effort or the other one, they at least put effort, even if it takes you longer to unpick it.

[00:05:19] Guest: Vaughan Grandin: And I think empathy is a word I think about a lot in modeling because, particularly in a distress situation, the person building the model has probably got no time, may not even have modeling experience, and they're trying to do the best thing they can in the short term without any real training. And so if you can understand that and appreciate that, then sometimes it means it's a little bit easier not to get too frustrated.

[00:05:45] Host: Paul Barnhurst: I love that you used the word.

[00:05:46] Host: Paul Barnhurst: Empathy with modeling, right? Everybody pictures an Excel nerd that wants to spend all day on a spreadsheet. You don't realize the importance of those soft skills such as empathy. How has that helped you? Because I'm sure you're working in very stressful environments. When these are distressed companies, maybe they've already filed bankruptcy or they're close to it and they're working through restructuring.

[00:06:08] Guest: Vaughan Grandin: I think thinking about the human nature behind models is important. And I mean, I helped a client once where the forecast was getting updated, but suddenly the latest forecast was significantly different to previous ones. You could just see formulas were being put on top of other formulas in the same cell, and you're suddenly going, whoa, this is a beast formula that wasn't there before. But what you could clearly see was that that person was under a lot of stress, and there were suddenly new variables that were coming in that they needed to take account of. And I think it does help, because it means that you can sort of sympathize with them a little bit more, be a bit more supportive. And often doing those sorts of things helps you build a bond between yourself and the client.

[00:06:50] Host: Paul Barnhurst: Makes a lot of sense. I am curious, this is a question. I always love to see how people answer it because everybody's a little different. If I asked you, how would you define what is a financial model or what you would consider a financial model, how would you answer that?

[00:07:06] Guest: Vaughan Grandin: I think it's quite a big thing sometimes from a risk perspective. Um, between a model and say something like an analysis. For me, a model is usually some sort of quantitative analysis, often in Excel, but it doesn't need to be, where you have sort of predefined inputs. So you've got inputs. You then have a methodology to process those inputs to turn them into outputs. And, a good model should be able to take whatever the inputs are that you give it and actually use them to create the answer to solve a problem. And I'd say it's as simple as that.

[00:07:41] Host: Paul Barnhurst: I like it, I like how you kept it kind of simple there or basically, look, you need to have those inputs. You need to be solving a problem. You got to run it through some kind of calculations. All makes sense to me. All right, tell us a little bit about Teneo and the work you're doing there. I think you've been there for about five years now. I think you said 2021 is when you kind of came in to build the team there. What's that been like?

[00:08:05] Guest: Vaughan Grandin: So I arrived in October that year. And I mean, the firm is a global firm. It's a consulting firm. They really were there to help, you know, CEOs, CFOs, companies in times of uncertainty and support them. So, you know, the firm has strategic communications. It has investor, investor relations, management consulting, um, and the team that my team is in, which is financial advisory. And that's really around, um, businesses going through some sort of major event or stress. You could think of that as maybe an equity transaction, a debt transaction. Um, we've got offices in the US, the UK, the US. Offshore. Asia Pacific. Australia. But in the UK we've got a really big team and there's a very strong existing skill set around distress and turnaround. So things from insolvency to helping businesses optimize themselves to just supporting transactions. Now where my modeling team fits in that is is two ways. So one, we do what a traditional modeling house would do where we will sell produce models for clients and deliver them directly. So, you know, if you wanted a three statement model, what we spend a lot of our time doing, though, is supporting the rest of the teams in the business doing their work. So it might be, you know, data manipulation. It may be producing a tactical analysis to show lenders to support a conclusion that we're doing. It may be just producing a model to support a particular transactional event. So we sort of split between those two areas. We do a fair bit of of training too, because we're all pretty passionate about Excel and we like to show others about it. So, um, there's a bit of that too. Um, but yeah, it's, um, pretty exciting. And, uh, we generally have a good time.

[00:09:50] Host: Paul Barnhurst: I love how you said we generally have a good time, right? I mean, work sometimes can be tough. Work sometimes can be, uh, maybe not not fun. But if you're generally having a good time and you're trying to make the best of it, it always goes a lot better. At least that's what I find. It's amazing how much it comes down to attitude.

[00:10:09] Guest: Vaughan Grandin: Yeah. And, and and particularly because we know that we've got I mean my team has business consultants too, but we are really focused on Excel. And we appreciate that others may not be as focused. So you know, very often we've got our own sort of in-house humor and can appreciate things, and issues and errors. Um, you know, if we see a crazy formula in a file we get from a client, we just sort of post it and have a chat about it sometimes to unpick it. So there's a real community, which I think is really great.

[00:10:39] Host: Paul Barnhurst: Right. And that's when you know you're a finance modeler, an Excel nerd, when you start sharing it with everybody else and you're picking it and kind of having fun with it. As one guy said it the other day, he found a formula on like row 7000in some model and input. It was an input, he said. It was like row 7000. He called everybody over to show him, and he was like, we were rage scrolling. And somebody responded to that by saying, tell me you work in finance without saying you work in finance.

[00:11:07] Guest: Vaughan Grandin: There's a lot of crazy things out there.

[00:11:09] Host: Paul Barnhurst: Oh, yeah, we've all seen them. You know, the different things people do and most of the time they're well intentioned. It's just people trying to learn and not knowing a better way. I mean, I look back, I think a lot of stuff I build early in my career in crazy formulas in Excel, and I'd be embarrassed for people to see them now. Like, wait, I built that.

[00:11:28] Guest: Vaughan Grandin: I bet you there was a story behind it. And that sort of goes back to that empathy. You might start with just a simple statement, and then suddenly a new variable is introduced. You don't have any space in the model, so you suddenly plug it into the same cell and then something else comes. And that's why models that are old, that have been going for 5 to 7 years in companies are generally obsolete because there's just so much extra formula in them.

[00:11:52] Host: Paul Barnhurst: Yep. And I think it's two things. It's the last minute trying to do it. And it's you know, for many people outside of you starting your career in investment banking or M&A, PE, you generally get some good training on modeling. If you start in corporate finance, you really don't get training on how to model. It's just handed to you and you're trying to figure it out and have never been taught. At least that was my experience. And so nobody ever taught me best practice. Hey, separate your inputs, your outputs, your calculations. You know, do this color coding, all those type of things. And so I was just building to survive and get the work done. It's all right. Somebody needs it. Right. The number looks. Makes sense. All right. Let's move forward. And yeah over time you start to learn. Oh, yeah. That probably wasn't a very good way to build that.

[00:12:37] Guest: Vaughan Grandin: Yeah. It's always just that subtle hint. So maybe just moving your assumptions into an assumptions tab is probably the fundamental starting point. Just makes life so much easier. So yeah I mean the struggle is real for sure.

[00:12:50] Host: Paul Barnhurst: All right. So I want to jump into a few different models you built when you and I chatted. You share some fun things. So one you mentioned is you build a model for a classic car dealership. That was a lot of fun. Tell our audiences about that model. What made it unique?

[00:13:03] Guest: Vaughan Grandin: Firstly, it was an interesting job because every other person in the firm at the time, and it was a long time ago. Um, but all the partners were coming into the room to just have a look and see what, um, what cars were on the spreadsheet. Uh, you know, we're looking at, like 1950s Jaguars, Ferraris, Aston Martins. I think they had about 80 to 100 cars and they were retailing, you know, there were a bunch below a million, but it was sort of 1 to 5 million. So, you know, I think there was a famous, uh, celebrity in one of them. And the problem with the business that had been underperforming and the bank was a bit concerned. They were looking, you know, covenants were getting breached. So the business basically needed to create a forecast model. Now, um, this happens very often when you go into a business. I mean, I was part of an integrated team. There were a lot of people trying to deal with the bank. My role was to produce the forecast model, and the client goes, well, you can't do that. A model can't replicate the business, which is, you know, a common a common challenge. And then you sort of have to go and ask, well, why is that? And they felt the reason in their business was because of the level of part exchange. So normally if you're a retailer, you'll sell, sell stock, get cash or debtors. In this business you end up selling your your car. But it was a lot of sort of swapping of vehicles, which seems to be a classic car thing. So, you know, someone might sell a swap, a Ferrari for a Porsche, but adding, you know, a couple of hundred thousand pounds on it, which is an interesting business model. And maybe part of the reason was in trouble in the first place. But you're suddenly doing a model where you've not only got to like, move the stock out, you've got to make assumptions about new types of stock going in. And that led to quite a lot of discussions.

[00:14:51] Host: Paul Barnhurst: I can imagine the same transaction. You're bringing inventory in and sending it out and there's a cash exchange.

[00:14:58] Guest: Vaughan Grandin: Yeah. And then you also, because of the nature of the business, that there weren't that many stock items, but they were high value. The way to sort of get the bank, because the main purpose was to get the bank on the side, you then needed to work out, actually, when are you going to sell these initial vehicles? So I sat down with a car expert who was very passionate about all his cars, bored me about them for a long time, but we sort of worked out a profile of when each car would likely sell. Now that's, you know, sometimes you think of a model as a whole bunch of small assumptions, slash lies, um, that you then build up toward a coherent picture. And it was sort of a little bit like that here, that each car had a selling price and had a notional date when it would be sold. You know, then we looked at it at an individual level and at the portfolio level to see the sales. But then what you sort of had to model in was sort of work out what cars you were then going to get in after that, and you sort of got this like a tearing system.

[00:15:54] Guest: Vaughan Grandin: So if you sold, I don't know, three expensive cars, you might get five smaller cars. And the model worked that out. And, then those cars needed to have their own profiling criteria. So you sort of had this inception. And, you know, I was younger at the time and that wasn't the best way to do it. I'm not 100% sure, but, um, I definitely, uh, enjoyed creating these layers of inception, of selling, selling the vehicles. And obviously you could flex things like when the car was sold, how much, etc. we presented that to, to the bank. And I think the thing with the bank they liked about it was, again, you're making a lot of assumptions, but you're making assumptions and thinking about the real drivers of the performance of the business. And the bank liked it and they supported it. So yeah, it was a fun job. I loved the movie inception for a little while, and yeah, it was nice to look at a bunch of vehicles.

[00:16:46] Host: Paul Barnhurst: Yeah. And I'm sure you, like you said you were the popular person in the office. Everybody wanted to come see your model, which probably isn't usually the case. Not that they want to see the work you have done. They want to know how much the vehicles were worth.

[00:16:59] Guest: Vaughan Grandin: Yeah, exactly. I think the problem in that case, that there'd been so much transacting of the vehicles that the stock value was getting artificially increased because of the stock valuation of the cars coming in. And that was ultimately one of the things that was impacting the business.

[00:17:16] Host: Paul Barnhurst: Bad financial models can lead to bad decisions or worse. If you want to learn to build better models, impress your boss and your clients, get the Advanced Financial Modeler accreditation. Podcast listeners save 15% on AFM registration. Just use the code Podcast at http://www.fminstitute.com/podcast.

[00:17:39] Host: Paul Barnhurst: Interesting. It sounds like a fascinating one. And I know, you know, you mentioned they were in a little trouble. Uh, they had been breaking some covenants and things. Distress companies, companies in trouble, turnarounds that you do a lot of that. So what is it? You know, you like modeling in those situations or maybe walk through kind of how you think about going into building a model when you know you're coming into a distressed business situation?

[00:18:04] Guest: Vaughan Grandin: Well, I think the first thing is that your time, a level is very, very different to, to the usual projects. I mean, I remember in 2013 going to Singapore to a $1 billion turnover subsidy construction company. So this is a big company. I only had money left to pay either their rent or their salaries for the next month. For the next month, that was it. So I had to build a model really, really quickly because I needed to get with the team to get the, you know, equity stakeholders inside to actually get them to invest in the business at least a little bit more to get it trading. So you've suddenly got this, this time pressure and it's real pressure. I know we often get artificial deadlines in our work, and no one likes that. Work that often happens with my team is the adrenaline is kind of real because it's really, really important. So time pressure, um, and often what you need to do initially may not be what the final model is. Another example, last year we worked in a company which had three weeks of cash left. We think it had three weeks of cash left. It was remarkably burning about $300 million of cash in that time, and we needed to actually know when this was going to happen.

[00:19:20] Guest: Vaughan Grandin: So suddenly we needed to build a cash flow really, really quickly. How is it the best cash flow in the world? No, but you need an answer quickly to at least give people an idea of what's going on, and then you refine it. Normally, in a modeling process, you scope everything out. You set everything and begin in a measured fashion. You don't have that luxury here. So those are probably the big issues. And then you're obviously thinking a lot more about cash and you're thinking a lot more of your stakeholders. It's not about company management. It's about the providers of lead, of the debt, the equity. These are people who are pretty, pretty tough. And they are probably pretty annoyed that their investments are in this position. So they scrutinize things, and they are sometimes unpredictable. They can ask questions and demand functionalities in what you're doing, which maybe you didn't think of before. So there's this real shift from management to looking at what the lenders need. And that's sort of where we get sort of bespoke models where we start looking at, uh, like, what are the outcomes to the lenders? And there's these models called entity priority models, where we try and actually work out what their likely return would be in certain scenarios.

[00:20:32] Host: Paul Barnhurst: There's a point you made there that I think is important to call out. You know, you often have a lot more people involved in the deal, a lot more stakeholders, especially if there's a lot of different debt holders. Public companies got all the equity holders now. They're not going to all see the model. But there's much more. The number of people often that have a stake in this is greater than a new project where there's 2 or 3 investors or M&A and there's two companies where, you know, they're healthy. So debt isn't involved. You know, you're not getting you're not you don't have the level. And that gets to the next point, which you mentioned I know can get really complex. And I'd just like you to talk a little bit about entity priority models. You mentioned they're often very complex. You know, they'll require circular references sometimes when we chat. So maybe tell our audience a little bit more about those type of models and why you have to use them in these distressed situations.

[00:21:27] Guest: Vaughan Grandin: I love the fact they sort of exist because I'm not a fan of circularity. Um, but then you go, oh, these models need circularity. And I think that's, um, that's kind of critical. If you imagine if you had one business, uh, and the lender had lent money into that business and say that business had defaulted, um, the first thing you know, you would know that that lender may have the option to wind the business up. So if you start at a single entity and what we call an estimated outcome statement, let's assume we are to sell the business or, or sell all the assets. And then we turn the assets into cash. That cash is then obviously used to pay any costs. And then you have a waterfall. So good fun, you know, waterfall where there might be guarantees, different ranking securities. So then we have to spend time speaking with lawyers. Um, and then obviously everyone will get paid depending on how much cash is there. And you could say to the lender at the end of it. Look, if you want this person, you might only get $0.20 in the dollar. It's not. Maybe, but if you support the business, it's likely you'll get a better return. So that's one business. Now, an entity priority model is more complex because you're dealing with an entire group of companies. And that could be I mean, I've done 120 plus companies in one one model.

[00:22:43] Guest: Vaughan Grandin: It's, you know, it's you get global groups. And very often what you're trying to do here is not model. You hope the scenario you're modeling isn't going to be what actually happens. But you want to sort of say if you don't support this deal, the worst case could be this if. And there you've suddenly got an interesting thing because you're doing this modeling for every entity. But you have two key differences. One, if a business, if there's a profit at the end of selling a business or you sell a business, and then that money goes up to a shareholder. So you've got this interrelationship through equity. The second one is intercompany loans. So if one entity owes money from another entity then why? If you did that traditional estimated outcome statement, you'd wind everything else up. The creditor of that company would be, uh, the other company which is then receiving the cash. So that's a function of that wind down. But if you suddenly then extrapolate that across 120 companies, all of them are going to be potentially owing each other money. So you've got this interconnected set of, of, uh, events where each company's return to creditors from that company will influence all the other companies because it's impacting their outcomes. Does that make sense? It's quite hard to circularity is very hard to explain.

[00:24:03] Host: Paul Barnhurst: Sometimes each entity impacts the next one. It's kind of like, you know, interest. Right. Because at the beginning and closing you want to take an average that impacts your final cash. And so you have a circular reference, unless you put some kind of breaker logic in it to manage it.

[00:24:21] Guest: Vaughan Grandin: Except in this case, because we actually want to work out definitively what each entity is going to pay to the other ones, you kind of need to just let that circularity run. Now then there's one more piece on top of it. So you've got the circularity. So we've got, you know, um, we've I've had models where you've sort of been running circular references 800 times. Uh, I've got a colleague who's worked a bunch of them. It's in the thousands. You just have to let the model, like, just sit for a while. But then if you think about the other things, you've got all these returns from the different entities coming in. But then you may well have cross guaranteed security. And so if you've got, say, I don't know, a security over five entities and say three of them are overpaying that entity, you then also have to work out an allocation mechanism by which the guarantee is repaid equitably amongst the companies that can repay their guarantees. So suddenly you've got these. All these businesses are being wound up at the same time. And we do it in one model. Uh, they are then repaying each other, but they are also repaying creditors. And we need to make sure they don't overpay those creditors. Um, so all that amidst a very complex, uh, waterfall can make things very interesting. But it's really important because what you're almost saying to the lenders and stakeholders is that if you don't support this business, this could be what happens. But then obviously what the and you may well also go well, if you're what often happens in these sort of deals is maybe the lender goes, well, I will advance you an additional amount of facility. What will that do on the model? So then you suddenly are using this model to also help the lenders do with their planning to assess their security in future situations. So it gets very, very complicated. And sometimes the outputs of the models are used in courts because you can actually have formal restructuring events where the debt gets compromised and the outputs of the model are actually used for that.

[00:26:19] Host: Paul Barnhurst: So yeah, I can imagine where they settle on $0.30 of a dollar saying it's better than nothing. If these guys go under or, you know, this one will get 50 million, everybody else will get this, they'll restructure it all, and everybody's better off than if we just wind it down now. And you each get your $0.12 on the dollar or whatever it might be.

[00:26:39] Guest: Vaughan Grandin: Yeah. And thankfully, the models themselves don't often get, interrogated too much, because if you think about it and you spoke earlier about when I worked on an automotive rental company, if you imagine that models producing an answer. But probably the most important variable in that is going to be what is the value of all those higher cars going to be disposed of at. So there's a lot of scrutiny around the inputs into the model, as well as also whether what we'd call the relevant alternative, um, Is, uh, whatever it is in the model. So, you know, is it really going to be the entire business is going to wind down, or is maybe you're going to do a slow, controlled sale of different assets, and what's that going to do? So it gets quite fun. I mean, uh, I say fun, but I mean, last year I think we were working on one project and, uh, there was, uh, us guys and there was sort of getting calls and suddenly scenarios were being expected at like midnight on the model. So it's and then ironically, I was on holiday at the time, it wasn't planned. But the other guy in my team doing it was suddenly 12 to 4 a.m. trying to get the answers, um, to satisfy them, because, again, the stakeholders have a lot of power here, so you've got to make sure they're happy.

[00:27:56] Host: Paul Barnhurst: I'm curious. Obviously, you deal with some really complex models, as you mentioned, 800 circular references, over a thousand for one of your coworkers. How do you balance the whole idea of, you know, simplicity with building models when you know you have a high level of complexity, right? 800 circular references isn't simple. I don't care what you've done to the model. So how do you kind of balance the two?

[00:28:22] Guest: Vaughan Grandin: When it looks at entity priority models? We're very lucky that we've developed a template. And so my colleague has a template, which means that if we're going to deal with a really crazy one, particularly with cross guarantees at different levels, we will use that. Otherwise it's really about getting a methodology and a structured approach. So you know everything. You know, if we're looking at 120 entities, it's the simple stuff of making sure they're all aligned in the right places, trying to communicate, to get the expectations of what the inputs are is really important so that you know what is going to be changed and what's not going to be changed. Now, obviously, that doesn't always work out, but it's but it's good to have that sort of starting point. Um, so I think the really important thing is. Yeah, modeling, particularly in distressed situations, you need to be able to deal with the complexity, but the user is never going to look at the calcs. And we don't often share models. We share the outputs and the inputs. So actually the big thing is to make sure that the inputs are in an easy to understand fashion, and they're tailored to what they need. And the outputs are also obviously tailored. And that can be graphs, you know, tables, the usual complexity will arise. It's just making sure that no one else sees it.

[00:29:37] Host: Paul Barnhurst:While my background is in fact, I am also passionate about financial modeling. Like many financial modelers, I was self-taught. Then I discovered the Financial Modeling Institute, the organization that offers the Advanced Financial Modeler program. I am a proud holder of the AFM. Preparing for the AFM exam made me a better modeler. If you want to improve your modeling skills, I recommend the AFM program podcast listeners save 15% on the AFM program. Just use Code Podcast.

[00:30:18] Host: Paul Barnhurst: Yeah, there's a lot of truth to that. You know, I like how somebody said I was an engineer. He said, you know, when you jump in your car, you don't pop the hood first. Make sure the battery's still there and that the engine looks okay. You just press the button and assume it's going to work. They want to see the inputs and outputs. They don't need to see the sausage making. They just want to know it works and the comfort level. And you have good structure and processes, right. If you actually open it up and start bouncing back and forth, especially if you have a messy model. Clean is either even if you know it really well. But every time you go back and forth between a bunch of different pages and move all over the place, you're just losing confidence is what I've found.

[00:30:58] Guest: Vaughan Grandin: Yeah. And for the avoidance of doubt, we really do. Mostly actually, we work very hard to make sure our models are particularly obvious when we're delivering models that they're following. Best practice that there is, that holds a set of approaches. I think the one thing I will say, though, that is important, that we need to get across, and that goes back to my empathy point, and I love telling my team that a good model should be a bit like a psychologist, is that you need to be able to make it seem easy, but at the same time, you also need to give stakeholders an understanding of whatever the change they request is will actually do. With model timelines. You get guys coming in and suddenly going, oh, what happens if I securitize this asset and this entity and you're sort of going, oh, I can do that, but that's going to cost time. And so it's that balance of being able to, you know, get the job done, but also explain the implications that I think is quite important. And and that goes also to a lot of communication. Really important.

[00:31:58] Host: Paul Barnhurst: Yeah. It gets back to you mentioned earlier you talk about empathy, communication, managing the stakeholder relationships. So important. Well, I want to ask you about a model that when you and I chatted, you mentioned one of the craziest modules you ever worked on was an aircraft leasing model. Or I think you said you had a model for every single aircraft separately.

[00:32:20] Guest: Vaughan Grandin: Yeah. And it's a little bit like, I mean, it sounds like every time I'm talking to you about a model, I'm looking at every stock item one on one, be it cars or aircraft is not like that normally. But obviously the more interesting cases are and I mean, I think the aircraft leasing one is a good example of sort of sometimes what happens. And this is purely distressed. But uh, during Covid you can imagine that the value of aircraft plummeted a lot. People didn't even know when, you know, if they were ever going to fly again. Um, so you had businesses that were really highly leveraged where the value of the, the value of the aircraft or what they call them tails, the value of the tails, tales and the debt was pretty similar. Starting out, Covid came. Suddenly. The Delta dropped and I think the debt was out for about $2 billion. Like a massive number, right? So every lender advised. So suddenly there's a huge number of lawyers and consultants looking at everything. But the first question was obviously just how do you get this model built? What's the business plan for this business? What's the forecast? let's make it robust and reliable. So suddenly, uh, jumping in, looking at the aircraft, looking at their maintenance cycle. So you have these nice calculation blocks of 500 or so aircraft. Then obviously, you need to have the ability to add them and sell them and all that. But you know, your your first day. One thing is not about planning the model or you're just kind of going, we need to get something to people really quickly to give them an understanding of what's going on in the business. And that was done.

[00:33:54] Guest: Vaughan Grandin: Uh, but then what sort of happened is, is obviously there's a lot of complexity there. Um, and then the questions from the various lenders got more and more prevalent and things got longer and longer and more functionality got in. And it was one of those examples where because there are so many advisors, there were a lot of questions. And frankly, my colleague and I were working from like eight in the morning till like 10 or 11 at night for a number of weeks trying to service and update the model and what normally happens around 8: 30. We'd obviously had a very elaborate system of checks and balances in the model. So if there was a mistake, the model flash red, you could kind of stop. But by 830, suddenly the model started turning red a lot more because we're getting more tired. We're making these mistakes. At least you can see them so suddenly you're getting in this sort of situation where you're going, the models turn red, what's happened? And you're checking, checking if you've made the change or you're or your colleague has made the change. And, you know, it was, I mean, that was pretty jarring. But it was really, really intense because there was so much pressure around that. And the moral purpose from just a simple business plan model moving to you're suddenly you're doing LBO debt structuring valuation. You've got used for so many different things that, you know, the 500 aircraft was the nice part in the beginning. At the end was the ability of this model to like, handle debt stacks and the tranches and the changes in timeframes of realization and all of that. Um, so yeah, it was very, very interesting.

[00:35:25] Host: Paul Barnhurst: Yeah. And it's like you said, not not every deal. Are you modeling at a stock level? But some of the most interesting are because that's a challenge, right? You know, those aren't the easier models. Okay. I make an overall assumption for revenue or products and I move to the next line and it's fairly straightforward. Maybe you have a page building your, you know, your revenue schedule. But here, if you're in every car or every aircraft, you have a lot of sections kind of building a lot, you know, a lot of lines, a lot of schedules to manage all that.

[00:36:00] Guest: Vaughan Grandin: And a lot of data too, uh, the nice thing about it is actually, you normally have a good understanding of their maintenance schedules and their maintenance curve. So you can actually extrapolate quite a lot out. It's a little bit like a real estate model in that way. But yeah, I mean again, a model from a long time ago that sort of just just stuck in my mind. But there is something really fun, isn't it, about when you get a chance to build a model that you try to replicate the real workings of the business? I sort of call it the model universe, and this model universe that tries to replicate reality as much as possible, and you've got these various buttons you can switch. And that project was weird because we had so many different requests for changes that I ended up having like two input tabs, and I end up needing to move the inputs that were actually used onto one tab, and the other one sort of got moved onto a second one. I mean, obviously that's a nightmare. You just wouldn't normally do that sort of stuff. It's terrible practice. But again, I mean, sort of in this sort of thing, you just needed to try and get the deal out. So, you know, I think again, another model where with the benefit of hindsight, I may well have done it differently, but I completely understood every decision I made because of the pressure that was there at the time. And the question which was coming from someone to do something.

[00:37:19] Host: Paul Barnhurst: I love when you have that moment when you can look at a model, make changes and the business and everybody gets it and like, oh, this is what would happen. Or, you know, you're looking at a new product. I still remember a conversation with a boss where I gave him the new model and our margin had gone down like 5% for the business overall, even though our revenue was way up and I had to walk him through all our revenue that you're expecting ourselves for is for the new product. I've layered in all the costs, see the Cogs go up to like 80% on this product. Therefore, our margins are going down even though revenue is up. And, you know, it took me a good hour to walk them through, but it all made sense. He got it. He challenged a couple assumptions. We made a few tweaks, but he's like, no, I can really see the business in the model. And those are cool moments when, you know, everybody can kind of get it and then you can start saying, okay, what do we do about it? And have those real strategic discussions of what does this mean? What are our options versus everybody going, is the model right? Well, I don't know that I trust this, which we've all been in those situations and those are never fun.

[00:38:24] Guest: Vaughan Grandin: And you touched on a point that I think is really important around you can't just build a model you have. You have to have that understanding or you have to develop that understanding really fast, because otherwise you're not going to be able to have those sorts of conversations with the key people. And, you know, sometimes what we're dealing with is really, really specialist stuff. I mean, I was dealing with a group of companies and we were trying to model the impact of a guarantee. An additional guarantee in one of the companies. But that guarantee wasn't very high on the security net. So it was quite a it was a low priority guarantee if you like. And so a lot of creditors came in front of it. We put it in and then the return to the creditor decreased and you sort of go but why is that. Um, and, and, and you could walk it through, but you need to take a little bit of time because what it was doing was diluting the intercompany return to another company that had a much bigger guarantee, that was a higher level of security. So actually that higher level security paid less. And there's a lot around trying to explain that sort of thing and understanding it. And sometimes a model will give you an answer when you go, what's just how did it get that? And then you have to, uh, spend a little bit of time thinking about it, because knowing full well that if you don't get it, someone else is going to ask you that question. And that can be quite hard if you've been modeling flat out for a long time, and you then need to step back and look at the number.

[00:39:48] Host: Paul Barnhurst: [00:39:48-00:40:10] Host: Paul Barnhurst: I'm the FP&A guy and a passionate financial modeler. When I wanted to improve my modeling skills, I turned to the Financial Modeling Institute. Save 15% on FMI's program with code podcast at http://www.fminstitute.com/podcast.

I like to say modeling is not a math exercise. If you treat it just as a math exercise, you're going to get yourself in trouble. And I've done that before. And people are looking at it like, ah, this doesn't pass the sniff test. Oh yeah. Yeah, I should have thought about that. So I want to ask one more thing, and then we're going to move into our standard questions here. So last year you co-wrote an article about the core elements of robust financial modeling. And in the article you had six core elements. And I'll list those real quick. And then I'd just like to get your thoughts of how you came up with the list and you know your thinking on this. So you had one. Understanding business requirements to scope and design three develop the drivers based model for communicating regularly and effectively with stakeholders. Five which I loved was test, test, test you know, and then delivery kind of how did you come up with that with that list?

[00:41:05] Guest: Vaughan Grandin: I think the first thing is I worked with a colleague of mine. So obviously I've had a lot of distressed modeling experience. Um, there's a guy in my team, Steve Repetto, he's worked in just, uh, you know, a lot of big modeling before. So actually bringing our two ideas together was quite useful on this particular piece of work. And, I mean, you know, it's for me, you sort of almost can split those points into three main areas, which is sort of planning, really make sure you understand the model. And as I say, we don't always have these distressed situations. We build lots of models for clients and it's actually understanding what they want. That's really hard because most of the time I get the feeling that clients think they know what they want. They don't necessarily know what they want. So you actually have to ask lots of questions to pull out things and also unpick the complications which are absolutely inherent in there. So that's really, really important. I think understanding them, documenting them, getting the client to agree, you know, so that way you can set your parameters by which you're going to flex your model. And that's the scoping, designing, and putting it in a good document for me. Driver's based modeling is almost a non-negotiable. I think if you want to produce a credible model, you need to make sure that the real drivers that impact future business performance are understood, documented and put in the model and the user has the ability to flex them. So for me, that's really, really important. And throughout this process you've probably worked out a lot of communication. So I sort of have grouped that in the sort of development phase.

[00:42:36] Guest: Vaughan Grandin: But we're doing it from the planning to, to, to the end testing are so important and sometimes quite difficult. And it's not just you who should be doing the testing. We really believe that the clients should have a good say in understanding and making sure they're comfortable with the model before they take it on, because that point around delivery is important. When the modeling team, we make sure that if we're going to deliver a client model, we have documented it. The client has read the document, they have looked at the model. They should be testing it and comfortable with it and actually sign off and accept that model. So actually say that that is something that's, you know, that's fit for purpose that they're going to use. That's a risk point. But it also means getting them involved. So that delivery point you may think is not important, but it's really, really, really, really critical. And and you know, particularly with nowadays, um, some of the more complex formulae around there and I mean, um, and, you know, there's, there's a guy in my team who's been doing quite a lot in these Excel competitions so he can run really complex solutions and sometimes we need to use them. But then it's really important that the client needs to understand what they are and why they're there. And only we only use complex formulas if we need to for a particular reason, and then get them on board, and document it and take them through it. So it's very much working with the client, planning, delivering, making sure they're happy.

[00:44:06] Host: Paul Barnhurst: Thank you for that. I appreciate that. All right. We're going to move into our standard section. And the first question we ask every guest is this question. What is your favorite Excel shortcut?

[00:44:16] Guest: Vaughan Grandin: I like control arrows or control shift arrows. You can be very quick and make you look fast.

[00:44:22] Host: Paul Barnhurst: Yes it does. It's much quicker than when you see somebody scrolling down 5000 rows and you're just like, can I show you something, please? Because we've all seen somebody working that way, you know, there, and you're just watching it going, this is painful. It was. That's what I do. I usually try to just let him work, but there's a point where I'm like, can I just show you a thing or two? So what is the most unique kind of fun thing you've built in Excel in your personal life?

[00:44:53] Guest: Vaughan Grandin: I think my work sort of intrudes quite a lot sometimes into my personal life, and I think I'm really lucky. I've built some really fun models in my work life or looked at them. So I'd kind of say my work life. Got some cool things. I mean, I once had to build a model to assess the safety of, like whether a business should financially build, you know, a massive, massive skyscraper, which is cool. I think in my personal efforts, boring stuff. It's like spreadsheets for things. Um, I'm in a band. A couple of years ago we did a tour of Europe, which is like 21 gigs and 28 days, and there was a good spreadsheet to keep track of everything, but it wasn't very busy. But, you know, it's kind of cool to to think back on.

[00:45:35] Host: Paul Barnhurst: Yeah. I had someone who had built a model for all of his Pokemon cards. What the current value is. What future value? If he sold it at certain points, what would the DCF be? And he had built out this very complex model for every single Pokemon card he owned. And I kind of kind of, you know, laugh. So you hear a little bit of everything, which, you know, that's similar to having to do the cars and airlines and you've built some really, I could tell some cool, complex models in your, uh, in your work life. Yeah. So I imagine the band one was pretty, pretty easy in comparison.

[00:46:10] Guest: Vaughan Grandin: Yeah. But I can assure you that if you didn't find the hotel on the night where it was and the thing you were stressing. I still like the idea of DCF for Pikachu, though. Um. That is. That's pretty cool. That guy was built like. Yeah, Pokemon DCF. That's funky.

[00:46:27] Host: Paul Barnhurst: So there you go. I've heard them all. I had a bathroom tracker, all kinds of different things. Someone who tracked how many swear words were in a TV show. He built an API and it was taking the text and counting. How many? Because he wondered. Because it swore so much. He didn't want to actually count it because he'd never heard a show swear that much. So he set it up through Excel like the things people do. But yeah, so I've had some fun ones. So. All right, we're going to move into rapid fire. So how this works is you don't get to say it depends, but you can elaborate on a couple answers at the end because, you know, like you could say it depends on all of these. So pick a side, you know, kind of based on your experience and where you feel a yes or a no. If there's one you really don't want to answer, you get one mulligan. We've started adding that so you can just say, I'll pass or plead the fifth or whatever you want to call it, and that's how it's going to go. So we'll quickly run through them. And then you can elaborate on a couple if you want circular references. Yes or no.

[00:47:24] Guest: Vaughan Grandin: It has a place mainly no, but obviously I've spoken about them a lot.

[00:47:28] Host: Paul Barnhurst: Yeah, exactly. Vba yes or no.

[00:47:30] Guest: Vaughan Grandin: Useful, but not necessary.

[00:47:32] Host: Paul Barnhurst: History. Do you prefer a horizontal model or a vertical? So you like, you know, lots of sheets or kind of stack it all in one sheet if you can.

[00:47:40] Guest: Vaughan Grandin: I'm definitely one who likes lots of sheets, particularly nowadays, because you can stack things and put them all together if you really need to. But I don't like millions of sheets. But definitely I just don't like scrolling down. It's a personal thing though. It's a personal thing.

[00:47:54] Host: Paul Barnhurst: It is. Dynamic arrays in models. Yes or no.

[00:47:58] Guest: Vaughan Grandin: Man. They're awesome. Like, it's very much a yes, but you need to use them properly.

[00:48:03] Host: Paul Barnhurst: And so here's my next question with that. Do you think we should be building fully dynamic models where you're trying to do dynamic arrays through the entire model with everything? No.

[00:48:14] Guest: Vaughan Grandin: But I think it's a bit like AI. You go, can I do anything? And you go, no. Could it do it in the future? Maybe. I think that it's you need a really high skill level to pull off a full dynamic array model. And I don't think it's needed, but I'm. I'm not ruling it out. I think people should be open minded about these things.

[00:48:34] Host: Paul Barnhurst: Yeah. I have a similar philosophy as you. External work, book links? Yes or no?

[00:48:38] Guest: Vaughan Grandin: It was great and like in the 2000. But now. No. Definitely not. No no no no no no no.

[00:48:44] Host: Paul Barnhurst: Named ranges. Yes or no.

[00:48:46] Guest: Vaughan Grandin: Yeah. Particularly because you can make them dynamic now. It's a game changer. Dynamic name ranges of the bomb.

[00:48:53] Host: Paul Barnhurst: How about formal standards? Do you follow a formal standards board like Fast or Smart or any of those in your modeling?

[00:48:59] Guest: Vaughan Grandin: Yeah. The is as a modeling team, we've developed our own, which basically sort of takes them, but yes, very much so.

[00:49:06] Host: Paul Barnhurst: Do you think financial modelers should learn Python in Excel?

[00:49:10] Guest: Vaughan Grandin: It's well, should is a strong word. I don't think they have to.

[00:49:14] Host: Paul Barnhurst: Okay. What about Power Query?

[00:49:17] Guest: Vaughan Grandin: More useful. So yes.

[00:49:18] Host: Paul Barnhurst: What about power BI.

[00:49:20] Guest: Vaughan Grandin: Is very useful. But you can also create dashboards in Excel. So I'm going to go, not necessary but it's a nice to have.

[00:49:28] Host: Paul Barnhurst: How about this? This is always a fun one to see what people say will excel. Ever die?

[00:49:32] Guest: Vaughan Grandin: Nah. Well, I'm going to go and say not in my lifetime, but not in our lifetimes, for sure. I mean.

[00:49:38] Host: Paul Barnhurst: Yeah, I get that answer from time to time. And my favorite is one person who said yes, just I'm praying it's not in my lifetime. So do you think AI will build the models for us in the future?

[00:49:52] Guest: Vaughan Grandin: Probably about the same time that we're going to send spacemen to, like, far off galaxies kind of thing. Don't think very soon in the immediate future, mainly because you've got to ask people questions to understand what's going on. So now I'm, I'm, I'm more on the people's side on this.

[00:50:07] Host: Paul Barnhurst: Do you believe financial models are the number one corporate decision making tool?

[00:50:13] Guest: Vaughan Grandin: I'll put the number two to just the simple concept of a business meeting. Bringing people together and talking about things to me is the number one way to get proper decisions done about communication. But it's obviously very hard.

[00:50:27] Host: Paul Barnhurst: Okay. Makes sense. What's your lookup function of choice? What's the lookup function you like to use the most?

[00:50:33] Guest: Vaughan Grandin: Oh, filter love filters and I can put a unique in front of them. Awesome. A count on a filter that.

[00:50:39] Host: Paul Barnhurst: You and Mark Proctor. That's his favorite as well.

[00:50:42] Guest: Vaughan Grandin: It's very intuitive. I think that's the thing is, you can explain an xlookup to someone and there's commas with a filter. You can go, this is what you want to pull. This is the question you want to answer. And you can have multiple questions. Um, I really like that ability. It's a bit more intuitive to someone new to lookup functions.

[00:51:02] Host: Paul Barnhurst: See, I actually I feel like which is funny when I train, I like Xlookup is a little easier for most people that I'm training, and that's probably because, you know, I a lot of people I work with don't understand boolean logic. And so they kind of get confused about how they have to write the formula. But if you understand the basics of boolean filters, they are very, very straightforward.

[00:51:24] Guest: Vaughan Grandin: I think, I mean, we start with Xlookup as the, as the thing that we teach most people, but we'll always try and just teach a filter also, because some people just work to it better. In my mind it makes a lot more sense. But absolutely, if I was going to teach it Xlookup first.

[00:51:39] Host: Paul Barnhurst: Okay. Yeah. Just curious kind of your take because that's the one thing I run into. But yeah, I much prefer teaching Xlookup over Vlookup. Like it makes so much.

[00:51:50] Guest: Vaughan Grandin: More on Vlookup though. That's the question. Did you start your career with Vlookup?

[00:51:54] Host: Paul Barnhurst: I did, I started with Vlookup, then index match.

[00:51:57] Guest: Vaughan Grandin: See the Vlookup that obviously it's the comma thing and then putting false on at the end. But you know when I remember I was in audit in South Africa a very long time ago and I was like, this was uncovering some secret that people just couldn't do. And that was kind of cool. And that probably got me into everything I never liked. I'd use index matches and I have a purpose, but like, I kind of yeah, I was like Vlookup and then Xlookup. I mean, sometimes I might drop a Vlookup in a formula just for, um, just for nostalgia purposes and to annoy members of my team who wore lookup things. But you've got to start somewhere, right? It's like your gateway. It'll always be there.

[00:52:38] Host: Paul Barnhurst: The lookup functions, you know, whether you start with Vlookup Xlookup, it really is kind of that gateway to becoming more advanced in Excel, because it's when you realize lookups can take something that would take you hours to do manually and reduce it to seconds.

[00:52:53] Guest: Vaughan Grandin: Absolutely.

[00:52:54] Host: Paul Barnhurst: And that's just a game changer, when all of a sudden you realize that savings that can happen and then you're like, what else should I be doing that I was doing manually before? Am I wasting hours? And you start digging in and all of a sudden kind of opens up that world to excel for a lot of people, is what I've found is that just learning the basic lookup, whichever one it is, if it's Vlookup, xlookup, index, match, filter, whatever choose, they really just kind of open a world for you.

[00:53:21] Guest: Vaughan Grandin: Obviously they're joining things together, but they bring pieces of data together to tell a story. And I think particularly in the world of dealing with companies that might be in distress, you need to bring pieces of information together to support an evidence, a story, you know. And so that important thing of taking people on the journey and bringing that information together, xlookup is great, but I'm still a filter guy first and foremost.

[00:53:45] Host: Paul Barnhurst: That's all right. We'll let you have your filter. We all have our formulas that we love. Well, you know, Vaughn, I think we're right at times here, but I want to just take a minute and thank you so much for joining me. I really enjoyed the conversation. It's been a lot of fun. I think our audience will enjoy it. I know you get to go do some singing with your bandmates, so enjoy that this weekend. And thanks again for just carving out an hour of your time to chat. I really appreciate it.

[00:54:13] Guest: Vaughan Grandin: No thank you. I mean, I think what you're doing with these podcasts is absolutely fantastic to make, to turn a community about something that is your job and make it a little bit more passionate. It's fantastic and it's great what you're doing, and I'm very honored to be here.

[00:54:27] Host: Paul Barnhurst: Well thank you. I'm lucky to get to interview you and so many others that do the hard work in the community. And so thank you for sharing your knowledge and you have a great rest of your day. So thank you, Vaughn.

[00:54:38] Guest: Vaughan Grandin: Fantastic. Thank you very much for your time. Cheers.

[00:54:40] Host: Paul Barnhurst: 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 at www.FMInstitute.com/podcast and use Code Podcast to save 15% when you enroll in one of the accreditations today.