In this episode of FinPod, host Asim Khan is joined by Duncan McKeen, CFA, FMVA, as they delve into the world of headcount forecasting and analysis. This engaging discussion explores essential strategies for forecasting a company’s employees and associated costs.

Discover how to forecast salaries, bonuses, and benefits while maintaining a comprehensive view of cash flow and income statements. Learn about new Excel features like up and down arrows to quickly spot salary changes and model alerts that enhance model integrity. Duncan shares innovative approaches to employee cost forecasting and the importance of cover pages in communicating critical information upfront.

Tune in for valuable insights into headcount forecasting & analysis that every FP&A professional needs to know.

Transcript

Asim (00:12)
Hello and welcome to the What’s New at CFI podcast. My name is Asim Khan, subject matter expert and instructor at CFI, and I’m joined by my colleague Duncan McKeen, who is also subject matter expert and instructor. Welcome Duncan. So Duncan, you’ve been busy at work delivering FP&A, financial planning analysis model for our clients. Is that true?

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

Yes, absolutely. It’s been quite busy recording for the last few weeks, for sure.

Asim (00:39)
And to date, we’ve discussed model design with respect to FP&A. We’ve also discussed the formatting and revenue forecasting course that you’ve recently delivered. And today, we’ll talk about what you’ve done with respect to headcount forecasting and analysis. That’s also a recently delivered course, right? OK. Would you like to begin maybe, I know one of the very first sections.

Duncan McKeen, CFA, FMVA (00:57)
Absolutely. Yep.

Asim (01:06)
of the video that you put together is the model tour. Would you like to give us a model tour?

Duncan McKeen, CFA, FMVA (01:10)
Yeah, like sort of an audio tour of the model. Really, what we’re essentially doing with the headcount forecasting is obviously forecasting the company’s employees. It’s a management consulting company that we’ve modeled for the particular course. But the things that we’re teaching, you can apply to any type of model. And we’re really trying to…

We’re looking at the costs of the employees really from two perspectives. One is that we’re making sure that we’re accruing all of the correct costs so that we can get them to the income statement and get a meaningful measure of profit.

And the second perspective is really that we’re looking at the costs in terms of the cash flow. So we want to look at exactly when the cash is leaving the company to pay those employees and how that’s impacting the company’s cash flow. And so that we can obviously connect that through to the cash flow statement later in the course.

Asim (02:05)
And so the model covers, as you said, various things, not just simply how many employees are there or will there be in the future, but the costs related to them, things like salaries and benefits and things like that as well. Correct?

Duncan McKeen, CFA, FMVA (02:21)
It’s definitely true. And often when you’re modeling benefits, you’d model benefits typically as a benefits load, which can range from about 20 up to 40%, depending on the different benefit structures that the employees have. And we’re modeling also bonuses, which can be quite tricky because, as you would know, bonuses don’t happen on every paycheck. They typically happen, well, sometimes once a year, sometimes four times a year, it depends.

And you want to be able to control the timing of those bonus payments in the model as well, because they can be, the timing can change from one period to the next, depending on what’s going on with the company.

Asim (03:00)
Yeah, well, we both worked on the sell side, you know, Wall Street firm on my case and equivalent in Canada and yours, but those bonuses got paid, but once a year, right? And that was the big day, if you remember that. All right.

Duncan McKeen, CFA, FMVA (03:11)
Hmm.

It’s true, yeah. And that’s very, very typical at the, at least the bank owned brokerages. Some of the independent brokerages in Canada would pay bonuses quarterly and even sometimes monthly if you can believe it, which was, which is a really great thing to have. I’m sure lots of people would love to have that. But it’s definitely like, yeah, yeah. It’s definitely, sometimes it’s too long when it’s annual.

Asim (03:31)
I’m going to go ahead and turn it off.

Right, right, you know, because you try to plan things out, then who knows if the firm has a bad quarter in December. It could devastate your expectations. There are some really nice features that you put into this model. As we know, as employees, tenor at a firm,

Duncan McKeen, CFA, FMVA (03:44)
Yeah, yeah.

Asim (03:53)
lengthens, their salary tends to go up and things like that. So that rise is kind of hard to capture when you’re looking at horizontally across a spreadsheet. But you made it very easy by triggering these up and down arrows for when salary changes.

Duncan McKeen, CFA, FMVA (04:07)
Yeah, that was a new feature that we haven’t put into a model. This is the first time we put it into a model, not because we’d been ignoring it, because it was a very new feature that’s come out in Excel where you can put effectively you can

insert tiny little icons in as text characters. And there’s a whole, there’s literally hundreds of them, but the ones that we found the most useful were up and down arrows in this case. And yeah, to your point, when you’re looking, and this particular model forecasts out for 24 months, so you’re looking across 24 columns of data.

It’s really hard to see when that particular salary might change, especially if it’s going from like, imagine something going from 70,000 to like 77,000, for example, it’d be really hard to see that second seven in the number. So there’s a little up arrow which triggers or if the salary moves down, which we never hope that is the case, but that would trigger then a down arrow. So you can easily spot the changes and when they’re happening in the model.

Asim (05:05)
Done and you’ve included a number of alerts as well.

Duncan McKeen, CFA, FMVA (05:09)
Hmm, yeah, we love to include model alerts for a whole host of different reasons. Later in the course, we will see that we’re gonna have alerts when the company’s exceeded the limit on its line of credit, for example. These particular alerts are alerting the person updating the model if they’ve put a number which is inappropriate into a cell, and so for example, if they leave certain cells blank, an alert will go off and it will turn like bright orange.

If they put in a negative number into a cell where it needs to be a positive number, then an alert will go off as well. If they’ve over scheduled an employee, if there’s 30 days in a certain month and they put that employee down as working for 31 days, then an alert goes off as well. So we just want to teach everyone watching the course how to program those alerts because they’re important because they’re protecting the integrity of the model

preventing somebody from putting something inappropriate into a cell.

Asim (06:06)
And then, you know, this is going maybe a little bit even to the context, but those are linked to the cover page so that if a new user opens it up, they’ll see right away that, you know, there’s a problem here. We’ve we’ve tripped something and they’ll investigate it further.

Duncan McKeen, CFA, FMVA (06:20)
Yeah, that’s a good point. The, often think about the cover page as like the dashboard in an automobile. And, you know, if something was going wrong in your car and alert was going off, that’s exactly where you’d want to see it. Up on the dashboard, you know, right in front of you so that you’d be aware like immediately and you wouldn’t miss it. So that’s the idea. We also find that by putting alerts on the cover page.

It also is instilling confidence in the person who opens the model and sees that for the first time because they’re saying, oh, wow, they were, you know, they, um, they had the wherewithal to program alerts in so that we would know. And it’s actually really comforting to see a panel of alerts that are telling you, all of them are telling you everything’s okay. You know, it’s really comforting to know that those checks are going on and that everything’s in check.

Asim (07:09)
Nothing’s flashing red and the person who built the model actually took the effort to build a cover page, you know, for the purpose of communicating upfront what’s kind of happening behind the scenes. Excellent. So what can we expect next from you regarding FP&A?

Duncan McKeen, CFA, FMVA (07:11)
Exactly.

Mm-hmm.

Yeah, so then the next course coming up, as we mentioned, is all about forecasting the external contractors for the company, which brings about its own challenges that are slightly different from forecasting the employees. And then, and these are of course, all a part of a large series. And…

there will be in total seven FP&A courses that all utilize the same underlying Excel model, which is quite complex. That’s the reason why it’s broken into seven different courses to focus on different areas of the model.

Asim (08:03)
It’s a good thing that I’ve taken the revenue forecasting course and the headcount forecasting analysis course. What I really appreciate was they were about three hours or less in duration.

Duncan McKeen, CFA, FMVA (08:14)
Yeah, it’s nice to have a nice bite-size piece that you could fit into an evening or an afternoon if you had available without spending a couple of days or even a week on the course.

Asim (08:26)
Right, okay. Well, Duncan, we’ll see you again when we discuss the Contractor Forecasting course. Thanks a lot for your time.

Duncan McKeen, CFA, FMVA (08:33)
Absolutely, thanks to Asim