Performing sensitivity analysis on Excel is a very important task if you work in finance. When working with models on Excel, analysts often have to factor in both historical and projected financial inputs. In addition, they have to formulate a lot of assumptions regarding operational performance metrics. With a lot of assumptions and projections, being able to test out different possibilities really helps diversify the risks, and that is when the sensitivity table comes into play. Sensitivity analysis is often performed on Excel, at the end of every financial model. Let’s first see what a sensitivity analysis on Excel looks like!
1. How Does a Sensitivity Analysis Work?
Sensitivity (or What-if) analysis in Excel helps us to deal with the uncertainty coming from having to predict, project, and assume different input variables. It allows users to observe different output possibilities given 1 to 2 variables or assumptions.
A simplified model below will help you better understand how it works.
The model’s output is to estimate the net profit after tax in 2021 (C7) based on the profit in 2020 (C3), the profit growth rate (C4), and the effective tax rate (C5). Next, the sensitivity table tests different scenarios of the output, which is net profit after tax in 2021 in this case, with a range of growth rate (10% – 30%) and a range of tax rate (3.4% – 5.8%).
2. How to Construct the Matrix?
Reference the formula that produces the output cell at the top left corner of the matrix. Call this cell the “focus cell”
Type a list of input values in the same column under the focus cell. In this example, we have a list of values for growth rates.
Type a list of input values in the same row right next to the focus cell. In this example, we have a list of values of effective tax rates.
Select the range of cells that contain the focus cell and the two lists of input values. In this example, we would select the range D11:I18.
Go to Data -> What-if Analysis -> Data Table. A data table box would pop up like this:
For the row input cell, put in the reference cell for the input values you put in the row (C5). Likewise, for the column input cell, put in the reference cell for the input values you put in the column (C4) and click OK. Now you have a fully developed sensitivity table.
Note: Sometimes Excel might be slow or it might be set in the mode that does not automatically calculate data tables. In this case, hit F9 to recalculate the entire worksheet.
3. How to Perform Sensitivity Analysis on your DCF Model?
Please refer to this article to understand the fundamentals behind the DCF valuation method. For this example, we will just work on the finished model and will not explain any concepts.
For this example, we will use the DCF model for Kellogg’s, a multinational food-manufacturing company based in the United States. We will walk you through how to test out different scenarios of implied equity value per share under both the perpetuity approach and the exit multiple approach.
Let’s say, you have built a finished DCF model to arrive at the equity value per share for Kellogg’s of $83.86 under the perpetuity approach and $72.49 under the exit multiple approach.
Now, for an analyst, your clients will be less interested in a definite answer. It is safer for you to give them a range of possible values because, as a matter of fact, your model is built on a lot of assumptions, and at least one of them might not be possibly 100% accurate. To mitigate the risk, your job as an analyst is to sensitize certain assumptions in the model. For a DCF model specifically, your assumptions are most uncertain on WACC, long-term growth rate, and EBITDA multiple.
In this example, we make assumptions on the long-term growth rate based on information we found on macroeconomic factors, the industry, the company’s internal strengths in sustaining growth. For the EBITDA multiple, we take the median multiple from comparable companies analysis. Both of these approaches are highly risky and subjective. We cannot be sure if the numbers we input are correct or not. Therefore, sensitivity analysis is the crucial surefire next step for our model.
Following the five steps above, when we put WACC on the column and long-term growth rate on the row, we get the first sensitivity table:
Similarly, for the EBITDA multiple approach, we put WACC on the column and the exit multiple on the row. We have another sensitivity table:
In financial modelling, there is no definite answer to anything. Sensitivity analysis is therefore used to present a range of possibilities that you might not be able to arrive at concretely. In addition, clients would be very much more interested in having a broader view of all possible options rather than be restricted to one. We hope that you are able to construct a sensitivity table now that you are done reading the article. We believe that the process is relatively easy and you should not hesitate to include this analysis in any of your models it can possibly be included in.