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: Click ok

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

increase data 20 percent

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

Step 8: 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 9: Rename all the cell number of Total Values with names of customer and names of quarters

rename cells

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

scenario summary

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

See also  How to Create Fibonacci Sequence 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.