Sensitivity Analysis in Financial Modeling: A Practical Guide

[00:00:00]hi and welcome to the sensitivity analysis course with the corporate finance Institute this course Builds on our previous courses on how to build a financial model in Excel by the end of this course you'll be able to build a dynamic sensitivity analysis on top of your financial model you'll be able to build a data table that displays a range of outputs based on changes in assumptions you'll also be able to build a gravity sort table which automatically ranks sensitivities based on their impact finally you'll be able to display your results in a professional output this course is critical for anyone in Investment Banking equity research Financial Planning and Analysis and corporate development now let's get started hi and welcome to the sensitivity analysis course with the corporate finance Institute my name is Tim Bond since 2005 I've been an active participant in capital markets across a wide range of Industries and transactions that include mergers Acquisitions divestitures and capital raising I've worked in a variety of different capacities that include Investment Banking and investment management and also on the corporate side on corporate development and investor relations I've spent a lot of time in Excel building Financial models and I can say that sensitivity analysis is one of the most important parts of a good model and a good analysis in this course we're going to focus on best practices industry proven techniques that produce the best sensitivity analysis let's get going let's talk about why you would perform a sensitivity analysis there are three main categories in terms of reasons to perform the analysis the first is for valuation second is for business planning and third is for model testing the first category valuation analysis allows us to assess the impact on valuation as our assumptions change it's very important to understand the range of values for a business under different scenarios this is critical for Investment Banking and corporate development type work next we have business planning this allows us to internally plan in a financial planning and Analysis type environment what our cash flow and funding requirements might be how hedging strategies might play out and what if analysis under different business scenarios finally model testing is a best practice to ensure that the model has proper functionality this allows us to stress test test extreme outcomes and make sure that we see intuitive results let's talk about model integration sensitivity analysis is typically performed at the end once a financial model is already completed it's best to build it in a separate space perhaps at the bottom of your worksheet so you have a nice clean area to display both your input assumption and your outputs this is very critical all the drivers for sensitivity analysis must be on the same worksheet as the output given that I typically build my financial models as one worksheet models this is not an issue but it may be in some other cases there are two types of model integration direct and indirect in this course I'll cover both it's important that we think about the formula that you're about to sensitize and where the linking occurs this is the difference between direct and indirect methods in the direct method you are changing an input that is linked up in the financial model itself since Excel can only change inputs and not formulas you need to use an indirect method if you want to sensitize a formula further down in the model I'll explain this in more detail as we continue in the course let's start with a direct method of model integration in this approach we will pick an input or driver that we want to sensitize this is something that is hardcoded for example the revenue growth assumption in your 2016 next we need to pick a range of sensitivity for this input is it something that that typically varies by 1 or 2% a year say 10 or 20% a year it's important to pick an appropriate amount of sensitivity for the input thirdly we need to think about what the output we want to measure is in this case we're going to measure the impact on share price based on changes in Revenue growth and finally you link up your table I'll walk you through in a step-by-step example exactly how you link up your data table in Excel please open up the Excel file that you downloaded as part of the paid version of this course let's take a second to quickly get oriented in this financial model before we start building our sensitivity analysis I typically organize my models into sections as you can see here with assumptions income statement balance sheet cash flow supporting schedule modes the DCF model evaluation and finally sensitivity analysis this is the section we're going to complete in this course today if we expand the entire model we can quickly scroll through and see how this model is built there are two scenarios that perform a live case which builds up an income statement a balance sheet Cash Flow State statement supporting schedules and a DCF model this allows us to calculate the equity value per share of this hypothetical business once that part of the model is complete we can finally build our sensitivity analysis below let's start by building a data table with the direct method of sensitivity analysis as discussed earlier the direct method links to inputs and changes them to show you different outputs in this case the output that we want to sensitize is the equity value per share please link that into this cell here I had colored it white for formatting purposes I'll leave it black for a moment so we can understand where it's linking now across this part of the data table we need to pick different Revenue growth rate assumptions on the other side I've decided to sensitize the ibida exit multiple of this business which determines the terminal value I think that a reasonable assumption for Revenue growth rates could be a range from 0% growth to 20% growth the model is currently running at 10 let's start by typing in in 0 we're then going to make a formula that takes 0 plus 5% which we copy across and fill right on the other side of the table I'm going to play with the iida exit multiple if we scroll up here we will see that the terminal value of this business is based on an EV to Eid exit multiple of eight times let's sensitize a range of e EV to e. exit multiples between six times and 10 times with eight being in the middle I've structured this data table so that the base case Assumption of 10% Revenue growth and 8 time EV to ea. exit multiple hits the exact center of the data table to quickly review let's scroll up and and see where the EV to eida multiple is located which is right here let's also scroll up and see where the revenue growth figure is located which is right here these are the two cells that we are going to link into the data table with those two cells LinkedIn Excel then performs an analysis of substituting in these values we've put here in place of the original hardcoded values now that we have the data table set up to plug in different Revenue growth rate assumptions and different EV to ebaa exit multiple assumptions let's actually link this up as you recall the output that we're going to sensitize is share price the inputs that are going to flow through the model are Revenue growth which is up here in our assumptions and exit multiple which is down here in the DCF assumptions in order to fill in the table we highlight the entire area using our shortcuts we pull up the data table input area the row that we want to sensitize is revenue across the top here the column is exit multiple so let's link the revenue figure first recall that it's located up at the top in the key assumptions area next we'll link the column which is the exit multiple and then we'll press okay if we scroll down to our table we look and see that it's working because at 8 times exit multiple and 10% growth we have a share price of $34.85 which is the current case that's running in the model we can then see intuitively that as Revenue growth increases the share prices are getting higher from left to right we can also see that as the exit multiple increases from top to bottom share prices are getting higher still the bottom right corner is the highest result top left is the lowest we can reformat this cell back to a white font since we don't need to see it displayed there congratulations you now have a properly functioning data table sensitivity analysis on your share price let's take a minute to talk about the indirect method of sensitivity analysis this differs from the direct method in that we can now pick a formula that we want to sensitize as opposed to a direct hardcode input as in the previous example this allows a lot more flexibility performing sensitivity analysis and you can literally layer it on top of any part of the model what we need to do is create a zero value hardcoded cell this allows us to take a hard code and put it into a formula thereby allowing us to sensitize it this is sort of a workaround that allows you to bypass excel's inability to sensitize formulas as in the other example pick a range of sensitivity for the output say plus or minus 10% and then pick the outputs that you want to see the impact on so we'll stick with the same example of share price finally you link up the data tables same as in the previous example and I'll walk through that with you right now now it's time to link up data tables using the indirect method of sensitivity analysis this differs from the direct method which we did previously in that we will now be editing formulas as opposed to just changing hard codes Excel can only produce sensitivity analysis on hard codes however so we need to create a separate section below where we will have a hardcoded input for each variable that feeds into a live formula let's start by putting zero values in each of these categories we're then going to do a plus or minus in this case with Revenue plus or minus 5% variability for cost of goods sold will be similar the discount rate being a little less volatile we will sensitize as plus or minus 1% and finally the exit multiple will be plus or minus one times now that we have each of these tables set up let's link them into the formulas above in the case of Revenue growth you will recall that the formula is located up in the income statement let's alter the revenue growth formula which is currently equal to the previous year * 1 + the growth rate we now want it to be equal to the previous year Time 1 plus the growth rate plus the sensitivity analysis which in this case is set to zero so it won't change the base case operating model once I have it linked up and I've angered the cell I can now select all the cells to the right and press crlr to copy it across I now see that it's linked up properly the same thing is done with cost of goods sold it's equal to revenue multiplied by the cost of good sold assumption I will now add plus the sensitivity analysis variable once again set to zero so it doesn't impact the financial model I select that and fill right scrolling back down to the tables let's set up the discount rate now the discount rate fills in to the intrinsic value calculation it's based on a hard-coded discount rate assumption above I want to take that assumption and add to it our little sensitivity analysis change here in this way I've indirectly impacted the NP calculation and finally exit multiple exit multiple drives the terminal growth value rather terminal value which I can add some sensitivity to by linking it to this cell I now have all four of these inputs as indirectly influencing the form formulas in the financial model now that the indirect sensitivity tables are set up let's link them we need to decide what we want to sensitize in terms of an output in this case it's going to be the same as the data table above where we sensitize share price let's copy the share price over into this cell on each table the way these tables are set up is that the hardcoded input is on the left and the output is on the right we can select the area of the table here and use the shortcut alt a WT to input a table in this case the column is what we want to substitute out or sensitize so we link the column to the input cell if done correctly we can look at the sensit sensitivity table above and make sure it matches as you see when Revenue growth decreases by 5% from the original forecast the share price is $281 this ties up with above conversely when Revenue growth is 5% above forecast or 15% we get a share price of 42 $283 the same as in this table so we know it's working correctly now let's select the area for cost of goods sold and Link it up in the same fashion now discount rate and finally exit multiple again with the exit multiple changes I can look at the table above and see that when the exit multiple decreases from 8 to 7 I get a share price of $31.97 which ties here and when it increases to nine times I get a price of $37.79 the same as here so I now have confidence that these are all correctly Linked UP now that we have our sensitivity analysis properly hooked up let's talk about how to analyze the results it's important to think about which inputs the model is most sensitive to and also think about expected volatility not all inputs will vary equally some will experience much more highs and lows than others and it's important to think about the appropriate amount of sensitivity to build in we should develop a range of outcomes based on different scenarios and probabilities one way to do this is to create what's called a gravity sort table a gravity sort table takes all of the inputs and organizes them based on formulas it then allows us to populate a tornado chart the tornado chart illustrates very clearly how much the share price will move in this example based on changes on inputs and drivers all of this combined will allow us to communicate the results in a very clear and effective way let's talk about how to build a gravity sort table which will allow us to automatically illustrate which sensitivities have the biggest impact and which have the smallest so a question would be how could you automatically have a table update these results well you could use the small or large functions in Excel which rank a series of items based on the nth smallest or nth biggest in a list we can combine that with the index and match functions to sort the table and produce the output that we want we'll walk through this in great detail together and I'll show you step by step how to build it in this section we're going to build a gravity sort table together I'll walk you through each of the steps to fill in this entire table we're going to start by linking each of the assumptions that are sensitized above I've done this one step in advance for you next we're going to calculate what the change is relative to the original input so take the ending value divided by the beginning value minus one let's do this for each of the sensitivity analysis pieces and as you'll see these are not sorted in order from most variable to least variable in fact they're sort of in a random order what we want to do here is have them automatically sort the next step that I'm going to take is to express each of these changes in positive or absolute terms so I'm simply going to type a formula that equals ABS and the number to the left of it this expresses the number in absolute terms I'm now going to ask Excel to rank each of these items I'm going to start by typing in the rank column at one and increasing down to the bottom of the list so now Excel knows I want to see the order of ranking as 1 2 3 4 but in terms of output I want them to actually rank the smallest first all the way up to the largest so I'm going to use the small function to use the small function I select the area that I wanted to choose from which I lock with F4 and and I want it to then return the K largest in that array close bracket once properly linked up I can select this area below and press contrl d by checking the formulas I now see that it is clearly ranked the smallest as being 4% and the highest as 19% now that I have the outputs ranked from smallest to largest I need to reproduce the corresponding assumption name in order to do that I'm going to pull information from the database using index and match functions the index function turns a table into a sort of database so I select first the area of the corresponding database that I want to pull information from in this case it's all the information to the left of the table I will lock it in place I then want to retrieve from that information the output that corresponds with the potential outputs so I'm asking it to search for in this case 4% within the range of possible outcomes which I lock into place after pressing comma again I select zero because I want to find an exact math match once I've found the output within this range I need to produce the Assumption name so I'm going to ask Excel to match once again the Assumption name which I lock into place as a column relative to the other columns which I will also lock in place and again I want an exact match after closing all the brackets I can press enter and in this case I am produced with the discount rate which it turns out is the smallest assumption if done correctly I can select the area below and press contrl D to fill down let's check that I did the formula correctly EV to eida should be the second smallest that's correct with cost of goods sold being the third correct and finally Revenue growth as being the greatest so we can see that this is properly linked up now in terms of creating the data that will populate the graph I first want to show the positive impact of the change in driver so I simply relink to this column on the left and then want to show the inverse of that for the negative side of the graph where I simply take the negative of that number there I've now populated the gravity sort table and we're ready to make our tornado chart now that the gravity sort table has been populated we can build our tornado chart a tornado chart is a great way to show sensitivity analysis in a presentation it clearly illustrates which are the most impactful and least impactful drivers of the financial model it's very easy for people to understand even if they have not spent time in the model excellent for presentations and great output I highly recommend using tornado charts now that we've populated the gravity sort table with the information for the tornado chart you'll see that it automatically fills in for you let's work through how to build this chart together I'm going to delete it and we'll recreate it on the top ribbon here I'm going to go to the insert section the type of chart that I need to choose is a stacked horizontal bar chart I'm going to rightclick and then select data I'm going to add the first series as being positive and select the positive values I'll then add the negative column has the negative values finally the category access label are these driver names that we've sorted here and click okay next thing I'm going to do is right click on the axis and change the labels such that they're no longer next to axis but in the low position this moves them to the left where they're easier to see you may want to format the negative side of the tornado chart as one color and the positive as another or you may wish to format them both as being the same let's try them both as being the same and you can see which you prefer for me personally I usually present them like this as being the same color on both side as you'll see this is a very easy way to understand how the model works if Revenue growth is 5% higher or lower than forecasted in the model model the share price could move by up to 20% in either direction if the cost of good sold margin moves by 5% share price could be up or down about 12 if the EV eida me exit multiple moves by one times that would result in nearly a 10% change in share price and finally if the discount rate increases by a percent or reduces by a percent we're going to see a small impact this is a great way to visualize what we need to focus on in the financial model now that we've completed all of our analysis in the financial model it's time to present our results in a presentation clear presentation of results is critical to being a good financial analyst and will set you apart from your Pier use a combination of tables and charts tables are great because they contain a lot of detail charts are great because they easily illustrate the main points so a combination of the two is very important show a range of values and outcomes remember there is not one future share price of a company that we were trying to narrow in on but rather a range of possible outcomes be sure to discuss the relative variability of each of the inputs not all input puts or drivers will range as much as others will it's important to factor that in when thinking about how sensitive the model is to say Revenue growth versus discount rate let's take a minute to review what we've covered in this course once we've completed our financial model from assumptions all the way down to a DCF analysis we can perform sensitivity analysis this is always done at the end and layered on top of the model the first type of sensitivity analysis We performed was the direct method where we sensitized the equity share price based on changes in Revenue growth and exit eida multiples we did this by sensitizing the direct inputs that are in the model like the exit multiple and the revenue growth this is the most common type of sensitivity analysis and it typically forms a data table next we performed an indirect method of sensitivity analysis this method allowed us to edit formulas in the model and sensitize those formulas what we did was we created drivers down here for each formula we wanted to impact we then went up to the model and edited this formula to include the hardcoded driver down below the same was done for cost of goods sold once we had these in place we could create a gravity sort table this table here took each assumption the change the absolute change and then rank them from smallest to largest we use the index and match functions to automatically output the name of the driver that we sorted this way we can change assumptions and inputs in the model and never have to worry about this table ever being out of date in order to populate the tornado chart we have a positive column of change and a negative column of change we then use a stacked bar chart to create this beautiful output this is a powerful tool for sensitivity analysis and if you take the learnings from this course and apply them in a role as a financial analyst in either Investment Banking equity research Financial Planning and Analysis you will have a great leg up against the competition this course built on our previous learnings of how to build a financial model in Excel if you found this course challenging please refer back to our fundamentals of financial modeling course and building a model in Excel course as we will go through the buildup of the entire model in full detail thanks for taking this course and we hope to see you again soon