Learn how to forecast with scenarios using what-if analysis tools in Excel

2 minute read time.

The Scenario Manager is a feature in what-if analysis and you can use this to enhance your forecasting in Microsoft Registered Excel Registered.

A scenario is a set of values that Excel saves and can automatically substitute in specific cells on a worksheet. You can create and save different groups of values on a worksheet, and then switch to any of these new scenarios to view results differently.

For example: Suppose you want to create a budget but you are uncertain of your revenue, you can define different possible values for the revenue and then switch between scenarios by applying any of the what-if analysis tools.

Note: You are welcome to download the workbook to practice.

Applies To: Microsoft Registered Excel Registered 2010 and 2013. 1. Click the Projection worksheet. 2. Set up the Best Case scenario:
  • On the Data tab, in the Data Tools group, click what-if analysis.
  • Click Scenario Manager, then click Add.
  • Click in the scenario name text box and enter "2nd Quarter Best Case".
  • Click in the Changing cells text box and select the D6:D8.
  • Press and hold down the Ctrl key then select D10:D11.
3_5F00_ExcelOnSteroids_5F00_ScenarioManager
  •   Click OK.
  • Enter values for the 2nd Quarter Best Case.
4_5F00_ExcelOnSteroids_5F00_ScenarioManager
  • Click OK.
3. Set up the Worst Case scenario:
  • Click Add.
  • Click in the scenario name text box and enter "2nd Quarter Worst Case".
1_5F00_ExcelOnSteroids_5F00_ScenarioManager
  • Click OK.
  • Enter values for the 2nd Quarter Worst Case.
2_5F00_ExcelOnSteroids_5F00_ScenarioManager
  • Click OK and then Close.
4. To view the scenarios:
  • On the Data tab, in the Data Tools group, click what-if analysis.
  • Click Scenario Manager and then click 2nd Quarter Best Case and click Show, Close.
  • On Data tab, in the Data Tools group, click what-if analysis.
  • Click Scenario Manager and then click 2nd Quarter Worst Case and click Show, Close.
5. To view a scenario summary.
  • On the Data tab, in the Data Tools group, click what-if analysis.
  • Click Scenario Manager.
  • Click the Summary button then click OK.
As you can see, scenarios give you different views of the same data and also helps in simplifying your forecasting process.