Scenario Manager in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a financial analyst at a retail company.

📋 Request: Your manager wants you to analyze how different sales price and cost scenarios affect the profit of a product.

📊 Data: You have a table with product details including Units Sold, Sales Price, Cost per Unit, and you want to see how changing Sales Price and Cost per Unit impacts Profit.

🎯 Deliverable: Create scenarios for different Sales Price and Cost per Unit values using Excel's Scenario Manager and summarize the profit results.

Sample Data

Product	Units Sold	Sales Price	Cost per Unit	Profit
Widget A	1000	20	12	=B2*(C2-D2)
Widget B	800	25	15	=B3*(C3-D3)
Widget C	600	30	18	=B4*(C4-D4)

1

Step 1: Enter the sample data into Excel starting at cell A1, including the formulas for Profit in column E.

In cell E2 enter =B2*(C2-D2) and copy down to E4.

Expected Result

Profit values calculated for each product: Widget A=8000, Widget B=8000, Widget C=7200.

2

Step 2: Open Scenario Manager from the Data tab under What-If Analysis.

No formula; navigate: Data > What-If Analysis > Scenario Manager.

Expected Result

Scenario Manager dialog box opens.

3

Step 3: Create a scenario named 'Base Case' with changing cells C2:D4 (Sales Price and Cost per Unit for all products). Use current values.

Changing cells: C2:D4; Scenario name: Base Case.

Expected Result

Scenario 'Base Case' saved with current Sales Price and Cost per Unit values.

4

Step 4: Create a scenario named 'High Price' where Sales Price increases by 10% for all products (C2:C4), Cost per Unit remains the same.

Change Sales Price cells C2:C4 to 22, 27.5, 33 respectively; Cost per Unit cells D2:D4 unchanged.

Expected Result

Scenario 'High Price' saved with increased Sales Price values.

5

Step 5: Create a scenario named 'Low Cost' where Cost per Unit decreases by 20% for all products (D2:D4), Sales Price remains the same.

Change Cost per Unit cells D2:D4 to 9.6, 12, 14.4 respectively; Sales Price cells C2:C4 unchanged.

Expected Result

Scenario 'Low Cost' saved with decreased Cost per Unit values.

6

Step 6: Show each scenario in Scenario Manager and note the updated Profit values.

Select scenario and click Show.

Expected Result

Profit recalculates for each scenario: Base Case total profit=23200, High Price total profit=29000, Low Cost total profit=30160.

7

Step 7: Create a Scenario Summary report to compare profits across scenarios.

In Scenario Manager, click Summary, select Result cells E2:E4, and choose Scenario summary.

Expected Result

A new sheet shows a table comparing Profit for each product under each scenario.

Final Result

Scenario Summary Report

| Scenario   | Widget A Profit | Widget B Profit | Widget C Profit | Total Profit |
|------------|-----------------|-----------------|-----------------|--------------|
| Base Case  | 8000            | 8000            | 7200            | 23200        |
| High Price | 10000           | 10000           | 9000            | 29000        |
| Low Cost   | 10400           | 10400           | 9360            | 30160        |

✓Increasing sales price by 10% increases total profit significantly.

✓Reducing cost per unit by 20% also increases total profit but more than increasing price in this case.

✓Scenario Manager helps quickly compare financial outcomes under different assumptions.

Bonus Challenge

Add a new scenario 'High Price & Low Cost' combining both increased sales price and decreased cost per unit, then analyze the profit impact.

Show Hint

Create a scenario with Sales Price increased by 10% and Cost per Unit decreased by 20% for all products.

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