Scenario Manager in Excel - Step-by-Step Guide

Introduction

Scenario Manager helps you save and compare different sets of values in your spreadsheet. It lets you quickly switch between these sets to see how changes affect your results without rewriting data.

When you want to compare different budget plans for a project.

When you need to test how changing sales numbers affects profit.

When you want to show different loan payment options to a client.

When you want to save multiple versions of input data for a financial model.

When you want to present different what-if analysis results without changing the original data.

Steps

Step 1: Click

- Data tab on the Ribbon

The Data tab options appear

💡 Look for the Forecast group on the Data tab

Step 2: Click

- What-If Analysis button in the Forecast group

A menu with options appears

Step 3: Select

- Scenario Manager from the What-If Analysis menu

The Scenario Manager dialog box opens

Step 4: Click

- Add button in the Scenario Manager dialog

The Add Scenario dialog box opens

Step 5: Type

- Scenario name field

You enter a name for your scenario, like 'Best Case'

Step 6: Select

- Changing cells field

You select the cells that will change in this scenario

Step 7: Click

- OK button in the Add Scenario dialog

A new dialog appears to enter values for the changing cells

Step 8: Type

- Values for the changing cells

You enter the values for this scenario

Step 9: Click

- OK button

The scenario is saved and appears in the Scenario Manager list

Step 10: Select

- Scenario name in the Scenario Manager list

The scenario is highlighted

Step 11: Click

- Show button

The worksheet updates to show the values of the selected scenario

Before vs After

Before

Worksheet shows original values in cells A1 to A3: 100, 200, 300

After

After selecting 'Best Case' scenario, cells A1 to A3 show values: 150, 250, 350

Settings Reference

Scenario name

📍 Add Scenario dialog box

To identify each scenario clearly

Default: Blank

Changing cells

📍 Add Scenario dialog box

Cells that will have different values in each scenario

Default: None

Summary report

📍 Scenario Manager dialog box, Summary button

To create a report comparing all scenarios

Default: Scenario summary

Common Mistakes

Not selecting the correct changing cells when adding a scenario

The scenario will not change the intended cells, so results won't update as expected

Carefully select all cells that should change for the scenario before saving it

Forgetting to click Show after selecting a scenario

The worksheet does not update to the scenario values, so it looks like nothing changed

Always click Show to apply the scenario values to the worksheet

Summary

Scenario Manager saves different sets of values for selected cells to compare results easily.

You add scenarios by naming them and selecting which cells change with new values.

Remember to click Show to see the scenario's effect on your worksheet.

Practice

1. What is the main purpose of the Scenario Manager in Excel?

easy

A. To protect worksheets with passwords

B. To create charts and graphs automatically

C. To sort data alphabetically or numerically

D. To save and compare different sets of input values for decision making

Solution

1. Step 1: Understand Scenario Manager's function

   Scenario Manager saves different input sets to compare outcomes easily.

2. Step 2: Compare with other options

   Other options describe unrelated Excel features like charting, sorting, or protection.

3. Final Answer:

   To save and compare different sets of input values for decision making -> Option D

4. Quick Check:

   Scenario Manager = Save and compare inputs [OK]

Hint: Remember: Scenario Manager is for comparing input sets [OK]

Common Mistakes:

• Confusing Scenario Manager with chart tools
• Thinking it sorts or filters data
• Assuming it protects sheets

2. Where do you find the Scenario Manager feature in Excel?

easy

A. Insert tab > Tables group

B. Data tab > What-If Analysis group

C. Review tab > Comments group

D. Formulas tab > Function Library group

Solution

1. Step 1: Locate Scenario Manager in Excel interface

   Scenario Manager is under Data tab in the What-If Analysis group.

2. Step 2: Eliminate other tabs

   Insert is for tables/charts, Review for comments, Formulas for functions, so they are incorrect.

3. Final Answer:

   Data tab > What-If Analysis group -> Option B

4. Quick Check:

   Scenario Manager location = Data > What-If Analysis [OK]

Hint: Find Scenario Manager under Data tab's What-If Analysis [OK]

Common Mistakes:

• Looking under Insert or Review tabs
• Confusing it with formula functions
• Not knowing What-If Analysis group

3. You create two scenarios in Scenario Manager: Scenario A with sales = 100 and Scenario B with sales = 200. If your formula in cell B1 is =A1*10 and A1 changes with scenarios, what will be the value in B1 when Scenario B is active?

medium

A. 1000

B. 10

C. 2000

D. 300

Solution

1. Step 1: Understand scenario input values

   Scenario B sets sales (A1) to 200.

2. Step 2: Calculate formula result with Scenario B

   Formula B1 = A1 * 10 = 200 * 10 = 2000.

3. Final Answer:

   2000 -> Option C

4. Quick Check:

   200 * 10 = 2000 [OK]

Hint: Multiply scenario input by formula factor [OK]

Common Mistakes:

• Using Scenario A value instead of B
• Forgetting formula multiplies by 10
• Mixing up cell references

4. You tried to add a scenario but got an error saying "Changing cells must be on the worksheet." What is the likely cause?

medium

A. You selected cells outside the current worksheet as changing cells

B. You selected cells from multiple worksheets as changing cells

C. You did not select any cells as changing cells

D. You entered text instead of numbers in changing cells

Solution

1. Step 1: Understand error message meaning

   "Changing cells must be on the worksheet" means selected cells are not on the active sheet.

2. Step 2: Identify cause from options

   Selecting cells outside current worksheet causes this error, not multiple sheets or empty selection.

3. Final Answer:

   You selected cells outside the current worksheet as changing cells -> Option A

4. Quick Check:

   Changing cells must be on active sheet [OK]

Hint: Select changing cells only on active worksheet [OK]

Common Mistakes:

• Selecting cells from other sheets
• Leaving changing cells blank
• Confusing data type with cell location

5. You want to compare three sales scenarios with different prices and quantities. How can Scenario Manager help you see the total revenue for each scenario quickly?

hard

A. Create scenarios with price and quantity as changing cells, then add a formula for total revenue and show each scenario

B. Use Scenario Manager to enter total revenue directly for each scenario

C. Create separate worksheets for each scenario and manually calculate revenue

D. Use Scenario Manager to sort the sales data by revenue

Solution

1. Step 1: Set up scenarios with changing inputs

   Use Scenario Manager to save different price and quantity values as changing cells.

2. Step 2: Use a formula to calculate total revenue

   Create a formula like =Price*Quantity to calculate revenue, then view results per scenario.

3. Step 3: Compare scenarios easily

   Scenario Manager shows each scenario's total revenue without manual recalculation.

4. Final Answer:

   Create scenarios with price and quantity as changing cells, then add a formula for total revenue and show each scenario -> Option A

5. Quick Check:

   Scenario Manager compares inputs and formulas [OK]

Hint: Use changing cells plus formula to compare scenario results [OK]

Common Mistakes:

• Entering total revenue manually in scenarios
• Using separate sheets instead of Scenario Manager
• Thinking Scenario Manager sorts data