It’s a question that every company should consider and try to answer. That’s why companies use scenario analysis, defined as the analysis of variables to understand potential future outcomes.
Every decision — from capital investments to price setting — can be improved by understanding how the decision will impact the business under different conditions.
Many companies turn to Excel for scenario analyses. Why wouldn’t they? It’s cheap and readily accessible (read: free because your organization already has it). Many business professionals have at least a basic understanding of spreadsheets. And any modeler or analyst worth their salt is an Excel expert.
However, Excel’s open-ended appeal is a double-edged sword. Users often build spreadsheets from scratch. This is a time-consuming process that can also become very confusing (and error-prone) very quickly.
Is Excel really worth the effort, or do most of us use it simply because we aren’t sure what other options are out there? Is there an easier, faster, more reliable way to perform scenario analysis?
In this post, we’ll walk through conducting scenario analyses in Excel and discuss the pros and cons of using it for financial modeling and budget planning.
An Intro to Scenario Analysis in Excel
Scenario analysis is one of the three “What-If Analysis” tools available in Excel. Each scenario can house up to 32 variables.
Let’s say a financial analyst wants to understand revenue using different prices and sales projections. The analyst can simply enter the variables (e.g., price, items sold) into Excel and create a formula to get the revenue (e.g., price x items sold = revenue):
Next, the analyst can save this formula as a scenario. Under What-If Analysis in the Data Tab, they can click the Scenario Manager and then Add. Variable cells (called changing cells) can be adjusted before saving the scenario for future use.
For this analyst, sales of 2,500 items is a best-case scenario. If they wanted to see revenue on a projection of, say, 1,000 items sold, they can manually adjust the variables and save as another scenario.
To view and compare different scenarios, users can create scenario summary reports. This page will lay out the scenarios in a Pivot Table. Excel automatically highlights the variable cells and creates grouping levels for easier navigation.
These summary pivot tables also eliminate the need to manually create your own summary page, thus saving on setup.
Note: Summary reports are not dynamic. If data in one of the scenario analyses changes, the summary report will not change. New reports must be created.
Building More Complex Scenarios
Of course, most businesses will have many variables going into their scenarios, especially when it comes to large projects like budgeting. What’s more, leadership will likely want to view scenarios in established formats like financial statements.
Excel makes these larger projects possible through the Merge Scenario wizard. This tool allows you to pull all worksheets in the workbook into a large-scale project. From here, users can choose the variables they want from each worksheet into a new scenario.
Continuing with our above example, let’s say the analyst wants to run a scenario analysis using projections from five products. With the Merge Scenario wizard, you can pull the individual product info into a summary-like scenario. The analyst can also choose the fields they want to pull into the new scenario (e.g., only pull in the revenue cells).
This allows users to narrow the focus of in-depth scenarios on only a few select metrics, a flexibility that’s especially important when presenting information to leadership and other decision-makers.