What-if analysis: a beginner’s guide to scenarios + goal seek in Excel

Some of the tools, such as data tables that we can use for performing what-if analysis are found on the tab in Excel helpfully labelled “What-if analysis” as shown in Figure 1. So what-if analysis is a general term which may refer to performing either sensitivities or scenarios, or both. Sensitivity Analysis is a tool used in financial modeling to analyze how the different values of a set of independent variables affect a specific dependent variable under certain specific conditions.

The sensitivity analysis is based on the variables that affect valuation, which a financial model can depict using the variables’ price and EPS. The sensitivity analysis isolates these variables and then records the range of possible outcomes. By putting financial models to the test against a wide range of potential outcomes, sensitivity analysis increases their trustworthiness. In the sensitivity analysis process, you change one input (such as cost, time, or scope) and subsequently evaluate how the output changes. You can understand how inputs affect the outcomes by repeating the process for various inputs.

Stay ahead of pace by planning and modeling across multiple scenarios and outcomes. Risk management is another area where sensitivity analysis can be invaluable, as it helps organizations identify, assess, and mitigate various risks, including credit risk, market risk, and operational risk. The procedure for sensitivity analysis involves “trying out” various alterations from the original assumptions to assess the impact. And when it comes time for analysts to meet with business leaders and present their findings, Synario’s Presentation Mode makes it easy to visualize the impacts of any executive decision. The method outlined in the previous section is known as the one-at-a-time (OAT) or local sensitivity analysis.

Do you own a business?

Scenario analysis assesses the impact of changing all variables at the same time. The conclusions drawn from studies or mathematical calculations can be significantly altered, depending on such things as how a certain variable is defined or the parameters chosen for a study. When the results of a study or computation do not significantly change due to variations in underlying assumptions, they are considered to be robust. https://accounting-services.net/sensitivity-analysis/ What-if analysis in financial modeling works on the same basic principles, using concrete numbers to model possible outcomes. In a fast-changing business environment, considering possible changes to your current assumptions helps you create accurate forecasting and build contingency plans. Under a set of assumptions, sensitivity analysis examines how a target variable is affected by a change in an input variable.

• Sensitivity analysis enables risk managers to identify the most critical risk factors and evaluate how changes in these factors affect the overall risk exposure of a portfolio.
• In the PMT formula held in C8, the value in C6 is replaced by the row values in Rows 9 to 12; and the value in C4 is replaced by the column values in Columns C to L.
• So what-if analysis is a general term which may refer to performing either sensitivities or scenarios, or both.
• Sensitivity analysis, therefore, is useful to determine which assumptions are critical and which have less impact.

Using sensitivity analysis, analysts might determine that both projects, which require financing, are very sensitive to a longer timeline for a vaccine because of the lost room-and-board costs. But the new dormitory construction is much less disruptive to in-person and virtual science classes because no students or teachers will have to be relocated. In sensitivity analysis, you change one variable while keeping other variables intact and study the impact of the change on a specific outcome.

This appears a logical approach as any change observed in the output will unambiguously be due to the single variable changed. Furthermore, by changing one variable at a time, one can keep all other variables fixed to their central or baseline values. In case of model failure under OAT analysis the modeler immediately knows which is the input factor responsible for the failure.

Table of Contents

Sensitivity analysis can aid in ensuring resource distribution is optimal. Moreover, it should enhance its resources in areas where it lags considerably behind its rivals. Next, select the range of cells containing the formulas and values you want to substitute.

How to do Sensitivity Analysis

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. 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. Once a realistic base case scenario is identified, analysts must decide which independent and dependent variables are most relevant to the outcome.

Advantages and Limitations of Sensitivity Analysis

Using variance-based analysis, a user might discover that 40% of the total variance in m results from x, 50% results from y, and the remaining 10% results from interactions between x and y. Therefore, pinpointing the degrees of uncertainty from various sources is vital to making informed decisions. One of the common ways to factor for it is to use probability-weighted expected values in place of uncertain inputs before running a simulation analysis. Decision-makers need a comprehensive view of all information before making any significant decision.

How Does Sensitivity Analysis Work?

This allows them to narrow their focus to only the most critical considerations. Sensitivity analysis can also help analysts identify which variables will have little to no impact, so you can confidently allocate valuable resources elsewhere. Company executives use sensitivity analysis to model the potential outcomes of a project and evaluate alternative decisions to determine the best course of action. This template allows you to build your table to demonstrate the effect of various variable changes on the outcome. Regression analysis, in the context of sensitivity analysis, involves fitting a linear regression to the model response and using standardized regression coefficients as direct measures of sensitivity. The regression is required to be linear with respect to the data (i.e. a hyperplane, hence with no quadratic terms, etc., as regressors) because otherwise it is difficult to interpret the standardised coefficients.

What is a what-if analysis?

It creates a baseline financial model and allows a planner to manipulate variables to show how it changes the outcome. OAT analysis looks at the impact of each input on the output one at a time, while keeping all other values in the scenario the same. But because it is so cut-and-dry, OAT sensitivity analysis doesn’t always reveal the relationships between variables or the probability of different combinations and permutations. Since sensitivity analysis explores a wide range of input and output variables, the formulas analysts use to get the numbers needed to calculate sensitivity can vary.

NPV takes into consideration initial capital, the acceptable rate of return, and the return on investment from cash flows. That’s not to say that sensitivity analysis is only useful for risk management. Sensitivity and scenario analysis are different techniques, although they serve the same purpose (i.e., assessing the risks or impact of changes). Sensitivity analysis helps determine how changes in one input affect the output. Project managers find this tool useful since it allows them to weigh the benefits and risks under different conditions. The what-if Scenario Analysis function can help you make decisions by showing the impact of changing one or more variables.

Here, we show the “Increase Price/Reduce Costs” scenario to understand how taking all three possible actions impacts our profit margin. Sensitivity analysis helps decision-makers to evaluate different scenarios and identify the critical factors that affect the outcome. This information can be used to make more informed decisions and develop robust strategies. The quality of sensitivity analysis depends on the assumptions and scenarios used, which are often subjective and can be influenced by the biases of the analysts involved.