How to Create Financial Scenarios in Excel

Financial scenarios in Excel can be easily created by using what-if analysis in Excel (present from Excel 2013 onwards). 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

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.

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.

Template

You can download the Template here – Download
Previous articleAvoid Errors Using IFERROR-Everyone Should Know
Next articleLinking Text Box To A Specific Cell