Sensitivity Analysis: “What if” Analysis
A financial model is a great way to assess the performance of a business on both a historical and projected basis. It provides a way for the analyst to organize a business’s operations and analyze the results in both a “time-series” format (measuring the company’s performance against itself over time) and a “cross-sectional” format (measuring the company’s performance against industry peers).
Typically, once an analyst inputs both historical financial results and assumptions about future performance, he/she can then calculate and interpret various ratio analyses, and other operational performance metrics such as profit margins, inventory turnover, cash collections, leverage and interest coverage ratios, among others.
General Rule of Thumb in Sensitivity Analysis
A scenario manager allows the analyst to “stress-test” the financial results because the reality is that expectations can and usually do change over time.
In previous articles, we discussed the fact that these forward-looking assumptions may not always hold true, and that the use of a scenario manager is a great way to incorporate several performance possibilities into your financial model. This allows the analyst to “stress-test” the financial results because the reality is that expectations can and often do change over time. Because the future cannot be predicted with any certainty, it’s never a good idea to take your financial model’s results and claim, either to your boss or to your client, that the results are final.
So what can you do if the financial model’s results are not the final results? Isn’t that why you build a model in the first place — to get some clarity or answer as to the future performance of the business? Yes and no. The purpose of the financial model is to provide some insight into future performance, but there is no one correct answer. Clients and managing directors like to see a range of possible outcomes, and this is where the sensitivity analysis, or “what-if” analysis comes into play.
So the analysis can be carried with maximum 2 variables? How can we test if we want to try 3 variables (EBIT margin, revenue growth plus another variable)?
Thank you
Can the sensitivity analysis data table be on a different sheet from where the basic model is? When I try that, Excel gives me an error “Input cell reference is not valid.” However, the same data table works fine on the same sheet as the basic model. Thank you for… Read more »
Hi there, thanks for the resource!
I’m having some trouble getting the results. I used Alt A W T and input the two cells, however, the results I got are all dash lines. Could you help with that please? Thanks so much
Hi, may I know where is the excel file for download? Can’t find the link, or can you send me?
Thanks!
Dears,
Thank you for the knowledge sharing.
Please how do I apply this technique on a financial statement that is built on monthly basis.
Warm regards.
Great tutorial you couldn’t have made it easier. Thank you:)
Hi,
I applied the above to one sensitivity exercise that i was working on, however, at times, the same values appear throughout the table. Why is that so? and how should i rectify that?