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

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

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® Excel® 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_ExcelOnSteroids_ScenarioManager

  •  Click OK.
  • Enter values for the 2nd Quarter Best Case.

4_ExcelOnSteroids_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_ExcelOnSteroids_ScenarioManager

  • Click OK.
  • Enter values for the 2nd Quarter Worst Case.

2_ExcelOnSteroids_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.


Sage © Sage South Africa Pty Ltd 2019 . All Rights Reserved.