Learn Building Scenario Logic | Scenario Modeling and Interactive Decision Systems

Swipe to show menu

The workbook already supports dynamic reporting and financial analysis. In this chapter, introduce scenario modeling so the entire system can simulate different business strategies using a single input.

CHOOSE Structure

=CHOOSE(index_num, value1, [value2], ...)

index_num: numeric position being selected;
value1: first possible result;
[value2]: optional additional results.

CHOOSE returns a value based on position instead of performing a lookup.

Scenario Modeling Logic

Scenario systems replace fixed assumptions with switchable inputs.

Instead of manually rewriting formulas, one input controls:

Price assumptions;
Volume assumptions;
Projected Revenue calculations.

Step 1 Create the Scenarios Sheet

Create a new worksheet named:

Scenarios

Add the following columns:

Scenario
Price_Multiplier
Volume_Multiplier
Label

Step 2 Add the Scenario Dataset

Copy the following data:

Scenario,Price_Multiplier,Volume_Multiplier,Label
1,1.00,1.00,Base
2,1.15,0.90,Price Increase
3,0.90,1.20,Volume Push

Convert the dataset into an Excel Table named:

Scenarios

Step 3 Create the Scenario Input

Build an input section:

Label	Value
Active Scenario	1

Apply Data Validation so the input allows:

1, 2, 3

Step 4 Display the Active Scenario Label

Type:

=CHOOSE(B6, B2, B3, B4)

B6: selected scenario number;
B2:B4: available scenario labels.

The displayed label updates automatically based on the selected scenario.

Step 5 Return the Active Price Multiplier

Type:

=CHOOSE(B6, C2, C3, C4)

The formula returns the selected pricing multiplier.

Step 6 Return the Active Volume Multiplier

Type:

=CHOOSE(B6, D2, D3, D4)

The formula returns the selected volume multiplier.

Step 7 Build Projected Revenue

Type:

=SUM(Sales_Data[Revenue]) * CHOOSE(B6, C2, C3, C4) * CHOOSE(B6, D2, D3, D4)

SUM(Sales_Data[Revenue]): baseline Revenue;
CHOOSE(...): selected pricing multiplier;
CHOOSE(...): selected volume multiplier.

The projected Revenue now updates dynamically based on the active scenario.

Step 8 Test Scenario Switching

Change the scenario input value.

Confirm that:

Scenario labels update automatically;
Multipliers update automatically;
Projected Revenue recalculates instantly.

Everything was clear?

Thanks for your feedback!

Section 5. Chapter 1

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 5. Chapter 1

Building Scenario Logic

CHOOSE Structure

Scenario Modeling Logic

1. What is the main role of the `Scenarios` table in this model?

2. What does the `CHOOSE` function rely on to return a value?

3. In the projected Revenue formula, what is being modeled?

Building Scenario Logic

CHOOSE Structure

Scenario Modeling Logic

1. What is the main role of the Scenarios table in this model?

2. What does the CHOOSE function rely on to return a value?

3. In the projected Revenue formula, what is being modeled?

1. What is the main role of the `Scenarios` table in this model?

2. What does the `CHOOSE` function rely on to return a value?