Scenario analysis is an important concept in financial modeling. It gives your financial model the flexibility to change assumptions and reflect important changes that may have taken place regarding 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 the game.

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 you calculating the value of a specific or a group of investments under a variety of future possibilities, i.e., scenarios, intending to get a better understanding of the effect of risks on value. A scenario is defined as a combination of inputs that occur simultaneously.

Most people often confuse scenario analysis with 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, in detail, a specific scenario. With sensitivity analysis, you need to understand how a set of independent variables affect some dependent variables. The sensitivity analysis is used in situations that have more than one variable. Both of these analytics models are used in tandem to determine the amount of risk and potential benefits. A scenario analysis example is when there is an increase in interest rates; then there are fewer sales and more debts.

Types of Scenario Analysis

Scenario analysis is often used to evaluate the pros and cons of decisions made by organizations. Organizations use scenario analysis to anticipate unexpected economic, technological, and political challenges and plan accordingly. Some scenarios help organizations in their financial planning for projects. 

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 the ideal situation that considers what will happen if everything goes the organization’s way. In this case, each input value is set at its best. The management puts this scenario in action to achieve their objective. An example is using the highest possible growth rates, lowest possible discount, and lowest possible tax rates to calculate the net present value.

  • 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 best or worst cases. Multiple scenario analysis predicts the value of a particular investment by taking into account multiple scenarios. Companies use multiple scenario analyses to predict multiple future outcomes and plan accordingly.

Scenario Analysis Using Excel

Scenario analysis is an old concept that huge companies such as Shell have been using 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 a matter of minutes.

Excel has the Scenario Manager feature in what-if analysis that can be used to 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 different results by changing different scenarios.

Sometimes, you may require specific information from several people. In such a case, you can collect the information in different worksheets and then merge the scenarios from the different worksheets into one. You can then create a scenario summary report that incorporates information from the different worksheets.

Excel has the Offset function that makes it easy to adjust your model for changing expectations. The offset function allows you to set a reference point anywhere in the formula and 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. Excel will then pull the data from the desired cells once you have provided the information.

The Scenario Manager Wizard in the What-If analysis group on the Data tab manages scenario analysis on Excel. You can easily switch from one revenue case to another by changing one cell by setting up a scenario manager and using the offset function.

To create an Excel Scenario on the excel spreadsheet, follow these steps:

  1. On the Ribbon’s Data tab, click What-If Analysis
  2. 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. 

  1. To prepare for the Finance scenario, change the values in cells B1, B3, and B4.
  2. On the Ribbon’s Data tab, click What-If Analysis, then click Scenario Manager.
  3. In the Scenario Manager, click the Add button.
  4. Type name for the second scenario. For this example, you can use Finance or best-case scenario
  5. 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.

What are the Pros of Scenario Analysis?

  • Scenario analysis provides a wide range of possibilities to consider. This helps investors avoid risk and failure by considering the best and worst-case scenarios and assessing investment prospects.

  • Scenario analysis leads to the optimal allocation of resources. Company owners can allocate resources effectively once they are aware of external conditions that are likely to affect their operations.

  • It helps improve systems thinking by implementing certain policies and strategies, helping a company manager predict positive and negative outcomes.

What are the Cons of Scenario Analysis?

  • Inability to predict growth drivers. Looking at the future of an industry or business on purely financial terms distracts companies from other growth factors such as technological advancement and changes. Technological innovation promotes competition by allowing other companies to create better products and sell them at a lower price.

  • Scenario analysis has unforeseen outcomes. Other unpredictable factors, such as a global health pandemic, may come into play and affect the forecast’s outcome in a different way than was predicted by the forecast.

  • 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.

  • This model assumes that the inputs correlate perfectly and that all the bad or good values occur together while inputs change in correlation to other inputs.

Scenario analysis is prone to two of the most common errors made by any strategic analysis: overprediction and underprediction. Scenario Planning solves the problem of overprediction and underprediction. It works on the assumption that the future is always changing, even if the rate of change in life is accelerated to a great extent. It segregates knowledge into two areas: things we know about and things we are uncertain of.

How to perform scenario analysis in financial modeling 

Financial modeling refers to creating an abstract representation of a company’s financial statement, and scenario analysis is at its heart. A financial model is created for the sole purpose of the 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 that the end-user is aware of the assumptions made in the model.

Most financial models have two modes: the pre-defined mode and the expert mode. The pre-defined mode is mostly used by beginners who may enter wrong input values and draw wrong conclusions. This mode does not allow the user to edit individual input. It instead allows the user to choose a scenario type — i.e best-case scenario or worst-case scenario — then it automatically fills in the inputs to the model. You can easily draw standard reports using this mode

The expert mode, on the other hand, allows users to change individual assumptions. You can choose any scenario and vary the inputs in the base scenario to see how it impacts the financial model. This mode is used by expert financial modelers.

To ensure consistency and continuity for the end-user, a financial modeler should make sure that the layouts of the various scenario analysis results are similar.

To do a basic scenario analysis in financial modeling, follow these steps:

  1. Define the issue

Decide what you want to achieve or decide and make a timeline for when you want the action completed.

  1. Gather data

Identify the trends, uncertainties, and key factors that may affect your idea or plan. Do a PEST Analysis for a large-scale plan. PEST analysis analyses the political, economical, social-cultural, and technological context in which the implementation of your idea will take place. Use a structured data analysis tool to analyze large quantities of data.

  1. 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 as much as possible to identify trends with solid foundations 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 biggest concern being at the top of the list.

  1. Develop scenarios

With your top uncertainty, take a good outcome and bad outcome and develop a scenario around each one, combining 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.

  1. 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.

Summary

By challenging your assumptions of the future, scenario analysis helps companies plan their business strategy. It allows management to identify potential risks and come up with plans to mitigate their impact. 

Scenario analysis is not infallible, however, and its shortcomings should be taken into account when developing a scenario analysis framework. The scenario analysis matrix is best used when tailored to specific situations using different parameters. 

However, the impact of the action and the level of uncertainty help determine the parameters. In order to determine the best option, a financial modeler should map out all the possible scenarios.

About DataRails

DataRails is developing a business augmented intelligence platform for the corporate finance function. Our FP&A solution allows for the connection and centralization of 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.), as well as thorough analysis of consolidated data for the creation of business and financial insights.