How to Create a Scenario Manager in Excel

The Scenario Manager in Excel allows you to create and compare different scenarios based on changing variables. Here’s how you can use it:

1. Set Up Your Data:
   • Organize your data in a way that allows you to perform “what-if” analysis. For example, you might have a set of variables and a formula that calculates a result based on those variables.
2. Select the Cell with the Formula:
   • Click on the cell that contains the formula you want to use for your calculation.
3. Go to the “Data” Tab:
   • Click on the “Data” tab in the Excel ribbon.
4. Click on “What-If Analysis”:
   • In the “Data Tools” group, you’ll find “What-If Analysis”. Click on it, and from the dropdown menu, select “Scenario Manager”.
5. Add a Scenario:
   • In the Scenario Manager dialog box, click “Add”.
6. Name Your Scenario:
   • Give your scenario a descriptive name. This could be something like “Best Case”, “Worst Case”, etc.
7. Set Changing Cells:
   • In the “Changing Cells” field, select the cells that contain the variables you want to change for this scenario. These are the cells that will be adjusted to see the impact on your formula.
8. Set Values for the Changing Cells:
   • For each changing cell, enter the values you want to use for this scenario.
9. Add More Scenarios (Optional):
   • You can add multiple scenarios to compare different sets of variables.
10. Show a Scenario:
    • In the Scenario Manager dialog box, select a scenario and click “Show”. This will change the values in the changing cells to match the selected scenario.
11. View Results:
    • Observe how the result of your formula changes based on the scenario you selected.
12. Generate a Summary Report (Optional):
    • In the Scenario Manager dialog box, you can click “Summary” to create a summary report that shows the results of multiple scenarios at once.

Example:

Suppose you have a loan calculation where you’re considering different interest rates and loan amounts:

1. Set up your data with initial loan amount, interest rates, and a cell to calculate the monthly payment.
2. Select the cell with the formula for the monthly payment.
3. Go to the “Data” tab, click on “What-If Analysis”, and select “Scenario Manager”.
4. Click “Add”, name your scenario (e.g., “High Interest, Low Amount”).
5. Select the changing cells (interest rate and loan amount).
6. Set the values for these changing cells to reflect the scenario.
7. Add more scenarios as needed.
8. Show a scenario to see how it impacts the monthly payment.

The Scenario Manager is a powerful tool for performing “what-if” analysis and comparing different sets of variables. It’s particularly useful for financial modeling and decision-making.