Golden Rules to Prevent Excel Disasters in Financial Modeling with Gary Knott

I was very keen to teach other people about how so that they should avoid errors. Use a clear, logical workbook structure. Use a clear, clean, consistent design. Write instructions for the users. If it's a tool which is going to be used and regularly updated, you need to make sure the users know how to use it. There's no worry that Excel is going to die anytime soon. So when you're developing any calculation, you need to be aware. Do you understand what it is that you're modeling? I invented the Warren Buffett rule of modeling. So Warren Buffett's famous rule of investing is only invest in what you understand. And. And I amended that for modeling to say only model what you understand. I am a big fan of macros, but not for calculations. I use macros for things like if you need to create an output sheet which needs to be uploaded to the system, then click on the button. It just creates the sheet in the CSV format that the upload system needs and then that's it. Certainly getting trained early. Not try and learn it yourself, but take some formal training course so you can learn.

Welcome to Financial Modeler's Corner, where we discuss the art and science of financial modeling with your host, Paul Barnhurst. Financial Modelers Corner is sponsored by Financial Modeling Institute. Welcome to Financial Modelers Corner. I am your host, Paul Barnhurst. In this podcast, we talk all about the art and science of financial modeling with distinguished financial modelers from around the globe. Financial Modelers Corner podcast is brought to you by the Financial Modeling Institute. FMI offers the most respected accreditations in financial modeling. I'm thrilled to welcome this week's guest to the show, Gary Knott. Welcome to the show.

Thank you very much. Thanks for inviting me.

Really excited to have you. As you know, we start every episode with this question. I know you've heard a few. Tell me that horror story. What's that worst model you've seen in your career?

Preparing for this. Two came to mind, if I may mention. Two. So one was where the calculations and the inputs and the outputs hadn't been separated clearly separated. So in the outputs in the profit and loss account and the balance sheet, there were calculations, lots of calculations. And including adding in the inflation. So it was a great big mess. So if you looked in the sales line, you could see a very complicated calculation there because you only had room in one line to do all the calculations he wanted to do, including inflation. So it was just terrible to follow. So there I would very much imagine.

That would be a mess to follow.

Mention the best practice rule Always split your inputs, your calculations and your outputs into separate sheets. And the other one I had was a complex problem project planning model, which was used for each new project planning. And I had the impression that each time a new user took over the model and had to use it for the new project, and they were probably afraid to delete any old. Any. Any content because they weren't sure what it was for. So they just added in new content. And if they wanted an input, they linked it back to the input from the last time, which was then linked back to the input from the previous time. And you've got these daisy chains. So to trace it back to where, where was the original input made? That was a bit of a struggle. Yeah. So that's not best practice, obviously. Try and reduce the. The content so that it matches the purpose.

Yeah, no, there's something to be said when you get the. I link it here, then I link that back to there, then that one goes back to there and you're like, okay, all six of those could have been linked to one place. Why am I having to trace it out? To figure out where it started.

Exactly. So that's what I always recommend trying to link back, particularly inputs to the original input and not use these, what are sometimes called daisy chains.

Yeah, no, we've definitely seen some daisy chains and they, They're a pain to review. I'm sure I've done a few over my career, but I try not to. So why don't you go ahead and tell us about yourself and your background. Just let the audience know your story.

Yeah, so I started at Deloitte or in. In England 2 Schross they were called at that time, and I found it great. And then there was one client that said they needed some help with Excel modeling and they asked around in the Deloitte team, who can do X, who's good at Excel or Gary's good at Excel, he can do that. So I went along and, and I found it amazing and it's really great. I hadn't been. I hadn't learned any financial modeling best practice rules, but I could see what. They already had a team there, but it was just had. They had too many projects on, so they wanted to have extra capacity. So I went along there and helped them with that and I really got into. I really liked it and I thought, this is what I want to do. And so then I later joined the financial modeling department at Deloitte and after a number of successful years there, I decided to set up on my own. So from 2015 I've now was that nine years now been successfully doing financial modeling and Excel tools and data analysis and training as an independent person. You know I have two employees so they support me but it's great. So my little office here, I'm based. Well I originally come from England. I'm living and working in Germany. I met my wife, she's German, so based in Germany and out of our small office we can service clients around the world. Great.

Well, congratulations on starting your own business. I know that's always a big step and I'm glad it's going well and you're loving what you're doing. But I'm curious you. So you started at Deloitte but you studied. If I remember right, you studied physics and math in college.

Correct.

How did you end up being a finance and accounting person? How did that happen? Tell me that story.

I guess I basically a bit fell into it. I mean I've always been interested in and good at maths. I did have a short stint at the Majesty's Customs and Excise in England as VAT inspector. So there I got to learn about double double entry bookkeeping. But some stage I realized that really wasn't what I wanted to do long term. And I looked around and someone suggested that with maths you can, you can study and become a chartered accountant. So I had to look at that. And first of all you got three years of study and work combined. So it was hard but I decided to have a go at that. And that was, I decided right from the first day that was for me working in accounts it gives you a very good training. So you get to go around different clients and you get to understand different business models and the way different businesses operate, different processes, different and also different personalities. So that was a very good background. And then based upon that I then moved into the modeling department modeling team and was just doing the modeling. So that was great.

Got it. So sounds like you had one, one job that did a little bit with accounting and that led to getting more interest in taking the accounting route to move from math and physics to the accounting world.

Correct. And I always remember double accounting, double bookkeep, double entry bookkeeping. That's. It's hard to learn first of all, but once you've learned stands you in good stead. You can always go back to that. And if I want to understand a process so that I can correctly model it in a model that really helps knowing okay what the two sides of this transaction, okay, sales goes up and the debtors Go up. And it also helps when you're trying to plot, when you're trying to sense check a model. You know, okay, when certain figures are moving, then certain other figures should be moving as well.

Yep. Having that foundation of accounting double entry bookkeeping and the interactions between the different financial statements is really critical so that you could look at it and go, okay, cash is way up, but my account's receivable is down, my accounts payables down. You start wondering, okay, what's going on? Did I raise capital? And you could start to just understand those interactions and go, okay, there has to be a reason for this. If I don't see it, then there's probably something broke in my model, something I did something wrong here.

Yeah, either that or it's something you didn't understand. You've got the aha effect. Ah, yes, of course, when that happens, then that happens and then that has this impact. Yeah.

So that also sometimes, yeah, definitely. Sometimes it's something you did totally wrong in the model formula, whatever. And sometimes it's a lack of understanding and you need to re, rewire some things and some assumptions.

Yeah. Or you get a deeper understanding of how things, what the effects are of certain changes.

Yeah, no, I, I, I'm totally with you on that. So you wrote a book.

Correct.

And I the title, make sure I got this right. Avoid Excel Horror Stories. Yes, I see it there. Avoid Excel Horror Stories. We all have them.

Yes.

So how did the book come about? Let's start there.

Yeah. So I was in the business modeling department at Deloitte and looking at other people's other colleagues models or client models. Sometimes we were doing model reviews and seeing, a lot of times, this is terrible or this is not good, there must be a better way of doing it. And through that I gradually learned these best practice rules that for example, you separate the inputs, the calculations, the outputs, so that kind of thing and structuring it clearly and consistently, those kind of things appeared obvious to me. So I think I'd already worked those out for myself. But certain other tips, like having a master check sheet, that was something I had to learn and implement in my daily doing. And then it became a bit of a something that I was, I was very keen to teach other people about how so that they should avoid errors. So I introduced my Gary's Golden Ground rules. So ggg, Gary's Golden Rules. And I taught people about those in the training courses that I gave internally. And then when I left Deloitte, I was very pleased to receive a glass plaque with my, at that time, three golden rules engraved upon it. So that was a very nice.

What were the three golden rules at that point?

Yeah, one of them was. Which is no longer golden rule, it's more of a principle which is work smarter, not harder.

Love it. What's the other two?

So the other two, I should have prepared for that.

Totally fine.

I should have seen. I wanted to dig out the plaque and show it to you. But anyway, yeah, so there were things like. I think the other two were. Oh yes, that was the other one. Consistency is key. That was one that's. I still very big fan of. Consistency is key. I think of all the rules there are, that's the one, if you follow that one, that can bring you a long way in terms of getting a reliable model. So that means consist making sure the columns are consistent. So if you're building a financial model and the year 2024 is in column F on one sheet, it should be in column F on all the sheets. And why does that matter? Because when you've got a formula which is referring to another sheet, you can then copy the formula across to the right. And if all the columns are consistent, then you shouldn't have any linkage errors. We are linking to the wrong column. So that's very important. And not just consistency of the columns. Consistency of, for example, the rows. So if you have a block that's the profit and loss account for company A with this structure, then that structure should be used for all of the different entities which you're depicting. So that just makes it. It makes it a little easier for you to develop. You develop it once and then copy and copy and paste it and obviously have to change the links. But it's consistent. Look and feel. It just makes it easy for you to develop and quicker. And once you've tested it, once you know it works, you can copy it across and be more reliable in the other entities. And for a user it's great. Once they've understood it, once they can understand all of the P Ls, they know where the structure, they know where they're coming from. And then when you're doing the consolidation, you just add through all the sheets and you know, you can copy the formulas down run across because the structure is the same now. So it's. Yeah, that's my number one tip. I think consistency is key.

That is a huge one. And you know, the example that was running through my head is I did FP&A. So there are times you might have 50 cost centers and you Want to make sure every one of those sheets is the same so when you roll it all up, you don't have issues. Right. If you have different accounts on different rows, that becomes a nightmare. You just really can't do it. Totally agree with you on that consistency. And I love how you mentioned, you know, across the columns too. It makes your formulas easier. It also makes it a lot easier for somebody to audit.

Correct. Yes.

January 2024 is always in column C on every page versus C here, and it's D here and it's F there. Occasionally there may be a page because you have different schedules that you may need to do something a little different for whatever reason. But as a general rule, it should be consistent throughout the model.

Correct. What I have seen, sometimes, which I have delivered, is on some sheets you need more columns at the front, so you can do some ifs or whatever and then. But thereafter, okay, maybe two or three columns offset, but then it's. It's still consistent thereafter. So you can still link from C to F and then copy it across. It's still correctly linked.

Yeah. What I'll often do is add the extra columns on every sheet so that even if they're not using one, it's just kind of.

Yes, that's a good tip. Yeah. So adding blank columns and make them a narrow width. Yeah. So you've got consistency of columns. Exactly. That's a great tip there.

So you had mentioned the three golden rules, but I. I know in the book believe you have some more. So maybe talk about what's your list of rules now? What are the ground rules today?

Yes. Yeah. So the ground rules are. So the first three cover the structure of the workbook, and then the other two are access and understanding. So the first three are use a clear, logical workbook structure. What does that mean? It means decide what worksheets you need and get them in order and give them clear names. So nothing, no rocket science, but just to make it clear so you've got clarity and the users have clarity. And I often put a separator sheet between each of these sections. So this is inputs, this is calculations, this is outputs or whatever.

I do that type of thing with a lot of models. My divider separator sheets, I'll usually highlight them in red, make the entire worksheet black, and it's just to separate the rest of the model.

Yes. Correct. And I usually put after the sheet name a greater than symbol symbol, which means it's like an arrow to the right just to show the sheets to the Right, Yeah.

Do the same paper thing.

And sometimes I split the inputs into transaction data and master data, if that's warranted. Okay, so that's the first one. The next one is. So that's the workbook. So the sheets in the workbook and then looking at each individual sheet, keep your worksheets as clear and simple as possible to make sure your worksheet is clearly laid out so it's easy to use. So that means having the columns at the top all consistent, as we mentioned before. And I usually have a settings sheet where if it's a model, I can set what's the first year and what's the last actual month. So each of the columns with the months in it will automatically identify what period type it is. Is it an actual period or is it a plan period? Any period, any months up to the date you've just input as the last actual month, they will be shown as actual. And thereafter they are plan periods. And those, those period types and period names. That's right, January 24th and so on, those are linked to all of the other sheets. And you can use that on the calculation sheets to say, okay, if the period is an actual, then take the number from the actual sheet or if it's not, then do your calculation to do the plan figure and. And then from the vertical structure, if you've got the profit and loss account, the balance sheet and the cash flow, then to do the. Depends how you do it. If you've only got one company, I tend to put the calculations for the each of the P and L and balance sheet items on a separate sheet. I call them value drivers. If you've got lots of companies, I put them underneath the balance sheet, underneath the P and L and balance sheet and then just clearly label the sections. So here is the financial statements. Then I group the lines together. You can open or close them. Then you've got the value drives, the profit and loss account, then you've got the value drives for the accounts, for the asset, for the balance sheet, the asset side and the balance sheet liabilities and equity side. So that makes it very clear. And, and having these groupings. I'm also a big fan of groupings. You can group, you can close them, open them at the click of a button just to get quickly get to.

The side everything instead of group it.

No, no, no, I don't like that. No, don't hide things and don't put white on white. That's what some people do.

Yes. Or you use. My favorite is the very hidden. If you go in, right, and you can make your sheet very hidden. That's always a fun one.

Yeah, I sometimes do that on client models where we've had sheets, we've had interface sheets which they shouldn't touch for some reason. They unhide them and play around with them.

Yes. I think that's the one, one situation where I agree with you that sometimes you're like, I just don't want them to find it. I'll know they'll ever see it. I can get it, get at it when I want. I just hate when you get a model and you're trying to figure everything out and you finally discover, oh, there's this very hidden sheet and sheet.

That's why if you're doing model reviews or you're taking over a model from someone else and you're amending or extending it, it's always good to use a tool to run through it. I have used obviously Oak, for example, that's probably the most well known one, Okris analysis kit. Yep. Oak for short. But I was. Some of the things weren't what I wanted, so I went away and wrote my own version. So I use that, I call it Checkmate. So. So that's the next time. The next tip is use a clear, clean, consistent design. So you want to make sure that the whole workbook has a clean, professional look and feel so that people, when they open it, they can see immediately, ah, this looks professional. I'm very happy to use this. I've got immediately got a good feeling that, that this is a reliable model. So obviously it doesn't change any of the formulas, but it does make make the users more comfortable in using it and things like not having lots of colors. So restricting yourself. If I'm building a model for a client, I ask them or I look on their website, what's the color scheme and try and make the color scheme similar or same. I can, I can extract what are the rpg, the red, blue, green codes from the logo and use those in Excel to set up a theme and use those. Obviously you have to be careful with clients. If they have a red or orange logo that's a strong color, you have to be careful about not overusing that, especially because red normally is used for warnings. So if you have too much red in your worksheet, in your workbook, it looks like there's too many warnings going on when really you're just trying to use their color scheme and things like clearly marking input cells. So I said before, you need to clearly separate the inputs and the calculations and the outputs, ideally by putting them on different sheets. And if you've got, for example, the actual P and L and balance sheet data, that can definitely go on a separate sheet. But when you're looking at value drivers to calculate how much sales do I think I'm going to make in the future years, then often it's useful to have the assumptions on the same sheet. So how much percentage growth, for example, do I expect for the either total sales or separate the price and the quantities. Separate them out. It's useful to have those on the same sheet. You can put them on an assumption sheet, but I find that you have to then spend time going backwards and forwards between the assumption sheet and the calculation sheet. Too much. It's more convenient to have them all in one place. And then, especially in these cases, it's very important to clearly mark the input cells. I use a gray background with a white border so that users are very clear where can they change things and where they shouldn't change. So if it's not gray with a gray background, then they should leave it alone. So either for inputs. And another big thing I'm keen on is using data validation, where possible, to restrict the inputs. So if you're expecting only positive numbers in this cell, and then you can restrict it so they can only enter a positive number. So if you want, for example, these examples, if you want a month number, when should these payments be made, when should the dividends be paid out each year, you can restrict them to entering a whole number between 1 and 12. So simple things like that. But they can really, if you do want to later, prevent users changing any other cells in the formatting. I use cell styles a lot, so I can click on a cell, this is an input cell, and use the cell style and say this is the input style. And in that it's not just the colors but also the protection. So we say that cell is unprotected, which means if you then later protect the whole workbook, those cells, which are gray input cells, those are still available for users to input. Okay, so that's the. That's the design questions. And obviously there's a lot more of that, which I go into my book, but those are the key principles. And then the last two best practice rules, or golden ground rules I call them, is number four, restrict access inputs and changes. So you make sure that users can only access and change your spreadsheet. So that relates to what I just mentioned. One thing is you can restrict the access so they can only change the input cells and obviously if, if it's particularly if it's got confidential information or any certain users should access the file and make sure you store it in a folder that only the relevant users have access to. If necessary, put a password on it. But we just have to remember the password protection is not foolproof. You can get, you can get access to it, but it helps to some extent to restrict access.

In today's business world, financial modeling skills are more important than ever. With Financial Modeling Institute's Advanced Financial Modeler Accreditation program, you can become recognized as an expert in the field by validating your financial modeling skills. Join the Financial Modeling Institute's community of top financial modelers, gain access to extensive learning resources and attain the prestigious Advanced Financial Model Modeler accreditation. Visit www.fminstitute.com podcast and use code podcast to save 15% when you register. Funny story with that real quick, I had a professor in college that had built this super complex model in Excel and he had affected everything. And I remember at the time and I think it's been fix next I opened it, I think it was an Apache officer, one of the free open source Office tools and it cracked the password basically. Yeah, it was no longer protected when I opened it in that and I remember telling him to look on his face is like hitting me right? Like no. But it's really helpful to see all your detail behind this.

Yes.

So yeah, I agree with you. You know, it's they're not by no means are they foolproof. If somebody wants to hack them, they.

Can find a way they can find correct. Yes. So just be aware of that. And lastly, and perhaps most importantly is to write instructions for the users. If it's a tool which is going to be used and regularly updated, you need to make sure the users know how to use it. In most of my models and tools I have a sheet called Instructions where it just lists in the column. First column has a hyperlink to the relevant sheet, then it tells you next column what should you have to do there? And then have a column with a drop down which says what's the status? Is it to do or is it done? And then when they click on done it goes to green and they can quickly look there. And if and also if someone's started it, not finished it, they can always go back and say ah yeah, that's where I got to. They know where they left off. So that's certainly something I would always recommend. And if it's any degree of complexity more than the basic model, then give A training at the end for the users, make sure they know to use it, give them the tips and tricks, tell them what to look out for, what to do, what not to do. So those are my four, my five golden rules. Yeah, so I've been, I've developed those over a number of years from, from practice and I find they've still been good set in good stead.

Yeah, no, I appreciate you sharing. Those make a lot of sense to me. There's one thing I really liked in there is just the importance of keeping it simple. I mean there are a number of different things but it's very easy, especially early on. I still find myself guilty of it. To make something more complex than it needs to be.

Yes, I think some people, yeah, some people like to show off or show off to show that they can calculate something. A lot of steps in one cell and I prefer to split them over several lines and one. So you can easy. Each step is then clearer, simpler, easy to understand, less risk of errors and you can change it more easily so it takes up more space. But as I was fond of saying, Excel doesn't charge you by the line so you can take those extra few lines to make the calculations clearer.

Agree with you, if it makes it easier for everybody to understand, just break it out. At the end of the day there's no reason to try to save space. I like that. That doesn't charge by the line.

Exactly. So when I'm writing a formula I try and follow these the priorities. The first one is that the result is correct. That's obviously has the top priority or sometimes say it has to be materially correct. So I think you can use a simplifying because normally when I'm talking about planning numbers, you're never going to hit that plan number anyway. You're never going to hit it on the nose. So if you can make the calculation a bit simpler, it's not then wrong, it's. It's just a simpler calculation logic. And if it's still good enough, then you can make the logic simpler. That's easier to understand, less risk of errors. I think that's something you can certainly do. And then the second priority is then making it easy to understand and that's where the step comes in spreading calculations if necessary over multiple lines. And the third one is getting it short. So you shouldn't give priorities again short or getting it all in one cell over the other two priorities. Yeah, and sometimes I, I use this, the rule of thumb. So that means your formula shouldn't be Longer than your thumb.

Yeah, I've, I've heard that one before. The kind of rule of thumb idea is keep it as short as you can.

Keep it as short as you can. Exactly. And if it's, if you need it longer, then spread it over multiple rows. And obviously with things like dynamic arrays, that helps you in this respect.

Pretty amazing what you can do with them.

I continue to be amazed when I see, and I look at videos on YouTube and so on to see what people are doing them. You can do some pretty amazing stuff.

Yeah, I agree with you. I've seen some things people do and I'm like, I would have never thought of doing that. Some of the formulas people write is pretty amazing what they can get Excel to do.

So I think this, there's no worry that Excel is going to die anytime soon.

I agree with you. Despite what some people might say on LinkedIn. Correct.

It's the same with AI and Copilot. That's just going to write the spreadsheets for you? No, I don't think so. I think it's could be, could end up in that situation in the future sometime, but I don't think it's going to happen anytime soon. It can certainly support you. That's very good. But you need to have the background in understanding what is the challenge that the client has or the user has that you need to solve with this model or workbook and to make the right design choices to get the right data that you need and so on and so forth.

I agree with you. I mean, Copilot is going to help us. Yes, there are areas where it can write things, can do work for us, but there's. It's not going to take our jobs. There's a role for us in all of this and we still need to understand what we're doing. In fact, I think in some ways the people who use AI and take advantage of it the best are those that really understand the processes.

Yes.

Those don't understand anything and just try to use it to do everything. So it's an exciting time for sure on that front. But have another. Corey, kind of going back to your book, before we move on, I have one more question I want to ask around that. So in the book you provided 20 real life horror stories.

Correct.

Was there a common theme across those horror stories? Like, did you find some common themes that came out from those horror stories?

Yes, I, I split them into different sections, the different kinds of errors which occurred, but I think we can basically split them into two groups. Errors which are caused. Which were errors in the, in, in the spreadsheet itself. So formulas wrong or whatever or user errors, quite a lot were in the area of user errors. So copy and pasting was done incorrectly or even on, on the grounds of confidentiality, things were sent out and they had confidential information. So maybe, perhaps. Should I mention. Can I mention a couple.

Sure, go ahead.

Yeah. So there was one. Which doctor job offers. So the National Health Service in Britain used spreadsheets for assessing the doctors which they had for interviews. And they used them to capture the interview results and to then determine which doctors or which interviewees will get a job offer. And then they copied the results across from each of the interviews into a master spreadsheet. But that didn't happen properly, correctly, and they were in different formats. That's also another key point when I mentioned it before, consistency is key. If you're going to copy data from one spreadsheet to another, make sure that the format is consistent. Spend the time before you send things out to make sure that you've got all the data you need. They're all consistent formats. You can do that either copy, paste or nowadays probably you can use power queries better. But anyway, this time they didn't do that. The format of the spreadsheets which they got back from the various interview interviews was a different format. When they copied and pasted, they made various errors, which meant basically the upshot was that some interviewees got rejections when they're actually very good. And the other way around. Yeah, so that's not problem there. Yeah. Okay. So basically you need to. And when you're copying and pasting, if you're copying and pasting, this case it was results. But if you're copying pasting formulas, you need to be aware of that. That can also create. Even if in the source that was not an error. And when you copied across another error, if you've got the fixing cell fixing incorrect, then that can also give you incorrect. And then there was the big one, the London Whale case, which probably the one which had the biggest losses, over $6 billion of losses, that was. JP Morgan used a spreadsheet model to determine the value at risk for its credit portfolio. Something that's very technical in nature. And the 6 billion which was apparently lost was also due to fines. It wasn't only due to the spreadsheet errors, but spreadsheet errors were a component of it. And in calculating value at risk, they apparently added two numbers instead of taking the average. So it sounds quite a simple error, but it Had a big impact. And it's the point I would make here is it's a complicated area. So when you're developing any calculation, you need to be aware, do you understand what it is that you're modeling? So I invented the Warren Buffett rule of modeling. So Warren Buffett's famous rule of investing is only invest in what you understand. And I amended that for modeling to say only model what you understand. So in this case, the subject value at risk is a technical. You need to make sure you understand it before you try and do the modeling for it. Otherwise you're going to get. You could have a high risk of errors. And obviously always, always test. I'm also a big fan of testing. That's another key way to avoid or detect errors. Test each individual calculation, then test it in the next biggest block. So test your sales calculation, then test your P and L. Does the, does the profit look correct? And then test that when it's flung into the cash flow and so on, does everything look okay? And if it's a really complicated model and with very important to be used for a very important decision, then get someone else to check it as well. As I say, some of the errors were in the model itself. There's some where people added up ranges and they missed out some of the lines. So simple errors like that, but they can have big, big results, big impacts on the decisions made and have caused financial loss and also loss of reputation. So it's not always just a financial loss. Obviously these entities suffer from a loss of reputation as well. What I found it a bit sad was in researching for the book that there wasn't often the technical details behind what was the actual error. So in the news stories when they're presented, they're often presented from the shock horror, there was a big error and it cost this much loss of earnings or whatever, or this much fine. But what was the error that actually occurred? And it's only in a few cases which we have the details for that, but certainly you can identify some basic principles which basically match up to my golden ground rules of the way you should work to try and avoid them. And one that also came up a number of times and also quite recently was the. And I mentioned this here, so your users are aware of it in a pivot table. If you've got a pivot table, you can drill down by double clicking on any cell and it shows you all the data behind it. And so. But a lot of people don't seem to know this. And if the data behind is confidential. You think you send someone the pivot table, they can't see the data behind it. Oh, yes they can. They double click. So a number of organizations have been fined for this very fact, where someone double clicked and found the information behind with personal information which they shouldn't have been able to see.

Yeah, all the data is stored there. And so.

Now the trick is there. If you want to send the information out that's in a pivot table, don't send a pivot table, send a PDF of it. So obviously you lose the functionality of the pivot table, but that's the safest way to protect confidential information.

Great reminder there. I like that one because, yes, it's easy to, if you're not careful, send out data that will get you in trouble.

Correct? Yes. And perhaps just to end on one of my favorite sayings, and having lived in Germany, there's a favorite saying of mine in Germany, trust is good, but control is better. So I'm always very keen fan of putting checks in models, collating them all on a master check sheet and then add them all up. So I write my checks that a result of zero means no error identified. So the typical example is you take the sum of the assets side of the balance sheet, deduct the sum of the other side, the equity and liabilities, and if the difference is zero, then your balance sheet balances and you can send that through to a master check sheet, and then if you add them all up, all the checks up, and they're totally zero, then you haven't identified any issues. So that's great. And you can send that result checks okay throughout the whole model. Which means if on any sheet you do something which creates an error, you can immediately see. I always have my eye up at the top left of each sheet to see if the checks remain okay. And if not, then you know that what you just did cause the error. So it helps you find it much more quickly. So that's something definitely recommend doing.

Yeah, definitely. Having a good process for error checking is really important. Totally agree with you on that. Appreciate you sharing some of those horror stories. Couple more questions here and then we're going to move to the rapid fire section where we get to put you on the spa. So I'm curious, going to kind of change gears on here, on, on you here a little bit. What's your favorite Excel shortcut? What's your go to or the one you like the Most?

Okay, the one I have two F2 and F4. Yes, F2 is edit, because I'm always editing that and F4 because it has two. Two things. If you. If you're using. Writing a formula with a cell reference, you click F4, it puts the dollar signs in it to fix it. Or if you're not editing a formula, it just repeats what you just did. So that's great.

I haven't used it for the second one. I use it all the time. For the dollars.

Yeah, yeah, exactly.

Good. That's. That is a great one. I appreciate both of those. And then I'm.

I like that because you just press F4 and you get more dollars. That's great.

I'll have to remember that. Well, what's the one I've heard? What do gold diggers and Excel references have in common?

No, you lost me on that one.

So if you want to. If you want them to stay in place, you have to throw money at them.

Ah, okay. Yeah. All right, good.

So there you go. Yeah, that's what I heard the other day. Next question we'll ask you here. As you look back over your career, what's the one thing you've learned that's. That's helped you the most in being more productive? What's that kind of one. One lesson you'd share?

Yeah. So I think shortcuts, which you just mentioned, but in the recent years. Power Query. Definitely.

I love me some Power Query. It can save you a huge amount of time.

Yes.

I was doing some training this last week, and the one guy's like, I have this process. It's in a nightmare. He starts explaining to me, I'm like, are you familiar with Power Query? He's like, no. I'm like, you should be able to reduce that at least 95%, possibly get it to a. Press a button. And he looked at me and he's like, we could do that. I could kiss you. And I have a training partner. I go, well, you can kiss Ron if you want. But, you know, because he's just so excited. Like, you mean I could take something from six hours and get it to where it takes me a minute or two? Yeah, yeah. If you set it up right, you can.

That's right. Yeah. Yeah, exactly. And unpivot by click of a button. I'm pivot. That's great.

I love me some on pivot. Yeah. It's also another fun one. You know, people's eyes light up like, oh, I can do that. That easy. Big, big fan of Power Query. All right, so we're going to move to rapid fire. I know you very good. Or.

Yes, I have. I'm looking forward to it.

I'll lay out the ground rules just to make sure we're on the same page. You get no more than 15 seconds to answer each of these. You can't tell me? It depends. Then at the end you can elaborate our one or two. Because I realize there's nuance to many of these. It's usually not as simple as one or the other. So we'll get started here. First one, Circular or no circular references?

No.

Circular VBA or no vba?

No. Vba.

Horizontal or vertical model?

Horizontal.

All right. Dynamic arrays in your model. Yes or no?

Financial model? No. Okay.

External workbook links. Yes or no?

No.

Alrighty. Named ranges in your model or no named ranges?

A few. Yes.

That's usually what we get. Kind of a few. Be sparing with it. Do you follow a formal standards board for your modeling? Yes or no?

No.

I figured you followed Gary's ground rules, correct?

That's right.

Will Excel ever die?

No.

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

Yes.

I know there's nuance to that one. I could tell by the way you're like I answer this one. All right. Use of sheet cell protection in your models, yes or no?

Yes.

All righty. Do you believe financial models are the number one corporate decision making tool?

Yes, definitely.

All right, and then the last one. What's your lookup function of choice? You like choose Vlookup, Index match, xlookup or something else?

Index match.

Okay, great. Which one would you like to elaborate on? Of those that you answered, I know there are a couple that you paused for a minute.

Yeah, I mean the circular references. We're talking there about the circular reference, what I call logical circular references where you have interest on the. On the bank loan. So that means you need. Means you need more bank loan, which means you have more interest and so on. So those kind of things. I think if you have monthly periods, I prefer not to have them. I just calculate the interest based upon the opening balance plus any drawdowns and maybe any repayments, but not the average of the opening closing balance to avoid circular references. And I've done simple models to calculate to see what the effect is. And you can see the effect dependence obviously on the model, but it's not so great. Where I do see a need for is if you have annual periods because there the values can change dramatically over the period of a year. And if you just take the opening balance, you can end up certainly in the first year where you take up the Loan, you've then got no interest and as you take the opening balance plus the drawdowns. Okay, so in that circumstance there could be a need to use some kind of circularity. And then obviously you've got the choice. You can either turn on the iterations. I'm not such a big keen on a big fan of that, because if you then introduce a second circular reference which was unintentional and not wanted, you won't spot it so easily. Or you can use a macro to do the copy paste. So you haven't removed the circularity, you just controlled it a bit and it basically ends up with a Mac copy paste macro. It just does what Excel does in the background. If you turn the iterations on, you're just making it in the control, giving the control to the user and making sure there's no other undesired circular references in them. Sure.

I know a lot of people use macros, kind of control that environment.

And then I was going to set to the macros. I am a big fan of macros, but not for calculations. I use macros for things like if you need to create an output sheet which needs to be uploaded to the system, then click on the button, it just creates the sheet in the CSV format that the upload system needs and then that's it. And maybe for hiding sheets, which we spoke about hiding sheets earlier. But sometimes if you. For certain end users, it's easy for them if they see less sheets. They only see the sheets that they need to see for the inputs or the outputs. That's easy for them to, to manage than seeing all the 20 sheets in the model. They only see the three or four that they need to see.

Yep, makes sense. Well, I appreciate you kind of running through those, sharing your thoughts on that. Always fun to get different people's perspectives. I don't think I've had two people answer every single question the same yet. Right. There's always a little bit of diversity and nuance in thought, and that's so true in modeling. If it was simple and the exact same for everybody, we wouldn't all have our own set of rules that are similar but slightly different. Because like I say, it's an art and a science and you gotta, gotta, you gotta get both sides of those. Right? So kind of speaking of that as we wrap up here, I've enjoyed our interview. I want to ask you what's one piece of advice you would share with our audience? If they want to be a better financial modeler today, what should they focus on.

Well, certainly getting trained early, not try and learn it yourself, but take some formal training course so you can learn the basics. So it's the same with anything you're doing, even with sports or whatever you want to learn. Get the basics under your belt first of all, so you can work with the best practice and not start off learning wrong things. Do it the wrong way.

I wish I had done that. That was definitely something I didn't have at the beginning of my career. So it took a long time for me to develop good habits. But that's another story. The last question for you, if our audience wants to learn more about you or get in touch, what's the best way for them to do that?

So I think basically follow me on LinkedIn so you can send me a contact or a request or just follow me. I'm posting every week with tips and tricks there. I do have a website from my from. I have two websites, not minus consulting, but that's very much static. And I also have an Excel tips spreadsheet website called how to number two howtoexcel.com Mostly, I would say just connect with me or follow me on LinkedIn and then you see my weekly tips and my comments if you're following me.

All right, great. Well, thank you so much for joining the show, Gary. Enjoyed chatting with you today and look forward to sharing this with the audience.

Thank you very much. Thank you for the invitation and the chance to speak with you. I find it very, very good, very enlightening. Thank you very much, Paul.

Thank you. Appreciate it.

Have a good day.

You too.

Bye Bye.

Financial Modelers Corner was brought to you by Financial modeling institute. Visit fmi@www.fminstitute.com podcast and use code podcast to save 15% when you enroll in one of their accreditations today.