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.