In this article, I will show you how to perform scenario and sensitivity analyses for effective stock valuation. Scenario analysis evaluates stock valuation under different future conditions by changing key factors such as growth rates, demonstrating how conservative, base, and optimistic growth scenarios can affect valuations. Meanwhile, sensitivity analysis examines the impact of changes in important variables on stock value, highlighting the sensitivities of these inputs.
Using scenario and sensitivity analysis in financial models helps to map out a range of potential outcomes for stock values. This article will take a closer look at these analyses, show how to apply them in valuation models, and guide you through interpreting the results. Understanding these methods will improve your ability to make informed investment decisions.
Understanding Scenario and Sensitivity Analysis in Stock Valuation
Scenario and sensitivity analysis are financial modeling techniques that offer insights into the potential impacts of varying assumptions on stock valuations. These methods and their significance are described in the sections below.
Scenario Analysis
Scenario analysis in stock valuation explores the impact of varying assumptions on a company's worth by modeling outcomes from best to worst case. It adjusts key factors such as free cash flow (FCF) growth rates, while keeping all other assumptions constant, to project different valuation scenarios. In short, scenario analysis is important because it accounts for uncertainties inherent in forecasting, enabling investors to understand the range of potential valuations.
Sensitivity Analysis
Sensitivity analysis is used to assess how a stock's valuation can be affected by different sources of uncertainty in the model's inputs. It specifically examines how changes in key assumptions, like discount rates, terminal growth rates, and the margin of safety, affect the overall valuation of a stock.
Sensitivity analysis is important because stock valuations are inherently based on assumptions, and some key inputs may be more sensitive than others. Even small changes in these inputs could lead to a significantly different valuation. Therefore, performing a sensitivity analysis can help investors determine which variables have the most impact on their valuation, leading to more informed valuations.
Scenario Analysis vs. Sensitivity Analysis
While both scenario and sensitivity analysis are important for effective stock valuation, they serve different purposes and offer unique insights.
Scenario analysis provides a broader view by considering a range of possible future states and their impact on valuation, thus preparing investors for various company and market conditions.
Sensitivity analysis, in contrast, focuses on the impact of individual variables on the valuation, offering a detailed understanding of how specific changes in assumptions can alter the stock's estimated worth.
Together, scenario and sensitivity analysis improve the accuracy and reliability of valuation models.
Standard DCF Valuation Model
In this article, we'll build a simplified discounted cash flow (DCF) valuation model for The Coca-Cola Company (KO), a global beverage leader, to demonstrate how to perform scenario and sensitivity analyses. The DCF model is a method used to value a company by estimating the present value of its expected future cash flows, using a discount rate to account for the risk and the time value of money. Familiarity with this model is recommended, although not required, for our sensitivity analysis example.
The DCF models described in this article can be accessed by downloading the file linked below:
The DCF model's inputs and assumptions for the case study presented in this article are detailed below:
- Number of Periods: 5 years
- FCF0: $9,821
- FCF Growth: 5.0%
- Discount Rate: 10.0%
- Terminal Value Method: Gordon Growth Model (GGM); with 2% terminal growth rate.
- Margin of Safety: 15.0%
For the company's current free cash flow (FCF0), for simplification purposes, we calculate simple FCF by subtracting the company's total capital expenditures (CapEx, also known as PP&E) from its cash from operations (for FY 2023). This calculation yields $9,821 ($11,599 - $1,778). We then project this FCF to grow at an annual rate of 5% over a 5-year forecast period, a reasonable assumption considering it is situated between the 5-year CAGR of ~10% and the 9-year CAGR of ~2%.
The 10% discount rate represents the required rate of return we're targeting for investing in Coca-Cola, with a 15% margin of safety. This relatively low margin of safety is justified by the size and stability of Coca-Cola's future cash flows. The adoption of a 2% terminal growth rate is also reasonable, as it is lower than the discount rate and below the nominal growth rate of the economy in which the firm operates.
Here's how the completed DCF model looks for Coca-Cola (without any scenario or sensitivity analysis):
Assuming our assumptions are sound, this DCF model provides a solid framework. It allows for adjustments in input values to reflect different outcomes based on our assumptions. However, it limits us to observing the effects of changing one input at a time. For instance, if we want to assess the impact of an 8% FCF growth rate, we would change the rate from 5% to 8% in the "FCF Growth Rate" cell. Yet, this change hinders our ability to view our base case or a more conservative scenario concurrently, making it challenging to evaluate the model's sensitivity to this input and its significance on the stock's valuation.
Scenario analyses address this limitation by allowing the simultaneous comparison of multiple scenarios. The next section will explain how to incorporate scenario analysis into the DCF model.
DCF Valuation Model With Scenario Analysis
Scenario analysis enhances the DCF model by including conservative, base, and optimistic scenarios, instead of relying on a single scenario like the conventional DCF approach. Typically, key inputs like FCF growth are modeled out in a scenario analysis to help investors understand how different growth rates affect the valuation, revealing the range of potential outcomes.
The only downside to scenario analysis is that it makes the model slightly more complex, as demonstrated below:
As you can see, the DCF model now includes new rows, incorporating three input cells for FCF growth, three lines for forecasted FCF, and present value calculations for each scenario. These additions impact the calculated enterprise values and equity values, along with the range of intrinsic share prices and buy price ranges.
With this scenario analysis in place, investors can better understand how different scenarios (conservative, base, and optimistic) might unfold. Its value lies in navigating the uncertainty of the future by modeling the company's worst, moderate, and best-case scenarios. Additionally, this method is useful as it shows how variations in the company's forecasted FCF growth rates can influence its implied buy prices, while all other variables remain constant.
Although this model represents an improvement, it still does not show how variations in key inputs like the discount rate, terminal growth rate, and margin of safety affect the company's valuation. To address this, it's recommended to build one or more sensitivity data tables using Excel's "what-if analysis" data table functionality, as described in the next section.
DCF Valuation Model With Scenario and Sensitivity Analysis
Incorporating sensitivity analysis into the DCF valuation model enhances its utility by detailing how various inputs influence valuation outcomes. The concept behind these data tables is that you'll compare how incremental changes to one key input (i.e., discount rates) and another key input (i.e., terminal growth rates) affect the company's intrinsic share price or buy price valuations. This enables you to understand the sensitivity of these key inputs and a range of applicable buy prices, without making the model overly complex. The section below will discuss setting up these data tables in Excel.
How to Setup Sensitivity Analysis Data Tables in Excel
Begin by building a complete and functional valuation model. This means the model should be dynamic, with changeable key inputs such as the discount rate, terminal growth rate, and margin of safety, which should not be embedded within formulas but rather referenced. In our case, this step has already been completed.
Next, designate a space for your data table, ideally making it 7 cells wide and 7 cells tall to accommodate a wide range of buy prices and input labels.
Then, decide on the key sensitivity analysis inputs to change incrementally. There will be two inputs for every data table. In our example, we'll build two sensitivity data tables. The first will compare the discount rate to the terminal growth rate, and the second will compare the discount rate to the margin of safety. Both tables will assess how changes to these rates affect the range of buy prices for Coca-Cola.
Finally, build the templates for the sensitivity analysis data table. The top left cell of the data table should reference the company's intrinsic share price or buy price, which, for our base case buy price, is $21.78.
The image below shows the blank sensitivity analysis data table templates:
After setting up the data table templates, input your model's current key sensitivity analysis inputs. In our case, we'd enter a discount rate of 10% into the discount rate cell and 2% into the terminal growth rate cell. Additionally, we'd enter 15% into the second data table as our margin of safety.
Next, decide on the incremental changes for your discount rate, terminal growth rate, and margin of safety, considering the model's assumptions and its sensitivity. A reasonable adjustment could be a change of 0.25% for the terminal growth rate, 0.50% for the discount rate, and 2.50% for the margin of safety.
Here's how these next two steps appear for the data tables:
To configure the data table, highlight the area that includes both sets of rates. Then navigate to "Data" --> "What-If Analysis" --> "Data Table." In the dialog box that appears, specify the input for rows (terminal growth rate) and for columns (discount rate). After setting this up, click OK to execute the data table calculation. Repeat this process for the discount rate vs. margin of safety sensitivity analysis data table, but reference the margin of safety input cell for the rows. Completing this setup will show the impact of varying rates on the buy price.
For automatic updates, set Excel's calculation mode to "Automatic" under the "Formulas" tab. If your calculation settings are on "Automatic Except for Data Tables" (which is recommended for optimal spreadsheet performance) or "Manual," use the "Calculate Now" or "Calculate Sheet" buttons instead to execute the data tables.
The image below demonstrates how to set up the data table for comparing the discount rate vs. the terminal growth rate:
After running the data table calculations, the next step is to thoroughly review the results in the sensitivity analysis data tables. Focus on understanding how different combinations of the discount rate and terminal growth rate (or margin of safety) influence the stock's valuation. This analysis will reveal how variations in these rates affect the suggested buy prices for the company, which may influence your decision to buy the stock at its current price or not.
Here's the completed Coca-Cola DCF model with scenario analysis and sensitivity analysis included:
Note that you can add conditional formatting to the sensitivity analysis tables to highlight buy prices below or above the company's current stock price in green or red, respectively. You can also include simple line charts, as shown in the image above, or create tornado charts to display the impact of changes to multiple variables at once, sorting by the most impactful change on the share price.
However, these steps are optional and not strictly required. Often, building a sensitivity analysis data table with a toggle to switch between conservative, base, and optimistic scenarios is sufficient.
The Bottom Line
Scenario and sensitivity analysis are key in financial modeling for stock valuations. Scenario analysis helps investors see how different future situations, like best and worst-case scenarios, might affect a stock's value. It involves adjusting the model to include these different scenarios. Sensitivity analysis uses Excel data tables to show how changes in core assumptions, such as discount rates, terminal growth rates, and the margin of safety, impact the stock's value, highlighting which factors are most important.
These methods are important because they help investors understand the potential risks and outcomes of their investments by illustrating how changes can affect stock values. By employing scenario and sensitivity analysis, investors can make more informed investment decisions, supported by models that consider market unpredictability and offer a clearer perspective on possible future scenarios.