Updated: 6/10/2024

Finance’s response to unpredictability is to be prepared for everything by holding multiple points of view simultaneously. But how can you act on that and build into your finance toolkit?

In a session at AFP FinNext Asia, Scenario Modeling for Better Forecasting in Excel, Danielle Stein Fairhurst of Plum Solutions describes techniques to embrace uncertainty in decision-making by using practical examples and applying standard Excel functionality to build scenarios.

Fairhurst begins the session by explaining the definition of a financial model: It is a tool that simplifies a large, complex system so that you can study possible outcomes and solutions to a real-world financial problem. This differs from a spreadsheet which is a catch-all term for anything built in a spreadsheet application; it could be anything containing data and is usually a single-purpose standalone report. A financial model is more structured, dynamic, uses relationships between variables and contains hypothetical outcomes.

Scenarios, sensitivities and What-if analysis

If your financial model is built in a well-structured way, you will be able to run sensitivities and scenarios over the top of it. All models include a raft of interdependent relationships happening throughout the model, so a few quick definitions will set the stage to examine Fairhurst’s methods:

• Sensitivity analysis is where you tweak one or two input variables, which determines how outcomes are affected based on changes in the targeted variables.
• Scenario analysis is the ideation and analysis of possible future outcomes, including simulating the impact of complex changes on the business. It allows you to examine and evaluate changes in multiple variables based on events or scenarios to prepare for various outcomes.

The What-if analysis in Excel refers to both scenarios and sensitivities. It is the process of changing the values to see how those changes will affect the outcome.

Technical methods of scenario analysis in Excel

Having tools to help FP&A professionals look forward and run scenario analyses is crucial, and Fairhurst describes five different techniques in Excel that could be of value.

Manual Scenario Selection

Manual Scenario Selection allows you to manually select a preset selection in Excel. “The methods that I most commonly use are data validation dropdown or a combo dropdown,” said Fairhurst. “You could also use tick boxes or numbers.” Some advantages of manual scenario selection are that it is easy to build and understand, and it can handle a large number of inputs and outputs. However, you are only able to look at one scenario at a time and it can be difficult to compare scenarios side by side.

Scenario Manager

Scenario Manager is another feature that is already built into Excel. This tool allows you to change or substitute input values for multiple cells, letting you view results of different input values or different scenarios.

Data Tables

Rather than showing one single scenario at a time, data tables allow you to look at all the possible outcomes simultaneously. This tool is perfect for sensitivity analysis because it allows you to view possible iterations or variations of inputs in your data table side by side. However, some disadvantages are that it uses array formulas, so it’s tricky for beginners to build; inputs and outputs need to be on the same worksheet; it can only handle one or two input variables and only one output; and it uses significant processing power that may slow down calculations.

Goal Seek (What-if Analysis)

Goal Seek in Excel is a tool for solving for a desired output by changing an assumption that drives it. It uses a trial-and-error approach that allows the user to specify the desired outcome and then back-solve the problem by automatically cycling through inputs until it arrives at the correct answer.

Monte Carlo (Stochastic Simulations)

The Monte Carlo simulation runs through multiple hundreds or even thousands of simulations to show the probability of various model outcomes, including most likely and most remote. These can be tricky to create and sometimes require specialized software or Excel add-ins; Fairhurst’s method takes advantage of Excel’s native sensitivity analysis functionality by running a single driver running through a data table. This does limit you to the capabilities of the data table noted above, however, and so more complex modeling might require a more robust tool.

“The beautiful thing about financial modeling is if you set it up in a well-structured way, you will be able to apply any of these techniques to your drivers and then show all the possible outcomes of your financial model,” Fairhurst concludes.

Fairhurst further demonstrates how to build a rolling forecast, including dynamic formulas and dynamic seasonal linear trends (using FORECAST functions), in AFP’s DIY Automation Quick Study video series.