Transcript

Hi there and welcome to this video on financial modeling best practices. I'm Ian Schnoor and I'm gonna take you over the next hour through some of my favorite tips and skills on building best in class financial models. Financial modeling has become one of the most important skills required for finance and accounting professionals, so I'm excited to share some really great tips with you today. Let me go ahead and share my screen so that we can get started. Let me start by doing a quick introduction so that you know a little bit about who I am and my background. First of all, I'm joining you from Canada, from Toronto, Canada, where I am based. I am currently executive director of the Financial Modeling Institute, the world's only financial modeling accreditation organization, but I have spent most of my career thinking about financial modeling, building financial models, and teaching financial modeling. I started as an investment banker at a bank called BMO and at Citibank. And then I started a training firm more than 20 years ago called the Marquee Group, where I taught financial modeling to students all over the world. After 20 years, I sold that training business to training the street a US training firm, and now I'm exclusively focused on helping to build FMI all over the world. I also had the honor and the privilege of creating the financial modeling practical skills module that is now a part of the CFA program and is required by everyone going through level one. I also am a lecturer at a large business school in Canada called Queens University, where I teach a course called Advanced Financial Modeling. All that to say that financial modeling has been a big focus of my career and in this video I'll get into some critical ideas momentarily quickly about FMI. I mentioned that FMI is the only financial modeling accreditation body in the world. We have three levels of accreditation programs and you can take a look at those on your own in these slides or on our website. Our first level is called the advanced financial model or a FM Accreditation program. So let's talk about, , now as I go through here, let's talk about some best practices. And I wanna start with a simple idea that financial modeling is a discipline, it's its own discipline. It requires some knowledge of accounting and of finance and of design and of technical skills, but modeling is its own discipline. And I wanna share with you a whole bunch of ideas in this file. So over the course of today's video, I'm going to share, I've got a notes file that I'm gonna be creating and I'm also gonna show you a financial model that we will use to discuss some of the best practices. So there are five key takeaways that I want you to leave this particular video with. Number one, we're gonna be talking about model flow. Model flow is so critical in the world of modeling. We're gonna talk about how to manage all the assumptions, a key part of any financial model. We're gonna talk about creating a scenarios page. A scenarios page allows us to manage uncertainty and upside and downside volatility. And then we'll be talking about two critical ideas that are pivotal in the world of modeling and they are repeat and link and then build it and link it. So over the next hour I will talk about all of these critical ideas that relate to financial modeling. Let's start with a very basic and simple question, which is, what is a financial model? It could mean many different things, but in a nutshell, in the world of accounting and finance, we usually refer to the term financial modeling to mean a forecast. It is simply a forecast usually of a company's financial statements. Using that we build in a spreadsheet and that's typically Microsoft Excel. All we're doing is building a forecast of the company's income statement, their cashflow statement, and their balance sheet. That's typically it. It sounds fairly simple. Now, first of all, how far in the future do people forecast? Usually people forecast five to 10 years. That's the most common range. We refer to that as the model's periodicity. Anything that relates to timing relates to the sort of the periodicity concepts. And five to 10 is very common. Some models are shorter, some models are longer, but this is a fairly common range. So again, it sounds fairly simple. All we're trying to do is build a forecast of a company's financial statements for a few years, five to 10 years usually into the future. But why? Why do we do that? Why are we building forecasts to look at a company's financial statements into the future? Well, it's because of what I already mentioned. It's because financial models have become the most important decision making tools in all of finance. Almost every major decision that gets made these days is made on the back of a financial model. So let's talk about that. So what types of decisions are we making with our models? Because there are lots and lots of decisions that people make. Well, people make credit decisions based on models. Can a company support more debt? Will it default if it takes on more debt? How about investment decisions? Should you buy or sell the shares of a company? Research forecast? All sorts of research analysts are building models to determine price targets for companies. Then we have standard valuation work and security analysis is often done on the backs of financial models. We build models for m and a purposes to look at mergers and acquisitions. We build models to evaluate leverage buyouts of companies. We also build models to look at operational issues, budgeting to determine head count, marketing spend, et cetera. And finally, we build models to look at sensitivity risk management and there are probably other reasons as well. The point is there are lots and lots of types of reasons that people build their models and lots of types of professionals that therefore need good financial models. It all sounds really simple and straightforward, but there is a problem, there's a very big problem. The problem is that most financial models that get built in the world are a mess. Models are often hard to understand, hard to use, they're often filled with errors and therefore they make it very difficult to make effective decisions. So what I wanna show with you in this video is some key ideas to allow you to build models that are are best in class and that are powerful decision making tools. We'll start by telling you that every good financial model needs to work in two very different ways. Number one, a good model has to work well electronically. And what that means is that every formula has to make sense. So if I email you a financial model and you start clicking off to the side and you go from one cell to the next, you should start to feel like this makes a lot of sense. I understand every single calculation. That's how you should feel in the spreadsheet. Every formula should be logical and make sense and you should be able to understand that's what it means to say a model should work electronically. But I'm sure, I'm sure you have probably opened up a spreadsheet and seen huge formulas that wrap around and around and around in a cell that's not optimal. It's never optimal to have huge long winding formulas because nobody can then understand it. So number one, a good model has to work well electronically. But number two, and just as important, a good model has to work well on paper or as a PDF, you have to be able to print it to paper or to a PDF docent because it needs to feel like a presentation. One of the big secrets that people don't always realize about a financial model is that a good model must be a powerful communication tool. It has to tell a story about the company that you are forecasting. What's the value? Is it worthy of handling more debt? There's a story and your model has to tell it. The other thing that modeling does is modeling is an excellent leadership development tool. People who can build powerful models become excellent leaders because it means they can ask questions, they can challenge assumptions, they can design models, clearly build them well, and they can communicate clearly off the model. So that's why it's highly encouraged that anyone working in accounting and finance has this strong skill. Well, you can't just build a strong model by accident. If you open a spreadsheet and start building, it's not going to get built by accident and be a really effectively effective model all by itself. A good model requires strong planning because model planning and the flow are critical. And I will tell you that a good financial modeler often spends no more than 50% of their time in the model working in Excel. The rest of the modeler's time is spent talking to people, asking questions, doing research, challenging, presenting, making changes to ultimately create a powerful communication tool. And so I wanted to see that model development and model planning are so important. There are a whole bunch of slides from slides 15 through 19 in the slide deck that I shared earlier, , that talk a little bit about model design, model planning and model flow. So I wanna talk about model flow, but before I do that, I wanna show you a financial model right now. I'm gonna flip my screen and I'm now gonna show you a financial model. This is the Henderson manufacturing model. This is one of the sample models used at the Financial Modeling Institute. This is a sample of the type of model that candidates must build on the A FM exam. On the exam, candidates have to build a full financial model of a company from scratch in under four hours and it can feel very, very much like this one. And what you'll notice is that in this model there is a cover sheet at the very bottom here, there's a cover sheet underneath it or next, and you can see a bunch of sheets that's cover, there's about, there's a summary sheet, assumptions, scenarios, and model. Let's take a look at them. There's the cover, and after the cover there is a summary sheet. This is the executive summary and it's so critical to have an executive summary in every model. And that's because the very first thing a client wants to know is what's the answer? So in this model we have an executive summary, we have three tables. I'm showing the values using a base case, I'm also showing the values using a best case and under the worst case, so I'm showing my client three different options. In this particular summary page, we have revenue for five years, we have the EBITDA with margins and growth rates, we have net income. You can build whatever you want on the summary page. You can use charts and graphs, tables, but it has to clearly explain to the reader what is the answer. So that is the executive summary . The next thing is the assumptions. Every good model should have a sheet with assumptions and we will talk about it in just a moment, but you can see all of the inputs and the assumptions have been entered here right up front in the model, we're gonna talk about assumption management. In this video there is a scenario page. The scenario page is next and this includes the base case, the best case, and worst case, I will talk about this toward the end of the video. And finally in the model you can see here that there's a sheet called model. And on this sheet we have all of the calculations and the financial statements. So I'm gonna scroll down slowly so you can see it. There is a revenue page and as I roll down, we have, after the revenues, we have all of the company's costs, we have the costs of production for this company. And as I roll down, I want you to see the flow and the orientation underneath it is the income statement here. Here's the forecast for the comp five-year forecast of the income statement. Underneath that is a five-year forecast for the cashflow statement and then the balance sheet. And after the balance sheet, there are a bunch of other calculations and schedules to calculate many of the numbers. Now I just wanted you to see that, to understand how it is being built, I'm gonna start to get into a bunch of tips in a moment. Now before I talk further about the flow, I also, I just showed you what this model looks like as an Excel file. I also now want you to see what this model looks like on paper. I want you to see what this model looks like as if I was to print it for my colleagues or my boss as A PDF. Let's go back then into my PDF file. So here are the presentation slides, but next I wanna show you the same financial model as a paper tool. If you were to print it, print it to PDF or print it on paper, I want you to see what it should feel like. Now, first of all, as I go off to the side here, you can see this model is 15 pages long, one five. Let me go into print preview mode. Once again, you'll notice the very first thing is the cover page because the cover page sets the stage, it makes it feel like a financial presentation. Now you'll already know that. The next page is the executive summary. And on this page, once again, I have the base case, the best, the worst because the very first thing your client wants to know is what's the answer? So I'm sharing it up front. After the executive summary, I have again the assumptions . You can see here one page of assumptions and then another page. Here's page four. You can see page nbers, another page of assumptions as I go down. Again, we have the scenarios, which I will show you how to build later in this video. And then you know what's next? The revenue forecast. Here's a calculation of the company's revenues. And as I go down, all of their costs are on the next page and in the income statement and in the cash flow and in the balance sheet. But what I really want you to focus on, what I really want you to pay attention to as I go up and down is the flow and the design. Do you notice that every page has a company name and there's a title and there's also a subtitle. Every page has an equal amount of space on the left and the right, every page is page numbers. And as I go back up, again, it feels like a financial presentation. The font is the same size on every page. This feels like a docent. And that's what our goal is as we build a powerful, effective financial modeling tool. So let's go back out and go back into the notes file here. I wanna share with you some notes on optimal flow, but you've now seen it. So what I shared with you already is that in a good model, the very first thing we should see is a cover page. It sets the stage, it makes the model feel like a financial presentation. What's next? Well, you saw it, it's the executive summary. Many models do not have this, but it is so important because it sets the stage and it provides the answer. It tells your client what's the answer. And again, you can use charts, you can use graphs, whatever will help to to communicate your message right up front. After the executive summary, we want to show the client all the ps, all the assumptions , all the inputs. Many models do not do this, but this is critical because it tells the reader, how did you get there? How did you get there? Once the reader sees the answer, the next thing that they want to know is, how did you get there? What decisions did you make? So the assumptions are next, the after that. The fourth thing we wanna show is the scenarios. This is this page that allows us to manage uncertainty, upside, downside cases. And then the last major component of every good model is what I like to call the engine. The engine includes all the schedules, all the calculations and the financial statements. So now we're getting a sense for the flow. All good models can accommodate this flow because this helps to tell the story. So the very first takeaway then in this , video was to talk about model flow. I've already looked at a lot. We've talked about the purpose of models, why models are used and how to optimally design a model. The next thing I want to get into then is, is the second tip here, which is how to manage the assumptions. We'll talk about that and then we'll build a scenarios page together. So let's go back down to my notes file. I just finished telling you that in a good financial model, it's very important to keep all of the assumptions upfront. And I mentioned it, it's important to do this because people want to know the inputs before they look at the calculations. As soon as you show somebody the answer, they want to know how did you get there? Now I'm gonna show you a very powerful Excel skills. So hang on for this. , here's a great tip that a lot of modelers, like we want to keep all the assumptions upfront and there's a great tool to play with that allows you to keep all your assumptions upfront, but to still see what's happening in your calculations. Lemme go back to the model. I'm gonna go back to the model and I'm gonna show you on the income statement. Let's take a look at the income statement. On my income statement, I have five years of EBITDA and I have five years of net income. You can see that. I'll just put them on the screen here. Five years of EBITDA, five years of net income. Okay, fine. Well here's a problem that some people get into. If I go back to the assumption page, what if you found out that you had to change the raw materials? So the raw materials were no longer going to be 2 26 per unit. They were going to be two 50. Look what I'm doing. I am changing the raw material cost to be two 50 and I press enter. Well that's great, but what happened? What did that do to my answer? I don't know. What did that do to net income? What did that do to EBITDA? I don't know. So I have to go back, I have to go back back to the model sheet and now I have to take a look at again, what did that do to EBITDA? What did that do to net income? Okay, the problem is every time I make a change, what if I make a two 40? Every time I make a change, I have to bounce around, I have to make the change here and then go back to the model to see what happened. And a lot of people don't like doing that. A lot of people will say that they wish they could change their assptions and immediately see what that is doing to the answers. Well, the good news is there's a really great skill in Excel to help us do that. And that skill is called Excel's Watch Window. It's a fantastic tool. It lives under the formulas men you and then it's called the watch window. And with the keyboard it's alt MW. Let's go back to the model and pull it up together. So I'm gonna go to the menus, I'm gonna pop up the menus and I'm gonna go to here. I'm gonna press the alt key on my keyboard, which open puts letters on the screen and I wanna go to the formulas menu. So it's here. I'm gonna press the letter M to access formulas. And then the one I want is the watch window. And you can see this little W. So alt MW opens it up and when I pop it open, you can see I've got a little box here that says the watch window. But what I'm going to do is click add a watch and I've, I click add watch. It says, what do you want to watch? Well, what I'm gonna do is go to the model sheet and I'm going to say, let's watch the first three years of ebitda. I've selected the first three years of EBITDA in my watch box and click add. And when I do that, look at that Presto, I have the first three years of EBITDA in my watch window along with the values. Let me do another one. I'm gonna click add watch again and I'm gonna go back to the model sheet and I will now click on three years of net income, three years of net income, and I will click add again. Now look what's happening. This is very powerful because I can collect answers from all over the model. I can select values from the valuation sheet, from ratios, pages, anywhere, and they will all live inside this little box. Now look at the beauty of this. If I was to go back to my raw materials and type in two 50, keep your eyes. Now when I press enter, keep your eyes on this box because when I press enter, you'll notice every number changed. So now every time I make a change to my inputs and assumptions, every single answer that I've pulled in the watch window will adjust and adapt so I don't have to jump around. It's a very, very powerful tool. You can look at all sorts of things throughout the model to see how they are evolving and adapting. Now, some people say to me, Ian, that's great, but I don't remember which of the values are your EBITDAs and which of the net incomes. Okay, no problem. You can see here there's one more coln that says name. Well, what I can do is I can go to back to my financial statements. I'm gonna click on the EBITDA in the first year, I'm gonna click on just the first year of ebitda, and I'm gonna go into the name box. Do you see I clicked in the top left corner here into the name box and I am going to type in the word EBITDA and press enter. And when I click EBITDA and press enter, look what happens. Now I've named this cell. This cell is now no longer K 92, it's now named ebitda, and that name has appeared here. Let's rename this cell, this cell at the bottom. I'm gonna rename this. It was K one oh four, I'm gonna call it net income. And when I press enter presto, it says net income. Well, now the beauty is you can play with any assption upfront and you immediately know what's happening deep in your model. One more time, let's go back to 2 26. And when I press enter, you will see every single nber change. So it's a very powerful tool and I would highly encourage you to use it on all your Excel spreadsheets. I'm gonna close it out and go back to our notes. So a very powerful tool is this watch window because it allows you to achieve optimal flow by having an executive smary and the assptions upfront. But with the watch window, you know what's happening to your answers. As soon as you make a change to the assptions, I hope you use it and find it helpful. The next skill I wanna show you is I wanna talk about how to manage uncertainty in a model. How do you manage uncertainty? Well, we talked about the fact that we need to build a scenario page and now let's talk about some of the ideas around that. In every financial model that you ever build in your life, it is critical to have a scenarios page because the scenarios page allows you to manage variability. It allows you to manage the upside and the downside cases be. And that's because in in models, in all financial models, some of the assptions are easy to forecast. For instance, the tax rate, if you know that the tax rate is 25%, well it'll always be 25%. It doesn't just change by itself. It only changes if the government changes tax rates, and that doesn't happen very often. So , a variable like an assption like a tax rate is easy to forecast. The depreciation rate, what rate do they use to depreciate their assets? That's also pretty straightforward. So some assumptions are easy to forecast, but other assptions are difficult to forecast. Things like cost inflation, interest rates, exchange rates, commodity prices, these are sort of economic variables. It's very difficult to forecast cost inflation, interest exchange rates, commodity prices. So these hard variables, we have a name for them. Any of the hard assptions, we call those key drivers. The key drivers are the assptions that are hard to forecast and hard to control from one day to the next. So because we have some key drivers in the model, we want to run multiple cases in our model, we wanna have an upside case, a downside case, or a base best and worst case so that we can look at some of these variables under strong case, under a weak case, it's very powerful and very important to do this. Now I will share with you that there are three steps to build a scenario page. In any model, there are three steps to build a very powerful scenario page. Let's go and build it together and then I will come back and show you what these steps were. So let's go take a look. Now I'm back in the model. I am back in the model. Let's go to the scenario page and take a look at this page together. First of all, you can see here that I have three variables. I have a variable for cost inflation, I have a variable for sales prices and sales voles. These are my key drivers. These variables are difficult to forecast. So I have a base case, I have a best case, and I have a worst case. The very first thing you have to do is you have to notice that for every one of these variables, that is a large rectangle. You notice everyone has a large rectangle with three rows and it's right here. Here's my large rectangle, here is my large rectangle with a base, with a best and with the worst. And so that's gonna be step one. Now these variables are blue and in a financial model, just like on my assption page, the blue values are inputs In a good financial model, we always like to make our input sales blue because that way people know that you typed it in. There was an input variable. So lemme put a note here off to the side that simply says, these are inputs or assptions and how do you, how do you decide what they should be? Do research, do some research, talk to your team, talk to your team, talk to your boss, decide what you want to use for the base, the best of the worst, and literally type them in as blue input variables right here. So I just said step one is to build these large rectangles. These are the inputs and that's why, and that's why they are blue simple. That is step nber one is to build the large rectangle that has all of your input variables. The second step, I'm gonna move this little switch, I'll come back to it later. The second step in a is to build that is step two. Step two is to have a cell. This is just a cell that has a switch. And you can see in my switch it is just the nber one, it's blue, it's just the nber one that was typed into the cell. And this is going to control which case we are running. Which version are we running in the model right now? So right now my scenario switch is set to one. So the model is running the base case, the model is running the top option, the base case option. Now how do you know that? Because you can see the base case values are reappearing in this little skinny row. And what I want to show you now is that step three is the live case. This skinny row is the live case. This is the row that's actually running in the model and you notice it's black. I'll put a note here. This is black, this is the case running in the model. Let me make that red so you can see it's a no. This is the case that we are actually using to run the model right now in the skinny row. So again, step one, you, you enter all your assptions in the large rectangle, then you go to a blank cell and type in a nber. That will be your switch. And then we have to create a skinny row that displays which case we're running. Now obviously if you put a two in the switch, the skinny row is now pulling up the second case. It is the best case that is going into the live row here. And finally, if you put a three into your switch, it is the worst case that is now reappearing in the top row in this skinny row. So now the big question is how do we, how do we create this skinny row? What formula should we use? That's a formula that is not an input cell. So which formula or function should we use? Well, many people use an if statement, if statement, but the problem is they get long, they get very long and hard to understand. So you could use an if statement in this row, but it's not ideal. It's not optimal. , it's not ideal. If I was going to, it would simply say if, right? I would use, if I would, my if statement would say, if this switch is equal to a one, then we grab the base case option. Well, otherwise, now I need another. If I'm gonna say otherwise, if the switch is equal to a two, then I will grab the best case. And if it's not one or two, I will get the worst. This would work, this would work. But this, I am building what's called a nested if statement, an if within an if they get very long and hard to understand. So I don't recommend it. My recommendation is to use other, another function. Now the one I like to use is excel's choose function, which I will show you in this video, the choose function. But there are lots of other ways you can use it. There are probably a dozen, there are probably a dozen formulas you could use to build this skinny row. Some people like using an index function in Excel, people like to use the offset, they like to use the X lookup. They look to use the V lookup. And there are a whole bunch of choices. And in a later video in this series, I will try to show you a few of the other ones. But in this video I wanna show you the choose function and why I like it because it is so simple. Watch this. To build a choose I am literally gonna type, simply type the word choose. And then all you do next is you click on the switch. The first thing it wants to know is, where is your switch? I'm going to press the F four key on my keyboard to lock it in. You notice when I pressed F four, it added the dollar sign. So now this cell reference is locked. So the first thing the choose function wants to know is where is the switch? And then all it wants to know is, where are your choices? So I'm gonna click the base case option and then I will click the best case, comma, and then I will click the worst case option. That's it. And the reason I like this feature a lot is because it is so simple, you cannot screw it up and every client who sees it will understand it. So how does it work? It looks at the switch, and if that switch is a one, it will pull out the next item within your list. If the switch has a two, it pulls out the second one in the order that you type them in. So the A one will gen will pull out G 14, A two, G 15, and if there's a three in the switch, it will retrieve G 16. The order depends on the order that you type them into the formula. It's very, very powerful and very simple. People like it because it is so simple. Now, you might be wondering in a choose, you are not allowed to give it a range. You cannot provide a range like this. That won't work. That's actually okay. There are other functions that some of these other functions allow you to use ranges. The choose function requires that every cell be clicked on separately. But that's okay. It keeps it very clean and very, very simple. So it's a very powerful tool. I hope you all try it because it's a very effective way to run scenarios and to run upside and downside cases. Now, , there is one last thing that I want to show you. Let's actually go back to our notes file. Let's just put these in the notes. So I told you that there are three steps to build a scenario page in a model. Nber one, you now know, enter all the assptions for the different cases. That's in your large rectangle. Step one. Step two is enter the value one in a cell, that will be your switch. And then finally, use a formula to create the live case in the skinny row above. And I showed you how to use the choose function and I gave you a bunch of other options you could pick as well. But there's one more issue and that's this. I don't want the switch value to stay exposed. I don't ever want, I never want my boss or my client to see this. Nber one, why not? Well, it's because it doesn't mean anything. It doesn't mean anything for somebody to see this. Nber one, someone might say, why is there a one in that cell? So what I want to do is I want to cover it with this little dropdown mechanism that you saw earlier is very powerful to build a control mechanism. When I open it up, you'll see the words base best and worst. But let me show you how to build it. Let me show you how to build this beautiful combo box, this dropdown menu to control your switch. The first thing you need is your developer menu. You have to go to the developer menu on in your ribbon. So you have all your menus in Excel and there's one called developer. However many of you might not be seeing it, you might not see the word developer on your screen by default, this menu is off. So if you do not see the word developer right here, right now, you need to turn it on. How do you do that? To turn it on, you go to the file menu. So when again, you go file in the top left corner file and then down to options, file options. And from there you're going to go customize the ribbon file option, customize the ribbon, and you must put a check mark in the developer option. Yours might look like this, yours might have the developer off. So you can put a check mark in and turn it on, and then it will always be there. Now what do you do? Now you click on the word developer and you go to the button that says Insert developer insert. And I am gonna go to the form controls. I'm gonna go to the form controls and click on the second button, the one that says combo box right here. I'm gonna click on the combo box. And when I do that, look what happens. It turned my cursor into a little plus sign. I'm going to take my cursor and simply draw. I simply drew a giant combo box on the screen. That's all I did. Now all I'm going to do is right click with my mouse. I will right click and go to the bottom where it says, format this control. So again, right click and go to format control. It wants to know two things. It wants to know the input range and it wants to know the cell link input range. Cell link input range just means this input range is the range of words, the range of words that you want to see inside the dropdown. So I'm simply gonna select the range of words, where's the cell link means, where's the actual switch? Where's the neric switch? So one more time. The input range means where's the range of the words and the cell link means where's the switch? That's it. As soon as you, I'm gonna delete this one. As soon as you do that, as soon as you do that and then click off to the side. Now you can open up your dropdown and those three words will be appearing. It's a very powerful tool because when I click the first option base, it actually enters a nber one into the switch cell. Watch this. If I click on the word best, the second option, it now enters the nber two into the switch, and this choose function will be pulling the second option. And finally, if I click on the third option, worst case, it now enters a nber three in the cell, let's go back to base and it puts a one. The only thing this little combo box does is it enters the nber one, two, or three into this cell. But it's very effective because it means your boss or your client, they do not have to type in a one, two, or three. They can play with this switch. Finally, finally, once you've built it, you can right click and activate it and simply move it. You can move it, you can resize it and drop it on top. And now you've got this very beautiful, elegant user control that allows you to change and play with your scenarios. So once again, I highly encourage you to include this in almost every Excel tool you build, but certainly in every model. Let's go back then to some notes. I just finished showing you that when you're done building the scenarios, you want to build a dropdown box to control it. And I told you already that you need to go to the developer tab to build the dropdown. Go to the developer tab. If you don't see it, you have to turn it on because by default it is not on. It is not on by default. How do you turn it on? You go to file, you go to options, and you go to customize the ribbon. Again, a very, very powerful tool. So now we are here. We are at the point where we are, I have shown you now how to, , we've talked about optimal model flow. We have talked about managing the assptions, and I showed you how to use Excel's watch window to play with your assptions and see what that does deepen your model. And I showed you how to build a very powerful scenarios page. The last two skills that I want to show you right now relate to what I call repeat and link and build it and link it. These are two extremely powerful ideas, very, very powerful concepts in a model. Let's look at them here very carefully so that you understand what these concepts mean. Because these two concepts relate to how you actually build up the file, how you actually build up a good model so that it is best in class. And as I just said, there are two powerful critical ideas. The first one says repeat and link. So what does repeat and link mean? Well, it's a fairly simple concept and a simple idea. What does it actually mean? It means that whenever you need to create a formula in a spreadsheet, you should first repeat the values that will go into that formula. So one more time. Whenever you have to build a formula in Excel first, repeat all the values that will go into that formula. I never want to see big giant long formulas in a model. Let's take a look here, let's go into the model. Let's go to the model sheet, and I'm gonna go all the way to the top to the revenue schedule. Now you can see on the revenue schedule, I have three sections. I have a section here for the pricing. This is the sales price. I have a section for sales vole, and then we have the actual revenue at the bottom, sales price, sales vole, and then the actual revenue. So very simply, what will the gross revenue be? Well, that's very simple. The gross revenue is simply gonna be a function of price times vole, very easy price times vole, and then we divided it by a thousand because we wanted the nbers to be in millions of dollars. Okay, no problem. So that's a very simple formula. How about the pricing section? How about the prices? What about the the values in the pricing section? Well, if you take a look at, this company has a sales price for their manufactured product, but they have a gross selling price and then they have a net price. This is quite common. Many, many companies have a gross price and then a net price. And when they do, there's always something in the middle. There is something that reduces the gross price to get down to the net price. In our case, it's freight and warehousing because our company Henderson pays for the shipping and the warehousing costs. Okay? So technically we sell this, we're gonna sell the product for $800 in 2025, but once we sell a product for 800, we are then responsible for paying the freight and warehousing. Okay? So that's a cost that we have to incur. That's just how this industry works. So I'm gonna show the gross price, I have the freight, and then look at the net price. The net price is very simply the gross minus the freight. Very easy. But where is the gross price coming from? Well, the gross sales price is just a link. It's just a link back to the scenario page scenarios G 23. Let's go take a look at it. It is simply linking to the scenarios on the scenario page for the sales price. So you'll notice what I did is I repeated it, I repeated the gross sales price and then I also repeated the inflation. You'll notice right here, the cost inflation has also been repeated. It's also one of our key drivers on the scenario page. So I repeated the gross sales price, I repeated the inflation. Now watch now to calculate the freight and warehousing every year. It's very simple. It's just the prior year multiplied by one plus inflation. Look how simple that is. Look how simple and easy this formula is. And then finally, because I repeated the gross and the inflation and then I calculated the freight and warehousing charge right here, my net price is just the gross price minus the freight. It's so easy, it's so simple. Anyone who looks at this will quickly and easily be able to understand it. If you look through this financial model, you'll notice that every single formula is just a link. I'm either repeating or repeating or building a very simple calculation. Again, it's a simple calculation all the way down. Anyone who reviews this will easily be able to understand exactly what's happening. And that is the idea that creates tremendous confidence. Let me show you one last tip here on this concept. I am sure many, many people have seen this. I'm sure many people have seen huge formulas that look like this. All right, have you ever seen a formula that goes into a model and grabs a nber? I'm gonna grab a nber on one sheet. Doesn't matter what it is. I'm taking a a link from a nber on a one sheet. I'm going to divide by a nber on a different sheet. I'm going to multiply it by a nber on a third sheet here, and then I'm going to subtract, , I'm gonna subtract a nber here. Doesn't matter. What do I get? It doesn't matter. Look what I did. I built a long difficult formula. I took a nber on one sheet, I divided by a nber on a different sheet. I multiply by a nber on a third sheet and I subtracted a nber on a fourth sheet. People build models and formulas like this all the time. This is a disaster. We never want to do this. I will say, , never build formulas like this. Watch the problem. The problem is it's very, very difficult to follow. It's very difficult to understand and it's easy to make mistakes. So what should we do instead? Well, I said repeat and link. What should I have done? I should have inserted a bunch of rows. I'm gonna insert a bunch of rows and I am going to repeat value. I'm gonna repeat one value and a second one and a third one and a fourth one. So the first value is coming from this sheet here, the smary page. I'm just gonna repeat that one all by itself. Whatever it was didn't matter. I'm gonna, it's a, it doesn't make sense, the formula, but I'm gonna show you how I would deconstruct it. I'm gonna take the value on the first sheet. I'm going to take the value on the second sheet, whatever that was, and I'm gonna repeat that one. I will take the value on the third sheet here. You'll see what a big difference this makes and I will repeat the third one. And then finally, the value on the model sheet is already there. But now look what I can do to make my formula so simple. Lead this row. Instead of having a big crazy formula that links to four different sheets, I'm going to say, let's take this value divide by that value multiplied by this one, and then subtract the fourth one. And I will get, I should have gotten the same answer here. I wanted to take the, this one divided by the second one. Oh, I didn't change it. I put the wrong one in here. This scenario's value is the scenario's value that was supposed to go here and it was this one here. So now my formula can take the first value, divide by the second one, multiply by the third one, and subtract the fourth. And you'll notice I get exactly the same answer. But now look how simple it is. Look how easy. Now we can understand. It's easy to follow this formula all the way down. So it's a very powerful idea in models is repeat and link, repeat everything, sell value, need, and then build formulas that will make your models much easier to follow and understand. And then the very, very last concept is one that says build it and then link it, build it, and then link it. What that means is never build big formulas on the financial statements, rather build schedules, build schedules, and link the schedule into the financial statements. So let me show you what I'm talking about there. Let's go back to the model. I'm going to. I'm going to delete these extra rows that I had built a moment ago. So you notice I have a revenue schedule to calculate the company's revenues. It's very simple. And then I had a cost schedule to calculate their costs. And then there's an income statement. But the message here is, I just finished saying that the key idea is build it and link it. What that means is I never ever wanna see a huge formula on my income statement when I click on the revenue line, look what it is. It's just a simple link. The revenue on the income statement is a simple link. And where is it coming from? From above K 26. It's linking to the bottom of the revenue schedule. We built a schedule and then link it in, build it, and then link it. How about the cost? How about the cost of goods sold? It's just linking to the bottom of the cost schedule. Once again, you build a schedule, you link it in. Now obviously my totals look, my totals can be calculations. EBITDA can obviously be a simple subtraction. There's no problem there. My net income can be a simple subtraction, but the line items themselves are just links. It's a link, a link. Every single cell reference is just a link. So where's the depreciation coming from? It's just a link. It's linking in the same coln, in the same sheet to sell K 2 22. Where is that coming? Two 11. It's linking to K two 11. Where is that coming from? As I roll down here, it's linking two, a depreciation schedule. This is how you build models. You build a schedule and then you link the total into the financial statements, build it, and then link it, build it, and then link it. No look, no giant formulas on my income statement. Every single line on the income statement is just a simple link and then a smation or a subtractions, but no huge formulas. And look at this, even the cashflow statement is exactly the same. The cashflow statement is just a link, a link, a link, a link, and then a total. And then here's a link back to the assption page. But the point is, every single cell is just a link, a link, a link, a link, a link, and then a total all the way down. Even the balance sheet, even the balance sheet is just a link, a link, , for cash. In fact, where's the balance sheet? Where does cash come from? Right on top of me because the cash on the balance sheet just links to the end of the cashflow statement. It's very simple. Just like on the cashflow statement, the net income is a link. Cashflow statement starts with net income, and that is linked to the income statement above me. When you keep your model aligned vertically and well designed, it makes it so easy to build the financial statements. Look at my balance sheet. The balance sheet is the link and another link all the way down. Every single cell reference is a link or a s total, right? A s total is nothing fancy on the financial statements. You see here, these three lines are just link, link, link to different places in the model. So the magic, the key is build powerful schedules, build a schedule, and then link the schedules into the financial statements. So those are the five tips that I wanted to show you that I wanted to share with you. I'm gonna go back to the top and smarize on this. , we covered a lot just in this one hour, but I wanted you to see five critical ideas. The first one is that flow matters. The order matters. , the way you present the model matters. So think about optimal flow. Think about the presentation of the data nber one. Second of all, keep your assptions upfront. It's much easier to read the model and make changes if the assptions are upfront. However, if you want to see what's happening to the outputs, use the watch window so that you can keep the assptions upfront, but see what's happening deep, deep inside your model. I showed you how to build a scenario page, how to build your, take the key drivers the most difficult assptions and set them up with a base case, a best case and a worst case. And then have a skinny row using a formula like a choose function to pull up the live case that's running, showed you how to build a switch and a dropdown. It's a beautiful, elegant way to manage uncertainty and volatility in your model. And then finally, finally, I talked about two key ideas here. Repeat and link and build and link it. Repeat and link again means repeat every value that you need in a formula. Repeat those values and then make your get your formula to pick up the values that are directly on top of you. Yes, it makes the model a little bit longer, but you have much more elegant, much cleaner formulas within the model, and it makes it much easier to understand and creates confidence. Finally, the last tip was build it and link it. What that meant was build a schedule and link it in, build a schedule and link it in. So those were the five critical modeling best practices that I wanted you to see here in , in this video. I'm gonna stop my screen now so that we can wrap up this particular video. , I started by saying that financial modeling is one of the most important skills required of finance and accounting professionals all over the world. , and the good news is that modeling is the same wherever you go all over the world, because historical financial statements are very similar. Most accounting standards are very similar wherever you go. And so when you take a company's historical financial statements, all we're trying to do is project it into the future. My promise to you is that if you take the ideas that I shared with you and the tips in this video, you will immediately be able to build powerful best in class financial models. I hope you enjoyed that video. I hope you found it helpful, and I look forward to seeing you in the next one on skills to check and review a financial model. I'll see you then.