DCF – Sensitizing for Key Variables
January 5, 2024
What is Sensitizing DCF Analysis for Key Variables?
A discounted cash flow (DCF) analysis is highly sensitive to key variables such as the long-term growth rate (in the growth perpetuity version of the terminal value) and the weighted average cost of capital (WACC). As a result, it is important to sensitize the output for these key variables to provide a valuation range. Sensitizing growth can cause problems of its own. Understanding these problems will help understand terminal value better and create better DCF valuations.
Sensitivity tables allow for a range of values to be quickly calculated and can be built manually or using Excel’s data table functionality. This blog has two parts:
- The first looks at common pitfalls in Excel data table creation, assuming pre-existing knowledge of building basic data tables.
- The second is a set of instructions to show you how to build basic data tables.
Key Learning Points
- DCF analysis is highly sensitive to some of the key variables such as the long-term growth rate (in the growth perpetuity version of the terminal value) and the WACC.
- The output of DCF analysis should be sensitized for key variables to provide a valuation range.
- Sensitizing key variables help to understand the sensitivity of the DCF model to key assumptions.
- Sensitivity tables can be built manually or using Excel’s data table functionality. Data tables allow dynamic sensitivities to multiple assumptions.
- Data tables have some common pitfalls which may lead to incorrect outputs, or too big a valuation range.
- The choice of how to do your DCF has impact on the data table. If a growth perpetuity is used it has a subtle, but important impact on data tables. This argues for the use of the value driver approach to terminal value.
The Need for Sensitizing
A DCF analysis usually calculates the enterprise value of a business as the present value of its forecasted free cash flows (see our blogs on DCF and free cashflows for more details on these subjects). DCF is seen as a very strong valuation technique. One of the few criticisms levelled at the method is that DCF valuation is very dependent on key assumptions. Even small changes produce large value variations. Sensitizing key variables helps understand the sensitivity of the DCF model to key assumptions. It’s very common in corporate finance to add a data table to any output that has uncertain inputs. DCF is no exception, and most professionals working in valuation will create data tables as part of their job.
This Blog: Two Parts
The first part of the blog will assume you know how to create a data table and want to read about some of the common pitfalls so you can avoid them. If you want info (or a refresher) on how to create a data table, skip down to ‘Part Two: How to create a data table’ further down this page.
Data Tables: Common Pitfalls
The below image shows a data table and its parts. It also highlights some of the common problem areas. Each of these areas (and others) are discussed in more detail in the sections that follow.
The data table will happily accept the equation it’s working with (located in the top left of a two-variable table) from another sheet. However, it won’t take the variables from another sheet. If you try to point it at variables on another sheet it will produce an error.
Solution: Build the data table on the same sheet as the assumptions you’re sensitizing.
The table won’t work! It displays only 0s.
Excel’s calculation setting is often created to exclude data tables. It’s common for analysts to turn off data table calculation, especially when working on big models. Data tables can be power-hungry. A spreadsheet with six or more data tables will become unwieldy and may freeze up for a while every time you introduce new data. A solution is to set excel calculations (Alt M X) to exclude data tables. It’s easy to forget you’ve done this, and then worry about your data table seemingly doing nothing and only showing 0s.
Solution: Check you have calculation set to “include data tables” using Alt M X when you build your table.
The table won’t work! It displays results, but I know they’re wrong!
Commonly, the ‘middle’ variable (in the top row or left-hand column of the data table) is set as the same number as your assumption in the model, for instance the WACC of 5% in our picture above, and the variables around that are small deviations from the 5%. You may have set the ‘middle’ variable as a linked cell. It’s tempting to link the middle variable to the assumptions in your model, because if you change your mind on the basic assumption in your model your data table will update automatically. The problem is that you’ve created a circularity by doing this. When excel runs the data table it changes the variable in the model, and this will filter through to your table, then back to the model in a loop.
Solution: Hard code the ‘middle’ variable.
The table works, but the results look slightly ‘off’!
If you’ve dutifully hard-coded the ‘middle’ variable, you may find that the ‘middle’ output is slightly different from the output of your DCF. This is disconcerting because if the ‘middle’ variables are the same as your model, then surely the result should be same? It’s likely some of your assumptions are calculations that are rounded, hiding detail.
The WACC is a good example. The WACC you used in the model may be calculated by Excel, and be displaying as 8.9%, but may be 8.8812%. Excel will only show you what you’ve told it to show in the style, so one decimal place in the example above. If you then program your ‘middle’ variable as 8.9%, you’ve slightly altered your variable.
Solution: This problem leads analysts to want to link the ‘middle’ variable to the assumption in the model, which then causes the problem discussed earlier. Pasting the value in from the assumption (not linking), which brings with it all decimal places, is a better solution.
The table works, but the valuation range is huge considering how small my increments are.
If you’ve got your data table working and have a range of outputs, you may find they cause a huge change in value. For instance, with a valuation model such as a DCF, our table has a massive difference between the smallest and largest valuation figures. This may be revealing an issue created by the most common way of calculating the terminal value, a growth perpetuity. Changing growth may be creating value ‘for free’ by not updating all the things that are needed to create growth, such as CAPEX in the free cashflows. For more detail on the way free cashflows interact subtly with discounting see our blog on terminal value complexities.
Solution: Use the value driver method to create terminal value. The value driver method is complex and has its own blog entry as part of the terminal value complexities blog mentioned above.
How to Create a Data Table
Below we have the inputs used in a DCF and the resulting enterprise value. In it, the WACC and long-term growth rate assumptions are the variables being sensitized. Attached to this blog we have included the full DCF model, as well as an empty template. Try working on the empty template as you read. It will help you relate the method to the reality of creating a data table.
The WACC assumption has been calculated and the long-term growth rate has been provided as a fixed assumption.
Sensitivity tables can be built manually or using Excel’s data table functionality. The data table functionality in Excel is memory intensive so often analysts will turn Excel’s calculation setting to ‘Automatic except for data tables.’ If you use this calculation setting you can still force Excel to calculate the data tables by pressing the F9 key.
We use the data table feature of Microsoft Excel to perform this sensitivity analysis. In a two-way data table, the information is organized as follows:
The Top Left Output Cell
link reference to the output cell
The figure being sensitized (in this case the enterprise value) is entered in cell C49. This is a link to the output cell in the DCF model. In this case, the Enterprise Value is in cell E40 (i.e. the formula in cell C49 is =C40).
The Variables
The variable growth has been entered in a row (D49:H49) while the variable WACC has been inserted in a column (C50:C54) of the table. The values in cells F49 and C52 are hardcoded and are the mid points. These growth and WACC values in F49 and C52 are the same as those values used in the model but are never linked to the model – just hardcoded. The remaining incremental assumptions are added using a formula. While there is a workaround so you can use hard numbers in the assumptions of a data table it is not intuitive and not well used so we are not covering it here.
The Outputs
The area inside the table (D50:H54) is the answer area. Excel will substitute each variable combination into the formula being sensitized and return each possible answer in this area.
To create the data table, select the area which includes both the row and column variables (C50:H54). To activate the data table dialogue box, we use the keyboard shortcut Alt A W T (or click on the Data Ribbon/What If Analysis/Data Table).
The row input cell is the long-term growth rate assumption from cell E31 (the ‘row’ along the top of the data table), and the column input cell is the WACC figure from cell E28 (the ‘column’ along the left of the data table). An easy way to remember which reference to put into which cell in the dialogue box is that the top box (‘row input cell’ relates to the assumption being sensitized across the top of the data table.
Upon pressing OK, we get the following results of the calculation:
The bottom right corner of the table uses the highest long-term growth rate of 2.2%. When combined with the lowest WACC of 5.6%, it gives the highest enterprise value of 29,851.5.
The midpoint of using 2.0% as the long-term growth rate and 5.8% as the WACC gives the same enterprise value 27,065.0 as the model answer.
The top left of the table that combines the lowest growth rate of 1.8% and the highest WACC of 6.0% gives the lowest enterprise value of 24,804.3.
While this is an example, a variety of outputs can be sensitized for different variables. The findings can lead to valuable insights for analysts and investors. It’s preferable to build the data table so the lowest value is in the top left-hand corner, and the highest value is in the bottom right-hand corner.
As seen above, DCF valuations are highly sensitive to changes in input variables. There are a few very important issues here:
- If your data table isn’t behaving as expected, troubleshoot it using the ‘common pitfalls’ section at the start of this blog
- Avoid wide ranges in the underlying assumptions for the long-term growth rate and WACC. We would recommend not sensitizing these numbers by more than 0.5%
- Changing the growth rate in the terminal value assumption in most DCF models should make a corresponding adjustment to the amount of capital invested in net PP&E and working capital, but in most models, this is not linked up and this causes issues
- When we are changing the growth rate, we are sensitizing how much growth the business can support given an investment in net PP&E and working capital. This can lead to very wide valuation ranges
- If you want to use a wider range of growth sensitivities, we suggest you use the value driver formula for the terminal value. More detail on how to calculate value driver is in this blog on terminal value complexities
The images in this section are snapshots of a DCF model. Download the file from the link for a full breakdown of the DCF model used.
Conclusion
Many tasks financial professionals undertake are subject to uncertainty. To explore that uncertainty specialised tools are needed. One of the most useful of these is a data table. Being able to reliably create error-free data tables is a key skill.