Do you want to make better stock investments? If so, sensitivity analysis is a skill that you need to master. Sensitivity analysis allows you to measure the impact of different variables on your stock's value. In this guide, we will show you how to do sensitivity analysis in Excel. We will also provide some tips on how to use this information to make more informed investment decisions. Let's get started!
What is sensitivity analysis, and why is it important for stocks?
Sensitivity analysis is a tool that helps investors understand how different variables can impact the value of their investments. For example, sensitivity analysis in Excel can be used to measure the impact of changes in interest rates, inflation, or economic growth on the value of a stock. By understanding how these variables impact your investment, you can make more informed decisions about when to buy or sell.
Why is sensitivity analysis important for stocks?
Sensitivity analysis is essential for stocks because it allows investors to identify the risks and opportunities associated with their investments. For example, suppose you are considering investing in a stock sensitive to interest rate changes. In that case, you may want to wait to invest until after the interest rate environment has stabilized. On the other hand, if you are considering investing in a stock that is insensitive to changes in interest rates, you may want to buy the stock sooner rather than later.
Building a financial model
The first step to performing any sensitivity analysis is to build a financial model. A financial model is a tool that allows you to input different assumptions and see how they impact the value of your investment. You can build many financial models in Excel using a spreadsheet's built-in functionality.
There are many different ways to build a financial model in Excel. For our purposes, we will keep it simple, so the focus is on how to perform effective sensitivity analysis on a stock.
In our model, we will assume that we are only interested in maximizing the terminal value of a stock based on a PE multiple that we will apply sensitivity analysis on and the expected earnings growth over 5 years.
As you can see, the two variables that determine the terminal value of a stock are the assumed earnings growth per year and the PE multiple at the end of the 5-year holding period.
Now we can move on to perform the sensitivity analysis on Apple below.
How to do sensitivity analysis in Excel?
After you build your Excel financial model, the next step is to choose whether you want to do a one-variable table or a multiple variables table and follow the following steps for each.
How to do a one-variable sensitivity analysis in Excel?
1) Build your one variable table by entering different values you would like to test for. In this case, we will focus on analyzing the PE ratio and its effect on the terminal value calculation.
image historical data
Based on historical stock data obtained from Wisesheets, historically, the stock has traded at a range from 15 to 30 PE in the past 5 years. Thus the PE will be analyzed in increments of 5.
2) Highlight the cells you want to change in your sensitivity analysis. In our example, we will highlight cells B20 and C24.
2) Click the "Data" tab and then click "What-If Analysis."
3) Click "Data Table."
4) In the "Row Input Cell" box, type the cell reference of the variable you want to vary in your sensitivity analysis. In our example, we will type "B7" in the "Column Input Cell" box.
5) In the "Column Input Cell" box, type the cell reference of the variable you want to vary in your sensitivity analysis. In our example, we will type "B6" in the "Column Input Cell" box.
6) Click "OK."
As you can see, we have created a one-variable sensitivity analysis of Apple stock. We can see that if the expected PE increases from 15 to 20, the terminal value of the stock will increase by approximately 33%. Alternatively, if the expected PE increases from 20 to 25, the terminal value of the stock will decrease by approximately 25%.
How to do a two-variable sensitivity analysis table in Excel?
1) Build your multiple variables table by entering different values you would like to test. In this case, we will focus on analyzing the PE ratio and earnings growth rate and their effect on the terminal value calculation.
Based on historical data from Wisesheets, the annual earnings growth rate has ranged from 3% to 12% per year. Thus the earnings growth will be analyzed in increments of 3% while the pe ratio will remain the same as for the one variable table.
2) Highlight the cells you want to change in your sensitivity analysis. In our case, we will highlight cells B12:F16.
3) Click the "Data" tab and then click "What-If Analysis."
4) Click "Data Table."
5) In the "Row Input Cell" box, type the cell reference of the variable you want to vary in your sensitivity analysis. In our case, we will type "B7" in the "Row Input Cell" box.
6) In the "Column Input Cell" box, type the cell reference of the variable you want to vary in your sensitivity analysis. In our case, we will type "B6" in the "Column Input Cell" box.
7) Click "OK."
As you can see, we have created a two-variable sensitivity analysis of Apple stock. We can see that if the expected earnings growth rate increases from 3% to 6%, the terminal value of the stock will increase by approximately 2.9%. Alternatively, if the expected earnings growth rate decreases from 6% to 9%, the terminal value of the stock will decrease by approximately 2.8%.
On the other hand, if the expected PE increases from 15 to 20, the terminal value of the stock will increase by approximately 33%. Alternatively, if the expected PE increases from 20 to 25, the terminal value of the stock will decrease by approximately 25%.
This sensitivity analysis in Excel is helpful in understanding how different assumptions can impact the terminal value of a stock. It is important to note that sensitivity analysis is not limited to two variables, it can be performed with multiple variables as well.
Interpret the results of your sensitivity analysis
When performing a sensitivity analysis, it is important to interpret the results to make sound investment decisions. In our example, we can see that both the earnings growth rate and PE ratio significantly impact the terminal value of Apple stock.
Assuming a 9% earnings growth rate and a 25 PE ratio, we can expect the terminal value of Apple stock to be approximately $276.59. However, if we decrease the earnings growth rate to 6% and keep the PE ratio at 25, the terminal value of Apple stock decreases to $268.98.
Conversely, if we increase the PE ratio to 30 and keep the earnings growth rate at 12%, then the terminal value of Apple stock increases to $341.04.
Based on our sensitivity analysis, we can see that even a small change in either the earnings growth rate or PE ratio can significantly impact a stock's terminal value. Therefore, as an investor, it is important to be aware of how sensitive your investment is to changes in assumptions.
When interpreting the results of a sensitivity analysis, it is also important to consider the practicality of the different scenarios. For example, while it is possible that Apple stock could have a PE ratio of 20, it is not as likely that the earnings growth rate would decrease to 3%. Thus, when making investment decisions, it is important to use sensitivity analysis to help you understand how different assumptions can impact your investment and consider the practicality of the different scenarios.
Tips for improving your stock picks through sensitivity analysis
- Use a tool like Wisesheets to find reasonable historical ranges for the metrics on which you would like to perform sensitivity analysis.
- Consider the different scenarios' practicality when interpreting your sensitivity analysis results.
- Use different cases to assess the most likely case, best case, and worse case scenarios.
- Be aware of how sensitive your investment is to changes in assumptions.
- Use sensitivity analysis to find your expected margin of safety range.
Conclusion
Sensitivity analysis is a powerful tool that can be used to improve your investment decisions. By understanding how different assumptions impact your investment, you can make more informed decisions about which stocks to buy and when to sell them. Additionally, sensitivity analysis can help you assess the risk of your investment by understanding how sensitive it is to changes in assumptions. In conclusion, sensitivity analysis is a valuable tool that every investor should use to improve their stock-picking abilities.
Happy investing!
