Creating financial scenarios in Excel can help you plan and analyze different outcomes based on different assumptions. Financial scenarios in Excel can be easily created by using what-if analysis in Excel.

Scenario manager in Excel

The scenario manager can create multiple scenarios in Excel. In this example we have created two sample scenarios

• Scenario 1 : Sales Data increases by 10% ( conservative)
• Scenario 2: Sales Data increases by 20% ( Aggressive)

Step 1: Insert the sales data per customer per quarter for the sample year.

Step 2: Recreate the same chart in a separate sheet where all the sales have increased by 10%.

How to make a financial scenario?

Step 3: Select Data tab -> What-if Analysis -> Scenario Manager -> Add Scenario

Step 4: Add scenario. Scenario Name: Conservative Select the Total sales per customer and total sales per Quarter in changing cells as below

Step 5: Now, increase the sales by 20% and recreate the same data as follows.

Step 6: Select Data tab -> What-if Analysis -> Scenario Manager -> Add Scenario.

Step 7: Add scenario. Scenario Name: Aggressive.

Select the Total sales per customer and total sales per Quarter in changing cells as below

Step 8: Rename all the cell number of Total Values with names of customer and names of quarters.

Step 9: Select Data -> What-if analysis -> Scenario manager -> summary -> scenario summary -> total sales

Step 10: A scenario summary will be automatically generated. Hide/Delete unnecessary columns to get an idea on the changing figures.

Multiple other financial scenarios can be created.

The scenario-manager can store up to 32 values for a single financial scenario. Scenarios can also be created using Macro in Excel.

While the Scenario Manager is useful for a limited number of scenarios and changing cells, for more complex models with many interacting variables or for dynamic sensitivity analysis, consider using data tables, Goal Seek, or dedicated financial modeling add-ins. These tools offer greater flexibility and control over complex modeling situations.

Related posts:

How to Create Pivot Table from Multiple Sheets How to Do Mann-Whitney U Test in Excel Profit Margin Formula in Excel