Using the Scenario Manager to create and analyze a scenario

Scenario Manager analyzes changes of the result value caused by various situational changes.

You can view how profit changes according to cost ratio changes against the selling price.

Steps

To create a scenario

  1. Create the following table.

  2. Calculate profit. Type the formula =C6-B6 in the cell D6 because profit is selling price minus cost.

  3. Calculate the profit of D7 to D13 by copying and pasting D6 or using AutoFill.
  4. Cost is Price*Cost Ratio. Type the formula =C6*$B$3 in B6.

    For cost ratio, you have to type cell addresses as absolute reference not to change cell reference.

  5. Calculate the cost of B7 to B13 by copying and pasting B6 or using AutoFill.
  6. Select B3 next to cost ratio and click Data > Scenario Manager.
  7. In the Scenario Manager dialog box, click Add.
  8. In the Add Scenario dialog box, type Selling Price 30% in Scenario Name and select the $B$3 cell where the cost ratio will be entered, in Changing Cell.
  9. The Scenario Value dialog box appears by clicking OK.
  10. Type 30% as the scenario value and click OK.
  11. In the Scenario Manager dialog box, add scenarios of Selling Price 50% and Selling Price 60% in the same way.
  12. Click Close to close the dialog box.

 

After a scenario is defined, the result value of the scenario is displayed so that you can check the changes of the result value according to each variable of the scenario.

Steps

To display a scenario

  1. Select a cell where you want to display the result value. Select the cell B3 in this example.
  2. Click Data > Scenario Manager.
  3. In the Scenario Manager dialog box, select the name of the scenario you want. Select Selling Price 60% in this example.
  4. Click Display.
  5. Display the result value of the scenario in the worksheet.
  6. To see another result value, select the name of a different scenario from the Scenario Manager list of the Scenario Manager dialog box and click Show.
  7. The result value of the scenario will be displayed.
  8. Click Close to close the dialog box.

 

If you define a scenario and then use scenario summary, result values by variables will be automatically summarized in the Scenario Summary tab.

Steps

To summarize a scenario

  1. Click Data > Scenario Manager.
  2. In the Scenario Manager dialog box, click Summary.
  3. In the Scenario Summary dialog box, select from B6 through B13 as the range of the output cell.
  4. Click OK.
    A new sheet named Scenario Summary will be created in a new sheet tab and a scenario summary table is displayed.
Was this article helpful?
0 out of 0 found this helpful