Sensitivity Analysis for Financial Modeling

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 rank 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 Vipond 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 a corporate side and 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 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 assumptions 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 the 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 hard-coded 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 typically varies by one or two percent a year say ten or twenty percent 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 schedules the DCF model and valuation 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 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 ebody exit multiple of this business which determines the terminal value I think that a reasonable assumption for revenue growth rates could be your range from zero percent growth to 20 percent growth the model is currently running at 10 let's start by typing in zero we're then going to make a formula that takes zero plus 5% which we copy across and fill right on the other side of the table I'm going to play with the ebody exit multiple if we scroll up here you will see that the terminal value of this business is based on an EVD bottom exit multiple of eight times let's sensitize range of eb e VD D beta exit multiples between six times and ten times with eight being in the middle I've structured this data table so that the base case assumption of 10% revenue growth and a times e V to e beta eggs and multiple hits the exact center of the data table to quickly review let's scroll up and root n see where the e V the e Badal 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 linked in Excel then perform some analysis of substituting in these values we've put here in place of the original hard-coded values now that we have the data table set up to plug in different revenue growth rate assumptions and different EVD budda 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 they're going to flow through the model our 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 is 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 ok if we scroll down to our table we look and see that it's working because at eight times exit multiple and ten percent growth we have a share price of $34 and 85 cents which is the current case that's running in the model you 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 eggs and 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 sell 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 hard-code input as in the previous example this allows a lot more flexibility in 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 hard-coded 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 excels 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 hard-coded input for each variable that feeds into a live formula let's start by putting 0 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 1 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 times 1 plus the growth rate we now wanted to be equal to the previous year times 1 plus the growth rate plus the sensitivity analysis which in this case is set to 0 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 ctrl R 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 goods 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 NPV 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 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 hard-coded 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 will link the column to the input cell if done correctly we can look at the sensitivity 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 $28 in 11 cents this ties up with above conversely when revenue growth is 5% above forecast or 15% we get a share price of $42 83 cents 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 eight to seven I get a share price of $31.90 which ties here and when it increases to nine times I get a price of $37 seventy two cents 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 enth smallest or enth 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 a BS 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 an increasing down to the bottom of the list so now Excel knows I want to see the order of ranking as one two three four 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 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 control 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 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 press roldy to fill down let's check that I did the formula correctly Evy de Buda 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 re linked to this column on the left and I 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 right click 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 as the negative values finally the category axis label are these driver names that we've sorted here and click OK next thing I'm going to do is right click on the axis and change the labels such that they are 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 the share price could move by up to 20% in either direction if the cost of goods sold margin moves by 5% share price could be up or down about 12 if the EVD BMX eggs and multiple moves by one times that would result in nearly a 10 percent 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 a 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 peers 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're 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 inputs 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 eBay done 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 hard-coded 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 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 build up of the entire model in full detail thanks for taking this course and we hope to see you again soon

Channel: Corporate Finance Institute

Views: 320,955

Rating: 4.9402428 out of 5

Keywords: sensitivity analysis, sensitivity analysis for financial modeling, financial modeling, how to do sensitivity analysis in excel, how to perform sensitivity analysis in excel, financial planning & analysis, financial model, corporate finance, finance training, finance education

Id: eMuSbHkFIRU

Length: 34min 16sec (2056 seconds)

Published: Mon May 30 2016