Sensitivity Analysis
Swipe to show menu
Scenario modeling tests individual business cases, but sensitivity analysis evaluates how outputs behave across a full range of conditions. In this chapter, build matrix-based models for Revenue and Profit sensitivity.
Mixed References
Sensitivity grids rely on mixed references so formulas adjust correctly when copied across rows and columns.
Examples:
$H4
I$3
$H4: locks the column, allows the row to change;I$3: locks the row, allows the column to change.
Multiplicative Scenario Logic
Sensitivity analysis uses multiplicative scaling:
Base_Value * Price_Multiplier * Volume_Multiplier
This preserves proportional relationships between variables.
Inside the worksheet, create the following structure:
| Cell | Value |
|---|---|
| H1 | Sensitivity Analysis |
| H2 | Projected Revenue |
Inside I3:M3, add:
0.80
0.90
1.00
1.10
1.20
Inside H4:H8, add:
0.80
0.90
1.00
1.10
1.20
- Top row: volume multipliers;
- Left column: price multipliers.
Inside I4, type:
=SUM(Sales_Data[Revenue])*$H4*I$3
$H4: price multiplier;I$3: volume multiplier.
Fill the formula across:
I4:M8
Each cell now represents a unique pricing and demand scenario.
Select:
I4:M8
Apply:
Conditional Formatting → Color Scales → Red-Yellow-Green
The grid now highlights:
- Low-performance zones;
- Mid-range transitions;
- High-performance scenarios.
Create a second structure:
| Cell | Value |
|---|---|
| H11 | Projected Profit |
Reuse the same multiplier layout:
I12:M12
H13:H17
Inside I13, type:
=SUM(Sales_Data[Profit])*$H13*I$12
Fill the formula across:
I13:M17
The grid now models Profit sensitivity under different pricing and demand combinations.
Review both grids and observe how:
- Revenue changes proportionally with pricing and volume;
- Profit behavior depends on the underlying cost structure;
- Certain combinations create stronger or weaker margins.
The grid zones represent different business conditions:
- Top-left: low price and low volume;
- Bottom-right: high price and high volume;
- Diagonal: trade-offs between pricing power and demand changes.
1. What is the main purpose of sensitivity analysis?
2. Why are mixed references used in sensitivity grids?
3. Why can Revenue and Profit grids behave similarly in this setup?
Thanks for your feedback!
Ask AI
Ask AI
Ask anything or try one of the suggested questions to begin our chat
