How to Create Financial Scenarios?

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.

sales data per customer per quarter

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

sales data increased by 10 percent

How to make a financial scenario?

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

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

edit scenario

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

increase data 20 percent

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

edit scenario increased values

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

rename cells

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

scenario summary

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

See also  How to subtract in Excel

financial scenario

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.