Scenario Manager - What-IF Analysis

Scenario Manager - What IF

Where has the week gone? It is Wednesday already! I received some excellent news yesterday. After five and half months, CFO Resources / CPASelfstudy.com has finally been approved as a CPE sponsor in New York. Hurray!

But I do need to finish up my What-IF section by talking about Scenario Manager. This is a cool feature and is available in all versions of Excel- you just have to figure out where Microsoft hid it. In earlier versions, you have to go to Tools>Addins. In Excel 2007, look under the What-if icon on the Data ribbon.


Scenarios allows you to substitute values in a number of cells and save that condition under a unique name. By invoking different scenarios, up to 32, you can easily demonstrate to your customers and managers the impact of different events or possibilities.

• A scenario is particularly useful in creating budgets and forecasts when you have a few key variables that are subject to change.
• You can use scenarios to forecast different outcome based upon changes to these variables in your model. You can create and save these scenarios with different names and then display them.
• To compare several scenarios, you can even create a report that summarizes them on the same page- similar to a data table. The report can list the scenarios side by side or summarize them in a pivot table.
  
  Before creating a scenario you need to determine which cells will be changed. Normally these should be your input or changing cells. You can have up to 32 changing cells in a scenario.

To Use Scenarios
1. Click the Data tab on the Ribbon.
2. Click the What-if Analysis button and click Scenario Manager from the menu.
3. Click the Add button.
4. Enter a name for your scenario in the Scenario Name box.
5. Click in the Changing Cells box.
6. Hold down the Ctrl key and select the cells in your worksheet that you wish to change.
7. In the Scenario Values dialog box, enter new values.
8. Click OK.
9. To Apply a Scenario to your worksheet, select the desired Scenario and then click Show.
10. Click Close.

Careful! Adding scenarios to a workbook will change the underlying data so it is highly recommended that you save a backup of your original workbook first.
Once you have created your scenarios, you can create side-by-side Summary reports to compare your scenarios.
Take a look at it if you get a chance. Tomorrow I will try to post some screenshots and an example.