Advance Excel Session__ Scenario Manager.pptx

This is a complete PPT for Scenario Management

1. Advance Excel: Scenario Manager

2. What-IF Analysis: Scenario Manager

3. What IF Analysis What-If Analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. Following three kinds of What-If Analysis tools come with Excel which we are going to discuss in this module: Scenario Goal Seek Data Tables

4. Scenario Manager With Scenarios in Excel, one can store multiple versions of data, in the same cells. For example, when preparing a budget, the Marketing and Finance departments may have different forecasts for sales. One can store each forecast as a Scenario, print them separately, or compare them side-by-side.

5. Steps for applying Scenario Manager: Step 1: On the ribbon, select the Data tab > What-If Analysis > Scenario Manager.

6. This displays the Scenario Manager dialog box. Since we haven’t created any scenarios yet, it says there are none defined.

7. Step 2: Step 2: Now Create the First Scenerio In the dialog box, click Add. Enter the name Small Venue. Select the cells you want to have different values (Scenarios). Enter a comment if you want. This is optional. The checkboxes for Protection are only if you want to protect the sheet from changes.

8. Click OK. The Scenario Values dialog box shows you a list of all the cells in the scenario and what their current values are. Note that you can’t resize this box, so use its scrollbar to see all of them. A quick way of creating several scenarios one after another is to click this Add button after entering values. That will immediately display the Add Scenario screen.

9. Step 3: Create multiple Scenarios as per requirements and click OK to get to the Scenario Manager Screen

10. Step 4: SWITCH BETWEEN The sheet still shows the original values. Select one of the scenario and click SHOW icon or Double-click one of the scenario names in the list. The sheet updates with those values.

11. Step 5: View All the Scenarios at Once: Click the Summary button. That confirms you want to create a summary, not a PivotTable, so leave the default radio button selected. It also confirms the main result cell is the Profit or Loss in B24. NOTE: None of the values are dynamic. If you change the underlying data on the original sheet, the values on this sheet will not change. You will need to create a new summary.

12. DO IT YOURSELF SESSION: PRACTICE SHEET on SCENARIO MANAGER

13. Add Scenario to Excel Ribbon An easier way to switch between Scenarios, is to add a command to the Ribbon. Follow these steps, to add a Custom Group, and put the Scenario command in that group. Right-click on the Ribbon, and click Customize the Ribbon From the drop down list at the top left, select All Commands In the list of commands that are currently on the Ribbon, click the plus sign for Data, then click Data Tools. That group contains the Scenario Manager, so we'll add the new group beside it.

14. Below the list, click the New Group button. Then, click the Rename button, type Scenario as the name for the group, and click OK

15. At the left, in the list of All Commands, scroll down to find Scenarios Make sure that the new Scenario group is still selected in the list at the right. Click on Scenarios, then click Add, to put Scenarios in the Scenario group.

16. Click OK, to close the window, then click the Data tab, and select a Scenario to view.

17. Practice sheet to Add Scenario to Excel Ribbon