How to use Scenario Manager in Excel 2013

Scenarios form an important part in real-world problems. The inputs used in the real-world problems are dynamic and hence lead to different outputs. Scenario Manager in Excel help us in getting solutions of these problems by accounting for changing inputs. Before we create a scenario, we must design the worksheet so that it contains at least one formula that is dependent on the input cells.

Once various scenarios are created based on different input values, Excel provides facility to create a detailed summary report of these scenarios.

Let's understand this on the basis of Costing example. The example calculates Net revenue of a coffee shop.

Formula: Net revenue = Gross Revenue - Total Variable Cost - Total Fixed Cost

Where, Gross Revenue = (Number of coffee cups sold *Selling Price per unit);                                 Total Variable Cost = (Number of coffee cups sold*Variable cost per unit)

Let's name these input cells as Number_Units_Sold, Selling_Price_Unit, Variable_Unit, Total_Fixed

Go To: "DATA" tab in the ribbon>> What-If Analysis>>>Scenario Manager [Shortcut: Alt+AWS]

CREATING SCENARIOS:

1. Click on the "Add" button in the Scenario Manager dialog box.

Insert Scenario name as "Base Case" and select all the input values (C2:C5) in Changing Cells field. In case the input values are scattered, we can hold CTRL key while clicking on different input cells OR use comma after clicking on each input cell.

2. Click "OK". This will open Scenario Values dialog box.

Insert various input values corresponding to this scenario as shown above.

3. Click "Add" to insert new scenario OR click "OK" to stop adding scenarios.

Once all the scenarios are added, the Scenario Manager dialog box will open again and show names of all the entered scenarios.

4. To see result of a particular scenario, click on the Scenario name and press "Show" button.

Excel will automatically change input values as listed in the scenario and show relevant output.

IMPORTANT NOTE:

• Always assign range names to your input cells before you begin creating the various scenarios. It is easy to recognize the input filed by names while using the Scenario Values dialog box to enter input values for various scenarios.