In this episode of FinPod, host Asim Khan is joined by Duncan McKeen, CFA, FMVA, as they discuss financial statement aggregation and analysis. Follow along as Duncan walks us through consolidating financial statements using advanced Excel functions.

Discover the significance of breaking down current and deferred taxes, balancing income statements, and mastering cash flow projections. Learn the benefits of monthly modeling for a comprehensive 24-month forecast period and how to effectively aggregate data by quarter and year. Join Duncan and Asim as they explore common modeling challenges while offering insights on building robust models with features like alerts and up-to-date functionalities.

Transcript

Asim (00:13)
Hello and welcome to the What’s New at CFI podcast. I’m Asim Khan joined by my colleague, Duncan McKeen. Welcome Duncan.

Duncan McKeen, CFA, FMVA (00:12)
Yeah.

Thanks very much, Asim

Asim (00:22)
It’s been a pleasure speaking with you these days about the FP&A course you put together. We have the opportunity to speak about the financial statement aggregation and analysis course that you recently put together. Can you tell us a bit about that?

Duncan McKeen, CFA, FMVA (00:35)
Yeah, absolutely. I mean, the main purpose of it is to really do two things. One is pulling the financial statements together. And in this model, there’s an income statement and a cash flow statement. And then the other main thing that we’re really looking to do in the course is up until this point, we’ve been building everything on a monthly basis. And we’ve been forecasting 24 months into the future. And what we’re going to do in the course is start aggregating up by quarter and by year

over in a different tab in the model using some interesting aggregation functions in Excel.

Asim (01:08)
So we’ve seen in previous iterations how you had a revenue forecast and a cost forecast. And I presume those roll down into the forecast income statement, correct? Yeah.

Duncan McKeen, CFA, FMVA (01:22)
They’re definitely gonna link into the income statement that we’ll be doing this course for sure. And we also put together a tax schedule near the beginning of this course. And always important. Never a fun subject, let’s just say that. But I think a subject that maybe a lot of people, it’s fair to say a lot of people struggle with, or sometimes they understand taxes, but they struggle with.

Asim (01:34)
All important.

Duncan McKeen, CFA, FMVA (01:47)
how to model them. I’ve definitely seen people overdo taxes and all of a sudden you get like 600 rows in a model of tax calculations. Or sometimes, in fact sometimes it’s more common to see tax calculations just being too simple. Like just showing say total tax and what we wanna do here is really stress the importance of breaking out the current and the deferred income taxes is really the key.

We need to break up the current ones because they’re cash taxes, they’re cash outflows, and if we need to evaluate the health of the company from a cash perspective, then that’s a critical piece in the calculation.

Asim (02:25)
Do you find that people have trouble with the concept of deferred taxes?

Duncan McKeen, CFA, FMVA (02:28)
Yes, definitely. Something that people often struggle with. We generally talk about them, like really the two main taxes for us to calculate here are really the total tax, which is going to go to the income statement and give us a meaningful measure of profit, and the current taxes which will go to the cash flow statement and allow us to evaluate the company’s cash flow. But definitely people struggle with the concept of deferred taxes like

non-cash taxes is something that people are often like, what do you mean non-cash taxes? What does that even mean? So in some of our other courses, it’s in our operational modeling course, we actually go through really detailed explanation of how those work. And it’s a great course on learning how to build all of the operational schedules. And by that, I mean really.

all of the schedules that you’d need to know how to build in a financial model that don’t involve capital structure. So there’s no debt modeling, there’s no equity modeling in that operational modeling course, but it’s a great course to learn how to build things like working capital schedule or depreciation schedule, you know, income tax schedule, all these peripheral schedules that you really need to know.

Asim (03:34)
That’s excellent. I’m sure people will become quite handy with taxes once they’ve gone through this course. I found the explanations super easy to follow, and I believe others will as well. So the model, as you’ve set it up, it covers a two-year forecast period, but it’s expressed monthly. So there are 24 periods.

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

Asim (03:57)
And by aggregation, I guess this is where aggregation comes in. Could you tell us what that means?

Duncan McKeen, CFA, FMVA (04:03)
Yeah, definitely. Yeah, but yeah, by aggregation, what we’re really saying is, you know, let’s sum everything up into quarters and let’s sum everything up into years so we can see it in that context as well. And I think it’s fair to say that one of the common things that we see with financial models, and it’s very typical, is that people often will model four quarters, let’s say four columns, you know, and each one is a quarter, and then they’ll sum into a year

really common. And then the next column will be Q1 of the next year, Q1 through four, and then they’ll sum into the next year. And it’s a really common structure, but it’s very problematic because of course, you’re not gonna be able to copy formulas from left to right through that structure, because you’re gonna have different formulas in the years when you’re summing up the quarters than you are in the quarters. And so we always, you know, advise, you know, the people we’re teaching to avoid that structure

at all costs. And so typically what you’d want to do is determine what is the ultimate most granular periodicity that you need. In the case of FP&A models, it’s usually almost always monthly. So then you build the entire structure of the model monthly, and then you can just literally create a carbon copy of that tab or worksheet and label it as say,

and then you’re using aggregation functions. Like in the course we’re using some if and some ifs for the most part to aggregate things up by quarter. It’s a much better structure, because then you can just, you can work down one column, build your formulas, and then copy them all straight across through the structure. Faster and less prone to errors as well.

Asim (05:52)
and dynamic, right? If you change something in a particular month, it will carry across to forecast quarters and years as well.

Duncan McKeen, CFA, FMVA (05:53)
Dynamic, yeah.

Absolutely. Yes, absolutely. Well, so, yeah, so a lot of a lot of the people when we see that structure of like four quarters to a year, four quarters to a year, that kind of structure, it’s, it’s not that they’re good model builders, but sometimes they just haven’t seen what good looks like, or they haven’t looked at the other structures and the other ways of doing it.

Asim (06:17)
You mentioned the income statement, you mentioned the cash flow statement. Is there anything to do with the balance sheet in this?

Duncan McKeen, CFA, FMVA (06:22)
We didn’t include a balance sheet in this course. We wanted to keep it kind of lean and sort of just focused on financial planning and analysis. We often find that most of the analysis getting done around the income statement and the cash flow statement, sort of evaluating profitability and evaluating the cash flow of the company. But definitely, for those that want practice at three-statement modeling, we have a three-statement modeling course, and we have an…

an intro to three statement modeling course as well. And both are just wonderful courses. And if you haven’t practiced on building a three statement model, it’s really, really great practice. Of course, as you know, it can be very, very hard to link together a three statement model and get it to balance. It doesn’t always balance on the first shot, but then you need the muscles to be able to detect where it’s wrong and fix it.

Asim (07:13)
Yeah, I’ve had that experience. I’ve done one recently for a public company. And I…

I kind of, I got it to balance on the first try. But it was only because I took my time going through it. I didn’t speed. I think when people are doing these sort of models, they tend to run through them really quickly for some reason. And that’s where the mistakes creep in, right? But we discussed this in the model design podcast that we did. If you take some time upfront to carefully design the model

and then go through it in kind of like a non-hasty manner, you’ll end up okay in the first or second attempt generally.

Duncan McKeen, CFA, FMVA (07:48)
Mm-hmm. Yeah, definitely. It’s definitely something that you want to learn at some point in your career. If you’re serious about financial modeling, you need to learn that. The other thing that we look at in that course is how to avoid creating balance sheets that always balance. So typically it’s done in retained earnings. Someone will say, oh, retained earnings, well, that must be all the assets minus liabilities minus every other piece of equity the company has.

And that’ll always balance. And that’s something, definitely something to take care of. Yeah, so we want to encourage everyone to build the balance sheet in such a way that properly, so it will actually detect errors, not always show you a balance under any circumstance. But yeah, a little bit off topic, but interesting, because we’re talking about the financial statements.

Asim (08:16)
All right. Sure, sure. Use a plug number.

Yes, no, sure. If somebody goes through the course and like, hey, where’s the balance sheet? And how do you forecast a balance sheet? We have a course for that too. We said it’s our three-statement modeling courses, the intro and the more advanced course. Then we’re just good because people may not know that. So excellent. This course will be published soon, I believe, you know, next week. Yeah.

Duncan McKeen, CFA, FMVA (08:57)
Very soon, I don’t have an exact day, but it’s maybe live within days, I’d say.

Asim (09:02)
Excellent, excellent. So I’m looking forward to that going live. I’m looking forward to our upcoming discussion on data visualization, give people a sneak preview. And well, thank you very much again for your time, Duncan. Much appreciated. Bye.

Duncan McKeen, CFA, FMVA (09:13)
Thanks, Asim.