Why Structure and Design are Critical Elements of Trust and Credibility in Financial Modeling with Nick Boberg
In this episode of Financial Modeler's Corner, host Paul Barnhurst chats with Nick Boberg, a financial modeler and consultant based in Hamilton, New Zealand. Paul and Nick explore the essentials of building well-structured, effective financial models. They discuss Nick’s approach to simplicity and consistency, the importance of clarity in model design, and the role of competition in refining one’s Excel and modeling skills.
Nick is a financial modeler and the co-founder of Boberg Advisory, a consultancy that specializes in providing financial modeling services to SMEs. With extensive experience as Finance Director at Anglesea Hospital and Associate Director at PwC, Nick has built and reviewed hundreds of models, many focused on cash flow forecasting, budgeting, and management reporting. He is also an accomplished competitor in the Financial Modeling World Cup and the Microsoft Excel World Championships, where he has achieved finalist and semifinalist placements.
Expect to Learn
Why structure and consistency are key in financial modeling
The role of simplicity in building models
Insights from Nick’s competitive modeling career
How to balance technical expertise with user-friendly design
Here are a few quotes from the episode:
"The beauty of financial modeling lies in its simplicity. If you can make a model both functional and easy to follow, you've mastered it." - Nick Boberg
"Consistency in formatting is key. A model that looks clean and well-structured builds trust with the user, especially when it's used for important decision-making." - Nick Boberg
Nick shares that the core of building great financial models lies in structure and simplicity. He emphasizes the importance of creating models that are not only accurate but also easy to follow and understand. Whether in competition or in client work, Nick highlights that clear, well-structured models make a real difference in their effectiveness and usability.
Follow Nick:
LinkedIn: https://www.linkedin.com/in/nickboberg/
Company: https://www.linkedin.com/company/boberg-advisory/
Follow Financial Modeler's Corner:
LinkedIn Page- https://www.linkedin.com/company/financial-modeler-s-corner/
Newsletter - Subscribe on LinkedIn -https://www.linkedin.com/build-relation/newsletter-follow?entityUrn=7079020077076905984
Sign up for the Advanced Financial Modeler Accreditation Today and receive 15% off by using the special show code ‘Podcast’.
Visit https://bit.ly/497oAqW and use the code “Podcast” to save 15% when you register.
In today’s episode:
[03:30] – Worst Modeling Experience
[08:08] – Running Boberg Advisory
[12:11] – Competing in Excel Championships
[17:42] – Importance of Structure
[20:00] – Risks of Complex Formulas
[23:20] – Avoiding Hard Codes
[27:13] – Building "Pretty" Models
[30:10] – Dynamic Arrays in modeling
[36:05] – Rapid fire Section
[40:54] – Last Question & Wrap up
Full Show Transcript
Host: Paul Barnhurst (00:33):
Financial Modeler’s Corner is the world's premier modeling podcast. It is brought to you by the Financial Modeling Institute, the world's leading financial modeling accreditation organisation. Welcome to Financial Modeler's Corner. I am your host, Paul Barnhurst. This is a podcast where we talk all about the art and science of financial modeling with distinguished financial modelers from around the globe. The Financial Modeler’s Corner podcast is brought to you by the Financial Modeling Institute. FMI offers the most respected accreditations in financial modeling, and that is why I completed the Advanced Financial Modeler. I'm thrilled to welcome our guest on the show this week. Nick Boberg, welcome to the show.
Guest: Nick Boberg (01:23):
Cheers, Paul. Good to be here.
Host: Paul Barnhurst (01:25):
Yeah, excited to have you. So let me give a little bit about Nick's background.Nick Boberg is a financial modeller based in Hamilton, New Zealand. He has worked for the past 3 years as a consultant financial modeller for Boberg Advisory alongside his wife Ella with a focus on SMEs (though we also have worked for some of NZ's largest companies). Previously Nick has worked as Finance Director for Anglesea Hospital (3 years) and Associate Director for PwC (8 years) where he was responsible for leading the financial modelling team as well as involvement in M&A, valuation and ad-hoc consulting engagements. He competes in the Financial Modelling World Cup (7th in 2025) and the Microsoft Excel World Championship (Finalist 2024, Semifinalist 2025), as well as being a frequent host and case creator for the MEWC. He is a member of the newly- formed Financial Modeling Global Leaders Council.Nick has built or reviewed hundred of models over his career to date, half of which have been integrated cashflow models intended for budgeting, forecasting and management reporting purposes. He adheres to a modified FAST standard of modelling where consistency, structure and formatting is imperative, however he also places high importance on the user experience – He likes to build a “pretty” model! He is qualified with Bachelor degrees from the University of Auckland in Commerce (Finance/Economics) and Science (Geography), and is a CFA Charterholder. Love the background. Nick.
Guest: Nick Boberg (03:05):
Didn't realise you were going to go through all of it, Paul, but yeah, thank you. That's me.
Host: Paul Barnhurst (03:10):
I know you've listened to some episodes. First question we always ask is that horror story. I'm sure you have one worst model worth modeling experience.
Guest: Nick Boberg (03:18):
I'll give you a couple because one is a bit more standard and one's a bit more me. The standard one was a small workbook that I needed to review, didn't think it was going to be a big issue, was analysing some different investments in the property space. And then I came across the formulas that were the nuts and bolts of it and you have the formula bar and if it wants to make it to the second line, you're thinking to yourself, oh boy, this one made it to the fifth line and I clocked it over 800 characters and there were multiple of these setups all a bit unique. So having to go through the process of unpacking what a mega nested formula looks like, it was not fun, not enjoyable. So step things out, please, everyone. Yeah, the second thing for me was one where it was like there was a different theme on every tab, different fonts and sizes and colours left right in the centre.
(04:14):
It was a little bit like a crazy Christmas tree or something, which is fine. We like to have the nice Christmas tree at home where you've got different tinsel and different decorations, but no, not in a model. So that was the sort of model I opened and I was just instantly stressed and incredibly irked having to deal with and look with what I was looking at without any consistency. And so yeah, you might learn over the course of this that I'm a little bit particular when it comes to structuring consistency within my model and whenever that's lacking, yeah, I get a little bit icky. So
Host: Paul Barnhurst (04:48):
No random Christmas trees and your models?
Guest: Nick Boberg (04:51):
No, I like the ones at the shopping mall when it comes to the models, the ones that look like they're off the shelf and have been done with the colour schemes and very particular
Host: Paul Barnhurst (04:59):
Makes sense and no long nasty nested statements.
Guest: Nick Boberg (05:04):
I mean, I don't adhere to it in the sense that I think you get a few index match matches that probably go beyond the thumb pretty commonly, and I'm okay with that being in play if it needs to be. But if you're stepping across multiple lines, my argument to you would be step it out, particularly for the sorts of models we're doing or I'm doing for clients, doesn't make sense to have that level of complication. I
Host: Paul Barnhurst (05:25):
Get it. I mean at the end of the day, especially if the customer has to understand it, maintain it, update it. You want relatively simple formulas. And as I've heard someone say, columns and rows are free, they don't cost you anything, so use them.
Guest: Nick Boberg (05:44):
They excel gives you a million rows. What's wrong with using a
Host: Paul Barnhurst (05:48):
Couple? Yeah, a million rows of what? 25,000 columns. Now if you're using all 25,000 columns in a million rows, Excel's never going to open. But hey, that's a whole nother issue.
Guest: Nick Boberg (05:58):
But what's a few thousand if you had to?
Host: Paul Barnhurst (06:01):
Exactly. I think the largest I've had, I got a table that had over 500 columns that I built with Excel that was not fun, but that's probably about as big as I've got.
Guest: Nick Boberg (06:09):
Wow. And how many rows to match with the 500, a couple
Host: Paul Barnhurst (06:12):
Hundred rows. So what it was a headcount schedule where we had to forecast by month, four years out as he had 10 different elements and we were doing it by person. So I had to call him for every single element for all four years all the way out. And then I went on pivot it and load it to a pivot table so I could report on it, do what I needed with it. But it was a monster with how many columns I had. It was not fun.
Guest: Nick Boberg (06:37):
I don't think I've used that many to be honest before if I go beyond a hundred columns. Yeah, that's seldom. I can think of a couple of examples off the top actually.
Host: Paul Barnhurst (06:46):
Yeah, the only case I could think of was the head count they required us to do by month, four years and had out each of the different account things I had to load. So I needed that many to do it and I preferred that over having 50,000 rows. I'd rather just unpivot it and when I was done I had 50,000 rows.
Guest: Nick Boberg (07:03):
Interestingly, my preference would be less columns, more rows. So I feel quite comfortable going into the thousands of rows if that's what's needed. And then if I was going hundreds of columns, something that you clearly, even with a few scrolls can't get to the end of on your spreadsheet, I'd be going, oh, how do I stagger this down in row format instead? Yeah.
Host: Paul Barnhurst (07:25):
And I think in most cases that is a general rule. I'd a hundred percent agree with you. I think in this case, since I was really the only one using this schedule and I converted everything for the way people saw it, it worked easier for me. But I get it. I wouldn't call it fast or best practise.
Guest: Nick Boberg (07:42):
Sometimes you got to do, you got to do, shouldn't get that on Michelle.
Host: Paul Barnhurst (07:45):
We all do what we have to sometimes. Right?
Guest: Nick Boberg (07:48):
Absolutely
Host: Paul Barnhurst (07:49):
All. So I'd love to start with, you've been running your own modeling business for the last about three years with your wife Ella. Talk a little bit about what that experience has been like running your own business, also running one with your spouse and just maybe share a little bit of that whole journey. So
Guest: Nick Boberg (08:03):
Three years for me, two years for Ella, she joined a year later. Just started off as I was working for a private hospital, really cool company, cool team. Was nothing wrong with the job I was doing, but I just kind of had a few itchy fate about could I do something else? Could I take on this journey of doing it myself by doing it myself. And I'd already built hundreds of models at pwc. I knew what I was doing at that time and as I was going through that process, I was the back of my mind thinking I'm doing an end-to-end process of this. I could be doing this for myself and not be charging out at 10 times where I get paid. And so I got to a point of why don't I give this a go? It was post COVID as well. Yeah, I'd gone through the other side of COVID if I'm thinking about it was kind of 2022.
(08:52):
I was thinking about things and then really thought about it that summer and all the trigger. And I was really lucky I could step down to three days a week still at the hospital and start working on myself for two days a week and seeing if I could make something happen. And as it transpired I was able to kind of fully resign like six weeks later I got enough work and it's been full battle station since. Been really lucky to have lots of really cool clients that have helped support me and obviously supporting them with the stuff that I do. To the point where Ella got a bit restless in her job and she's a really talented mother in her own right chartered accountant, really good with big data and so much more talented than I am when it comes to the likes of Power query, power bi and working with database integrations and the like. So we compliment each other really well in that regard. And the last two years she's been able to do that as well and couldn't really go back I think to the real world just being such a cool experience and just gives us a fantastic level of flexibility around home life as well.
Host: Paul Barnhurst (09:55):
Flexibility is hard to beat. I've been four years now running my own business, so I understand now and that would be the hardest thing for me of having to go back to a regular job is the flexibility for sure. And that's great compliment of if she has that big data, a really nice to have really strong modeling, really strong data side and bringing that all together.
Guest: Nick Boberg (10:14):
Absolutely. We are normally not working on the same projects actually, although we've got desks to each other. We have our own clients or our own projects within clients, but we still have the ability to bounce side ideas off each other back and forth as we need to and makes the conversations at the kitchen table pretty good.
Host: Paul Barnhurst (10:32):
I'm sure your kids love it, right?
Guest: Nick Boberg (10:34):
Yeah, yeah. Well they're school kids now, so our youngests just started school and joined his big brother and I mean those that have the nine to fives and are dealing with kids at school and stuff, I salute you. I don't really know how you do it. So for us, we can work around them and make sure that we are there for them after school and take them off to after school activities and have that good family time. And if we have to jump back on the tools after DS or whatever, then that's okay. We're working for ourselves, right? We're getting that alignment between what we're putting in and what we get out, which you might not get at a salary job doing the same sort of thing. So I think that's another part that's really cool and rewarding is that if we're working hard, we're getting rewarded for it. If we're not working, if we're sick, if it's public holiday or if it's annual leave and we're taking time off, we're not getting paid. But that's okay. I'd much rather it be that there's that real good alignment between what you're putting into the job and then what you get out.
Host: Paul Barnhurst (11:34):
There's our commercial for starting your own business, some flexibility and freedom. Just remember you have to make enough revenue to make it work.
Guest: Nick Boberg (11:42):
Absolutely. That is the flip side, but if you do get up working, then it does lead to a really rewarding lifestyle.
Host: Paul Barnhurst (11:49):
I'm a hundred percent with you. I agree. Alright, so got to ask, I know you run the business with your wife, you both also compete in the Microsoft Excel World Championships. So the question I want to know who's more competitive, you or her?
Guest: Nick Boberg (12:03):
It's a tie maybe we're both very competitive, just competitive people. A tie
Host: Paul Barnhurst (12:07):
That's no fun is a tie.
Guest: Nick Boberg (12:11):
Ella gets more excited beating me than I do beating her. Does that make her more competitive perhaps? I mean we're on different journeys slightly with that too. I've been competing for five or 10 years depending on how you define it and what you're looking at there with the different And
Host: Paul Barnhurst (12:27):
How long has she been, she's more recent, right?
Guest: Nick Boberg (12:29):
Oh yeah, she picked it up six months ago.
Host: Paul Barnhurst (12:31):
Oh, is that all okay?
Guest: Nick Boberg (12:32):
Oh yeah, yeah, yeah. So she picked it up, she did her first road to Las Vegas series for the MEWC this year, last month. And so yeah, she was competing in Vegas in the last chance round before she'd ever done a competitive battle and then she got through into the quarter finals. It was awesome. It was amazing. So she is just starting her journey now and getting better and better, which is,
Speaker 3 (12:55):
I can see why she gets excited to be you. You've been doing it for 10 years.
Guest: Nick Boberg (12:58):
That makes sense. Absolutely. And then I'm probably, I dunno, it might be higher at the moment, but I seem to be on the downward trend and trying to rest my fall as all these really talented people are continuing to come into the sport.
Host: Paul Barnhurst (13:10):
So what is it you like most about competing? What makes it fun for you?
Guest: Nick Boberg (13:14):
Honestly, Paul, everything. At first it was the cases themselves. They were fun. They are fun. It's 30 minutes of challenging yourself with some of the Excel skills that you have. But really at the end of the day, these cases are problem solving. It's using the tools that you have and you can do some pre-prep around providing as many tools to yourself as you can, but it's how do I solve this problem? And so I get the same kick out of it that someone doing Sudoku or crossword in the morning gets out of doing that. And the sense of achievement you can get when you get a word out or you finish it quickly. I get that from the half hour process. So the cases are awesome. It's an outlet for my competitive nature, competing against other people, competing against myself and what I think I'm capable of or I've always been a competitive person.
(14:05):
So sports, everything growing up has been brilliant in that regard. But I've always been good at lots of things, never great at anything. Just a good ballor and having fun in the B team, not in the A team. But yeah, Excel is something that I am genuinely good at and I'm able to see myself competing and half the time holding my own against the world's best. And there's a real kick and thrill that comes out of that. And so all of that side is addictive and you stay and then you meet the people that are involved in the community and then you're hooked because the people that are involved in the XL eSports world are brilliant. They are so clever. It's so much fun hanging around them and feeding off them. And they're friends. So many of them are really good friends that yeah, I couldn't leave it now if I wanted to because of all of that. The people make you stay.
Host: Paul Barnhurst (15:00):
Awesome. I love that. I'm glad you made the B team. I think I made the C team when I was a kid.
Guest: Nick Boberg (15:05):
I think there were two teams.
Host: Paul Barnhurst (15:09):
It's a fabulous community. What would you say to anyone who's considering competing? What advice would you
Guest: Nick Boberg (15:15):
Give? Let's do it. Look, if you are considering competing, then you're someone that already has some Excel knowledge. Otherwise how have you come across this sport and how do you probably even know about it? And if you're thinking about tipping your toes from then, it's a fun problem solving sport, taking on board ology, we've already got that. You're just going to grow and then you get to meet the people involved and participate and everything and all the shenanigans that go on behind the scenes there. There's a large WhatsApp group for the community, which probably has over a thousand members involved now and people are chewing the fat about everything on a daily basis. There's a lot going on to be honest outside of Excel as well. But if you get involved and then you start to enjoy it, then you've now got a hobby which is actively developing something which is only going to turbocharge your career and your employability and your skill.
(16:08):
Just such a marketable skill out there in the world no matter what you're doing, if it touches, to be honest, even if it doesn't touch Excel, you're building on your analytical thought processing and your ability to distil problems and find solutions. And I mean ultimately you'd be have hopefully having a fun time while just upgrading your ability to do whatever it is you want to do in the world going forward. So I don't see a downside give it a go if you hate it. Well what you spend a couple of hours working out, that's not for you. But I think if 10 people turned up to try it, I think nine would love it.
Host: Paul Barnhurst (16:45):
All right, well there we go. There we have our ad for modeling World cup. I've done it a few times and keep telling myself I'm going to find more time to do it, but I haven't done that yet, so it's on my list to spend a little more time competing. It is fun. I will admit I've enjoyed when I do it and you will get a lot better at Excel.
Guest: Nick Boberg (17:02):
Yeah, absolutely. I think that's somewhat of a guaranteed side effect
Host: Paul Barnhurst (17:06):
For many people. That's probably the biggest benefit. If you want to get better at Excel or modeling, do the financial modeling side, do the Excel eSports side. Both of 'em have benefits. Alright, so I want to switch subjects. We've covered a little bit of your business. We've talked about modeling and competition. Now let's talk about structure in modeling. I've heard you structure that you don't like those unstructured models from our conversation. So let's start with what does it mean to you to have a well-structured model? When you think of a well-structured model, what does that include? What does that look like?
Guest: Nick Boberg (17:40):
Yeah, I think the main thing is for me, structure is somewhat equivalent to consistency. And so I'm expecting or wanting within a workbook that you've got consistent cell formats. You've got a designated input cell designated link to another worksheet. If you have them, you might learn that I don't love them, but that's fine. Links to inputs or if you are, again, go off but maybe have a hard code in there that it's formatted appropriately to make sure that it's clear to the user what's going on. So you're separating your inputs, your workings from your outputs. You make sure if you've got dates within your model, its that they're starting in the same column across every single shape. If you've got schedules in play that they follow the same format section dividers if it makes sense, formulas being kept to a reasonable length and following a similar pattern and movement and readability. And so for me, if you keep your workbook clean, then that's going to project confidence for the user in the working of it and that the inputs that they put forward and the drivers that they're putting numbers against, the outputs that come from it, that they can take some confidence of what comes from that.
Host: Paul Barnhurst (19:01):
While my background is in fact, I am also passionate about financial modeling. Like many financial modelers, I was self-taught. Then I discovered the Financial Modeling Institute, the organization that offers the Advanced Financial Modeler program. I am a proud holder of the AFM. Preparing for the AFM exam made me a better modeler. If you want to improve your modeling skills, I recommend the AFM program podcast listeners save 15% on the AFM program. Just use Code Podcast.
Guest: Nick Boberg (19:51):
I think back to my Christmas tree model from earlier and the format and that chaos that took place there. It's generally a bit more of a when it rains at pause. I think if you're picking up a model that doesn't have structure, then chances are good. It doesn't adhere to a lot of the principles that you're looking for. But I think probably the most dangerous thing that can come would be just overly complex formulas in play and not stepping out appropriately. I am actually a big believer in using some of the rows that are given to you in the workbook. I mean they give you a million rows, you shouldn't use them all, but you can use a few. And if that's going to provide more clarity to the user, then I'm all for that. Because here's the thing, if you've got a model which has overly complex formulas within them and the user is someone that has not built the model, does not have the ability to build the model, hence they got someone else to do it, then the risk as that comes across as, I dunno, impressive to the user that what's been put forward.
(20:48):
I don't have a clue what's going on there that's super clever and smart. But as you know, the more you're doing within a formula, particularly if you're not staggering it out properly with we're not talking maybe about a let here, which nicely defines the variables and then does a cal at the end nested ifs and the like, just trying to do too many things at once instead of stepping it out through schedules, then you're increasing the risk of something being wrong and you're increasing the risk of something being wrong while then providing it to someone who might look at it and be impressed at the length of a formula and then take more confidence in what's coming out the other side from it. And so anything that increases the risk of errors, I'm not for because you put a big model together, the chance of there being an error in it is high.
(21:35):
Just it's, we can go through the process of trying to put error checks in place, which I'm a big fan of. And every opportunity where we as the model builders know, an error could arise. And so make sure that we're doing that at every step to pick up errors that can come about, but there's not an ability to put an error check in for everything. And so our ability to limit errors un for that. And if I'm seeing big complex formulas where they don't need to be, I think that's probably the most dangerous thing that can come from it and would upset me the most. Taking on someone else's model,
Host: Paul Barnhurst (22:08):
My nasty, nested if mock.
Guest: Nick Boberg (22:09):
Yeah, or even it doesn't necessarily need to be that nested if it can be trying to take a kelp block and times it by another where
Host: Paul Barnhurst (22:18):
You try to do 15 different steps in one step.
Guest: Nick Boberg (22:21):
Correct. If you get one thing and the issue with that is that you hit enter and you probably get a reasonable answer coming out the other side, but if something's being missed, it's not going to be obvious. Whereas step out those 15 steps, provide a total at the bottom and link to that instead. I'm a bigger fan of that
Host: Paul Barnhurst (22:37):
Makes a lot of sense. I mean 2, 3, 4 things, all right. That was pretty easy to follow. But when you get up to 7, 8, 10, 15, 20, it's near impossible without spending a fair amount of time when you get up to big numbers.
Guest: Nick Boberg (22:51):
And if I'm coming across that, then I'm maybe silently judging the builder that did it and if that's the way in which they're wanting to do this, then I'm losing confidence and in other parts of the model I haven't even seen yet.
Host: Paul Barnhurst (23:01):
So where do you think most modelers go wrong? Is it in the complexity? Is it just not knowing good design principles or where do you think most models go wrong when it comes to structure? What do you think are those kind of top two to three things?
Guest: Nick Boberg (23:16):
Design? I mean I mentioned when it rains it pauses. I think you've got modelers that have grown up properly, perhaps they've taken on principles with how they've put their models together and what comes out the other side is something that's clean and structured and you can rely on bad ones. I'm getting have issues all over the show that the formula complexity is the part that is the biggest issue when I come across it. And I would say that half of bad models I pick up suffer from a little hard code that makes its weigh in. If you've got a model that's being used on a monthly basis and someone's kind of rolling it forward and then the numbers don't line up the way they want to in the forecast and they go and sneak a number in. I mean for us as for modeling audience, people are going hard codes.
(24:00):
Who does that? Of course you don't do hard codes. In reality, they are all over the show and businesses and finance teams and yeah, it's all well and good for us to go. Look, we don't want to deal with hard codes that separate them out for inputs if we're not getting the answers we want. Well there are ways to go about that by using inputs and add an adjustment line and suddenly that's an input we can link our formula to and just add a number in the adjustment line instead. And now you're getting clarity of what's coming out the other side. But people aren't doing that in reality, a lot of people aren't. And so that would be one of the more annoying things you come across. You come across one hard code, you're going to come across two and then you've got to go through a process.
(24:37):
If I'm picking where these things are hiding, how do I get them out? And so that forms part of the reason why I'm taking someone else's model on and making updates to it has gone from something for me that I hated doing to almost something for me that I won't do. If I'm seeing that someone else has put a model together and it's not competent looking, then I'm going to, if I need to be involved and I'm going to be suggesting a rebuild so that I can feel confident in the way that it's being put together. Otherwise you're going to sign up for taking on a model and making changes to it and getting 95% of it right and missing one or two things along the way and then you are going to be left with it. You're going to be, people will point at you and say, that was your model. And it's like, whoa, whoa, I took someone else's garbage. I made it a lot better and I left some garbage behind without noticing and then you're on the hook for it. So yeah, if I'm taking on a seriously bad looking model that has some real fundamental flaws and errors with it, I'm going to be saying if I'm involved, I'm going to be suggesting a rebuild in this sort of way rather than taking on a broker model and trying to fix it.
Host: Paul Barnhurst (25:42):
Bad financial models can lead to bad decisions or worse. So how do you minimise the risk of a bad model? You make sure the models you build are great Financial modeling Institute developed the Advanced Financial Modeler accreditation programme to help modelers like you. The AFM programme offers a step-by-step approach to building world-class financial models. The programme ensures that you know the best practises in model design and structure and will help you brush up on your Excel and accounting skills too. Be the one on your team to build great models. If you want to impress your boss and your clients, get a FMI accredited podcast listeners, save 15% on the AFM programme. Just use code podcast at http://www.fminstitute.com/podcast..
Guest: Nick Boberg (27:00):
Yeah, I dunno how else you want to put it in terms of how I think of it as a pretty model, but yeah, majority of the models I'm putting together, yeah, I'd say about two thirds of them these days are financial forecast models, monthly integrated, three-way models for businesses that don't have a forecasting mechanism or if they do it's not good and I'm helping them build something which is taking, linking into their accounting system, pulling forward their actuals for a month, rolling onto the forecast for the following month and being able to be updated. And so often off the front of that, the real outputs that they're after is going to be what forms part of their board pack or reporting pack. So how did the p and l and balance sheet and cashflow go for the month versus budget versus last year and how does the year to date look, how does the four year forecast look and what are some long-term charts and graphs look like?
(27:55):
So if that's going to be picked up on and used on a monthly basis rather than one off and I need to go through the process of creating these reports anyway, I might as well make them look good. And I have fun going through the process of trying to do that so I can spend 98% of my time understanding the drivers and putting the inputs together and the workings and charts and get to the end. Or I can spend another percent or two of time in order to make sure it adheres to the brand scheme of the company and make sure that the charts aligned appropriately and that they aren't all roughly the same size, they are the same size and that the fonts used within them are consistent and the sizing of that is consistent and the colour scheme is consistent because when you've got neatness, I think to the human eye it comes across more polished and more professional and nicer to look at.
(28:51):
And so I even make my charts like little almost bi mini bi things with an Excel. You can do a bunch of cool stuff when it comes to how you filter things and how you can use dynamic date ranges in order to change the way in which charts move. I find that a lot of fun and so yeah, I put it in extra one or 2% of time putting the extra effort and polish on what those outputs are going to be for the user. I think the one or 2% of time comes back to the user as a 20 or 30% change in what their experience is going to be.
Host: Paul Barnhurst (29:24):
That's what they're going to see the most is the front end where they're making decisions.
Guest: Nick Boberg (29:28):
Absolutely, yeah, the nuts and bolts. I go through the process of making sure that the drivers make their flow through to the outputs in the best manner that they can, but once you do that and go through that process, the outputs that other people are going to be seeing, maybe not even the model user but we're talking then the decision makers at the other end management, the board, et cetera, they have to read the reports and see what the numbers look like, try and make it a good experience.
Host: Paul Barnhurst (29:54):
So do you have a favourite chart type? Yeah,
Guest: Nick Boberg (29:55):
A line chart. Simple, but yep, I've got a few different ways in which I put things together, actual and forecast and against budget and I like to make use of the markers and dotted lines out from the forecast and yeah, I've got a nice little default, which I like.
Host: Paul Barnhurst (30:11):
That is always helpful when you can kind of make it really clear because when you get into forecast budget actuals, there's just nice little touches you can do over the defaults in Excel that make a huge difference.
Guest: Nick Boberg (30:24):
Yeah, I think it makes sense when you are creating a product that's intended to be used more than once. Yeah. Monthly reporting models, the idea is that this is giving you your monthly reporting pack on a monthly basis 12 times a year if you created a good model, which should be able to march on for some years until it requires tweaks or overhaul or whatever else and just make it a good experience. You've got to go for a board pack of a bunch of other stuff anyway, make your section the whole up. Alright,
Host: Paul Barnhurst (30:51):
So I know you're a big fan of dynamic arrays. I'm curious, do you think of the future of financial modeling? Do you think we'll get to the point where most models are built with dynamic arrays or exclusively dynamic arrays or how do you think about it?
Guest: Nick Boberg (31:02):
It's a good question. I think they're part of the future. I don't think they are and I don't think we'll get to a point where most models are built with dynamic arrays. However, there are certainly some really cool use cases and to be honest, things I've done lately with dynamic arrays is I've been trying to experiment and learn about them more that I wouldn't be able to work with traditionally. So I've created a cashflow model, a daily cashflow model where the client wanted to go forward a year now the main focus was what's happening in the next three months, but for completeness, they wanted to go forward a year and have that reconciled to their monthly revenues and expenses and how that then tied to timings and bringing out a cash flow and I was wanting to create something that didn't, if they were updating on the 15th of January, I didn't want it to end on the 14th of January the next year.
(31:56):
That's no fun for me in the ultimate kind of charts that I want to have off the back of it where I do kind of look at it from a monthly perspective where I could do, so I'm thinking if I'm going from the 15th of January and wanting to get to the end of a month, but they might then update it on the 2nd of January or on the 12th of February, then I'm actually got potentially different time series that I have to deal with. This is an example of actually me using hundreds of columns, kind of 365 to 395 in there, but holding
Host: Paul Barnhurst (32:25):
It dynamically a full year you get a lot of columns.
Guest: Nick Boberg (32:28):
Yeah, but the start point was suddenly the dates at the top were dynamic and if the dates at the top of the dynamic for your columns that everything else has to follow. So and yeah, went through that process as half a challenge of can this be done and how hard is it? And also me, as I said, wanting to make the model end at the end of a month and managed to go through that process quite simply. To be honest, I was really impressed with what could be done with dynamic array. Love
Host: Paul Barnhurst (32:54):
It. Thank you for sharing that. Do you have a favourite dynamic ray
Guest: Nick Boberg (32:58):
Sequence and filter? I sequence is maybe one of the more OG ones for me and I use it a lot in Excel eSports. It's pretty commonly used to help achieve a bunch of things. Filter is really cool and filter is really powerful and the more you get to understand and use that, it doesn't even need to be with dynamic array to be honest. You can just do it with simple fixed arrays as well. But powerful tool unique's a good one. I mean, I'm trying to think if I was actually before Unique turned up, was I removing duplicate? I'm trying to work out what I was doing to be honest. It's kind of such a simple formula that you wonder how and why I wasn't, hasn't been there for a long time. It
Host: Paul Barnhurst (33:32):
Really is a simple formula. Those are some great ones. So the next one, favourite shortcut
Guest: Nick Boberg (33:37):
Native within Excel will be alts, WFF to freeze pans. The last step I'm doing at the end of a model, making sure that everything is lined up nicely for a client on each tab, keep the dates at the top and the text to the left. But fun one outside that I'll say will be like alt one, alt two, alt three. Do you know about that?
Host: Paul Barnhurst (33:57):
Those are your custom quick access toolbars.
Guest: Nick Boberg (34:00):
They are. So you can chuck whatever formulas you want at the top there or shortcuts within Excel at the top there and have them really easily accessible. But you can go a little step further and create your own custom macros, say them down to your personal workbook and then link them up the top there and have them be really readily accessible. I've got a number format that I love, puts the negatives into brackets a dash for the zero and going through the process of control one, bring up formatting and choose it for sale. That's not fun. So instead I have that sitting in a one or on position one on the quick access toolbar and I can alt one to bring it through. But one quick disclaimer on that is that you are activating a macro and running a quick macro when you do that. So if you want to go backwards in time control Z doesn't work too well, but once you're aware of that and the limitation, yeah, I still march forward and use it and find it really useful.
Speaker 3 (34:52):
Yeah, I use Control Z too much. No,
Host: Paul Barnhurst (34:54):
I'm just kidding. What's the most unique or kind of fun thing you've created, built in the spreadsheet in your personal life outside of work?
Guest: Nick Boberg (35:04):
A fun time in my life, like eight years ago comes to mind where I got involved in arbitrage sports betting. So it combines a lot of lovers in my life. I love sports, I'm risk averse, so that's the arbitrage side. And I also don't mind a flutter or a bet, but I found a way to web scrape a bunch of information from various sports betting websites and I had an account with each of them and I found mismatches had fun taking both sides and tried to supplement my own calm doing that. So yeah, I had a model which was spitting out a bunch of different positions I could or should be taking and I followed through.
Host: Paul Barnhurst (35:44):
Nice. I like it. Alright, so we're going to move on to our rapid fire section then we're going to wrap up. I know we just have a few minutes left. So how this works is you can't give me an, it depends. You got to take a side and then you can elaborate at the end on a few circular references. Yes or no,
Guest: Nick Boberg (36:00):
No,
Host: Paul Barnhurst (36:02):
VBA, yes or no.
Guest: Nick Boberg (36:03):
Yes.
Host: Paul Barnhurst (36:04):
Lambdas in financial models, yes or no?
Guest: Nick Boberg (36:07):
Yes. And I might talk about that further at the end.
Host: Paul Barnhurst (36:11):
All right, we'll reserve that right for you. External workbook leaks, yes or
Guest: Nick Boberg (36:15):
No? No, no, no, no, no, no.
Host: Paul Barnhurst (36:16):
Mouse for modelers, yes or no?
Guest: Nick Boberg (36:19):
Yes.
Host: Paul Barnhurst (36:20):
Alrighty. Models should always be print ready, yes or no
Guest: Nick Boberg (36:24):
Outputs. Yes. The rest of it doesn't need to be,
Host: Paul Barnhurst (36:28):
Are merge cells ever acceptable, yes or no?
Guest: Nick Boberg (36:31):
Yes. Some parts of charts, if you want a multi-level for your X axis on your dates, you can't achieve it or I haven't found a way to achieve it without merge sales. Basically have one level be JFMA, et cetera for January, February, March, April, and have that go 24 months. And then you want to have something that says like FY 26, FY 27, then I think you need me sales to achieve that or I can't find another way. So that instance, yes,
Host: Paul Barnhurst (36:59):
I'm going to have to see this one next time we're in Vegas. I can't picture you, but I get it. You're not the only one who says yes. I would actually say yes, but I did laugh at the prior person who basically said he who uses them as going was his phrase. He had coin. So should financial modelers learn Python in Excel?
Guest: Nick Boberg (37:16):
No.
Host: Paul Barnhurst (37:17):
What about power query?
Guest: Nick Boberg (37:18):
Yes.
Host: Paul Barnhurst (37:19):
Power bi.
Guest: Nick Boberg (37:20):
No.
Host: Paul Barnhurst (37:21):
Alrighty. Every financial modeler should be able to build a fully integrated three statement model.
Guest: Nick Boberg (37:27):
Absolutely. Yeah.
Host: Paul Barnhurst (37:28):
It's funny, I never built one in the first 15 years of my career, but I worked in fp and a for large companies,
Guest: Nick Boberg (37:33):
Nor now that you have built one, what's your thoughts? Do you think it would've helped you if you've done that earlier on?
Host: Paul Barnhurst (37:38):
I think it would've helped. I don't think it would've been required. We were mostly focusing on p and l, so I lean toward the no, but I understand the yes. And if you're working in valuations in a lot of areas, my cake is you have to understand the workings of the three statement regardless of whether you built one or not. And the best way to truly understand those workings is to build them. No disagreement. But yeah, the first one I ever built was when I started my own business four years ago, were for work. Interesting. Yeah. Most people who do modeling, look at what, it's just kind of that difference if you work the large fp and a. So that's a fun one I always ask because that's a typical answer. Will excel ever die? Yes or no?
Guest: Nick Boberg (38:19):
No.
Host: Paul Barnhurst (38:19):
You hesitated for a minute.
Guest: Nick Boberg (38:21):
I mean, look, I'm answering that question from spective of, I dunno, within my lifetime in 200 years, who knows, right? But I think it's too ingrained in too many things within the business world that I think if you turned off Excel tomorrow and everyone's files from the computer evaporated, I kind of feel like the world would stop.
Host: Paul Barnhurst (38:41):
Alright. Have you used AI to help you build a model in Excel
Guest: Nick Boberg (38:45):
For maybe specific formulas, but that's the extent I've gone to so far.
Host: Paul Barnhurst (38:49):
What financial statement is most important for modelers? Income statement, balance sheet or cashflow?
Guest: Nick Boberg (38:54):
Cashflow.
Host: Paul Barnhurst (38:55):
What's your favourite? LLM, Claude copilot chat, GPT or something else?
Guest: Nick Boberg (38:59):
I use chat GPT.
Host: Paul Barnhurst (39:01):
All right, perfect. If you could only pick one for all your models, would you pick sensitivity analysis or scenario analysis
Guest: Nick Boberg (39:11):
Scenario?
Host: Paul Barnhurst (39:11):
Do you believe financial models are the number one corporate decision-making tool?
Guest: Nick Boberg (39:17):
Yes. In my experience, yes.
Host: Paul Barnhurst (39:19):
And then what is your lookup function of choice? Vlookup, right.
Guest: Nick Boberg (39:24):
I started with Excel in 2013 and I started with index. Index match, so what's that, 13, 13 years. Now you're
Host: Paul Barnhurst (39:33):
Making me feel old. I started with X Excel in the nineties.
Guest: Nick Boberg (39:37):
I bet it was V lookups obviously existed, were very prevalent when I first got involved, but I was kind of taught, and even a training I took early on with our clients at PWC was about, look, here's we look up, I appreciate what it can do, but are you aware of the pitfalls of vlookup and are you aware that you're linking to this data set on this other sheet and you started to add another column in and now suddenly over here when you're referencing column three, it's now become column four, but you've lifted this column three and now your formulas update what I want to do, but I
Host: Paul Barnhurst (40:04):
Didn't X match and it choose columns.
Guest: Nick Boberg (40:07):
Never been V lookup ever since X lookup turned up. It's been fantastic and that's replaced my index match use, but I'll still use an index match match over a nested X lookup.
Host: Paul Barnhurst (40:20):
See, I'll use a nested, but I get it because nested can be a little hard for people who understand.
Guest: Nick Boberg (40:25):
Or I also use an XY lookup, which is a custom lambda to do the Excel match match for me.
Host: Paul Barnhurst (40:31):
I figured. I'm like, yeah, there has to be something behind that. Anything else you want to elaborate from that list before we wrap up?
Guest: Nick Boberg (40:38):
What do I say, Lambdas? Where Yes, yes. If they, there's a time and place where in a model I'm doing they can turn up and I'll be okay with it. And that's if the alternative is schedules, which are hundreds of thousands of rows and it's like, look, if we don't need to do that, then let's map this out Clearly. Make sure that you are being clear about what your variables look like within the lambda, within the element of the Lambda and to make it as clear as possible. Yeah, go for it. If you're trying to save yourself five rows, I would suggest you've instead go through a process of having those five rows that are necessary there instead. Because what you are doing by being quick and clever with the lander is you are achieving the same thing, but you are going to remove some transparency for people because not everyone knows a lot of people actually, to be honest, picking up a spreadsheet, do not know or understand what's going on with the Lambda.
(41:30):
And so you're going to lose the transparency and auditability that can come with that. So depends what it's trying to achieve if you're trying to save yourself thousands of rows. Yeah, sure. Well, I have one other thought or comment going through that list. I said no to Python. I said no. Yes to query. I said no to Power bi. I think it's important as a well-rounded modeler that you probably don't need to go down the path of actually doing any of them and with great detail if that's not what you want to do. I do think it's important that you understand and know the capability of each of those things so that you are able to come across circumstances where Excel is not the right solution and instead put forward that this makes more sense with Power Query and Power bi perhaps for the data visualisation piece or no, no, this makes sense in Excel and I think that's an important, if you put yourself out there as an advisor, I think it's important that you know what these things do.
Host: Paul Barnhurst (42:21):
I think that's well said. You really need to understand
Speaker 3 (42:22):
Those limitations because the reality is can you do just about everything in Excel? Yes.
Host: Paul Barnhurst (42:29):
Should you? That's a more detailed discussion. So if our audience
Speaker 3 (42:32):
Wants to learn more about you get in touch, what's the best way for them to do that?
Guest: Nick Boberg (42:37):
LinkedIn net boic there. Company website, boic advisory.co nz, but probably the easiest way, get involved in the Excel sports community. Yeah, I'm pretty active there and doing cases or creating them or hosting the battles, it's all a lot of fun. So
Speaker 3 (42:51):
If you want to get to know Nick, sign up for Financial Model and World Cup. There's your invitation. All right. Thank you Nick so much for carving out some of your morning. Really appreciate you joining and getting the opportunity to chat. So thanks again.
Host: Paul Barnhurst (43:06):
Financial Modeler's Corner was brought to you by the Financial Modeling Institute. This year, I completed the Advanced Financial Modeler certification, and it made me a better financial modeler. What are you waiting for? Visit FMI at www.FMInstitute.com/podcast and use Code Podcast to save 15% when you enroll in one of the accreditations today.
