How to Use the Excel Scenario Manager Tool for What-If Analysis

When financial analysts build complex forecasting models in Microsoft Excel, they rarely rely on a single set of assumptions. To accurately project revenue, an analyst might need to calculate three distinct scenarios: a \”Best Case\” (high sales, low costs), a \”Base Case\” (average sales, average costs), and a \”Worst Case\” (low sales, high costs). Instead of manually overwriting the input variables and copying the results to different worksheets, professional modelers use the Excel \”Scenario Manager\” to mathematically store and switch between multiple versions of the future instantly.

Why Use the Scenario Manager?

The Scenario Manager is a What-If Analysis tool that allows you to mathematically group a specific set of input values (up to 32 different cells) and save them under a single name. Once multiple scenarios are saved, you can instantly swap the mathematical inputs of your entire spreadsheet with a single click. Most importantly, the tool can generate a \”Scenario Summary\” report—a completely separate dashboard that mathematically compares the final outputs of all your different scenarios side-by-side, without destroying your original formulas.

Step 1: Open the Scenario Manager

Before launching the tool, ensure your spreadsheet has clear input cells (variables you will change) and output cells (formulas that rely on those variables).

1. On the main ribbon, click the Data tab.
2. In the Forecast group, click What-If Analysis.
3. Select Scenario Manager… from the dropdown menu. A new dialog box will appear.

Step 2: Add Your First Scenario (Base Case)

You should always save your current, default data first.

1. In the Scenario Manager dialog box, click Add.
2. In the Scenario name field, type Base Case.
3. Click into the Changing cells field. Use your mouse to select the specific input cells on your spreadsheet that you plan to alter (e.g., your \”Projected Sales\” and \”Material Costs\” cells). You can hold the Ctrl key to select non-adjacent cells.
4. Click OK.
5. A new box will appear displaying the current mathematical values of those cells. Since this is the Base Case, leave them as they are and click OK.

Step 3: Create Alternate Scenarios

Now, mathematically define the variables for your \”Worst Case\” scenario.

1. Click Add again in the Scenario Manager.
2. Name this scenario Worst Case. The Changing cells will mathematically default to the ones you selected previously. Click OK.
3. In the values box, input your pessimistic data. Change the \”Projected Sales\” value to a lower number and the \”Material Costs\” to a higher number.
4. Click OK. Repeat this process to create a \”Best Case\” scenario with optimistic numbers.

Step 4: Swap Scenarios and Generate a Summary

You can now mathematically alter your entire spreadsheet instantly.

1. In the Scenario Manager list, click on Worst Case, then click the Show button. Excel will instantly replace the input variables and recalculate the entire workbook based on your pessimistic data.
2. To compare all scenarios simultaneously, click the Summary… button in the Scenario Manager.
3. Excel will ask you to define the Result cells (the final formula cells you care about, like \”Net Profit\”). Select them and click OK.
4. Excel will mathematically generate a brand new worksheet containing a pivot-style table, perfectly comparing the inputs and outputs of your Best Case, Base Case, and Worst Case scenarios side-by-side.

By mastering the Scenario Manager, financial analysts can mathematically prove multiple business outcomes and generate comparative reports without constantly rewriting their source data.