Power Query Magic to Become a Financial Modeling Master with Ken Puls

Will AI be able to build our financial models? I think that AI is the greatest data theft in the history of the world. The majority of answers are not right the first time. 70% of people take what ChatGPT gives them and just go with it without actually verifying it. And that's pretty scary considering that only 50% of the time is it actually right.

How do you validate it?

You need to be able to validate these things to actually make sure that a the piece that you get is right and then B, how Excel has this incredible list of formulas that can do all kinds of amazing things here. Coming up in the finance world world, I think that it behooves you to learn at least one technology for cleaning up data and automating that process. One of the things that I want to make sure that people don't lose sight of on this thing is that the whole point of Power Query is to grab your data and reshape it into a format that is usable. The more complex it gets, the more likely there's going to be an error in it to start with and the less likely someone's going to notice it. All external data should be driven, in my opinion. Yeah, if the that's my come to Jesus moment as Ken's leg.

Welcome to Financial Modeler's Corner where we discuss the art and science of financial modeling with your host, Paul Barnhurst. Financial Modelers Corner is sponsored by Financial Modeling Institute Foreign. Welcome to Financial Modeler's Corner. I am your host, Paul Barnhurst. This podcast is where we talk all about the art and science of financial modeling with distinguished guests from around the globe. The Financial Modelers podcast is brought to you by the Financial Modeling Institute. FMI offers the most respected accreditations in financial modeling. I'm excited to welcome to the show Ken Pulse. Ken, welcome to the show.

Thanks. Good to be here.

Really excited to have you. So Ken is an Excel mvp. Ken's my Jesus and he does. He runs his own business and we're super excited to have him. We start with the first question for every guest. Tell me about that horror story, that worst financial model you've seen that you've worked on.

So I'm not sure I'm going to classify this as a financial model, but it was sort of one of the key things that we use to do our sales reporting on a daily basis. And I remember when I got there it had a macro in it, which turned out after some long work to figure out was actually a memorized series of Lotus keyboard shortcut switches in order to run the macro. So it wasn't macro at all. But I just remember the way that this whole thing was set up. I remember that the instructions that I got at the beginning of the day is, okay, well, the first thing you're going to do is you're going to go into this cell and you're enter the date in this format. And then on another worksheet, you're going to put in the same date but in a different format over here. And over here, you got to put in the day, and over there you got to put in the year. And on this one, you got to put in the month. But make sure that it's only spelled with three characters, not full month names and whatnot. And the amount of repetition of data that went in there, you know, pretty soon you're looking at February 31st of a specific year, and it's like, what is going on? Like, it just doesn't make any sense. And I wish I realized at the time how terribly designed that was. But I was still kind of early in the. In the career here. And let's just say that by the time I left the company, it looked an awful lot different. There was one place you put in the date, and that was it. So, yeah, that was. I don't know. I can't say it gives me nightmares anymore because we solved it. But it was. It was definitely a horror show.

Yeah. One of the worst I've had is a sales commission model. Not quite that bad, but you still remember I was getting instructions. One of them is, you know, sort the list in this order and then highlight the first 20 purple. Okay. And, you know, finding hard codes everywhere. And it was so funny. I inherited it. And I was working on trying to transition it all and clean it all up. And my boss calls me in a panic one day going, do you realize these. These commission files will fail in audit? Like, yeah, they would have for the last year. Where have you been? Like, I just took it over. I'm trying to fix that. Like, this is better than what we had. But, yeah, it was a nightmare. So I can be there with you. I think commission models sometimes could be the worst, worst ones out there because they're usually a ton of data, they're complex, and if you have somebody that's not good with data, they're just such a mess the way they get built.

Well, complexity is one of those things. It gets terrifying, right? I mean, the harder or the more complex it gets, the more likely there's going to be an error. In it to start with, and the less likely someone's going to notice it. So in my experience, what do they do when they have that? They go in and they format it to look really, so that hopefully nobody will look. Because, you know, the prettier it is, the less likely we're going to actually go and audit it. Right. And yeah, it's. It's a terrifying.

I'm with you. So what was maybe the key takeaway or learning from that experience? What did you take away?

For me, it came down to, like, look. I mean, you know, Excel has this incredible list of formulas that can do all kinds of amazing things here. So let's figure out how we can follow. It's. It's what I actually refer to as the single input paradigm. Right. You should be entering data with once and once only, and use your formulas to break it apart as needed into its other constituent components so you can reassemble it as needed elsewhere. But the key thing there is that if you've got one input that can drive your 10 different places, you need your date in your, you know, 12 different formats or whatever it is. There's only one place that somebody can make a mistake, and that's in the original date input. You can even add data validation to that to try and actually stop some of those things. And you may not be able to eliminate them altogether, but by reducing the amount of places you actually have your inputs, being able to add some advent of our validation to it proactively, even reactively, with conditional formatting, to say, look, this doesn't look right. You know, at that point you reduce garbage in, which reduces the potential of garbage out. And that's, you know, really what it comes down to is we're always trying to make sure that our models are spitting out valid information, not garbage, at the end of the day.

So, yeah, and we've all seen plenty of garbage, so I get it. Alrighty. So can you tell us a little bit about yourself and your background? Just give our audience, you know, a little bit of who Ken is and what you're doing today.

Sure. So I am. I'm actually a charter professional accountant based in Canada. I live on the. The far west coast on Vancouver Island. It's where I, you know, sort of started my career and why I've been working with, with data for as long as I have. I also have had an interesting career in that, you know, as I was going through and getting my CPA designation or CMA at the time, we've, we've amalgamated our design Canada. I also, you know, ended up with jobs like, you know, accounting supervisor and system administrator or, you know, controller and director of it. So I mean, I very much walked lockstep hand in hand with both a finance and an IT side of my career pretty much since my first job, you know, so I've looked after, you know, Excel spreadsheets, I've looked after accounting teams at the same time looking after your networking and you're building, you know, hypervised servers and farms and things like that. So you know, for me, this always this sort of hand in hand approach as we're building things and trying to figure out how to provide information to my users that work within the confines of the IT infrastructure, not, you know, blowing things out the side. So, you know, it's, it's been an interesting journey and I mean, I'll certainly say from the IT side, there's been times when I've sort of got up and looked at myself in the mirror and go, oh my God, I'm that IT guy that everybody hates. I need to figure out a way to enable my audience to do the things I need to do. And you sort of change to try and focus and pivot on that without letting down the security side. It's been interesting, but you know, it's, it's been fun because it's built a lot of skills that I think really go hand in hand. And I think for a lot of companies we need tighter integration between the finance department and the IT department because one of the things that I see way too much in big companies is when you walk in there and you say to, to somebody, hey, like, so where do you get the data from this? And say, oh, it won't let them have data for that. I'm like, well, do they capture it? Oh yeah, but they're not going to give it to me. Then why are they capturing it? What's the point? Like, you know, we only capture for one reason. That's the report on it. Let the figure out how to break down those silos so that you can actually use what you're capturing. Otherwise you're just, you know, wasting money on capturing data that nobody's ever going to play with. So it's been one of those kind of fun journeys to just sort of figure out how to break down some of those barriers, how to get the reporting out from a pure finance and find out the modeling side. It's, you know, I don't know, it's, it's, it's been interesting. So, so today what I've sort of transitioned that into is. Is taking a lot of those learnings. I now teach courses. So I run my own company, XL Guru. We have a learning platform that we run which is called Skillwave Training. We actually run courses there. Power Query on Power Pivot, Power bi. I have a really awesome complete one called the Self Service BI Academy, where we actually cover right from raw data and through Power Query, dimensional modeling, Power Pivot, dax, all the way to Power bi. Lots of fun stuff there. And because that's just not enough to keep me busy, I also write software. I write a program, an Excel add in called Monkey Tools, which allows people to build better data models faster. Both Power Query and Power Pivot Technologies are involved in that as well as a. A cool little tool we have called Bibliomonkey, which allows you to store and replay formulas really quickly. So if you've got some complex formulas for, I don't know, doing some kind of forecasting, you can, you know, select yourselves, right click insert and it pops you up with the inputs you need and where you go, you're done. So just making life easier and more reproducible. So. So those are the big things I do today. So.

Is that it?

Oh yeah. Well, I mean, I've written some books, you know, I'm trying to take some time off every now and then.

Yeah, on the shelf here somewhere. Yeah. M is for Data Monkey. I keep saying I should buy the new version.

Yeah, yeah, yeah, the. The new version. The new version looks like this and it's. I highly recommend it. It's a. It's a complete upgrade to the previous.

Book, so I would imagine it is. I know a lot has changed. I just. I don't look at the books much anymore, so I've just been like, I probably should get it for the shelf type of thing.

There you go.

So. Alrighty. Well, I appreciate that background. I can totally relate. So what you're talking about, the IT and finance, I've kind of hovered in the middle. I did a Master's of Information Science and Finance and spent my first couple of years doing report writing. So I maintained access to the database that nobody in finance had. So I was always the one running the data and getting the reports for FP&A and doing all those kinds of things. Because not only did I know all the databases know how to pull it, I still actually had access because they never turned it off when I switched roles. And it was my boss loved it. Hey, can you pull me this data? Yeah, sure. I'll go Pull it.

But isn't it weird that the role by default wouldn't have that like, it just. I've always thought the role of analyst should be to analyze the data. Right?

Yeah, I get both sides. But I know sometimes, you know, large companies wanting to limit who has access to the data, especially if there's ppi. I work for a credit card company.

Oh yeah.

I was on the travel side where they were much more laxed on the credit card side. Super tight as you could imagine.

And don't get me wrong, I'm not saying that everybody should have access to the CEO's salaries. And if you're working in the military, there's classified data for a reason. Like I get all that. Right. But when, you know, when you're working in an organization, and this is what I see far too much is, you know, the IT policy. I like to affectionately refer to this as the just say no policy. Right. And you phone it, say hey, it, I need, and they say no. And you go like, I haven't even asked you what I need yet. It just doesn't matter, I'm busy, go away. And you know, over and over I keep hearing about that and you're going to. This doesn't make sense. Like, you know, it's, it's nuts. I just, it's something that teams and I understand, you know, the bigger the organization you get, the more siloed you get. I understand the challenge, but at the same time, you know, the database admins of the world, the IT departments of the world need to realize that they are actually an enabler, they're not a blocker. Right. You need to make sure that the right people have access. But that is the job is to make sure the right people have access to the right data. Not to flat out say nope. And you know, successful companies do that. Companies that are less don't as much.

I, I agree with you. It's very important we have access to the data. You know, it's critical when you're building financial models, when you're analyzing those models and the data you built, you need to be able to get that data. Whether they run the report for you, you run it yourself. Depending on skill set and other things, fine. But you need to have access to that data. So I'm, I'm curious, how did being tech savvy, it's clear you're tech savvy, kind of from the beginning you gravitated toward technology. How has that helped you be a better accountant? How did that help you in the accounting Side, the finance side of things.

Wow, that's a crazy big question. I can't imagine not being tech savvy and not ever trying to do it a different way. I mean, so, you know, one of the things I think about me is that, you know, I've always tried to, to push boundaries and sort of continue to make things better. I mean, I joke with a lot of people that I'm inherently efficient. That's the word I'm looking for, like to do things multiple times. Right. So, you know, I'm the kind of guy, my mom used to give me a hard time when I was a kid. She said, you know, like, why do you spend, you know, 45 minutes trying to plan out how to unload the dishwasher? I'm like, because tomorrow it's only going to take me two minutes and I'm going to be done. And you know, I take that sort of mentality with me where I go, my brother would take him five minutes. I got it done last time. I can go play video games. I'm good to go. So, you know, when I look at this, I'm like, okay, let's figure out like, what systems have my data. How can I go and actually extract that data so that I can actually use it? Because the last thing I want to be doing is taking a report and hand typing the numbers into it. Why? Well, one, because it takes too long and I get bored, my brain checks out and I'm more likely to make a mistake. We're back to the garbage in, garbage out thing, right? The more inputs that we ask somebody to make, the more likely you're going to have a challenge in your hands. So for me, I was kind of like, well, can I extract it from somewhere? Can I figure out a way to automate this thing so that it's A, faster when I need the time and B, maybe more consistent. So that kind of led me into a little bit. I mean, that wasn't the first catalyst, but it was one of those things that once I understood what the power of VBA was, I mean, it was got what got me in there going, hey, I wonder if I can write a little macro so I can make my computer work for me rather than me working for my computer. So, you know, being able to sort of take those two sort of skill sets and say it's not either an accounting thing or an IT thing. It is simply a tool for me to leverage in order to do the job that I need to do to be able to build the reporting these Things go together and that's, you know, over and over I'll say, those kind of things, they go together. So it's just a tool to use. I just wasn't afraid of it and I wasn't afraid of running into a roadblock and saying, okay, I might have to put this aside for right now, but I'm going to come back to it later because I am not going to let this computer beat me. There's no way, right? And, you know, I keep on, you know, pushing forward and trying to find ways to do things to get them done. So sometimes it involves workarounds. You got to download a CSV or a text file because you can't connect directly to the database. At the end of the day, I'm like, you know what? I can write a little macro to import that text file or CSV file the way I need. And that's why I used to do pre Power Query. Today we get Power Query. Man, does it make that job so much easier. Right? So, I mean, I think, like I say, I think the things go hand in hand. I don't think you separate them. I think you, you know, as if you're, you know, coming up in the, in the finance world, I think that it behooves you to learn at least one technology for cleaning up data and automating that process. Whether that be Power Query, whether it be vba, I definitely recommend Power Query. You know, whether it be Python, like, it doesn't matter. But you need something that you can actually leverage to say, how can I invest some time upfront in order to make it more efficient when I need to do this job regularly? Because that's one of the big things in finance. Not all times created equal. Time at month end, inherently precious time later on during the month, much more expendable. Right. Just because of deadlines.

So great point. And I like how you put them together. Now, me, I learned SQL because I did report writing for a couple of years and that helped a ton, being able to pull the data. Then when Power Query came along, a lot of times I didn't have access to the databases to pull, but I could actually get to them through Power Query a lot of times that's where I started using it a ton. As that came along, I still remember when I first learned it, I was like, why hadn't I learned this earlier? What took me so long? Because it really transforms things. So I know you have a huge love of Power Query. How did you first learn about Power Query and what were your thoughts when you saw, maybe talk about that and then we'll talk about why it's important for our audience.

So I mean, one of the things I didn't say in my intro, I am a Microsoft MVP. So I've been awarded by Microsoft every year since 2006 in both the Excel and Power BI categories along the way. So around in 2012, I think I was down at Microsoft and I was showing one of the big models that I'd actually built, a forecast for housing development. This thing had a ton in it. It's one of the most complex models I've ever built. Still today the Excel engineers, they came to me and they said, how come you're not using pivot tables for reporting? Using all these formul like this just seems inherently dangerous. I'm like, oh it is. But I can't use pivot tables because my data is not in the right shape. And they kind of nudged each other. And so we said somebody should build a tool for Ken that would allow him to get his data into the right shape so he could use pivot tables. Haha, whatever. And I didn't know that those guys right then were working on Power Query. They didn't tell me that. And so the next year 2013 rolls along and it's like, hey Ken, we want to invite you to this private preview of this tool that we have. And you know, it's called Power Query. Here, let's have a, let's have a run with this and see how this works. So now remember, Power Query was released to the general public in 2014. So I had a pretty good sneak peek into what was actually going on with this. And I remember going down to Microsoft and having a conversation with these guys. You need to understand one of the things about CAN is that I know enough to be dangerous in several different areas. I know enough to be very dangerous in SQL. Like I know how to delete a table in a database by accident. Have not done this, but I do know how it works, you know. So I know SQL, I know vba, I know vb dot net, I know some C Sharp, I know a variety of different languages. So here I am, I go down to meet with these guys and they say, so what is your impression of Power Query? I said, I think you guys are building a terrible replacement for SQL Server Management Studio. And they looked at me totally confused and they said, well that's not really the point of what we're doing. It's like, well what's the point of what you're doing? They Said, well, what we're trying to do is we're trying to build a tool so that Excel people like you don't need to use SSMs. And I looked at the one. Oh, well, that changes everything. And it was just this weird light bulb moment for me where suddenly I'm like, I was looking at this tool completely wrong. At that point, I went back and I started looking into it a lot more seriously, because I was trying to do SQL Server stuff through this tool, which really wasn't what it was designed for. It was designed to help people who don't know anything about SQL go and start writing things by clicking buttons, and it'll just write the SQL behind the scenes for you. If you're connected to a database, which is brilliant, right? And it records all the step, like, it's so close to. I mean, it's not close to VBA at all, but the concept that it records stuff is very correct.

Yeah, I know.

And you know, I always like to make that because I've seen, I've seen a lot of people say, oh, it's super close to vba. Like, no, the coding language is completely different. Don't ever say that.

Languages aren't similar at all.

The concept of recording, concept of recording is the same, but Power Query's interface is actually better because you can do it all by clicking buttons like that. They've really built an amazing tool for this. So once I sort of, like, put together what it really was and I started doing things, man, I took it back to, you know, I'm working on it in my office and, you know, doors closed, nobody's allowed to see what I'm doing. So finally this thing goes and it releases to the general public. And I'm like, okay, cool, now I can tell my team what I'm doing. I'm like, hey, you got to do this and whatever. And I remember, I remember sending one of this to, to one of the guys on my team. I'm like, hey, dude, you got to see this particular thing that I just wrote. I said, copy the script. Because we really, you know, it wasn't a good way to share things. So I gave him the M code, said, you're going to go to the advanced editor, put this in, hit enter. He did it. And I heard, oh, my God. And I'm like, I know this, this thing was something that was so hard to deal with. We had to grab the data, we had to pivot it, make some actions, unpivot it, do some more work, pivot it, do some More work, unpivot it to get to what we needed. And I made it work in Power query in about 15 minutes. I had been trying to figure out how to get this done for a year before that period in SQL and I finally went, went, wow, this changes everything. So first I had to figure out what the heck the tool was supposed to be for. But once I got that, I'm like, this is amazing. So the tool was out. In 2014, I taught my first to half day course on. On. I. I was begging CPAP here, like, you guys got to give me a course. I'd only got about a half a day of material. They're like, okay, Ken, sure, we'll give it a shot. I taught my first half day course on Power Query, I think in, in late 2014. Like, yeah, I was, I was on it really quickly out there and you know, since then it became a full day course. And now I'd say, you know, we got a PowerPoint Academy at Skill Wave that I think is hours of recorded video. Like, there's so many things you can do with this tool and we could put in a ton more if we wanted to. I mean, it's just. But you know, at the same time, sometimes it becomes overwhelming with how much content there is, right? Yeah, but yeah, it's, it was a game changer because, you know, it's interesting and I do want to say this because, I mean, a lot of people know me as the Power Query guy. Power Query, Jesus. Even, like, which is just weird to me, but whatever. But you know, one of the things that I want to make sure that people don't lose sight of on this thing is that I don't Power Query to Power Query. Like, that's not the point of dealing with this stuff. Stuff. The whole point of Power Query is to grab your data and reshape it into a format that is usable. That model I mentioned that I showed those guys in 2012. Few years later, I built another one. It's what paid for my first year in private consulting. And I said, my condition is I want to be able to use Power Query and Power Pivot. By the time we were done, that model had three formulas in it where the previous one had 448,000 unique formulas. Crazy index invested index matches. This one was three formulas. Everything else was driven through Power Pivot because all of it, we took the input sheets that the engineers gave us, we ingested them, and we use Power Query to do the data transformation. It was brilliant because my users could enter data when they updated it in the format that made sense to them so they could self validate to reduce errors. I could really manipulate it into the output I needed. We could report it all through pivot tables. Absolutely brilliant. And change the game. Power Query is about getting data into the right sheet. That's what it's about. But it's a means to an end so you can do more with it. It is not the end. Right. And that's the big thing that I think a lot of get confused with me. They're like, oh, Ken can do anything in PowerPoint, like, yeah, but I do have a reason, right. It's not just for fun.

So yeah, I mean, if Python was a better tool, you use Python or you know, sometimes Server Management Studio, whatever the case may be. You're using Power Query because it's a fabulous tool to get data into Excel, into a data model, into Power Bi, wherever you may be pulling it in.

Exactly.

That's something that, you know, financial modelers struggle with a lot of. I know a lot of financial modelers that still copy and paste their monthly data in each month.

I know, I know, it makes me cry. I say like, it's so 1995.

Yeah, exactly. That's a big reason I want to have you on the show is I think every financial modeler. So ignore accounting. I don't care where you are. If you're private equity, if you're corp dev, whatever. If you're regularly building financial models in Excel, you should at least understand the basics of Power Query. How to bring in some data, how to shape it a little bit it and combine it. You don't need to go deep, you don't need to know everything because like I know you probably put thousands, if not tens of thousands of hours into Power Query. Modeler doesn't need to put that much time in 50 hours to do 95, 99% of what they need to do.

And I mean, we got to keep in mind, right, I'm not normal and I know that. Right. Like, you know, and I mean, I pride myself on it. I mean, I'm not normal. But you know, the deal in this is, you know, if you're looking at any time that you're copying and pasting into your data file, I mean, you're running the risk of a couple of big issues. I mean, number one, you're overwriting data that shouldn't be overwritten. So there's number one. Number two, when you copy and paste the February records in on top of the January table, did you remember to delete the 29th, 30th and 31st from the bottom of the table. Or do you still have those in there? Because February was actually short. Right. So there's those kind of things that lead to data errors. But anytime that you're copying and pasting in and say, there's a prime candidate right now for Power Query.

Yep.

You know, it does create an external link to the data source, which, you know, know we don't love links, but it's not like a workbook link. I mean, the concept is at that point I can download a new file, replace the old one, and I just hit the refresh button and boom, it comes in. So there's no worrying about the copy and paste. There's no worried about missing or overwriting rows because the table it loads to will expand and contract exactly as it needs to. And that. That's brilliant. So number one, it's faster. Number two, it's less human intervention, which makes it less error prone. I mean, it's absolutely brilliant. And yeah, yeah, all external data should be driven, in my opinion, through Power Query. Plain and simple.

FP and a guy here. And as you know, I am very passionate about financial modeling and the Financial Modeling Institute's mission. I have been a huge fan of the FMI for years and I was super excited when they decided to sponsor the Financial Modelers Corner. I recently completed the Advanced Financial Financial Modeler certification and love the entire experience. It was top notch from start to finish. I am a better modeler today for having completed the certification. I strongly believe every modeler needs to demonstrate they are a qualified financial modeler. And one of the best ways to do that is through the FMI's program. Earning the accreditation will demonstrate to your current and future employers that you are serious about financial modeling. What are you waiting for? Visit www.fminstitute.com podcast and use code PODCAST to save 15% when you enroll in an accreditation today. Yeah, no, I'm a huge fan of Power Query and I connected all the times to Salesforce to build sales commissions files and all kinds of different places so I could just pull it all in and do my work, whether it was an Excel file, whether it was a CSV, a text, whatever it might be. So if we have someone listening, we have our financial modelers listening. They're like, I'm still copying and pasting. I really should learn Power Query. How would you recommend they get started?

Depends on what your appetite is for price, really. I mean, in the grand scheme of things. So, I mean, I have a fantastic book called Master your Data. You can pick that up at Skillwave Training in a PDF format if you prefer the physical format of course, check out my website. I'll give you a link to where you can actually buy it direct from the publisher. So you don't have to necessarily fund rockets and things like that for sailboats for some high priced exec that you might be buying it from. Otherwise, I'm just saying it's always nice when the authors get rewarded on these things. So there's that. So if you go to Skillwave Training, we have a shop there and MasterYourData you can find there. We have the information on where to buy it as well as you can buy the PDF direct from SSA. So that's one and that runs you less than 20 bucks US for that. But you know, if you prefer to.

Read with your book.

Seven years ago, exactly.

I bought two books and that was one of them. The other was a Rob Collie Power Bi Little Bit of Power Query book, his first book.

So I would say and I mean I know in that book he actually recommended get our book to start first because the data should be in the right shape. Because once the data's in the right shape, the data modeling is actually much easier.

And I'm with you. I think you learned Power Query first and then Power Pivot or Power Bi.

And Power Query actually can serve regular Excel without Power Pivot as well. So I mean that's the benefit, whatever, right? It's just super, super robust. So that's one option if you prefer books, if you prefer a course. We actually have a free course at Skill Wave which is just an introductory course into Power Query. So you can see how Power Query actually works. And then that leads into our Power Query Academy, which I mean it's certainly not a lower price point. I mean it is a premium price point course. But you know, like I say start with a free one, give it a shot, see if you can pick up some nuggets there. And if you, if you like the style then come visit us for the full course. Course. I also teach Power Query in my self Service BI Academy and my Excel Fundamentals academies like lots of different places. So Skill Wave has lots of different resources out there. Outside of that, you know, there's lots of videos on YouTube and whatnot. You know there's, there's other courses out there. All I can say on my course is that I work really hard to curate them and make sure they have a story that goes end to end. That's, you know, why the prices are what they are and whatnot. You're learning from somebody who's really been with us a long time time. At the end of the day, though, I'm going to give you a big piece of advice right this. If you don't learn it from me, that's fine, but learn it from someone, it's so important. I've got lots of colleagues that do great jobs in there, you know, on these, these things. But it is an absolutely essential technology that everybody that is in a modeling and role here should definitely have in their toolkit, for sure.

I agree with you. I'm going to say that everybody should learn it. I know you're a great resource. I would encourage anyone to pick up the book, whether you take one of the courses or not, pick up the book to get started. There's other books you can pick up as well. There's lots of, like I said, videos. There's plenty of ways to learn it. Just learn it at the end of the day. I want to ask you specifically about a course you have on your website because I thought it was really interesting you had one called Building Financial Statements in Excel with Power Pivot.

I did. Should I tell you it also has Power Query in it?

Well, I figured I had to have.

Power, of course, first give it just.

A little overview of what Power Pivot is for the audience and then kind of why you put the course together, what they had learned from it, what the course is about.

Okay, so let's see. Let's start with the. What the course is. Well, let's start with why I put it together. So. So I actually put it together in some ways as a challenge for myself. Now, here's the thing, right? So I'm a finance guy, I'm an accounting guy. And again, you know, one of the reasons that we always build all of our financial models at the end of the day is because we're trying to figure out how much money we're going to make or we're going to lose. And, you know, ultimately the way that the client usually wants that presented is some kind of a financial statement format. You know, one of the things that was interesting in my history is that we went in with our organization and we actually bought a big centralized system to run our entire company. So we had a golf course, we had real estate, we had food and beverages, we had a beer and wine store, we had a fitness facility, land development. We had a lots of different things there. I think I said real estate, land development. Choice, but whatever. Lots of different things. So what was interesting is we spent about 150 grand on the system. We implemented the whole thing. We went and we put the financial statements together and we printed them off. And the software, we never really thought to look at this, but the double underlines were done with equal signs and the lines were done with minus signs. So it looked like the software that we had just paid 150 grand for was written in 1980. As it turns out, that was when it started being written and they hadn't changed anything in the report writer. And we went, oh my, how are we going to go and give a report ahead office of our brand new system that looks like it should be off of a dot matrix printer. So we said, look, let's dump all our information into Excel. So, you know, as it turned out, we had other things that we wanted to add over time. And basically, you know, sort of where we went with that is, well, how do we stitch all this information together? Together? This is what Power Pivot brought to the table. Power Pivot, for those who don't know, is kind of like pivot tables on steroids. One of the big issues in a pivot table is you only get to serve up one data table to it and that's it. If you can't vlookup or xlookup your information into that table, you're kind of stuffed. You can't use a pivot table. So Power Pivot allows you to link multiple tables together so that you can actually start reporting and do things like, you know, budgets versus actuals. Even though, you know, the transactions don't line up, the granularity, it doesn't line up. There's, you know, items in budgets that aren't in transactions items and transactions in our budgets, things like that. So let's you solve those problems. So we use that technology to sort of stitch things together to build ourselves a financial statement system in Excel because we didn't like what we had. I have since done this with other models that I've built. Let's prepare a forecast and let's put it out in a financial statement format so that why not share this information? And I also did it in some ways, like I say, as a challenge to myself, because in order to work with Power Pivot, you have to write your formulas in a language called dax, which is very similar to Excel, but it can get a little bit more complicated. And the challenge that I wanted to ask if I could solve was can I write a statement of cash flows index or, you know, maybe formerly known as the statement of changes in financial Position. I mean, it's the, it's the number one thing that goes on accounting exams to screw up students everywhere because hardly anybody understands how the statement works. And it's complex and challenging. And I'm like, if I can do that, I know I've really done something special. Like I've actually managed to make it in the DAX world. That's going to make me happy. And I got it done. Took me a lot longer than I thought it would to go and write the whole course out, but we actually managed to get it pulled together. So what the course actually does is we go through and we say, okay, let's say we start with some raw data here and maybe you're dumping your financial statement history or whatnot into a file. What does it look like when it comes in? What do we want it to look like when we come out? I think we provide three different formats of how you might want to see it in your data table. So, talking about the layout for the power pivot model, we use Power query to reshape things at that point in time. Then we build the model out the way that we actually need it. It's got some complex modeling components to it, you know, some, some leaf tables and weird things that, that happen in things to be able to report the statement of cash flows correctly. So we teach how to build the dimensional model, we teach how to build the DAX step by step, going all the way through. And then we teach how to put it all together and, you know, protect it as much as we can in Excel and release it to, to our users. So it's an interesting journey for a course. I mean, I certainly say it's not for everybody. If you're working in a multibillion dollar, you know, organization, you probably shouldn't be using Excel to generate your financial statements. But you know, for smaller businesses or organizations who ran into issues like mine or you bought the system, and it's like, oh, this looks like garbage. We need to recreate something better. You know, then it, it works, it works quite nicely. And the reviews and feedback I've had from people on it have been very good. They've enjoyed the complexity of the, they've enjoyed the way that we broke down the DAX and we layer it up gradually because it is complex. So they've enjoyed that. They've been able to get through the complexity with it and actually build these, these things off or build, you know, statements off of their own, own stuff, which is you know, at the end of the day, isn't that why we teach? Right. Is to help people apply this stuff to their own knowledge?

So, so it sounds like it really. One was personal challenge.

Yeah.

Two, it's for people like you said, smaller companies generally, not big companies that have a really good reporting system. Maybe somebody who's a freelancer who, a. The company needs the model to go into Power Bi or Power Pivot. They want to see their statements each month. Month. It's a way to build some automated reporting.

Yep.

So in addition to just your model.

What, what's interesting is like, I mean, you know, I see a couple. So one scenario where I've seen this kind of thing work is where somebody's got their own system, but they're also forecasting. We're thinking about adding an entire new arm to the business here. Well, we're not going to post all of that into our regular system in order to make this thing work because this is just forecasting. We're, we're, you know, we're spitballing, we're prototyping, we're trying to find out whether or not it makes sense to invest in this. But we need, we would need a consolidated financial statement to say with a reg Business operations. This. What is it going to look like? There's a prime case where this would make sense to go in and take in. So it really depends on the needs. And I'm not going to second guess what the needs of all the companies are. I mean, I'm assuming if they're coming in to look at that, that they have a reason. When I do consulting problems, I ask a lot of questions about the reasons. But when I put a course out, it's like, look, I mean, this is, this is out there. I mean, you know. Yeah, in some respects, I wrote it for me. And in some respects, though, I wrote it because I just wanted to generally share the knowledge out there. And you know, as I say, I mean, I've got some, some great feedback. I will tell you the. One of the funniest things that I got from that is when somebody emailed me me and I think, I think they were from India and they said, look, Ken, I'm curious about this course, but I'm wondering if I can get a discount because of course I'm going to have to adapt to the way that financial statements work in my company or in my country. And I said, okay, well, that's interesting. Here, let me send you a PDF of what the reports look like when they come out the door and email me back. He goes, oh, they look identical to what we use here. Like, yeah, how many ways can you do a balance sheet and an income statement? Like it's a standard, Right? So that was kind of a funny thing. I'm like, all right, yes, awesome. I got that one nailed. Right, Right.

So that is an interesting one. I mean, I could get different accounting, but that's not going to impact that. Whether you're using IERS or gap, that's just your debit and credits and how you recognize things. Not.

You know, there is a difference in the way some people like to report their financial statements. Like, do you want to have a gross profit or do you want to break things down slightly differently? Do you. You know, of course. So, I mean, I get. I get all that. But ultimately, at the end of the day, the concepts in there, even if you want to move things around, the concepts are still true for what actually needs to happen, happen. And that's the big piece that we sort of teach in this is when you want to get into weird subtotal or into a subtotal here. And it needs to total to this number here. What's the formula you got to do to get the right things up above here? And if you're adding something back in twice, like your depreciation for your statement of cash flows, how do you handle that? Because that's not the way a financial statement or that's not the way a pivot table is going to work by default. It's going to want it in one place or the other. So how do we get it into twice and make it. So those are the kind of concepts we teach is how do we actually take control of these formulas to get the right thing in the right place and set the dimensional model up correctly to do it. So, like I say, it's complex stuff, but it was. It was fun to be able to go through and solve it. For me, like, that was the personal challenge there. And then, you know, seeing that people have responded really well to it when, you know, when they come back and go, ken, that was amazing. It's like, oh, cool, That's. That's awesome. I'm glad to hear it because for me, it was just. It was a challenge I wanted to get through. And, you know, well, great.

Well, yeah, I appreciate you sharing some on that. I thought that'd be interesting for some of your people. That may be, hey, I need to find a better way to do this, you know, dealing with modelers. I'm sure some of them are listening and going, I might have to take that course. That sounds interesting.

We're.

We're going to move into kind of the section where most of the rest of these questions are pretty standard questions we ask everybody. So the first one, what's your favorite shortcut in Excel?

Contrl T?

That's becoming. That's probably mine at this point. I. Every day, I'm doing that all the time.

I'm going to throw a caveat on this, though, because in my world, I have my monkey tools add and installed. And in my Control T dialog, I actually hijacked what Microsoft does for Control T. So in my world, when you press Control T, it pops up a box that says, what would you like to call your table?

I thought name the table as well.

Name the table up front. Right. And that's, that's a key thing. But yeah, Control T. I mean, you know, tables are the fundamental sort of layer for, for all data, for business intelligence and whatnot. So that's, that's definitely got to be one of my favorite. I mean, the other one I would say is now I know that Microsoft's actually put in a direct shortcut for Control Shift. I. I still use Control Alt VV because I'm, I've been doing paste special values for so long.

I do all. But yes, V. I get it. There's.

Oh, yeah, no. So, yeah, Control Shift V, I think now just does it with, you know, an office365, but try and retrain 30 years of muscle memory. Right. It's kind of hard to do.

That's my thing. I'm just so used to all dsv, I'm like, you know, okay, it might save me a split second. I don't feel like relearning.

Exactly.

So what's your favorite power query feature if you had to pick one?

I have to pick one.

Yeah, I know. It's like picking between your kids. I get it.

I mean, I got 30. Jeez.

Fine, I'll give you two.

Wow, that was generous. You know, can I, can I give you my favorite power query technique? I mean, it's, you know, not for mere mortals. I love creating a calendar table by just writing a nice little, you know, formula that goes curly brace, number dot from start date, dot, dot, number, dot from end date, close, curly brace, enter. That's one of my favorite things. Like I say, it's not for mere mortals. On that, you need to understand why you're doing it. But basically, when you've got your start date and your End date as separate date queries. At that point you actually get a calendar table that runs from start to finish. You do have to convert it to table and rename it. But that's just. Is brilliant. But you know, for general use, I'm.

Hearing that that technique from you. I haven't used it in years.

It's awesome learning.

I remember like how do you create a calendar table? And I saw it on your website.

Dynamic calendar tables start from the beginning of the fiscal year to the end of the fiscal year for all the data you have in your data. And every time you hit refresh it automatically refreshes. That's what I love about that one. But the, the other, I mean, you know, if I'm looking at sort of general features on things, I mean, you know, sort of the inbuilt. The fact that it records everything I do is absolutely brilliant. But you know, as far as as like the sexy ones, unpivot's pretty awesome. And the, the, the, the fill down, I mean is, is such a small one but it just is such a time saver. I think it's really cool. So I, I'll let, I'll go with those two for, for, for normal people that aren't quite as geeky as me. So love it.

All right, so we're gonna move to what we call our rapid fire section. I know we talked a little bit about this before, but I'll just lay out the ground rule. There's about 10 items I go through through. You have to pick a side on them or pick something. You get no more than 10, 15 seconds to answer. So no elaboration. And then at the end you can pick the one or two that you're like, I want to add more rapid fire section. I know we talked a little bit about this before, but I'll just lay out the ground rule. There's about 10 items I go through. You have to pick a side on them or pick something. You get no more than 1015 seconds to answer. So no elaboration. And then at the end you can pick the one or two that you're like. I want to add more context because it depends on all of these. I know that's the consultant answer for everything, but we're going to make you pick one side if you have to. So first one building. And this is building financial models. So thinking specifically financial modelers. Circular or no circular references. No circular VBA or no vba.

Happy with vba. Love vba.

Horizontal or vertical model?

Generally go horizontal. I think think all Right.

Dynamic arrays in your model. Yes or no where needed.

Absolutely.

External workbook links excluding power query. Yes or no.

Hard no.

That's the normal response. Using name ranges. Yes or no.

Absolutely.

That's an interesting one. You get a little different things there. Will Excel ever die?

No.

Will AI build the models for us in the future? Future.

I'm not allowed to say. Maybe. Right. Yeah.

If you had to pick a side, we can elaborate on that one at the end.

Yeah, let's. I'm, I'm going to say a qualified. Yes, but let's, let's elaborate on that one at the end.

All right, perfect. Should you use she sell protection in your models? Yes or no?

Yeah.

All righty. And then do you believe financial models are the number one corporate decision making taking tool?

I do.

All right. It's always interesting to see what people say. If they say no, I'm always like, well, what is it then? And then sometimes they're like politics. I've got that one before. Exactly. I'm like, hard to argue with that.

But don't. The part the politics based around the results of the financial model. Yeah.

What is your lookup function of choice? And I'm going to give you four options. If you want to pick a different one, you can. I know there's more than four. I've been told that many times.

Times.

Choose Vlookup, xlookup or Index Match. Slash Index X Match.

I don't have a preference. I don't care. I use the one that works for me at the time. And I think that this, I hate to say it, man, but I think this is one of the most pointless debates on the planet. Use what works.

I am completely with you at work. For me, it's more interesting to see what people say. I hate the debate I say all the time on LinkedIn. I'm like, look, I don't care which one you use. Understand all the different lookups, understand the strengths and weaknesses and then if it works for you, and you know what, you're doing great.

Three years ago I would say Vlookup. Now I lean more towards xlookup. But I still am happy to bust out a Vlookup. But qualifying that with I understand exactly how Vlookup works and I never forget to declare the fourth parameter. So, you know, as long as you know what's, what's going on in this, like whatever works. That's the thing that's important. So, you know.

Yeah, yeah, no, I, I appreciate that. So I know you wanted to elaborate on will AI build the models for us? So we'll give you a moment there.

All right, so I, I have some real mixed opinions of AI, and one of the, the things that I'm going to say right up front is that I think that AI is the greatest data theft in the history of the world. I, I know my website's been scraped in order to grab data. My friends websites have been scraped to grab data. And you know, you see opinions from Google, you see opinions from Microsoft that, hey, it's on the web, so it's free to take, even though it's got a copyright notice on the, the bottom. Now the question that, so this, that's my first political statement on that particular one. This drives me crazy. As a content creator, copyright was there to protect people like me so that we can still actually survive to provide you with more content. So having said that, will AI be able to build our financial models? If I look today at where AI is, AI is a fantastic tool for enabling people who understand their domain of knowledge to do things more rapidly so that they can get a first hit with which is almost right, so that they can tweak it in order to get it right. The important part that you realize about that is that the majority of answers are not right the first time. And just because they are from the stats, I've seen 70% of people take what ChatGPT gives them and just go with it without actually verifying it. And that's pretty scary considering that only 50% of the time is it actually right. So, you know, to me, that's, that's, that's terrifying. I think that the successful piece where AI can actually make a big difference to things is where it actually starts to get domain smarter. Not general answer question kind of things, but where we actually start to get things that are trained on correct modeling techniques and whatnot. But here's the thing I want to, I want you to ask yourself though, is that how many times when you get involved in a modeling project, can you go and buy a package off the shelf that does exactly what you you need? And the answer is, if you're here learning or, you know, participating in this podcast which is focused on building your own via Excel? The answer is none. Never. So is the AI model going to be able to build it for you? It's certainly not going to be a just make it hit, save and walk away. Will it be able to be built pieces for you? Yes, I think so. But you're still going to need to know how to put them together. And, you know, this is one of the parts that, that I'm actually really concerned about when I start looking at public opinion. And I've seen, you know, people who now say, well, I don't, I don't need to take a course because AI is going to do it for me. And I'm going, dude, you need to take the course more than you ever did before because AI is going to do something for you, but you don't know whether or not it's right. And you need to be able to validate these things to actually make sure that, A, the piece that you get is right and then B, how should you put those pieces together together. So I think it's, you know, I think there's going to be some great advancements in this area. I have concerns and certainly about the way I'm sharing my information this day to make sure that I'm sharing in a way that's not going to get hoovered up by somebody who's, you know, going to forget that I ever existed. But, you know, at the end of the day, yes, I think there's going to be advancements, but I want to put a definitely qualified yes on it. I think learning is going to be more important than ever to understand what these things are doing, to kick the tires on it, make sure they pass the smell test before you start adopting them in the real world world and in practice.

Yeah. And what I've said when it comes to AI is those who are going to benefit the most from it are those that are knowledgeable in the subject because they know how to push the limits with it. They could take it further, they can validate it, all those type of things. Yes. Can you benefit from AI even if you don't know how to do it? Yeah, you can. And there are times that some people are smart enough to at least be able to do a sense check and kind of, hey, do I have the right answer? But a lot of people don't. And that's where it's really dangerous one, if you don't know if it's right or wrong.

Yep, absolutely. Yes, you definitely do. The other thing that I want to just sort of throw out there for people that are watching this thing is just sort of an awareness. So, you know, for the content creators that are out there, please don't stop visiting them because, you know, at the end of the day, a lot of us are the ones that are actually putting this information out that is being, you know, consumed by these machines. And I'm Seeing more and more where these, these things are coming up as results. But there's no link back to the original people that create them. And it's really difficult for those people to continue to do things if, you know, the systems just turn a blind eye to those that are actually feeding them the good information. So, you know, keep that in mind as you go through. I mean, you know, it's, it is what it is in a lot of ways. I don't believe it's right, but it's an appeal from an author out there. So.

And I, I appreciate that. It's a good point there and I think we'll wrap up on that note. And the last question I want to ask you, you if our audience wants to learn more about you, the resources you have, is the website the best place to do that? LinkedIn. How should they kind of reach out if they want to learn more?

You can definitely find me on LinkedIn. I've got, I've got my profile up there. So yeah, that's definitely one place. I would certainly recommend going there over, over some of the other social media sites today. The other thing is you can always check out my website for our training material. You can find us at just HTTPs Skillwave Training Training. If you're interested in learning more about my software Monkey Tools, that one is at HTTPs. It's monkeytools ca because we're proudly Canadian. And then if you're looking, I have another website as well that I run started. My original one actually is excelguru again ca dot com. But yeah, you can connect with me at any of those locations.

Great. Well, thank you for joining us. I really enjoyed chatting with you and I'll just tell our audience if you've been considering learning Power Query Start today. Ken's a great resource. There's a lot of others out there. Just start.

Absolutely.

So thank you for joining us, Ken. I really appreciate you carving out some time.

Absolutely. Thanks for having me on. I appreciate it.

Financial Modelers Corner was brought to you by the Financial Modeling Institute. This year I completed the Advanced Financial Modeler certification and it made me a better financial model. What are you waiting for? Visit fmi@www.fminstitute.com podcast and use code podcast to save 15% when you enroll in one of the accreditations today.