Scenario analysis is a key concept in financial modeling. It allows your financial model to change assumptions and reflect significant changes that may have occurred in the company’s operation.
It can be likened to a game of chess, where players think of multiple possible moves that can increase their likelihood of winning.
Understanding the key steps and thought processes behind scenario analysis will help you make more efficient and effective decisions. Therefore, it’s one of the most powerful tools in your arsenal as a finance professional.
After reading this article, you’ll understand what scenario analysis is, how it works, and the problems it can solve. Let’s get started.
What is Scenario Analysis?
Scenario analysis is the process of analyzing the movement of a specific metric or valuation under different scenarios.
Simply put, it involves calculating the value of a particular or a group of investments under a variety of future possibilities, i.e., scenarios, to better understand the effect of risks on value.
A scenario is defined as a combination of inputs that occur simultaneously.
Scenario Analysis vs. Sensitivity Analysis
People often conflate sensitivity analysis, as they both have similar base concepts: to predict outcomes given different scenarios. They, however, differ a lot.
With scenario analysis, you need to describe a specific scenario in detail.
With sensitivity analysis, you must understand how a set of independent variables affects some dependent variables. It is used in situations with more than one variable. For example, when interest rates increase, there are fewer sales and more debt.
These analytics models are used in tandem to determine the amount of risk and potential benefits.
Types of Scenario Analysis
Scenario analysis is often used to evaluate the pros and cons of organizational decisions.
Organizations use scenario analysis to anticipate unexpected economic, technological, and political challenges and plan accordingly. Some scenarios help organizations with project financial planning.
The initial base scenario is the average scenario that uses the current and commonly accepted assumptions about the future.
After that, two alternative scenarios are prepared: the best-case scenario and the worst-case scenario.
Best Case Scenario
This is what happens if everything goes the organization’s way. In this case, each input value is set at its best. The management puts this scenario into action to achieve their objective.
For example, calculate the net present value using the highest possible growth rates, lowest possible discount, and lowest possible tax rates.
Worst Case Scenario
The worst-case scenario considers all the negative factors that will affect the outcome. These factors include high interest rates, economic recession, poor sales, and global disruptions.
In a worst-case scenario, each input value is set to its worst. It considers the most severe outcomes in any given situation.
Multiple Scenario Analysis
This scenario is not restricted to the best or worst cases.
Multiple scenario analysis predicts the value of a particular investment by considering multiple scenarios. Companies use multiple scenario analyses to predict multiple future outcomes and plan accordingly.
Click here to download a sensitivity analysis template.
Scenario Analysis Using Excel
Scenario analysis is an old concept that huge companies such as Shell have used since 1970.
However, the process has improved due to the availability of computer simulation software that models a company’s environment and evaluates potential outcomes in minutes.
Excel has the Scenario Manager feature in what-if analysis, which can enhance financial forecasting.
Using the scenario manager, Excel can save a set of values that can be automatically substituted in specific cells on a worksheet. You can create and save different values on a worksheet and view various results by changing different scenarios.
Sometimes, you may require specific information from several people.
In this case, you can collect the information in different worksheets and then merge the scenarios from the other worksheets into one. You can then create a scenario summary report incorporating information from the different worksheets.
Excel has an Offset function that makes adjusting your model for changing expectations easy. The offset function allows you to set a reference point anywhere in the formula.
You can then set the number of rows to move down from the reference point and the number of columns to move to the reference point’s right. Once you have provided the information, Excel will pull the data from the desired cells.
You can also manage scenario analysis on Excel Scenario Manager Wizard in the What-If analysis group on the Data tab.
You can easily switch from one revenue case to another by changing one cell. Do this by setting up a scenario manager and using the offset function.
To create an Excel Scenario on the excel spreadsheet, follow these steps:
- On the Ribbon’s Data tab, click What-If Analysis
- Click Scenario Manager
3. In the Scenario Manager, click the Add button
4. Type name for the scenario. For example, use Marketing
5. Press the Tab key to move to the Changing cells box
6. On the worksheet, select cells B1
7. Hold the Ctrl key, and select cells B3:B4
8. Note: There is a limit of 32 changing cells
9. Press the Tab key to move to the Comment box
10. (optional) Enter a comment that describes the scenario.
11. Click the OK button.
12. The Scenario Values dialog box opens, with a box for each changing cell.
13. You could modify these values, but in this example, they contain the values currently on the worksheet and don’t need to be changed.
14. Click the OK button to return to the Scenario Manager.
15. Click the Close button to return to the worksheet.
To create a second scenario, you will change the values in B2, B3, and B4.
- To prepare for the Finance scenario, change the values in cells B1, B3, and B4.
- On the Ribbon’s Data tab, click What-If Analysis, then click Scenario Manager.
- In the Scenario Manager, click the Add button.
- Type name for the second scenario. For this example, you can use Finance or best-case scenario
- The Changing cells box should show the previous selection — B2, B3:B4 — so leave that as is.
6. Press the Tab key to move to the Comment box
7. (optional) Enter a comment that describes the scenario.
8. Click the OK button.
9. The Scenario Values dialog box opens, with a box for each changing cell.
10. Click the OK button to return to the Scenario Manager.
11. Click the Close button to return to the worksheet.
You can use the second procedure for any number of scenarios after the first one.
Here are three advantages of scenario analysis.
1) Numerous Possibilities
Scenario analysis provides a wide range of possibilities to consider. It helps investors avoid risk and failure by assessing investment prospects and evaluating the best—and worst-case scenarios.
2) Resource Allocation
Scenario analysis leads to the optimal allocation of resources. Once company owners are aware of external conditions likely to affect their operations, they can allocate resources effectively.
3) Predict Outcomes
Implementing certain policies and strategies helps improve systems thinking, which can help a company manager predict positive and negative outcomes.
What are the Cons of Scenario Analysis?
There can also be disadvantages to this analysis method.
1) Inability to Predict Growth Drivers
Looking at the future of an industry or business on purely financial terms distracts companies from other growth factors.
One example is technological advancement and changes. Technological innovation promotes competition by allowing other companies to create better products and sell them at a lower price.
2) Unexpected Outcomes
Scenario analysis has unforeseen outcomes. Other unpredictable factors, such as a global health pandemic, may come into play and affect the forecast’s outcome differently than the forecast predicted.
3) Might Lack Reliability
The reliability of this model depends on the quality and quantity of data available. Poor quality data or lack of data makes the model unreliable.
4) Assumptions
This model assumes the inputs correlate perfectly and that all the bad or good values occur together while inputs change in correlation to other inputs.
Remember, scenario analysis is prone to two of the most common errors in strategic analysis: overprediction and underprediction.
Scenario Planning solves the problems of overprediction and underprediction.
It works on the assumption that the future is constantly changing, even if the rate of change in life is significantly accelerated. It segregates knowledge into things we know about and uncertain things.
How to Perform Scenario Analysis in Financial Modeling
Financial modeling refers to creating an abstract representation of a company’s financial statement; scenario analysis is at its heart.
A financial model is designed solely for management to perform scenario analysis before arriving at a decision.
Each scenario in a financial model has assumptions that can either be implicit or explicit. A financial modeler ensures the end-user is aware of the assumptions made in the model.
Modes of Financial Models
Most financial models have two modes: pre-defined and expert modes.
The pre-defined mode is used mainly by beginners who may enter wrong input values and draw incorrect conclusions. This mode does not allow the user to edit individual input.
Instead, it allows the user to choose a scenario type—e.g., best-case scenario or worst-case scenario—and then automatically fills in the inputs to the model. This mode allows you to quickly draw standard reports.
The expert mode, on the other hand, allows users to change individual assumptions.
Expert financial modelers use this mode. It allows them to choose any scenario and vary the inputs in the base scenario to see how it impacts the financial model.
A financial modeler should ensure that the layouts of the various scenario analysis results are similar to ensure consistency and continuity for the end user.
Scenario Analysis: Step-by-Step Process
To do a basic scenario analysis in financial modeling, follow these steps:
- Define the Issue
Decide what you want to achieve or decide and make a timeline for when you want the action completed.
- Gather Data
Identify the trends, uncertainties, and key factors affecting your idea or plan.
Do a PEST Analysis for a large-scale plan. PEST analysis analyses the political, economic, social-cultural, and technological context in which you will implement your idea. A structured data analysis tool analyzes large quantities of data.
- Separate the Certainties from the Uncertainties
Certainties are the trends that are sure to continue predictably. Analyze potential blind spots in your thinking to eliminate unconscious bias in favor of certain assumptions.
Try to identify trends with solid foundations as much as possible, and always base your assessments on evidence rather than supposition.
Once done, adopt these trends as your certainties and separate them from your uncertainties, underlying factors that may or may not change, or trends that may or may not be important.
Next, list your uncertainties in order of priority, with the most significant concern at the top.
- Develop Scenarios
With your top uncertainty, take a good outcome and a bad outcome and develop a scenario around each one. Combine your certainties with your chosen outcome.
Do the same for your second-most serious uncertainty. At this point, with a bit of creative thinking, you should be able to establish a cause-and-effect link.
- Use the Scenarios in Your Planning
After developing different scenarios in the fourth step, you can finally use the scenarios as part of your planning to make the decisions you need to make with an idea of the risks and rewards you can expect in the outcome.
Plan Your Business Strategy with Datarails Scenario Modeling
Scenario analysis helps companies plan their business strategies by challenging their assumptions of the future. It allows management to identify potential risks and devise plans to mitigate their impact.
At Datarails, we say you should analyze your data, not just report it. With our scenario modeling tools, you can do exactly that.
However, scenario analysis is not infallible, and its shortcomings should be considered when developing a scenario analysis framework. The scenario analysis matrix is optimal when tailored to specific situations using different parameters.
However, the action’s impact and the uncertainty level help determine the parameters. A financial modeler should map out all the possible scenarios to determine the best option.
Book a Datarails demo today to see how it does just that.
About Datarails
Datarails is a business-augmented intelligence platform for the corporate finance function. Our FP&A software allows for connecting and centralizing all organizational financial data from various systems (ERP, GL, CRM) alongside Excel spreadsheets and operational data.
It also allows for the creation of automated financial reports (P&L, cash flow, budgets, etc.) and thorough analysis of consolidated data to create business and financial insights.
Did you learn a lot about scenario analysis in financial modeling in this post?
Here are three more to read next:
