In this episode of FinPod, we discuss how to get started with building a financial model. We address common issues beginners face – like too quickly relying on Excel –  and provide tips to streamline the model-building process. Emphasizing the importance of design, we cover the tangible benefits of reverse-engineering financial modeling problems and working backward from the outputs.

Get advice on how to create efficient, effective models. Tune in to get inspired when starting a financial model!

Transcript

Ryan Spendeow (00:13)
Hello everybody, my name is Ryan Spendelow I’m a Senior Vice President here at CFI. Now, we hear that a lot of people often have problems and issues when they first start building financial models. It might be their first financial model and they’re just a bit unsure how to start or what the best way to go about that is. They might have built some financial models and they’re just having problems with the complexity

of their models as they dive deeper into financial model building. So I’m here today with CFI Financial Model Building Guru Duncan McKeen, a fellow subject matter expert, to have a bit of a chat and to give some tips and some pointers to hopefully help people complete that task of building financial models a little bit easier. So, hi Duncan, I’m so glad that you’re here on this session today because you really are.

Quite an expert at this, aren’t you?

Duncan McKeen (01:11)
Thanks, Ryan. It’s I feel a little bit shy. You saying that, but yes, I have been building financial models for a long time and I do love it. And so, yeah, I think we can help people for sure. Often. I think people don’t often don’t know where to start sometimes. And one of the things, one of the places you can start is, is you really, you got to remember models are decision-making tools. So great starting place can be, well, what important decision are we trying to get?

Ryan Spendeow (01:16)
Not like you Duncan.

Duncan McKeen (01:40)
The model to help us with, right? Like, what do we want it to do at the end of the day? Right? Do we want, is it like an FP&A model where we want it to help us track what’s going on on a month to month basis versus our budget? Or are we thinking about an acquisition and we want to go value a company to figure out, you know, what that company is worth and maybe what we should offer as an acquisition price, for example. So you need to figure out first of all, what it’s, what it’s set out to do.

What important decision is it gonna help you try to make? First of all, that can be a good starting place for sure.

Ryan Spendeow (02:15)
Yeah, that makes sense. And there’s so many different types of financial models out there to help with all the different decisions that analysts have to make in the financial markets and in finance. So understanding that question or being able to answer that first question sounds like a really, really good place to start. So once you know the decision that you’re looking for help on, what’s next Duncan?

Duncan McKeen (02:32)
Mm-hmm. Yeah, definitely.

Yeah, well, tell you what’s not next. Excel is not next. People have this habit of like, they get really excited about the model or maybe they’re anxious and nervous and they’re like, my gosh, I need to open up Excel and start doing something. But really, you want to spend a lot of time designing it. Like Excel is for when you start building it. I wouldn’t really be designing in Excel. I usually would design on paper first. It’s so much faster.

Ryan Spendeow (02:44)
Really?

Duncan McKeen (03:08)
And the other thing about, so stay out of Excel. Okay. And, it is counterintuitive. Yeah. There’s going to be another counterintuitive piece in a moment, but it’s like, well, imagine that, imagine that you wanted to build a house and, and you had, you just, there was some vacant land, right? Your first step would not be to go to Home Depot and buy some hammer and nails and start hammering them together. Right. That’d be crazy. You would probably start by like,

Ryan Spendeow (03:12)
Bit counter-intuitive, but yep.

That’s a good point.

Duncan McKeen (03:37)
looking at pictures in a magazine, trying to envision what you’re going to build. You might involve, I guess, an architect to help you with the design. But you would always start and do so much designing upfront before you actually broke ground. And models are the same. So you need to spend a lot of time designing. With the design, the first counterintuitive piece is stay out of Excel. And then the next counterintuitive piece is you’ve got to design

backwards. Okay, so you don’t want to start with the inputs. You want to start with the output. So, we started off with that question. So what important decision are we trying to make? Right? So, well, what outputs do we want to see that will help us make that decision? So imagine we’re trying to decide whether or not we should acquire a company or what we should offer to acquire a company. Well, what output would we want to see? We’d want to see

the value of the company, right? The enterprise value, the equity value. So that might be our output. And now we start back solving. Okay, well, what do we need to get that enterprise value? Well, we probably, if we’re using a DCF, we would need unlevered cash flows. Well, what do we need to get unlevered cash flows? Well, we would need most of an income statement, a lot of a cash flow statement. And then what do we need to get those? Well, we need a revenue schedule.

We need a cost schedule. You keep backwards solving all the way through, and eventually you’re gonna get back to the inputs. And then by doing it that way, you’re only going to include the schedules which are absolutely necessary, and you’re only gonna include the inputs which are absolutely necessary. And that’s critical. A lot of people, if they do design, they often start with the inputs.

They’ll often say, what inputs do I have available? Okay, I have hundreds of inputs. Great, let’s put them all into Excel, right? And that’s not a great idea because a lot of them aren’t needed. And you’re introducing much too much complexity into the model. And then all of a sudden they’re all in Excel. So then you feel like you have to use all of them. And if you follow through that kind of a process, you’re gonna end up with this model that’s like so complex

Ryan Spendeow (05:31)
You

Duncan McKeen (05:59)
that you might even confuse yourself in it. So, yeah, well, I’ll just make another comment about that. Actually, this is a really common problem. I’m sure you’ve seen this too, Ryan, is like, the world is awash in data right now, right? Decades ago, it used to be that it was hard to find data. Well, now we’ve got the opposite problem. Like we’ve got too much. And so a lot of the times when, let’s say you’re, you’re, you know, using this example, we’re building a model because we want to know what a company’s worth.

Ryan Spendeow (06:07)
Mm.

Yeah, for sure.

Right.

Duncan McKeen (06:28)
You have access to so much data for inputs on that company. You don’t want to include all of it. You’ll end up with an overly complex model. This is why if we start with the outputs and back solve all the way through, we only get to the group of inputs that we absolutely need. And then you can focus on those and just build those into the model. A lot of people think just because they have the data, it must go in the model. That’s not true. In fact,

Ryan Spendeow (06:28)
Mm-hmm.

Duncan McKeen (06:58)
Some of the best models in the world are really, really simple. Super simple. Just because it’s simple doesn’t mean it’s good. It doesn’t mean it’s bad. In fact, simple can be great. Yeah.

Ryan Spendeow (07:02)
Yeah. All right.

No.

Yeah, a simple solution is always going to be preferred over a more complex solution if it achieves the same goal, particularly from a model auditing point of view, understanding what’s going on in your model, interpreting the output of your model. The simpler we can make it, but still getting the job done. I guess that’s better. And that’s where that planning and design phase becomes so critical, doesn’t it?

Duncan McKeen (07:27)
Yep.

It really, it really, really does. Like start your design on paper, stay on paper for hours, I would say, until you have a clear idea of, you know, what output you’re trying to get to, and then what schedules you need to get to that output. And you only want to go into Excel after you know everything that you need to build. And then once you’re in Excel, it just becomes a sort of a robotic mechanical build.

Ryan Spendeow (07:37)
Wow.

Duncan McKeen (08:00)
process. You don’t want to be in there designing, contemplating the design. You want to have that locked down before you get in there. Yeah, so yeah, definitely tons of design up front. You might think, okay, so learners that are listening might think, I don’t want to waste all this time on the design. I’m in a rush. I got to start building. You, the more time you spend on design, you will save back all that time and more

with the build, because the build would be so much faster and you’ll end up with a better model in the process.

Ryan Spendeow (08:34)
I really like the house analogy Duncan, how if you wanted to build a house, you just wouldn’t go down to a Home Depot or a Homebase depending on what country you’re building your house in. You wouldn’t rush out and buy all the stuff without putting your thoughts down on paper and designing it thoroughly. So that analogy actually really resonates with me. I think that’s a really, really great point that you make.

Duncan McKeen (08:37)
Mm.

Ryan Spendeow (09:00)
Thinking about that design phase, just in your experience, because you’ve built so many financial models in your time, what do you think on average, and I know it might vary from model to model, but what’s that kind of split between the design phase of the financial model building process and the actual getting into Excel part of the model building process? Is it split fairly evenly, 50 -50? Is it leaning more to one than the other?

Duncan McKeen (09:22)
Mm.

Mmm.

Ryan Spendeow (09:29)
Does it depend on the model? Are there some models that you may have to spend more time designing than others, for example?

Duncan McKeen (09:38)
That’s a great question. I’ve actually never thought about that split, but I would think that the design portion would be anywhere from 5%, maybe up to, could be 20% of the total time. And the rest would be the build. I think the build always takes longer because it’s, you know, there’s formulas to put in. You’ve got to be mindful of formatting and structure and like layout to make sure it’s clear.

So definitely think the build always takes longer. But the design, I think, can be up to maybe 20%. I think it can be really quick. Design can be really quick if it’s straightforward. Like if we’re just talking about, we need to value this company, and we’re going to do a DCF. Well, it’s like, OK, well, I would know that design right away. Maybe even the learners could get there pretty quickly. But if it’s a little bit more obscure, then you’re going to need to think about the design a bit more.

Ryan Spendeow (10:15)
Well, so.

Yeah.

Duncan McKeen (10:38)
But it’s, yeah, it saves so much time designing upfront. Some of the worst models that I’ve ever seen were so bad because the design was skipped. So it’s kind of like, okay, I need this thing. yeah. Darn it. Yeah. I almost forgot. I need this other thing too. And then you tack that on and it looks like let’s use a house analogy. It looks like this little house with eight renovations that are done and stuck onto the sides of it. Right. It wasn’t thought through from the beginning.

Ryan Spendeow (11:03)
Yeah. Yeah, yeah. Yeah.

Duncan McKeen (11:07)
The other thing I guess that I’ll say is sometimes, sometimes a great way to, when you’re designing, can be to start super, super simple. So we were talking earlier about a DCF model and we’re saying, okay, well, we need to discount cash flows. So you could do that by only having like an unlevered free cash flow schedule. That’s it. So you could say like, what are, what’s my EBITDA today?

And you could simply just grow it at a certain rate. You could put in an estimate for like capex, a quick estimate for current taxes. You could literally build one schedule and then DCF it and have your answer. So that might be a great place to start. And then you just say to yourself, right, okay, this is obviously too simple. So where do I need to add some granularity? Okay, well, I need to add some granularity around.

EBITDA. Well, how are they generating it? So maybe I need to break that now into revenue cost and SG. Maybe I could keep the SG simple, but I need to explode the revenue out into a proper schedule to show price and volume. And you can kind of start uber simple and then only just open it up, open up the granularity in the places where you absolutely need it. And then, and then by following that process, you’re never going to get too complex or you’re only going to get complex where you need to, I’d say.

Ryan Spendeow (12:32)
Yep. And that kind of level of complexity, I guess, would become clear through that model design process, right? And again, underpins and reinforces why the model design part of financial model building is so critical.

Duncan McKeen (12:51)
Yep. Yeah, it certainly is. When I was doing a lot of financial model consulting, I’d often, going back to this idea of starting with the outputs, I’d often like, you know, give the client a piece of paper and say like, okay, in a perfect world, what would you want this model to show you? Sometimes they draw a graph or a chart, or sometimes they just put line items like, well, I need to see enterprise value. I need to see EBITDA going out, you know, for 20 years or whatever it is. That can be really helpful because you just give them a blank canvas and…

say it can be anything, what do you want it to show you at the end of the day? Yeah.

Ryan Spendeow (13:26)
Hey Duncan, you’ve given us some fantastic tips and let’s see if I can remember as many of them as I can. If I forget any, see if you you catch me out here. So think about our financial models as decision-making and what decision is it that we need to answer. Start with the output. So following on from decision, what decision is it and what outputs do you need? Then work backwards from those outputs to figure out what inputs that you need.

Duncan McKeen (13:42)
Yep.

Ryan Spendeow (13:55)
Don’t start in Excel. Pen and paper would, don’t skip the design part, right? And maybe in that design part, focus on what a simple model might look like and then determine where you need to build complexity into that model in order to get the decision that you need in the output. Is that an accurate summary?

Duncan McKeen (13:59)
Yeah.

Yep. I think, yeah, no, I think, I think you’ve got it. You’ve got it right there. Yeah. Yeah. Definitely. And what you mentioned near the end, there’s, are so important. Start really, really simple and only make it complex where you need to. Yeah. Yeah. And always go backwards. For sure.

Ryan Spendeow (14:31)
Awesome. Not a time to try to show off to your peers to see how complex you can make the model. Because I remember, I know that in previous lives, I’d get a financial model for someone and I feel like that model is really just a chance for someone to show off, see how big a formula they can put in one cell. And frustrating, eh?

Duncan McKeen (14:52)
Gosh, I know. It’s job protection. They think that like, they can’t be fired if they’re the only ones that understand the model. You know, it’s like, well, guess what, then you can’t get promoted either if you need to run that model for the rest of your life. I remember sending my models when I was working in equity research, I’d have portfolio managers ask for copies of my models early on. I’d send the model. I’d never hear from them again because my models

Ryan Spendeow (15:02)
Yeah.

Exactly.

Duncan McKeen (15:19)
were too complex. They’d open them and they couldn’t understand them and they’d be like, okay, we’ll move on to the next person. Thanks very much. So.

Ryan Spendeow (15:26)
That’s funny. That’s a lovely little story there. Hey Duncan, thanks ever so much for imparting some of your vast amount of experience in how to build a financial model. I’m sure people listening to the session will take a lot out of it. And hopefully, we can have you back on another session in the future to talk about another element of how to build a financial model.

Duncan McKeen (15:49)
I’d love that. Thanks so much, Ryan. Appreciate it.

Ryan Spendeow (15:51)
Awesome. All right. Thanks very much and thanks everybody for listening to the session. I hope to see you in another CFI video a bit later on. Take care. Bye bye.