How to Perform Sensitivity Analysis on Excel?

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?

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.