Transcript for:
Best Practices for Financial Modeling

[Music] [Music] [Music] [Laughter] [Music] [Music] CFA Society Nigeria and the financial modeling Institute so I'm David Brown I'll be hosting a co-hosting and I would like everybody that is currently called Gloria you need to do something because there was just a small error in the way we set it up and that's why we had this delay so sorry for the delay everybody and I would like you to click on the three dots next to Gloria the name Gloria and I want you to change the name to your name if you could do that for us that would be great so click on the three dots and just change the name from Gloria to your name can we quickly do that I can see Mohammed thank you very much for doing that I can see your change the name so everybody let's do that please you click on the three dots and then change the name to your name sorry about the mix-up great great let's change the names three dots and then change the name to your name great let's do that let's do that excellent excellent excellent so while you're doing that I'll quickly introduce webinar so this is financial modeling best practices organized by CFA Society Nigeria in collaboration with the financial modeling institutes financial modeling is a discipline a good model needs to tell a story and create confidence for the users and decision-makers unfortunately many financial models are a mess financial modelers are often self-taught and left to figure out things for themselves often wondering if there is a better way in this webinar Ian schooners sorry Ian I'm not pronouncing your name right sorry again we'll review a better way better way to do this now during this session it will present and discuss some critical tools and skills refer refer to building a best-in-class financial model that inspires confidence and leads to optimal decision-making specific topics will conclude the discipline of financial modeling itself top 10 modeling best practice key modeling tips skills to check a financial model great modeling technical skills a little bit about Ian ian is a CFA charterholders as an executive director of financial modeling institute ian is a principal and founder of the Marquis Group a leading provider of financial modeling training to professionals in the financial community ian has built the Marquis group into a leading provider of financial modeling training consulting and accreditations ian is also the executive director of the financial modelling Institute in this role he works closely with the entire financial modelling Institute team on the creation and execution of the f-m ice strategy over the years ian has taught thousands of business professionals and university students in Canada and the United States and also the United Kingdom Australia and Mexico ian is passionate about teaching and brings a hands-on interactive approach to every course Ian teaches at Queen's University and is a past recipient of the instructor of the Year award in the master of Finance program at the Smith School of Business prior to establishing the Marquis group Ian spent a number of years in the investment banking department at Citigroup and BB Capital Markets Ian completed a bachelor's of Commerce honours degree with academic distinction and has also attained the CFA designation so let's welcome Ian who is going to really share his wonderful wealth of knowledge with us I can't wait to hear what one has to say over there yeah can you hear me okay David yes I can awesome and everyone hear me and Julian you're there well thank you all really very very excited to to be here today and I'm gonna just start with a very quick intro of the seminar and we're gonna jump right into it we're gonna move very very quickly here I've got lots of stuff to show you and and we're gonna get in so first of all a big thank you to cf8 nigeria sea bass Society Nigeria and Gloria and Frank for bringing us in and my friend David Brown David and I have had a chance to meet many times or last year so it's great to see you again David thank you for for hosting and three a very warm introduction I actually kind of liked it everybody's name was Gloria I've never I've never seen a thousand people named Gloria before but if you want to use your real name that is also that is great you know I was amazed at how many people they told Gloria and Frank told me yesterday how many people have signed up for this over a thousand as David said I've been teaching financial modeling for almost 20 years and I've never talked to over a thousand people before at one time so I asked them are they sure that everyone knows this is financial modeling and not fashion modeling but um but in fact they told me that you didn't know its financial modeling and and I'm excited to have all of you here with me today in this in this session and I'm actually not surprised because financial modeling has become the most important skill and discipline in the world of finance and accounting and business is so critical if you're gonna work in Business and Finance to have a good sense of financial modelling and as a result I'm really thrilled that you're all here today with me Julian you're here can I hear you I can't see you Julian so you just let me know that you can hear me okay I'm gonna might need to key in on you one of my colleagues Julian is here with me as well from the of mine that's a candy and you sound great hi everyone garbage Julian is with me from the fmi fantastic so let me just quickly tell you a little bit history in the background so as David mentioned I started a training business many years ago called the Marquis group and we mostly run training in in in financial modeling in in North America but I want to say hello to some of my friends in Pakistan I was out with CFA Society in Pakistan last year running some training as well and and that's what we do on that side but one thing we noticed many years ago is that a lot of people would take a financial modeling course and then they would put on their CV or their resume that they took a course from some firm maybe from David's firm or from our firm and everyone would write down that they attended a course the problem is it's not very helpful to say you attended a course that doesn't tell an employer much about your skills and we realized that there was a real need in the world to establish a global standard and a very rigorous challenging test for financial modelling so a number of us around the world in the world in financial modelling established around four years ago the financial modeling Institute it's an independent body with an independent team and what Andy and would call it the fmi and the only thing you have to my does is it creates and administers very rigorous financial modeling exams and when people pass the exam they truly can indicate to their team's their colleagues that they have excellent financial modeling skills so there is a website you can see the the link at the bottom here if the f-m Institute comm I'm not going to any more time talking about it I'm gonna show you the website here quickly I assume you can see that right Julian the you can just give me the thumbs up or say yes but that works okay yeah so feel free to check that out you can see what we do and we'd be more than happy to chat with you after about the level of testing to truly prove people's financial modeling skills so what I want to do today and I'm going to move very quickly we have well we've lost a little bit but I'm gonna try and go in the next 70 minutes or so 75 minutes and we put together this presentation on financial modeling best practices now a couple of things as I said I'm gonna move quickly if you have questions maybe put your questions into the chat indicate your question in the chat my colleague Julian will interrupt me at times or I'll ask him if there's any questions and I will try to address some of them but there's such a large number of you that if you have questions what we might do is collect all of them and then put together a summary note and send out that note to everybody after the session is over the other thing I will mention is that the session is being recorded so if you if you if I'm going a little bit quickly or if you can't watch all of it please come back and watch the video again so that you can see it again and and think about all the things I'm talking about the last thing is I will be showing you some slides some presentation slides and I will be showing you an a model in Excel both of those tools are going to be sent to you after the webinar so you're gonna receive the slides and you're gonna receive the financial model that you can use as well so you're gonna get a bunch of resources after this as well so let's jump right in another for a second please I'm dropping you down so anyone that can connect you can watch us on Facebook thanks I think is saying that it's a full house so people can come in is 300 already so anybody that can't come in he could watch a live on Facebook basically I'm wondering if they know the link for the Facebook but that's that's fine I think thank you David I think it's the CFA Society Nigeria Facebook page so if you're on and your friends can't get on for some reason you can let them know that they can watch it on I feel like feel like an ass fresh laughs lead or something there's so many people trying to get in it's excellent but you can watch it on Facebook or you can just remember the video will be available afterwards as well so let's jump right in here this is the Welcome slide and and here's some of my contact information you're welcome to email me or be in touch with us if you have questions after don't be shy guys modeling is a passion of mine you're welcome and I know of David's as well but you're absolutely welcome to email me afterwards if you have questions if you wanna if you want to chat um so this was the pup age 3 was just the agenda that David mentioned and then here's some more information about the fmi you can read up about the fmi on your own time I'm going to start with some general best practices and then I have a list of top ten modeling tips and we're gonna review those top ten tips throughout this presentation so let's start here with with this first page page number seven and what it says is that financial modeling is a discipline and I truly believe that in in the next number of years people are going to be speaking about financial modeling as a discipline almost in the same way that people talk about accounting or modeling sorry the way people talk about accounting or finance people will refer to modeling modeling is its own discipline modeling is a very multidisciplinary skill set and as David mentioned part of the reason why models often don't work very well is that no one's been taught historically nobody was taught formally how to use financial models let me put a couple of notes here on the screen and hopefully you can see this now Julian I'm into Excel is that right yeah what Jacques down here is there so simple question that I always ask in a class is what is a financial model if anybody ever said to you what is if an answer model and you had to answer that question people always stumble and they wonder how to answer it well I would say very very simply that it's simply a forecast all we're trying to do in a financial model is build a forecast for a company we're trying to forecast the next usually the next five or ten years into the future so then the question is why why do we forecast well I will tell you there right now that models have become the most important decision-making tool in finance in all of finance the most important decision-making tool is our financier models all sorts of decisions that people make in business are based on financial models so what types of decisions obviously while we're making decisions about valuation we're making decisions about investments we're making decisions about mergers we're making decisions about operations companies use their forecasting tools to make operational decisions around staffing and hiring people use their models to make credit decisions a lot of credit bankers want to know if a company can handle more debt more debt capacity we use models to determine risk and think about risk so my point is this we use financial models to make a wide range of decisions and the big problem is that as David mentioned earlier and we say this all the time most financial models are a mess and they don't work very well people struggle like crazy with their financial models so that's part of what we do at the fmi is when we when people write exams and we grade them we try to make sure that their exams are beautiful and organized and clean because what we always tell people is that a model needs to tell a story it has to communicate a story about the company or the situation so that you can make an effective decision now it should be very simple all we need to do in a model is take the company's historical financials imagine you're gonna take last year's actual financial statements and forecast them into the future make a projection so why is it so difficult why is it a mess well there's a lot of elements I'm going to go back to my slides for one second here the reason is because to build a strong model you have to have a lot of different skills you need some good Excel skills you need some good skills in design I will tell you the number one reason why financial models fail is because of poor design they're not well designed and organized so I will try and focus over the next hour on some critical ideas regarding design in a model you have to understand accounting now I'm not an accountant I'm guessing some of you might be accountants you don't have to be an accountant but you need to understand the way all the numbers talk to each other to build an integrated set of financial statements including things like subsidiary accounting or tax accounting and you need to know how all the lines talk to each other on the company's financials you have to understand finance and our job is to pull the whole thing together into one clean organized tool in Excel our model so the last thing I will tell you here before we move on to our list is this one of the messages I always tell people is this a good financial model needs to work two different ways so if you guys have ever done any forecasting if you guys have never built a model before and projected a company how do you know if your model is working well well there are two criteria and everything I'm going to talk about today has to come back to these two criteria how do you know if the model is working well number one a good model needs to work electronically what that means is every formula needs to make sense if I built a financial model and I emailed it to you guys you should be able to open up the model on your computer screen you should be able to click on any single cell in the spreadsheet and understand exactly what that cell is telling you it should be clear you should understand every single formula easily and that doesn't happen all the time has this ever happened can you guys think of this has ever happened to you have you ever opened up a spreadsheet in Excel clicked on a formula and the formula was so long that it wrapped around and around and around you've seen that before haven't you we've seen that we see that all the time that's not optimal that should never happen that way because it's not clear people can't follow and understand so a good model has to work well electronically but I would also say to you that a good model needs to work well on paper what that means is you should be able to take your model print it and staple it and feel like you've just given somebody a presentation because the model has to create confidence and as I said it has to tell a story so it has to be clear someone has to be able to read it like they're reading a presentation I'm gonna put a note here it needs to feel like a presentation guys sometimes people will look at our models and they'll say to me did did you build that in PowerPoint no of course not it was built in Excel but we need to make people feel like it's a clean organized presentation and if you can do that you will have a very strong effective model what I'm gonna do is quickly show you them I'm gonna show you the model that we are going to be looking at today but I'm gonna show you a pdf version of the model before we look at the Excel version can we can see this hopefully Julianne you got the PDF model here yep so guys this a financial model that you're going to get access to it's called that the Henderson manufacturing model and I've opened it up as a PDF as a PowerPoint and I'm just gonna very quickly imagine you printed this out and you showed it to your client or your boss it should feel like this as I quickly flipped through it I have an executive summary page I have an assumption page and another assumption page but I could drink this I have a scenario page every single page is clean it's clear it has titles on it it has page numbers it's clear you notice we've used consistent formatting consistent number size i have a cost paid a revenue page a cost page here's my income statement but i hope you would agreed that if you printed this and handed it in someone's gonna feel confident and i can tell you that doesn't happen all the time many people struggle to make a PDF version of their model or they struggle to print it it's a huge mess there's no way to print it but you need to make people feel confident in your work and this will happen if your model can be printed or turned into a PDF so we'll talk about how we do that over the course of the next hour so let me go back to my excel um so I'm gonna go back to the the presentation slides here for a moment and let's look at a couple ideas and then I'll stop and see if there's any questions on page 8 of the slides you'll read this on your own guys it talks about the model discipline the very first note just as a model has to be a powerful communication tool make sure that people can read it and feel comfortable with it so it has to be dynamic and flexible printable I talked about this transparent if you do that you are gonna be head and shoulders you're gonna be well above what most people do in their financial models as I've said here a strong model will create credibility and inspire confidence so how do we get there we need to keep things simple we need to keep things simple to get there and so what I say keep it simple that means simple formulas simple design simple logic we need to make sure that everything is that doesn't mean simple ideas we can use more complex ideas but our techniques have to be simple so people can follow one of the key things to keep it simple is to avoid hard coding and I'm going to show you that in the model when I get into Excel never hard code never never never hard code in a spreadsheet what that means is never build a formula like this if you look at this formula here guys on my screen it says equals a 5 in the middle here equals a 5 times point O 2 5 plus 4.9 can anyone has anyone ever seen a formula like that I'm sure you have we see it all the time what does it mean if you build a formula and you put plus 4.9 does anybody know what that 4.9 means in a webinar last week somebody tried to explain it to me but of course you don't know what 4.9 means it's a hard-coded value somebody told you that there was going to be a four point nine dollar adjustment to a certain cost and you typed it right in you can never do that because nobody will understand what it means this four point nine should be in its own separate cell as an input cell and then used in a formula and one of the key ideas of model and I'm going to show you when I get to Excel is that we need to separate we need to use a different color coding for input cells versus formulas you might have seen in my model here I'm back to my model in a second right here you can see this hopefully thumbs up Julianne there you can see the model hi hopefully yeah so um anything that's an input cell in a model should be a blue number so you know if you come to a model and you see blue numbers these are inputs you can play with those you can change those where as anything that's a formula should be black that's a really critical industry standard to make sure people know what they can play with and what they can't so let's now move to a list of top ten modeling best practices I have these endless slides guys I have these in the slides on page eleven I'm going to review all ten of these over the next 50 minutes top ten modeling best pact and I'm also going to show you what these mean in the financial model but you know in order so I don't have to jump back and forth between my PowerPoint and my Excel I'm gonna go to my Excel right now and I'm gonna show you the same best practices right here Julian we can see them here the best practices if I jump back and forth okay excellent so here they are guys I hope that after the next 45 or 50 minutes if we can review these and talk about them and you watch this video again maybe a couple times this will help you understand what makes a really powerful strong model so I'll stop right now just for two seconds are there any questions that I should be addressing or answering at this moment before I get into the top ten top ten tips um there's there are a couple I know some you'll cover later in the presentation one that I just want to make sure you flag someone has mentioned that infrastructure models with P P modes get very complex and if you had any input on that yeah it's a great comment so you know people say that to me all the time they say my infrastructure models get very complex they say my they say our mining models get very complex they say well I work in oil and gas oil and gas models get very complex and very often so you're right what makes infrastructure models complex is two things there is often timing flexibility needing needed in infrastructure models so there is some complexity in infrastructure models around when things are going to happen I won't get a chance to get into that today but if people have if that person wants to you know send us a note separately there are some there are a lot of things we can do to simplify tiny flexibility in infrastructure off and looking to build a model for a new a new development sometimes a new infrastructure project and and there's often timing issues timing delays and we have to be able to change when things happen so there are ways to make that simpler the other way that infrastructure models get complex is around debt and being able to force the debt into a certain optimal capital structure we call that debts so there are elements that get more complex but I will tell you most of the time when people say their models are complex it's because their formulas are a mess most of the time people confuse detail for complexity nine times out of ten when someone says my models really complex and we look at it we'll say oh I know what you mean your model is actually not complex your models detailed you have a lot of detail and you built it in a very inefficient way with very long crazy formulas and it feels complex but actually using some of the techniques we're going to talk about there are ways to simplify every model we've ever looked at can be simplified and yes there are times when there is an area for complexity but but not everywhere we can still generate a lot of simplicity even in infrastructure models so let's take a look here at a couple things I'm gonna go through these probably two at a time the first thing I will do and I like the question I'm gonna use the question as I get through these tips the first two tips I want to take away talk about are number one and to plan and design a model and ask the right questions and what I would say is this may be the most important and I know David David preaches this in his own David's firm by the way deep ground consulting is one of the approved training providers for the financial modeling Institute one of there are twenty or thirty training firms around the world to offer training to help people learn modeling and Andy Brown consulting does a very good job of that and I know I've talked to David David shares this view that I'm gonna tell you now maybe the most important part of any modeling process has nothing to do with Excel and what I would say is it's critical to have a strong plan it is critical to have a strong plan in a financial model and what I would say is this take 30 minutes 45 minutes an hour every time you build a model first make a planning document in our team in arts when we're doing that we call this a scoping document and what that means is what do I need to do think of it like a blueprint think of it like the blueprint for an architectural project if you were ever going to build a building or a some sort of a physical product you would build a plan you would make a blueprint you would have a set of instructions and yet when it comes to a financial model people often don't do that usually I can tell guys I've built over a hundred models myself I've looked at thousands of models when I look at models they're a mess I can usually know within two minutes whether they person built a strong plan or whether they did not have a strong plan imagine if you built a house or a building guys imagine you built a big building with no plan no blueprint it would be a mess and that's what it feels like when we often look at models that there was no planning to went into it and it doesn't work very well very very quickly Here I am in the presentation slides I have to do something every time I jump back and forth but in the presentation slides just so you're aware of this when you go to page 12 and page 13 of the presentation slides you're gonna see this on your own there are some steps to follow to build a strong plan but what I would say is the purpose of planning is to ask a bunch of questions like what's the purpose of my model what decisions are we trying to make what assumptions do I need do I have enough detail what are my metrics on the outputs all these questions we need to think about upfront and what I will tell you here very quickly is that there are three components there are three components of the planning process you don't you don't have to do it exactly this way but we find this a very effective way to plan a model think of it in three major components number one I would think about determine the major issues and in corporate finance we will often say these are the key drivers and so start when you looked at your company whether it's an infrastructure asset or a mining company or whatever company it is think about what are the key drivers what does that mean well the key drivers are the most important most critical assumptions that will impact the success or the failure of the company and what we say about these variables is that they are usually very hard to forecast and hard for management to control hard to forecast and they're hard to control but they can have a huge impact on the success or failure and what I will say to you is we will treat these variables in a special way in our model so that we can play with them as an example for it well let's think of an example for an oil and gas company I know there's a lot of Natural Resources of course in in Africa of course a lot of natural resource mining companies a lot of oil and so as an example if we're thinking about an oil and gas company obviously one of the key drivers that management can't forecast with perfect precision would be the price of oil what do we think the price of oil for the next X years is that five years 10 years 20 years whatever you're forecasting what do we think the price of oil nobody knows exactly what the price of oil is going to be or the price of gold so we're going to want to have multiple options what else what else is a really critical issue that impacts oil and gas companies obviously things like exchange rates because oil like most global commodities is priced in u.s. dollars but then it gets converted back into local currency because because companies are based around the world so exchange rates are easy to forecast with accuracy so the point is there will always be issues maybe things like um cost inflation so when you're building a model you need to say which variables are the ones that I'm gonna have a very hard time forecasting those are the key drivers that we need to treat separately then what I would say is step two is make a big list of all your inputs and assumptions that you will need in the model I'll show those to you in the model and finally think about what will be on each page once the model is done what I mean by that is plan it out like you would a PowerPoint presentation I bet you a lot of you I'm sure a lot of you build presentations powerpoints and I'm sure that when you build a PowerPoint the first thing you do is you ask yourself what will be on each page what do I want on page 1 page 2 page 5 page 10 you make a table of contents and then you plan it out do the same thing for your model people don't usually do that what will be on every single page and if you do that then once again like the model I showed you a minute ago you'll be able to think about I'll what do I need in my model what should be so that I can present it to my client my boss so as I go back to my best practices I promise you if you do a good job planning your model making a strong plan and asking good questions you will already be way ahead of what most people are doing can I tell you what the role is of the modeler the modelers role the modelers role is often a lot more important than people give it credit for I would say it's the model is role the person building model is not just to create a giant calculator in Excel people sometimes think that a modeler is the person who sits in the corner building out a spreadsheet for hours and hours and hours a day that's true but that is not the sign of a truly sophisticated intelligent modeler I will tell you that in most modeling projects the time working in Excel is usually only approximately 50% when I'm building a model when you build models for clients I mean it's not exactly but when you're building a model you're not spending 99 percent of your time in your spreadsheet working in Excel maybe only 50 or 60 percent is working in Excel or 70 but a lot of your time a lot of time is spent talking to people talking to people your clients your boss understanding what needs to happen thinking about the assumptions challenging people asking them where they're getting their information from what do we want to use for oil and gas pricing what do we want to use for inflation and really understanding the business so a good modeler truly understands all aspects of the business or the project and that only happens when you take a view that your role has to be to challenge to ask good questions and to get involved and if you do that you will capture the first couple tips here right away so we haven't even talked about the Excel yet but this is critical let's get into the next two then takeaways the next couple tips that I want to show you are as follows it says keep the assumptions of front in a model and and then make them blue I already talked about this make them blue and keep them upfront let me show you the model here for a second I'm actually I'm gonna go to the model which you're gonna have access to here it is here's the actual Henderson model in Excel and as I said you will be sent this model when our presentation is over I'm going to talk about tab structure in a few minutes I'm going to get to the structure how you organize your tabs and the setup in an optimal but what you can see you guys in the model is first of all if I was to do the whole thing if I was to look at the entire model the first thing is we have a cover page and you noticed that I'm in my print preview mode but you'll notice the very first thing is a cover page most models do not have a cover page why is it nice to have a cover page well because you want to think of your model as a presentation and it just communicates quickly who built the model what date who's it for you can communicate some key information and immediately tell people that this is a presentation so in a good model we often show people the cover page and then the next thing we like to show people is an executive summary the executive summary will always be different for any model but what this does is right up front it tells people what's the answer so I will tell you that if I go back to my notes for a second here and I'll tell you why I'm coming back to this the order for a good model if you're thinking about kind of the order to present information your information in a good model usually follows this logic it's nice to have a cover page because that tells people what company what project they're looking at what version what date it's always nice to include an executive summary up front and this needs to be customized for every model so I don't know who asked the question about infrastructure but it was a very good question if I was working with you and we built an infrastructure model our executive summary would look different than if it was a brewery or if it was a mining company but you need to have an executive summary because it the executives the executive summary says it tells the clients this tells the client what's the answer that's the first thing that they always want to know what's the answer but the next thing that's really critical to include in a good model is assumptions right after that and you can see here if I go back to my model the first thing I show people is an executive summary and the next thing in is all of the assumptions all my blue inputs on things like the operating costs interest rates depreciation this is a simple model because it's a 1 it's a simple company in a simple model that we use to kind of communicate a simple business but I could show you I mean if I showed you some of our complex models there are a hundred pages long they look exactly the same they're longer but they look exactly the same as this I have my assumptions you can see as I roll down all of the assumptions are right up front on things like cost adjustments I have assumptions on on capital expenditures in the model what's the capital expenditure going to be working capital so the big question is why do we need an assumption section of grunt I will tell you many many models that I've ever looked at do not have an assumption section upfront they just do not have any assumption page upfront so let me ask you I will tell you here many models do not have an assumption section upfront so where do you think people put their assumptions if they don't put them all in front and I will tell you if they don't have an assumption page then the assumptions are scattered everywhere in the model everywhere you go in the model in hundreds of places there are inputs and there are also formulas what's the problem with having your inputs and your assumptions scattered in a hundred places in the model makes it very difficult to update the model so I will tell you here if assumptions are scattered if the assumptions are scattered in the model the number one it's very hard to make changes to the model and number two it's hard for the client to read and understand the model because I promise you this has happened every single time we've ever presented a model I promise you I promise you if you ever show somebody a model the first thing they will want to know is what's the answer they always want to know what's the answer to the model so you show them the executive summary and that tells them what's the answer but I promise you the next thing people always want to know is how did you get there how did you get the answer what decisions did you make so it's really nice to show all the assumptions up front next so people understand all the decisions around depreciation around costs around taxes around the operations whatever your decisions are on these were your decisions and then I will tell you right now that it is it is very easy to build the assumptions page in a model if you first made a strong plan if you did a good job planning your dock planning your model and making up thinking about what do you need what information do I need then it's very easy to take your planning document and turn that into a set of assumptions and that's the order we're gonna think about next so I'm gonna show you now a really really neat excel technical tip I want to show you here quickly so I've told you that it's really nice to keep the assumptions up front and to make them blue now we know why so why do we want to keep them up front so that we can so clients can read it you can present it and people read it and you can update your model very quickly because everything is together but I will tell you that sometimes when I show people of models and I talk about modeling people sometimes say to me they'll say you know Ian I understand that I get it I understand what you're saying but they'll tell me I'm gonna go in my model and I'm gonna go down for the revenues cause I'm gonna go down to my income statement okay everyone can see here my income statement and people sometimes say to me that they like to have assumptions right on the financial statements so that they can see what that might do to net income so for example they might say to me I hate it I don't believe you switched you switched windows here excellent Thank You Julie and I'm back in the model thank you very much so I'm gonna go thank you I'm back in the model thank you so I'm back in the model Thank You Julia I'm in the model and I'm on the income statement on the model tab and I will tell you that some people will say to me Ian I like to type in numbers right on the financial statement so that I can see what that's going to do to my earnings my IVA da or my net income so if I change if my cost of goods sold changes to let's say a hundred and ninety if I type that in let's say here as an input I can see right away what that does to my net income but the problem is it creates a huge mess so how do we um so what are some great skills I'm gonna show you two great skills here I don't think I went back it did I thank you first flagging that for me so there are two ways two great ways to keep assumptions upfront but also to see what that does to the outputs and I will show you a couple of neat ideas right here so there's nice so we're gonna keep our assumptions upfront but I want to know what that does to some critical outputs in the model number one I'm going to say build a simple outputs box directly on the assumptions tab this is a simple idea so if I go back in my model for a second here Julien we're good thumbs up you can see the model I hope okay yeah so for instance I have my assumption page one very easy idea that people like is as follows what if I went down to the bottom here and made what if I went down to the very bottom of my assumption tab and I built another box and this box said something like key I said something he outputs okay so I can say something like what are some of the key outputs in the model for instance maybe I want to understand on the income statement what's happening to Eva da and I want to understand what's happening to net income very simple maybe you want to add some balance sheet items or some cash flow statement items whatever it is that you want to look for it doesn't matter so I'm going to simply no and and it's a simple idea but what I'm trying to tell you is a lot of good modeling ideas are simple it doesn't require crazy technology all I would need to do now is go back in and link I can link up in my forecast here to EBIT uh and I'll copy that over I will also then link it up to net income and I'm gonna copy that over so literally all I did here guys very simple is I'm repeating so if I wanted to change for instance if I was going to change something in my assumption page above I could see immediately what that does so for instance if I change my capital expenditures this capital expenditure hit the income statement no of course not but if we increase capital expenditures we might need more debt and if we have more debt that might have more interest and a more interest might reduce net income let's test this theory what if capital expenditures was going to go up to a hundred or a hundred and fifty million dollars we're gonna invest in a huge new asset do I need more debt let's see here maybe I do maybe I don't well as I expected look my eBay da stayed exactly the same my net income dropped by four million dollars it went down by four I'll do that again so you can see my net income was thirty and it dropped to twenty six let me go back and change the capital once again to 150 eBay da stays the same net income drop that tells me that interest expense must have gone up and dropped that income my point though is I can make some changes right here to my inputs and I don't have to go anywhere else in the model I can see the outputs right here on the screen that's one little idea to allow you to see what's happening in your model the other way which is a tool that most people I'm gonna show you a tool now that most people have never seen there's a really good tip in Excel called use Excel use a really neat tool in Excel called excels watch window are you seeing my screen Julie it's just pause no not at the moment so resume share are you singing now yep thank you so watch this guys there's a really great tool in Excel that you can go to here I need to slide some zoom stuff out of my way here if you go into your Excel menu and I believe it's on the formulas tab somewhere in your excel in in mind it's on my formulas tab it's a little different in different versions of Excel but I promise you this feature is in your Excel you may have seen it before and never known what it does there's a really great tool off the side your called the watch window watch what happens when I click on this when you click the watch window you get this box can you see the box Julia does that pop-up yes and what it allows you to do is watch something deeper in your model so I'm gonna say add a watch and I'm going to make this box a little bit bigger and I'm going to say let's watch and it says I'm gonna click on the model screen and I'm gonna move over here to my net income let's say and I'm gonna select five cells I'm gonna select just my net income klm oh oh and I'm gonna click OK and look what happens now in this separate window that's popping up above my screen I have a watch window and it's showing me what's going on so maybe you don't want to include maybe you don't want to include a separate output box on your screen maybe you don't but maybe like this idea of having a watch window so what I can do with my watch window is it showing me what's happened to net income so as an example if I go back here and I change my capital expenditure once again I can see what's happening deeper in the model I can see what's happening to various items in my net income and I can watch um very carefully I could add another one I could add EBIT uh I could add revenues whatever you want and then you could turn it off and on you could close this and only open it whenever you want it so it's a very powerful way and a really great tip in Excel to allow you to show your assumptions up front would allow you to see your critical results at the same time let's anything I should stop for at this point um Julianne before we continue on yeah though that'd be awesome so there's um the the cover page in the summary I think while you were speaking to it I don't know if it was showing so we got a number of questions of of candidates wondering what should go on a cover page and what should be in a summary connection thank you thank you for asking and I appreciate that let's go back then and talk about that very quickly and then we'll move on so let's because we've gotten through the first four we'll go through the rest here quickly if I go back to my model let's see here are we it's not stay in my notes okay I'm gonna stay in my are you seeing my notes for Julian yeah so what do not they would like sort of they would like to see the the summary that couldn't didn't see it so you're on your notes when you are talking about it I don't see it in the model what's a good idea to have on a summary in a cover so let's go back in the model then what's a nice thing to include on a cover page so here's the cover and again if I was to print it I'm gonna go to my print I'm actually gonna go to my print preview mode so you can see what it would look like if it was printed what's a nice thing to include on a cover page keep it simple me include logo for the company maybe include the name of your always the name of the company the date maybe put your name maybe put your email address or your how people can reach you maybe the version number there's no perfect answer but I would say think of it the same way you would as the cover of a PowerPoint presentation it needs to communicate some key information about who did it when what the company is what the version is and that will create confidence make it look nice whatever you like um in this particular model the executive summary includes only a few things it's one box and we kept it simple we have some historical and forecast information just for revenues EBIT uh net income but there are three boxes because in this model we did the story I'm going to show with you differently Julian you're seeing a print preview of it now yes Vita you can see I did the same thing three times so I'm running it with a base case a best case and the worst case I'll go back in so I'm just showing my boss or my client what would evict aa net income look like under a base case set of assumptions under the best case under the worst case but this is just an example of what you might include so if I was to go oh and I do want to show you one nice little tip sometimes on the cover it's nice to include a date and you can see here that I have today's date July 9th and if it tomorrow it's gonna update automatically so let's go back and just spend another moment on some notes for on a cover page you know things to include our name logo maybe a version a date your name whatever whatever you like make it clean make it nice but I will tell you um if you want to automate if you want to automate the date in Excel there are only two ways there are only two ways in Excel to automate a date most people know this one you can use a formula that says equals to date in Excel there's a formula that says equals equals today I'll show you here equals to date we'll pull up today's date this is called a volatile function in Excel because it will change automatically but there's another way that people often have not heard of it and that's called equals now what that does is also shows the time the now function will also show you it shows you the dates but it also as I said show you shows dates and the time so that's what's nice on a cover page on an executive summary and exact here's the most important thing to put on it on your executive summary okay straight include should include whatever your boss or client needs to see in order to make a decision that's what belongs on the cover page so examples might include NPVs if you're doing evaluation work IRS if it's an infrastructure project debt ratios if you're trying to do some debt sculpting or you're looking at credit evaluation even margins margins whatever etc the only thing that belongs on the summary page is whatever you need to help make the decision for your model that all comes out of the planning stage so thank you for asking I'm gonna go back in because I've got another 20 minutes and I want to finish up the next 5 or 6 tips here the next one is critical 5 and we've got enough time and Twain this to finish this off tip number 5 says build a scenarios page and I'm going to show that to you this might be the most important page in the model in my the most important page in the entire model back up to my notes I'm gonna send out these notes as well guys so when um when you get the model from CFA society I'm also going to include the notes and one thing you might have noticed as I told you that a good order for a model is a cover an executive summary and then assumptions well the next thing I would say that you want to include is a a scenarios page and I'm gonna show this to you in the model right here I'm gonna go back in and reshare the model and you can see here that there's a separate tab it's so important we have a separate tab called scenarios and what this is is mmm if I go down the scenario page it says economic and pricing scenarios I have four variables cost inflation sales Majan this is an oil and gas company sales prices although wouldn't this be nice if this was the price of oil you know not exactly a but um imagine that we have cost inflation selling price sales volume I have a few variables on this page so what exactly is going on on this particular scenario page well let me go back to my notes and tell you that the scenario page I'm gonna insert some rows the scenario page is just all this is is more assumptions that's it this page is another page of assumptions but what you notice differently in our sample model in the sample model I just showed you what were the Assumption variables well it was things like cost inflation it was things like sales prices right ie you know trice of oil or the price of gold so what do you notice about these particular assumptions well these particular assumptions are the key drivers these are the ones that are hard to forecast and hard to control so what we did differently on this page is we therefore have we therefore the scenarios means we have a few cases right a few options I had in my model a base case a best case in a worst case and I showed you because I don't know exactly what the price of oil is going to be for the next five years so a scenario page allows me to have more than one option I can have a base case option a best case and a worst case option and I can play with the model let's go back to the model for a second I'm going to show you what it looks like so if we see the model here and again guys just in the interest of time I'm going to quickly show you this page but do yourself a favor when the course is open when this day is over go look at the scenario page and look at how we built it up it's very simple I want you to see it because every single model that you ever build can benefit from a scenario page every single model that we would ever build includes a scenario page and I'm going to show you exactly what this means so there are three steps to build a scenario page there are three steps to make a scenario page step one is to create this large box who's everyone see there's always a large box that has three rows just like this my base case my best is my worst for cost inflation if I go down to my selling price again three rows a base a best and a worst again sales volume I don't know exactly what my selling volume is gonna be three options so the first step in building a scenario page is to make this large box well you should already know what the large box is because there's a hint the numbers are blue what that means is that these are assumptions there are some things and that's why they are blue you need to talk to your boss talk to your client and say what do we think cost inflation is going to be under our base case or the best case or the worst case or the price of oil what do we think the price of oil will be base case best worst and then you just enter them in as blue inputs very simple the next thing you need to do I'm going to move this out of the way the next thing you need to do to build this page is have one cell that's your switch this is my switch it tells me which version of the model am i going to run right now am I going to run the base the best of the worst if I have a one in here you can see the skinny row the skinny row is the live case this is the one running in the model right the skinny one is the live case I'll put a note here that says this is the one running in the model so right now my model is running the base case because there's a one here if I put a two in the cell it will run the best case you can see now the model is going to be linking to and picking up the best case and if I have a three in here my switch the model will run the worst case so then of course the question is how do I build a formula in the skinny row to figure out which case I should run the only thing that's happening in my skinny row here is the skinny row is looking at my switch and depending on what's in my switch it's pulling out one of these options either the base or the best or the worst that's all it's doing so how do we build it what most people will do is use an if statement this is not the best it's not optimal most people will build an if if you've never used an if before that's fine you can try it on your own but this is not optimal what they'll say is if my switch has a one if that's equal to one then click on the base case otherwise if the switch is equal to a two you've got the idea is if that is equal to a two then run the best if you've ever seen really long formulas in your models it might be because of a long nested if statement so we try to avoid nested if statements because they're hard to read and hard to understand to get very hard to audit and hard to check so what do we recommend instead in the slides that I'm in the slide deck there's a page in the slide deck that you're gonna get after this this shows you a much more elegant tool and this is one of our favorites in Excel because it's so simple and it's excels choose function it's really simple very elegant all it does is it says where's the switch so I've said where is my switch its d6 and then where are my choices oops sorry with that and then where are my choices you can see the model still Julian we're good thumbs up there were yeah okay so where's the switch and then where are my three options and you can see right underneath the way it works is if this switch d6 has a 1 then the choose will pull up the next item in your formula which happens to be my base case if the switch contains a 2 it will pull the second option the best and if the switch has a 3 it will pull out the worst option the third so it's very very simple clients love it because they understand it and they can make sense of it it's not a complex function if I had some of you might be wondering what if we had 20 options 20 cases personally I think that's a lot of cases but let's say you did if you had 20 cases then I might not use the choose I might use a more complex function like an index function or an offset but in most models we've ever seen people have 3 or 4 or 5 cases and then this is a beautiful tool to run it in to pick I'm not gonna have a time to show you this drop-down but I want you to be aware of this the last thing but I want you to see is I don't want to leave my switch exposed I don't want to leave this number Swiss just sitting here because if I email this to David let's say David was my boss David Brown Nike know him my model David might say and what does this mean why is there a 1 here it's not it's not obvious why that's a 1 or David might not know what he has to put in there he doesn't know if he should put in a 1 a 2 a 3 a 4 a 5 it's going to confuse people so what we do instead is we include a drop-down this is a really great feature in Excel there is a slide in the presentations I'm going to show you on how to build this is really simple but the message is you go to your Developer tab if you don't have a Developer tab some of you might be thinking wait a second he and I don't even have a Developer tab if you don't have a Developer tab it's because Microsoft likes to hide it so if you don't have a Developer tab in your excel first you have to go to the file menu look where I am just watch carefully you need to go to your file menu and then down to options file options and then you can go to your customize ribbon and when you go customize ribbon you will see an option that says Developer if that's off you must turn it on so put a checkmark in there turn it on and then you will magically get a new menu in Excel called Developer and if you go to the insert menu now under the form controls you will see an option it says combo box you see where I am and that allows you to build a little drop-down you just draw one and then you set it up did I promise you that you're gonna see it in the model and you're going to see a slide in our presentation on step-by-step instruction on how to build this because we're short on time if you can't figure it out I'm sure you will but if you have a question just email us email me and we'll make sure you know how to build it but the point is it's a very simple tool and the reason it's so nice is because now if I email my model to David Brown he doesn't have to worry about what the number means it feels like a user control the only thing David has to do says ah look at this nice model Ian built and there's a switch and he can choose the base the best or the worst and it will run the model so I really Inc there's no macros there's no VBA it's a very simple user control that many people don't know about but it allows you to control them all so I would encourage you guys all your models build a cover a summary an assumption tab and a scenario strap which leads me to the last few tips of the which leads me to the last few tips of the presentation which we'll do quickly are we back on the best practices tips you Julian we're good to go okay thank you is the last ones we're gonna be very quickly and we can do them quickly there are two major approaches to building models there are two major approaches for tab structured and I will tell you what the two major ones are there are two major approaches for tab structure in models and what we will say is that they are vertical people either builders models vertically or horizontally and the one I'm showing you today is a vertical model a vertical model means that many of the pages are stacked vertically rich should sound obvious um so if I go back to the model for a second this is what it means a vertical model means that many of the pieces like the revenues are on the model tab and then I when I go to app costs underneath it and then as I roll down the income statement is underneath it um that's not always obvious to people but what it does is it keeps your formulas very very simple because everything stays contained on the same sheet and we're one of the big reasons why models often get in people getting trouble with models is when you have too many tabs all your formulas have to link back and forth between the tabs and it gets very difficult to figure out I'm sure you guys have seen has anyone ever seen a formula they look I'm gonna do has anybody ever seen a form look that looks something like this I'm gonna make it I'm gonna make up a nonsense formula but just watch this carefully have you ever seen a formula that says something like equals and then it's linking to a number on one sheet and it's multiplying by a number on another sheet and then it's dividing by a number on a third sheet I'm just you know is this is this is nonsense but I'm just and then you're I'm just showing you what this looks like then I'm adding in a number on a fourth sheet who cares I'm making up a number here my point is have you ever seen a formula that looks like this I'm linking to four different sheets in one formula believe it or not we should never do that it makes it very very difficult to audit to check if David's my boss David's gonna be very upset with me because he's gonna have a very hard time understanding what I'm doing and we find that this often happens when people have too many tabs so we want to keep our tab numbers manageable so that our formulas can stay simple I'm gonna leave that formula here on the screen and and what I would say is try to have a minimize we saw a model recently that had 113 tabs and it was impossible to work with the client was found it impossible because there were just too many tabs to work with and I'm Control seen that so to have a have a manageable number of tabs in your model maybe that's five or ten but if you have 20 or 30 or 40 tabs in your model that's going to be too many it's going to be very difficult to work with your model and we talked so I said this try to have a manageable number of tabs is 0.6 here's another critical take away one of the biggest errors we ever see is this 27 and 8 go together actually all of these go together but let's go with 7 8 and then 9 and 10 and you're gonna see this you're gonna get this guy's after you'll read them on your own point 7 says do not build any formulas on your financial statements and I truly mean never it doesn't matter how flex the model is even in a very even in a more complex infrastructure model never build any giant formulas right on the financial statements so what am i and then what am I talking about that's point seven if I go back to the model take a look if I roll down to my income statement the only thing I want to see on my income statement if I click on revenue does everyone see I've got a revenue line I never want to click here and see a big giant revenue formula the only thing here is a link look how simple that is what about my cost of goods sold I do not want to see a big formula in my cost of goods sold it's just a link what about depreciation I don't want to see a big depreciation calculation what's in here it's just a link well then the question you're all wondering is well where are you linking it to well tip number eight point number eight on my top ten list said build schedules so the nicest way to build a model in a clean simple way is never do your work on the financial statements what we do instead is we build schedules for every part of the model so at the top I have a revenue schedule my revenue schedule lays out row by row what's my pricing if this is an oil and gas company what do we think pricing will be for this company what are the volumes going to be and then we build up to revenue the revenue is simple the revenue of course is price times volume maybe you guys are thinking well Mike maybe you're thinking that the company's you model how many products sure I'm sure they do all it means is you do this more times your revenue schedule might be very long sometimes a revenue schedule could be hundred rows a couple hundred rows that's fine but the ideas are simple you build up your revenues one one piece of the time and my point here is that when you get to the income statement there is no big formula it's just a link to the schedule same thing cost of goods sold never build a formula for cost of goods sold on the income statement it's just a link where's that linking to right above me and look how nice it is because my model stays vertical it's directly on top of me linking to this cell this is the bottom of the coschedule there's a cost schedule and I'm linking to that so every single line on my financial statements is a link so where is my depreciation linking to well that must be underneath me a depreciation schedule same thing with interest same thing with income tax guys I really encourage you when you see this model when you get it afterwards look at this carefully see the simplicity of the links you will never see a big formula on our financial statements but I promise you your boss will love this because it's very easy for them to follow the logic and understand what you're doing so that was point seven eight point seven eight and I apologize again if someone has to drop off now go ahead I have about three to five minutes left and then we will then we will end but if I go back to my notes I said don't do your work on the financial statements do them on schedules and then the last thing is nine and ten which I can do quickly how do we keep it simple we're on Julien around my top ten tips here you can see this yeah we're good Aksum so then the last two tips say build simple formulas and how do we build simple formulas we repeat the data so let's go back to the last two things here I actually already showed this to you I never never want to see a formula that looks like the silly one I built never I never want to look at your model and see something like this that's gonna be very difficult for me to understand what would I have to do what do I have to do now if I want if you built this guys and you emailed me you're forcing me to go back to this page and figure out what that number is and then I have to go back to this page and figure out what that one is and then I have to go this page and figure out what that is if you build hundreds of formulas like that it's going to be impossible for me to understand what you did what did we do what's a better way what I did is I repeated I if I want to build a formula I first repeat look at this I have repeated my scenario I repeat one value I repeat another one's it's a simple link and now my formula is simple look how simple this formula is I'm taking last year's no don't worry don't worry exactly what I'm doing but my point is it's understandable you see I took a number last year and I'm multiplying it by a value that's right above me same thing here a simple formula every single cell here is either a link a link to an assumption or a simple formula a simple formula a simple David Brown is gonna understand this formula because it's simple so what did I do I repeated my assumptions and then I built a simple formula so as an example if you need this calculation if you actually had to do this I would never build it that way what I would do is I would first add a bunch of rows by the way rows are free you don't pay for your rows in a spreadsheet you can add as many as you want so if I had to build this formula here what I would do instead is this let me show you in two options here's option one a crazy link to four different cells what I would do if I again this is still a silly formula but what we would do in our when we teach and we look at modelling is I would first repeat repeat the first variable that I need and I would label it so I would leave whatever that is I would real able that value it's nonsense but I would real able that value here and then I would have the second one and I would put that in its own cell like this and I would label that and then I would put the third one you can see what I'm doing here I hope very easily and I would do that and I would real able that one and then the fourth one and then my formula would be to do this take the first one multiply by the second one / the third one and add in I the fourth one you can see I have the same answer is David Brown gonna be happier if he has to look at hundreds of formulas like this or is David Brown gonna be happier if he looks at a link a link a link each one is labeled and then when he gets the formula it looks like that everything he needs is right there so when you go through the model you'll see that that's how we've built it up so I've gone to the end here what I'm gonna suggest is as follows if you have questions and want to stick around I think we can do a few minutes of questions but I'm gonna go back to the notes and I knew this would be quick which is why I said earlier I really encourage you to watch this again I encourage you to play with the model when you get it and I encourage you to look at these takeaways I promise you I probably teaching modeling for many many years I promise you if you can leave this one-hour session and you can think about these 10 ideas and you can do your best to incorporate these ideas into your models I promise you your models are gonna be cleaner stronger more effective and create a lot more confidence than they ever would have if you didn't so guys I really hope that helps um I know we move quickly here but I hope it makes sense I really want to thank you all for your time I really enjoyed being with you packed a lot of stuff in but that's why we're giving you some resources after if you have to drop off again totally understand but if you do want to stick around I'm more than happy to stick around for a few questions so I'm gonna turn it over back to both my colleague Julian and David to see if you guys have any questions or thoughts that we can address here for a few minutes before we let everyone move off into your afternoon thanks thanks a lot of things and that was super that was excellent I have a quick question straight up from our salon what are your views on the modeling time monthly quarterly semiannual or annual in case of energy project supported by public/private partnership yeah that's the next question so it's an excellent question I mean it's an excellent question um there's a word for that we use in modeling and it's called the periodicity it's called the periodicity and the period the periodicity means what's the right timing that I need in the model and I'm gonna tell you there is no right answer there is no right answer the right answer is back to a point that I made Julian are beyond my notes here you can see this the right answer is what's the right period of time that you need in order to make a decision so one of the critical assumptions that you should think about when you're making your model is what's the correct up periodicity meaning do I need a monthly do I need quarterly you all surely the annual all models have annuals but do you need monthly and quarterly can I will tell you we have seen so many models where people built in a monthly forecast or a quarterly forecast and whoever's ask the question knows as soon as you add in monthly and quarterly the model gets much bigger because now you have dozens and dozens of columns the model gets slower we have seen so many models where people build in monthly and quarterly and then they never use it the reason they thought they might need it but then they didn't if you need monthly or quarterly modeling to make a decision then you need it so for instance if it's an infrastructure project and you need to be able to sculpt your deck on a monthly or quarterly basis and you have to show the regulator some results on a quarterly or monthly basis then absolutely you're going to need monthly or quarterly models if it is but if you're simply building a model to do a valuation if you're looking to do a private equity situation or or you're just building a model to evaluate a look at a value of a company you might not need it so every situation requires different periodicity I do want to give you one tip though I want to give you one great tip um I think if you need multiple time periods in a model there's a good way and a bad way if you need a period if you need to have multiple periods in the model I'm gonna tell you what we see maybe maybe you can relate to this guys this is gonna be a challenging way to do it but this is what people do can you guys relate to this imagine you had to build a quart of me imagine your boss said we need a quarterly model then you said okay by the way whenever a client says to me I need a monthly or a quarterly model you know the first thing I always do is I will say to them are you sure I will challenge that assumption every single time because I want to make sure that we actually need that for this particular model only because if you build in month as I said if you build a monthly or quarterly model and then you don't need it it just becomes clunky there's more risk of error but if you if you do really need a monthly or quarterly model this is this is the challenging way when people do this has anyone ever seen this before guys a model that says XI 1 Q 2 Q 3 Q 4 and then David wasn't Toto toto terrible annual has anyone ever seen this before and then you do Q one two three four annual q1 annual can I tell you something this is a disaster and I would encourage you if if you have to build a quarterly or a monthly model do not do this can I show you what I've seen this is gonna make many of you very uncomfortable but what I've seen is people losing January then they'll do February then they'll do March and then they'll do q1 and then they'll do April and then they'll do May and then they'll have Q 2 and then annual why is this a disaster it's a disaster because you have different formulas in different columns you can't just copy all the way across these quarterly formulas are doing something and then the ante one has to be like a sum of these ones it's very challenging when your columns have different formulas we always find mistakes and models when people do this so what is an optimal way if if you need multiple periods what I would say did the more optimal weight to model it is this hue one two three four one two three four one two three four or January January February March and then you know April and then May so what what I mean is only have the quarters in one spot and then what I would say is then um then off to the right or on a different sheet have all have all the annual type here all the annual columns you need to keep periodicity together and I know it sounds obvious but it's not what people do most of the time if people are not thinking carefully about it they'll do this that always comes back to haunt you and it's very it's very hard to change it if you build a big model this way and then you realize oh man that's terrible that's a disaster it's very hard to go back and fix it it's like doing surgery and and really hard to fix it later so I hope that answers your question your job as a model builder is ask good questions find out the right periodicity you need to make the right decision if you need monthly or quarterly then you have to do it but don't just do it because someone says it make sure you really need it and then if you're going to do it build it in a way that's optimal to keep your model clean and simple so it's a great great question love the question a question yeah so I don't know if you have time for just one last one and I have time I'm okay for time it's only early in the morning here so I'm good there are still two hundred and sixty people that's waiting so that's amazing let's do that sixty questions then before we leave that's great I like that too so sad that asked what is the best practice where the model excel file becomes too large splitting in multiple files okay so I have an idea around that but what I will tell you is this so I think you're talking about a couple things you're talking about file size you're talking about file bloats we call this bloating when a model gets too big as I would say as a rule of thumb as a rule of thumb as a rule of thumb models are hard to work with when they're more than 10 megabytes but that's huge models are hard to use when more than let's say 10 megabytes but that's giant that is giant and I will tell you that if your model if your model is anywhere close to that if your model is anywhere close to 10 megabytes you may be dealing with file bloat there are lots of reasons guys I don't have time to cover it today there are we actually have a presentation on the top 10 tips to get rid of model bloat which is ways to get rid of garbage very often when a client sends us a model and or we look at a model it's really big we can usually discover that there's a bunch of stuff Excel garbage that is making the file really big and we can clean it up if someone's interested I can maybe send you our a list of things to check examples if you have lots of external links if you have um some old names or macros or graphs or visuals that can often make the model get huge in size even if you're not using those things um we would Inc we have built some huge models of massive companies that have tens of thousands of rows and they're still in one file and they are less than ten megabytes so strong recommendation strong recommendation - that's but strong recommendation to keep a model in one excel file it is it is not ideal it's not ideal to link between excel files you all know that it's possible you can link one excel file to another we see so many errors when people do that because if you have multiple files linked together you have to make sure all of them are open all the time because if you make a change on one and the other one is not open it might not properly pull the correct links anymore it began and then if you email one file to somebody else they might not be able to work with it if you did not send them the other files so many people over the years have become frustrated with models and and they abandon their models because of external links so to two tips on that if your model is really huge you're welcome to email it to us if you want but if your model is really huge more than ten megabytes there's a very good chance that there is some garbage and if we take out the garbage the same model will be I can tell you we have looked at models that are 30 megabytes 30 the client didn't realize that there was some garbage we took out the garbage and the model dropped to like five megabytes or four megabytes and nothing changed but there was some junk so that's one thing to think about do you have bloat in your file and secondly I would really encourage you to keep everything in one excel file to minimize the chance of errors so I don't know if you want to add to that David but that's that's something that we have found by looking at thousands of models it helps yeah definitely that's that's it so it's really model bloat and most of the model bloat is based on formatting so it's just formatting you wipe out some formatting for highlight highlight columns at the end and just delete you think they're empty but no they have some funny the stuff in there and they will really reduce it so I agree with you hundred percent because I build a model that has hundreds like a group model with a hundred companies in the same model and it's less than ten megabytes thank you I didn't even pay him to say that but um but that is that is exactly right you can put a lot of stuff you can put a lot of stuff in a model and keep it under ten megabytes so yes there are some tips formatting is one of them to clean stuff up did you want to do a couple more questions here that might be topical yes I do so a candidate has just asked does the define name function for input affect the model size um so does define naming I think a little bit I don't think it has a huge impact but what I will tell you is that when you go into the name manager I hope you can see it in the name manager here which is control f3 pops that open sometimes you'll go in a model and you will see dozens and dozens and dozens of I have actually looked at models that have hundreds of old names and if you don't know what that means it just means that you can name a cell so if I have if I have some cells here by default by default a cells name is its address so this is the address d1 13 that's its name I could change it I'll name that one David here we go so that's now David so that cell is no longer d1 13 look what I have I have d1 12 there is David and if I go down here I could name this one Ian so people do this sometimes because now if I build a formula I can say what happens if you add together David there's David and if I start typing equals DAV I can say what do you get if you add up it almost sounds like a joke right David what do you get if you had an iam now these names are here if you add up Dave's eternium you get 81 what does that mean I don't know some people like doing this because they think it's easier to read we act and I don't have a strong view we don't do a lot of naming in our own modeling because we repeat data what you'll find is if you repeat every single assumption and you build little formulas like I showed you at the very end of the presentation I think you'll find you don't really need it but if I go back in my name manager control f3 now you see I have two names here somebody asked if it creates a lot of extra bloat these names these two names will not create a lot of exercise but some people have names that refer to huge formulas and they'll put a giant formula like it offset or an index right in here if you have hundreds like that then yes it will definitely add a lot of processing capacity if it's looking at lots lots of things so it is it's one of the areas we look at when we try to reduce file size is we look at the name manager and if there's a lot of garbage names we'll take them out and the file gets smaller yeah if I could add to that so what I see some models some people prefer to name the entire row so you name the entire row name it revenue your name cost of sales and then you go into profit gross profit and say equals and revenue minus cost of sales so me that's terrible use of names just like Ian said you shouldn't do that the only good use I see for names is to simplify your macros so sometimes you need to build and I always say don't build VBA don't write VBA if you don't need to but it will simplify some of those coding and then and what I also have as some best practice Ian it would be nice to get your opinion on this is I use if there's a single cell a single cell that contains something so important as used nearly everywhere then we could give it a name a descriptive name your and and then use it everywhere else but then you need a name naming convention so yes naming convention for how you name right so why don't I just do a very quick demo and show people what you're talking about so what they by the way David said this is a terrible use of names I'm that's I don't feel quite as strongly about that um but i but i we don't do it we don't do it um but um what he means is this you could take these four cells i could name this you know i could go in and very quickly can select these four cells and i could i could name these i could name this here if I create a new name I name this revenue I'll just call Ari beep and there we go I've got a new name called revenue I could dance like these for it's just not necessary this is it's not necessary to do this if I click on new I could name these costs so now I have two new names these four cells are named revenue you can see up on the top it says ref and these four cells are named costs so now if I want to get to let's say EBT earnings before tax I can say equals revenue minus costs and what it will do is it will and it's a new version of Excel it has spilled it so it is it is taking revenue minus costs all the way across and so it's applying the name and copying it you can see in my formula bar the names revenue minus cost I never do this I don't think it's necessary but what they even mentioned that I do agree this is fine if you want to name your tax rate I can go in here I can also just type in tax rate like that and now I can have income taxes I could make my income taxes I can simply take that cell and multiply it by tax rate if I say take my earnings times the tax rate some people think it's a little bit easier to read because now I can say oh yeah I'm multiplying it by the tax rate again I don't do this in our models because we first repeat all the assumptions yes definitely nothing wrong with this the only caution is if you find there are dozens and dozens of names it can get confusing so we minimize names but yeah should we do a lot of still there's still a lot of people on so again what we could I think let's I don't have this anymore so the naming things so back to the naming thing a hundred percent like tax rate you should just repeat them have a row of tax rate so what I really use names for our scenario analysis right so when I'm creating scenarios I use the name to name the list under switch to have this methodology where you're naming the list they're naming a single-cell switch and you use index lists which it's just a framework so index the list and the switch index list comma switch and it pulls whatever you need out so that's just simplify as much as I make it simple and make it easy to understand otherwise don't use it yeah do we do a let me do one more and then I'll let people go I mean we still have a couple hundred people which is awesome to to all of you hate to all of you hardcore modelers out there I applaud you and I guess you can tell from just from our excitement and passion I mean we could talk about modeling for glittery hours there's so much to us there's so much but that's that's part of why we built credit EFM I to make sure people truly understand and have you know understand and how to use all these ideas ready should we do so there's a question that says could you discuss the skills to check a financial model section a little bit I don't know what that means the financial section it was the seminar agenda but obviously we can't go over it sure let me show you a couple then what I'm going to do I have included some slides and I can apologize we had a we had a bit of a delayed start so I included some stuff in here can you see my slides Julian are we good on that yeah why don't I do this why don't I show why don't i end why don't I end by showing you a couple of my favorite tips they're all here guys so you can check them page 17 the slide you're gonna get it has my favorite tips to check our model and I'm gonna show you a couple very quickly and then we'll wrap it up and and then you can look at these on your own but a couple of great ways to check them all up to make sure that it's working and that there's no hidden time bombs or errors in the model so I'm gonna go back to my model here let me change my control so again check out this page and then again you're I'm by the way goes if you don't if something doesn't make sense as I said already just email me email me and we're happy to address your questions and and talk about them after what do we do only get rid of some stuff here see the model here is you're gonna be good we're seeing a lot okay excellent so let me get rid of this stuff imagine that whoever asked the question about checking imagine please that you emailed me the model and I want to check it how difficult would it be to do this can you imagine how painful it would be this and this is actually a pretty small model can you imagine how painful it would be to check the model by me going to every single cell it's not possible can I can you actually do this say yeah that looks good yeah that looks good you know you cannot go and check a model by checking every single cell it's too time-consuming so there's some really great skills we look at one of the things that I like to look at all the time is did somebody override a formula as an example does everyone see here that this is a formula that takes my gross price don't worry what it's doing but I have a formula here it's taking one minus the other and my net selling price my net price of let's say oil or my product is 750 whatever my boss said to me hey Ian what happens if the model is running at 850 my boss might say Ian run the model if this is at 850 not 750 I might be tempted to do this has anyone ever done this you can raise your hand to yourself if you've ever done this if you've ever typed in a dead number like that right of the model so now I've typed in 850 it's gonna run through but now of course I've destroyed my whole model because it's not linking anymore I have a dead number so one of the things I like to do when I check a model is I have to see are there any dead numbers like that that I need to know about there's a really really great skill in Excel to look for dead numbers and you're gonna press the f5 key on the keyboard f5 opens up the go to menu so f5 if that didn't work guys if f5 didn't work it might be ctrl and the letter G G so ctrl G opens up go to so everybody knows everybody knows that there's a go-to screen that says go to what you might not have seen before is that there's a button called special this means you can go to some special places I don't know why the people at Microsoft called them special but watch this if you say go to and then special you can click on an option that says Constance if you say go to special Constance and you click OK then look what it does it's amazing it highlights every single cell in the entire model that was entered as an input I'm gonna look this is okay because these two are historical that's okay but I'm gonna have a heart attack when I see that this number here is lighting up that's not supposed to light up that's supposed to be a formula because it's in the future but I now know that if someone built this they typed it in as a hard number and if I scroll down I can look and see did anybody type in a number when this should have been a formula now I can go here and say oh my gosh look what they did they typed it in and I can now go back and copy and I can fix it so that's a really really great skill to check for dead numbers you will find that let me show you one last tip and then we'll end it up here right on the hour so that found this that found my error if you type in a dead number but what if somebody did something else what if somebody what if I was using the model and I said one minus the other and then I added in a number 50 ok what if I that's a hard code I put in a number at the end it says 50 that's also a big problem that's also a big problem because I don't know that it's there now you might notice that there's a green flag people don't find these green flags very effective because sometimes you might have lots of green flags some people turn them off and back honestly I I find that they can get so annoying most a lot of model builders I know turn these green flags off because when it's on they might light everything up everything might have a green flag and it might not be helpful so I'm actually gonna go to the file options menu I'm gonna show you under options how you can turn it off you under formulas under the air Excel options formulas most people that I know like to keep it off error checking and you turn it off and you click ok now it's gone because again if you turn it on you're gonna find that there's lots of green flags and it's still not helpful there's a better way look what I can do if I ever notice let's say I noticed that this formula is a good formula ok this is a good formula I should not have to check every single one it's too many what I want to know is this if I like the first one are the rest of them exactly the same because if I like the first one and then the model builder copied it and if they're all the same then obviously I'm gonna like all of them they're all gonna be ok well there's a great keyboard shortcut here that says if you select the entire range and then you press ctrl it's this one it's gonna be ctrl key it's the control and then the end the backslash now that that might not work on your keyboards guys in them in Nigeria but what happens is if I select a formula and I like it and look what I do i press this keyboard shortcut ctrl backslash look what happens what it does is it stops at the one that's different it realizes that this one is different than the first one so my first one was that it realized that this is different and now I can go say oh my gosh what's going on here somebody put a dead number at the end look even if there was two of them like that imagine someone put a dead number here also five if I like the first one and I select the whole row and I press ctrl backslash it stops on both of them and I can say wow that is two errors that I need to fix why am i showing you these tips because these are two of the biggest errors people make in models they have dead numbers or they have numbers at the back of a formula now watch I just fixed it so now if I like the first one and I press select the whole row and I do this control backslash it says no cell that's perfect that's good it means all the formulas are exactly the same as the first one there are no inconsistencies and just last if this keyboard shortcut is not working for you if this keyboard shortcut is not working you can also use this tool by going back into the go to menu that I showed you a minute ago watch this if you go go to and then you go special you can do this you can say look for row differences it's a little more time-consuming because you have to do it with a couple steps but the feature still works use this to check your models look for road do differences it if you click OK it says no cells found again if I had a dead number at the end like 50 and I did it from that menu if I go back into my go to menu and I say special row differences and click OK it will stop on use this guy's use these two techniques there are other ones I'm gonna go back I'm gonna wrap up the day the session right here can you see my screen you're dealing it back to PowerPoint yep look at these tips if they don't make sense email me but I pointed out a couple of the more important ones I promise you you will find some issues with your models if you use these tips so I'd like to formally end it because I know some of you got to go but I'm so impressed that a couple hundred people are still on you know as you can tell I love talking about modeling as I said to start the day it is the one of if not it's maybe the most important skill in the world of finance if you've got strong modeling skills they will stay with you forever they will help you in any business job you ever take they will show that you have a great skill to think about a company to analyze a company to tell a story and you will be way ahead of most other people because most models are not very good so I really encourage you to go back through the tips that I showed you think about the issues use those issues in your own models and if you and you know try and build your modeling capability if you have questions feel free to reach out would love to hear from you would love to see you at the fmi and you know if you want to prove your skills to try the first level of the exam again David is is it is a great guy to help with that because he's an approved training provider there are a whole bunch there but this is a list of all the approved training providers in the world and they have different offerings some have online somehow here's David's logo but the point is there's a whole community in the world that's trying to help people build better skills and I hope this was a helpful today so I'm gonna leave it on the hour cuz I am I feel bad that we've been ready gone a little bit long over but I loved it everyone's sticking around I don't know if anyone from the CFA society whether Gloria or Frank wants to say anything before we before we wrap up I want to thank you guys again it really enough I hope to get there in person one day I've always wanted to get to Lagos but I'd love to see some of you in person but is there anything else before we before we wrap up here then no yeah we're good good you love you okay thank you Frank Thank You Gloria thank thank you your honor Thank You Gloria and Frank for thank you David for hosting did you wanna say anything David before we just telling I was telling Frank he needs to write the exams as well everyone should write that's good so this was this was wonderful this was excellent and I mean as you said we love modeling I think is one of the most exciting professions in the world because it's it's a profession that you need to sometimes be a lawyer especially when in a project finance deal and you need to understand the contract you need to be an economist sometimes when you're doing an optimization or probability and you need to kind of build a Monte Carlo you need to be an analyst of course you need to be a risk analyst sometimes you need to be an architect sometimes you behave like an engineer so what other profession does that so yeah I'm glad you guys like it because it's a wonderful thing to be able to be a good model I totally agree with you David I said earlier it's multidisciplinary and you become you become the smartest person in the room about the company or the project that you're modeling if you've done a good job because you have to think like David just said but all the pieces of the so I hope this has helped if you have any feedback we'd love to hear your feedback as well but have a great rest of your day guys um and we look forward to being in touch with some of you thank you so much for attending and I look forward to seeing you so with that Frank Frank I didn't know you're writing the exam coming up that's excellent I'm I will first babies on me for that