Go Back

What’s New at CFI: Mastering Financial Planning & Analysis: Insights from a CFI Expert

April 29, 2024 / 12:00 / E1

In this episode of FinPod, host Asim Khan is joined by Duncan McKeen, CFA, FMVA, to discuss Financial Planning & Analysis (FP&A). Duncan shares insights into the importance of model design in finance, emphasizing the significance of careful planning and structuring for effective decision-making. Learn how to efficiently gather inputs using modern tools like Power Query and how keyboard shortcuts can significantly improve productivity.

Duncan and Asim discuss practical tips such as designing models backward, starting with the dashboard, and working in reverse to ensure a seamless flow.

Tune in for expert advice on mastering financial modeling and how the FP&A courses can help you excel in your finance career.



Transcript

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

Duncan McKeen, CFA, FMVA (00:22)
Thanks, Asim. And yes, delighted to be here and delighted to be talking about our new FP&A courses.

Asim (00:27)
I understand you’ve been quite busy putting out a series of FP&A courses covering a number of subject areas. Could you give us a rundown, Duncan?

Duncan McKeen, CFA, FMVA (00:35)
Yeah, absolutely. Yeah, we’ve been quite busy. This started off in earnest last fall. It always starts with the model build process, which is sort of the foundational exercise that needs to happen before a financial modeling course can get underway. And then over the last few months, it’s been busy recording those on top of the model for the FP&A courses. And it’s an entire series at this moment. There’s three of them out that are live on our learning management system.

or LMS, but there’s another four coming out and then even maybe another two after that coming out as well.

Asim (01:12)
And that covers things from revenue forecasting to cost forecasting. Right. And what else?

Duncan McKeen, CFA, FMVA (01:17)
Yeah, it certainly does. Yeah, we start off on the first one really with looking at sort of high-level model design and how you’d want to go about designing an FP&A model. We go through a lot of formatting, custom formatting, conditional formatting in that first course as well. And we also talk a lot about the keyboard because it’s not just about learning.

The model itself, it’s about, you know, how can we be more efficient when we’re, when you’re in and out of financial models all the time. And, and so a big thing that we’d like to talk about at CFI and really encourage the learners to do is to break that addiction that they might have to the mouse and start using the keyboard just to boost their efficiency way up. So yeah, we, we always go through, we always use the keyboard and encourage the everyone watching to learn, use the keyboard as well when we’re going through our courses.

Asim (02:10)
Right, that is a best practice sort of thing, right? So the model design module is what is a prelude to the entire set of series, the FP and analytics that you’re doing.

FP&A is tricky and this is why model design is important, right? Can you tell us a bit about that?

Duncan McKeen, CFA, FMVA (02:27)
Definitely. Yeah. And model design is a funny thing. The first thing about model design is that most people just skip it. We think about if you were going to build a house and you had a piece of property and you’re going to build something on a cottage or a house. Well, the first thing you would do would not be to go to the hardware store and buy a bunch of lumber and nails and start hammering them together. The first thing you would do is

What do I want it to look like? You might start ripping pictures out of a magazine or sketching things up, and you would start by designing. But yet when people build models, they often just jump right into Excel and start hammering in formulas and numbers and stuff like that, and they don’t spend enough time on the design. And what we often find is if you spend up time upfront on the design.

Then the model build process is much faster. You save back more than that amount of time with the build.

Asim (03:23)
Indeed. And what’s the big deal, the big idea behind the cover page?

Duncan McKeen, CFA, FMVA (03:29)
The cover page is really, it’s, first of all, it doesn’t take long to build at all. It’s very, very simple, but it’s really like what you’re doing with the cover page is, it’s your chance to establish a great first impression with your audience. You gotta understand what these models are doing. They’re decision-making tools, and your audience is trying to make, you know, a very, very important

an often large financial decision with the model. They’re not gonna be comfortable trusting the model as a tool unless they feel like it’s been well put together, has good integrity, has model checks, and all of these things. And so by putting together just a really, really nice cover page, you’re really saying to them, hey, we’ve spent the time to build this properly. It’s best in class.

And it’s often just like a PowerPoint presentation. There’s always a cover page. And models are really no different. So we like to think about them as not just spreadsheets that we built, effectively we format them and put them together so they’re like a financial presentation. And it always starts off with a cover page.

Asim (04:39)
I was viewing the cover page for your FP&A model today. Number one, it looks good, and it gives the impression that you intended it to give. Thirdly, it doesn’t take long at all to put it together. Things like the design, the hyperlinks, that’s, it’s a matter of a few minutes work, and the payoff is big.

Duncan McKeen, CFA, FMVA (04:51)
Yeah, no, it really doesn’t take long.

Mm hmm. Yeah, no, I’m glad you, I’m glad that you agree that it’s literally, you’d be surprised at the number of people who skip over them or just put it together as an afterthought. You know, they just literally spend like three, Oh, oops, I forgot a cover page. Let me throw something together quickly in 30 seconds. And we would recommend that they just sort of pause for a minute and say, wait, this is your first impression. You know, this is the audience going to form their first impression based on what they see when they open up that cover page. So make sure it’s sharp.

and just showing all the things that you want it to show.

Asim (05:29)
And also in the model design course, you made an allusion or a reference to Power Query. Could you tell us a bit more about that?

Duncan McKeen, CFA, FMVA (05:37)
Yeah, certainly. We talked a little bit about Power Query. I think that it’s fair to say that in FP&A and actually all people that build financial models, they sometimes wonder about what are the best practices for getting inputs into the models? Sometimes that’s really simple. You can just manually load the inputs in. Other times, you can see people maybe linking a master file up to other Excel files, which are underneath.

But Power Query gives you another way to get inputs into a model. And it’s definitely a more sophisticated and modern way to gather inputs. First of all, it can connect to literally any data source that you can think of. In fact, it’s hard to think of a data source it can’t connect to. So it can establish a reliable connection to those data sources. And then what you can do is you can either, you can tell it to go refresh and fetch new data or you can schedule your

refreshes, which can be helpful. What it can also do then is once it brings the data in, usually the data is not in the form that you want. It’s come in whatever form it happens to be in that moment. But then you can schedule steps. You can transform the data in a number of steps. And it’ll sequence through those same steps every single time for you and then get the data into the format that you want.

If people are updating their models monthly, they’ll go fetch data and then they’ll just spend hours transforming the data, arranging columns, deleting certain columns, and it can take a lot of time. It’s better practice to use something like Power Query where you can, you can program that once and then just let it run and it’ll do the same thing every month when you, um, when you, when you tell it to refresh. So a lot of people, um,

I’ve been buried in Excel for a long time, but a lot of people don’t know about it and don’t use it.

Asim (07:24)
Right, so I presume I haven’t taken the entire, all the courses you’ve built, but I presume Power Query kind of permeates all of them to some extent.

Duncan McKeen, CFA, FMVA (07:35)
Yeah, we actually, we talk about it, but we don’t actually use it in the course just because we wanted the course to be really simple from that perspective. We wanted to be able to deliver an Excel template to anyone taking the course and for that template to already have the data inside it that would be needed for the course. The reason that we mentioned Power Query is because…

You know, once a learner gets outside of that course environment and they’re actually working, you know, with their team or at their financial institution, they would then want to, um, look at maybe a different way of bringing the data into their own models.

Asim (08:13)
Okay, and I guess if someone wants to learn Power Query on their own or Power BI, you know, we have CFI’s Business Intelligence and Data Analytics course that they can refer to for that, right?

Duncan McKeen, CFA, FMVA (08:25)
Yeah, we have a whole host of courses that look through the entire stack. It really starts with Power Query, and then it would go into Power Pivot, which allows you to pull different data sources together. And then those are the two main engines which make up Power BI in the Microsoft stack as well.

Asim (08:44)
If you could give our listeners three or some small number of quick tips regarding model design principles to keep in mind, what would they be?

Duncan McKeen, CFA, FMVA (08:51)
Uh-huh.

Yeah, the first, the main tip with model design is that you need to do it backwards, which is totally counterintuitive. Most people wanna start with the inputs and then logically they wanna go in sort of a linear chronological order all the way through thinking about all their calculations. Eventually they get to outputs. But we always recommend designing backwards and essentially start with the dashboard, right? Essentially figure out

what you want the dashboards to show, what questions are they trying to help people answer, what decision is it the executive team’s trying to make. And once you get an idea of what those dashboards need to look like, then you start back solving through the model. You say, okay, well, in order to do those dashboards, if I’m gonna show say annual estimates, quarterly estimates, monthly estimates, well, then I need schedules that are annual, quarterly and monthly.

And in order to produce all of those schedules, do I need a cashflow statement? Do I need a income statement balance sheet? And you just solve all the way backwards. Eventually you get all the way to the inputs.

You start with a dashboard and you start back solving through. Once you know how the dashboards are gonna look, what schedules do I need to support it? And then once you have an idea of what all those schedules are, then you back solve for the inputs that are required for those schedules. And so, yeah, you always go backwards. You have to do that.

Asim (10:14)
Excellent. Anything else in terms of principles? Always go backwards. Okay, so start with the dashboards. And for our listeners, we have a complete course on dashboards and data visualization as well. You know, so if you need skills to be, if you need to be upskilled in those areas, we have those courses. Any other principles before we sign off, Duncan?

Duncan McKeen, CFA, FMVA (10:20)
Mm-hmm.

Mm-hmm.

I’m not a principal per se, but I would definitely really, really encourage everyone to be really open to learning the keyboard. I cannot stress that enough. I wish someone had given me that advice when I started in finance originally. I could have saved so much time and moved so much faster. The computer’s fast and your brain is fast. The slowest part of the whole team or system

is where you’re interacting with it. And if you’re using the mouse for that interaction, it’s, that’s the bottleneck really. So you need to, it’s hard. It takes time. It’s awkward to learn the keyboard. It’s gonna take you a week or two, but you really wanna try it and stick to it. And you will never regret it. I promise you that because you’ll save back so much time and you’ll feel so efficient and so fast on the computer.

Asim (11:29)
I agree. I’ve had the very same experience. So Duncan, thank you so much for your time. Thank you everyone for listening. This has been What’s New at CFI, and we’re discussing financial planning and analysis, model design, and we’ll have more podcasts regarding financial planning and analysis in the near future.

Duncan McKeen, CFA, FMVA (11:45)
Perfect, thank you.

Asim (11:46)
Thank you.

0 search results for ‘