Have you ever been faced with a large, complex risk model and been tasked with determining what the most important factors are? What if you need to determine how sensitive your profits are to particular variables, such as material costs or market share? Or perhaps you want to run a risk analysis in @RISK, but don’t know which variables are important enough to add distribution functions to.
Within @RISK software, users can perform automated “what if” sensitivity analysis on Microsoft Excel spreadsheets to answer these questions. Define any output or “bottom line” cell or cells, and what-if analysis will automatically find and vary all input cells which affect your output. You can also control exactly which cells you’d like to analyze. The end result is easy-to-understand tornado charts, spider graphs, and other reports which clearly identify and rank which affect your bottom line the most.
What is what-if analysis?
What-if analysis is a process used to assess the impact of changing input variables on a model’s outcome. It also helps you determine which of your model inputs affect your “bottom line” results the most.
Running a what-If analysis is a key component of making any decision based on a spreadsheet model. This analysis identifies which inputs affect your outputs the most. The analysis identifies those factors you should be most concerned with as you 1) gather more data and refine your model and 2) manage and implement the situation described by the model.
Additionally, what-If analysis is often the first analysis performed on a spreadsheet model. Its results then lead to a further refinement of the model, additional analyses, and ultimately, a final decision based on the analysis.
Why use what-if analysis before a Monte Carlo simulation?
@RISK offers a quick, easy, and intuitive way to perform what-If analyses, making it an ideal first step before a more complex Monte Carlo simulation. It helps:
- Simplify input analysis using straightforward +/– changes rather than probability distributions.
- Save time by identifying the most critical inputs, allowing you to focus your @RISK modeling efforts.
- Improve accessibility, as decision makers often find what-If results easier to understand than probabilistic outputs.
- Support time-sensitive decisions, especially when there's not enough time to build a full @RISK model.
How to perform what-if analysis in @RISK
To perform what-if analysis in @RISK, simply take your existing spreadsheet risk model, select any cell or cells as outputs, and what-if analysis scans the spreadsheet to find cells which affect your results. Click the ‘Run What-if’ menu and what-if analysis tries different values for each variable cell, changing values over ranges such as -10% and +10%. Each time a new value is tried, the spreadsheet is recalculated, and new results are generated. Upon completion, what-if analysis ranks the variable cells according to the effect they have on your selected outputs. You can also select exactly which cells you wish to vary, and by how much, for a more customized analysis.
What-if analysis uses Vary functions in @RISK to vary inputs across a range. What-if analysis functions are true Excel functions and behave exactly as native Excel functions do. What-if analysis windows point to the cells they refer to via callouts, and changes in one place are automatically carried out in the other.
What-if analysis use cases
What-if analysis can be used for a variety of applications like:
- Investment Planning & Portfolio Optimization: Identify the most critical factors that lead to volatility or undesirable outcomes in investments.
- Cost Estimation: Understand the factors that drive overages in materials and labor costs throughout the entire project.
- Engineering Reliability: Gain insight into the factors driving variance and inconsistencies to ensure quality.
- Capital Reserves: Determine the risk factors which most likely lead to loss scenarios to enable focused mitigation planning.
- Oil, Gas, & Mineral Reserves: Understand what issues are most likely to derail an exploration project so that mitigation steps can be taken.
What-if analysis with @RISK
Quickly identify your critical factors with automated “what-if” sensitivity analysis – right in your Microsoft Excel spreadsheet. By identifying and ranking all input factors that affect your bottom line, you can make strategic decisions that help you achieve your goals. Request a demo of @RISK today.