Packed insights for Excel in FP&A: Jeff Gudim
50:47 Share

If you would like to earn CPE credit for listening to the show, visit earmarkcpe.com slash FPA. Download the app, take a short quiz, and get your CPE certificate. Finally, if you enjoy listening to FP&A Today, please go to your podcast platform of choice, click the subscribe button, and leave a rating and review of the show. And now, on to the show. From Data Rails, this is FP&A Today. ♪

Welcome to FP&A Today. I'm your host, Glenn Hopper. Today, I'm joined by Jeff Gooden, a senior financial analyst, Excel evangelist, and hardcore financial model architect. Jeff made the leap from private wealth management to corporate FP&A, leveraging his deep financial planning skills and passion for Excel to quickly rise as a data-driven problem solver.

Jeff brings a unique blend of analytical rigor and spreadsheet creativity to his work at Strategic Education, where he leads forecasting and analysis for a $100 million IT function. He's found some pretty cool ways to push the boundaries of what Excel can do, whether it's building dynamic dashboards, automating workflows with Power Query, or developing Monte Carlo simulations from scratch. I thought today would be a great day to just dig into some Excel nerd stuff, and I think we have the perfect guest for this venture. Jeff, welcome to the show. Thanks, Glenn. Happy to be here.

You know, we talked a fair amount about this the other day, but for our audience, walk us through your career background, starting with what you're doing today at Strategic Education and kind of your career progressions to this point. Sure. Yeah, yeah.

Like you said, I'm a senior financial analyst, strategic education and strategic education is a publicly traded for profit university. So that's kind of the background there. The senior analyst role, I am supporting the IT function. I'm the primary analyst supporting the IT function. Like Glenn said, it's about 100 million between CapEx and OpEx. I've been at the company for about three years, which is also the point in time where I kind of made the jump from private wealth management to FP&A. Obviously, there's definitely some finance and

analyst adjacent work that I was doing in private wealth management. But prior to that, I did not have any direct FP&A experience. And I really credit my kind of the point of this podcast. Like it's been my experience in Excel that's kind of allowed me to rise as quickly as I did. In fact, I actually credit my experience in Excel for landing me the job at strategic education in the first place three years ago. I started out three years ago as sort of like a junior analyst. It was a kind of poorly defined role. About a year after that,

Made the bump up to full analyst and about a year and a half after that, I bumped up to senior analyst where I am now. Like you said, prior to that, I was in private wealth management, kind of did the associate financial advisor thing, doing a lot of retirement planning, investment management, analyzing mutual funds, ETFs, incorporating that kind of thing into client reporting and building out retirement and estate plans.

Gotcha. And we talked about this a bit yesterday, and I think we'll get into some of the modeling you've done. But I think, you know, a lot of wealth advisors and people in that space are more, almost become more salespeople. And that was why I had to get out. Yeah, I hear you. I was, it was not really working for me. It's a great industry. I made a lot of good acquaintances there. Just wasn't my cup of tea. And I probably honestly spent longer there than I should have. Yeah.

Yeah, I hear you. I hear you. And it's funny, like if you're a model builder at heart, it's hard to keep you out of that and be in that place where you're, you know, really more on the sales side. So I totally get it. And I think we could spend a lot of time today talking about your career. But after we talked the other day in the pre-call, I thought, no, we're just going to geek out on Excel today. I love these kind of episodes, you know. So you're an Excel evangelist, I know. And at Data Rails, we love Excel. And, you know, I'm a huge AI, generative AI evangelist myself. However.

However, every project I do seems to start in Excel. I'm not picturing Excel going away for any of us. But when we were talking before the show, I loved your origin story with Excel was pretty interesting. So can you say what it tell us what it is about Excel that kind of that made you fall in love with the tool and become this Excel evangelist? And how did that passion begin? You know, I thought I was thinking about it this morning going even further back. As a kid, I really had I was not a techie.

type kid. I open, I remember opening up Excel, you know, when I was 10 or 12 and just being clueless, like absolutely no idea, you know, it was giant mystery to me. The only experience I had with Excel growing up was I have a cousin who has his PhD in meteorology and he's the type of guy who's like, he's actually in the models. He's not like presenting on TV. He's like, he's like a researcher in the models. And he would build this score tracker for this family, you

tournament that we had every Christmas. And he built this complex score tracker in Excel that I always was fascinated with as a kid, but had no idea how it worked. So my first experience where I really started to love Excel was I was working a job between colleges, kind of like we talked, like you and I were talking about before. I kind of also was trying to go the philosophy route before I realized that that wasn't going to pay the bills. And so

I was between colleges because I did a whole big college transfer in there too. Between colleges, I was working a job as a cost estimator at a manufacturing company. So I was, my responsibility was I would get requirements

request for quotes and I would turn around our formal quote. So it was estimating the cost of extruded and die cut rubber and plastic products. And I worked for a guy who had built this big essentially a calculator in Excel. And this was, you know, this was back in like 2010. So we didn't have like, we didn't have all like the fancy functions that we have available now. This was all VLOOKUPs and old SUMIF and all that sort of stuff. But the calculator was

It was just fascinating. I loved seeing how just everything, you know, we had all of our VLOOKUP tables, we had our margin calculation, we use Goal Seek every so often. Like, I just loved seeing how all that data just kind of came together in that final number, right? We have all these variables, we have all these inputs, all these dropdown menus, and it all just kind of like came together in that final number that I then pop in our quote and send it back to the client. And that was like the first time

that I really was like, wow, this is really cool. And like I said, this was actually before I started my finance degree. So by the time I got in college, I was taking like spreadsheets courses in my, as part of my finance program, they were just worthless compared to kind of what I had already been doing. So yeah.

That what I love about your origin stories, because when we were talking about it the other day, I thought, oh, yeah, you would like everybody, you know, get exposed to Excel in the finance courses. But no, you had actually used it before. And without the courses, like when you figure out on your own how to make Excel do really cool things and you're not doing it just and it's actually helping you in your job. It just it's so much more motivating than you're, you know, doing some case study or something in your work working with Excel. So yeah.

So you had a need and Excel solved it. And I think that, yeah, that made it connect for you. Well, on that topic, and we talked about several of the models you'd built, but tell us about your favorite model you've ever built in Excel and what made it so cool or fun to create.

Yeah. So this would be going back to kind of my days, maybe somewhat surprisingly, right? Going back to my days working with financial advisors in the private wealth management industry. I just, one day I had this sort of this, this light bulb moment where I'm working in these very, very advanced retirement planning areas.

software platforms. There's various, various ones like eMoney, MoneyGuide Pro, things that were used for like very complex estate planning. But I was working in those one day and I just had the light bulb moment of kind of two things. One, I was like, it would be really nice if you could get one of these that was just a little bit simpler, right? Because for those, you had to enter hundreds of very, maybe

maybe not literally hundreds, but it was tons. It was dozens at a minimum of variables before you could get any sort of like output. So it took several hours and talking with the client. Then you had at least another hour or two of massaging the data in the program. And I kind of had this thought of like, could I do this? Could I just get like five details from the clients and make sort of an educated guess at whether or not they'll be able to retire on the timeline that they want. And then number two was like, I could totally build this in Excel and I

it would be really fun to challenge myself by trying to build a Monte Carlo component to this also in Excel, because these, you know, these platforms had Monte Carlo analysis, right? Where in this setting, the Monte Carlo is you're running out, you know, a thousand or 5,000 or 10,000 kind of depending, whatever different scenarios for market, like annual market returns, you know? So you're essentially, you essentially have to create a scenario where it's like,

I have to create a thousand different 80 year periods with market performance, you know, randomized, but randomized, you know, within, within, with certain controls. And so I was like, this would be really fun to try to create this in Excel. One, try to make something just a little bit more simple and to build a Monte Carlo component. Let's try to do this in Excel. And so I, and I did, I built it, it fully worked. I actually epic failure, but I launched a company around it trying

trying to do it. Wasn't the right time, didn't have enough runway. It was a terrible life decision, but it was fun. Because the model worked well enough that I could actually present it to people and be like, hey, this is legit. I can get your details. There's five or ten details from you, and I can give you a Monte Carlo estimate. The nitty-gritty to kind of get... Because we're going to nerd out about Excel a little bit. So the nitty-gritty...

of how this worked was I to create the Monte Carlo side of it was I essentially used the the Rand function. It was a nested Rand function, basically. So it was a whole bunch of logic with the Rand and the Rand between functions to kind of guide the randomization of market returns over my

In this case, I did 1000 different 80 year periods. I know it's very complicated because I had to do a whole bunch of market research and be like, okay, how many years out of every five years or every ten years, how many of those years have a negative market return? Right. So I have to kind of set that limiting factor. And then I also have set the limiting factor of like, well, I can't have it showing a 200% return, and I also can't have it showing a negative 80% return. It's like those never happen.

So I kind of had to build those in, but I built out this kind of like this complex logic using nested RAND functions and a whole bunch of like if then statements. Now, because the RAND function is a volatile function in Excel, which means it recalculates every time you make any change at all to the spreadsheet. There's a handful of these in Excel. The RAND and RAND between functions are one of them. The indirect function is another one. And there's a couple of

a couple more because it does that. Ultimately speaking, I actually just created my scenario and then hard coded the results. I hard coded in a thousand different 80 year scenarios. And then I would just tweak kind of like the variables per client and they would each be assessed kind of against those thousand different hard coded values. So that was kind of the solution I built. That's how I did it. It was fun and really fun to like

have a product that I didn't excel that was like, I didn't even know if it was possible, but it's just like, let's just try this. I love Monte Carlo simulations also. I do them all the time. I love them for scenario analysis and I'll be in just doing forecasting and planning. I'll be like, I don't know, let's run out some Monte Carlo and do the, you know, the 95% confidence interval and all that. And I think about like in Python, you know, it's pretty straightforward. There's a, you know, you use NumPy. There's like

just generating the random numbers and you can put a, you know, random seed. Yeah. And if I were doing it today, I would probably do it differently. I would probably try to use, I would probably try to rely on Power Query because that way I could restate, I could restate the whole, whole model and it only takes, I mean, 80,000 rows in Power Query is nothing. So like I could restate my entire model in one second because

Because like having the volatile function, like I tried it and it was like, it just crashed. Well, it didn't technically crash, but it took way too long. But then the other thing was building it in Excel. Like you actually, because you're down in the weeds like that, you actually, you get like a more fundamental understanding that if you just write a couple lines of code and do it, you're actually building it out. And you're sort of, because of that, you're seeing the value, you're understanding Monte Carlo better. And it's like, oh, now I get it. This is why we would run it a thousand times. Yeah, yeah.

Another thing, and we could go into models all day, but one of the things that just kind of came up naturally when we were talking was you mentioned a couple of little hacks that you do in Excel that save time and make your work easier. And I thought that would actually be a great sort of

sort of FP&A newsletter where it's just, you're in Excel all day. What are some ideas of time-saving tips or whatever? And so we talked about, and I thought our listeners would really appreciate this. If you've got, say, three time-saving tips for Excel, stuff that you use all the time, maybe underrated features that you could share with other FP&A analysts, what would those be?

Sure. Yeah, no. Yeah. And it's so funny on the note of like a newsletter. What I've actually started doing on my team is every it's random. I don't I don't have a cadence to it at the moment, but I randomly just spam out to the team. I was like, hey, guys, like, I think you should know.

about this like new feature that I've been tracking in Excel for the last year. Like, I think you should know about this. Like, so, and I kind of highlight it for my team. It's kind of fun. I'm definitely, you know, the sort of like the token Excel guy on my team. But yeah, so top three, I would say first one, utilizing kind of some of these new functions in Excel, these sort of they called dynamic array functions,

There's a really nice kind of combo that I use a lot for doing really quick and dirty analysis where say you've got a column with a whole bunch of repeated values in it. It's like you want to extract the unique values, right? You used to be able, Excel's been able to do this forever, right? With like, you know, remove duplicates. But now we actually have a function that does it.

And so you can use the unique function to just unique is a single argument function where you just type in equals unique. You point it to the column or kind of like the range where you want to grab unique values. You close a parenthesis, you hit enter and it will, you know, getting into kind of like how these dynamic rate functions work, your results will actually spill down the column.

the number of unique values that you have in that data set. So the unique function is huge and helps. I use it all the time when I just need, I just need to know the unique values here. Let's say, you know, I have a list of like cost centers, right? I got to call them. Maybe I have a bunch of flat data where I have cost center in one field, but that cost center is repeated. And I just want to know which cost centers are in there. Put unique on that. Boom, done. It's like takes two seconds. And then

in combination with that, you can put unique inside the sort function to get your unique values sorted, ascending or descending. Um, and that's also super easy, very simple. The sort function is just two arguments. You have your range reference as your first argument. Your second argument is just a one or a negative one, depending on what you want your data to be ascending or descending. So sort unique, great, quick and dirty analysis. Number two, I,

I'd say get your, this is a little bit more like process focused. Get your data into tables. Like if at all possible, your source data should be in a formal Excel table. Some like sometimes it's not possible, right? Like I use a lot of, I use a lot of data that comes in through an add-in. I don't know how all add-ins work, but in my case, the data is

the data can't be placed into a table because that add-in has to refresh and, you know, it just doesn't work. So there are some cases where you can't, but if at all possible, you know, get your source data into a formal table so that you can use what are called structured references in all your formulas, where when you're typing your formula, you don't have to type in, you know, like A1 through B10. You can type in, you know,

the name of your table with the name of the column. So you're building your sum Fs, right? You have your criteria column. You can actually specify the name of the criteria column and the name of the amount column and all those things. So when you open that file back up a month later, you know what your formulas are doing. So that's a good one. And then I'd say just like this one's high level. I would kind of expect certainly anyone who has like five to 10 years of experience in FP&A, most people are probably doing this already. But if you're not,

definitely use like the, what I would call kind of like the simple keyboard shortcuts. I'm not one of those people who's like, you should never use your mouse. Like it's not, that's not me, but there are some really simple ones like the control shift arrow buttons to navigate like contiguous data, um, to kind of like select or kind of like navigate between like different data on one sheet. Uh, control shift arrow is kind of a must in my opinion.

Using control spacebar or shift spacebar for selecting entire columns, entire rows. Again, I would kind of assume like if you got five, 10 years in FPNA, you probably know those. The one that I would highlight though is control shift V, which was just done in the last like year or so where we can now paste values with a keyboard shortcut. That's like, that's a big deal. That's relatively new. So if you're not doing control shift V for paste values,

That's a must. I love that one because I'm just picturing back, you know, it's always the right click. I know. God. And the other thing, so years ago, I remember, you know, when I worked with a bunch of analysts back when I was in telecom, there were several people,

on the developer side and on the Excel side who do things like, you know, smash their mouse or hang it, you know, on a noose on their cubicle or whatever. But I was thinking as you were talking about sort and unique, like how frustrated I would be when I would watch, you know, people like fumble around with my spreadsheets and I'm like, just get out of the way.

But as you were talking about those, I was picturing my process of going through, you know, highlighting the table, filter, then sort, then copy, you know, copy visible rows and paste over just how clunky that is and how much more time that would take than sort and unique. But I'm, you know, I've hit that point in my career where I just don't spend the time in Excel that I used to. And I'm embarrassing when I get in front of it. Yeah.

Well, it's funny. That's my, that's my supervisor. My supervisor is a senior director. She's a CFA, uh, very, very, very, very good on the engineering kind of like, and she always wants to nerd out about Excel, but she's just, you know, she's in a position where, you know, she's not really valued for her Excel expertise. Yeah. Yeah. When,

When my default is still to go back to VLOOKUP and everything you were talking about back in the day, that was like my Excel heyday back then. So I still have that muscle memory, but the new stuff, it's harder for me to keep up with. But yeah, control shift V, I've got to remember that for pasting the value.

You mentioned dynamic arrays and Lambda functions. I want to go into that a little more. I know probably most of our listeners are familiar with those, but walk us through what dynamic arrays and Lambda functions are, and then how these newer Excel features have changed the way that you build models. Yeah. It's a great question because this was what I would call a fundamental change to how Excel operates. This happened in 2018. In 2018, Excel

or Microsoft kind of made a pretty core change to how Excel handles what are called arrays. An array is just columns and rows of data, right? And Excel always had array handling capabilities. These were just the old control shift enter formulas where the entire formula would be put in like the curly brackets. If you wanted Excel to handle an array or trying to process the formula as an array, an example of this would be

Say you wanted to multiply two entire columns against each other, right? Some product does that, but like there are other scenarios where you might want to do that inside a function that is not some product. And so you would have to tell Excel that you want it to process that as an array and you actually want those two whole columns multiplied against each other at the row level.

And so like the old way to do that was you had to like hit control shift enter when you entered the formula and it would put the curly brackets around your formula, whatever. And that's kind of how it was handled. But Excel had been a pretty core change in 2018 where they said you can now, not only can you just use arrays across all the, well, not all the formulas, but across most of the formulas, you can just use arrays. You don't have to do control shift enter, but they introduced a new kind of type of function where the results of the

Well, it's not even a type because some of the old functions do this too now. But it's a new way where if the output of your formula is an array, right? So the output and a really good example of this is like, let's suppose you have a column that has the names of months and you have, say, 10,000 rows in this one column and you got January through December just all over in there, right?

If you go into another cell and you type in equals column A, and then you type in equals again, and you put January in double quotes, so it's a text value of January, and then hit enter, what's going to happen is Excel is going to spill down the 10,000 rows directly underneath the cell where you entered the formula are going to contain a whole bunch of true false values, depending upon whether the value in that row is

from column A is January or not. And that's a new thing. So Excel also introduced the spill error, which essentially is an error that shows up now if you don't have enough space on your spreadsheet for the data to spill into. So if you've got like data, maybe you have data below a function where you have data that's going to spill, the spill error kind of comes in and tells you

We can't do this right now because you've got data where this formula is going to spill down to. It gets a little bit interesting. It's much, much easier to demonstrate on a spreadsheet. It gets a little ethereal trying to explain it. But functionally, you now have this system now where Excel handles arrays really well. You can do lots of kind of like you can do what you might call matrix math now.

inside the formulas themselves, which hasn't, you know, hasn't really been possible. And Excel is, they keep adding things like, um, they have like, say the V stack function now where you can actually take, say you have like two tables that have identical columns, right. And you want to append them, um, where you can do the manual copy paste and, you know, put your table together, or you can actually just use the V stack formula. You

You could say equals VStack. You put in the name of your first table, the name of your second table. It can also be range references. Close the brackets and you now, your data will now be stacked and you'll get both of those tables in kind of one single range. And you only ever enter one single formula in one cell. And the rest of that data just spills all the way across your sheet. So fundamentally, that's what these dynamic array formulas are doing. And the Lambda functions are

There's kind of a whole bunch of, there's at least like three different types of Lambda functions kind of in Excel. I'm only going to focus on like the sort of like the simplest one, which is what I might call like the built-in Lambda functions where you now have functions like by row is a really good example where you can actually tell Excel, I want to sum by row on this array. So I have this array that's going to spill across my spreadsheet and I want to get the sum of

of that row in a new column that doesn't actually exist anywhere except for in the logic of the function, right? And so, and you can create that. And so then it will spill all your data. You've got all your columns and all your rows, and you got this extra column out off to the right that has the sum of all those different rows. But that is that some column is created entirely inside that single formula you entered.

over in that cell where you have that dynamic array formula. So that's kind of the power that you're working with. That's the whole new world that kind of everyone is still figuring out. Because I would say I've been pretty active in the LinkedIn community in Excel for the last couple of years, and I would like several years. And I would say the dynamic array formulas really didn't start gaining traction

until, I don't know, like three or four years ago. They were technically available back in 2018, but it took a few years for people to kind of fully digest what this meant. And now we're kind of really getting this age of like people like, wow, you can do crazy stuff.

As an example, right? So as an example of something that I've done, something that I've done in my work as an FP&A analyst on this topic, there are a couple of really cool formulas. One of the really cool formulas that Excel now has is called the let formula. And let allows you to assign variables that are local to the cell in which you're typing the formula. So these are not variables that can be called anywhere else in your spreadsheet.

um, anywhere else in the workbook, but you can actually assign variables. So say you have a, a match operation or maybe a V lookup operation, but you want it to, um, it's going to like repeat a whole bunch of times, or it's going to, or you're going to kind of like use it in some, uh,

logic that's going to be repeated. And I'll kind of give, I'll get into a specific example here in a second. But you can actually assign that match operation or that VLOOKUP operation to a variable. And then at the end of the let function, you can put in kind of like the calculation steps. So rather than having to

Rather than having to paste in this long VLOOKUP or this long index match match thing, you know, and you get this formula that's really complex and just it is tons of range references everywhere. You get this formula that has really clean names and you can kind of tell, OK, like this process, you know, this name means that we're getting this piece of data in this part of the process, so on and so forth. It's really clean, really clean.

What I've done in a very specific example is I'm pulling a lot of data in an add-in. And so in my case, I can't use the nice layout you get from a table. Right? So in my case, what I've had to do is I've kind of had to rely on kind of the old fashioned dynamic named range of using offset and counter to capture the data that's coming in from my add-in. But I would like to create a report

where I can copy the same formula that essentially performs a sum ifs operation on certain columns based on the quarter, right? So I'm preparing like a quarter summary or summary by quarter. Summary by quarter. My source data has quarter columns, but since it's not in a table and some ifs actually can't work with arrays right now. So like you, it either has to be like a range reference or a table reference in order to be in some ifs. So it's like,

I have to somehow bring this like, you know, bring these array quarters onto my report spreadsheet. And you can write the shortcut for this is like either you just hard code in the range reference and you do some ifs anyway, or you hard code, you hard code in some sort of column space

match number that just sits in a helper cell somewhere. So you, you, you know that you're going to match that. But I really, I really don't like that solution because if your source data ever changes and you just forget to account for it, all your numbers are messed up and you never know. A solution I have is to use the dynamic array functions and put essentially, I'm going to try to simplify this because I think it'll be too complicated if I explain the whole thing, but use a,

There's sort of a new function that's called choose calls, choose columns, that allows you to specify which column in an array you want to grab for your sum operation. And so using that in combination with the match function where I say, hey, match on, in this case, like Q2, right? Match on Q2 in the headers column.

for my source data, put that inside the choose columns function. Now I have a formula that is completely dynamic to my source data, dependent on the sort of the column headers in my report that never has to be updated.

It's always going to either show the correct value or an error, right? And that's like, in my opinion, that's ideal, right? You don't want your, to the extent possible, you never want your formula to be able to give you an answer that's totally wrong.

FP&A Today is brought to you by DataRails, the world's number one FP&A solution. DataRails is the artificial intelligence-powered financial planning and analysis platform built for Excel users. That's right, you can stay in Excel. But instead of facing hell for every budget, month-end close, or forecast, you can enjoy a paradise of data consolidation, advanced visualization, reporting, and AI capabilities.

plus game-changing insights giving you instant answers and your story created in seconds. Find out why more than a thousand finance teams use Data Rails to uncover their company's real story. Don't replace Excel, embrace Excel. Learn more at datarails.com.

It's just so interesting walking through the history of, as a user of Excel, how they've modified over the years. And I know another one that I always think about is, you know, VBA was huge back in the day. And I know we're past the heyday of that, but kind of along the lines of that, how do you think like the shift from that legacy VBA toward now Excel?

Power Query, dynamic functions, and even like the co-pilot assistance. I mean, what do you think about the shift and what are you seeing now? And are you, you know, because it's crazy, Excel just continues to evolve. It is. I mean, honestly, I think we're in a really cool sweet spot right now because, you know, if you pull the LinkedIn Excel community, there's kind of the assumption that VBA is going to probably go away completely at some point. It's not in the next year, not in the next five years, but probably at some point VBA is going to go away the dinosaur.

And because of that, though, we're at this really unique point where it's really cool how much automation, just how much you can automate right now for a relatively small amount of work is amazing. You no longer have to rely on VBA to transform data, right? You can do tons of data transformation, data cleaning in Power Query. You can save all of your SSRS reports in a single folder somewhere, and you can have Power Query go out and grab everything

everything that's in that folder, append it all into one table, put it into PowerPivot where you can build a relational table structure, bring that data into your spreadsheet via a pivot table where you have all sorts of tables that are connected and linked, right? You can do that in terms of like data transformation. Then where you want to automate anything that's point and click, you can go to Copilot, you can grab your code. Like, I mean, it's like, you know, you have this really, it's this really cool situation we're in right now where you can do tons of workflow automation at Power Query. And then if there are

sort of point and click changes you still want to make where you would need some VBA, you just go to Copilot and you can grab, I mean, relatively, you know, anything that's like kind of beginner to intermediate VBA, I would say Copilot can easily provide right now. I've done it numerous times in the last like year or two where it's just like, oh, like I have this macro because I'm not a heavy VBA user. I kind of came into Excel kind of past the VBA heyday. But

But you can now, you know, you can go in, use Copilot, bring that code in seamlessly. It works great. I've done this numerous times. Just like randomly, I'll be sitting there. It's like, oh, it'd be really nice if I had a button that, you know, did this like relatively simple operation. But it's something I do like dozens of times a month. Right. And you just do that now. Super easy. So I love where we're at, even though VBA is probably on its way out in some way. Yeah. Yeah.

So Microsoft put 15 billion plus or 15 billion initially into open AI and they've done their acquires and, you know, they're all in on AI. And I think rightfully so. But Copilot getting it to write formulas is one thing. But the integration, I'm kind of surprised. I don't know. That's not really fair because AI is moving so quickly. Yeah.

that there are so many things happening, but the AI integration, co-pilot integration directly into Excel has been limited. You know, the whatever, co-pilot for finance is pretty limited functionality, right? It's really not anything that a power user would benefit from. But I wonder, what I think that Microsoft is picturing is, this is like Clippy's revenge. Remember Clippy from the, what was that, the 90s, early 2000s? Yeah, yeah, yeah, yeah.

But all the stuff that you've been talking about and all the domain expertise that FP&A people have kind of built over their whole career, I wonder if are we going to get to a point where with generative AI, you know, you're just going to come, Clippy is going to pop up and you're going to tell Clippy what you want. And it's going to go do all this complex stuff and bring the data to you. I don't, do you think that's where we're headed?

I think so. I think that's where it's headed. I mean, certainly. So I haven't experimented with Copilot in Excel. With Copilot, I'm saying I'm still going out to Bing or whatever and grabbing and just interacting with the messaging side of Copilot. I know that Excel has Copilot in Excel.

There's a guy on LinkedIn, David Fortune, he's Canadian, I think, has covered the Copilot stuff. And from what I understand, you can even do some of that with Copilot in Excel right now, where you can be like, I need this data to be summed in this way, right? And it'll actually provide you the dynamic array function that you need to use. And so I know that is happening to a certain extent. I haven't personally played with it much because kind of to your point, it's like the use cases for me are probably somewhat limited, but I know it seems to be going that direction.

Yeah, and it's similar to data science. To be a really good...

FPNA Pro, you, throughout our whole careers, you've had to be really good at Excel. You just, you have to, I mean, or it's going to take you forever if you're manually trying to bang through and you don't know these automations. And it's kind of like on the data science side, to be really good at data science, you had to know Python and be able to write SQL queries and, or, you know, R and other components of it. But what's going to, it's going to be super interesting. And I don't know how long it's going to take. Maybe it's one year, maybe it's two years, maybe it's

eight years, I don't know. But this barrier to entry of being able to do all the cool stuff like that you've been talking about in Excel, that's going to go away.

And anyone will be able to do it. But there's also behind it, there's also that same thing that made you power through and figure out how to do Monte Carlo simulations in Excel. You've got to have that sort of modeler's mindset and that engineering problem solver and all that. So even if this stuff gets more automated, you're going to have to still have that sort of logical thought.

I think, I don't know. I mean, unless you just end up with the Star Trek computer that you just bark out what you want and it goes off like an agent and comes back, you know, two minutes later with a complete, you know, incredible model that, you know, would have taken a human 20 days to build or whatever. Yeah, no, I agree. I agree. It definitely is going to take, right. I mean, you're still going to need that mindset, right? Even if you are, even if you aren't somebody just a glorified prompt engineer, you know, you're still going to need kind of like that data and curiosity mindset. Completely agree.

Yeah. And so, and you're, you're really pushing the limits of what you can do in Excel. And we talked a little bit also about Power BI. And I know you've built a semantic model in Power BI that integrated your forecasting software, GL and vendor systems. Maybe talk through that a little bit. And the reason I'm shifting to this now is just thinking it's great to have the Excel skills, but also right now, because with era of big data and all that, and as more and more is getting expected and everybody's talking about AI,

well, AI could be a lot of what we were doing with machine learning in Power BI. Walk through that and the integration and crossover from Excel into Power BI. Yeah.

If I have my timeline straight, I believe Power Query was first built in Excel and then Microsoft used it to build Power BI. I think that's right. Yeah. I believe that that was the timeline. And so, yeah, I built a big semantic model in Power BI, sort of in the same way that Excel kind of like really got me into data. It was Power Query and Excel that really kind of got me into Power BI, where I started automating some workflows using Power Query and Excel. And then

And then when I kind of figured out that like the hard part of Power BI is just Power Query. So I was like, once I kind of figured that out, I was like, oh, hey, well, we could just, we should just be doing a whole bunch of stuff in Power BI. So I actually, I was pretty instrumental on my team. I pushed pretty hard and ultimately convinced the senior VP of finance that we should really put some time and resources into building out some Power BI models, which was, you know, it was mostly, it was me. It was, you know, I was totally just volunteering to do the job, which was fine with me. I loved it.

But yeah, so I built this big semantic model in Power BI utilizing a lot of the a lot of kind of like the skill set I had honed in Power Query in Excel, Power Query and Power Pivot, because a lot of that is the same. Right. So you have Power Query where you do a lot of your data transformations and then you have Pivot in Excel where you can set up table relationships. And those two steps are replicated nearly perfectly.

in Power BI. And so when you, you know, if you, you log in your Power BI desktop and you go into like your, oh, I think it's like, forget what that button is called, but like edit data or whatever. And that brings you sort of into the Power Query interface where you have, you know, you have all your tables over on the left, and then you have kind of all your transformation steps for each table over on the right. And sort of, you know, in the middle, you got your, you know, your big, the big table, which kind of shows the preview of your data.

So yeah, I built this, I kind of used my knowledge of Power Query to build this big semantic model, kind of like you referenced. What we were trying to do is we were trying to bring in, we had various attributes in our vendor system. Our vendor manager is Coupa for managing our invoices. And so we had various attributes in there that we wanted to bring in at the line transaction level on our general ledger. So that's,

the way we've been doing all of our general ledger reporting, you know, so far and for all of history was we'd go out, we'd manually load an SSRS report, you know, trial balance that gives us our, you know, gives us the, what we're looking for in the time period we want, right? So the

So the goal was, we need to replicate that. We need to make sure we're pulling the same numbers as that report. But we also want to add some line level detail in here so that when you're looking at transactions, you can say, oh, this is the purchase order number that that transaction is assigned to. Or this is sort of like the corporate sponsor from the business unit in the department where

kind of where this invoice is coming from. We wanted to add that line level detail. And then we also wanted to be able to summarize our general ledger

by various P&L categories that we work with in our forecasting software. So it was bringing those three things together, which we did very, very complicated, right? I mean, I think some of the more challenging things about that were identifying the unique identifier that you need to use for all of your table relationships, right? And oftentimes that was not very straightforward. Oftentimes it took several transformation steps. You had to go out to, well, like,

Part of the unique identifiers in one table, part of it's in another table. You either merge it in or you use M code to pull them together. You can cat them, whatever. There's lots of stuff like that to get the data into a format where we could identify and map all those table relationships.

So kind of a three-part question I'm going to throw at you here now. So one, like the kind of the mindset shift going from Excel to realizing, going from Power Query to realizing, oh, wow, there's a whole new world with Power BI. So there's the mindset shift and then what really the biggest unlock was there. And then I guess to kind of put a button on it or a bow on it, if somebody is great at Excel, but new to Power BI, how would you advise them to sort of,

make that shift, a word of wisdom for someone who's crossed that chasm. Yeah. If you don't mind, maybe I'll start with kind of like that last one there, because I kind of touched on it, honestly, in my last one and in my sort of in my sort of previously where if you're good at Excel, absolutely start using Power Query because Power Query is where you can really bring some substantial data transformation and automation into your Excel workflow. Like I said,

Most finance people listening to this probably have some monthly systems where they are going out and they are grabbing files that are saved in certain folders. And it's a monthly process, right? The file is always in that folder. The file has the same name, or at the very least, the file has the same internal structure, right? It's a CSV. It's got 12 columns. They always have the same name, whatever. Almost everyone listening to this probably has some type of process that they follow on a monthly or quarterly basis that the

that takes that form in some way, right? Well, you can automate that entirely in Power Query. And you can just, you can have Power Query go and point to that folder, grab everything there, bring it through some transformation steps. You know, you can kind of do what might otherwise kind of be, you know, maybe you relied on like VLOOKUPs to kind of like add dimensionality to your table or, you know, you're removing duplicates or you're, you know, you wanted to, maybe you can cat like two or three different columns together. Like all that stuff could just be knocked out in Power Query.

And so then you can bring that data into your spreadsheet in a totally different kind of a cleaned form already. You don't have to rely on a whole bunch of like manual transformation steps anymore. So do that. And then once you kind of have that,

You can totally leverage that understanding of Power Query because like I said, that is the hard part of Power BI. The visualization, kind of like the sexy side of Power BI, right, is very easy. Like, you know, once you have your data clean and once you have a structured well and you have your table relationships built out,

The visualization side of PBI is easy. It's pretty much just drop it in, put the right field in, maybe update some of the formatting and boom, and it just looks amazing. And Power BI is much more seamless on the data side than Excel is, sort of for better or worse.

I've definitely noticed like Power BI is it's just more seamless when you're like going out and connecting to data sources and stuff. Power BI handles the connections a lot better, but you can totally leverage knowledge of Power Query and Excel to kind of become good at Power BI because it is the

Getting good at Power Query in Excel means that you've mastered the hard part of PBI. So a little bit different episode, and I love these. I think we might need to start doing these more often, honestly, because this is, you know, Excel is the language that we all speak. And so we focus today on Excel to the exclusion of all the other things we do in FP&A, of business partnering and of, you know, storytelling and all that. And

I think though, and we talk about all those, whether it's soft skills or different management styles and all that, but what we've talked about today, this is the kind of the core fundamental stuff that we need to be able to do. You then have to layer on, you know, the business partnering and the storytelling and all the other, you know, sort of forming a narrative from the data and all that. So I guess before we get to our sort of boilerplate questions that we throw out to everybody, to wrap all this up,

I mean, I know you've carved out a niche as the go-to data guy on your FP&A team. And obviously, because you have all these skills, people could be driving around using the same Excel you are, but if they don't have the skills, it's going to take them a lot longer to do the exact same thing. So with these skills, I guess...

What advice would you give someone trying to build out that reputation and to be the master of this platform beyond just all the soft skills that we talked about? Because maybe there's a caveat here. When you're talking to senior management, they don't care how the sausage is made. They just want great sausage and all that. You're in an interesting spot because I have a feeling that where you work, you probably are the guy that everyone would go to to answer any Excel question and all that.

Yeah. Oh, no, it's good. It's a great question. And actually...

crazy. Like the irony is unbelievable. But just last night I had someone reach out to me on LinkedIn to someone I, you know, haven't really connected with before. They reached out to me asking almost this exact thing where they said, Hey, I'm in a really similar position to you. I've seen your work. I've seen your content kind of on LinkedIn. Like I really like how you think about things. They're like, I too have done a lot of exploration and data, but I can't, like, I can't really, I've had trouble kind of convincing my team to kind of adopt some of this stuff.

uh like what do you what do you recommend for how to like how to handle that so yeah really crazy that happened just last night um but i think and what i told him which is what my answer will be here too is identify a problem that's facing the team and then use the new excel stuff to solve that problem um because that's absolutely what i did and and i think it would probably generally work right i don't think the

The difficulty and the uphill battle that we face with this stuff, right, is that if you've got a director who is, they've been using Excel for 20 years, you know, they really believe themselves to be a master of Excel, right? Now, the hard part is given all the changes that have been made,

They're probably not right. They're probably not anymore. Right. But they don't believe that. And since they can still tech, you know, they can get all the answers they need without using the new stuff. So it's like, how do you, you know, how do you convince a person like that of the need for change? There's, it's pretty hard. It's you, you can't really, right. Unless you can identify a problem that's facing, you know, it's not just your problem. It's everybody's problem. And you can identify that and say, okay,

hey, you know what? I think we could use some of the new features, whether it's Power Query, whether it's Dynamic Rate Formula, whatever. I think we can use some of the new features to solve that problem. You solve that problem and suddenly, you know, you've got the senior VP saying like, this guy knows what he's talking about. Like we should, we got to allocate more resources like that, you know, like and those conversations start happening. So I think you identify a problem that impacts more than just you and solve it for everybody and you'll have a platform. Yeah, huge. Yep. Well said. That's great.

All right, well, let's bring it home with our closing questions. So the first one we always throw out there is what's something that not many people know about you? Maybe something we couldn't find just from looking you up online. I love this question. I feel like you kind of find this in the tech space sometimes. So in as much as I love data and in as much as I'm a bit of a tech guy myself, I'm actually sort of at the philosophical level. I'm very much one of those like,

Like, is this good for us? You know, for humanity in general, right? Like I'm very much one of those people where I'm just like, I think about this a lot and it's like, you know, and particularly with AI, it's like, you weren't stupid for asking this question before AI, but now it's like with all the AI stuff, it's really like, oh my goodness, like this is going, you know, where's the off ramp? What

what's the end game here? We were doing pretty cool stuff 10 years ago. So I'm totally one of those people. Despite my love for data and how much I rely on technology for my job, at a philosophical level, I'm one of those people that kind of questions the general trend here and kind of like putting my hand up being like, hey, is there like, where's the off ramp? And it's interesting right now because the philosophical voices are not getting

heard as much now as just sort of the acceleration is just go, go, go. Have you ever read, have you ever read any Nick Bostrom? I have not. So he's a, he writes on AI and he does take a philosophical angle. So I would recommend, he's got a couple of books out from there. That's several years old now, but talking about what does it mean in sort of a post AGI world and stuff. Highly recommend Nick Bostrom though, to, to sort of it,

at least quench that philosophical question you have, because he's sort of done these thought experiments where he's kind of run out the ground ball on that. So,

All right. Well, this one's going to be, I've actually been looking forward to this question all day talking to you because we've been, we ask every guest what your favorite Excel function is and why. I keep saying we need to log these and I'm going to start one day or maybe we can get AI to go listen to all the old episodes and pull out what the answers were. Hey, you could. That's true. You could do that. But with everything you've talked about, tell me what's your favorite Excel function?

I'm going to try it too, whether that's fair or not. But it's either chooseColumns, which is one of the new, and it's the syntax there is chooseCols. It's chooseCols. The chooseCols function I use a lot these days where I want to specify a certain column in my array.

for calculation. I use choose columns all over, usually kind of like in the rather complicated example I gave half hour ago, usually in combination with match, where I put the match function inside the choose columns function to say, I want to choose a specific column in my array

And I want that column to be based on some match identifier that I have sort of on my report or on my dashboard. It's the choose. You know what? I can do that one. Choose columns. That's my go-to these days.

Cool. Cool. Love it. Love it. Finally, how can our listeners connect with you and just learn? Because you've had some pretty good LinkedIn posts on Excel stuff too, right? I have. And then I got sick. I got sick and it like knocked me out of my game. But I'm trying to get back, trying to get back. But yeah, I mean, I'm on LinkedIn. My name is Jeff Gudim. It's G-U-D-I-M as in Mary. I do think my title is like Excel evangelist and senior FBA analyst or something like that.

Yeah, happy to connect. I love sharing content. I'm fairly active in the Excel community on LinkedIn. Kind of interact with a bunch of those guys there.

Great. We'll put links to your profile and to the Excel community. I'm sure a lot of our listeners are already members of that, but in case they're not, we'll be bringing a new resource to them. Well, Jeff, I really appreciate you having on. I think this was a great episode. And I would say to our listeners, if you like this kind of episode, reach out, let us know, and we'll start doing these more often. So again, Jeff, thank you so much for coming on the show. Yeah, absolutely. Thank you, Glenn. I loved it.