Unleash the Power of Scenario Manager in Excel

As an Excel user, you must be familiar with tweaking cell values to model different outcomes But changing cells manually can be tedious for large models

This is where Scenario Manager comes into play. It allows you to save different versions of your spreadsheet, switch between them instantly, and visualize the outcomes.

In this comprehensive guide, you’ll learn how to use Scenario Manager in Excel to effortlessly manage different data scenarios.

What is Scenario Manager in Excel?

Scenario Manager is an Excel tool to create and quickly switch between different versions of a spreadsheet.

For example you may have a complex financial model and want to see results for

• Best case
• Worst case
• Most likely case

Instead of manually changing multiple cells, you can use Scenario Manager to save these as scenarios. Then switch between them with a single click!

Key features of Scenario Manager:

• Save multiple data scenarios
• Instantly switch between scenarios
• Visualize scenario outcomes together
• Compare scenarios to identify trends

This enables robust what-if analysis and simplifies sensitivity testing in your models. Now let’s see how to use it.

How to Create Scenarios in Excel

Follow these steps to create scenarios in Excel:

1. Enter Your Model Data

Build your base spreadsheet model first. This could be anything from a simple expenses table to a large financial forecast.

2. Select Scenario Input Cells

These are cells that you want to change between scenarios. For example, sales amounts, tax rates, cost figures, etc.

3. Go to Data > What-If Analysis > Scenario Manager

This opens the Scenario Manager dialog box.

4. Click Add

Give the scenario a name like “Best Case”, “Worst Case” etc.

5. Enter Values for the Input Cells

Fill the input cells with values you want for this scenario. Leave others blank.

6. Click OK

The scenario is saved and will reflect in the summary on top.

7. Repeat to Add Scenarios

Go through steps 4-6 above to add more scenarios as needed.

That’s it! Now you can switch between the saved scenarios and instantly view different model outcomes.

Scenario Manager Example in Excel

Let’s see an example to understand this better:

![Scenario Manager Example][]

In this revenue forecast model:

• B5:B8 are the input cells – unit price, units sold, fixed costs, variable costs
• Row 12 shows the forecast revenue based on input cells

We create three scenarios:

1. Best Case – High price, high sales, low costs
2. Worst Case – Low price, low sales, high costs
3. Most Likely – Moderate assumptions

Steps:

1. Build model
2. Select input cells B5:B8
3. Go to Scenario Manager
4. Add scenarios (Best, Worst, Most Likely)
5. Input values for B5:B8 in each case
6. Click OK

Now we can instantly view the revenue in each scenario!

Scenario Manager Features in Excel

Here are some useful things you can do with scenarios:

Switch Scenarios – Select any scenario from the dropdown on top to view its data.

Edit Scenarios – Click the Edit button to change values in a scenario.

Merge Scenarios – Combine two scenarios into one. Useful for sensitivity testing.

Delete Scenarios – Remove any unwanted scenarios.

Generate Report – Creates a report comparing input values and outcomes of scenarios.

View Outcomes Together – Check ‘Show’ to see scenario outcomes side-by-side.

Protect Sheet – Lock the sheet and optionally the scenarios to prevent accidental changes.

Scenario Manager Use Cases

Here are some examples of how Scenario Manager is used for analysis:

Budgeting and Forecasting – Model best, worst and expected case assumptions.

Sensitivity Analysis – See how outcomes vary for different variables.

Simulations – Test model robustness across wide range of inputs.

Presentations – Switch between scenario views during presentations.

Goal Seeking – Understand target inputs needed to achieve specific outcomes.

Decision Analysis – Compare scenarios to determine optimal business strategy.

Impact Assessment – Quantify risks and opportunities for different situations.

Audits – Test model integrity by stress testing assumptions.

The possibilities are truly endless with Scenario Manager!

Scenario Manager Limitations

Despite being an extremely useful tool, Scenario Manager does have some limitations:

• Max. 32 scenarios per workbook
• Scenarios need manual updating if model structure changes
• Limited flexibility in summarizing scenario differences
• Cannot link scenarios across multiple workbooks
• Cell formatting not managed by Scenario Manager
• Limited ability to automate scenario creation and analysis

For advanced requirements, you may need complex VBA code or an Excel add-in. But Scenario Manager covers fundamental scenario modeling needs for most users.

Scenario Manager Best Practices

Here are some tips to use Scenario Manager effectively:

• Keep fewer key input cells for clarity
• Build scenarios from smallest variances to largest
• Name scenarios intuitively for easy selection
• Stress test extreme cases to check model validity
• Update scenarios whenever source data changes
• Review reports before finalizing decisions
• Protect sheet and scenarios to prevent errors
• Document methodology and limitations

Scenario Manager is an incredibly useful tool for conducting what-if analysis in Excel. It saves the effort of manually changing input values and instantly allows viewing alternative outcomes.

With just a few clicks, you can create and manage multiple scenarios for effective forecasting, projections, simulations and decision analysis.