Transcript

Asim (00:12)
Hello and elcome to the What’s New at CFI podcast. My name is Asim Khan. I’m a subject matter expert and instructor at CFI. I’m with my colleague Duncan McKeen, who is also subject matter expert and instructor. He’s the brains behind our financial planning analysis course. Duncan, welcome.

Duncan McKeen, CFA, FMVA (00:29)
Thanks, Asim Delighted to be here. And thanks for referring to me as the brains behind the course, like the nicest thing someone said to me all day.

Asim (00:36)
Well, it certainly isn’t me. This session, why don’t we talk about what you’ve done in terms of modeling debt and CAPEX, capital expenditures, in the FP&A model.

Duncan McKeen, CFA, FMVA (00:46)
Definitely, yeah. I have to say the capital expenditure schedule in the course is relatively straightforward. It’s really the debt schedule where we focus a lot of the time and attention to making sure that everyone taking the course understands how to model debt correctly. There’s definitely a few challenges with modeling debt. One of the things in particular that you definitely wanna make sure that you do is.

always in a financial model, you always want to set up a lender of last resort. And often that is a line of credit or a revolver so that if the model needs money, it can go to that revolver if it’s set up as the lender of last resort. So we definitely talking about that. And we also talk about the idea of, even though in the real world, there’ll be, there’ll always be limits on revolvers or revolving lines of credit.

You don’t want to limit, you don’t want to put that limit as a hard limit into your financial model. Because then if you, if the world got hit with another global pandemic and you put really sort of worst case scenario assumptions into the model and the model needed cash and it couldn’t get that cash from the revolver because of the limit, you’d have a situation where the cash balance would flip to negative and

you would know from working at Wall Street that you never wanna show a balance sheet with a negative cash balance on it.

Asim (02:10)
That would be a bad fact. You would get involuntarily petitioned into bankruptcy when you could have easily been saved by an increased credit limit. So what we’re talking about is not term debt. It’s not a loan that’s due in five years. It’s a revolving line of credit, which operates much like a credit card or charge card, right? If you need it, cash falls short, you can dip into it. It’s your first source of liquidity.

Duncan McKeen, CFA, FMVA (02:14)
Yes.

Mm-hmm.

Asim (02:36)
for large expenditures.

Duncan McKeen, CFA, FMVA (02:36)
Yeah, and it’s definitely not a very large piece of debt, but a really tricky one to model. Because as you just mentioned, it needs to have that flexibility of the company drawing on it only when it’s needed. And the analogy to a credit card is a good one. I guess the only difference being that the revolvers would usually have a cash sweep mechanism, which would mean that the…

the company would have to pay down the revolver as soon as it was able to. A credit card company would be very happy for you to leave an outstanding balance month over month and only make minimum payments there.

Asim (03:07)
Right.

All right. So extending that credit card analogy, so this is more like Amex rather than Visa or MasterCard. It’s meant to be paid off, right?

Duncan McKeen, CFA, FMVA (03:21)
Mm-hmm. That’s a fair. Yeah, that’s the first thing. Meant, yeah, meant to be meant to be paid off. And in fact, that’s often a term of a line of credit would be that there’s with a cash sweep mechanism, the moment there’s excess free cash flow available would need to be swept and paid down to zero.

Asim (03:39)
And I suppose the benefit of not putting in a hard cap for the revolver as your modeling is, as you model, as you roll forward the years, as you stress the cash flows, you get an idea of how much you could possibly need in the future. It’s a way of, if you had to size the revolver, right, leaving the limit aside. Yeah, go ahead.

Duncan McKeen, CFA, FMVA (03:56)
It’s a great, yeah. Oh yeah, yeah. You mentioned sizing the revolver. That’s a really great point because it can definitely, as you’re flexing the model, it can definitely help you size the revolver. And you might know, for instance, a company that has a seasonality to its business, there may be certain times of the year when it really needs to draw very, very close to that limit or even above that limit. And…

and then they could start the conversations earlier with the lenders to see about extending the limit so that they wouldn’t be going over and above the revolver limit.

Asim (04:33)
And in modeling the revolver, we’ve, or you have made one more terrific example of how we stay far away from if statements if we possibly can. So how do you avert those if statements?

Duncan McKeen, CFA, FMVA (04:46)
Mm, it’s

in the course and in fact there’s a part of everybody’s brain that tells them whenever they encounter something like that use an if statement, use an if statement. You have to learn how to override that part of your brain and use other formulas that are often better than if statements.

Asim (05:02)
Now, fair point. And following the debt and CAPEX portion of the FP&A model, the next one would be aggregation, right? How you aggregate financials. That’s great.

Duncan McKeen, CFA, FMVA (05:14)
That’s true. And that’s, yeah, that’s the next course in the series. That’s kind of an exciting one. Well, this one’s exciting because there’s a lot of great things to learn about forecasting debt. But the next one’s fun because we really, we pull everything and aggregate everything together into, into a monthly income statement and a monthly cashflow statement. But then we also show how to aggregate figures on one of the other tabs up into quarters and up into years, which is worth

talking about on that one on a separate podcast, because it’s something that people struggle with. And we often see suboptimal structures in these types of models. And I’m sure you’ve seen it before as well, where you’re modeling out, say, 12 months, and then you sum to a year, or you model three months and then sum to a quarter. And that can lead to a lot of issues, because your formulas are changing for the months, and then they change for the quarter. And it’s easier and better just to model

months all the way and have a different tab where you aggregate those up to quarters into years. Better structure. Easier to copy left to right across the sheets.

Asim (06:20)
And the debt model is also connected to the last bit of that financial aggregation model, right, where it rolls down to cash or is influenced by cash somehow.

Duncan McKeen, CFA, FMVA (06:27)
It certainly is.

Yes, it certainly does. Yeah, everything that we’re modeling here in this course in terms of debt is gonna flow into the income statement and the cashflow statement as well. Actually, and that’s an interesting point because part of the challenge with this course here that we’re talking about is that there are parts of the debt schedule that connect to the cashflow statement, which isn’t done till the next course. So part of the thing that makes this tricky as well is that the model doesn’t match the completed PDF print, if you will.

So it’s another thing. It’s the first time we’ve encountered it in this series. And it’s a little bit, it’s a little bit unnerving, but it’s more true to real life. Because as you would know, you never know what the solution is going to look like when you’re building a model for real outside of a course.

Asim (07:15)
Absolutely. So I guess we just say this as a word of, you know, to alert our users, if you’re going through the model as we’re rolling it out and you get to this portion of it, we’re dealing with that in CapEx, and your numbers don’t seem to exactly add up or foot. It’s not you. It’s, there’s more coming. Yeah.

Duncan McKeen, CFA, FMVA (07:36)
Absolutely. Yeah. And we mentioned that in the course and flag it so people aren’t left thinking that they’ve done something wrong in the course.

Asim (07:44)
Good. Okay, well I hope to have fun with this part of it. I certainly did in watching it and reviewing it and going through the exercises. Duncan, thank you so much. We’ll see you next time. Okay.

Duncan McKeen, CFA, FMVA (07:54)
Thanks, system.