If you would like to earn CPE credit for listening to the show, visit earmarkcpe .com slash f p a.
Download the app, take a short quiz and get your CPE certificate.
Finally, if you enjoy listening to FPNA 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 Datarails, this is FPNA Today.
Welcome to FPNA Today, I'm your host, Glenn Hopper.
Today I'm joined by Jeff Goudem, 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 hundred million dollar 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 it 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.
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. Like you said, I'm a Senior Financial Analyst, strategic education and strategic education is a publicly traded for -profit universities.
That's kind of the background there.
The senior analyst role, I am supporting the IT function.
I'm the primary analyst supporting 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 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.
I really credit my point of this.
podcast. Like my, 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.
Uh, started out three years ago as sort of like a junior analyst 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, uh, 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, you know, analyzing mutual funds, ETFs, um, incorporating that kind of thing into client reporting and building out retirement and estate plans.
Gotcha. I mean, 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, uh, wealth advisors and, uh, people in that space are more, almost become more salespeople.
And that was why I had to get out.
Yeah, I hear you. It was not really working for me.
It's a great industry.
I made a lot of good acquaintances there.
Just wasn't my, wasn't my cup of tea.
And I probably honestly spent longer there than I should have. 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 can spend a lot of time today talking about your career.
But after we talked the other day on the pre -call, I thought, no, we're just gonna 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 I'm a huge generative AI evangelist myself.
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, tell us what it is about Excel kind of that made you fall in love with the tool and become this Excel evangelist and how did that fashion 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 opened 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 tournament that we had every Christmas.
And he built this like 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 I was working a job between colleges, kind of like we talked, like you and I were talking about before.
I kinda also was trying to go the philosophy route before I realized that that wasn't gonna 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 requests 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 V look ups and old sum if, 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 V look up tables.
We had our margin calculation, um, we use Goal Seek every so often.
Like I just love seeing how all that data just kind of came together in that final number, right?
We have all of 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 was 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 as part of my finance program, they were just worthless compared to kind of what I had already been doing, so.
What I love about your origin stories, because when we were talking about it the other day, I thought, oh yeah, we would like everybody to 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's so much more motivating than your doing some case study or something in your work working for Excel.
So you had a need and Excel solved it and I think that, yep, that made it connect for you.
Well, on that topic, and we talked about several of the models you'd build, 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 software platforms. There's various, various ones like eMoney, MoneyGuidePro things that were used for like very complex estate planning.
Uh, but I was working in those one day and I just had the, 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?
Cause for those, you had to enter hundreds of var - well, 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 like, 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 it would be really fun to challenge myself by trying to build a Monte Carlo component to this also in Excel because these platforms had Monte Carlo analyses, 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, 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 a little bit more simple.
And two, build a Monte Carlo component.
Let's try to do this in Excel.
And so I, and I did, I, I built it.
It fully worked. I actually, epic failure, but I launched a company around it, trying to, trying to do it.
Wasn't the right time.
Didn't have enough runway.
It was a terrible life decision, but like, it was fun.
I, it was a, the, cause the model worked well enough that I could actually, I could present it to people and be like, Hey, this is like, this is legit.
Like it actually, I can get your details.
There's like, you know, five or 10 details from you.
and I can give you a Monte Carlo estimate.
The nitty -gritty, because we're going to nerd out about Excel a little bit.
The nitty -gritty of how this worked was, to create the Monte Carlo side of it, was, I essentially used the rand function.
It was a nested rand function, basically.
It was a whole bunch of logic with the rand, and the rand between functions to guide the randomization of market returns over my, in this case, I did a thousand different 80 year periods.
I know, you know, it was very complicated because it was like, I had to like do a whole bunch of like market research and be like, okay, you know, how many years, you know, out of every five years or every 10 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, 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, those never happen, you know, So I had to build those in, but I built out this complex logic using nested rand functions and a whole bunch of 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 randBetween functions are one of them.
The indirect function is another one and there's a couple more because it does that.
Ultimately speaking, I actually just created my scenario and then hard -coded the results.
You know, I hard -coded in a thousand different 80 year scenarios.
And then I would, then I would just tweak kind of like the variables, you know, per client.
Um, 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.
Um, it was fun and really fun to like, have a product that I did in Excel that was like, I didn't even know if it was possible, but it was just like, let's just try this.
I love Moneycrawler simulations.
Also I do them all the time.
I love them for scenario analysis.
And I'll be just doing forecasting and planning.
I'll be like, I don't know, let's run out some Monte Carlo and do the 95 % confidence interval and all that.
And I think about, like in Python, you know, it's pretty straightforward. There's Numpy, there's like you just generating the random numbers and you can put a 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, nothing.
So like I could restate my entire model in one second because like having the volatile function, like I tried it and it was like it just crashed.
It didn't technically crash but it would 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.
And another thing, and we could, you know, 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 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 FPNA analysts, what would those be?
Sure, yeah, no, yeah.
And I it's so funny on the note of like a newsletter, what I've actually started doing in my team is every, it's random, I don't have a cadence to it at the moment, But I ran 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.
Um, I'm definitely, you know, the, sort of like the token Excel guy on my team.
But, uh, yeah. So top three, I would say first one utilizing kind of some of these new functions in Excel, these, uh, sort of, they're 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, you know, 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, removed 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 the parentheses, you hit Enter, and it will, you know, getting into kind of how these dynamic array functions work, your results will actually spill down 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 to know the unique values here.
Let's say I have a list of like cost centers, right?
I got a column, 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 takes two seconds.
Then in combination with that, you can put unique inside the sort function to get your unique values sorted, ascending or descending.
That's also super easy, very simple, the sort functions, there's 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 2, I'd say, this is a little bit more process focused.
Get your data into tables.
If at all possible, your source data should be in a formal Excel table.
Sometimes it's not possible.
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 can't be placed into a table because that add -in has to refresh and it just doesn't work.
So there are some cases where you can't, but if at all possible, get your source data into a formal table so that you can use what are called structured references and all your formulas.
Where when you're typing your formula, you don't have to type in like A1 through B10, you can type in the name of your table with the name of the column.
So you're building your sum f's 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.
Then I'd say just like this one is high -level, I would expect certainly anyone who has five to 10 years in experience in FPNA, most people are probably doing this already, but if you're not, definitely use the simple keyboard shortcuts.
I'm not one of those people who's like, you should never use your mouse.
That's not me, but there are really simple ones like the control shift arrow buttons, to navigate like contiguous data to kind of like select or kind of like navigate between like different data on one sheet.
Control shift arrow is kind of a must in my opinion.
Using control space bar or shift space bar 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.
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, and then it's - I know!
God, and the other thing, so years ago, I remember, you know, when I worked with a bunch of analysts, when I, 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 I as you were talking about those I was picturing my process of going through, you know, highlighting the table filter and sort and 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 and excel that I used to and I'm I'm embarrassing when I get in front of it Well, it's funny.
That's my that's my supervisor.
My supervisor is a senior director.
She's a CFA 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.
When my default is still to go back to Vlookup and everything you were talking about back in the day, that's that was like my Excel heyday back so I'm still I still have muscle memory.
But the new stuff, it's harder for me to keep up with.
But control shift fee, I've got to remember that for pace and the value.
you mentioned dynamic arrays, and Lambda functions.
I want to go into that a little more.
And if you could, I know a lot of our, probably most of our listeners are familiar with those.
But, kind of walk us through what dynamic arrays and Lambda functions are, and then how these newer Excel features have kind of changed the way that you build models.
Yeah, yeah, no, it's a great question because this was a, this was what I would call a fundamental change to how Excel operates.
And this happened in 2018.
So, 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 process the formula as an array, an example of this would be, say you want it to multiply two entire columns against each other, right?
Some product does that, but like there are other scenarios where you might wanna 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.
Uh, 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 of 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, the results of the, it's not even a type because some of the old functions do this too now, but they just, um, it's a new way where if you have if the output of your formula is an array, right?
So the output in a really good example of this is like let's suppose you have a column that has the names of months and you have a, say 10 ,000 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 you know, January in double quotes, so it's the text value of January and then hit enter.
What's gonna happen is Excel is going to spill down the 10 ,000 rows directly underneath the cell where you entered the formula are gonna contain a whole bunch of true false values, depending upon whether the value in that row 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.
If you've got data, maybe you have data below a function where you have data that's going to spill.
The spill error 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 hard to, 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 a matrix math now inside the formulas themselves which hasn't really been possible.
And Excel is, they keep adding things like 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.
Well, you can do the manual copy -paste and put your table together, or you can actually just use the VStack formula.
You can 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 your data will now be stacked.
You'll get both of those tables in 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.
Fundamentally, that's what these dynamic array formulas are doing, and the Lambda functions are just a whole bunch of these at least three different types of Lambda functions in Excel.
I'm only going to focus on the simplest one, which is what I might call 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 that row in a new column that doesn't actually exist anywhere except for in the logic of the function.
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've got this extra column out off to the right that has the sum of all those different rows.
But that sum 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 that kind of fully digest what this meant.
Now, we're kind of really getting this age of like people like, wow, you could do crazy stuff.
As an example, 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.
LET allows you to assign variables that are local to the cell in which you're typing the formula.
These are not variables that can be called anywhere else in your spreadsheet, anywhere else in the workbook, but you can actually assign variables.
So say you have a match operation or maybe a VLOOKUP operation, but you want it to, it's going to repeat a whole bunch of times, or you're going to use it in some logic that's going to be repeated.
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.
Then at the end of the let function, and you can put in kinda like the calculation steps.
So rather than having to paste in this long VLOOKUP with this long index match, match thing, and you get this formula that's really complex and it just tons of range references everywhere.
You get this formula that has really clean names and you can kinda tell, okay, like this process, 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.
In my case, what I've had to do is I've had to rely on 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 SUMIFS operation on certain columns based on the quarter, 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 SUMIFS actually can't work with arrays right now, so it either has to be like a range reference or a table reference in order to be in SUMIFS.
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're just hard -code in the range reference and you do some ifs anyway.
Or you hard -code, you, you hard -code in some sort of column match number that just sits in a helper cell somewhere.
Um, 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 there's 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, match on Q2 in the headers for my source data, put that inside the choose column's function.
Now I have a formula that is completely dynamic to my source data dependent on 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.
That's in my opinion, that's ideal.
You don't want to the extent possible, you never want your formula to be able to give you an answer that's totally wrong.
FPNA today is brought to you by DataRails, the world's number 1 FPNA 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 in 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 learn more at datareals .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 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 power query, dynamic functions and even like the amazing components that you have I mean, how?
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 really cool sweet spot right now because, you know, if you if you pull the LinkedIn Excel community, there's kind of the assumption that BBA is gonna probably go away completely at some point.
It's not not in the next year, not in the next five years, but probably at some point, BBA is going to go the way 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 amounts of work, is amazing.
You no longer have to rely on VBA to transform data.
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 get a Power Query go out and grab everything that's in that folder, append it all into one table, put it into Power Pivot 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.
You can do that in terms of data transformation.
Then where you want to automate anything that's point and click, you can go out to Copilot and you can grab your code.
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 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 anything that's beginner to intermediate VBA, I would say Co -Pilot can easily provide right now.
I've done it numerous times in the last year or two, where it's just like, I have this macro, because I'm not a heavy VBA user.
I came into Excel, passed the VBA heyday.
But you can go in, use Co -Pilot, bring that code in seamlessly, it works great.
I've done this numerous times.
Just like randomly, I'll be sitting there, it was like, it'd be really nice if I had a button that did this relatively simple operation, but it's something I do 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.
So Microsoft put 15 billion plus, 15 billion initially into OpenAI, and they've done their acquires, and they're all in on AI, and I think rightfully so.
But, co -pilot, 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 that there's 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, the 90s, early 2 ,000s.
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 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 Co -pilot 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, who's Canadian I think, has covered the copilot stuff.
From what I understand, you can even do some of that with copilot in Excel right now, or you can be like, I need this data to be summed in this way, And, they will 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, like the use cases for me are probably somewhat limited.
But I know it's, it seems to be going that direction.
And it's similar to data science.
To be a really good FPNA Pro 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 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 2 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 modelers mindset and that engineering problem solver and all that so even if this stuff gets more automated you're gonna 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 two minutes later with a complete incredible model that 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 in some ways just a glorified prompt engineer, you're still going to need that data and curiosity mindset.
Completely agree. Yeah, and 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.
So I mean, walk through that and sort of the integration and cross over from Excel into Power BI.
Yeah. If I had 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 was the timeline.
I built a big semantic model in Power BI sort of in the same way that Excel really got me into data.
It was Power Query in Excel that really got me into Power BI where I started automating some workflows using Power Query in Excel.
Then when I figured out that the hard part of Power BI is just Power Query.
Once I figured that out, I was like, oh, hey, well, we should just be doing a whole bunch of stuff in Power BI.
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 mostly me.
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 skill set I had honed in Power Query in Excel, Power Query and Power Pivot.
Because a lot of that is the same.
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.
When you log in your Power BI Desktop, and you go into, I think it's like, I forget what that button is called, but you edit data or whatever, and that brings you into the Power Query interface where you have all your tables over on the left, and then you have all your transformation steps for each table over on the right, in the middle, you got your big table, which shows the preview of your data.
Yeah, I built this.
I kind of used my knowledge power query to build this big semantic model 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 manages Coupa for managing our invoices.
We had various attributes in there that we wanted to bring in at the line transaction level on our general ledger.
The way we'd been doing all of our general ledger reporting so far and for all of history was, we'd go out, we'd manually load an SSRS report trial balance that gives us what we're looking for in the time period we want.
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, this is the purchase order number that that transaction is assigned to.
Or this is like the corporate sponsor from the business unit and 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 the, some of the more challenging things about that were identifying like the unique identifier that you need to use for all of your table relationships.
Oftentimes that was not very straightforward. Oftentimes it took several transformation steps.
You have to go out to well, part of the unique identifiers in one table, part of it is in another table.
You either merge it in or you use M code to pull them together, you can count them, whatever.
There's lots of stuff like that to get the data into the format where we could identify and kind of map all of that all those table relationships.
So kind of a three part question I'm gonna 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 the last one there, because I kind of touched on it honestly, and my last one and it 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.
Um, like I said, most finance people listening to this, probably have some monthly systems where they are going out and they are grabbing, you know, 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, um, you know, internal structure, right?
It's a CSV. It's got, you know, 12 columns.
They all, that they always have the same name, whatever that you've got, Almost everyone listening to this probably has some type of process that they follow on a monthly or quarterly basis that takes that form in some way.
You can automate that entirely in Power Query, and you get Power Query go and point to that folder, grab everything there, bring it through some transformation steps.
You can do what might otherwise be, maybe you relied on VLOOKUPs to add dimensionality to your table, or you're removing duplicates, or you maybe you can cat two or three different columns together.
All that stuff can just be knocked out in Power Query.
When you can bring that data into your spreadsheet in a totally different, a cleaned form already.
You don't have to rely on a whole bunch of manual transformation steps anymore.
So do that. Then once you 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, the sexy side of Power BI is very easy.
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.
Power BI is much more seamless on the data side than Excel is, For better or worse.
I've definitely noticed Power BI is just more seamless when you're going out and connecting to data source and stuff.
Power BI handles the connections a lot better.
But you can totally leverage knowledge of Power Query in Excel to become good at Power BI, because getting good at Power Query in Excel means that you've mastered the hard part of PBI.
A little bit different episode and I love these.
I think we might need to start doing these more often, honestly, because 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 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 sort of forming narrative from the data and all that.
So I guess before we get to our 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 FBNA team, and obviously, because you have all these skills, some 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, you know, what advice would you give someone kind of trying to build out that reputation and to be the sort of the, the master of this platform beyond just all the soft skills that we talked about?
Because maybe there's a caveat here, like when you're talking to senior management they don't care how the sausage is made, they just want great sausage and all that.
So, I mean, it's, you're kind of 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 kind of Excel question and all that.
Yeah. No, it's good.
It's a great question.
Actually crazy, irony is unbelievable.
But just last night, I had someone reached out to me on LinkedIn, someone I 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 on LinkedIn, I really like how you think about things.
I too have done a lot of exploration and data, but I can't really, I've had trouble convincing And I think I'm just announcing my team to kind of adopt some of this stuff.
Like, what do you, what do you recommend for how to, like, how to handle that?
So yeah, really crazy.
That just 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 difficulty in the uphill battle that we face with this stuff right, is that if you've got a director who is, uh, 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've been made, they're probably not, right?
They're probably not anymore, right?
But they don't believe that.
And since they can still take the, 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, it's pretty hard, 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, hey, you know what, I think we could use some of the new features, whether it's Power Query, whether it's Dynamic Array 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 got the senior VP saying like, this guy knows what he's talking about.
Like, we should, we gotta allocate more resources.
Is 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 inasmuch 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's the, what's the end game here?
Like I we were doing pretty cool stuff 10 years ago, like, so I'm, I'm totally one of those people, um, despite my love for data and, 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 put my hand up being like, Hey, is there like, you know, where where's the off -ramp?
And it's, it's interesting right now because the philosophical voices are not getting heard as much now is just sort of the acceleration is just go, go, go, have you ever read any Nick Bostrom?
I have not. So he writes on AI and he does take a philosophical angle.
I would recommend, he's got a couple of books out, that's several years old now, but talking about what does it mean in a post -AGI world of stuff.
I highly recommend Nick Bostrom though to at least quench that philosophical question you have because he's done these thought experiments where he's 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 can apply to all right, whether that's fair or not.
But it's either chooseColumns, which is one of the new and it's the syntax that is chooseCols.
Choose Cols. The chooseCols function I use a lot these days where I want to specify a certain column in my array for calculation.
I use chooseColumns all over.
Usually, in the rather complicated example I gave half hour ago, usually in combination with match I put the match function inside the choose columns function to say I want to choose a specific column in my array.
And I you know what 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.
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?
Can just learn it? I don't know.
You've had some pretty good LinkedIn posts on Excel stuff too, right?
I have and then I got sick.
I got sick and then like knock 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 Goodem.
It's G U D I M as in Mary.
I do think my my titles like Excel evangelists and senior FBA analysts or something like that yeah.
Happy to connect. I love love sharing content.
I'm fairly active in the Excel community on LinkedIn.
Can I interact with a bunch of those guys there?
Great well and we'll put links to your profile and to the Excel community.
I'm sure a lot of our listeners already members of that, but in case they're not, will be bringing a new resource to him.
So well Jeff really appreciate you having on.
I think this was a great episode and I would say to our listeners reach. 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 for coming on the show.
Yeah absolutely. Thank you Gwen loved it.
