What-If analysis

Hi Tommy,

Thank you for the forum question.

The key limitation
Scenario Manager’s changing cells need to be on the same worksheet. The Merge button does not combine changing cells from different tabs into one scenario; it imports scenarios that were created on another worksheet or in another workbook. Microsoft describes Merge primarily as a way to collect separately prepared scenarios into one place.

The cleanest solution is therefore to create a small scenario-control section on one assumptions tab, then make both your revenue and staff-cost workings respond to those controls.

Recommended model structure
Create a tab called something like Assumptions or Scenario Control, with inputs such as:
Input Example
Sales team exit date 31/12/2027
Revenue active after exit? 0
Sales team active after exit? 0

In practice, you may only need one changing cell:

Sales team exit date
Both the revenue and staff-cost formulas can reference that date.
Revenue formula
Suppose:
• Assumptions!B5 contains the sales-team exit date
• Revenue!F4 contains the forecast month
• Revenue!F20 would otherwise contain the forecast revenue calculation

The adjusted formula could be:
=IF(F$4>=$Assumptions.$B$5,0,Existing_Revenue_Calculation)
For example:
=IF(F$4>=Assumptions!$B$5,0,F10*F12)
Staff-cost formula
On the staff-cost tab:
=IF(F$4>=Assumptions!$B$5,0,Existing_Staff_Cost_Calculation)

For example:
=IF(F$4>=Assumptions!$B$5,0,F15*F16)
This means that selecting one exit date affects both tabs automatically.
If the team remains employed through the exit month, use:
=IF(F$4>EOMONTH(Assumptions!$B$5,0),0,Existing_Calculation)

That distinction is important:
• >= exit date removes revenue/cost from the period containing the exit date.
• > EOMONTH(exit date,0) keeps revenue/cost through the end of the exit month.

Setting up your two scenarios

Go to:
Data → What-If Analysis → Scenario Manager
Choose the central exit-date assumption—such as Assumptions!B5—as the Changing cell.
1. My best estimate
Set the exit date to a date beyond your forecast horizon, for example:
31/12/2099
This leaves revenue and staff costs intact throughout the forecast.
Name the scenario:
My best estimate
2. Sales team exit
Use the same changing cell, but enter the actual proposed exit date—for example:
30/06/2027
Name the scenario:
Sales team exit

Excel scenarios store sets of input values and substitute them into the model; the calculated outputs should be formulas driven by those inputs. A scenario can store up to 32 changing values, although your model should ideally need only one or a few central controls.

If revenue stops on a different date
If revenue does not cease on exactly the same date as employment costs, create two central assumptions:
Sales team exit date
Revenue cessation date
Then Scenario Manager would use both cells:
Assumptions!B5, Assumptions!B6
Your scenarios might be:
Scenario Team exit date Revenue cessation date
My best estimate 31/12/2099 31/12/2099
Sales team exit 30/06/2027 30/06/2027

You could also add other inputs if required, such as:
• redundancy payments;
• notice-period costs;
• commission run-off;
• customer-contract revenue that continues after the team leaves;
• recruitment or restructuring costs.

Why not select all revenue and salary cells?

Scenario Manager is best used to change assumptions, not to overwrite every monthly forecast output.
Rather than selecting, for example, 24 revenue cells and 24 staff-cost cells:
1. Change one exit-date assumption.
2. Let the formulas determine which forecast months become zero.
3. Keep the underlying calculations intact.
4. Use named ranges such as Sales_Team_Exit_Date to make formulas readable.

For example:
=IF(F$4>=Sales_Team_Exit_Date,0,F10*F12)
This is more robust, easier to audit, and avoids Scenario Manager’s 32-changing-cell limit.

Scenario summary
Once both scenarios are configured:
1. Open Scenario Manager.
2. Select Summary.
3. Choose key result cells, such as:
o total revenue;
o EBITDA;
o operating profit;
o closing cash;
o headcount;
o total staff costs.
4. Select Scenario summary.
Excel will create a separate comparison sheet. Result cells can be outputs from the wider model, while the scenario itself changes the central assumption cells.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector