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.

Related posts:

How to Copy a Table from Excel to Word How to do regression with non numeric data in Excel Survival Analysis in Excel