This tutorial demonstrates how to use the Scenario Manager to calculate revenue in Excel.
The Scenario Manager is one of Excel’s “what-if” tools. It can be used to show different figures for forecasting or budgeting.
Create a Scenario
1. First, create worksheet where the resultant cell (i.e., Net Profit) changes according to figures you enter into the worksheet. In the example below, the net profit changes according to how many units you can sell.
2. Then, select the cell in the worksheet which you wish to change based on the figure you are going to enter into the Scenario Manager.
3. In the Ribbon, select Data > What-If Analysis > Scenario Manager.
4. Click on the Add button to add a new scenario.
5. Then, (1) Type in the name of the scenario, and (2) select the cell you want to change the value of to adjust the result (here, Net Profit in F3) of the scenario, and then click OK.
6. Enter the value to be placed in B3 and click OK.
7. A scenario is added to the Scenario Manager.
8. You can now continue to add as many different scenarios as you like by changing the value of B3 in each scenario.
9.To show a particular scenario, click on the scenario’s name, and then click Show.
Once you have created multiple scenarios, you may wish to see them summarized either in a pivot table format, or a summary sheet.
1. In the Scenario Manager, click Summary…
2. Select Scenario summary, and then, in the Result cells field, make sure you select the Net Profit cell (F3).
3. Click OK and a summary sheet is created.
4. Alternatively, you could have selected Scenario PivotTable report to create a pivot table.