Lesson 4.5 – Scenario Analysis & What-If Modeling in Excel

Lesson Overview

Scenario Analysis and What-If Modeling help analyze different business or financial scenarios without altering the original data. This lesson covers Goal Seek, Scenario Manager, and Data Tables to make data-driven decisions.

1. Goal Seek

1.1 What is Goal Seek?

• Tool to find the input value needed to achieve a desired result.

• Useful for backward calculations.

1.2 Steps to Use Goal Seek

1. Enter formula in a cell (e.g., total profit = sales * price – cost).

2. Go to Data → What-If Analysis → Goal Seek.

3. Set:

   • Set cell: The formula cell you want to achieve a goal

   • To value: Desired result

   • By changing cell: Input cell to adjust

4. Click OK → Excel finds required input value

1.3 Example

• Find required monthly sales to achieve ₹1,00,000 profit:

2. Scenario Manager

2.1 What is Scenario Manager?

• Allows saving multiple sets of input values to compare results in one sheet.

• Useful for best case, worst case, and most likely case analysis.

2.2 Steps to Use Scenario Manager

1. Go to Data → What-If Analysis → Scenario Manager → Add

2. Name the scenario (e.g., Best Case, Worst Case)

3. Select changing cells (inputs)

4. Enter values for the scenario → Click OK

5. Repeat for other scenarios

6. Click Show to see results for each scenario

7. Click Summary to create a comparison table

2.3 Example

• Monthly Sales scenarios:

  • Best Case: 1000 units

  • Most Likely: 700 units

  • Worst Case: 500 units

• Compare total profit for all scenarios

3. Data Tables

3.1 What is a Data Table?

• Allows automatic calculation of results for multiple input values

• Can be one-variable or two-variable

3.2 One-Variable Data Table

• Shows results for different values of one input

• Steps:

  1. Enter formula

  2. Create a column of input values

  3. Select the table range → Data → What-If Analysis → Data Table

  4. Set Column Input Cell (the input cell to vary)

3.3 Two-Variable Data Table

• Shows results for two varying inputs

• Steps:

  1. Enter formula

  2. Create table with row and column inputs

  3. Select range → Data → What-If Analysis → Data Table

  4. Set Row Input Cell and Column Input Cell

3.4 Example

• One-Variable: Profit for sales between 500 to 1000 units

• Two-Variable: Profit for different combinations of sales and price

4. Tips for Scenario Analysis & What-If Modeling

• Always backup data before running scenarios

• Use descriptive scenario names

• Combine with charts for visual comparison

• Use Data Tables for dynamic, automated calculations

5. Real-Life Examples

• Finance: Analyze cash flow for different interest rates or loan amounts

• Sales: Compare revenue for varying sales and pricing strategies

• Operations: Plan resource allocation under multiple scenarios

• Project Management: Forecast project completion under different conditions

6. Practice Questions

1. Use Goal Seek to find required sales to achieve ₹50,000 profit.

2. Create three scenarios (Best, Most Likely, Worst) for monthly sales and compare profit.

3. Build a one-variable data table for profit with sales varying from 500 to 1000 units.

4. Build a two-variable data table for profit with sales and price varying.

5. Explain the difference between Goal Seek, Scenario Manager, and Data Tables.