Golden Rules to Prevent Excel Disasters in Financial Modeling
Show Notes
Welcome to the Financial Modeler's Corner (FMC), where we discuss the Art and Science of Financial Modeling with your host Paul Barnhurst.
Financial Modeler's Corner is sponsored by the Financial Modeling Institute (FMI), the most respected accreditation in Financial Modeling globally.
In today’s episode, Paul engages in a conversation with Gary Knott, to discuss invaluable insights and tips on avoiding common Excel pitfalls, structuring workbooks for success, and the importance of consistency and clarity.
Gary is a seasoned financial modeling expert and author of "Avoid Excel Horror Stories." He shares his golden ground rules for Excel modeling. With a background at Deloitte and his successful consultancy, Gary's practical advice and innovative tips have helped countless professionals enhance their modeling skills.
Key takeaways from this week's episode include:
Organize workbooks with clear, logical structures. Separate inputs, calculations, and outputs into distinct sheets. Use consistent naming conventions and separator sheets to enhance clarity and ease of use.
Maintaining a consistent design throughout the workbook. Use uniform column and row structures across sheets. Consistency minimizes errors and simplifies formula management, making models easier to audit and understand.
Formal training in financial modeling is crucial. It establishes a solid foundation, ensuring you learn best practices early on, which prevents the development of bad habits and enhances overall modeling skills.
Real-life horror stories highlight common errors, such as improper data copying and pasting. Ensuring consistent data formats and understanding the intricacies of your calculations are essential to avoid costly mistakes.
Use Macros for repetitive tasks like generating output sheets, not for complex calculations. They help automate processes and reduce manual errors but should be applied judiciously to maintain model integrity.
Implement data validation to restrict inputs. This minimizes user errors by ensuring only valid data entries are made, such as positive numbers or specific date ranges, enhancing the reliability of your models.
Regularly test models to ensure accuracy. Use master check sheets to consolidate checks and quickly identify errors. This proactive approach helps maintain the model’s integrity and reliability over time.
Quotes:
Here are a few relevant quotes from the episode on financial analysis and modeling:
“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.”
“Certainly, getting trained early, not trying to learn it yourself, but taking some formal training course so you can learn the basics.”
“I'm always a very keen fan of putting checks in models, collating them all on a master check sheet, and then adding them all up.”
“If the difference is zero, then your balance sheet balances. You can send that through to a master check sheet. Then if you add them all up, all the checks up and they're totally zero, then you haven't identified any issues”
Sign up for the Advanced Financial Modeler Accreditation Today and receive 15% off by using the special show code ‘Podcast’. Visit www.fminstitute.com/podcast and use code “Podcast” to save 15% when you register.
Go to https://earmarkcpe.com, download the app, take the quiz and you can receive CPE credit.
View and download the Financial Modeling Code at financial-modelling-code.ashx (icaew.com)
Follow Gary:
Website - https://www.knott-consulting.com
Follow Paul:
Website - https://www.thefpandaguy.com
LinkedIn - https://www.linkedin.com/in/thefpandaguy
TikTok - https://www.tiktok.com/@thefpandaguy
YouTube - https://www.youtube.com/@thefpaguy8376
Follow Financial Modeler's Corner
LinkedIn Page- https://www.linkedin.com/company/financial-modeler-s-corner/?viewAsMember=true
Newsletter- Subscribe on LinkedIn- https://www.linkedin.com/build-relation/newsletter-follow?entityUrn=7079020077076905984
In today’s episode:
(00:55) - Introduction
(01:40) - Guest Introduction
(01:50) - The worst Financial Model the Guest Experienced
(01:56) - First Horror Story and Mixed Calculations
(02:50) - Second Horror Story and Daisy Chains
(04:06) - Guest’s Career Journey
(05:50) - The Role of Accounting in Financial Modeling
[08:45] - Guest’s Introduction as Author
(10:00) - Golden Ground Rules for Financial Modeling
(14:00) - Importance of Using a Clear and Logical Workbook Structure.
(17:00) - A clean, Consistent Design to Make Models Professional and User-friendly.
(23:10) - Instructions for Users to Ensure They Understand How to Use the Model.
(29:00) - Horror Stories from Financial Modeling
(33:40) - Tips and Tricks and Benefits of Using Power Query
(37:19) - Rapid-Fire Session
(42:10) - Guest's Contact Information
Full Show Transcript
Host: Paul Barnhurst:: Welcome to Financial Modeler's Corner, where we discuss the art and science of financial modeling with your host Paul Barnhurst. Financial Modeler's Corner is sponsored by the Financial Modeling Institute.
Host: Paul Barnhurst:: Welcome to Financial Modeler's 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 Modeler's 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.
Guest: Gary Knott:: Thank you very much. Thanks for inviting me.
Host: Paul Barnhurst:: 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 the worst model you've seen in your career?
Guest: Gary Knott:: In preparing for this two came to mind, if I may mention two. So one was where the calculations, 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 including adding in the inflation. So it was a great big mess. So if you looked at the sales line, you could see a very complicated calculation there because you only had room in one line to do all the calculations you wanted to do, including inflation. So it was just terrible to follow.
Host: Paul Barnhurst:: I can imagine, that would be a mess to follow.
Guest: Gary Knott:: Mention the best practice, always split your inputs, your calculations, and your outputs into separate sheets. The other one I had was a complex project planning model which was used for each new project planning. I had the impression that each time a new user took over the model and had to use it for a new project, they were probably afraid to delete any old content because they weren't sure what it was for. So they just added in new content. 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. You've got these daisy chains. So to trace it back to where was the original input made was a bit of a struggle. So that's not the best practice, obviously. Try to reduce the content so that it matches the purpose.
Host: Paul Barnhurst:: Yes, I know 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 do I have to trace it out to figure out where it started?
Guest: Gary Knott:: Exactly. So that's what I always recommend, trying to link back, particularly, the inputs to the original input, and not use these, what are sometimes called daisy chains.
Host: Paul Barnhurst:: No, we've seen some daisy chains and 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.
Guest: Gary Knott:: I started at Deloitte or in England. Touche Ross, they were called at that time, and I found it great. Then there was one client that said they needed some help with this, with Excel modeling. They asked around in the Deloitte team who's good at Excel. It's all Gary's good at Excel. You can do that. So I went along and and I found it amazing. It's great. I hadn't learned any financial modeling or best practice rules, but I could see that they already had a team there but it was just they had too many projects on, so they wanted to have extra capacity. So I went along there and helped them with that. I got into it, I liked it, and I thought, this is what I want to do. 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, was that nine years now, I have been successfully doing financial modeling and Excel tools and data analysis and training as an independent person. I have two employees. So they support me, but it's great. So my little office here, I'm based when I originally come from England. I'm living and working in Germany. I met my wife. She's German. So based in Germany and at a small office, we can service clients around the world. Great.
Host: Paul Barnhurst:: Well, congratulations on starting your own business. I know that's always a big step, and I'm glad it's going well. You're loving what you're doing. But I'm curious. So you started at Deloitte, but if I remember right, you studied physics and math in college. How did you end up being a finance and accounting person? How did that happen? Tell me that story.
Guest: Gary Knott:: I guess, I, a bit, fell into it. 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 a VAT inspector. So there I got to learn about double-entry and bookkeeping. But at some stage, I realized that wasn't what I wanted to do long term. I looked around and someone suggested, do it with maths. You can study and become a chartered accountant. So I had to look at that. First of all, you've got three years of study and work combined. So it was hard, but I decided to have a go at that. That was what I decided right from the first day that for me working in accounts gives you 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. Then based on that, I moved into the modeling department modeling team and was just doing the modeling. So that was great.
Host: Paul Barnhurst:: Got it. So sounds like you had one job that did a little bit with accounting. That led to getting more interest and taking the accounting route to move from math and physics to the accounting world.
Guest: Gary Knott:: Correct. I always remember double accounting, double bookkeeping, and double-entry bookkeeping, it's hard to learn first of all. But once you've learned it, it stands you in good stead. You can always go back to that. If I want to understand a process so that I can correctly model it in a model, that helps, "Okay, what are the two sides of this transaction? Okay, the sale goes up and the debtors go up." That 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.
Host: Paul Barnhurst:: Yes. Having that foundation of accounting, double entry bookkeeping and the interactions between the different financial statements is critical so that you could look at it and go, okay, cash is way up, but my accounts receivable is down, my accounts payable is down. You start wondering, okay, what's going on? Did I raise capital? 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 broken in my model. I did something wrong here.
Guest: Gary Knott:: Yes, either that or it's something you didn't understand. You've got the 'Aha' effect. Aha! yes, of course, when that happens then that happens, then that has this impact.
Host: Paul Barnhurst:: Yes, definitely. Sometimes it's something you did wrong in the model formula whatever. Sometimes it's a lack of understanding and you need to rewire some things and some assumptions.
Guest: Gary Knott:: Or you get a deeper understanding of what the effects are of certain changes.
Host: Paul Barnhurst:: No, I'm totally with you on that. So you wrote a book. The title is, make sure I got this right, Avoid Excel Horror Stories. Yes, I see it there. Avoid Excel Horror Stories, we all have them. So how did the book come about? Let's start there.
Guest: Gary Knott:: I was in the business modeling department at Deloitte and looking at other people's or colleagues' models, or clients' models, sometimes we were doing model reviews and seeing them a lot of times, this is terrible, or this is not good. There must be a better way of doing it. Through that I gradually learned these best practice rules, for example, you separate the inputs, the calculations, and the outputs, so that kind of thing and structuring it clearly and consistently, those kinds 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. Then it became a bit of something that I was very keen to teach other people about how they should avoid errors. So I introduced Gary's Golden Ground rules. So GGG, Gary's Golden Ground rules and I taught people about those in the training courses that I gave internally. 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 very nice.
Host: Paul Barnhurst:: What were the three golden rules at that point?
Guest: Gary Knott:: One of them was, which is no longer a golden rule, it's more of a principle which is "Work smarter, not harder."
Host: Paul Barnhurst:: Love it. What are the other two?
Guest: Gary Knott:: So the other two. I should have prepared for that. I should have seen it. I wanted to dig out the plaque and show it to you. But anyway. So there were things like I think the other two were, "Consistency is key", that was one. I am still a 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 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. Why does that matter? Because when you've got a formula that is referring to another sheet, you can then copy the formula across to the right. If all the columns are consistent, then you shouldn't have any linkage errors when you're linking to the wrong column. So that's very important. Not just the consistency of the columns but the consistency of, for example, the rows.
Guest: Gary Knott:: 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 that you're depicting. So it just makes it a little easier for you to develop. You develop it once and then copy and paste it. Also, you have to change the links. But it has a consistent look and feel. It just makes it easier for you to develop and quickly. Once you've tested it, once you know it works, you can copy it across and be more reliable in the other entities. For a user, it's great. Once they've understood it, once they can understand all of the panels, they know the structure, they know where they're coming from. Then when you're doing the consolidation, you just add through all the sheets and you know, you can copy the formulas down and across because the structure is the same. So that's my number one tip. I think consistency is key.
Host: Paul Barnhurst:: That is a huge one. You know, the example that was running through my head is I did FP&A. So there are times when 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 can't do it. Agree with you on that consistency. I love how you mentioned, across the columns, too, it makes your formulas easier. It also makes it a lot easier for somebody to audit.
Guest: Gary Knott:: Correct, yes.
Host: Paul Barnhurst:: January 2024 is always in column C on every page versus it's C here, 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.
Guest: Gary Knott:: Correct. What I have seen sometimes which I have to live with, is on some sheets, you need more columns at the front, so you can do some ifs or whatever. But thereafter okay, maybe 2 or 3 columns offset. But then it's still consistent thereafter. So you can still link from C to F and then copy it across if it is still correctly linked.
Host: Paul Barnhurst:: What all often do is add the extra columns on every sheet so that even if they're not using one, it's just kind of there.
Guest: Gary Knott:: Yes, that's a good tip. So adding blank columns and making them a narrow width, you've got consistency of columns. That's a great tip there.
Host: Paul Barnhurst:: You had mentioned the three golden rules, but in the book, I believe you have some more. So maybe talk about that. What's your list of rules now? What are the ground rules today?
Guest: Gary Knott:: 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 deciding what worksheets you need, getting them in order, and giving them clear names. So not rocket science. But just to make it clear. So you've got clarity and the users have clarity. I often put a separator sheet between each of these sections. So this is input, these are calculations, these are outputs, or whatever they happen to be.
Host: Paul Barnhurst:: I do that type of thing with a lot of models by divider and separator sheets. I'll usually highlight them in red, and make the entire worksheet black. It's just separate the rest of the model.
Guest: Gary Knott:: Correct. I usually put after the sheet name greater than the symbol, which means it's like an arrow to the right just to show the sheets to the right.
Host: Paul Barnhurst:: Right, I do the same type of thing.
Guest: Gary Knott:: Sometimes I split the inputs into transaction data and master data if that's warranted. So that's the first one. The next one is so that's the workbook. So the sheets in the workbook. Then looking at each individual sheet, keep your worksheets as clear and simple as possible to make sure your worksheet is laid out. So it's easy to use. So that means having the columns at the top or consistent as we mentioned before. I usually have a setting sheet where if it's a model, I can set their 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? So 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 planned periods. Those period types and period names, month 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 actual, then take the number from the actual sheet.
Guest: Gary Knott:: Or if it's not, then do your calculation to do the plan figure. Then from the vertical structure if you've got the profit and loss account in the balance sheet, and the cash flow then that depends on how you do it. If you've only got one company, I tend to put the calculations for each of the PNL 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 PNL and balance sheet, and then just clearly label the sections. So here are the financial statements. Then you've grouped the lines together. You can open or close them. Then you've got the value that drives the profit and loss account. Then you've got the value drives to the accounts for the assets for the balance sheet, the assets side, and the balance sheet, liabilities, and equity side. So that makes it very clear. and having these groupings, I'm a big fan of groupings, you can group them, you can close them, and open them up at the click of a button just to quickly get to everything.
Host: Paul Barnhurst:: Can I hide them instead of group it?
Guest: Gary Knott:: No, no, I don't like that. Don't hide things and don't put white on white. That's what some people do.
Host: Paul Barnhurst:: Yes or you use my favorite is very hidden if you go in right, you can make your sheet very hidden. That's always a fun one.
Guest: Gary Knott:: 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.
Host: Paul Barnhurst:: I think that's the one situation where I agree with you that sometimes you're like, I just don't want him to find it. I know they'll never see it. I can 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.
Guest: Gary Knott:: That's why if we'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 opus analysis kit, oak for short. But some of the things weren't what I wanted, so I went away and wrote my version. So I used that. I call it checkmate. So 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 the people, when they open it, they can see immediately, ah, this looks professional. I'm very happy to use this. I immediately got a good feeling that this was a reliable model. So obviously it doesn't change any of the formulas, but it does 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, I look on their website, what's the color scheme and try and make the color scheme similar or the same. I can extract the RPG, and the red blue green codes from the logo and use those in Excel to set up a theme and use.
Guest: Gary Knott:: 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 and your workbook, it looks like there are too many warnings going on when it's just trying to use their color scheme and things like clearly marking input cells. As I said before, you need to separate the inputs, the calculations, and the outputs, ideally by putting them on different sheets. If you've got, for example, the actual PNL and balance sheet data, that can go in a separate sheet. But when you're looking at value drivers to calculate how much sales do I think you're 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 either the total sales or the price and the quantity separate them out? It's useful to have those on the same sheet.
Guest: Gary Knott:: You can put them on an assumption sheet, but I find that you have to then spend time going backward and forward between the assumption sheet and the calculation sheet too much. It's more convenient to have them all in one place. Then, especially, in these cases, it's very important to mark the input cells. I use a gray background with a white border so that users are very clear about where they can 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 input. another big thing I'm keen on is using data validation to, where possible, restrict the inputs. So if you're expecting only positive numbers in this cell, then you can restrict it so that they can only enter a positive number. So if you want, for example, 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 from changing any other cells in the formatting. I use cell styles a lot so I can click on a cell.
Guest: Gary Knott:: This is an input cell and use the cell style and say this is the input style. In that, it's not just the colors but also the protection. So we say that the cell is unprotected, which means if you then later protect the whole workbook, those cells that are gray input cells are still available for users to input. So that's the design questions. There's a lot more of that which I go into my book. But those are the key principles. Then the last two best practice rules or golden ground rules I call 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. Obviously, if it's particularly if it's got confidential information or only certain users should access the file, then 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 that password protection is not foolproof. You can get you can get access to it, but it helps to some extent to restrict access.
Host: Paul Barnhurst:: In today's business world, financial modeling skills are more important than ever with financial modeling institutes. 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 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 who had built this super complex model in Excel, and he had protected everything. I remember, at the time, I think it's been fixed. Next, I opened it, I think it was in Apache Office or one of the free open-source office tools, and it cracked the password. It was no longer protected when I opened it, and I remember telling him, and the look on his face was like hitting me. No, but it's helpful to see all the details behind this. So I agree with you. By no means are they foolproof. If somebody wants to hack them, they can find a way.
Guest: Gary Knott:: They can find them. Correct. So just be aware of that. Lastly, and perhaps most importantly is to write instructions for the users. If it's a tool that is going to be used and regularly updated, you need to make sure the users know how to use it. So in most of my models and tools, I have a sheet called instructions where it just lists in the column. The first column has a hyperlink to the relevant sheet, then it tells you in the next column what you have to do there. Then it has a column with a drop-down that says what's the status? Is it to do or is it done? Then when I click on done, it goes to green and they can quickly look there. Also if someone's started it and not finished it, they can always go back and say, ah, yeah, that's where I got to, and know where they left off. So that's certainly something I would always recommend. If it's any degree of complexity more than the basic model, then give training at the end for the users, and make sure they know how to use it. Give them tips and tricks, tell them what to look out for, what to do, and what not to do. So those are my four, or five golden rules. So I've developed those over a number of years from practice. I find them still being good in good stead.
Host: Paul Barnhurst:: Yes, I appreciate you sharing. Those make a lot of sense to me. There's one thing I 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.
Guest: Gary Knott:: Yes, some people like to show off or show off that they can calculate something, a lot of steps in one cell, and I prefer to split them over several lines so each step is then clearer, simpler, easy to understand, and 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.
Host: Paul Barnhurst:: I 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.
Guest: Gary Knott:: Exactly. So when I'm writing a formula, I try and follow these priorities. The first one is that the result is correct. It 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 be hit on the nose. So if you can make the calculation a bit simpler it's not then wrong. It's just a simpler calculation logic. If it's still good enough, then you can make the logic simpler. That's easier to understand, and less risk of errors. I think that's something you can certainly do. The second priority is then making it easy to understand. That's where the step comes in, spreading calculations if necessary over multiple lines. The third one is getting it short. So you shouldn't give priority to getting it short or getting it all in one cell over the other two priorities. Sometimes I use this as the rule of thumb. So that means your formula shouldn't be longer than your thumb.
Host: Paul Barnhurst:: I've heard that one before. The kind of rule-of-thumb idea is to keep it as short as you can.
Guest: Gary Knott:: Keep it short. Keep it as short as you can. Exactly. If you need it longer, then spread it over multiple rows. Obviously, with things like dynamic arrays that helps you in this respect.
Host: Paul Barnhurst:: Pretty amazing what you can do with them.
Guest: Gary Knott:: So 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.
Host: Paul Barnhurst:: Yes, 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 are pretty amazing in what they can get Excel to do.
Guest: Gary Knott:: So I think there's no worry that Excel is going to die anytime soon.
Host: Paul Barnhurst:: I agree with you, despite what some people might say on LinkedIn.
Guest: Gary Knott:: Correct, it's the same with AI and copilot. That's just going to write the spreadsheets for you now. I don't think so. I think it could end up in that situation in the future sometime. But I don't think it's going to happen any time soon. It can certainly support you. That's very good. But you need to have the background in understanding what it is, that what is the challenge that the client 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.
Host: Paul Barnhurst:: I agree with you. Copilot is going to help us. There are areas where it can write things, can do work for us, but 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. I think in some ways, the people who use AI and take advantage of it the best are those who understand the processes. Because those who 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 I have another query. Go back to your book before we move on, I have one more question I want to ask about that. So in the book, you provided 20 real-life horror stories.
Guest: Gary Knott:: Correct.
Host: Paul Barnhurst:: Was there a common theme across those horror stories? Did you find some common themes that came out from those horror stories?
Guest: Gary Knott:: Yes. I split them into different sections, the different kinds of errors that occurred, but I think we can split them into two groups of errors, which are calls that were errors in the spreadsheet itself. So formulas are wrong or whatever or use errors. Quite a lot were in the area of user errors. So copy and pasting was done incorrectly or even on the grounds of confidentiality. Things were sent out and they had confidential information. So maybe perhaps can I mention a couple?
Host: Paul Barnhurst:: Sure. Go ahead.
Guest: Gary Knott:: So there was one which was "Doctor job offers." So the National Health Service in Britain used spreadsheets for assessing the doctors, which they had for interviews. They used them to capture the interview results and to then determine which doctors or which interviewees were going to get a job offer. They copied the results from each of the interviews into a master spreadsheet. But that happened didn't happen properly correctly, and that 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. Make. Spend the time before you send things out to make sure that you've got all the data you need, and that all the 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 were actually very good, and the other way around. So that's not a problem there. So when you're copying and pasting, if you're copying and pasting this case, it was results.
Guest: Gary Knott:: But if you're copying and pasting formulas you need to be aware, that can also create even if the source was not an error. When you copy it across to another error if you've got the fixing cell incorrect, then that can also give you incorrect. Then there was the big one, the London Whale Case, which was probably the one that had the biggest losses of over 6 billion dollars of losses. That was why JP Morgan used a spreadsheet model to determine the value at risk for its credit portfolio, something that's very technical in nature. The 6 elements which were lost were also due to fines. It wasn't only due to the spreadsheet errors, but there were spreadsheet errors that were a component of it. In calculating value at risk, they added to numbers instead of taking the average. So it sounds like quite a simple error, but it had a big impact. and it's the point I would make here is that it's a complicated error. 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. I amended that for modeling to say only model what you understand.
Guest: Gary Knott:: So in this case the subject value at risk is 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. Always, always test. I'm also a big fan of testing. That's another key way to avoid or detect errors, test each calculation, then test it in the next biggest bloc. So test your sales calculation, then test your PNL. Does the does the profit look correct? And then test that when it's flowing into the cash flow and so on, does everything look okay? And if it's a complicated model and with very important to be used for and a very important decision, then get someone else to check it as well. As I said, some of the errors were in the model itself. There are somewhere people who 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. These entities suffer from a loss of reputation as well. What I found a bit sad was in researching for the book that there weren't often the technical details behind what was the actual error.
Guest: Gary Knott:: 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 and loss of earnings or whatever or this much fine. But what was the error that occurred? It's only in a few cases that we have the details for that. But certainly, you can identify some basic principles that match up to my golden ground rules of the way you should work to try and avoid them. One that also came up a number of times and also quite recently was the. I mentioned this here, that 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. So but a lot of people don't seem to know this. If the data behind is confidential, you think you sent someone the pivot table. They can't see the data behind it or yes, they can 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.
Host: Paul Barnhurst:: All the data is stored there. So the reminder to people.
Guest: Gary Knott:: The trick is that if you want to send the information out that's in a pivot table, don't send the 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.
Host: Paul Barnhurst:: No. Great reminder there. I like that one because, yes, if you're not careful, and send out data, that will get you in trouble.
Guest: Gary Knott:: Yes. Perhaps just to end, one of my favorite sayings is, having lived in Germany there's a favorite saying of mine in Germany, "Trust is good, but control is better." So I'm always a very keen fan of putting checks in models, collating them all on a master check sheet, and then adding them all up. So I write my checks that a result of zero means no, no error identified. So the typical example is you take the sum of the assets side of the balance sheet and deduct the sum of the other side, the equity, and liabilities. If the difference is zero, then your balance sheet balances. You can send that through to a master check sheet. 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. You can send the result checks throughout the whole model. This means if on any sheet you do something that creates an error, you can immediately see it. I always have my eye up at the top left of the sheet to see if the checks remaining are okay, and if not, then you know that what you just did caused the error, so it helps you find it much more quickly. So that's something I recommend doing.
Host: Paul Barnhurst:: Definitely. Having a good process for checking is important. Agree with you on that. Appreciate you sharing some of those horror stories. Couple more questions here. Then we're going to move to the rapid-fire section where we get to put you on the spot. So I'm curious going to kind of change gears here a little bit. What's your favorite Excel shortcut? What's your go-to or the one you like the most?
Guest: Gary Knott:: I have two, F2 and F4. F2 is the edit because I'm always editing that and F4 because it has two, two things. If you're 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.
Host: Paul Barnhurst:: I haven't used it for the second one. I use it all the time for the dollars.
Guest: Gary Knott:: Exactly.
Host: Paul Barnhurst:: Good. That is a great one. I appreciate both of those.
Guest: Gary Knott:: I like that because you just press F4 and you get more dollars. That's great.
Host: Paul Barnhurst:: I'll have to remember that. Well, what's the one I've heard? What do gold diggers and Excel references have in common?
Guest: Gary Knott:: Nope. You lost me on that one.
Host: Paul Barnhurst:: If you want them to stay in place, you have to throw money at them.
Guest: Gary Knott:: Ah! Okay. Good.
Host: Paul Barnhurst:: So there you go. That's what I heard the other day. The next question we'll ask you here is when you look back over your career. What's the one thing you've learned that helped you the most in being more productive? What is that one lesson you'd share?
Guest: Gary Knott:: I think shortcuts, which you just mentioned, but in recent years, Power Query.
Host: Paul Barnhurst:: I love some Power Query. It can save you a huge amount of time. I was doing some training this last week, and the one guy's like, I have this process. It's 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. 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 because he was just so excited, I could take something from six hours and get it to where it takes me a minute or two. If you set it up right, you can.
Guest: Gary Knott:: That's right, exactly. Unpivot, if I click a button, unpivot. That's great.
Host: Paul Barnhurst:: I love me some unpivot. That's also another fun one. People's eyes light up like, oh, I can do that that easy. Big, a big fan of Power Query. All right. So we're going to move to rapid fire. I know you've heard this before.
Guest: Gary Knott:: Very good. Yes, I have and I'm looking forward to it.
Host: Paul Barnhurst:: 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 on 1 or 2, 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 is circular or no circular references?
Guest: Gary Knott:: No circular.
Host: Paul Barnhurst:: VBA or no VBA?
Guest: Gary Knott:: No VBA.
Host: Paul Barnhurst:: Horizontal or vertical model?
Guest: Gary Knott:: Horizontal.
Host: Paul Barnhurst:: All right, dynamic arrays in your model. Yes or no?
Guest: Gary Knott:: Financial model? No.
Host: Paul Barnhurst:: External workbook links. Yes or no?
Guest: Gary Knott:: No.
Host: Paul Barnhurst:: All right. Named ranges in your model or no named ranges?
Guest: Gary Knott:: A few, yes.
Host: Paul Barnhurst:: 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?
Guest: Gary Knott:: No.
Host: Paul Barnhurst:: I figured you followed Gary's Ground rules.
Guest: Gary Knott:: Correct. That's right.
Host: Paul Barnhurst:: Will Excel ever die?
Guest: Gary Knott:: No.
Host: Paul Barnhurst:: Alright. Will AI build the models for us in the future?
Guest: Gary Knott:: Yes.
Host: Paul Barnhurst:: I know there's nuance to that one. I could tell by the way you're like, how do I answer this one? All right. Use of sheet cell protection in your models, yes or no?
Guest: Gary Knott:: Yes.
Host: Paul Barnhurst:: Alrighty. Do you believe financial models are the number one corporate decision-making tool?
Guest: Gary Knott:: Yes, definitely.
Host: Paul Barnhurst:: All right. Then the last one, what's your lookup function of choice? Do you like CHOOSE, VLOOKUP, INDEX MATCH, XLOOKUP, or something else?
Guest: Gary Knott:: INDEX MATCH.
Host: Paul Barnhurst:: Okay, great. Which one would you like to elaborate on of those? I know there were a couple that you paused for a minute.
Guest: Gary Knott:: The circular references. We're talking here about the circular reference, what I call logical circular references, where you have interest in the bank loan. So that means you need more bank loans, which means you have more interest, and so on. So those kinds of things. I think if you have monthly periods, I prefer not to have them. I just calculate the interest based on the opening balance, plus any drawdowns and maybe any repayments, but not the average of the opening-closing balance. to avoid circular references. I've done simple models to calculate to see what the effect is. You can see the effect. Depending obviously on the model, but it's not so great. Where I do see a need for is if you have annual periods, because they're the values can change dramatically over the period of a year, and if you just take the opening balance, you can end up sending in the first year where you, we take you take up the loan, you've then got no interest unless you take the opening balance plus the drawdowns. Okay. So in that circumstance, there could be a need to use some kind of circularity. Then obviously, you've got the choice. You can either turn on the iterations and not such a big keen or a big fan of that, because if you then introduce a second circular reference, which was unintentional or not wanted, you won't spot it so easily. Or you can use the macro to do the copy paste. So you haven't removed the circularity. You just controlled it a bit and ended up with a copy-paste macro. It just does what Excel does in the background which returns the iterations on. You're just giving the control to the user, and making sure that there are no other undesired circular references in there.
Host: Paul Barnhurst:: I know a lot of people use Macros to kind of control that environment. Then I.
Guest: Gary Knott:: Then I was going to say, 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 that 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. Then that's it. For hiding sheets, which yeah, we spoke about hiding sheets earlier. But sometimes if you for certain end users, it's easier for them. If they see fewer sheets, they only see the sheets that they need to see for the inputs or the outputs. That's easier for them to manage than seeing all the 20 sheets in the model. They only see the 3 or 4 that they need to see.
Host: Paul Barnhurst:: Makes sense. Well, I appreciate you kind of running through those and 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. There's always a little bit of diversity and nuance and thought. That's so true in modeling. If it were simple and the 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 have to 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?
Guest: Gary Knott:: Certainly, getting trained early, not trying to learn it yourself, but taking 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.
Host: Paul Barnhurst:: I wish I had done that. That was something I didn't have at the beginning of my career. It took a long time for me to develop good habits, but that's another story. So 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?
Guest: Gary Knott:: So Follow me on LinkedIn. So you can send me a contact or a request or just follow my posting every week with tips and tricks there. I have two websites. Knott-consulting.com, but that's very much static. I also have an Excel tips website, called how2excel.com. Mostly I would say, just connect with me or follow me on LinkedIn. There you see my weekly tips and my comments if you're following me.
Host: Paul Barnhurst:: 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.
Guest: Gary Knott:: I thank you for the invitation and the chance to speak with you. I find it very good, very enlightening. Thank you very much, Paul.
Host: Paul Barnhurst:: Thank you. Appreciate it.
Guest: Gary Knott:: Have a good day.
Host: Paul Barnhurst:: You, too.
Guest: Gary Knott:: Bye bye.
Host: Paul Barnhurst:: Financial Modeler's Corner was brought to you by the Financial Modeling Institute. Visit FMI at www.fminstitute.com/podcast and use code 'Podcast' to save 15% when you enroll in one of their accreditations today.
