How to Do Scenario Analysis in Excel

Wondering how a sudden increase in material costs or a surprisingly successful marketing campaign might impact your bottom line? Instead of guessing, you can map out these possibilities directly in Excel. This article will walk you through how to use Excel's built-in tools for scenario analysis, allowing you to compare best-case, worst-case, and most-likely outcomes side-by-side.

What is Scenario Analysis?

Scenario analysis is a way to make better decisions by exploring several different possible futures. Instead of relying on a single forecast, you create a few different versions - or scenarios - based on a range of potential events. By seeing how your key metrics (like revenue, profit, or inventory) change in each scenario, you can create more resilient strategies and be better prepared for uncertainty.

Typically, businesses model three core scenarios:

• Best Case: This is your optimistic scenario. For example, sales are 15% higher than expected, and operating costs are 10% lower.
• Most Likely Case (or Base Case): This is your primary forecast, based on your most realistic assumptions and historical data.
• Worst Case: This is the pessimistic view. For example, a key supplier increases their prices by 20%, and sales drop by 10%.

By comparing these three, you can understand your potential upside, downside, and the most probable outcome, helping you allocate resources more effectively and set realistic goals.

Step 1: Build Your Base Model in Excel

Before you can analyze different scenarios, you need a solid foundation. This is your "Base Case" model, and it's where you'll set up all your inputs, calculations, and outputs. The quality of your analysis depends entirely on the logic of this model, so it's worth taking the time to get it right.

Key Components of Your Model

A good financial model has three distinct parts laid out clearly on the spreadsheet.

1. Inputs (or Assumptions): These are the variables you plan to change in your different scenarios. They should be grouped together at the top of your sheet for easy access. These are hard-coded numbers, not formulas. For a simple profit forecast, your inputs might be:

• Units Sold
• Price Per Unit
• Cost Per Unit
• Fixed Costs (like rent or salaries)

2. Calculations: This is the engine of your model. These cells contain the formulas that connect your inputs to your outputs. For our example, the calculations would be:

• Total Revenue: =Units Sold * Price Per Unit
• Total Variable Costs: =Units Sold * Cost Per Unit
• Gross Profit: =Total Revenue - Total Variable Costs

3. Outputs (or Results): These are the final results you're most interested in. The output cells should always be formulas that pull from your calculation section.

• Net Profit Before Tax: =Gross Profit - Fixed Costs

Example Model Layout

To make this tangible, let's set up a simple model. We’ll organize it so inputs are easily identifiable. Your Excel sheet might look like this:

Cell A1 to A5 (Labels):

Inputs: Units Sold Price Per Unit Cost Per Unit Fixed Costs

Cell B1 to B5 (Values for our Base Case):

(Leave B1 blank or title it "Base Case") 1000 $50.00 $20.00 $15,000

Cell A7 to A10 (Labels):

Calculations & Outputs: Total Revenue Total Variable Costs Net Profit

Cell B8 to B10 (Formulas):

=B2*B3 =B2*B4 =B8-B9-B5

With these numbers and formulas, your Net Profit (the cell you care most about) would calculate to $15,000. This is your Base Case.

Step 2: Using the Scenario Manager Tool

Now that the model is built, you can use Excel's built-in Scenario Manager to save and compare your different scenarios without having to manually change the input values each time.

Finding the Scenario Manager

First, you need to locate the tool. It's tucked away under the Data tab.

Go to the Data tab on the Excel ribbon, look for the Forecast group, and click on What-If Analysis. A dropdown menu will appear. Select Scenario Manager.

Creating Your First Scenario (The 'Base Case')

The Scenario Manager box will pop up, and it will be empty. Your first step is to save your current model as the "Base Case."

1. Click the Add... button.
2. In the "Add Scenario" dialog box:
3. Click OK. Another box will appear showing the values for your changing cells. Since this is your Base Case, the values are already correct, so just click OK.

You’ve now saved your first scenario!

Adding 'Best Case' and 'Worst Case' Scenarios

Now, repeat the process to add your other scenarios. From the main Scenario Manager window:

For the 'Worst Case':

1. Click Add... again.
2. For Scenario name, "Worst Case".
3. The Changing cells field should already be populated with B2:B5. If not, select them again.
4. Click OK. The values box will appear. Now, enter your pessimistic assumptions. For instance:
5. Click OK.

For the 'Best Case':

1. Click Add... one more time.
2. Type the name "Best Case". The changing cells should still be B2:B5.
3. Click OK and enter your optimistic values:
4. Click OK.

Viewing and Analyzing the Scenarios

Now, the Scenario Manager lists all three scenarios. To see how each one affects your Net Profit, simply click on a scenario name (e.g., "Best Case") and click the Show button at the bottom. Excel will instantly update the values in your input cells, and your formula-driven output cells will recalculate.

For summary reporting, in the Scenario Manager box, click Summary.... A small dialog will ask which Result cells you want to see. Select cell B10 (your Net Profit cell). Click OK.

Excel will instantly generate a new sheet with a clean, formatted summary report. It lists all your input values and the resulting Net Profit for each scenario, making side-by-side comparison easy.

Going Deeper: Answering Specific Questions with Goal Seek

Scenario Manager is great for comparing predefined outcomes, but what if you have a specific target in mind? For example, if you need a Net Profit of $20,000 and want to know how many units you need to sell to get there. For this, you can use Goal Seek.

1. Go back to the Data tab > What-If Analysis > Goal Seek.
2. A simple dialog box appears:
3. Click OK.

Excel will run calculations and find the exact number of units you need to sell (e.g., 1,167) to hit your profit target. This helps you quickly determine the necessary lever to reach your goal.

Tips for Effective Scenario Analysis

As you build more complex models, keep these best practices in mind:

• Keep It Simple: Focus on 3-5 key variables that most impact your results. Avoid overly complex models with many variables.
• Document Your Assumptions: Use comments in Scenario Manager to explain why you chose specific values.
• Check Your Formulas: Verify formulas are correct to prevent errors in results.
• Use Realistic Data: Base your scenarios on factual data rather than arbitrary numbers.
• Visualize the Results: Create charts from the Scenario Summary report for clearer presentation and comparison.

Final Thoughts

By mastering Scenario Manager and Goal Seek, you turn a static spreadsheet into a powerful decision-making tool. Thinking in terms of best, likely, and worst-case outcomes helps shift from reactive reporting to proactive strategic planning, preparing your business for future uncertainties.

At Graphed, we simplify data collection across platforms. Our tool connects directly to your data sources, creating a live, always-updated source of truth. You can then ask natural language questions, build dashboards, and get instant insights without manual data wrangling — automating the process so you focus on exploring what-ifs instead of data cleanup.