Excel 2010 Tips and Tricks Essay Example

Excel 2010: Tips and Tricks Essay Alternatively, take a look at Handout 5 All Functions Using the Scenario Manager Used to model more complicated problems than data tables can handle involving as many as 32 variables 0 A scenario is a named combination of values assigned to one or more variable cells in a what-if model 0 The Scenario Manager records, tracks and applies combinations of variable values 0 Create multiple scenarios for a single what-if model, each with its own sets of variables, You can create as many scenarios as your model requires 0 Distribute a what-if model to other members of a group so they can add their own scenarios. We will write a custom essay sample on Excel 2010: Tips and Tricks specifically for you for only $16.38 $13.9/page Order now We will write a custom essay sample on Excel 2010: Tips and Tricks specifically for you FOR ONLY $16.38 $13.9/page Hire Writer We will write a custom essay sample on Excel 2010: Tips and Tricks specifically for you FOR ONLY $16.38 $13.9/page Hire Writer Then you can collect the multiple versions and merge the scenarios into a single worksheet. Track changes made to scenarios with the version-control features of the Scenario Manager. This feature lets you record the date and the user name each time a scenario is added or modified. 0 Print reports detailing all the changing cells and result cells. 0 Password-protect scenarios from modification, and even hide them from view. Scenario Manager: illustration Imagine that you manage a grocery store whose profit picture is modeled by the The numbers in DO:DO and E:EYE are historic averages; column C contains the range names applied to the relevant cells in columns D and E. Youre interested in testing the impact of changes in these cells on the bottom line in cell EYE. Defining a scenario Well create different sets of assumptions for our model and save each one as a different scenario. To define a scenario, follow these steps: 1 . Click the What-If Analysis button on the Data tab, and then click Scenario Manager. 2. In the Scenario Manager dialog box click Add 3. In the Add Scenario dialog box, type Last Year as the name for your scenario and select DO:DO,DO,E:EYE as the changing cells 4. Click K to create the first scenario. The Scenario Values dialog box appears, displaying a box for each hanging cell. If you have named the changing cells, the names appear adjacent to the boxes, otherwise, the references of the changing cells appear. . To complete a scenario, edit these values, and Just click K. 6. To create another scenario, click Add to return to the Add Scenario dialog box. Browsing the scenarios Select a scenario name in the Scenario Manager dialog box, and click Show. The Scenario Manager replaces the variable values currently on the worksheet with the values you specified when you created the scenario. The Scenario Manager dialog box remains on the screen after you click the Show button thou returning to the worksheet. If you click Close or press Sec to close the Scenario Manager dialog box, the values from the last scenario you browsed remain on the worksheet. Dont forget to create a starting values scenario Creating scenario reports The Scenario Manager summary reports help you keep track of the possibilities, and Pivotal reports give you additional what-if functionality by allowing you to manipulate the elements of the report. Clicking Summary in the Scenario Manager dialog box displays the dialog box shown on this slide. Use it to create reports that show the values hat each scenario assigns to each changing cell. At the bottom of the dialog box, you identify result cells that you want to appear in the report, separated by commas. You want cells that are dependent on the most changing cellsin this case, the Operating Profit value in cell EYE, as well as cell E, the yearly Gross Profit value. The scenario summary report The Scenario Summary is a fully formatted report placed on a new worksheet, Notice that all the changing cell values in column E are shaded in gray. The shading indicates cells that change in the scenario named at the top of the column. Excel 2010 Tips and Tricks Essay Example Excel 2010: Tips and Tricks Essay Alternatively, take a look at Handout 5 All Functions Using the Scenario Manager Used to model more complicated problems than data tables can handle involving as many as 32 variables 0 A scenario is a named combination of values assigned to one or more variable cells in a what-if model 0 The Scenario Manager records, tracks and applies combinations of variable values 0 Create multiple scenarios for a single what-if model, each with its own sets of variables, You can create as many scenarios as your model requires 0 Distribute a what-if model to other members of a group so they can add their own scenarios. We will write a custom essay sample on Excel 2010: Tips and Tricks specifically for you for only $16.38 $13.9/page Order now We will write a custom essay sample on Excel 2010: Tips and Tricks specifically for you FOR ONLY $16.38 $13.9/page Hire Writer We will write a custom essay sample on Excel 2010: Tips and Tricks specifically for you FOR ONLY $16.38 $13.9/page Hire Writer Then you can collect the multiple versions and merge the scenarios into a single worksheet. Track changes made to scenarios with the version-control features of the Scenario Manager. This feature lets you record the date and the user name each time a scenario is added or modified. 0 Print reports detailing all the changing cells and result cells. 0 Password-protect scenarios from modification, and even hide them from view. Scenario Manager: illustration Imagine that you manage a grocery store whose profit picture is modeled by the The numbers in DO:DO and E:EYE are historic averages; column C contains the range names applied to the relevant cells in columns D and E. Youre interested in testing the impact of changes in these cells on the bottom line in cell EYE. Defining a scenario Well create different sets of assumptions for our model and save each one as a different scenario. To define a scenario, follow these steps: 1 . Click the What-If Analysis button on the Data tab, and then click Scenario Manager. 2. In the Scenario Manager dialog box click Add 3. In the Add Scenario dialog box, type Last Year as the name for your scenario and select DO:DO,DO,E:EYE as the changing cells 4. Click K to create the first scenario. The Scenario Values dialog box appears, displaying a box for each hanging cell. If you have named the changing cells, the names appear adjacent to the boxes, otherwise, the references of the changing cells appear. . To complete a scenario, edit these values, and Just click K. 6. To create another scenario, click Add to return to the Add Scenario dialog box. Browsing the scenarios Select a scenario name in the Scenario Manager dialog box, and click Show. The Scenario Manager replaces the variable values currently on the worksheet with the values you specified when you created the scenario. The Scenario Manager dialog box remains on the screen after you click the Show button thou returning to the worksheet. If you click Close or press Sec to close the Scenario Manager dialog box, the values from the last scenario you browsed remain on the worksheet. Dont forget to create a starting values scenario Creating scenario reports The Scenario Manager summary reports help you keep track of the possibilities, and Pivotal reports give you additional what-if functionality by allowing you to manipulate the elements of the report. Clicking Summary in the Scenario Manager dialog box displays the dialog box shown on this slide. Use it to create reports that show the values hat each scenario assigns to each changing cell. At the bottom of the dialog box, you identify result cells that you want to appear in the report, separated by commas. You want cells that are dependent on the most changing cellsin this case, the Operating Profit value in cell EYE, as well as cell E, the yearly Gross Profit value. The scenario summary report The Scenario Summary is a fully formatted report placed on a new worksheet, Notice that all the changing cell values in column E are shaded in gray. The shading indicates cells that change in the scenario named at the top of the column.