Financial Modeling Quick Lesson: OFFSET / MATCH and Data Validation (Part 1)

thank you for tuning into this financial modeling quick lesson in this lesson we're going to do three things first we're going to show you how to integrate scenarios into a financial model then we're going to build a drop-down menu in Excel we'll show you how to do that using the preferred way to do that from a financial modeling standpoint that's using data validation and then we're going to do is we're going to connect those scenarios in with the drop-down menu so that whenever someone who's using the model selects at one of the three scenarios we're going to have a best-case base case in a weak case scenario whenever one of those are selected we're going to see how that affects the entire model so connecting the drop-down menu to the actual scenarios we're going to do that using one of the one of the more powerful function combinations in Excel and that's the offset match function combination before we begin I wanted to remind everyone that to get more detail and get more in depth about any financial modeling or valuation modeling topic to get deeper dives into excel shortcuts and learn more about investment banking in general visit our website at wwlp.com okay let's begin so what we have here is a simple income statement and we see we've designated the historical part of the income statement this is something these are the results that have already happened you know from a formatting perspective hard coded numbers are formatted blue formulas and calculations informative black that's a general industry convention and what we need to do is we need to figure out how this company's revenues all the way down and net income are going to be forecast notice that some of the line items formulas are already calculated we know that regardless of what's actually what the revenue or operating expense figures are going to be for example operating profit will be calculated the same way it is calculate an historical basis which is revenue minus operating expenses same goes for pre-tax income and net income okay so what we've done right below these store Claire is we've already calculated some of the historical operating statistics so we can see the revenue growth over the last two periods was 5% and 8% respectively and then we have operating expense margins which appear to be deteriorating or I should say actually operating expenses going down as an improvement less expense for every dollar sold and then interest expense is a percent of revenue there are many different ways to actually forecast interest expense probably the most robust ways is off of a full debt schedule that's a little beyond the scope of this quick lesson so what we've done here is simply looked at interest expenses of percent of revenue and we see that that is declining a little bit as well and the tax rate is holding a fairly steady around 39 percent this is all an historical basis and now what we've done here is down below we've got some some empty empty areas so the first is you see the shaded box this is what we're going to put in our drop-down menu but before we do that let's actually build in some operating assumptions so I'm going to split the screen like I've done some other quick lessons all WS will split the screen and that enables me to jump using the f6 key back and forth from the top of the screen to the bottom of the screen fairly easily and so here I am and what I'm going to do here is from a forecast perspective I'm gonna I'm actually going to look at sort of what historical revenue growth figures look like and I'm going to create a best case a base case in we case for those assumptions an important part of modeling is actually spending some time trying to understand where these forecasts are going to come from but for the purposes of this case study which is us trying to really just understand mechanically how to build these we're going to just put in some assumptions and and just move on so revenue growth in the last historical period was 8 percent let's just assume that on the best base case scenario it continues to be you know 8 percent throughout the forecast period and I'm going to format that blue I'm using a toolkit called the boost add-in to ok which enables you to format things automatically based on your preferences that toolkit by the way can be downloaded from WSP analytics comm and now I'm going to put some tails around that so a best-case scenario let's say is 10% and let me fill that across and let's say a weak case would be 6% again actually arriving at these numbers can can sort of take up a lot of an analyst time but for for our purposes here we just want to we just want to put in some placeholders so we can see how have the numbers play out operating expense margins historically were 58.5% so let's assume that they stay at 58.5% going forward again all of these sort of excel tricks that you're seeing are done using the boost toolkit which basically lets you attach shortcuts to a whole bunch of common tasks and then the best case scenario let's say best-case scenario they're at 55% in other words they really are able to improve their cost structure down to you know only fifty five fifty five percent in a weak case their cost structure looks like 62% again I'm just making up numbers here okay and then interest expense is a percentage of revenue it was seven point four percent before so let's assume that it continues is seven point four percent let's assume that the best case scenario is six percent and the week' scenario is 10 percent lastly a tax rate it says thirty nine percent on a best base case on best case let's say it's thirty eight percent and what we case is forty percent let's fill that through across of course we could have different assumptions during different periods if we wanted to but here we're just trying to get the mechanics under our belts okay all righty so so now we have some of these assumptions laid out and so now the challenge I'm going to unsplit the screen here and now the challenge for us becomes let's italicize all these keep the formatting consistent now the challenge for us is to really connect that to to this general air I'm going to move the text box away so it doesn't it doesn't bother us and and so the first thing that we're going to want to do is let's just actually build this drop-down menu and the way you build a drop-down menu which we're not going to connect to anything yet but we're just going to have a drop-down menu here that lets a user select do I want to see what the results look like on the best-case-scenario base case or we case I have to go into data validation so with the mouse you can see where it's located it's under here so if you go to data data validation you click here data validation this is this is where you need to go to create a drop-down menu there's actually another way to create a drop-down menu in Excel but this is a preferable one so I don't even want to talk about the other way so data validation the shortcut for that is alt DL is a couple of other shortcuts but LTL DL is the quickest one and that pops up that data validation window and in that data validation window under settings if you want to drop down menu you want to go to lists list tells Excel I want to drop down menu in the active cell and in here you have two options either you type in the stuff that you want in the cell so we're going to do it this way first we're going to hit OK and what you'll see is a little drop-down icon appears and if I click the mouse here I'll see the three options without the mouse if I hit alt down arrow key I and then start using the arrow keys I can select whichever one of these options I want so let's put the best case I have the base case I hit enter and I see it in here and again any time I want to change it I simply just all down arrow key and hit and so now I have the scenario I want I also mention guys that there's another way to do this and the other way to do this is to actually go again to alt DL for data validation and within the source instead of actually hard coding the stuff you want imagine if you have for example eight cases or seven cases which is actually surprisingly not that uncommon you could go in with the arrow keys and select if you have anywhere in the model just all the scenarios laid out or any of the texts laid out you can just as long as it's contiguous you can just grab all of it and hit OK that way there are circumstances which that approach makes more sense than the first approach and vice-versa so um I'll leave that for you guys to decide for your own you know whatever you guys are working on yourself so so now we have the scenarios but what we still haven't done and this is the final step and that is to tie the scenario to to the actual model itself right here so here is so there's actually two steps here in general modeling the best practice is to have the four forecast to have the operating assumptions like revenue growth rate and operating expense margins and all these assumptions down below and then for the main area of the model to sort of reference that and turn that into an explicit forecast I'll show you what I mean in a second if for example I knew that I wanted the base case revenue growth figure so what I do is I'd reference into here right and then I'd calculate revenue growth based off of that that assumption and in fact let's let's do that across the board and then I'll sort of change the operating assumptions here so if I want operating expense margin I select base case and I want interest expense as a percentage of revenue select base case and tax rate a select base case well all I need to do in that case to finish up the model is to fill that across and to calculate operating expenses at as 58 percent of my revenue assumption to calculate interest expense as revenues times my interest the percentage of revenue assumption and taxes has pre-tax income times my tax rate assumption now I can fill that through to the right and I'm and I'm effectively done with my model now we're not quite done here because if I change that the best-case-scenario nothing happens and we do want something to change we want all of these numbers to change based on whatever the user selects for his scenario there's actually a couple of ways to do this there's several ways to do this and one some of you may be thinking probably the most straightforward one is just a whole bunch of if statements in here in other words if you know this equals best case this is not a good way to do it but I'll walk you through how you would do this just you see it's probably the most simple one to use it's not the best one but it's a simplest one for for beginners if this equals best case then then output best case otherwise then you need another if statement says if this equals base case then select this one otherwise if it's neither best case or base case it must be weak case and we have to make sure that we in fact I already screwed up the son you have to make sure that you are very good about closing your parentheses and that's actually the weakness of these embedded if statements and even to embedded if statements sometimes can be fairly difficult to sort of think through three embedded statements become a virtually impossible so here you go now base case and weak case oh yeah I screwed up my formula what did I forget okay I need I need my weak case okay here we go so best case works for for this particular cell for this one rather because I only did it on this one best case base case and we case now the problem with that is I already sort of mentioned is that if statements if you have five scenarios which is not uncommon you start you're starting to look at for embedded if statements and for those of you who have done a little bit of work in Excel you know that trying to navigate through embedded if statements is a nightmare and so we start taking advantage of somewhat more sophisticated Excel functions to do the same thing in a more dynamic and elegant way and probably the more common sort of fixed for building scenario analysis is combining two functions the match and the offset function let me walk you through how I'm going to do that so I'm going to eliminate the border here and I'm going to do this across the board for I'm going to delete these I'm going to do one and I'm going to let you guys take a stab at at the remainder so I'm going to do offset match for revenue growth I'm going to delete this here is offset and so offset works as follows offset says give me a reference point anywhere in the excel file in the Excel model and output a result that is X rows below that reference point and Y columns across and for our purposes this height and width parameter is not required so we really are looking for three parameters so let's see what I mean by that let's get a reference point going right now this won't quite make sense so give it a give it a moment if I wanted for example weak case I want to output something so I just arbitrarily picked a reference point right right above where the three scenarios are and I want to output a result that is one to three rows I'm going to hard-code for the moment below that reference point and 0 columns across and that'll actually give me that 6% that'll give me 6% if I want to make that two rows it'll give me 8% if I want it one row it will give me 10% so now the next challenge is to make that dynamic so depending on whatever is selected by the user here that's how many rows it'll go down it'll always be zero columns as it turns out if I pick my reference point right above where the scenarios are ill you'll always be able to maintain column number of zero so it's this number that's either one two or three that needs to become dynamic based on what the user selected and for that we're going to use the match function and so the match function says give me a certain lookup value that's going to be this whatever the user needs to match and match that to its relative position within a given array okay so that's a little bit confusing but what does it mean what what is the relative position of the of the phrase or the string we case within this array is it position 1 2 or 3 well clearly it's 3 and that's what this is outputting the last argument here is it's looking for a match type a match type we just we're looking for an exact match and so you owe in this particular type of scenario analysis you want an exact match you don't want to excel to try to find the closest match and so we type in 0 and once I hit enter here what I've done is it is really built a dynamic formula such that if I select this without using an if stick such as if I select base case it's 8% if I select best case it's 10% there's one other thing I need to do in order to make this really dynamic and that is to anchor by hitting f4 the the array because we never want the array to move and and the we don't want it we don't want to anchor the c18 because we do want that to move as we copy the cells over but we do want to anchor the users drop-down menu so we're going to hit f4 it there as well so I'm going to fill that across and I must've oh did I not anchor it oh I incurred the wrong thing okay so I anchored the reference point where I should be anchoring c80 okay alrighty so now I have this cell complete and with the with the file down in this video you'll see in a little note on top of this video where we started the video you'll see a URL for where for you where you can download this template and try filling out the in the next video I'll walk you through the remainder after you've had a chance to struggle with it but the main takeaway at this point is we now have a model that we can begin to sort of dynamically link scenarios to and whenever you change your scenarios here it'll all flow through here without ugly if statements okay we're gonna stop right here and come back in the next video and show you how we finish this up

Channel: Wall Street Prep

Views: 165,946

Rating: 4.9315314 out of 5

Keywords: financial modeling, offset, match, excel, data validation, wall streeet prep, investment banking, Microsoft Excel (Software)

Id: P_b8SRxeudY

Length: 17min 52sec (1072 seconds)

Published: Mon Dec 24 2012