See all How-To Articles

Use Scenario Manager to Calculate Revenue in Excel

This tutorial will demonstrate how to use the Scenario Manager to calculate revenue in Excel.

ScenarioManager Intro

The Scenario Manager is part of Excel’s “What-if” tools.  It can be used to show different figures for forecasting or budgeting.

Creating a Scenario

Firstly, create worksheet where the resultant cell (eg Net Profit) will change according to figures that you enter into the worksheet.  In the example below, the net profit will change according to how many units we can sell.

ScenarioManager Net Profit

Then, select the cell in the worksheet which you wish to change based on the figure that you are going to enter into the Scenario Manager.

ScenarioManager SelectCell

In the Ribbon, select Data > What-If Analysis > Scenario Manager.

ScenarioManager Ribbon

Click on the Add button to add a new scenario.

ScenarioManager Add

 

(1)  Type in the name of the scenario, and then (2) select the cell that you want to change the value of to adjust the result (in this case Net Profit in F3) of the Scenario, and then click OK.

ScenarioManager Name Scenario

 

Enter the value to be placed in B3 and click OK.

ScenarioManager EnterValue

A scenario will be added to the Scenario Manager.

ScenarioManager Bad Sales

You can now continue to add as many different scenarios as you like by changing the value of B3 in each scenario.

ScenarioManager Multiple Scenarios

To show a particular scenario, click on the Scenario name, and then click Show.

ScenarioManager Show

Scenario Summary

Once you have created multiple scenarios, you may wish to see them summarized either in a Pivot table format, or a Summary sheet.

In the Scenario manager, click Summary.

ScenarioManager Summary

Select Scenario Summary, and then, in the Result cells, make sure that you select the Net Profit cells (F3).

 

ScenarioManager Summary Result

 

Click OK and a summary sheet will be created.

ScenarioManager Summary Sheet

Alternatively, we could have selected Scenario PivotTable report to create a pivot table.

ScenarioManager Summary Pivot