How do you test the sensitivity or robustness of your CAPM valuation results?

1 What is sensitivity analysis?

Sensitivity analysis is a technique that helps you measure how much your valuation results change when you vary one or more of the input parameters. For example, you can see how the cost of equity and the value of the company change when you increase or decrease the risk-free rate, the market return, or the beta by a certain percentage or amount. Sensitivity analysis can help you identify the most critical and uncertain inputs, as well as the range of possible values for your valuation.

2 How to perform sensitivity analysis?

There are different ways to perform sensitivity analysis, depending on the level of detail and complexity you want to achieve. One of the simplest and most common methods is to use a data table in Excel. A data table allows you to create a matrix of values for your valuation results based on different combinations of input parameters. For example, you can create a data table that shows the cost of equity and the value of the company for different risk-free rates and betas. To create a data table, you need to set up your CAPM formula in a cell, and then select the cell and the range of values for the input parameters. Then, go to Data > What-If Analysis > Data Table and specify the row input and the column input.

3 What are the limitations of sensitivity analysis?

While sensitivity analysis can provide useful insights into your valuation results, it also has some limitations that you need to be aware of. One of the main limitations is that it only varies one or two input parameters at a time, while holding the others constant. This means that it does not capture the interactions and correlations between the input parameters, which might affect your valuation results in reality. For example, the risk-free rate and the market return might move together in response to changes in the economic environment, but sensitivity analysis does not reflect that. Another limitation is that sensitivity analysis does not assign probabilities or weights to the different scenarios, which means that it does not reflect the likelihood or the expected value of your valuation results.

4 What is scenario analysis?

Scenario analysis is another technique that helps you test the robustness of your CAPM valuation results. Scenario analysis involves creating different scenarios that reflect different assumptions about the input parameters, based on your judgment or historical data. For example, you can create a base case scenario that uses the current or average values of the input parameters, a best case scenario that uses the most optimistic values, and a worst case scenario that uses the most pessimistic values. Scenario analysis can help you evaluate the impact of different events or situations on your valuation results, as well as the potential upside and downside risks.

5 How to perform scenario analysis?

To perform scenario analysis, you need to define the scenarios and the values of the input parameters for each scenario. You can use Excel's Scenario Manager tool to create and manage your scenarios. To use Scenario Manager, go to Data > What-If Analysis > Scenario Manager and click Add to create a new scenario. Give your scenario a name and specify the cells that contain the input parameters that you want to change. Then, enter the values for each input parameter for that scenario. Repeat this process for each scenario that you want to create. Then, you can view and compare your scenarios by clicking Show or Summary in Scenario Manager.

6 What are the benefits of scenario analysis?

Scenario analysis has some advantages over sensitivity analysis that make it more realistic and comprehensive. One of the benefits is that it allows you to vary multiple input parameters at the same time, which captures the interactions and correlations between them. For example, you can create a scenario that reflects a high-growth and low-risk environment, where the risk-free rate, the market return, and the beta are all low. Another benefit is that it allows you to assign probabilities or weights to each scenario, which reflects the likelihood or the expected value of your valuation results. For example, you can assign a 50% probability to the base case scenario, a 25% probability to the best case scenario, and a 25% probability to the worst case scenario. Then, you can calculate the weighted average of your valuation results across the scenarios.