Building Scenario Analysis for Financial Models

What is Scenario Analysis?

Scenario analysis is a process of examining and evaluating possible future events. It considers various feasible results or outcomes.

In financial modeling, this process is typically used to estimate changes in the value of a business or cash flow. Especially, when there are potentially favorable and unfavorable events that could impact the company.

What are the most frequently used cases?

Managers typically start with 3 basic scenarios:

1. Base case scenario – This is the average scenario. It is most probably to happen based on management assumptions.
2. Worst case scenario (downside case) – considers the most serious negative outcome that may happen in a given situation. For example, that would consider a possible financial recession over the projected years, negative trends in the company’s industry and / or pessimistic scenario for the company overall.
3. Best case scenario (upside case) – This is the most positive projected scenario. Everything is expected to grow (economy, industry, company itself) and have an optimistic outcome.

Most business managers use scenario analysis during their decision-making process. They want to find out the best-case scenario and worst-case scenario. It helps a lot while anticipating profits or potential losses.

Guide to Building Scenario Analysis for Financial Models

Let’s build three scenarios with the relative steps for the chosen company (see the picture):
The base case is the case we have in the financial model built. We assume that Revenue Growth for 2019 is 14.3% for all three scenarios. But for 2020-2023 it might go down or up depending if it’s Upside (Step = +1.5%) or Downside (Step = -2%) case.

1. Calculate projected scenarios for revenue growth (2020 – 2023) using assumptions for 2019 and the step.

Note: Don’t forget to anchor column K for the step, so you can fill the formula right and down.

After completing the formula in G38, highlight G38 – J39 with Shift + Right / Down Arrow (shortcut to highlight cells in Excel). Then, fill the formula to the right (Ctrl + R shortcut) and down (Ctrl + D shortcut).

2. Based on the Scenarios for Revenue Growth let’s build Scenarios for Revenue.

Note: Don’t forget to anchor column K for the step, so you can fill the formula right and down.

After completing the formula in G42, highlight G42 – J44 with Shift + Right / Down Arrow (shortcut to highlight cells in Excel). Then, fill the formula to the right (Ctrl + R shortcut) and down (Ctrl + D shortcut).

3. Let’s build a Scenario switch (cell B46 in our example) and use the CHOOSE function to build scenarios.

The Excel CHOOSE function returns a value from a list using a given position or index.

CHOOSE(index_num,value1,[value2],…)
– If index_num is 1, CHOOSE returns value1;
– If it is 2, CHOOSE returns value2; and so on.

See on the next screenshots how different scenarios reflect in J46 thanks to the CHOOSE function we have used.

4. Let’s use INDEX function to build scenarios

The Excel INDEX function returns the value at a given position in a range or array.

INDEX(array,row_num,[column_num]).
Array – Array of cells,
Rows – It selects the row in the array from which to return a value,
Column_num – It selects the column in the array from which to return a value. Optional.

5. Let’s use OFFSET function to build scenarios.

The OFFSET function is one of the built-in functions in Microsoft Excel. Its purpose is to return a range that is a specified number of rows and columns from a reference cell or range. The range that the OFFSET function returns can be a single cell or a range of multiple adjacent cells.

OFFSET(reference, rows, columns).
Reference – Initial Cell,
Rows – How Many Rows Down From The Initial Cell,
Cols – How Many Columns To The Right From The Initial Cell.

See on the next screenshots how different scenarios reflect in J46 thanks (CHOOSE function), J48 (INDEX function) and J50 (OFFSET function).

There are other Excel functions you can use when building scenarios for your model, such as MATCH, INDIRECT and more. Learn all possible options in our Excel Efficiency educational game that includes over 70 Excel functions and over 200 shortcuts.

Scenario Analysis vs. Sensitivity Analysis

Sensitivity analysis is the study of how the outcome of a decision changes due to variations in input. It is used in situations that rely on one or more input variables.

In contrast, scenario analysis is the process of predicting the future value of an investment. It depends on changes that may occur to existing variables. It requires one to explore the impact of different market conditions on the project or whole investment.

Learn more about Sensitivity Analysis in our article.

Benefits of performing Scenario Analysis

Predicting the future is a risky business. So, better to explore as many different cases of what could happen as is reasonably possible.

Key benefits for Scenario Analysis include:

1. Future planning – The goal of any business venture is to increase revenue over time. Even if it is a worst-case scenario. Building different scenarios give managers and investors a peek into the expected returns and risks. Thus, it will help them to make educated decisions.
2. Avoid potential losses for companies – Companies can decrease potential losses that result from uncontrollable factors. They can become aggressively preventive during worst-case scenarios. Besides, it will help to analyze events and situations that may lead to unfavourable outcomes.
3. Avoid risk and failure for investors –Scenario analysis takes the best and worst probabilities into account. It helps investors to make an informed decision.

Conclusion

Here I have taken you through the process of building a scenario analysis. To master financial modeling and other excel tricks and tips, you could connect with keySkillset to know details of the course it offers. After being a pro at financial modeling and excel, not only can you create and compare multiple scenarios, but you can also build your entire financial model, track all of your key performance indicators, and forecast your company's growth.