In this episode of FinPod, host Asim Khan is joined by Duncan McKeen, CFA, FMVA, to discuss FP&A model design, revenue forecasting, and the power of proper formatting. Duncan shares insights on creating visually appealing and easy-to-follow models using the latest Excel features, such as binary switches and conditional formatting.

Discover how to enhance your models with up and down arrows to highlight changes and leverage binary switches for efficient data management. Learn how these techniques contribute to telling the story behind the numbers, helping you make informed financial decisions. Elevate your FP&A revenue forecasting skills with this quick, high-impact conversation.

Transcript

Asim (00:12)
Welcome to the What’s New at CFI podcast. I’m Asim Khan, subject matter expert and instructor. I’m joined by my colleague, Duncan McKeen, who is also a subject matter expert and instructor. Welcome, Duncan.

Duncan McKeen, CFA, FMVA (00:24)
Thank you, welcome.

Asim (00:25)
So Duncan, you’ve been hard at work delivering courses on financial planning and analysis for learners.

Duncan McKeen, CFA, FMVA (00:31)
Absolutely. Yeah, we’ve been busy recording for many, many weeks now. Excited to see them go live so that the learners can get in there and start experiencing them.

Asim (00:39)
Right. Last week we spoke about the first installment of this course, which is the model design course. And then recently you completed the course on formatting and revenue forecasting. Is that right?

Duncan McKeen, CFA, FMVA (00:51)
Absolutely. Yeah, and we really the focus on the course is revenue forecasting, but the reason that there’s a decent amount of formatting in there is because it is the second course in the series, and we wanted to make sure that everybody that was watching had really good knowledge of how formatting works in Excel and in terms of custom number formats and also there’s some work in there on conditional formats as well.

Because we just find that it’s, models are not just about numbers. It’s really important that they’re properly formatted so that they’re easy to read and easy to follow. And we find that poorly formatted models can be very, very overwhelming and sometimes just difficult to glean the information that you need from the model.

Asim (01:33)
No, I believe you’ve done a great job. I thought it was a, the formatting bit of it was a master class in formatting. Yeah, it really was. And I learned a thing or two, especially with the check boxes and the up and down arrows. The model actually tells a story. You could look at it and without delving too deeply into the numbers, see what’s going on. Kind of a big picture idea.

Duncan McKeen, CFA, FMVA (01:38)
Oh, thanks, Asim

That’s good. That’s great.

Mm-hmm. Yeah, thank you.

Asim (01:54)
And it gives you a big picture idea just by looking at it.

Duncan McKeen, CFA, FMVA (01:56)
It certainly does. And some of the features that are in that course are actually fairly new in Excel. The checkboxes have not been around too long. And one of the things that we did was we put in checkboxes, but underneath them, they’re actually binary switches, which sounds like a mouthful. But they’re simply ones and zeros. And the reason that we use binary switches, essentially a one or a zero in a model

is because they help us keep the formulas so simple. And then instead of using an if function to see whether or not something is turned on or off, you literally just multiply through by the one or the zero. And of course, if it’s a one, you’re multiplying by one and then the number comes out as itself. And if you’re multiplying by zero, it effectively zeros that line item out. So it’s a great way to turn certain features on and off in a model.

And the up and down arrows that we used came out at the same time as checkboxes. And they’re super helpful because if you, if you’re forecasting something like, let’s say you’re forecasting a salary across one row and let’s say that salary is $70,000, the moment you change it, say up to 75,000, you can put in a little up arrow indicating that there’s been a change so that then your eye goes straight to that.

That change, which can be really, really helpful. Just, I think you mentioned the idea that you can look at the model and understand what story it’s telling. It really helps you to see where that change has happened.

Asim (03:19)
Yeah, it absolutely does the trick. And I like the bit about the binary switches because if statements, as you know, can get unruly, unless you find some sort of elegant way around them, and the binary switches in this case do just that.

Duncan McKeen, CFA, FMVA (03:31)
Yeah, definitely. I agree with you that they can become unruly. And I found, I found over years and years of using Excel that the good old if function is probably the most overused function in the software, because people tend to, you see it used heavily, often when people don’t know about all the other amazing functions that are in the software, when you have a really wide vocabulary of the functions, you tend to use other ones, and only revert to an if statement if absolutely necessary.

Asim (03:55)
Right. Okay. And we can now expect some more courses from you now. What’s out now? We’ve got this revenue forecasting bit and headcount forecasting coming up.

Duncan McKeen, CFA, FMVA (04:01)
Revenue forecasting and the next one after that is headcount forecasting and analysis. So in this course, we’re talking about here the revenue forecasting. We’re really looking at the top line for the company. And then we start getting into the cost of goods sold where we’re forecasting their own employees. And then after that one, we look at also cost of goods sold, but forecasting external contractors.

Asim (04:21)
Excellent, exciting stuff and we’ll have podcasts coming up where we discuss those upcoming courses as well. Duncan, thank you so much for your time.

Duncan McKeen, CFA, FMVA (04:28)
Absolutely. Thanks, Asim