How to Do a Scenario Analysis in Excel

“What if…?”

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.

Scenario Analysis in Excel: The Pros

Excel is an inexpensive and readily accessible spreadsheet tool. Odds are your organization already uses Excel or another spreadsheet program for some activities. In this sense, using Excel for scenario analyses saves time and money because you won’t have to buy new software or train employees on how to use it.

Excel also gives users the freedom to build their spreadsheets from the ground up. Custom line items, report formatting, and much more can be accommodated at the request of leadership.

While there will always be a need for detailed scenario analysis, business owners often need simple, straightforward calculations. Excel is perfect for these simpler scenarios and will take even modestly experienced users little time to run.

Scenario Analysis in Excel: The Cons

While Excel does have the capabilities to do in-depth scenario analyses, its disadvantages become clear fast.

First off, the sheer number of sheets you must create in order to test different scenarios is, to put it lightly, less than ideal. Depending on the size of your organization, getting all the financials needed from different departments may also require input from various people.

Companies also have to worry about editing and sharing access. While Microsoft’s OneDrive for Business provides cloud storage and online editing, lack of coordination could lead to file overwrites and missed updates. Most of the time, Excel spreadsheets are still downloaded and attached to emails, which makes sharing the right versions with the right people a nightmare.

That’s not all. For Excel features like Merge Scenario to work, all worksheets need to be set up the exact same way. For example, if “Items sold” is entered into cell A2 in one sheet, they better be in the same place in all other sheets. Any discrepancies can cause merging issues, leading to a lot of wasted time spent manually checking for and fixing errors.

This brings us to another point: building spreadsheets can be very time-consuming. If the person in charge of the scenario analysis workbook leaves, someone else is left dissecting the leftover formulas, which never ends well.

Templates Are Helpful, To a Point…

You may be thinking, “Well, what if I just grab a free template from online?”

There are indeed plenty of financial templates around to get started. However, they will all require at least some basic tinkering to meet your company’s needs.

Yet without knowledge of how these templates were built, one could easily overlook formulas that could lead to inaccurate scenario projections.

Financial Modeling Tools: A Better Option

It’s safe to say that scenario analyses in Excel require a lot of technical knowledge, time dedicated to set up, and organization. For many companies, Excel just might not be worth the effort.

The good news is there are better scenario analysis solutions than Excel. Scenario analysis tools like Synario take the manual work out of financial modeling, allowing users like you to focus on the future of your business.

Easier Control Over Scenario Analysis

Synario’s patented Multiverse Modeling™ gives analysts access to flexible and adaptable assumption tables.

This allows users to create initiatives around key factors such as capital expenditures, personnel changes, sales growth, and more. Users can then toggle these initiatives on and off or adjust values in sliders to view real-time updates for different scenarios.

For example, let’s say a manufacturing company needs to run scenario analyses that take the following into account:

With these features, scenario analyses can be done mid-presentation. This allows for engaging and dynamic discussions that help everyone arrive at the right decision for your company. Synario’s drill-down functionality can also show the underlying math behind every calculation, providing complete transparency to decision-makers.

Faster scenario analyses and easy drill-downs can’t be easily done with Excel, which requires endless follow-up meetings and emails to address lingering questions.

It's Time to Move Beyond Excel

You may be wondering: “what if I already use Excel for my finances? Wouldn’t it be easier to just stick with what I have?”

Though this is certainly tempting, it’s better to think of Excel as a stepping stone to more powerful tools. As companies grow, so does the importance of scenario analysis. More variables must be accounted for—and more outcomes need to be considered—in a shorter period of time. Not to mention more money is on the line.

Excel, for all its customizability, is ultimately a static data visualization tool. Every “what-if” raised at your company will require another scenario analysis and a different spreadsheet. Keeping track of everything will only grow more difficult and eventually lead to decision-making based on incomplete, outdated, or erroneous information.

Fortunately, many financial tools are Excel-compatible, especially at setup. When getting started with Synario, for instance, users can import company financial information using Excel import templates. From here, you’re free to start using financial statements and run scenario analyses. The time savings compared to Excel become clear right away.

If you’re ready to move on, we’re happy to help you get started.